Resources » Code Snippets » C# Syntax

Use of SqlCommandBuilder


Posted Date: 02-Jun-2008  Last Updated:   Category: C# Syntax    
Author: Member Level: Gold    Points: 10


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


Did you like this resource? Share it with your friends and show your love!

Responses to "Use of SqlCommandBuilder"
Author: Kapil Dhawan    18 Jun 2008Member 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: Gopi     13 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 Singh     30 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: selvam     16 Jun 2012
Hi
its very very nice help us for interview



Feedbacks      

Post Comment:




  • 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India