14.6 Changing Database Records

So far, you've retrieved data from a database, but you haven't manipulated its records in any way. Using ADO.NET, it is of course possible to add records, change an existing record, or delete a record altogether.

In a typical implementation, you might work your way through the following steps:

This process gives you very fine control over the update to your data as well as an opportunity to fix any data that would otherwise cause an error.

In the following example, you'll create a dialog box that displays the contents of the Customer table in Northwinds. The goal is to test updating a record, adding a new record, and deleting a record. As always, I'll keep the code as simple as possible, which means eliminating many of the error-checking and exception-handling routines you might expect in a production program.

Figure 14-7 shows the somewhat crude but useful form I've built to experiment with these features of ADO.NET.

Figure 14-7. The ADO Update form

This form consists of a listbox (lbCustomers), a button for Update (btnUpdate), an associated text box (txtCustomerName), and a Delete button (btnDelete). There is also a set of eight text fields that are used in conjunction with the New button (btnNew). These text fields represent eight of the fields in the Customers table in the Northwind database. There is also a label (lblMessage) that you can use for writing messages to the user (it currently says "Press New, Update or Delete").

14.6.1 Accessing the Data

First, create the DataAdapter object and the DataSet as private member variables, along with the DataTable:

Private myDataAdapter As SqlDataAdapter
Private myDataSet As DataSet
Private myDataTable As DataTable

This enables you to refer to these objects from various member methods. You start by creating strings for the connection and the command that will get you the table you need:

Dim connectionString As String = _
    "server=localhost; uid=sa; " & _
    "pwd=YourPassword; database=northwind"

Dim commandString As String = _
    "Select * from Customers"

These strings are passed as parameters to the SqlDataAdapter constructor:

myDataAdapter = New SqlDataAdapter( _
    commandString, connectionString)

A DataAdapter may have four SQL commands associated with it. Right now, we have only one: myDataAdapter.SelectCommand. The InitializeCommands( ) method creates the remaining three: InsertCommand, UpdateCommand, and DeleteCommand. InitializeCommands( ) uses the AddParams( ) method to associate a column in each SQL command with the columns in the modified rows:

Private Sub AddParms( _
    ByVal cmd As SqlCommand, _
    ByVal ParamArray cols( ) As String)
    ' Add each parameter
    Dim column As [String]
    For Each column In cols
        cmd.Parameters.Add("@" & column, SqlDbType.Char, 0, column)
    Next column
End Sub 'AddParms

InitializeCommands( ) creates each SQL command in turn, using placeholders that correspond to the column argument passed to AddParm( ):

Private Sub InitializeCommands( )

    ' Reuse the SelectCommand's Connection.
    Dim connection As SqlConnection = _
        CType(myDataAdapter.SelectCommand.Connection, _
        SqlConnection)

    ' Create an explicit, reusable insert command
    myDataAdapter.InsertCommand = connection.CreateCommand( )
    myDataAdapter.InsertCommand.CommandText = _
        "Insert into customers " & _
        "(CustomerId, CompanyName, ContactName, ContactTitle, " & _
        " Address, City, PostalCode, Phone) " & _
        "values(@CustomerId, @CompanyName, @ContactName, " & _
        "   @ContactTitle, @Address, @City, @PostalCode, @Phone)"

    AddParms(myDataAdapter.InsertCommand, _
        "CustomerId", "CompanyName", "ContactName", _
        "ContactTitle", "Address", "City", "PostalCode", "Phone")

    ' Create an explicit update command
    myDataAdapter.UpdateCommand = _
        connection.CreateCommand( )
    myDataAdapter.UpdateCommand.CommandText = _
        "update Customers " & _
        "set CompanyName = _"
        @CompanyName where CustomerID = @CustomerId"
    AddParms(myDataAdapter.UpdateCommand, _
        "CompanyName", "CustomerID")

    ' Create an explicit delete command
    myDataAdapter.DeleteCommand = _
        connection.CreateCommand( )
    myDataAdapter.DeleteCommand.CommandText = _
        "delete from customers where customerID = @CustomerId"
    AddParms(myDataAdapter.DeleteCommand, "CustomerID")
End Sub 'InitializeCommands

The DataAdapter uses these three commands to modify the table when you invoke Update( ).

Back in the constructor, you can now create the DataSet and fill it with the SqlDataAdapter object you've just created:

myDataSet = New DataSet( )
myDataAdapter.Fill(myDataSet, "Customers")

