Difference between SqlCommand and SqlCommandBuilder
-
Hi everybody, Can anybody tell me the exact difference between SqlCommand and SqlCommandBuilder. Thanking you.
param
-
Hi everybody, Can anybody tell me the exact difference between SqlCommand and SqlCommandBuilder. Thanking you.
param
A Command object is used to execute scalar or non query commands to a database. You would set a command objects CommandText property to and sql statement that you want to run and then use it's ExecuteScalar or ExecuteNonQuery method to run it. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "UPDATE myTable SET col1='my value'"
cmd.ExecuteNonQuery()A CommandBuilder object is used to automatically create Update, Delete, and Insert SQL statements for you, based on a Select statament that you supply. You would declare a DataAdapter object, set it's SelectCommand.CommandText property to your Select SQL statement. Then when you declare a CommandBuilder object, you include the dataadapter in the CommandBuilder's constructor parameter and it will automatically create the other statements for you when you run a DataAdapter. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "SELECT * FROM myTable"
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim cb As New SqlClient.SqlCommandBuilder(da)
da.Update(myDataTable)If you hadn't of used a CommandBuilder, the program would have blown up on the da.Update(myDataTable) statement, because the adapter wouldn't have known how to insert new rows that had been added to the table, or delete rows that had been removed, etc.
-
A Command object is used to execute scalar or non query commands to a database. You would set a command objects CommandText property to and sql statement that you want to run and then use it's ExecuteScalar or ExecuteNonQuery method to run it. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "UPDATE myTable SET col1='my value'"
cmd.ExecuteNonQuery()A CommandBuilder object is used to automatically create Update, Delete, and Insert SQL statements for you, based on a Select statament that you supply. You would declare a DataAdapter object, set it's SelectCommand.CommandText property to your Select SQL statement. Then when you declare a CommandBuilder object, you include the dataadapter in the CommandBuilder's constructor parameter and it will automatically create the other statements for you when you run a DataAdapter. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "SELECT * FROM myTable"
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim cb As New SqlClient.SqlCommandBuilder(da)
da.Update(myDataTable)If you hadn't of used a CommandBuilder, the program would have blown up on the da.Update(myDataTable) statement, because the adapter wouldn't have known how to insert new rows that had been added to the table, or delete rows that had been removed, etc.
An SQLCommand object can also be used to return query results. Under the covers, this is what happens in the DataAdapter - the command buider builds commandtext for the other sqlcommand instances used by the adapter, using the commandtext provided for the query or read command. SqlCommand instances can also be used to return query results as a datreader, which is like a fast forward-only cursor.