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.

More ADO.NET Articles
More By David Waddleton

 

developerWorks - FREE Tools!

Be the first to hear about i5/OS V6R1! Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br /> FREE! Go There Now!

NEW! Develop with Java and PHP technology on AIX Version 5.3, Part 6: Building the Java business application Set up a PHP Web interface for the Java(TM) business application using a database created in earlier in this series. The PHP Web interface collects information from users and sends the session data to the Java business application for processing and for a response. FREE! Go There Now!

NEW! Did you say mainframe? e-kit Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available. FREE! Go There Now!

NEW! Rational Talks to You:Per Kroll on Rational Method Composer Plug-in customization Join this Rational Talks to You teleconference on December 11 at 1:00 pm ET to get tips on building your own plugins with Rational Method Composer. Get your questions answered! FREE! Go There Now!

NEW! Rational Talks to You: Grady Booch on Architecture Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered! FREE! Go There Now!

NEW! Successful Change and Release Management for .NET Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months. FREE! Go There Now!

NEW! Trial download: IBM Rational Manual Tester V7.0.1 Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually. FREE! Go There Now!

NEW! Try the IBM SOA Sandbox for Process Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly. FREE! Go There Now!

NEW! Webcast: Eclipse: Empowering the universal platform The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now. FREE! Go There Now!

All FREE IBM® developerWorks Tools!