Display the table contents by calling the PopulateLB( ) method, which is a private method that fills the listbox from the contents of the single table in the DataSet:

Private Sub PopulateLB( )
     myDataTable = myDataSet.Tables(0)
     lbCustomers.Items.Clear( )
     Dim dr As DataRow
     For Each dr In myDataTable.Rows
         lbCustomers.Items.Add(dr("CompanyName") & _
             " (" & dr("ContactName") & ")")
     Next
 End Sub 'PopulateLB

14.6.2 Updating a Record

The form is now displayed, and you're ready to update a record. Highlight a record and fill in a new customer name in the topmost text field. When you press Update, read the resulting name and put it into the chosen record.

The first task is to get the specific row the user wants to change:

Protected Sub btnUpdate_Click( _
    ByVal sender As Object, _
    ByVal e As System.EventArgs) _
    Handles btnUpdate.Click

    Dim targetRow As DataRow = _
        myDataTable.Rows(lbCustomers.SelectedIndex)

You declare a new object of type DataRow and initialize it with a reference to the specific row in the DataTable's Rows collection that corresponds to the selected item in the listbox. Remember that DataTable was declared as a member variable and initialized in the PopulateLB( ) method shown in the previous section.

You can now display the name of the company you're going to update:

lblMessage.Text = "Updating " & _
    targetRow("CompanyName")
Application.DoEvents( )

The call to the static method DoEvents( ) of the Application class causes the application to process Windows messages and paint the screen with the message. If you were to leave this line out, the current thread would dominate the processor and the messages would not be printed until the button handler completes its work.

Call BeginEdit( ) on the DataRow to put the row into editing mode. This suspends events on the row so that you could, if you chose, edit a number of rows at once without triggering validation rules (there are no validation rules in this example). It is good form to bracket changes on DataRows with calls to BeginEdit( ) and EndEdit( ):

targetRow.BeginEdit( )
targetRow("CompanyName") = txtCustomerName.Text
targetRow.EndEdit( )

The actual edit is to the column CompanyName within the targetRow object, which is set to the text value of the text control txtCustomerName. The net effect is that the CompanyName field in the row is set to whatever the user put into that text box.

Notice that the column you want is indexed within the row by the name of that column. In this case, the name will match the name that is used in the database, but this is not required. When you created the DataSet, you could have used the TableMappings( ) method to change the names of the columns.

Having edited the column, you are ready to check to make sure there are no errors. First, extract all the changes made to the DataSet (in this case, there will be only one change) using the GetChanges( ) method, passing in a DataRowState enumeration to indicate that you want only those rows that have been modified. GetChanges( ) returns a new DataSet object:

Dim dataSetChanged As DataSet = _
    myDataSet.GetChanges(DataRowState.Modified)

Now you can check for errors. To simplify the code, I've included a flag to indicate that all is OK. If you find any errors, rather than trying to fix them you can just set the flag to false and not make the updates:

Dim okayFlag As Boolean = True
If dataSetChanged.HasErrors Then
    okayFlag = False
    Dim msg As String = "Error in row with customer ID "

    Dim theTable As DataTable
    For Each theTable In dataSetChanged.Tables
        If theTable.HasErrors Then
            Dim errorRows As DataRow( ) = theTable.GetErrors( )
            Dim errorRow As DataRow
            For Each errorRow In errorRows
                msg = msg & errorRow("CustomerID")
            Next
        End If
    Next
    lblMessage.Text = msg
End If

You first test to see whether the new data record set has any errors by checking the HasErrors property. If HasErrors is true, there are errors; set the Boolean okayFlag to false, and then go on to discover where the error lies. To do so, iterate through all the tables in the new database (in this case, there is only one), and if a table has errors you'll get an array of all the rows in that table with errors (shown here as the errorRows array).

Then iterate through the array of rows with errors, handling each in turn. In this case, you just update the message on the dialog box, but in a production environment you might interact with the user to fix the problem.

