ADO.NET How To: Unite Records

	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:

Showing Records of Various Tables

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

Uniting the Records

Sometimes, either for the sake of comparing records or for preparing to merge them, you may want to display, at once, the records of more than one table. To support the ability to select records of various tables and show them together, you use the UNION operator. The basic formula to follow is:

SELECT What FROM OneTable
UNION
SELECT What FROM AnotherTable;

There are rules you must follow:

Both tables must have the same number of columns
The sequence of data types of the columns in each table must be the same. For example, if the column in one table is string-based, the corresponding column in the other table must also be string-based
The data types in the order of the columns of both tables must be compatible. For example, if the first column of one table has an integer based data type, the first column of the other table must also have an integer-based data type that can be reconciled with the corresponding column of the other table

The columns don't have to have the same names.

Practical Learning: Uniting the Records

On the form, click the top data grid view
In the Properties window, click (Name) and type dgvMusicCollection
On the form, click the bottom data grid view and press Delete

Change the code of the Click event as follows:

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 " +
                           "UNION " +
                           "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];

        dgvMusicCollection.DataSource = bsMusicCollection;
    }
}

Press Strl + F5 to execute
Click Show
Close the form and return to your programming environment

Uniting ALL Records

By default, when UNION is used, the database engine arranges the records based on the first column. This means that if the first column is number-based, the records would be sorted in increment based on that column. If the first column is character-based, the list would be given with that column in alphabetical order. As an alternative, you can ask the database engine to include the records as they are made available; that is, the records of the first table, followed by those of the second table, and so on. To give this instruction, add the ALL keyword after UNION. The formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION ALL
SELECT WhatField(s) FROM AnotherTable;

Here is an example:

SELECT * FROM Employees
UNION ALL
SELECT * FROM Contractors;
GO