Posted on 12/26/2020 11:47:18 AM by Admin

SqlCommandBuilder in Ado.Net

In order to understand SqlCommandBuilder , observe the figure shown below.

dataset and dataadapter
DataSet and DataAdapter

From the above figure, You can notice , three changes has been made to dataset since the last time it was loaded
    1. the salary column of First row has been Modified.
    2. the third row has been deleted and
    3. A new row has been added to the dataset

Now , if we want to update database based on these changes then , we will have to provide Insert,Update and Delete T-Sql query to the DataAdapter, Because The SqlDataAdapter does not automatically generate the Transact-SQL statements required to reconcile changes made to a DataSet with the associated instance of SQL Server. If we don't want to write all transact_SQL statements by our self ,then we use SqlCommandBuilder object.

SqlCommandBuilder automatically generates INSERT, UPDATE and DELETE sql statements based on the SELECT statement for a single table ,meaning if you set the SelectCommand property of the SqlDataAdapter. Then, any additional Transact-SQL statements that you do not set are generated by the SqlCommandBuilder.

For the Transact-SQL statements to be generated using SqlCommandBuilder, there are 2 steps
Step 1. Set the "SelectCommand" property of the SqlDataAdapter object


    SqlDataAdapter dataAdapter = new SqlDataAdapter();
    dataAdapter.SelectCommand = new SqlCommand("SELECT_Query", con);


Step 2. Create an instance of SqlCommandBuilder class and associate the SqlDataAdapter object created above using DataAdapter property of the SqlCommandBuilder object

    SqlCommandBuilder builder = new SqlCommandBuilder();
    builder.DataAdapter = dataAdapter;

Please Note: Step 2, can also be done in single line as shown below. Here, we are passing the SqlDataAdapter instance as an argument to SqlCommandBuilder class constructor


    SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);

Example

Step 1.Retrieve information of all employee from database.
Step 2. Display it in a Gridview.
Step3. Modify some of the information of some or all employee.
Step4. Add a new record to the dataset
Step5. Delete some Rows from DataSet
Step4. View the changes in a Grid. and when you are completely satisfied with the changes then
Step 5.Commit all the changes in the database in one go.

Following Table have been used in the above example, Open SQL Server Management Studio and Run below script, it will create a database with the name Test and Two tables Employee and Department and populate them with some Test Data.


   CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
   );
  CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
    );
  SET IDENTITY_INSERT [dbo].[Department] ON
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
  SET IDENTITY_INSERT [dbo].[Department] OFF
  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
  SET IDENTITY_INSERT [dbo].[Employee] OFF

Step 1.Retrieve Data from Database and Bind the Gridview.


    private DataSet GetEmployee()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee",con);
                da.Fill(ds);
            }
            Cache["ds"] = ds;
            return ds;
        }
     private void BindGrid()
        {
            if (Cache["ds"] != null)
            {
                GridView1.DataSource = (DataSet)Cache["ds"];
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = GetEmployee();
                GridView1.DataBind();
            }       
        }

Step 2.Modify Details and Save Changes to DataSet.


    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {         
            GridView1.EditIndex = e.NewEditIndex;
            BindGrid();          
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
         {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows[e.RowIndex];
            dr[1] = e.NewValues[0].ToString();
            dr[2] = e.NewValues[1].ToString();
            dr[3] = Convert.ToInt32(e.NewValues[2]);
            Cache["ds"] = ds;
            GridView1.EditIndex = -1;
            BindGrid();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            BindGrid();
        }

Step 3.Delete Rows and save changes to DataSet.


     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows[e.RowIndex];
            ds.Tables[0].Rows.Remove(dr);
            Cache["ds"] = ds;
            BindGrid();
        }

Step 4.Insert new and save changes to database.


     protected void Button1_Click(object sender, EventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataTable dt = ds.Tables[0];
            DataRow dr = dt.NewRow();
            dr[0] = ds.Tables[0].Rows.Count + 1; // this has no impact , it is just to look dataset good
           
            dr[1] = txtName.Text;
            dr[2] = Convert.ToInt32(txtSalary.Text);
            dr[3] = Convert.ToInt32(ddlDepartment.SelectedItem.Value);
            dt.Rows.Add(dr);
            Cache["ds"] = ds;
            BindGrid();
        }


     protected void Button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                SqlCommandBuilder builder = new SqlCommandBuilder(da);  
                da.Update((DataSet)Cache["ds"]);
            }
        }

