Working With Command Builder in ASP.NET

The CommandBuilder class is a part of the .NET Framework. Its purpose is to automatically build SQL INSERT, UPDATE, and DELETE statements for a DataTable based on a SQL SELECT statement.

When creating a CommandBuilder a DataAdapter is passed as a parameter to the constructor. This links the CommandBuilder to a DataAdapter allowing it to read the SELECT command that has been associated with the DataAdapter. It will use the SELECT command to extract the schema information from the Table in the Database. Using this schema information it can automatically build INSERT, UPDATE and DELETE Command objects and the required SQL statements.

The CommandBuilder is useful when the immediate updation are not required in DB, because CommandBuilder is Disconnected Object

When we use UPDATE Method of corresponding DataAdapter then only updation are reflected in DB

In .CS FILE

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

SqlConnection con; static SqlDataAdapter da; SqlCommandBuilder cb;

static DataSet ds; DataRow rec;

protected void Page_Load(object sender, EventArgs e)

{

if (IsPostBack ==false)

{

con = new SqlConnection("server=FOCALPOI-392A7D\\SQLEXPRESS;user id=raji;password=chekuri;database=raji");

da = new SqlDataAdapter("select * from Emp", con);

ds = new DataSet();

da.Fill(ds, "emp");

cb = new SqlCommandBuilder(da);

GridView1.DataSource = ds;

GridView1.DataBind();

}

}

//In ADD button Click method we add a record to DataSet(ds) Table

protected void btnadd_Click(object sender, EventArgs e)

{

rec = ds.Tables[0].NewRow();

//create a new record with stucture of the DataSet Table

rec[0] = Convert.ToInt32( TextBox1.Text);

rec[1] = (TextBox2.Text);

rec[2] = Convert.ToInt32(TextBox3.Text);

rec[3] = (TextBox4.Text);

ds.Tables[0].Rows.Add(rec);

//Add a new record to DataSet(ds) Table,At this time record is added in DataSet Only Couldt not replect in DB

GridView1.DataSource = ds;

GridView1.DataBind();

}

//In Get button Click method we retrive corresponding of given empno from DataSet(ds) Table

protected void btnget_Click(object sender, EventArgs e)

{

rec = ds.Tables[0].Select("EmpNo=" + int.Parse(TextBox1.Text))[0];

TextBox2.Text = rec[1].ToString();

TextBox3.Text = rec[2].ToString();

TextBox4.Text = rec[3].ToString();

}

//Delete a record from DataSet(ds) Table by using select command

protected void btndelete_Click(object sender, EventArgs e)

{

rec = ds.Tables[0].Select("EmpNo=" + int.Parse(TextBox1.Text))[0];

// rec Stores the delete record

rec.Delete();

// delete's the record from DataSet Table

GridView1.DataSource = ds;

GridView1.DataBind();

}

//Update a record from DataSet(ds) Table by using select command

protected void btnupdate_Click(object sender, EventArgs e)

{

rec = ds.Tables[0].Select("EmpNo=" + int.Parse(TextBox1.Text))[0];

rec[1] = (TextBox2.Text);

rec[2] = Convert.ToInt32(TextBox3.Text);

rec[3] = (TextBox4.Text);

GridView1.DataSource = ds;

GridView1.DataBind();

}

protected void btnclear_Click(object sender, EventArgs e)

{

TextBox1.Text = "";

TextBox2.Text = "";

TextBox3.Text = "";

TextBox4.Text = "";

}

//Update in DB

protected void btnupdatedb_Click(object sender, EventArgs e)

{

da.Update(ds,"emp");

}

}

DESIGN