use of sqlcommandBuilder and sqlcommand

Question

my question is that when we will use sqlcommandBuilder and when we will use sqlcommand ...please me

Santosh Kumar · Answer

Normal 0 false false false EN-US X-NONE X-NONE SQLCommand The SQLCommand Object is used to executes SQL statements and Stored Procedures against the data source. It execute all kind of SQL queries like Insert, update etc. e.g. SqlCommand cmd = new SqlCommand("your sql statements", Connection); cmd.ExecuteNonQuery(); SqlCommandBuilder The SqlCommandBuilder can be used to build and execute SQL queries based on the select command that you will supply. It provides the feature of reflecting the changes made to a DataSet or an instance of the SQL server data. The CommandBuilder opens the Connection associated with the DataAdapter Object and makes a round trip to the server each and every time and it's asked to construct the action queries. The SqlCommandBuilder object acts as a listener for RowUpdating events, whenever the DataAdapter property is set. You can create a SqlCommandBuilder object to automatically generate SQL statements for single table updates if you set the SelectCommand property of the SqlDataAdapter. e.g. SqlDataAdapter adapter = new SqlDataAdapter("your sql statement", connection); SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter); adapter.Fill(ds); //Here you can do any modification to the selected data ds.Tables[0].Rows[i].ItemArray[2] = Convert.ToInt32 (ds.Tables[0].Rows[i].ItemArray[2]) + 100; //Here save the data to the datasource adapter.Update(ds.Tables[0]); /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0in;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;}

Sanjeeb Lenka · Answer

a) SQLCommand is used to execute all kind of SQL queries like DML(Insert update Delete) & DDL like(Create table drop table etc) b)SQLCommandBuilder object is used to build & execute SQL (DML) queries like select insert update & delete. The SqlCommand encapsulates all the required informations to execute the command which includes: Database connection. (see SqlCommand.Connection property) the command string like "select * from .... " , see SqlCommand.CommandText property) The command type (stored procedures,sql query .... ), see SqlCommand.CommandType property. The Command parameters , see SqlCommand.Parameters property. The SqlCommandBuilder can be used to generate update,insert and delete commands based on the select command.so basically the class will have an internal logic to generate those type of commands for you.

Veena Sarda · Answer

For DML (Select, Insert, Update, Delete) you can use both. For DDL (Create, Drop, Truncate) you can use SQLCommand. Transact SQL Commands are automatically generated in SQLCommandBuilder - you need not write the TSQL manually. A good example at http://www.dotnetperls.com/sqlcommandbuilder