The first article in this series detailed how to build data-aware forms in Visual Studio .NET using the Data Form Wizard.

It demonstrated how easy it is to build forms using a point-and-click interface. You also learned that the generated form was especially suited to production development. This article continues where that article left off and introduces you to techniques to improve data-aware forms created with the Data Form Wizard.

The Data Form Wizard accomplishes two goals: first, it provides an illustration of how the various ADO.NET objects work; and, secondly, it shows you how data-binding works in a Windows form. Note the choice of words: “accomplish” not “accomplish well!” In fact, the means by which the Data Form Wizard accomplishes what it does leaves the user with a moderately useful academic sample but nothing that could be used in a serious production application. Furthermore, if you are attempting to get a sense of how the ADO.NET objects work, it can be a rather daunting task when viewed through the lens of a data-aware form created by the Data Form Wizard. For example: Where is the line of demarcation between the user interface (UI) and the data? What belongs in the UI? What belongs in the middle/data tier? How does ADO.NET really work? This article attempts to answer these questions by investigating what the Data Form Wizard produces. Specifically, this article focuses on how the various ADO.NET objects work on their own as well as how they work together in their joint function of working with data. Where the Data Form Wizard combines and confuses, this article separates and clarifies.

A Review of What the Data Form Wizard Produces

The end result of the Data Form Wizard is a consolidated entity consisting of UI and Data Tier elements. The wizard embeds all of the data logic directly on the form. Specifically, all of the ADO.NET objects as well as the associated setup code are directly embedded. You can quickly conclude that what the Data Form Wizard produces does not fall within component-based/n-tier development methodologies. A moderate attempt at componentization is achieved by the wizard through a set of form-level methods. Still, the initialization steps for both the form and the various ADO objects are merged into a single block of code that would result in many printed pages of paper. It is important to note that the Authors Form, illustrated in Figure 1, is a relatively simple form with only a few UI elements.

Figure 1: The initial version of the Authors Form produced by the Data Form Wizard.
Figure 1: The initial version of the Authors Form produced by the Data Form Wizard.

Taking just a few more moments to discuss the results from the Data Form Wizard, you'll find code that establishes a connection, a data adapter, a dataset and various command objects. These items, with perhaps the exception of the dataset object, are exclusively data related. Specifically, the UI does not need to directly deal with these items. In fact, the Data Form Wizard creates distinct form-level methods that act as an interface to the various operations that delete, update and insert data. If the Wizard created separate form methods, why not go the extra step of creating a separate class? The good news for you is that this article does just that!

Another observation is that the various command objects appear to require a lot of code to set up the various parameters. On one hand, you don't have to crank out the code manually since the wizard does the work. On the other hand, what the wizard leaves you with is something that cannot be reused. Are you trapped with having to crank out this code manually if you want reuse? Is there a generic way you can determine the structure of tables and command parameters? The answer is “yes” and this issue is addressed in this article, too!

Next, the Data Form Wizard has a Load button that grabs all of the data and marshals that data to a dataset for data-binding purposes in the form. In conjunction with this, the wizard provides a set of navigation buttons that enables the user to go to the first, last, next and previous data records. No scaleable client/server application of note works this way. Typically, when data is requested, the user provides information to lookup the data via a query. When the dataset is small, a pick list with two columns helps to navigate through data. At all times, the goal is to minimize the amount of data brought back to the client. This article also highlights techniques you can use to minimize the amount of data you bring back to the client.

Finally, by default the wizard produces client-side rendered Select, Update, Insert and Delete SQL statements for the various ADO.NET command objects. For reasons of performance and security, you want to make use of stored procedures whenever possible. As you might guess, this article also illustrates how to use stored procedures with ADO.NET.

Issues to Address in the Modified Design

