Home

ADO.NET Programming: Merging Records

     

Introduction

Imagine you have two tables created at different times, or by different people, or for different reasons. You may have two tables that have duplicate records (the same record in more than one table, for example the same employee number and same name in two tables). You may have records in different tables but some of those records share a field's value (you may have an employee A in one table and another employee B in another table but both have the same employee number with different names, perhaps when two companies merge). As an assignment, you may be asked to combine the records of those tables into one.

Practical LearningPractical Learning: Introducing Records Merging

  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 CarDealer1
  5. Click OK
  6. In the Solution Explorer, right click Form1.cs and click Rename
  7. Type CarDealer.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 CarDealer1
    {
        public partial class CarDealer : Form
        {
            public CarDealer()
            {
                InitializeComponent();
            }
    
            void InitiateCarDealer()
            {
                using (SqlConnection conCarDealer =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCarDealer =
                new SqlCommand("CREATE DATABASE CarDealer1;", conCarDealer);
                    conCarDealer.Open();
    
                    cmdCarDealer.ExecuteNonQuery();
                    MessageBox.Show("The CarDealer1 database has been created.",
                                    "Car Dealer", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
    
                using (SqlConnection conCarDealer =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CarDealer1;" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCarDealer =
                        new SqlCommand("CREATE TABLE NewCars(" +
                                       "CarCode nchar(20) not null," +
                                       "CarYear int, Make nvarchar(40)," +
                                       "Model nvarchar(50), Price money);",
                                       conCarDealer);
                    conCarDealer.Open();
    
                    cmdCarDealer.ExecuteNonQuery();
                    MessageBox.Show("A table named NewCars has been created.",
                                    "Car Dealer", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
    
                using (SqlConnection conCarDealer =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CarDealer1;" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCarDealer =
                        new SqlCommand("INSERT INTO NewCars " +
                                       "VALUES('P2740442', 2010, 'Honda', 'Accord', 24650)," +
                                       "      ('WKL72750', 2009, 'Honda', 'CR-V', 25445)," +
                                       "      ('68471815', 2010, 'Honda', 'Accord', 28245)," +
                                       "	  ('974PP392', 2009, 'Honda', 'Civic', 22500)," +
                                       "	  ('75854H80', 2009, 'Honda', 'Civic Hybrid', 14675)," +
                                       "	  ('26RT8137', 2010, 'Honda', 'Insight', 22080);",
                                       conCarDealer);
                    conCarDealer.Open();
    
                    cmdCarDealer.ExecuteNonQuery();
                    MessageBox.Show("Records have been added to the table.",
                                    "Car Dealer", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
    
                using (SqlConnection cnnCarDealer =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CarDealer1;" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCarDealer =
                        new SqlCommand("CREATE TABLE UsedVehicles(" +
                                       "VehicleNumber nchar(20) not null," +
                                       "YearManufactured int, Make nvarchar(40)," +
                                       "Model nvarchar(50),	Mileage int," +
                                       "OriginalPrice money, CurrentValue money);", cnnCarDealer);
                    cnnCarDealer.Open();
    
                    cmdCarDealer.ExecuteNonQuery();
                    MessageBox.Show("A table named UsedVehicles has been created.",
                                    "Car Dealer", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
    
                using (SqlConnection conCarDealer =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CarDealer1;" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCarDealer = new SqlCommand("INSERT INTO UsedVehicles " +
                                       "VALUES('984795MM', 1998, 'Ford', 'Escort', 112683, 12420, 3250)," +
                                       "	  ('96304807', 2006, 'Toyota', 'Corolla', 64286, 18855, 12500);" +
                                       "INSERT INTO UsedVehicles(VehicleNumber, Make, Model, OriginalPrice)" +
                                       "VALUES('P2740442', 'Honda', 'Accord', 24650)," +
                                       "	  ('68471815', 'Honda', 'Accord', 28245)," +
                                       "	  ('75854H80', 'Honda', 'Civic Hybrid', 14675);",
                                       conCarDealer);
                    conCarDealer.Open();
    
                    cmdCarDealer.ExecuteNonQuery();
                    MessageBox.Show("Records have been added to the table.",
                                    "Car Dealer", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
            }
    
            private void CarDealer_Load(object sender, EventArgs e)
            {
                InitiateCarDealer();
            }
        }
    }
  10. Press F5 to execute
  11. After click OK on the message boxes, close the form and return to your programming environment
  12. Change the design of the form as follows:
     
    Car Dealer
    Control Text Name Anchor
    Button Present Cars btnPresentCars  
    Label New Cars    
    DataGridView   dgvNewCars Top, Bottom, Left, Right
    Label Used Vehicles   Bottom, Left, Right
    DataGridView   dgvUsedVehicles Bottom, Left, Right
  13. Double-click the Present Cars button and change the file as follows:
    private void CarDealer_Load(object sender, EventArgs e)
    {
        // InitiateCarDealer();
    }
    
    private void btnPresentCars_Click(object sender, EventArgs e)
    {
        using (SqlConnection cnnCarDealer =
        new SqlConnection("Data Source=(local);" +
                          "Database='CarDealer1';" +
                          "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("SELECT CarCode AS [Car Code], CarYear AS [Year]," +
                               "Make, Model, Price AS [Market Value] FROM NewCars;",
                               cnnCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            cnnCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvNewCars.DataSource = bsCarDealer;
        }
    
        using (SqlConnection cnnCarDealer =
                new SqlConnection("Data Source=(local);" +
                                  "Database='CarDealer1';" +
                                  "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("SELECT VehicleNumber AS [Vehicle #], YearManufactured AS [Year]," +
                               "       Make, Model, Mileage, OriginalPrice AS [Original Value]," +
                               "       CurrentValue AS [Market Value] FROM UsedVehicles;",
                               cnnCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            cnnCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvUsedVehicles.DataSource = bsCarDealer;
        }
    }
  14. Press F5 to see the result
  15. Click Show
     
    Car Dealer
  16. Close the form and return to your programming environment

Merging the Records

Record merging consists of inserting the records of one table, referred to as the source, into another table, referred to as the target. When performing this operation, you will have the option to:

  • Insert all records from the source to the target
  • Update the records that meet a criterion
  • Delete some records based on a condition

 The primary formula to merge two tables is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField

WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET
    THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE
    THEN Not Matched By Source Operation(s)

You start with the MERGE operator followed by the table to which the records will be added. You continue with the USING operator followed by the table from which the records will be retrieved. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers

You must specify the condition by which the records must correspond. To merge the records, the tables must have a common column. The columns don't have to have the same name but they should be of the same type (and size). To provide this information, type ON followed by the condition. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON Workers.ContractorCode = Teachers.EmployeeNumber

To make the statement easier to read, you can include it in parentheses. After specifying the tables and the records corresponding conditions, you must indicate what to do if/when a record from the source table meets a record from the target table. These conditions are set in the last part of the statement.

Consider the following tables:

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 MergingRecords
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            SqlCommand cmdExercise = null;
            SqlConnection cntExercise = null;
            SqlDataAdapter sdaExercise = null;

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                                   "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand(
                    "CREATE TABLE Contractors(" +
                    "ContractorCode nchar(10), FName nvarchar(20)," +
                    "LName nvarchar(20), Wage decimal(6, 2));" +
                    "CREATE TABLE Employees(EmployeeNumber nchar(10)," +
                    "DateHired date, FirstName nvarchar(20), LastName nvarchar(20)," +
                    "HourlySalary money, EmploymentStatus nvarchar(20) null);" +
                    "INSERT INTO Contractors VALUES(N'350809', N'Mary', " +
                    "N'Shamberg', 14.20), (N'286606', N'Chryssa', N'Lurie', " +
                    "20.26), (N'415905', N'Ralph', N'Sunny', 15.55);" +
                    "INSERT INTO Employees VALUES(N'286018', N'20020426', " +
                    "N'Julie', N'Chance', 12.84, N'Full Time')," +
                    "(N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, " +
                    "N'Part Time'), (N'922620', N'20100815', N'Ann', N'Keans', " +
                    "20.52, N'Full Time'), (N'415905', N'20061222', N'Godwin', " +
                    "N'Harrison', 18.75, N'Full Time'), (N'682470', N'20080430', " +
                    "N'Timothy', N'Journ', 21.05, NULL);",
                            cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();

                MessageBox.Show("The Contractors and Employees tables have been created.",
                                "Exercise",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("ContractorsSet");

                sdaExercise.Fill(dsExercise);
                dgvContractors.DataSource = dsExercise.Tables[0];
            }

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("EmployeesSet");

                sdaExercise.Fill(dsExercise);
                dgvEmployees.DataSource = dsExercise.Tables[0];
            }
        }
    }
}

This would produce:

Merging Records

When merging records, the first conditional operation is to decide what to do if two records match. To start, you would add a WHEN MATCHED statement:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED

If two records of the tables match, one thing you can do is to delete the matching record. To do this, after the WHEN MATCHED expression, add a THEN DELETE statement. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    SqlCommand cmdExercise = null;
    SqlConnection cntExercise = null;
    SqlDataAdapter sdaExercise = null;

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                           "Database='Exercise1';" +
                                           "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand(
            "MERGE Contractors AS Workers " +
            "USING Employees AS Teachers " +
            "ON (Workers.ContractorCode = Teachers.EmployeeNumber) " +
            "WHEN MATCHED " +
            "THEN DELETE;",
                    cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();

        MessageBox.Show("The merge has been performed.",
                        "Exercise",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                       "Database='Exercise1';" +
                                       "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();
        sdaExercise = new SqlDataAdapter(cmdExercise);
        DataSet dsExercise = new DataSet("ContractorsSet");

        sdaExercise.Fill(dsExercise);
        dgvContractors.DataSource = dsExercise.Tables[0];
    }

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                       "Database='Exercise1';" +
                                       "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();
        sdaExercise = new SqlDataAdapter(cmdExercise);
        DataSet dsExercise = new DataSet("EmployeesSet");

        sdaExercise.Fill(dsExercise);
        dgvEmployees.DataSource = dsExercise.Tables[0];
    }
}

Merging Records

Imagine that you want to merge the records of both tables by inserting the employees in the Contractors table. Instead of deleting matching records, another option is to update the matched records. Notice two employees who have the same employee number as two contractors:

Merging Records

In this case, a simple solution we will use is to precede those employee numbers by 00. We can write the same statement as follows:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET ContractorCode = N'00' + ContractorCode

The other operation is to specify what to do if the records don't match: Simple, we will simply merge them (the employees) with the others (the contractors). This can be done 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 MergingRecords
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnCreateTables_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" +
                                                   "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
            {
                SqlCommand cmdExercise = new SqlCommand(
                    "DROP TABLE Contractors; " +
                    "DROP TABLE Employees; " +
                    "CREATE TABLE Contractors(" +
                    "ContractorCode nchar(10), FName nvarchar(20)," +
                    "LName nvarchar(20), Wage decimal(6, 2));" +
                    "CREATE TABLE Employees(EmployeeNumber nchar(10)," +
                    "DateHired date, FirstName nvarchar(20), LastName nvarchar(20)," +
                    "HourlySalary money, EmploymentStatus nvarchar(20) null);" +
                    "INSERT INTO Contractors VALUES(N'350809', N'Mary', " +
                    "N'Shamberg', 14.20), (N'286606', N'Chryssa', N'Lurie', " +
                    "20.26), (N'415905', N'Ralph', N'Sunny', 15.55);" +
                    "INSERT INTO Employees VALUES(N'286018', N'20020426', " +
                    "N'Julie', N'Chance', 12.84, N'Full Time')," +
                    "(N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, " +
                    "N'Part Time'), (N'922620', N'20100815', N'Ann', N'Keans', " +
                    "20.52, N'Full Time'), (N'415905', N'20061222', N'Godwin', " +
                    "N'Harrison', 18.75, N'Full Time'), (N'682470', N'20080430', " +
                    "N'Timothy', N'Journ', 21.05, NULL);",
                            cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();

                MessageBox.Show("The Contractors and Employees tables have been created.",
                                "Exercise",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void btnSelectRecords_Click(object sender, EventArgs e)
        {
            SqlCommand cmdExercise = null;
            SqlConnection cntExercise = null;
            SqlDataAdapter sdaExercise = null;

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                                       "Database='Exercise1';" +
                                                       "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("ContractorsSet");

                sdaExercise.Fill(dsExercise);
                dgvContractors.DataSource = dsExercise.Tables[0];
            }

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("EmployeesSet");

                sdaExercise.Fill(dsExercise);
                dgvEmployees.DataSource = dsExercise.Tables[0];
            }
        }

        private void btnMergeRecords_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" +
                                                   "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
            {
                SqlCommand cmdExercise = new SqlCommand(
                    "MERGE Contractors AS Workers " +
                    "USING Employees AS Teachers " +
                    "ON (Workers.ContractorCode = Teachers.EmployeeNumber) " +
                    "WHEN MATCHED " +
                    "    THEN UPDATE SET ContractorCode = N'00' + ContractorCode " +
                    "WHEN NOT MATCHED " +
                    "    THEN INSERT(ContractorCode, FName, LName, Wage) " +
                    "    VALUES(EmployeeNumber, FirstName, LastName, HourlySalary);",
                            cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();

                MessageBox.Show("The merge has been performed.",
                                "Exercise",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

Merging Records

We can them see the contents of both tables again to see what records each contains now:

Merging Records

Of course, in the same way, you can merge the records of any table to those of the other table, as long as you follow the rules. For example, based on the above tables, if you want to merge the contractors with the employees, you can specify the Employees table as the target, the Contractors table as source, and their numbers as the common column to match. Then, when the numbers match, you can specify what to do. For us, once more we can simply ask the database engine to start the number with 00. For the records that don't match, we can simply add the records from the source to the targe. Here is an example:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

When you write WHEN NOT MATCHED, it is assumed that you want the cases where a record of the target matches a record of the source. As a result, you can also write the expression as WHEN NOT MATCHED BY TARGET:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

To specify what to do if a record of the source matches one from the target, add a WHEN NOT MATCHED BY SOURCE statement. In this case, you must use either a DELETE or an UPDATE statment.

Practical LearningPractical Learning: Merging the Records

  • Delete the whole code in the Query window and replace it with:
    USE CarDealer;
    GO
    
    MERGE UsedVehicles AS Target
    USING NewCars AS Source
    ON (Target.VehicleNumber = Source.CarCode)
    WHEN NOT MATCHED BY Target
        THEN INSERT(VehicleNumber, YearManufactured,
    	        Make, Model, OriginalPrice)
    	 VALUES(CarCode, CarYear, Make, Model, Price)
    WHEN MATCHED
        THEN UPDATE SET Target.YearManufactured = Source.CarYear,
    		    Target.Make = Source.Make,
    		    Target.Model = Source.Model,
    		    Target.OriginalPrice = Source.Price
    GO

Outputting the Results of a Merge

If you do a merge using the above formula, after the merge has been performed, you would not know the result9s) unless you run a new query on the target table. Fortunately, you can ask the database engine to immediately display a summary of what happened. To do this, after the last THEN statement, create an OUTPUT expression. The formula to follow is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField
WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET Not Matched By Target Options
	THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE Not Matched By Source Options
	THEN Not Matched By Source Operation(s)
OUTPUT $action, DELETED | INSERTED | from_table_name.*

To get a summary of the merging operation(s), if you are performing only one type of operation, type OUTPUT, followed by either inserted.* or deleted.*. If you are performing different types of operations, type OUTPUT, followed by $action, followed by either inserted.* or deleted.* or both.

Practical LearningPractical Learning: Outputting the Results of a Merge

  1. On the form, click the top data grid view
  2. In the Properties window, click (Name) and type dgvCars
  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 btnPresentCars_Click(object sender, EventArgs e)
    {
        using (SqlConnection conCarDealer =
    	new SqlConnection("Data Source=(local);" +
                              "Database='CarDealer1';" +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("MERGE UsedVehicles AS Target " +
                               "USING NewCars AS Source " +
                               "ON (Target.VehicleNumber = Source.CarCode)" +
                               "WHEN NOT MATCHED BY Target " +
                               "    THEN INSERT(VehicleNumber, YearManufactured," +
                               "    	    Make, Model, OriginalPrice)" +
                               "         VALUES(CarCode, CarYear, Make, Model, Price)" +
                               "WHEN MATCHED " +
                               "    THEN UPDATE SET Target.YearManufactured = Source.CarYear," +
                               "        		Target.Make = Source.Make," +
                               "        		Target.Model = Source.Model," +
                               "        		Target.OriginalPrice = Source.Price " +
                               "OUTPUT $action, inserted.*, deleted.*;",
                               conCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            conCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvCars.DataSource = bsCarDealer;
        }
    }
  5. Press F5 to execute
  6. Click Present Cars
    Car Dealer
  7. Close the form and return to your programming environment
  8. Change the design of the form as follows:
     
    Car Dealer
    Control Text Name Anchor
    Button Present Cars btnPresentCars  
    Label Cars    
    DataGridView   dgvCars Top, Left, Right
    Label Used Vehicles   Bottom, Left, Right
    DataGridView   dgvUsedVehicles Top, Bottom, Left, Right
  9. Double-click the Present Cars button
  10. To see the results on the target table, change the code as follows:
    private void btnPresentCars_Click(object sender, EventArgs e)
    {
        using (SqlConnection conCarDealer =
        new SqlConnection("Data Source=(local);" +
                              "Database='CarDealer1';" +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("MERGE UsedVehicles AS Target " +
                               "USING NewCars AS Source " +
                               "ON (Target.VehicleNumber = Source.CarCode)" +
                               "WHEN NOT MATCHED BY Target " +
                               "    THEN INSERT(VehicleNumber, YearManufactured," +
                               "    	    Make, Model, OriginalPrice)" +
                               "         VALUES(CarCode, CarYear, Make, Model, Price)" +
                               "WHEN MATCHED " +
                               "    THEN UPDATE SET Target.YearManufactured = Source.CarYear," +
                               "        		Target.Make = Source.Make," +
                               "        		Target.Model = Source.Model," +
                               "        		Target.OriginalPrice = Source.Price " +
                               "OUTPUT $action, inserted.*, deleted.*;",
                               conCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            conCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvCars.DataSource = bsCarDealer;
        }
    
        using (SqlConnection cnnCarDealer =
    	new SqlConnection("Data Source=(local);" +
                  		  "Database='CarDealer1';" +
                  		  "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("SELECT ALL * FROM UsedVehicles;",
                               cnnCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            cnnCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvUsedVehicles.DataSource = bsCarDealer;
        }
    }
  11. Press F5 to execute
  12. Click the Present Cars button
    Car Dealer
  13. Close the form and return to your programming environment
 

Home Copyright © 2010-2011 C#Key