Home

ADO.NET How To: Move Records

     

Introduction

Consider the following two tables:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnMoveRecords;
    Button btnCreateTables;
    Button btnSelectRecords;
    DataGridView dgvEmployees;
    DataGridView dgvContractors;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTables = new Button();
        btnCreateTables.AutoSize = true;
        btnCreateTables.Text = "Create Tables";
        btnCreateTables.Location = new Point(12, 12);
        btnCreateTables.Click += new EventHandler(btnCreateTablesClick);

        dgvContractors = new DataGridView();
        dgvContractors.Width = 360;
        dgvContractors.Location = new Point(12, 210);

        dgvEmployees = new DataGridView();
        dgvEmployees.Width = 360;
        dgvEmployees.Location = new Point(12, 44);

        btnSelectRecords = new Button();
        btnSelectRecords.Text = "Show Employees";
        btnSelectRecords.AutoSize = true;
        btnSelectRecords.Location = new Point(110, 12);
        btnSelectRecords.Click += new EventHandler(btnSelectRecordsClick);

        btnMoveRecords = new Button();
        btnMoveRecords.AutoSize = true;
        btnMoveRecords.Location = new Point(230, 12);
        btnMoveRecords.Text = "Move Records";
        btnMoveRecords.Click += new EventHandler(btnMoveRecordsClick);

        Controls.Add(btnCreateTables);
        Size = new Size(400, 415);
        Controls.Add(dgvEmployees);
        Controls.Add(dgvContractors);
        Controls.Add(btnMoveRecords);
        Text = "Exercise";
        Controls.Add(btnSelectRecords);
    }

    void btnCreateTablesClick(object sender, EventArgs e)
    {
        SqlConnection cntExercise = null;
        SqlCommand cmdExercise = null;

        using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand(
                        "CREATE TABLE Contractors(" +
                        "ContractorCode nchar(5), Salary money," +
                        "LastName nvarchar(20), FirstName nvarchar(20));" +
                        "INSERT INTO Contractors VALUES" +
                        "(N'86824', 12.84, N'Chance', N'Julie')," +
                        "(N'84005', 9.52, N'Kaihibu', N'Ayinda')," +
                        "(N'27084', 14.26, N'Gorman', N'Alex'); " +
                        "CREATE TABLE Employees(" +
                        "FirstName nvarchar(50), LastName nvarchar(20)," +
                        "EmplNbr nchar(5), HourlySalary money);" +
                        "INSERT INTO Employees VALUES" +
                        "(N'Ann', N'Keans', N'22684', 20.52)," +
                        "(N'Godwin', N'Harrison', N'48157', 18.75)," +
                        "(N'Timothy', N'Journ', N'82476', 21.05)," +
                        "(N'Ralph', N'Sunny', N'15007', 15.55);",
                        cntExercise);

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

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

    void btnMoveRecordsClick(object sender, EventArgs e)
    {
    }

    void btnSelectRecordsClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise = 
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("SELECT ALL * FROM Contractors;",
                               cntExercise);


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

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

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


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

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

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}
 
 
 

Moving records consists of transferring them from one table, the source, to another table, the target. Neither SQL nor Transact-SQL directly supports this operation, which happens to be extremely easy. You have many options. Probably the easiest way to do this consists of copying the records from the source to the target, then deleting the same records from the source. 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
{
    Button btnMoveRecords;
    Button btnSelectRecords;
    DataGridView dgvEmployees;
    DataGridView dgvContractors;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnMoveRecords = new Button();
        btnMoveRecords.AutoSize = true;
        btnMoveRecords.Text = "Move Records";
        btnMoveRecords.Location = new Point(12, 12);
        btnMoveRecords.Click += new EventHandler(btnMoveRecordsClick);

        dgvEmployees = new DataGridView();
        dgvEmployees.Size = new Size(360, 200);
        dgvEmployees.Location = new Point(12, 44);

        dgvContractors = new DataGridView();
        dgvContractors.Size = new Size(360, 100);
        dgvContractors.Location = new Point(12, 260);

        btnSelectRecords = new Button();
        btnSelectRecords.Text = "Show Employees";
        btnSelectRecords.AutoSize = true;
        btnSelectRecords.Location = new Point(110, 12);
        btnSelectRecords.Click += new EventHandler(btnSelectRecordsClick);

        Controls.Add(btnMoveRecords);
        Size = new Size(400, 415);
        Controls.Add(dgvEmployees);
        Controls.Add(dgvContractors);
        Text = "Exercise";
        Controls.Add(btnSelectRecords);
    }

    void btnMoveRecordsClick(object sender, EventArgs e)
    {
        SqlConnection cntExercise = null;
        SqlCommand cmdExercise = null;

        using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand(
                        "INSERT INTO Employees " +
                        "SELECT FirstName, LastName, ContractorCode, Salary " +
                        "FROM Contractors " +
                        "WHERE Contractors.Salary >= 10.00; " +
                        "DELETE FROM Contractors " +
                        "WHERE Contractors.Salary >= 10.00;",
                        cntExercise);

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

 MessageBox.Show("The Contractors who make more than $10 have been made Employees.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSelectRecordsClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise = 
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("SELECT ALL * FROM Contractors;",
                               cntExercise);


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

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

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


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

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

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}
 
 
   
 

Home Copyright © 2014-2016, FunctionX