The previous section paints a dim picture of the output produced by the Data Form Wizard. The news is not all that bad. In reviewing the code produced, you can conclude that the actual functions of ADO are somewhat obscured through a lot of code. However, you can get a sense of how the various ADO objects work. Still, it remains to be seen whether the way ADO is implemented in the wizard is optimal or not. If you worked with earlier versions of ADO, you had to marvel at its simplicity and, further, you had to scratch your head by how ADO.NET works. With this in mind, the broad issues to address are:

  • Identify and segregate UI and data-related code
  • Simplify the ADO.NET implementation
  • Create a reusable middle-tier component to encapsulate data-related functions
  • Create a UI to consume the services of the new data component

With the big issues identified, let's go about the task of improving upon what the Data Form Wizard left us!

When building multi-tier applications you want the user interface to do nothing more than display data to the user. Apart from instantiating the data object and interacting with the data object's interface, the UI should not know the details about how data is inserted, updated, deleted or selected. The only other task we want the form to do is data binding. This makes sense because the user accesses data through UI elements such as combo boxes, textboxes, checkboxes, etc.

As you might guess, the bulk of the work is offloaded from the form produced by the Data Form Wizard produced to a new data class that serves as a middle/data tier component. Near the end of this article, the manner in which the data form consumes the services of this new data object will be discussed.

Move over DataAdapter, Say Hello to the New Data Tier Component

In analyzing how ADO.NET is implemented, attention immediately focuses on the DataAdapter object. If you recall, the DataAdapter ties together the various insert, update, delete and select commands objects. In addition, the DataAdapter fills a dataset with data. The DataAdapter also takes care of invoking the various commands to update, insert, delete and select data. The downside is that you are surrendering control to what is essentially a black box. If you modify the code produced by the wizard, the DataAdapter may cease to operate correctly. As it turns out, the wizard-created DataAdapter does not do anything that you cannot do yourself with a little work. Since a lot can be accomplished with little work, you gain complete control of your development process. If given the choice, a professional developer would gladly pay a small price for maximum control. The new data tier component ties together the ADO connection, Command, Dataset and DataReader objects.

How the Data Tier Component Works

The following sections outline the code for the data tier component. When broken down in small digestible units, you can see that the operation of the new class is simple and, yes, ADO.NET does not have to be that complicated after all!

Before getting into the nitty-gritty details, let's take a moment to look at the specific ADO.NET objects the data tier component uses. Looking back at what the Data Form Wizard produced, you may recall objects like OleDbConnection, OleDbCommand, etc. These are the generic objects that can work with any OLE-DB provider. You may have noticed another namespace called System.Data.Sqlclient. This namespace contains a set of classes specifically designed and optimized to work with SQL Server. Since this class is being designed to use SQL Server, why not use objects that are specially tailored for the job? The objects that our new class use are called SqlConnection, SqlCommand and SqlDataReader. In addition, the new class uses the DataSet object. Since the DataSet is simply a vessel for holding data, a specialized SQL Server version of this object does not exist.

Initializing the Data Tier Component

As shown in Listing 1, the code establishes the class definition by defining two public properties and six private properties. As you can see, the class takes the place of the DataAdapter by coordinating the actions of the various commands. Because this is a custom class, a few additional commands are required to provide a list of items and to provide a blank record for new author entries. When the class initializes, the process culminates with setting up the connection and the various command objects. These private methods are discussed in the next section.

SetupConnection, SetupCommands, and CommandFactory Methods

The SetupConnection method, as detailed in Listing 2, is fairly straightforward. The method instantiates a new connection object, which is a member property of the class. The ConnectionString property is populated with specific information. Finally, the process culminates with opening the connection.

The SetupCommands method, as shown in Listing 3, makes individual calls to the CommandFactory method, shown in Listing 4. The argument passed to the CommandFactory method specifies how the CommandText property is constructed. By using a simple naming convention, you can generalize the process of constructing command objects. The tool used to create this feature is the SqlCommandBuilder class and, specifically, the DeriveParameters method. In earlier versions of ADO, you could populate the parameters collection by calling the Parameters Collection Refresh method. The DeriveParameters method of the SqlCommandBuilder class facilitates the same functionality in ADO.NET. The one caveat is that you must use the SqlClient objects in System.Data. Once the CommandObject has been made aware of the Connection object it is to use, along with the values specified in the TableName and KeyField properties, the data tier object is all set to go to work.

