Microsoft Visual C#: Uniting Records

	Microsoft Visual C#: Uniting Records

Introduction

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: Introducing Unions

Start Microsoft Visual Studio
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 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

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 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


Home	Copyright © 2011 FunctionX