|
ADO.NET How To: Unite Records |
|
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:
Practical
Learning: Introducing Unions
|
|
- Start Microsoft Visual Studion
- To create a new application, on the main menu, click FILE -> New
Project...
- In the middle list, click Windows Forms Application
- Change the Name to MusicCollection1
- Click OK
- In the Solution Explorer, right click Form1.cs and click Rename
- Type MusicCollection.cs and press enter
- Double-click the middle of the form to generate its Load event
- To create a new database, implement the event as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace MusicCollection1
{
public partial class MusicCollection : Form
{
public MusicCollection()
{
InitializeComponent();
}
void CreateMusicCollection()
{
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Integrated Security=SSPI;"))
{
SqlCommand cmdMusicCollection =
new SqlCommand("CREATE DATABASE MusicCollection1;", cnnMusicCollection);
cnnMusicCollection.Open();
cmdMusicCollection.ExecuteNonQuery();
MessageBox.Show("The MusicCollection1 database has been created.",
"Music Collection", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database=MusicCollection1;" +
"Integrated Security=SSPI;"))
{
string strCreate = "CREATE TABLE AfricanPop(Artist nchar(50)," +
"AlbumName nvarchar(50)," +
"Releaser nvarchar(50)," +
"CopyrightYear int);";
SqlCommand cmdMusicCollection = new SqlCommand(strCreate, cnnMusicCollection);
cnnMusicCollection.Open();
cmdMusicCollection.ExecuteNonQuery();
MessageBox.Show("A table named AfricanPop has been created.",
"Music Collection", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database=MusicCollection1;" +
"Integrated Security=SSPI;"))
{
string strCreate = "INSERT INTO AfricanPop " +
"VALUES('Salif Keita', 'Folon... The Past', 'Mango', 1995)," +
"('Vincent Nguini', 'Symphony-Bantu', 'Mesa Records', 1994)," +
"('Tshala Muana', 'Mutuashi', 'Stern''s Music', 1996);";
SqlCommand cmdMusicCollection = new SqlCommand(strCreate, cnnMusicCollection);
cnnMusicCollection.Open();
cmdMusicCollection.ExecuteNonQuery();
MessageBox.Show("Records have been added to the table.",
"Music Collection", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database=MusicCollection1;" +
"Integrated Security=SSPI;"))
{
string strCreate = "CREATE TABLE Rock(" +
"Musician nvarchar(50)," +
"Title nvarchar(50) not null," +
"RecordLabel nvarchar(50)," +
"YearReleased smallint);";
SqlCommand cmdMusicCollection = new SqlCommand(strCreate, cnnMusicCollection);
cnnMusicCollection.Open();
cmdMusicCollection.ExecuteNonQuery();
MessageBox.Show("A table named Rock has been created.",
"Music Collection", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database=MusicCollection1;" +
"Integrated Security=SSPI;"))
{
string strCreate = "INSERT INTO Rock " +
"VALUES('Taylor Dayne', 'Can''t Fight Fate', " +
"'Arista Records', 1989),('Cyndi Lauper', " +
"'She''s So Unusual', 'CBS', 1983)," +
"('Beverly Hills Cop', 'Soundtrack', 'MCA Records', 1984)," +
"('Michael Jackson', 'Dangerous', 'MJJ Productions', 1991)," +
"('Bruce Hornsby and the Range', 'The Way It Is'," +
"'Arista/Ariola International', 1986);";
SqlCommand cmdMusicCollection = new SqlCommand(strCreate, cnnMusicCollection);
cnnMusicCollection.Open();
cmdMusicCollection.ExecuteNonQuery();
MessageBox.Show("Records have been added to the table.",
"Music Collection", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
private void MusicCollection_Load(object sender, EventArgs e)
{
CreateMusicCollection();
}
}
}
- Press Ctrl + F5 to execute
- After clicking OK on the message boxes, close the form and return
to your programming environment
- Change the design of the form as follows:
|
Control |
Text |
Name |
Anchor |
Button |
|
Show |
btnShow |
|
DataGridView |
|
|
dgvAfricanPop |
Top, Bottom, Left, Right |
DataGridView |
|
|
dgvRock |
Bottom, Left, Right |
|
- Double-click the Show button and change the file as follows:
private void MusicCollection_Load(object sender, EventArgs e)
{
// CreateMusicCollection();
}
private void btnShow_Click(object sender, EventArgs e)
{
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database='MusicCollection1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdMusicCollection =
new SqlCommand("SELECT * FROM AfricanPop;",
cnnMusicCollection);
SqlDataAdapter sdaMusicCollection =
new SqlDataAdapter(cmdMusicCollection);
BindingSource bsMusicCollection = new BindingSource();
DataSet dsMusicCollection = new DataSet("MusicSet");
sdaMusicCollection.Fill(dsMusicCollection);
cnnMusicCollection.Open();
bsMusicCollection.DataSource = dsMusicCollection.Tables[0];
dgvAfricanPop.DataSource = bsMusicCollection;
}
using (SqlConnection cnnMusicCollection =
new SqlConnection("Data Source=(local);" +
"Database='MusicCollection1';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdMusicCollection =
new SqlCommand("SELECT * FROM Rock;",
cnnMusicCollection);
SqlDataAdapter sdaMusicCollection =
new SqlDataAdapter(cmdMusicCollection);
BindingSource bsMusicCollection = new BindingSource();
DataSet dsMusicCollection = new DataSet("MusicSet");
sdaMusicCollection.Fill(dsMusicCollection);
cnnMusicCollection.Open();
bsMusicCollection.DataSource = dsMusicCollection.Tables[0];
dgvRock.DataSource = bsMusicCollection;
}
}
- Press F5 to see the result
- Click Show
- Close the form and return to your programming environment
|
|