Part 13 - What is SqlCommandBuilder

Suggested Videos
Part 10 - SqlDataAdapter in ADO.NET
Part 11 - Dataset in asp.net
Part 12 - Caching dataset in asp.net



SqlCommandBuilder automatically generates INSERT, UPDATE and DELETE sql statements based on the SELECT statement for a single table.

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

Sql script for the table used in this demo. 
Create Table tblStudents
(
ID int identity primary key,
Name nvarchar(50),
Gender nvarchar(20),
TotalMarks int
)

Insert into tblStudents values('Mark Hastings','Male',900)
Insert into tblStudents values('Pam Nicholas','Female',760)
Insert into tblStudents values('John Stenson','Male',980)
Insert into tblStudents values('Ram Gerald','Male',990)
Insert into tblStudents values('Ron Simpson','Male',440)
Insert into tblStudents values('Able Wicht','Male',320)
Insert into tblStudents values('Steve Thompson','Male',983)
Insert into tblStudents values('James Bynes','Male',720)
Insert into tblStudents values('Mary Ward','Female',870)
Insert into tblStudents values('Nick Niron','Male',680)

ASPX Code:
<div style="font-family: Arial">
<table border="1">
    <tr>
        <td>
            Student ID
        </td>
        <td>
            <asp:TextBox ID="txtStudentID" runat="server"></asp:TextBox>
            <asp:Button ID="btnGetStudent" runat="server" Text="Load" 
                OnClick="btnGetStudent_Click" />
        </td>
    </tr>
    <tr>
        <td>
            Name
        </td>
        <td>
            <asp:TextBox ID="txtStudentName" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Gender
        </td>
        <td>
            <asp:DropDownList ID="ddlGender" runat="server">
                <asp:ListItem Text="Select Gender" Value="-1"></asp:ListItem>
                <asp:ListItem Text="Male" Value="Male"></asp:ListItem>
                <asp:ListItem Text="Female" Value="Female"></asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            Total Marks
        </td>
        <td>
            <asp:TextBox ID="txtTotalMarks" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button ID="btnUpdate" runat="server" Text="Update" 
                OnClick="btnUpdate_Click" />
            <asp:Label ID="lblStatus" runat="server" Font-Bold="true">
            </asp:Label>
        </td>
    </tr>
</table>
</div>

ASPX.CS Code:
public partial class WebForm1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void btnGetStudent_Click(object sender, EventArgs e)
    {
        string connectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);
        string selectQuery = "Select * from tblStudents where ID = " +
            txtStudentID.Text;
        SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);

        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet, "Students");

        // Store DataSet and the select query in ViewState, so they can be used
        // later to generate the T-SQL commands using SqlCommandBuilder class
        ViewState["DATASET"] = dataSet;
        ViewState["SELECT_QUERY"] = selectQuery;

        if (dataSet.Tables["Students"].Rows.Count > 0)
        {
            DataRow dataRow = dataSet.Tables["Students"].Rows[0];
            txtStudentName.Text = dataRow["Name"].ToString();
            txtTotalMarks.Text = dataRow["TotalMarks"].ToString();
            ddlGender.SelectedValue = dataRow["Gender"].ToString();
            lblStatus.Text = "";
        }
        else
        {
            lblStatus.ForeColor = System.Drawing.Color.Red;
            lblStatus.Text = "No record with ID = " + txtStudentID.Text;
        }
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string connectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);

        SqlDataAdapter dataAdapter = new SqlDataAdapter();
        // Retrieve the Select query from ViewState and use it to build
        // SqlCommand command object, which will then be set as the 
        // SelectCommand of the SqlDataAdapter object
        dataAdapter.SelectCommand = 
            new SqlCommand((string)ViewState["SELECT_QUERY"], con);

        // Associate SqlDataAdapter object with SqlCommandBuilder. At this point
        // SqlCommandBuilder should generate T-SQL statements automatically
        SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);

        DataSet ds = (DataSet)ViewState["DATASET"];
        DataRow dr = ds.Tables["Students"].Rows[0];
        dr["Name"] = txtStudentName.Text;
        dr["Gender"] = ddlGender.SelectedValue;
        dr["TotalMarks"] = txtTotalMarks.Text;
        dr["Id"] = txtStudentID.Text;

        int rowsUpdated = dataAdapter.Update(ds, "Students");
        if (rowsUpdated == 0)
        {
            lblStatus.ForeColor = System.Drawing.Color.Red;
            lblStatus.Text = "No rows updated";
        }
        else
        {
            lblStatus.ForeColor = System.Drawing.Color.Green;
            lblStatus.Text = rowsUpdated.ToString() + " row(s) updated";
        }
    }
}

Please make sure to include the following using declarations.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;