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!

NEW! Run your first CICS application on a PC using TXSeries for Windows Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1. FREE! Go There Now!

NEW! Understanding Web application security challenges As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security. FREE! Go There Now!

NEW! Evaluate Rational Business Developer V7.1 Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities. FREE! Go There Now!

NEW! Webcast: WebSphere Process Server WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies. FREE! Go There Now!

Role of Integrated Requirements Management in Software Delivery As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change. FREE! Go There Now!

NEW! Trial download: IBM Rational Method Composer V7.2 Get a free trial download of the latest version of IBM Rational Method Composer V7.2 which helps you deliver customized yet consistent process guidance to your project teams and IT organization, and includes the latest version of IBM Rational Unified Process (RUP), which has provided process guidance to teams since 1996. FREE! Go There Now!

NEW! Accelerating Software Innovation on i on Power Systems Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!

Check out the new Jazz space on developerWorks <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts. FREE! Go There Now!

NEW! Trial download: IBM Informix Dynamic Server Express Edition V11.0 Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data. FREE! Go There Now!

NEW! Download a free trial of Lotus Quickr 8.0 Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications. FREE! Go There Now!

All FREE IBM® developerWorks Tools!