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!

IBM DB2 Deep Compression ROI Tool The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times. FREE! Go There Now!

NEW! Calling all CC Power Users – and those that would like to be! Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development FREE! Go There Now!

NEW! "ebook: Exploring IBM SOA Technology & Practice Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success. FREE! Go There Now!

NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo. FREE! Go There Now!

NEW! Hello World: Monitor a simple business process using WebSphere Business Monitor V6.0.2 This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product. FREE! Go There Now!

NEW! IBM Enterprise Modernization Sandbox for System z: Architecture Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server. FREE! Go There Now!

NEW! Maintaining QoS and Process Integrity in an SOA Environment This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”. FREE! Go There Now!

NEW! Project and Portfolio Management Executive Resource Kit Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!

NEW! Rational Asset Manager eKit Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions. FREE! Go There Now!

NEW! Trial download: IBM Lotus Forms V3.0 Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format. FREE! Go There Now!

All FREE IBM® developerWorks Tools!