Use of SqlCommandBuilder


With the use of sqlcommandbuilder the following code helps to insert the record into gridview and once update method is called then only it insert record into table..

With the use of sqlcommandbuilder the following code helps to insert the record into gridview and once update method is called then only it insert record into table.. here you can extend this idea to add record more record in the grid and when need you can also update it in sql table


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public partial class commandBuilder : System.Web.UI.Page
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds;
DataTable dt;
DataRow dr;
SqlCommandBuilder build;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection("Server=D-1028;Database=kumar;uid=sa;pwd=sa123;");
adap = new SqlDataAdapter("select * from builder",con);
build = new SqlCommandBuilder(adap);
ds = new DataSet();
adap.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
dr = dt.NewRow();
dr["bid"] = TextBox1.Text;
dr["bfname"]=TextBox2.Text;
dr["blname"]=TextBox3.Text;
dt.Rows.Add(dr);
adap.SelectCommand = build.GetInsertCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();

}
protected void Button2_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
int a=Convert.ToInt32(TextBox1.Text);
//dr=dt.Rows[a-1];
//dr["bid"] = TextBox1.Text;
dr["bfname"] = TextBox2.Text;
dr["blname"] = TextBox3.Text;
//dt.Rows.
adap.UpdateCommand = build.GetUpdateCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Button3_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
//int a = Convert.ToInt32(GridView1.SelectedRow.Cells[0].Text);
int a = Convert.ToInt32(TextBox1.Text);
dt.Rows[a].Delete();//.ToString();
//dr["bfname"] = TextBox2.Text;
// dr["blname"] = TextBox3.Text;
//dt.Rows.
adap.DeleteCommand = build.GetDeleteCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}


Comments

Author: Kapil Dhawan18 Jun 2008 Member Level: Gold   Points : 2

Hello
Nice piece of code
Thanks for sharing your knowledge with us.
I hope to see more good code from your side
This code will help lots of guys
Thanks to you
Regards,
Kapil

Guest Author: Gopi13 Feb 2012

Nice and comprehensive material,
it would be better if u add the theoretical importance of command builder methods in code comment line

Guest Author: Abhinav Singh30 May 2012

i am not able to update through sql command builder.



SqlConnection con = new SqlConnection();
DataSet ds = new DataSet();
con.ConnectionString = "Initial Catalog=MyDB;Data Source=SINGHAM-PC;uid=sa;pwd=Hotmail123";
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tbl_Customer", con);

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "tbl_Customer");
// adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// adapter.AcceptChangesDuringUpdate = true;
SqlCommandBuilder cb = new SqlCommandBuilder(adapter); /*After fill create SqlCommand builder object*/


ds.Tables["tbl_Customer"].Rows[0]["sCustomerName"] = "Abhinav prtap Singh ";
ds.Tables["tbl_Customer"].Rows[0]["sAddress"] = "Panvel Sainagr ";
ds.Tables["tbl_Customer"].Rows[0]["nAge"] = 25;


ds.Tables["tbl_Customer"].AcceptChanges();



//adapter.UpdateCommand= cb.GetUpdateCommand();
adapter.Update(ds.Tables["tbl_Customer"]);

Guest Author: selvam16 Jun 2012

Hi
its very very nice help us for interview



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: