9.5 Implement the Methods That Update the Database

Now that you have a class that can retrieve a specific row from the database, you will need to implement a method that will save changes to the database.

You'll also need a method to insert new rows into-as well as delete existing rows from-the Customers table.

Technique

In the case of updating and deleting rows in the database, you will need to implement the Save and Delete methods that are defined in the ICustomer interface.

To insert new rows into the database, you'll need to add additional constructors to the class. Having multiple methods with the same name is a new feature in Visual Basic .NET called overloading. In this example, you will only overload constructors, but you can also overload functions and subs.

Steps

The first task is to implement the Save and Delete methods that were defined in your interface in section 9.1. You have already set up all the database-access objects you will need, so it's just a matter of adding the relevant code.

  1. You will need two helper methods for the Save and Delete methods: a method whose sole responsibility is to call the update method of the data adapter, and a method that clears the properties of the object. Both methods are defined in Listing 9.26.

    Listing 9.26 frmHowTo9_5.vb: The WriteChangesToDB and Clear Methods
    Private Function WriteChangesToDB (ByVal pStateToHandle _
 As DataRowState) As Boolean

    Try
        ' Create a dataset of only those rows that have been
        ' changed (specifically, those changed in the manner
        ' specified by the DataRowState).
        Dim dsChanges As DataSet = mdsCust.GetChanges(pStateToHandle)

        ' Pass this subset of the dataset to the data adapter
        ' to write the changes to the database. The data adapter
        ' will handle all calls to the Delete, Insert, and Update
        ' commands.
        odaCustomers.Update(dsChanges)

    Catch ex As Exception
        ' If the update fails, communicate this back to
        ' the calling function by returning False.
        mdsCust.RejectChanges()
        Return False

    End Try

    ' Update the customer's dataset to reflect the changes
    ' you have made.
    mdsCust.AcceptChanges()
    Return True

End Function

Private Sub Clear()
    ' Consumers of this class should be savvy enough to dispose
    ' of the object after they call the Delete method, but
    ' to be sure, let's set all the pointers to null, so if a
    ' developer forgets... NullPointerException
    mdsCust = Nothing
    mdrCust = Nothing
    mstrCustomerID = Nothing
    mstrCompanyName = Nothing
    mstrContactName = Nothing
    mstrContactTitle = Nothing
    mstrAddress = Nothing
    mstrCity = Nothing
    mstrRegion = Nothing
    mstrCountry = Nothing
    mstrPostalCode = Nothing
    mstrPhone = Nothing
    mstrFax = Nothing
End Sub

    Adding code to insert new rows into the database requires a bit more work. The first problem is that you have only one way to create an instance of the CCustomer class: by passing a CustomerID value to the constructor that retrieves an existing row from the database. You will need to add a second constructor to your class that allows you to create new Customer objects.

    Having two methods with the same name is called overloading. In Visual Basic 6, you had limited support for overloading with properties. Think about it: Properties are pairs of methods with the same name, except that one is a sub and accepts a parameter, whereas the other is a parameterless function that returns a value. Each independent Visual Basic 6 function or sub, however, must have a unique name.

    Visual Basic .NET lifts this restriction by allowing you to overload any method, be it function, sub, or constructor. For constructors, all you need to do is add another New sub. (For functions and subs, you need to preface the declaration with the Overloads keyword.)

    You can add as many overloaded methods as you like, as long as the parameter list is different. Be aware that .NET determines which overloaded method to call-constructor or otherwise-based on the order of the datatypes in the parameter list, and not the names of the parameters. In other words, you can have two overloaded methods that take an integer and a string as their parameters, as long as one method has a string as the first parameter, and the other has an integer as the first parameter-like the first two methods in Listing 9.27. It doesn't matter what you name the parameter: Two overloaded methods with a single integer parameter but with different names-like the second pair of methods in Listing 9.27-are not allowed.

    The name of the method, the return type, and the order of its parameters make up what is called the method signature. When it comes to overloading, however, Microsoft only takes into account the argument signature. In other words, the return type doesn't matter.

    Listing 9.27 Overloaded Methods: The Datatypes of the Parameters Are What Matter
    'These two methods can be declared because the order
'of the datatypes differs. If you do this, make sure
'your parameters have descriptive names. Otherwise,
'your code can be confusing to other developers.
Overloads Sub method1(ByVal IntegerValue As Integer,
                      ByVal StringValue As String)

Overloads Sub method1(ByVal StringValue As String,
                      ByVal IntegerValue As Integer)

