Home

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

  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click File -> New Project...
  3. In the middle list, click Windows Forms Application
  4. Change the Name to MusicCollection1
  5. Click OK
  6. In the Solution Explorer, right click Form1.cs and click Rename
  7. Type MusicCollection.cs and press enter
  8. Double-click the middle of the form to generate its Load event
  9. 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();
            }
        }
    }
  10. Press F5 to execute
  11. After clicking OK on the message boxes, close the form and return to your programming environment
  12. Change the design of the form as follows:
     
    Music Collection
    Control Text Name Anchor
    Button Button Show btnShow  
    DataGridView DataGridView   dgvAfricanPop Top, Bottom, Left, Right
    DataGridView DataGridView   dgvRock Bottom, Left, Right
  13. 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;
        }
    }
  14. Press F5 to see the result
  15. Click Show
     
    Music Collection
  16. Close the form and return to your programming environment

Practical LearningPractical Learning: Uniting the Records

  1. On the form, click the top data grid view
  2. In the Properties window, click (Name) and type dgvMusicCollection
  3. On the form, click the bottom data grid view and press Delete
     
    Music Collection
  4. 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;
        }
    }
  5. Press F5 to execute
  6. Click Show
     
    Music Collection
  7. 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 C#Key