The Various Action Methods of the Data Tier Component

Once the data object has been instantiated, it waits for the user to make a request. The user in this case is the modified data form, which will be discussed later. You will begin to see a consistent pattern emerge throughout the following methods and, soon, it will gel very quickly.

GetBlank Method

The GetBlank method, as shown in Listing 5, does exactly what the name implies, it obtains an empty record for the table specified in the TableName property. Notice that the BlankCommand is invoked for this operation. The first step involves instantiating a new DataSet object. A new table is added to the DataSet. In this case, the table is the name specified in TableName. Once the table has been added, the ExecuteReader method of the BlankCommand object is invoked. This method produces a SqlDataReader object. The SqlDataReader object provides two important pieces of information: the information for the fields to be defined in the new table in the DataSet, and the data contained in the SqlDataReader itself.

The next block of code iterates through the SqlDataReader's field collection. The information in each field object defines a new field in the new DataSet table. Once the fields, more commonly known as columns, have been defined, the next step involves iterating through the reader via the SqlDataReader's Read method. The data in each row in the SqlDataReader is transferred to a new row in the DataSet table. This is essentially how the Fill method of the DataAdapter works. The good news is that, in this case, you don't need to worry about a complex set of table mappings that have to be in place for the DataAdapter to work properly!

GetData Method

When you wish to obtain data for a specific row in the Authors Table, the GetData method is invoked, as shown in Listing 6. This method accepts one parameter, which is the primary key value for a specific author. The GetData method is nearly identical to the GetBlank method. Just like GetBlank, GetData establishes a new DataSet, uses a Command object (the SelectCommand in this case) to obtain a SqlDataReader object. Instead of a blank record, the SqlDataReader contains data for a specific author. Like GetBlank, the information in the SqlDataReader is used to specify the columns and the data to add to the DataSet table.

UpdateData Method

The UpdateData method, as shown in Listing 7, accepts a DataSet as its sole parameter. Remember that the UI binds to and updates the data in the DataSet. The primary key information can easily be gleaned from the DataSet. The UpdateData method invokes the services of the UpdateCommand. How can we determine the mapping between fields and stored procedure parameters? It is quite simple, since the name of the field and the stored procedure are the same with the exception of the “@” character that prefaces SQL Server variables. By iterating through the fields in the DataSet table, each corresponding parameter in the UpdateData Command can be populated. Finally, when all of the parameters have been populated, the ExecuteNonQuery method is invoked. This method simply executes the command without returning a result set.

InsertData Method

The InsertData method, as detailed in Listing 8, works in nearly the same way as the UpdateData method. The primary difference is that the InsertCommand is used and a new entry into the Authors table is created.

DeleteData Method

The DeleteData method, as shown in Listing 9, is very simple. This method accepts the primary key of the author to delete and then invokes the DeleteCommand. It does not get much simpler than this!

GetList Method

The last method to discuss is the GetList method, as shown in Listing 10. This method invokes the ListCommand to obtain a SqlDataReader of data. Like the other methods, the SqlDataReader provides the column-related data as well as the core data. In other words, the SqlDataReader provides the foundation for the DataSet table.

The Data Tier Component, and a Few Final Thoughts

So do you now have a complete and optimized design? Certainly not! A good start? Absolutely! Because ADO.NET is so new, there is not a plethora of resources to consult as would be the case with a mature technology. In the end, the Data Tier Component presented here greatly improves on what the Data Form Wizard produces. Most importantly, it should help to simply and clarify how portions of ADO.NET work. The idea was to focus on those things an experienced ADO developer would be most interested in when it comes to ADO.NET. As for improving on the design, I'll leave that to you because that's the most fun for a software developer! With the Data Tier Component out of the way, let's conclude with taking a brief look at the data form.

The Improved Data Entry Form

Figure 2 illustrates the new and improved version of the Authors Data Entry Form. This version uses a drop-down combo box to navigate through the authors. In addition, this version toggles the enabled status of various controls based on whether the user is in add, edit or view mode.

