Home arrow ADO.NET arrow Automatic Generation of Single Table SQL Statements in .NET
ADO.NET

Automatic Generation of Single Table SQL Statements in .NET


In this article David shows us how to automatically generate Insert, Update, and Delete SQL statements using the SqlCommandBuilder object.

Author Info:
By: David Waddleton
Rating: 4 stars4 stars4 stars4 stars4 stars / 24
January 27, 2003

print this article
SEARCH DEVARTICLES

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.
  1. The SelectCommand of the SqlDataAdapter must be using valid
    Select single table SQL Statement. Example: “Select * from Doctors”
  2. 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”.
  1. Dim cb as SqlCommandBuilder(adapter)
  2. 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:

GetInsertCommand

INSERT INTO Doctors( LastName , FirstName , MiddleInitial , Address , City , State , Zip ,
LicenseNo ) VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 )


GetUpdateCommand

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


GetDeleteCommand

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.

Example:

SqlCommandBuilder.DeriveParameters(cmd);

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.

Complete Sample:

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.

All ADO.NET Tutorials
More By David Waddleton


blog comments powered by Disqus
ADO.NET ARTICLES

- Datasets in Microsoft.Net
- Latest Developments in the .Net World
- Introduction to .NET
- Automatic Generation of Single Table SQL Sta...
- Data Access in .NET using C#: Part 1
- All You Need To Know About ADO.NET: Part 2/2
- All You Need To Know About ADO.NET: Part 1/2
- Easing Transition From ASP and ADO to ASP.NE...
- A Practical Comparison of ADO and ADO.NET

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials