Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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;

14 comments:

  1. Sir what is that ? "Students"
    line No 33: da.Fill(ds, "Students");
    where you define Students and what is that? i've one error
    Could not find stored procedure 'SQLQuery'.
    please Help me

    ReplyDelete
    Replies
    1. students is the table name

      Delete
    2. The data that comes into the dataset is the table and he named that table to Students.

      Delete
  2. student is the name of dataset and if we dont specify the name it will take table0 by defaull....da(data adapter instance will fill) the ds(dataset) student

    ReplyDelete
  3. when i pas dataAdapter to sqlcommadbuilder constructor it gives error
    Error 1 'SqlCommandBuilder' does not contain a constructor that takes 1 arguments

    ReplyDelete
    Replies
    1. check whether ur class is same as SqlCommandBuilder .If so change ur class name to something different and then use SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);

      As u might have given same class name .Its creating an issue.

      Delete
  4. If the select query is a store procedure, then how do i store it in ViewState?

    ReplyDelete
    Replies
    1. Select query here is not a stored procedure. To be it so it would have called from command Class object.

      Delete
  5. Sir i get this error when i implement the code :
    "Update requires a valid UpdateCommand when passed DataRow collection with modified rows".
    please reply soon

    ReplyDelete
  6. when i pass dataAdapter to sqlcommadbuilder constructor it gives error
    Error 1 'SqlCommandBuilder' does not contain a constructor that takes 1 arguments

    ReplyDelete
  7. Venket sir, How can i insert a new record using this sqlcommandbuilder, if i give new record and give update, it says, "ExecuteReader: CommandText property has not been initialized". i could not insert a new record. pls do one video for insert data using sqlcommandbuilder.

    ReplyDelete
  8. SqlDataAdapter dataAdapter = new SqlDataAdapter();

    Sir, in the above liine IDE0017 Error is showing, please let me know how to get rid of it.

    message is object initialization can be simplified.

    ReplyDelete
  9. int rowsUpdated=da.Update(ds,"Students");
    is not working on my code

    ReplyDelete
  10. An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code
    Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    ReplyDelete

It would be great if you can help share these free resources