Figure 2: The improved version of the Authors Data Entry Form.
Figure 2: The improved version of the Authors Data Entry Form.

The inner workings of the data form are actually quite simple. The following sections take you through the different portions of the code to give you a lay of the land.

Initializing the Form

A form property called AuthorData is created to hold a reference to the Data Tier Component discussed earlier. The form also has a property to hold a reference to a DataSet that holds the current author information. The DataSet is produced from the Data Tier Component. Finally, there is a form-level property to keep track of the current edit mode status. This data type is an enumerated type that can have the value of add, edit or view.

In the New procedure, as shown in Listing 11, the author drop-down combo box is populated, the mode is set to view and the ChangeMode Method is invoked. The ChangeMode is a form-level method that simply toggles the enabled status of the various form objects. That method is discussed in a few moments.

PopulateAuthorCombo Method

The PopulateAuthorCombo method, as shown in Listing 12, invokes the Data Component's GetList method, which returns a DataSet. With a few lines of code, the combo box can bind to the dataset. It is a matter of setting the DataSource, DisplayMember and ValueMember properties for the combo box. The only additional work involves saving the combo box's SelectedIndex property and restoring it, if necessary.

ChangeMode Method

The ChangeMode method, as shown in Listing 13, toggles the enabled status of a control based on the current mode (add, edit or view). Every time the user initiates an operation, the mode property is updated and a call is made to the ChangeMode method. In further illustrations, you'll see numerous calls to the ChangeMode method.

DisplayData Method

A call is made to the DisplayData method whenever the selected value of the combo box changes. The DisplayData method, as shown in Listing 14, gets the primary key information from the selected item in the combo box, and then passes that information to the Data Tier Component's GetData method. If you recall, the GetData method returns a DataSet that contains data of the selected author. The last step is a call to the CreateBindings method, which is discussed in the next section.

CreateBindings Method

Form elements like a textbox, combo box, checkbox, etc. can bind to elements in a DataSet. Specifically, a textbox can bind to a field which is contained in a table which in turn is contained in a DataSet. Each form element can support one active binding at a time. That is why the bindings are preemptively cleared at the beginning of the CreateBindings method, shown in Listing 15. Also, in order to refresh the data on the form, this method of clearing and re-establishing the bindings proved to be the easiest and most straightforward way of accomplishing the task. Once the bindings are cleared, you add a data binding to each object by specifying the object's property to display the data. For a textbox, the Text property is used to display and update the data. For a checkbox, the Selected property is used for data binding. You could specify the Text property, but then you would see the label text change and you would have no way of writing data back! In each case, you need to specify which DataSet as well as the table and field the data is located. As you'll see, it works like a charm!

WriteData and DeleteData Methods

When you click a command button, it invokes one of the form level methods. To review, whenever you pick an item from the Author combo box, the DisplayData method is invoked, which does the work of getting data from the Data Tier Component and binding the data to the various form controls. When you click the Save button, the WriteData method, as shown in Listing 16, is invoked. The Mode property determines whether the Data Tier Component's InsertData or UpdateData method is invoked. Regardless of which method is invoked, the DataSet holding the current and perhaps modified or new author information is passed as a parameter to the respective method. Once the data has been updated, a call is made to the PopulateAuthorCombo and DisplayData methods. Finally, the mode is reset to view, toggling the enabled status of the various form controls.

The DeleteData method, as shown in Listing 17, is fairly straightforward. The user is prompted to confirm whether the data should be deleted and, if the user answers yes, the Data Tier Component's DeleteData Method is invoked. Again, the PopulateAuthorCombo and DisplayData methods are invoked. This Delete button is only enabled in view mode, so there is no need to call the ChangeMode method.

What About Closing the Form with Pending Changes?

In earlier versions of Visual Basic, forms had a QueryUnload event that you could use to trap when the user pressed the Close box on the form toolbar. Like many things, there is a different way of doing the same thing in .NET. Windows forms support a Closing event that enables you to trap when user clicks the Close button. As seen in Listing 18, the form's Mode property is checked and if it is not View, the user is prompted to save or cancel their changes first. The code looks quite a bit different than the equivalent Visual Basic 6 code, but it accomplishes the same job.

