 |
Microsoft Visual C#: Selecting Records from Different
Tables |
|
|
|
In a list-based control such as a data grid view or a
list view, you can present the records of as many tables as you want and
those tables don't need to have anything in common. They don't even have to
belong to the same database. Consider the following database named Cruise
equipped with a table named Cabins:
|
using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnDatabase;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnDatabase = new Button();
btnDatabase.Text = "Database";
btnDatabase.Location = new Point(12, 12);
btnDatabase.Click += new EventHandler(btnDatabaseClick);
Controls.Add(btnDatabase);
}
void CreateDatabase()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Integrated Security=SSPI;"))
{
SqlCommand command =
new SqlCommand("CREATE DATABASE Cruise1 " +
"ON PRIMARY " +
"( NAME = StoreRepository, FILENAME = 'C:\\exercises\\Cruise1.mdf') " +
"LOG ON " +
"( NAME = StoreLog, FILENAME = 'C:\\exercises\\Cruise1.ldf');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A database named Cruise1 has been created.",
"Cruise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='Cruise1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Cabins(" +
"CabinType nvarchar(20) not null," +
"Deck nchar(20)," +
"Size int," +
"Rate1Passenger money," +
"Rate2Passengers money);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Cabins\" has been created.",
"Cruise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='Cruise1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Cabins " +
"VALUES(N'Inside', N'Riviera', 215, 289.00, 578.00)," +
" (N'Outside', N'Riviera', 185, 319.00, 638.00)," +
" (N'Outside', N'Riviera', 225, 389.00, 778.00)," +
" (N'Suite', N'Verandah', 295, 1009.00, 2018.00)," +
" (N'Inside', N'Upper', 185, 379.00, 758.00)," +
" (N'Inside', N'Main', 215, 359.00, 718.00)," +
" (N'Outside', N'Riviera', 185, 349.00, 698.00)," +
" (N'Suite', N'Main', 300, 885.00, 1680.00);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been created.",
"Cruise",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
void btnDatabaseClick(object sender, EventArgs e)
{
CreateDatabase();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
Here is another database that contains a table named
Employees:
void CreateDatabase()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Integrated Security=SSPI;"))
{
SqlCommand command =
new SqlCommand("IF EXISTS (SELECT name " +
"FROM sys.databases " +
"WHERE name = N'VideoStore1') " +
"DROP DATABASE VideoStore1 " +
"CREATE DATABASE VideoStore1 " +
"ON PRIMARY " +
"( NAME = StoreRepository, FILENAME = 'C:\\Video Store\\Video1.mdf') " +
"LOG ON " +
"( NAME = StoreLog, FILENAME = 'C:\\Video Store\\Video1.ldf');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A database named VideoStore1 has been created.",
"Video Store",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Employees(" +
"FirstName nvarchar(20), " +
"MI nchar(2), " +
"LastName nvarchar(20), " +
"HourlySalary smallmoney, " +
"Status tinyint);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Employees\" has been created.",
"Video Store",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Employees " +
"VALUES(N'Christopher', N'L', N'Larsen', 14.50, 3), " +
" (N'Henry', N'C', N'Jonathan', 12.85, 1), " +
" (N'Lance', N'A', N'Seagal', 16.95, 1); " +
"INSERT INTO Employees(FirstName, LastName, Status) " +
"VALUES(N'Roberta', N'Ortez', 1); " +
"INSERT INTO Employees(FirstName, LastName, HourlySalary) " +
"VALUES(N'Paul', N'Swanson', 10.90); " +
"INSERT INTO Employees " +
"VALUES(N'Kristofer', N'L', N'Michaels', 12.85, 2); " +
"INSERT INTO Employees " +
"VALUES(N'Ivan', N'N', N'Sanders', 15.00, 2), " +
" (N'David', N'P', N'Monahan', 13.05, 1);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
To show the records of more than one table, write a
SELECT statement for each table. If the tables belong to different
databases, make sure you indicate this. Here is an example:
using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
DataGridView dgvCruise;
DataGridView dgvEmployees;
SplitContainer scViews;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
dgvCruise = new DataGridView();
dgvCruise.Dock = DockStyle.Fill;
dgvCruise.Location = new Point(12, 44);
dgvCruise.Size = new System.Drawing.Size(470, 100);
dgvEmployees = new DataGridView();
dgvEmployees.Dock = DockStyle.Fill;
dgvEmployees.Location = new Point(12, 150);
dgvEmployees.Size = new System.Drawing.Size(470, 100);
scViews = new SplitContainer();
scViews.Dock = DockStyle.Fill;
scViews.SplitterDistance = 100;
scViews.Panel1.Controls.Add(dgvCruise);
scViews.Panel2.Controls.Add(dgvEmployees);
scViews.Orientation = Orientation.Horizontal;
Text = "Video Collection";
Size = new System.Drawing.Size(500, 290);
Controls.Add(scViews);
Load += new EventHandler(ExerciseLoad);
}
void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='Cruise1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT * FROM Cabins;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaCruise =
new SqlDataAdapter(command);
BindingSource bsCruise = new BindingSource();
DataSet dsCruise = new DataSet("CabinsSet");
sdaCruise.Fill(dsCruise);
bsCruise.DataSource = dsCruise.Tables[0];
dgvCruise.DataSource = bsCruise;
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command = new SqlCommand(
"SELECT FirstName, MI, LastName, HourlySalary, Status = " +
"CASE Status " +
" WHEN 1 THEN N'Full-Time' " +
" WHEN 2 THEN N'Part-Time' " +
" ELSE N'Contractor/Seasonal' " +
"END " +
"FROM Employees;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(command);
BindingSource bsEmployees = new BindingSource();
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
bsEmployees.DataSource = dsEmployees.Tables[0];
dgvEmployees.DataSource = bsEmployees;
}
}
void ExerciseLoad(object sender, EventArgs e)
{
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
|
|