'These two methods cannot be declared, even though the
'parameters have different names.
Overloads Sub method2(ByVal IntegerValue As Integer)
Overloads Sub method2(ByVal AnotherIntegerValue As Integer)

    Tip

    graphics/tip_icon.gif

    You should always use Option Strict in applications in which you use overloaded methods. Option Strict prevents you from implicitly converting between datatypes when data might become lost as a result of the conversion.

    For example, if you have two overloaded methods-one that accepts a string and one that accepts an integer-without Option Strict, .NET might get confused and convert the integer value to a string and call the wrong method. With Option Strict, not only will this not happen, but you will not even be able to compile your code with an implicit type conversion.

  2. Now that you understand what an overloaded method is, add one more constructor, as shown in Listing 9.28, that accepts the two required values for the Customers table: CustomerID and CompanyName. You will also need to declare a private, class-level Boolean variable to track whether you have a new or existing record loaded into the class.

    Listing 9.28 frmHowTo9_5.vb: A New Constructor for Inserting New Rows into the Customers Table
    Private mfNew As Boolean ' By default, this value will be false

Public Sub New(ByVal pCustomerID As String,
           ByVal pCompanyName As String)
    ' This constructor is used to create new Customer records
    ' in the database.
    mdsCust = New dsCustomers()
    mfNew = True
    mstrCustomerID = pCustomerID
    Me.CompanyName = pCompanyName
    mstrContactName = ""
    mstrContactTitle = ""
    mstrAddress = ""
    mstrCity = ""
    mstrRegion = ""
    mstrCountry = ""
    mstrPostalCode = ""
    mstrPhone = ""
    mstrFax = ""
End Sub

    Take a look at the first two lines of the constructor. First, you need to make sure that an instance of the Customers dataset is created. Second, you need some form of flag to keep track of whether the instance is a new or existing row. This example will use a class-level Boolean variable called mfNew to fill this role.

  3. Next, implement a method that will take the values of your class' properties and write them to the data row, as shown in Listing 9.29.

    Listing 9.29 frmHowTo9_5.vb: Implementation of the Save Method
    Private Sub WriteValuesToDataRow()
    ' This technique allows consumers to modify properties (class-
    ' level variables) but leaves the DataRow intact until the
    ' save method is called. In essence, the DataRow holds the
    ' original state of the Customers row, while the class-level
    ' variables hold the current state. This method synchronizes
    ' the two.
    With mdrCust
        .CompanyName = mstrCompanyName

        If mstrAddress.Length = 0 Then
            .SetAddressNull()
        Else
            .Address = mstrAddress
        End If

        If mstrRegion.Length = 0 Then
            .Set_RegionNull()
        Else
            ._Region = mstrRegion
        End If

        If mstrCountry.Length = 0 Then
            .SetCountryNull()
        Else
            .Country = mstrCountry
        End If

        If mstrPostalCode.Length = 0 Then
            .SetPostalCodeNull()
        Else
            .PostalCode = mstrPostalCode
        End If

        If mstrPhone.Length = 0 Then
            .SetPhoneNull()
        Else
            .Phone = mstrPhone
        End If

        If mstrFax.Length = 0 Then
            .SetFaxNull()
        Else
            .Fax = mstrFax
        End If

        If mstrContactTitle.Length = 0 Then
            .SetContactTitleNull()
        Else
            .ContactTitle = mstrContactTitle
        End If

        If mstrContactName.Length = 0 Then
            .SetContactNameNull()
        Else
            .ContactName = mstrContactName
        End If

        If mstrCity.Length = 0 Then
            .SetCityNull()
        Else
            .City = mstrCity
        End If
    End With
End Sub

  4. Now implement the Save method by pasting the code from Listing 9.30 into the class. To make your code as easy to use as possible, have the Save method manage both new and existing ecords.

    Listing 9.30 frmHowTo9_5.vb: Implementation of the Save Method
    Public Function Save() As Boolean Implements ICustomer.Save

    Dim nState As DataRowState

    If mfNew Then
        ' If this is a new Customer, you need to add a data row
        ' to the dataset.
        mdrCust = mdsCust.Customers.AddCustomersRow(mstrCustomerID, _
            mstrCompanyName, mstrContactName, mstrContactTitle, _
            mstrAddress, mstrCity,mstrRegion, mstrPostalCode, _
            mstrCountry, mstrPhone, mstrFax)

        nState = DataRowState.Added
        mfNew = False
    Else
        nState = DataRowState.Modified
    End If

    ' Begin editing the data row
    mdrCust.BeginEdit()

    ' If you have a problem writing values to the data row, you want to
    ' catch the exception, cancel editing, reject the changes, and
    ' immediately dump out of the method with a return value of false.
    Try
        writeValuesToDataRow()
    Catch ex As Exception
        mdrCust.CancelEdit()
        mdrCust.RejectChanges()
        Return False
    End Try

    ' If you succeed in writing the new values to the data row,
    ' end the editing block, and then write the changes to the
    ' database. If you have a problem writing to the DB, then
    ' reject the changes to the row and return false.
    mdrCust.EndEdit()
    If WriteChangesToDB (nState) Then
        Return True
    Else
        mdrCust.RejectChanges()
        Return False
    End If