The Stored Procedures

This solution implements six stored procedures in the Pubs SQL Server database. Those procedures are listed as follows:

  • sp_authors_blank: Returns an empty author record
  • sp_authors_delete: Deletes a specific author record
  • sp_authors_insert: Inserts a new author record
  • sp_authors_list: Obtains a list of authors
  • sp_authors_select: Selects data for a specific author
  • sp_authors_update: Updates data for a specific author

Please refer to the pubs.sql script file in the ZIP file that contains the source code for this article. Before working with the data tier component and data form, you must run the script. You can download this file from http://www.code-magazine.com/downloads/sepoct2002adodotnet.zip.

Summary

The first article of this series left off with the following three questions:

  • Which of the generated code is good?
  • Which of the generated code is bad?
  • How can the code be optimized?

When dealing with the first question you can conclude that not much was worth saving. That pretty much answers the second question, in that all the code was bad. Perhaps the underlying principle of the code was fine but the means used to carry out the intended result were not so good. In any case, this article has shown a method for how the code can be re-tooled and optimized. By implementing a component-based approach and by allocating the work to the proper tier, the mass of unintelligible and complex code was simplified. Hopefully for you, this has brought a sense of clarity to your understanding of ADO.NET and how data-aware forms can be implemented in .NET. By no means is this article meant to convey the notion that the solution discussed here is perfect or optimal, it is merely a first cut on how to employ a new technology. Hopefully, this solution gives you some ideas on how to employ component-based/database application development with Visual Basic, Visual Studio and ADO.NET.

Listing 1: The AuthorData Class

Imports System.Data.SqlClient
Public Class AuthorData

    Public TableName As String
    Public KeyField As String

    Private Connection As SqlConnection

    Private InsertCommand As SqlCommand
    Private UpdateCommand As SqlCommand
    Private DeleteCommand As SqlCommand
    Private SelectCommand As SqlCommand
    Private ListCommand As SqlCommand
    Private BlankCommand As SqlCommand

    Public Sub New()
        ' Initialize to look at authors table
        TableName = "Authors"
        KeyField = "au_id"

        ' setup and create the data objects
        SetupConnection()
        ' Setup the various command objects
        SetupCommands()
    End Sub

Listing 2: The SetupConnection() Method

Private Sub SetupConnection()
        Connection = New SqlConnection()
        With Connection
            .ConnectionString = _
              "integrated security = true;initial _ 
    catalog=pubs;data source=(local);Connect Timeout=30"
            .Open()
        End With
End Sub

Listing 3: The SetupCommands() Method

Private Sub SetupCommands()
        InsertCommand = CommandFactory("Insert")
        UpdateCommand = CommandFactory("Update")
        DeleteCommand = CommandFactory("Delete")
        SelectCommand = CommandFactory("Select")
        ListCommand = CommandFactory("List")
        BlankCommand = CommandFactory("Blank")
End Sub

Listing 4: The CommandFactory() Method

Private Function _ 
       CommandFactory(ByVal type As String) As SqlCommand
        ' Create a new command object
        CommandFactory = New SqlCommand()
        With CommandFactory
            ' Set the connection property
            .Connection = Connection
            ' Set the command type
            .CommandType = CommandType.StoredProcedure
            ' Set the command text based on the type
            .CommandText = "sp_" + TableName + "_" & type
        End With
        ' Derive the parameters using the 
        ' sqlcommandbuilder class
            SqlCommandBuilder.DeriveParameters(CommandFactory)
        Return CommandFactory
End Function

Listing 5: The GetBlank() Method

