SqlCommandBuilder class in ADO.NET.

Question

Hi friends Explain SqlCommandBuilder class in ADO.NET? What he does?

Satyapriya Nayak · Answer

Hi Akshay SqlCommandBuilder class in ADO.NET provides the feature of reflecting the changes made to a DataSet or an instance of the SQL server data. When an instance of the SqlCommandBuilder class is created it automatically generates Transact-SQL statements for the single table updates that occur. The object of the SqlCommandBuilder acts as a listener for RowUpdating events whenever the DataAdapter property is set. The SqlCommandBuilder object automatically generates the values contained within the SqlDataAdapter's InsertCommand UpdateCommand and DeleteCommand properties based on the initial SelectCommand. The advantage here is that you will not need to write SqlCommand & SqlParameter Types explicitly. Basically the command builder object builds these objects on the fly. The command builder object actually reads the metadata of the method called. After the builder object reads the underlying schema of the adapter's method it autogenerates an underlying insert update & delete command object. Example Data Insert Update Save Delete Using SqlCommandBuilder. using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace Ado_Project_Data_Insert_Update_Save_Delete_Using_Disconnected_Mode_In_CSharp { /// /// Summary description for Form1. /// public class Form1 : System.Windows.Forms.Form { SqlConnection con=new SqlConnection(workstation id=\HOME-Z8CKE1NER2\;packet size=4096;user id=sa;initial catalog=Dotn + et;persist security info=False); SqlCommand com; SqlDataAdapter sqlda; DataSet ds=new DataSet(); SqlCommandBuilder objcom; String str; int flag; DataTable dt; DataRow dr; internal System.Windows.Forms.Button btnclose; internal System.Windows.Forms.Button btnload; internal System.Windows.Forms.Button btndelete; internal System.Windows.Forms.Button btnsave; internal System.Windows.Forms.Button btnmodify; internal System.Windows.Forms.Button btnadd; internal System.Windows.Forms.Button btnlast; internal System.Windows.Forms.Button btnprevious; internal System.Windows.Forms.Button btnnext; internal System.Windows.Forms.Button btnfirst; internal System.Windows.Forms.Label Label5; internal System.Windows.Forms.Label Label4; internal System.Windows.Forms.Label Label3; internal System.Windows.Forms.Label Label2; internal System.Windows.Forms.Label Label1; internal System.Windows.Forms.TextBox TextBox5; internal System.Windows.Forms.TextBox TextBox4; internal System.Windows.Forms.TextBox TextBox3; internal System.Windows.Forms.TextBox TextBox2; internal System.Windows.Forms.TextBox TextBox1; internal System.Windows.Forms.DataGrid dataGrid1; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// /// Clean up any resources being used. /// /// protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.btnclose = new System.Windows.Forms.Button(); this.btnload = new System.Windows.Forms.Button(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.btndelete = new System.Windows.Forms.Button(); this.btnsave = new System.Windows.Forms.Button(); this.btnmodify = new System.Windows.Forms.Button(); this.btnadd = new System.Windows.Forms.Button(); this.btnlast = new System.Windows.Forms.Button(); this.btnprevious = new System.Windows.Forms.Button(); this.btnnext = new System.Windows.Forms.Button(); this.btnfirst = new System.Windows.Forms.Button(); this.Label5 = new System.Windows.Forms.Label(); this.Label4 = new System.Windows.Forms.Label(); this.Label3 = new System.Windows.Forms.Label(); this.Label2 = new System.Windows.Forms.Label(); this.Label1 = new System.Windows.Forms.Label(); this.TextBox5 = new System.Windows.Forms.TextBox(); this.TextBox4 = new System.Windows.Forms.TextBox(); this.TextBox3 = new System.Windows.Forms.TextBox(); this.TextBox2 = new System.Windows.Forms.TextBox(); this.TextBox1 = new System.Windows.Forms.TextBox(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // btnclose // this.btnclose.Location = new System.Drawing.Point(368 292); this.btnclose.Name = btnclose; this.btnclose.Size = new System.Drawing.Size(88 23); this.btnclose.TabIndex = 41; this.btnclose.Text = Form Close; this.btnclose.Click += new System.EventHandler(this.btnclose_Click); // // btnload // this.btnload.Location = new System.Drawing.Point(480 244); this.btnload.Name = btnload; this.btnload.Size = new System.Drawing.Size(96 23); this.btnload.TabIndex = 40; this.btnload.Text = Load Records; this.btnload.Click += new System.EventHandler(this.btnload_Click); // // dataGrid1 // this.dataGrid1.DataMember = ; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(304 16); this.dataGrid1.Name = dataGrid1; this.dataGrid1.Size = new System.Drawing.Size(312 160); this.dataGrid1.TabIndex = 39; // // btndelete // this.btndelete.Location = new System.Drawing.Point(280 268); this.btndelete.Name = btndelete; this.btndelete.TabIndex = 38; this.btndelete.Text = Delete; this.btndelete.Click += new System.EventHandler(this.btndelete_Click); // // btnsave // this.btnsave.Location = new System.Drawing.Point(192 268); this.btnsave.Name = btnsave; this.btnsave.TabIndex = 37; this.btnsave.Text = Save; this.btnsave.Click += new System.EventHandler(this.btnsave_Click); // // btnmodify // this.btnmodify.Location = new System.Drawing.Point(104 268); this.btnmodify.Name = btnmodify; this.btnmodify.TabIndex = 36; this.btnmodify.Text = Modify; this.btnmodify.Click += new System.EventHandler(this.btnmodify_Click); // // btnadd // this.btnadd.Location = new System.Drawing.Point(16 268); this.btnadd.Name = btnadd; this.btnadd.TabIndex = 35; this.btnadd.Text = Add; this.btnadd.Click += new System.EventHandler(this.btnadd_Click); // // btnlast // this.btnlast.Location = new System.Drawing.Point(280 220); this.btnlast.Name = btnlast; this.btnlast.TabIndex = 34; this.btnlast.Text = Last; this.btnlast.Click += new System.EventHandler(this.btnlast_Click); // // btnprevious // this.btnprevious.Location = new System.Drawing.Point(192 220); this.btnprevious.Name = btnprevious; this.btnprevious.TabIndex = 33; this.btnprevious.Text = Previous; this.btnprevious.Click += new System.EventHandler(this.btnprevious_Click); // // btnnext // this.btnnext.Location = new System.Drawing.Point(104 220); this.btnnext.Name = btnnext; this.btnnext.TabIndex = 32; this.btnnext.Text = Next; this.btnnext.Click += new System.EventHandler(this.btnnext_Click); // // btnfirst // this.btnfirst.Location = new System.Drawing.Point(16 220); this.btnfirst.Name = btnfirst; this.btnfirst.TabIndex = 31; this.btnfirst.Text = First; this.btnfirst.Click += new System.EventHandler(this.btnfirst_Click); // // Label5 // this.Label5.Location = new System.Drawing.Point(8 148); this.Label5.Name = Label5; this.Label5.TabIndex = 30; this.Label5.Text = Year; // // Label4 // this.Label4.Location = new System.Drawing.Point(8 116); this.Label4.Name = Label4; this.Label4.TabIndex = 29; this.Label4.Text = Student Address; // // Label3 // this.Label3.Location = new System.Drawing.Point(8 84); this.Label3.Name = Label3; this.Label3.TabIndex = 28; this.Label3.Text = Student Marks; // // Label2 // this.Label2.Location = new System.Drawing.Point(8 52); this.Label2.Name = Label2; this.Label2.TabIndex = 27; this.Label2.Text = Student Name; // // Label1 // this.Label1.Location = new System.Drawing.Point(8 20); this.Label1.Name = Label1; this.Label1.TabIndex = 26; this.Label1.Text = Student Id; // // TextBox5 // this.TextBox5.Location = new System.Drawing.Point(128 148); this.TextBox5.Name = TextBox5; this.TextBox5.TabIndex = 25; this.TextBox5.Text = ; // // TextBox4 // this.TextBox4.Location = new System.Drawing.Point(128 116); this.TextBox4.Name = TextBox4; this.TextBox4.TabIndex = 24; this.TextBox4.Text = ; // // TextBox3 // this.TextBox3.Location = new System.Drawing.Point(128 84); this.TextBox3.Name = TextBox3; this.TextBox3.TabIndex = 23; this.TextBox3.Text = ; // // TextBox2 // this.TextBox2.Location = new System.Drawing.Point(128 52); this.TextBox2.Name = TextBox2; this.TextBox2.TabIndex = 22; this.TextBox2.Text = ; // // TextBox1 // this.TextBox1.Location = new System.Drawing.Point(128 20); this.TextBox1.Name = TextBox1; this.TextBox1.TabIndex = 21; this.TextBox1.Text = ; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5 13); this.ClientSize = new System.Drawing.Size(664 334); this.Controls.Add(this.btnclose); this.Controls.Add(this.btnload); this.Controls.Add(this.dataGrid1); this.Controls.Add(this.btndelete); this.Controls.Add(this.btnsave); this.Controls.Add(this.btnmodify); this.Controls.Add(this.btnadd); this.Controls.Add(this.btnlast); this.Controls.Add(this.btnprevious); this.Controls.Add(this.btnnext); this.Controls.Add(this.btnfirst); this.Controls.Add(this.Label5); this.Controls.Add(this.Label4); this.Controls.Add(this.Label3); this.Controls.Add(this.Label2); this.Controls.Add(this.Label1); this.Controls.Add(this.TextBox5); this.Controls.Add(this.TextBox4); this.Controls.Add(this.TextBox3); this.Controls.Add(this.TextBox2); this.Controls.Add(this.TextBox1); this.Name = Form1; this.Text = Form1; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.Run(new Form1()); } private void Form1_Load(object sender System.EventArgs e) { str = select * from student; com = new SqlCommand(str con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); dt = ds.Tables[student]; TextBox1.DataBindings.Add (Textdtsid); TextBox2.DataBindings.Add (Textdtsname); TextBox3.DataBindings.Add (Textdtsmarks); TextBox4.DataBindings.Add (Textdtsaddress); TextBox5.DataBindings.Add (Textdtyear); dataGrid1.DataSource =ds; dataGrid1.DataMember =student; } private void btnfirst_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position=0; } private void btnnext_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position+=1; } private void btnprevious_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position-=1; } private void btnlast_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position =dt.Rows.Count-1; } private void btnadd_Click(object sender System.EventArgs e) { flag=1; TextBox1.Text = ; TextBox2.Text = ; TextBox3.Text = ; TextBox4.Text = ; TextBox5.Text = ; TextBox1.Focus(); } private void showall() { con.Open (); str = select * from student; com = new SqlCommand(str con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); con.Close(); dataGrid1.DataSource =ds; dataGrid1.DataMember =student; } private void btnmodify_Click(object sender System.EventArgs e) { flag=2; dr=dt.Rows[this.BindingContext[dt].Position]; TextBox1.Focus(); } private void btnsave_Click(object sender System.EventArgs e) { if (flag==1) { dr = dt.NewRow(); dr[sid] = TextBox1.Text; dr[sname] = TextBox2.Text; dr[smarks] = TextBox3.Text; dr[saddress] = TextBox4.Text; dr[year] = TextBox5.Text; dt.Rows.Add(dr); objcom= new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Inserted); } else if (flag==2) { dr.BeginEdit(); dr[sid] = TextBox1.Text; dr[sname] = TextBox2.Text; dr[smarks] = int.Parse(TextBox3.Text); dr[saddress] = TextBox4.Text; dr[year] = TextBox5.Text; dr.EndEdit(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Modified); } showall(); } private void btndelete_Click(object sender System.EventArgs e) { if( MessageBox.Show (do u 1 2 delete the Recordthis.Text MessageBoxButtons.YesNo  MessageBoxIcon.Question )== DialogResult.Yes ) { dr = dt.Rows[this.BindingContext[dt].Position]; dr.Delete(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Deleted); showall(); } } private void btnload_Click(object sender System.EventArgs e) { // con.Open(); // str = select * from student; // com = new SqlCommand(str con); // sqlda = new SqlDataAdapter(com); // ds = new DataSet(); // sqlda.Fill(ds student); // dataGrid1.DataSource = ds; // dataGrid1.DataMember = student; // con.Close(); } private void btnclose_Click(object sender System.EventArgs e) { // Application.Exit(); this.Close(); } } } Thanks

Akshay Teotia · Answer

nice explanation thanks.