In this article David shows us how to automatically generate Insert, Update, and Delete SQL statements using the SqlCommandBuilder object.The generation of single table SQL statements can be accomplished by using the System.Data.SqlClient.SqlCommandBuilder object. This object uses the Select Command object’s Command Text to generate the appropriate SQL Statements that are missing from the SqlDataAdapter.
There are a couple of requirements for using this object.
The SelectCommand of the SqlDataAdapter must be using valid Select single table SQL Statement. Example: “Select * from Doctors”
The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated.
After Creating a SqlDataAdapter and setting its SelectCommand the SqlCommandBuilder can be created two ways. We will assume for the examples that the name of the SqlDataAdapter object is “adapter”.
Dim cb as SqlCommandBuilder(adapter)
Dim cb as New SqlCommandBuilder() cb.DataAdapter = adapter
To view the resulting SQL statements from the SqlDataAdapter, there are three methods that can be used; GetUpdateCommand, GetDeleteCommand and GetInsertCommand.
These methods return SqlCommand Objects. From these SqlCommand Objects you can examine the CommandText to view the resulting SQL.
Using the “Select * from Doctors” Select SQL Statement here is an example of what is returned:
INSERT INTO Doctors( LastName , FirstName , MiddleInitial , Address , City , State , Zip , LicenseNo ) VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 )
UPDATE Doctors SET LastName = @p1 , FirstName = @p2 , MiddleInitial = @p3 , Address = @p4 , City = @p5 , State = @p6 , Zip = @p7 , LicenseNo = @p8 WHERE ( (ID = @p9) AND ((LastName IS NULL AND @p10 IS NULL) OR (LastName = @p11)) AND ((FirstName IS NULL AND @p12 IS NULL) OR (FirstName = @p13)) AND ((MiddleInitial IS NULL AND @p14 IS NULL) OR (MiddleInitial = @p15)) AND ((Address IS NULL AND @p16 IS NULL) OR (Address = @p17)) AND ((City IS NULL AND @p18 IS NULL) OR (City = @p19)) AND ((State IS NULL AND @p20 IS NULL) OR (State = @p21)) AND ((Zip IS NULL AND @p22 IS NULL) OR (Zip = @p23)) AND ((LicenseNo IS NULL AND @p24 IS NULL) OR (LicenseNo = @p25)) )
DELETE FROM Doctors WHERE ( (ID = @p1) AND ((LastName IS NULL AND @p2 IS NULL) OR (LastName = @p3)) AND ((FirstName IS NULL AND @p4 IS NULL) OR (FirstName = @p5)) AND ((MiddleInitial IS NULL AND @p6 IS NULL) OR (MiddleInitial = @p7)) AND ((Address IS NULL AND @p8 IS NULL) OR (Address = @p9)) AND ((City IS NULL AND @p10 IS NULL) OR (City = @p11)) AND ((State IS NULL AND @p12 IS NULL) OR (State = @p13)) AND ((Zip IS NULL AND @p14 IS NULL) OR (Zip = @p15)) AND ((LicenseNo IS NULL AND @p16 IS NULL) OR (LicenseNo = @p17)) )
This information is automatically populated into the appropriate SqlDataAdapter statements and creates the appropriate SqlParameter statements necessary for the SqlDataAdapter.Update.
If the SelectCommand of the SqlDataAdapter is changed, then the SqlCommandBuilder.RefreshSchema() must be called to regenerate the appropriate Update, Delete or Insert SQL Statements.
SqlCommandBuilder.Dispose() will remove the SqlDataAdapter reference and generated SQL statements.
Another useful method of the SqlCommandBuilder is DeriveParameters. This is a shared/static method that can be used to generate SqlParameters for a SqlCommand that is passed into the constructor.
NOTE: This will only work with Stored Procedures.
After examining the following sample you will be able dynamically update DataSets within your code that will allow you to only know the name of the table that you need to access and Connection Information for the SqlConnection object.
Have fun and experiment.
Dim cb As SqlCommandBuilder Dim ds As New DataSet() Dim adapter As SqlDataAdapter
adapter = New SqlDataAdapter(TextBox1.Text,AppSettings("ConnectionString")) cb = New SqlCommandBuilder(adapter) adapter.Fill(ds) DataGrid1.DataSource = ds ‘Make some changes to the DataSet txtUpdate.Text = cb.GetUpdateCommand().CommandText txtDelete.Text = cb.GetDeleteCommand().CommandText txtInsert.Text = cb.GetInsertCommand().CommandText
‘After making changes update adapter.Update(ds)
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.