Public Function GetBlank() As DataSet
        Dim authorreader As SqlDataReader
        Dim item As Int16
        Dim newrow As DataRow

        GetBlank = New DataSet()
        GetBlank.Tables.Add(TableName)

        authorreader = BlankCommand.ExecuteReader
        For item = 0 To authorreader.FieldCount - 1
            GetBlank.Tables(0).Columns.Add(authorreader.GetName(item), authorreader.GetFieldType(item))
        Next

        While authorreader.Read
            newrow = GetBlank.Tables(0).NewRow
            For item = 0 To authorreader.FieldCount - 1
                newrow(item) = authorreader.Item(item)
            Next
            GetBlank.Tables(0).Rows.Add(newrow)
        End While
        authorreader.Close()

    End Function

Listing 6: The GetData() Method

Public Function GetData(ByVal id As String) As DataSet
        Dim authorreader As SqlDataReader
        Dim item As Int16
        Dim newrow As DataRow

        SelectCommand.Parameters("@" + KeyField).Value = id

        GetData = New DataSet()
        GetData.Tables.Add(TableName)

        authorreader = SelectCommand.ExecuteReader
        For item = 0 To authorreader.FieldCount - 1
            GetData.Tables(0).Columns.Add(authorreader.GetName(item), authorreader.GetFieldType(item))
        Next

        While authorreader.Read
            newrow = GetData.Tables(0).NewRow
            For item = 0 To authorreader.FieldCount - 1
                newrow(item) = authorreader.Item(item)
            Next
            GetData.Tables(0).Rows.Add(newrow)
        End While
        authorreader.Close()
    End Function

Listing 7: The UpdateData() Method

Public Function UpdateData(ByVal CurrentData As DataSet) As Boolean
        Dim item As Int16
        Dim column As DataColumn
        For Each column In CurrentData.Tables(0).Columns
            UpdateCommand.Parameters("@" + column.ColumnName).Value = CurrentData.Tables(0).Rows(0).Item(column.ColumnName)
        Next column
        UpdateCommand.ExecuteNonQuery()

    End Function

Listing 8: The InsertData() Method

Public Function InsertData(ByVal CurrentData As DataSet) As Boolean
        Dim item As Int16
        Dim column As DataColumn
        For Each column In CurrentData.Tables(0).Columns
            InsertCommand.Parameters("@" + column.ColumnName).Value = CurrentData.Tables(0).Rows(0).Item(column.ColumnName)
        Next column
        InsertCommand.ExecuteNonQuery()
    End Function

Listing 9: The DeleteData() Method

Public Function DeleteData(ByVal id As String) As Boolean
        DeleteCommand.Parameters("@" + KeyField).Value = id
        DeleteCommand.ExecuteNonQuery()
    End Function

Listing 10: The GetList() Method

Public Function GetList() As DataSet
        Dim AuthorList As SqlDataReader
        Dim item As Int16
        Dim newrow As DataRow
        GetList = New DataSet()
        GetList.Tables.Add(TableName + "List")
        AuthorList = ListCommand.ExecuteReader
        For item = 0 To AuthorList.FieldCount - 1
            GetList.Tables(0).Columns.Add(AuthorList.GetName(item), AuthorList.GetFieldType(item))
        Next
        While AuthorList.Read
            newrow = GetList.Tables(0).NewRow
            For item = 0 To AuthorList.FieldCount - 1
                newrow(item) = AuthorList.Item(item)
            Next
            GetList.Tables(0).Rows.Add(newrow)
        End While
        AuthorList.Close()
    End Function 
End Class

Listing 11: The frmPubsAuthors Class

Public Class frmPubsAuthors
    Inherits System.Windows.Forms.Form
    Public AuthorData As New AuthorData()
    Public CurrentAuthor As DataSet
    Public Mode As editmode
#Region " Windows Form Designer generated code "

Public Sub New()
        MyBase.New()
        'This call is required by the Windows Form Designer.
        InitializeComponent()
        ' populate the combobox so the user can navigate through authors
        populateauthorcombo()
        Mode = editmode.View
        ChangeMode(editmode.View)
End Sub

Listing 12: The PopulateAuthorCombo() Method

