use of sqlcommandBuilder and sqlcommand

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]);

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:

1. Database connection. (see SqlCommand.Connection property)
   
2. the command string like "select * from .... " , see SqlCommand.CommandText property)
   
3. The command type (stored procedures,sql query .... ), see SqlCommand.CommandType property.
   
4. The Command parameters , see SqlCommand.Parameters property.