The complete source code looks like below.

The code behind (aspx.cs)

   public partial class DataSetAndDataAdapter : System.Web.UI.Page
    {
        string cs = "Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";  
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack){   
                BindGrid();
                BindDepartment();
            }   
        }

        private void BindDepartment()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Department", con);
                da.Fill(ds);
            }
            ListItem li = new ListItem() { Text = "Please Select", Value = "-1" };
            ddlDepartment.Items.Insert(0, li);
            ddlDepartment.DataSource = ds;
            ddlDepartment.DataBind();
        }
        private void BindGrid()
        {
            GridView1.DataSource = GetEmployee();
            GridView1.DataBind();
        }
       
        private DataSet GetEmployee()
        {
            DataSet ds = new DataSet();
            if (Cache["ds"] == null)
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                    da.Fill(ds,"Employee");
                    // Set ID column as the primary key
                    ds.Tables["Employee"].PrimaryKey =
                        new DataColumn[] { ds.Tables["Employee"].Columns["Id"] };
                }
                Cache["ds"] = ds;
            }
            ds = (DataSet)Cache["ds"];       
            return ds;
        }

       
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {   
            GridView1.EditIndex = e.NewEditIndex;
            BindGrid();       
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows.Find(e.Keys["Id"]);// find row using primary key
            dr[1] = e.NewValues[0].ToString();
            dr[2] = e.NewValues[1].ToString();
            dr[3] = Convert.ToInt32(e.NewValues[2]);
            Cache["ds"] = ds;
            GridView1.EditIndex = -1;
            BindGrid();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            BindGrid();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                SqlCommandBuilder builder = new SqlCommandBuilder(da);  
                da.Update((DataSet)Cache["ds"],"Employee");
                lblMessage.Text = "Database updated successfully";
                lblMessage.ForeColor = System.Drawing.Color.Green;
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataTable dt = ds.Tables[0];
            DataRow dr = dt.NewRow();
            dr[0] = ds.Tables[0].Rows.Count + 1; // this has no impact , it is just to look dataset good
            dr[1] = txtName.Text;
            dr[2] = Convert.ToInt32(txtSalary.Text);
            dr[3] = Convert.ToInt32(ddlDepartment.SelectedItem.Value);
            dt.Rows.Add(dr);
            Cache["ds"] = ds;
            BindGrid();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows[e.RowIndex];
            ds.Tables[0].Rows.Remove(dr);
            Cache["ds"] = ds;
            BindGrid();
        }
    }

Aspx page

   <form id="form1" runat="server">
    <div>   
        <table>
            <tr>
                <td>Name</td>
                <td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Salary</td>
                <td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Department</td>
                <td>
                    <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
                </td>
            </tr>
           <tr>
               <td colspan="2">
                     <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
               </td>
           </tr>
        </table>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" 
       OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" 
        OnRowDeleting="GridView1_RowDeleting">
            <Columns>
                <asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
                <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
                <asp:BoundField DataField="DepartmentId" HeaderText="DepartmentId" SortExpression="DepartmentId" />
            </Columns>
        </asp:GridView>
        <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>
        <br />
        <asp:Button ID="Button2" runat="server" Text="Updata Database" OnClick="Button2_Click" />
    </div>
    </form>

Now, Run the application and Modify some records,delete some records and add new records, on Update button click , you will be successfully able to save changes to the database at one go.

example view
example output

For troubleshooting purposes, if you want to see the autogenerated INSERT, UPDATE, and DELETE T-SQL statements, use GetInsertCommand(), GetUpdateCommand() and GetDeleteCommand().


   string insertQuery = builder.GetInsertCommand().CommandText;
   string updateQuery = builder.GetUpdateCommand().CommandText;
   string deleteQuery = builder.GetDeleteCommand().CommandText;