Private Sub PopulateAuthorCombo()
        Dim authorlist As DataSet
        Dim selectedindex As Int16
        selectedindex = cboAuthorList.SelectedIndex
        authorlist = AuthorData.GetList()
        With cboAuthorList
            .DataSource = authorlist.Tables(0)
            .DisplayMember = authorlist.Tables(0).Columns(0).ColumnName
            .ValueMember = authorlist.Tables(0).Columns(1).ColumnName
        End With
        If selectedindex < 0 Then
            selectedindex = 0
        End If
        cboAuthorList.SelectedIndex = selectedindex
End Sub

Listing 13: The ChangeMode() Method

Public Sub ChangeMode(ByVal newmode As editmode)
     Mode = newmode
     cboAuthorList.Enabled = newmode = editmode.View

     txtau_id.Enabled = newmode = editmode.Add

     txtau_lname.Enabled = newmode <> editmode.View
     txtau_fname.Enabled = newmode <> editmode.View
     txtphone.Enabled = newmode <> editmode.View
     txtaddress.Enabled = newmode <> editmode.View
     txtCity.Enabled = newmode <> editmode.View
     txtState.Enabled = newmode <> editmode.View
     txtZip.Enabled = newmode <> editmode.View
     chkContract.Enabled = newmode <> editmode.View

     cmdEdit.Enabled = newmode = editmode.View
     cmdExit.Enabled = newmode = editmode.View
     cmdNew.Enabled = newmode = editmode.View
     cmdDelete.Enabled = newmode = editmode.View

     cmdCancel.Enabled = newmode <> editmode.View
     cmdSave.Enabled = newmode <> editmode.View

End Sub

Listing 14: The DisplayData() Method

Public Sub DisplayData()
     Dim rownumber As Int16
     Dim selectedauthor As DataTable
     Dim id As String
     rownumber = cboAuthorList.SelectedIndex
     selectedauthor = cboAuthorList.DataSource
     id = selectedauthor.Rows(rownumber).Item(AuthorData.KeyField)
     CurrentAuthor = AuthorData.GetData(id)
     CreateDataBindings()
End Sub

Listing 15: The CreateDataBindings() Method

Public Sub CreateDataBindings()
   txtau_id.DataBindings.Clear()
   txtau_fname.DataBindings.Clear()
   txtau_lname.DataBindings.Clear()
   txtaddress.DataBindings.Clear()
   txtCity.DataBindings.Clear()
   txtState.DataBindings.Clear()
   txtZip.DataBindings.Clear()
   txtphone.DataBindings.Clear()
   chkContract.DataBindings.Clear()

   txtau_id.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.au_id"))

   txtau_lname.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.au_lname"))

   txtau_fname.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.au_fname"))

   txtaddress.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.address"))

   txtCity.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.city"))

   txtState.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.state"))

   txtZip.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.zip"))

   txtphone.DataBindings.Add(New System.Windows.Forms.Binding("Text", CurrentAuthor, "authors.phone"))

   chkContract.DataBindings.Add(New System.Windows.Forms.Binding("Checked", CurrentAuthor, "authors.contract"))
End Sub

Listing 16: The WriteData() Method

Public Sub WriteData(ByVal CurrentData As DataSet)
        Dim id As String
        id = txtau_id.Text
        Select Case Me.Mode
            Case editmode.Add
                AuthorData.InsertData(CurrentAuthor)
            Case editmode.Edit
                AuthorData.UpdateData(CurrentAuthor)
        End Select
        populateauthorcombo()
        cboAuthorList.SelectedValue = id
        DisplayData()
        ChangeMode(editmode.View)
    End Sub

Listing 17: The DeleteData() Method

Public Sub DeleteData(ByVal id As String)
If MsgBox("Are you sure you wish to delete this author?", MsgBoxStyle.YesNo, Text) = MsgBoxResult.Yes Then
            AuthorData.DeleteData(id)
            cboAuthorList.SelectedIndex = cboAuthorList.SelectedIndex - 1
            populateauthorcombo()
   End If
End Sub

Listing 18: The Form Close Event

Private Sub frmPubsAuthors_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
   If Mode <> editmode.View Then
   MsgBox("Please save or cancel your changes before exiting.", MsgBoxStyle.Information, Text)
            e.Cancel = True
        End If
End Sub