If the okayFlag is still true after testing HasErrors, there were no errors and you are ready to update the database. First, merge the new DataSet back in with the original (presumably, in a production program you'd be merging the fixed tables back in with the original):

If okayFlag Then
    myDataSet.Merge(dataSetChanged)

You can now update the DataSet:

myDataAdapter.Update(myDataSet, "Customers")

This causes the DataAdapter object to create the necessary command text to update the database. You can actually see that text by accessing the CommandText property of the DataAdapter object. You can display the command in the message text:

lblMessage.Text = myDataAdapter.UpdateCommand.CommandText
Application.DoEvents( )

You now must tell the DataSet to accept the changes and then repopulate the listbox from the DataSet:

myDataSet.AcceptChanges( )
PopulateLB( )

If okayFlag were false, there would have been errors; in this example, we'd just reject the changes:

Else
     myDataSet.RejectChanges( )
End If

14.6.3 Deleting a Record

The code for handling the Delete button is even simpler. First, get the target row:

Protected Sub btnDelete_Click( _
    ByVal sender As Object, _
    ByVal e As System.EventArgs) _
    Handles btnDelete.Click
    ' get the selected row
    Dim targetRow As DataRow = _
        myDataTable.Rows(lbCustomers.SelectedIndex)

and form the delete message:

Dim msg As String = _
    targetRow("CompanyName") & " deleted. "

You don't want to show the message until the row is deleted, but you need to get it now because after you delete the row it will be too late!

You're now ready to mark the row for deletion:

targetRow.Delete( )

Calling AcceptChanges( ) on the DataSet causes AcceptChanges( ) to be called on each table within the DataSet. This in turn causes AcceptChanges( ) to be called on each row in those tables. Thus the one call to DataSet.AcceptChanges( ) cascades down through all the contained tables and rows.

Next, you need to call Update( ) and AcceptChanges( ), and then refresh the listbox. However, if this operation fails, the row will still be marked for deletion. If you then try to issue a legitimate command, such as an insertion, update or another deletion, the DataAdapter will try to commit the erroneous deletion again, and the whole batch will fail because of the delete. In order to avert this situation, wrap the remaining operations in a try block, and call RejectChanges( ) if they fail:

Try
    myDataAdapter.Update(myDataSet, "Customers")
    myDataSet.AcceptChanges( )
    PopulateLB( )
    lblMessage.Text = msg
    Application.DoEvents( )
Catch ex As SqlException
    myDataSet.RejectChanges( )
    MessageBox.Show(ex.Message)
End Try

Deleting records from the Customers database might cause an exception if the record deleted is constrained by database integrity rules. For example, if a customer has orders in the Orders table, you cannot delete the customer until you delete the orders. To solve this, Example 14-6 will create new Customer records that you can then delete at will.

14.6.4 Creating New Records

To create a new record, the user will fill in the fields and press the New button. This will fire the btnNew.Click event, which invokes the btnNew_Click( ) event handling method:

Protected Sub btnNew_Click( _
    ByVal sender As Object, _
    ByVal e As System.EventArgs) _
    Handles btnNew.Click

In the event handler, you call DataTable.NewRow( ), which asks the table for a new DataRow object:

Dim newRow As DataRow = myDataTable.NewRow( )

This is very elegant because the new row that the DataTable produces has all the necessary DataColumns for this table. You can just fill in the columns you care about, taking the text from the user interface (UI):

newRow("CustomerID") = txtCompanyID.Text
newRow("CompanyName") = txtCompanyName.Text
newRow("ContactName") = txtContactName.Text
newRow("ContactTitle") = txtContactTitle.Text
newRow("Address") = txtAddress.Text
newRow("City") = txtCity.Text
newRow("PostalCode") = txtZip.Text
newRow("Phone") = txtPhone.Text

Now that the row is fully populated, just add it back to the table:

myDataTable.Rows.Add(newRow)

The table resides within the DataSet, so all you have to do is tell the DataAdapter object to update the database with the DataSet and accept the changes:

myDataAdapter.Update(myDataSet, "Customers") 
myDataSet.AcceptChanges( )

Next, update the user interface:

lblMessage.Text = _
    myDataAdapter.UpdateCommand.CommandText
Application.DoEvents( )

You can now repopulate the listbox with your new added row and clear the text fields so that you're ready for another new record:

PopulateLB( )
ClearFields( )

ClearFields( ) is a private method that simply sets all the text fields to empty strings. That method and the entire program are shown in Example 14-6.

Example 14-6. Updating, deleting, and adding records

Option Strict On
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient

Public Class ADOForm1
    Inherits System.Windows.Forms.Form
    Private components As System.ComponentModel.Container
    Private label9 As System.Windows.Forms.Label
    Private txtPhone As System.Windows.Forms.TextBox
    Private label8 As System.Windows.Forms.Label
    Private txtContactTitle As System.Windows.Forms.TextBox
    Private label7 As System.Windows.Forms.Label
    Private txtZip As System.Windows.Forms.TextBox
    Private label6 As System.Windows.Forms.Label
    Private txtCity As System.Windows.Forms.TextBox
    Private label5 As System.Windows.Forms.Label
    Private txtAddress As System.Windows.Forms.TextBox
    Private label4 As System.Windows.Forms.Label
    Private txtContactName As System.Windows.Forms.TextBox
    Private label3 As System.Windows.Forms.Label
    Private txtCompanyName As System.Windows.Forms.TextBox
    Private label2 As System.Windows.Forms.Label
    Private txtCompanyID As System.Windows.Forms.TextBox
    Private label1 As System.Windows.Forms.Label
    Private WithEvents btnNew As System.Windows.Forms.Button
    Private txtCustomerName As System.Windows.Forms.TextBox
    Private WithEvents btnUpdate As System.Windows.Forms.Button
    Private lblMessage As System.Windows.Forms.Label
    Private WithEvents btnDelete As System.Windows.Forms.Button
    Private lbCustomers As System.Windows.Forms.ListBox

    ' the myDataSet, myDataAdapter, and myDataTable are members 
    ' so that we can access them from any member method.
    Private myDataAdapter As SqlDataAdapter
    Private myDataSet As DataSet
    Private myDataTable As DataTable


    Public Sub New( )
        InitializeComponent( )

        Dim connectionString As String = _
            "server=localhost; uid=sa; " & _
            "pwd=YourPassword; database=northwind"

        Dim commandString As String = _
            "Select * from Customers"
        myDataAdapter = _
            New SqlDataAdapter( _
                commandString, connectionString)

        InitializeCommands( )

        myDataSet = New DataSet( )
        myDataAdapter.Fill(myDataSet, "Customers")
        PopulateLB( )
    End Sub 'New

    Private Sub AddParms( _
        ByVal cmd As SqlCommand, _
        ByVal ParamArray cols( ) As String)
        ' Add each parameter
        Dim column As [String]
        For Each column In cols
            cmd.Parameters.Add("@" & column, SqlDbType.Char, 0, column)
        Next column
    End Sub 'AddParms

    Private Sub InitializeCommands( )

        ' Reuse the SelectCommand's Connection.
        Dim connection As SqlConnection = _
            CType(myDataAdapter.SelectCommand.Connection, _
            SqlConnection)

        ' Create an explicit, reusable insert command
        myDataAdapter.InsertCommand = connection.CreateCommand( )
        myDataAdapter.InsertCommand.CommandText = _
            "Insert into customers " & _
            "(CustomerId, CompanyName, ContactName, ContactTitle, " & _
            " Address, City, PostalCode, Phone) " & _
            "values(@CustomerId, @CompanyName, @ContactName, " & _
            "   @ContactTitle, @Address, @City, @PostalCode, @Phone)"

        AddParms(myDataAdapter.InsertCommand, _
            "CustomerId", "CompanyName", "ContactName", _
            "ContactTitle", "Address", "City", "PostalCode", "Phone")

        ' Create an explicit update command
        myDataAdapter.UpdateCommand = _
            connection.CreateCommand( )
        myDataAdapter.UpdateCommand.CommandText = _
            "update Customers " & _
            "set CompanyName = _"
            @CompanyName where CustomerID = @CustomerId"
        AddParms(myDataAdapter.UpdateCommand, _
            "CompanyName", "CustomerID")

        ' Create an explicit delete command
        myDataAdapter.DeleteCommand = _
            connection.CreateCommand( )
        myDataAdapter.DeleteCommand.CommandText = _
            "delete from customers where customerID = @CustomerId"
        AddParms(myDataAdapter.DeleteCommand, "CustomerID")
    End Sub 'InitializeCommands

    ' fill the listbox with columns from the Customers table
    Private Sub PopulateLB( )
        myDataTable = myDataSet.Tables(0)
        lbCustomers.Items.Clear( )
        Dim dataRow As DataRow
        For Each dataRow In myDataTable.Rows
            lbCustomers.Items.Add((dataRow("CompanyName") & _
                " (" & dataRow("ContactName") & ")"))
        Next dataRow
    End Sub 'PopulateLB

    Private Sub InitializeComponent( )
        ' Removed to save space
    End Sub 'InitializeComponent

    ' handle the new button click
    Protected Sub btnNew_Click( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles btnNew.Click
        ' create a new row, populate it
        Dim newRow As DataRow = myDataTable.NewRow( )
        newRow("CustomerID") = txtCompanyID.Text
        newRow("CompanyName") = txtCompanyName.Text
        newRow("ContactName") = txtContactName.Text
        newRow("ContactTitle") = txtContactTitle.Text
        newRow("Address") = txtAddress.Text
        newRow("City") = txtCity.Text
        newRow("PostalCode") = txtZip.Text
        newRow("Phone") = txtPhone.Text

        ' add the new row to the table
        myDataTable.Rows.Add(newRow)

        ' update the database
        Try
            myDataAdapter.Update(myDataSet, "Customers")
            myDataSet.AcceptChanges( )

            ' inform the user 
            lblMessage.Text = "Updated!"
            Application.DoEvents( )

            ' repopulate the listbox 
            PopulateLB( )
            ' clear all the text fields
            ClearFields( )
        Catch ex As SqlException
            myDataSet.RejectChanges( )
            MessageBox.Show(ex.Message)
        End Try
    End Sub 'btnNew_Click

    ' set all the text fields to empty strings
    Private Sub ClearFields( )
        txtCompanyID.Text = ""
        txtCompanyName.Text = ""
        txtContactName.Text = ""
        txtContactTitle.Text = ""
        txtAddress.Text = ""
        txtCity.Text = ""
        txtZip.Text = ""
        txtPhone.Text = ""
    End Sub 'ClearFields

    ' handle the update button click
    Protected Sub btnUpdate_Click( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles btnUpdate.Click
        
        ' get the selected row
        Dim targetRow As DataRow = _
            myDataTable.Rows(lbCustomers.SelectedIndex)

        ' inform the user
        lblMessage.Text = "Updating " & targetRow("CompanyName")
        Application.DoEvents( )

        ' edit the row
        targetRow.BeginEdit( )
        targetRow("CompanyName") = txtCustomerName.Text
        targetRow.EndEdit( )

        ' get each row that changed
        Dim myDataSetChanged As DataSet = _
            myDataSet.GetChanges(DataRowState.Modified)

        ' test to make sure all the changed rows are without errors
        Dim okayFlag As Boolean = True
        If myDataSetChanged.HasErrors Then
            okayFlag = False
            Dim msg As String = "Error in row with customer ID "

            ' examine each table in the changed myDataSet
            Dim theTable As DataTable
            For Each theTable In myDataSetChanged.Tables
                ' if any table has errors, find out which rows
                If theTable.HasErrors Then
                    ' get the rows with errors
                    Dim errorRows As DataRow( ) = theTable.GetErrors( )

                    ' iterate through the errors and correct
                    ' (in our case, just identify)
                    Dim theRow As DataRow
                    For Each theRow In errorRows
                        msg = msg & theRow("CustomerID")
                    Next theRow
                End If
            Next theTable
            lblMessage.Text = msg
        End If
        ' if we have no errors
        If okayFlag Then

            ' update the database
            myDataAdapter.Update(myDataSetChanged, "Customers")

            ' inform the user
            lblMessage.Text = "Updated " & targetRow("CompanyName")
            Application.DoEvents( )

            ' accept the changes and repopulate the listbox
            myDataSet.AcceptChanges( )
            PopulateLB( )
            ' if we had errors, reject the changes
        Else
            myDataSet.RejectChanges( )
        End If
    End Sub 'btnUpdate_Click

    ' handle the delete button click
    Protected Sub btnDelete_Click( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles btnDelete.Click
        ' get the selected row
        Dim targetRow As DataRow = _
            myDataTable.Rows(lbCustomers.SelectedIndex)

        ' prepare message for user
        Dim msg As String = targetRow("CompanyName") & " deleted. "

        ' delete the selected row
        targetRow.Delete( )

        ' update the database
        Try
            myDataAdapter.Update(myDataSet, "Customers")
            myDataSet.AcceptChanges( )
            ' repopulate the listbox without the deleted record
            PopulateLB( )

            ' inform the user
            lblMessage.Text = msg
            Application.DoEvents( )
        Catch ex As SqlException
            myDataSet.RejectChanges( )
            MessageBox.Show(ex.Message)
        End Try
    End Sub 'btnDelete_Click

End Class 'ADOForm1

Figure 14-8 shows the filled-out form just before the New button is pressed.

Figure 14-8. Ready to add a new record

Figure 14-9 shows the form immediately after the new record is added. Note that the new record is appended to the end of the list and the text fields are cleared.

Figure 14-9. After adding the new record