End Function

  5. Finally, implement the Delete method. The Delete method flags the data row for deletion and then calls the WriteChangesToDB method with a DataRowState of Deleted. Paste the Delete method from Listing 9.31 into your class.

    Listing 9.31 frmHowTo9_5.vb: Implementation of the Delete Method
    Public Function Delete() As Boolean Implements ICustomer.Delete

    If mfNew Then

        ' If you're working with a new record, you don't need to
        ' communicate with the database, so clear the properties
        ' of the object and return true to indicate that the delete
        ' succeeded.
        Clear()
        Return True

    Else

        ' If this is an existing record, flag the data row as
        ' deleted, then write the changes to the DB.
        mdrCust.Delete()

        If WriteChangesToDB (DataRowState.Deleted) Then
            Clear()
            Return True
        Else
            mdrCust.RejectChanges()
            Return False
        End If

    End If

End Function

  6. To test this code, try creating a new CCustomer instance, set its properties, and save it to the database. Then instantiate a new CCustomer instance, loading the new row into the object. Make a change to that instance, and save the changes back to the database. Last, load that new row into a third CCustomer instance and delete it. Add the code in Listing 9.32 to the click events of the Delete, Save, and New buttons of frmHowTo9_5 to test the new code in your class.

    Listing 9.32 frmHowTo9_5.vb: Testing the New Constructor and the Delete and Save Methods
    Private Sub btnNew_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnNew.Click

    Try

        ' These two properties are the minimum required to insert a new row.

        If txtCustomerID.Text.Length > 0 Then
            If txtCompanyName.Text.Length > 0 Then
                mCustomer = New CCustomer(txtCustomerID.Text, txtCompanyName.Text)
            Else
                MsgBox("A company name is required for a new Customer.")
            End If
        Else
            MsgBox("A CustomerID is required for a new Customer.")
            Exit Sub
        End If

        GetProperties()
        Me.rtbToString.Text = mCustomer.ToString

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub


Private Sub btnSave_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnSave.Click

    Try

        If mCustomer.Save() Then
            MsgBox("Save succeeded.")
        Else
            MsgBox("Save failed.")
        End If

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub


Private Sub btnDelete_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnDelete.Click

    Try

        If mCustomer.Delete() Then
            MsgBox("Delete succeeded.")
            mCustomer = Nothing
            ClearAllTextBoxes()
        Else
            MsgBox("Delete failed.")
        End If

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

How It Works

When a consumer of the CCustomer class instantiates an object using the new constructor, the required properties of the object (the CustomerID and CompanyName) are set using the parameters of the constructor, whereas the optional properties are all set to zero-length strings. This constructor also sets an internal flag saying that the current instance is a new record. When the Save method is called, the internal flag tells the class that a new data row should be added to the dsCustomer dataset. Finally, the WriteChangesToDB is called and a new row is inserted into the Customers table.

If the Save method is called with an existing record, the internal flag lets the class know that the data row already exists in the dsCustomer dataset, so the WriteChangesToDB method is called to update that row in the Customers table.

The behavior of the Delete method also changes based on the value of the internal new-record flag. If the object instance is a new record, you don't need to delete the row from the database; therefore, the only necessary action is to dispose of the class-level variables. If the object instance is an existing record, the delete method of the data row is called. Finally, the WriteChangesToDB method is called to physically delete the row from the Customers table.

Both the Save and Delete methods use the AcceptChanges and RejectChanges methods of the data row to react to exceptions thrown when making changes to the database.

Comments

One thing to note is that the Delete and WriteChangesToDB methods only return Boolean values and do not throw exceptions. The data validation code that you will see in the next section should prevent most of the exceptions that could be thrown when updating the database, so a Boolean return value is sufficient. Depending on your needs, however, this might to be too simple of an implementation. In the next section, you will learn how to create custom exceptions that will provide more error detail to consumers of your classes.