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! Applying lean thinking to the governance of software development Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations. FREE! Go There Now!

NEW! Don't wait! Try the Rational Application Developer (RAD) v7.5 open beta code today Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems. FREE! Go There Now!

NEW! Harnessing the power of SQL and Java for high performance data access Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services. FREE! Go There Now!

NEW! IBM Enterprise Modernization Sandbox for System z IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects. FREE! Go There Now!

NEW! IBM Rational Systems Development e-Kit As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br /> FREE! Go There Now!

NEW! Integrating XML into Your Enterprise Using Data Federation XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats. FREE! Go There Now!

NEW! Rational Testing eKits Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing. 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! Using IBM Rational Developer for System z and IBM Rational ClearCase together to manage application development Whether you are creating new applications or modifying existing ones, managing integration of new components with traditional z/OS elements is a critical part of building and deploying modern applications. Listen to this webcast to see how IBM can help you optimize your development process using an IDE like Rational Developer for System z that integrates with management tools, such as ClearCase to manage your application development on mainframes. FREE! Go There Now!

NEW! Webcast: Extreme transaction processing with WebSphere Extended Deployment In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications. FREE! Go There Now!

All FREE IBM® developerWorks Tools!