ADO.NET CommandBuilder in VB.NET

In this article you will learn how to use CommandBuilder in ADO.NET.
  • 7866

CommandBuilder: Easing the Work of the Programmer 

Sometimes creating SQL statements could be lengthy job when dealing with many columns in a table. A CommandBuilder object reduces the burden of creating SQL statements for you. In other words, the CommandBuilder helps you to generate update, delete., and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The OleDbCommandBuilder, SqlCommonBuilder, and OdbcCommandBuilder classes represent the CommonBuilder object in the OleDb, Sql, and ODBC data providers. These classes also work in pretty similar fashion. Once you know how to use OleDbCommandBuilder, you can use SqlCommandBuilder and OdbcCommandBuilder in a similar way. I'll use OleDbCommandBuilder class in this example.

Creating a Command Builder Object

Creating a CommonedBuider object is pretty simply. You pass a DataAdapter as an argument of the CommandBuilder constructor. For example:


        ' Create a command builder object 
        Dim builder As New SqlCommandBuilder(adapter)

SqlCommandBuilder Members

The DataAdapter property of a CommonBuilder represents the DataProvider attached to a CommonBuilder object for which automatic SQL statements are generated. The GetDeleteCommon, GetUpdateCommand, and GetInsertCommand methods return the delete, update, and insert commands in the form of a Command object. The RefreshSchema method refreshes the database schema.

Using the sqlCommandBuilder 

Now you'll see how to use the SqlCommandBuilder in an application. You can use OleDbCommand Builder and ODBCCommandBuilder classes in same way. Listing 5-54 shows how to use SqlCommandBuilder. As you can see, as usual, you create a connection to the database and use it to create the adapter object. The adapter is constructed with the initial query for the Employees table as well as with the database connection.

Next you construct the CommandBuilder by passing the DataAdapter into its constructor. The act of creating the CommandBuilder automatically cause the UPDATE, INSERT, and DELETE commands to be generated for the adapter:


Dim builder As New SqlCommandBuilder(adapter)

Next, fill the DataSet using the adapter and create an instance of the Employee DataTable from the DataSet:


        ' Create a dataset object 
        Dim ds As New DataSet("Employee Set")
        adapter.Fill(ds, "Employees")

Now insert a new DataRow into the DataTable in memory and popular a row with your desired value using DataTable'sAddNew method. After that you call the DataRowCollection.Add method to add the row to the DataTable:


        ' Create a data table object and add a new row 
        Dim EmployeeTable As DataTable = ds.Tables("Employees")
        Dim row As DataRow = EmployeeTable.NewRow()
        row("firstName") = "Rodney"
        row("LastName") = "Dangerfield"
        row("Title") = "comedian"
        EmployeeTable.Rows.Add(row)

Finally you call DataAdapter's Update method to update the DataTable change to the data source:


        ' update data adapter 
        adapter.Update(ds, "Employees")

Listing 5-54 shows the full source code of how to create and use a CommandBuilder object.

Listing 5-54. Creating and using the SqlCommandBuilder class


        'Create a connection object 
        Dim ConnectionString As String = "Integrated Security = SSPI;" & "Initial Catalog = Northwind;" & "Data Source = localhost;"
        Dim conn As New SqlConnection(ConnectionString)

        ' open the connection 
        conn.Open()

        ' Create a data adapter object 
        Dim adapter As New SqlDataAdapter("SELECT * FROM Employee ORDER by EmployeeID", conn)

        ' Ceate a command builder object 
        Dim builder As New SqlCommandBuilder(adapter)

        ' Create a data Set object 
        Dim ds As New DataSet("EmployeeSet")
        adapter.Fill(ds, "Employees")

        ' Create a data table object and add a new row 
        Dim EmployeeTable As DataTable = ds.Tables("Employees")
        Dim row As DataRow = EmployeeTable.NewRow()
        row("FirstName") = "Rodney"
        row("LastName") = "DangerField"
        row("Title") = "Comedian"
        EmployeeTable.Rows.Add(row)

        ' update data adapter 
        adapter.Update(ds, "Emlpoyees")
        MessageBox.Show((row("FirstName").ToString().Trim() & " ") + row("LastName").ToString().Trim() & " Added to Employees")

As you can see from listing 5-54, you didn't have to figure out how to create the InsertCommand for Employee table because the CommandBuilder did it for you. All you had to do was add row to the DataSet and invoke and Update on the DataAdapter. You may argue that the InsertCommand is automatically generated by VS.NET anyway by the DataAdapter configurer, but the CommandBuilder works with the SelectCommand you choose for the adapter, so you can change the SelectCommand on the fly and reuse the CommandBuilder at run-time. 

Note that the method RefereshSchema of the CommandBuilder should be called if the SelectCommand of the associated DataAdapter changes. The RefreshSchema rebuilds the other command structures (InsertCommand, DeleteCommand, UpdateCommand) of the DataAdapter. 


Conclusion

Hope this article would have helped you in understanding 
CommandBuilder in ADO.NET. See my other articles on the website on ADO.NET.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.