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
0 comments:
Respects for your's Questions & Opinions