Using ADO.NET

 

Paul D. Sheriff
PDSA, Inc.

January 2002

Summary: Discusses using ADO.NET Connection object, other ADO.NET objects, and provides examples. (41 printed pages)

Objectives

  • Learn to create a Microsoft® ADO.NET Connection object
  • Submit SQL through an ADO.NET Command object
  • Use the ADO.NET DataReader class
  • Learn to load objects into a list box
  • Use DataTable objects and DataSet objects
  • Modify data using DataSet objects

Assumptions

The following should be true for you to get the most out of this document:

  • You understand relational databases
  • You are familiar with ADO
  • You understand how to build Microsoft Windows® Forms
  • You are familiar with and have access to Microsoft SQL Server™ or some other database, like Microsoft Access

Contents

ADO.NET Connection
The ADO.NET Command Object
Using the ADO.NET DataReader
Using ADO.NET DataTables and DataSets
What's Different Between Visual Basic 6.0 and ADO?
Summary

ADO.NET Connection

You use the ADO.NET Connection object to create a connection between your program and a database engine. You will normally keep this connection open just long enough to retrieve or update data. By quickly opening, then closing a connection, you use server resources for as little time as possible. This helps you develop scalable, fast applications that are resource-friendly. The fewer resources you use, the more users you can support on your applications at one time.

If you are creating a database application, you will eventually need to open a connection to that database. An ADO.NET Connection object allows you to create that connection. You will also need to retrieve and modify data in the database, and that is where you will need to use the ADO.NET Command object.

When connecting to SQL Server 7.0 or greater, you use the SqlConnection and SqlCommand objects in the System.Data.SqlClient namespace. When connecting to other OLE DB datasources, use the OleDbConnection and OleDbCommand in the System.Data.OleDb namespace. The rest of the examples in this document show examples using the objects from the System.Data.SqlClient namespace.

To modify data within a database

  1. Create a SqlConnection or an OleDbConnection object and give it a connection string.
  2. Open the connection to the database.
  3. Create a SqlCommand or an OleDbCommand object and assign to it the connection object you opened.
  4. Place an SQL statement into the Command object.
  5. Execute the SQL statement by invoking the ExecuteNonQuery method on the Command object.

Connection and Command Objects

In this document, you will learn to create and open a connection to a SQL Server database using a SqlConnection class. In addition, you will learn to submit an INSERT statement to the same SQL Server database using the SqlCommand object.

Follow the steps below to learn how to open a connection to a SQL Server database. Figure 1 shows the sample form that you will use to test your connections. Create this form by following the steps outlined below.

Figure 1: Submitting SQL statements to a SQL Server database

To create the sample form

  1. Open Microsoft Visual Studio® .NET.

  2. Click New Project.

  3. From the tree view on the left, select Visual Basic Projects.

  4. From the project templates window, select Windows Application.

  5. Set the Name to DataConnect.

  6. Click OK.

  7. Rename the form called Form1.vb to frmConnect.vb.

  8. Set the Text property on this form to SQL Tester.

  9. Create the controls for this form by referring to Table 1.

    Table 1. Controls to build the form to test the ADO.NET objects

    Control Type Property Value
    CommandButton Name btnConnect
      Text Connect
    TextBox Name txtSQL
      MultiLine True
    Label Name Label1
      Text Rows Affected
    TextBox Name txtRows
      Text  
      ReadOnly True
    CommandButton Name btnExecute
      Text Execute SQL

Now let's write some code to make a connection to the database.

  1. Double-click Connect.

  2. Add the code shown below to the btnConnect_Click event procedure.

    Private Sub btnConnect_Click( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles btnConnect.Click
        Dim oConn As SqlClient.SqlConnection
        Dim strConn As String
    
        Try
            ' Create the Connection object
            oConn = New SqlClient.SqlConnection()
    
            ' Build the connection string      
            strConn &= "Data Source=(local);"
            strConn &= "Initial Catalog=Northwind;"
            strConn &= "User ID=sa;"
            strConn &= "Password=;"
    
            ' Set the Connection String
            oConn.ConnectionString = strConn
    
            ' Open the Connection
            oConn.Open()
    
            MessageBox.Show("Connection Open", _
             "btnConnect_Click()")
    
            ' Close the Connection
            oConn.Close()
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message, _
             "btnConnect_Click()")
    
        End Try
    
    End Sub
    

In this event procedure, you first create a new instance of a SqlConnection class. Then you fill in the ConnectionString property prior to opening the connection.

In previous versions of ADO, you were allowed to set the ConnectionTimeout property to a value from 0 to n. This represented the time to wait for a connection to be made before an exception was thrown. In ADO.NET, this property is read-only. To set the ConnectionTimeout, pass Connect Timeout=n in the provider string. In addition, you no longer set the individual properties such as DataSource, Database, etc., as properties on the SqlConnection object. These values are now read-only, and reflect the values that are parsed from the provider string.

Connection Strings

You next create a connection string that points to a SQL Server database. A connection string has a set of semi-colon-separated attributes. Each .Net Data Provider connection string looks different, depending on the type of .NET Data Provider you need to use and which attributes are set for each different type of database system. For example, the connection string below is an example of what you use to connect to a local SQL Server.

Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=;

The connection string shown below is an example of how you would connect to a Microsoft Access 2000 database using the OleDbConnection object in System.Data.OleDb.

Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Northwind.mdb

Note If you are using a different database engine, you will need to look up the appropriate attributes to set for your particular engine. Consult the ADO.NET Help for more information on setting the connection string for different providers.

Create this connection string in the string variable strConn, and then assign that variable to the ConnectionString property of the Connection object.

After the ConnectionString property is set, invoke the Open method on the Connection object. This causes the connection object to load the specified provider and open a connection to the data source. If supplied, the User ID and Password attributes are used to log into the data source. The Initial Catalog attribute tells the connection to make the default database the one specified in this attribute.

After you are finished with the connection, you should always close it. In the example code you just typed in, you simply open a connection and close it right away. Typically, you would perform some operation on the connection prior to closing it. This will be shown in the next section.

Try It Out

  1. To run the project, press F5.
  2. Click Connect to run the code you just typed in. If everything is set correctly, you should see a message box informing you that the connection was opened.

Now that you know how to open a connection, you probably want to do something with it. Unlike previous versions of ADO, ADO.NET does not allow execution of SQL statements directly on a connection object. To submit SQL statements, you always use a Command object.

The ADO.NET Command Object

The Command object is very similar to the old ADO command object. It is used to store SQL statements that need to be executed against a data source. The Command object can execute SELECT statements, INSERT, UPDATE, or DELETE statements, stored procedures, or any other statement understood by the database. In Figure 1 you saw an example of an INSERT statement that you could execute against the Northwind database to add a row to the Customers table. You will now learn to write the code that will execute that INSERT statement.

  1. Open the frmConnect.vb form.

  2. Double-click Execute SQL.

  3. Write the code shown below in the btnExecute_Click event procedure.

    Private Sub btnExecute_Click(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles btnExecute.Click
        Dim oCmd As SqlClient.SqlCommand
        Dim strConn As String
    
        Try
            ' Build the connection string
            strConn &= "Data Source=(local);"
            strConn &= "Initial Catalog=Northwind;"
            strConn &= "User ID=sa;"
            strConn &= "Password=;"
    
            ' Create the Command Object
            oCmd = New SqlClient.SqlCommand()
            ' Assign Connection to Command Object
            oCmd.Connection = _
             New SqlClient.SqlConnection(strConn)
            ' Open the Connection
            oCmd.Connection.Open()
            ' Assign the SQL to the Command Object
            oCmd.CommandText = txtSQL.Text
            ' Execute the SQL, 
            ' Return Number of Records Affected
            txtRows.Text = _
             oCmd.ExecuteNonQuery().ToString()
    
            MessageBox.Show("SQL statement succeeded", _
             "btnExecute_Click()")
    
            ' Close the Connection      
            oCmd.Connection.Close()
    
        Catch oExcept As Exception
            txtRows.Text = 0.ToString()
            MessageBox.Show("Error executing SQL: " & _
             oExcept.Message, "btnExecute_Click()")
    
        End Try
    End Sub
    

In the btnExecute_Click event procedure, you need to declare a variable named oCmd that references a Command object. You then create a connection string used to create a connection to the data source. Next, you instantiate the Command object and set its Connection property to a New Connection object. Once you have created this new Connection object, you can open the connection.

After the connection is open, you can fill in the CommandText property with the SQL statement you wish to submit to the database. In this case, the SQL statement comes from the Text Box on the form.

You invoke the ExecuteNonQuery method of the command object to submit the SQL to the backend database. Use the ExecuteNonQuery method when executing a command that does not return results, such as an INSERT, UPDATE or DELETE statement. These types of queries do not return any rows as a result set, they simply modify data and return the number of rows affected. This method returns the number of rows affected by the SQL statement submitted. You convert this value to a string so it can be placed into the Text property of the txtRows text box. Finally, you close the connection using the Close method on the Connection property.

Note Unlike in ADO, where you could wait until the Connection object went out of scope for the connection to be closed, in ADO.NET you should always explicitly close the connection as shown in this procedure. Calling Close() on the Connection will release the database connection back to the pool for some other procedure to reuse as soon as possible.

Try it Out

You can try out this new button by writing an INSERT, UPDATE, or DELETE statement against one of the tables in the Northwind database and submitting that to the database.

  1. Run the program by pressing F5.
  2. Type in a valid INSERT, UPDATE, or DELETE statement. You can use the INSERT statement that is already placed into the text box as an example, if you like.
  3. Click Execute SQL.

If everything works, you should see the number of rows affected show up in the text box that you created to the left of the button.

Using the ADO.NET DataReader

In ADO.NET, you no longer have a Recordset. Instead, you have new objects such as DataSets, DataTables, and DataReaders that will be used to retrieve records from data sources. Next, you will learn about the DataReader object. You will learn about DataTables and DataSets later in this document.

The DataReader object is a forward-only type of cursor that provides the fastest way to retrieve records from a data source. Because its direction is limited to forward-only, it provides great performance for programmatically processing results or loading list boxes, combo boxes, etc.

Load a List Box Using the DataReader

In this section you will load a list box with data from the Products table using the DataReader object. In addition, you will retrieve a single record after clicking the list box. Figure 2 shows a simple data entry screen that you might use to display, add, edit, and delete product information.

Figure 2: A typical Client/Server data entry screen

Follow the steps below to load the list box with the names of all of the products in the Products table in the Northwind database.

  1. Create a form that looks like Figure 2. Set the Name of this form to frmProducts.vb. The name of the list box is lstProducts.

  2. In Solution Explorer, double-click the frmProducts.vb file to display the products form.

  3. Double-click anywhere on the form (make sure you are not clicking on a control). The frmProduct_Load event procedure will now be displayed in the code window.

  4. Within this Load event procedure, make a call to the ListLoad procedure that you are going write in the next step. Your procedure should look something like the code shown below.

    Private Sub frmProduct_Load( _ 
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
            ListLoad()
    End Sub
    
  5. Create the ListLoad procedure just below the End Sub of the frmProduct_Load event procedure, and type the following code into this ListLoad procedure.

    Private Sub ListLoad()
        Dim oCmd As SqlClient.SqlCommand
        Dim oDR As SqlClient.SqlDataReader
        Dim strSQL As String
        Dim strConn As String
    
        strConn = ConnectStringBuild()
    
        strSQL = "SELECT ProductName "
        strSQL &= "FROM Products"
    
        Try
            oCmd = New SqlClient.SqlCommand()
            With oCmd
                .Connection = _
                 New SqlClient.SqlConnection(strConn)
                .Connection.Open()
                .CommandText = strSQL
                oDR = .ExecuteReader()
            End With
    
            lstProducts.Items.Clear()
            Do While oDR.Read()
                lstProducts.Items.Add(oDR.Item("ProductName"))
            Loop
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
    
        End Try
    End Sub
    

The code above declares two objects, one for the Command and one for the DataReader. The Command object holds and executes the SELECT statement to send to the data source. The DataReader is the object that retrieves the data from the result set that comes back from the SELECT statement.

Set the Connection property on the Command object to a new instance of a Connection object. You pass a connection string to this new Connection object; you will build your own ConnectStringBuild function later in this section. After the proper connection string is set, you can open the connection on the command object.

Place the SELECT statement into the CommandText property. When you invoke the ExecuteReader method, the command object submits the SELECT statement to the back end data source. The result is returned and the DataReader object is given a pointer to this result set. The cursor is set just before the first record in this result set.

You will loop through each of the rows in the DataReader by invoking the Read method. The Read method moves the cursor from one row to the next. After the Read method has executed, you can pass the name of the column you wish to retrieve to the Item property on the DataReader. This returns the actual data from that column.

The data returned from the Item property comes back as an Object data type. Because the items you add to the list box are of type Object, no conversion is required when using the Add method of the Items collection on the list box.

In this manner, you continue looping until the Read method returns a False. This means you have hit the end of the rows of data that were returned from the SELECT statement.

Build the Connection String

Before you can try out this routine to see if the data is loaded correctly into the list box, you will need to build the connection string.

  1. Right after the End Sub of the ListLoad procedure, add a new procedure called ConnectStringBuild.

  2. Type in the code below to create this connection string. You may need to change the connection string if you are using a different server name, or a different database engine.

    Private Function ConnectStringBuild() As String
        Dim strConn As String
    
        strConn &= "Data Source=(local);"
        strConn &= "Initial Catalog=Northwind;"
        strConn &= "User ID=sa"
    
        Return strConn
    End Function
    
  3. Press F5 to run this application.

If you typed everything in correctly, you should see a list of products in the list box.

Issues With the Code

One of the problems with the above code is that you could have duplicate product names in your table. If this is the case, there is no mechanism for you to store a primary key into this list box so you can uniquely identify which record is which in the list. Unlike Microsoft Visual Basic® 6.0, the ListBox in .NET does not have an ItemData property. ItemData was used to store a long integer data type that was tied to the text in the text box. .NET has eliminated the ItemData property. However, there is a much better method to handle this type of situation: storing the object in the ListBox itself.

Store an Object Into the ListBox

The key to making the connection string code work is to create a class with enough properties to hold the data that you wish to place into the list box. One property of this class is used to display the data in the text portion of the list box. The other property, or properties, will be used to hold primary key information. For the Product table, you need one property for the ProductName column and one property for the ProductID column. In fact, for many tables, you will just need two properties like this.

**Tip   **Create a generic class with two properties called Value and ID that you can re-use for loading any list box or combo box with a description and a primary key value.

Create a Generic ListItem Class

Let's add a new file to your project. This new file contains the class definition for your generic list item class.

  1. On the Project menu, click Add Class… to add a new class to your project.

  2. Give it a name, such as clsListItems.vb.

  3. Create a class like that shown in the code below.

    Public Class PDSAListItemNumeric
        Private mstrValue As String
        Private mintID As Integer
    
        Public Sub New()
    
        End Sub
    
        Public Sub New(ByVal strValue As String, _
         ByVal intID As Integer)
            mstrValue = strValue
            mintID = intID
        End Sub
    
        Property Value() As String
            Get
                Return mstrValue
            End Get
            Set(ByVal Value As String)
                mstrValue = Value
            End Set
        End Property
    
        Property ID() As Integer
            Get
                Return mintID
            End Get
            Set(ByVal Value As Integer)
                mintID = Value
            End Set
        End Property
    
        Public Overrides Function ToString() As String
            Return mstrValue
        End Function
    End Class
    

The above class has two properties, Value and ID, which will be used to hold the text and primary key for any table that you place into a list box. For any class that you intend to place into the Items collection on a list control, you must override the ToString method. ToString is a method on the default Object data type that from which all classes automatically inherit. When the list box displays items in its Items collection, it will always call the ToString method to retrieve the data. In the ToString method of this class, you return the Value property, as that is the value you wish to display in the list box.

Use the New Class in ListLoad

Now that you have created this new generic class, let's use it in the ListLoad procedure. This time, instead of adding the ProductName directly from the DataReader, add the ProductName and ProductID to a new instance of your PDSAListItemNumeric class and add this object to the list box.

  1. Change the code in the ListLoad procedure you created earlier so it looks like the code shown below.

    Private Sub ListLoad()
        Dim oCmd As SqlClient.SqlCommand
        Dim oDR As SqlClient.SqlDataReader
        Dim oItem As PDSAListItemNumeric
        Dim strSQL As String
        Dim strConn As String
    
        strConn = ConnectStringBuild()
    
        strSQL = "SELECT ProductID, ProductName "
        strSQL &= "FROM Products"
    
        Try
            oCmd = New SqlClient.SqlCommand()
            With oCmd
                .Connection = _
                 New SqlClient.SqlConnection(strConn)
                .Connection.Open()
                .CommandText = strSQL
                oDR = _
                 .ExecuteReader()
            End With
    
            lstProducts.Items.Clear()
            Do While oDR.Read()
                oItem = New PDSAListItemNumeric()
                With oDR
                    oItem.ID = CInt(.Item("ProductID"))
                    oItem.Value = _
                     .Item("ProductName").ToString()
                End With
    
                lstProducts.Items.Add(oItem)
            Loop
            If lstProducts.Items.Count > 0 Then
                lstProducts.SetSelected(0, True)
            End If
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
    
        End Try
    End Sub
    

The major change you made to this routine is that you added the ProductID column to your SELECT statement, and then added the instantiation of a new oItem object inside the Read loop. Each time you read a new record, you create a new PDSAListItemNumeric object, store the ProductID into the ID property and the ProductName into the Value property. You then add this new object to your list box.

Try It Out

Now that you have changed this code to use a PDSAListItemNumeric class, run the project to make sure you typed everything in correctly. Press F5 to see if you are still getting Products loaded into your list box.

Displaying Product Detail Information

Once you have placed these objects into the list box, you can click an item in the list box to retrieve that object and get the ID and Value properties. When you click an item in a list box, the SelectedIndexChanged event procedure will fire. Within this event, you can write code to call another subroutine that you will write next, called FormShow.

  1. Bring up the Products form in design view mode.

  2. Double-click the list box to display the SelectedIndexChanged event procedure.

  3. Add a call to a routine named FormShow.

    Private Sub lstProducts_SelectedIndexChanged( _
     ByVal sender As Object, ByVal e As System.EventArgs) _
     Handles lstProducts.SelectedIndexChanged
        FormShow()
    End Sub
    

Now create this FormShow procedure after the End Sub statement of the above event procedure. FormShow retrieves the PDSAListItemNumeric object from the list box and builds a SELECT statement to retrieve all of the columns from the Products table for the particular item selected. It then builds a DataReader object of that one row, reads the data from the data source, and puts all of the detail information for that product into the appropriate text boxes on this form.

  1. Create the FormShow procedure within this form.

  2. Type in the code shown below.

    Private Sub FormShow()
        Dim oCmd As SqlClient.SqlCommand
        Dim oDR As SqlClient.SqlDataReader
        Dim oItem As PDSAListItemNumeric
        Dim strSQL As String
        Dim strConn As String
    
        strConn = ConnectStringBuild()
    
        ' Get Primary Key From List Box
        oItem = CType(lstProducts.SelectedItem, _
         PDSAListItemNumeric)
    
        strSQL = "SELECT ProductID, ProductName, "
        strSQL &= " QuantityPerUnit, UnitPrice, "
        strSQL &= " UnitsInStock, UnitsOnOrder, "
        strSQL &= " ReorderLevel, Discontinued "
        strSQL &= " FROM Products "
        strSQL &= " WHERE ProductID = " & oItem.ID
    
        Try
            oCmd = New SqlClient.SqlCommand()
            With oCmd
                .Connection = _
                 New SqlClient.SqlConnection(strConn)
                .Connection.Open()
                .CommandText = strSQL
                oDR = .ExecuteReader()
            End With
    
            If oDR.Read() Then
                With oDR
                    txtID.Text = .Item("ProductID").ToString()
                    txtName.Text = _
                     .Item("ProductName").ToString()
                    txtQty.Text = _
                     .Item("QuantityPerUnit").ToString()
                    txtPrice.Text = _
                     .Item("UnitPrice").ToString()
                    txtInStock.Text = _
                     .Item("UnitsInStock").ToString()
                    txtOnOrder.Text = _
                     .Item("UnitsOnOrder").ToString()
                    txtReorder.Text = _
                     .Item("ReorderLevel").ToString()
                    chkDisc.Checked = _
                     CType(.Item("Discontinued"), Boolean)
                End With
            End If
            oDR.Close()
            oCmd.Connection.Close()
    
        Catch oException As Exception
            MessageBox.Show(oException.Message)
    
        End Try
    End Sub
    

There's not too much new in the FormShow procedure as you are using the same kind of coding you created in the ListLoad procedure. Retrieve the PDSAListItemNumeric object from the SelectedItem property of the list box and place it into the variable oItem.

oItem = CType(lstProducts.SelectedItem, _
 PDSAListItemNumeric)

The CType function converts one data type to another data type. In this case, you are converting an Object type into a PDSAListItemNumeric type. All items placed into the Items collection of a list box are stored as generic Object types.

The rest of the procedure creates a Command object, opens a connection and creates a DataReader object to read the one record from the data source. It then takes each of the columns and places the data into the appropriate text boxes.

Try It Out

Now that you have created the FormShow procedure, try it out to make sure it works.

  1. Start the application by pressing F5.
  2. Click an entry in the list box to make sure that the detail information for that particular product shows up in the text boxes on the form.

Don't worry about the combo boxes for Supplier and Category; you will learn how to work with those controls in the next section.

Loading Combo Boxes

On the Product form, there are two combo boxes that need to be loaded: Categories and Suppliers. Both can be loaded just like you load a list box. Use a PDSAListItemNumeric class to load both the primary key and the text for the combo box. For the Categories table, select the columns CategoryID and CategoryName. For the Suppliers table, select the columns SupplierID and CompanyName. In the Products table, the CategoryID and SupplierID columns are the foreign keys into the Categories and Suppliers tables, respectively.

  1. Create the CategoryLoad procedure within this form.

  2. Type in the code shown below in the form's code window.

    Private Sub CategoryLoad()
        Dim oCmd As SqlClient.SqlCommand
        Dim oDR As SqlClient.SqlDataReader
        Dim strSQL As String
        Dim strConn As String
        Dim oItem As PDSAListItemNumeric
    
        strConn = ConnectStringBuild()
    
        strSQL = "SELECT CategoryID, CategoryName "
        strSQL &= "FROM Categories"
    
        Try
            oCmd = New SqlClient.SqlCommand()
            With oCmd
                .Connection = _
                 New SqlClient.SqlConnection(strConn)
                .Connection.Open()
                .CommandText = strSQL
                ' Closes connection when 
                ' closing DataReader object
               oDR = .ExecuteReader( _
                CommandBehavior.CloseConnection)
            End With
    
            Do While oDR.Read()
                oItem = New PDSAListItemNumeric()
                With oDR
                    oItem.ID = CInt(.Item("CategoryID"))
                    oItem.Value = _
                     .Item("CategoryName").ToString()
                End With
    
                cboCategory.Items.Add(oItem)
            Loop
            oDR.Close()
            ' No need to close this because of the 
            '.CloseConnection on the ExecuteReader
            'oCmd.Connection.Close()
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
    
        End Try
    End Sub
    

There is one difference between this routine and the ListLoad procedure you created earlier: on the ExecuteReader method, you passed in a new constant, CloseConnection. This parameter tells the DataReader class to close the connection when it is finished loading the list. This means that you don't have to make an explicit call to the Close method on the command object's Connection property.

Create the SupplierLoad Procedure

Create the SupplierLoad procedure just like the CategoryLoad procedure. In fact, you can copy and paste the CategoryLoad procedure back into the form and just change the name.

  1. Copy the CategoryLoad procedure into the clipboard, then paste a copy of it just below the CategoryLoad procedure.
  2. Change the name of the second procedure to SupplierLoad.
  3. Change the appropriate column and table names in the SELECT statement. The columns in the Suppliers table to use are SupplierID and CompanyName.
  4. Change all references to the cboCategory combo box in this procedure to use the cboSupplier combo box.

Call These Procedures When Loading the Form

You need to add a call to these procedures from the frmProducts_Load event procedure.

  • Change the frmProduct_Load event procedure to look like the following.

    Private Sub frmProduct_Load( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        ' Load Suppliers
        SupplierLoad()
        ' Load Categories
        CategoryLoad()
        ' Load List Box of Products
        ListLoad()
    End Sub
    

Try It Out

Run the program to make sure that these routines are indeed loading the data into the appropriate combo boxes on the screen. Press F5 to see if the category and supplier combo boxes got loaded.

Finding Values in Combo Boxes

Now that you have the category and supplier combo boxes loaded, you need to be able to position the combo boxes to the appropriate values whenever you click a new product in the list box. For example, when the CategoryID value in the Products table gets read in via the DataReader object in the FormShow procedure, you need to find that CategoryID within the objects that are loaded into the Category combo box. Let's try it.

  1. Change the FormShow procedure and add a Dim statement at the beginning of the routine with a variable named strID.

    Dim strID As String
    
  2. Change the FormShow procedure and add the CategoryID and SupplierID columns to the SELECT statement.

  3. Find the section of code shown below and add the line that is highlighted in bold.

    strSQL = "SELECT ProductID, ProductName, "
    strSQL &= " SupplierID, CategoryID, "
    strSQL &= " QuantityPerUnit, UnitPrice, "
    strSQL &= " UnitsInStock, UnitsOnOrder, "
    strSQL &= " ReorderLevel, Discontinued "
    strSQL &= " FROM Products "
    strSQL &= " WHERE ProductID = " & oItem.ID
    
  4. Find the section of code in FormShow that loads the data from the DataReader into the text boxes, and add the following lines that are in bold.

    txtID.Text = .Item("ProductID").ToString()
    txtName.Text = .Item("ProductName").ToString()
    strID = .Item("SupplierID").ToString()Call FindItem(cboSupplier, strID)strID = .Item("CategoryID").ToString()
    Call FindItem(cboCategory, strID)
    txtQty.Text = .Item("QuantityPerUnit").ToString()
    txtPrice.Text = .Item("UnitPrice").ToString()
    txtInStock.Text = .Item("UnitsInStock").ToString()
    txtOnOrder.Text = .Item("UnitsOnOrder").ToString()
    txtReorder.Text = .Item("ReorderLevel").ToString()
    
  5. Next, create the FindItem function that accepts a combo box reference and a string variable. This function finds that string variable within the ID property of the objects in the combo box and sets the combo box to display the data located in that position.

    Private Sub FindItem(ByVal cboCombo As ComboBox, _
     ByVal strID As String)
        Dim intLoop As Integer
        Dim boolFound As Boolean
        Dim oItem As PDSAListItemNumeric
    
        oItem = New PDSAListItemNumeric()
        For intLoop = 0 To cboCombo.Items.Count - 1
            oItem = CType(cboCombo.Items(intLoop), _
             PDSAListItemNumeric)
            If oItem.ID = CInt(strID) Then
                cboCombo.SelectedIndex = intLoop
                boolFound = True
                Exit For
            End If
        Next
        If Not boolFound Then
            cboCombo.SelectedIndex = -1
        End If
    End Sub
    

This FindItem function loops through all of the values in the combo box, removing an item from the combo box each time through, and converting it to a PDSAListItemNumeric object. You can compare the ID property of the returned object to see if it is equal to the value that is passed in as the second parameter to this function. If it finds this value, it will set the SelectedIndex property to this location, which forces the combo box to position itself to that value.

Performance Issues

You can further enhance performance for certain situations by passing in a different CommandBehavior constant to the ExecuteReader method on the Command object. You are allowed to specify KeyInfo*,* which reads in only primary key information. You can specify SchemaOnly to read in just the column schema information with no data attached. You can specify SingleColumn if you are only returning an aggregate value. Or, if you know that only one row will be returned, specify SingleRow to get even better performance in this situation.

Modifying Data

You also need to create procedures for this client/server type of application to add, edit, and delete data. Use the Command object to submit these INSERT, UPDATE, and DELETE statements via the ExecuteNonQuery method of this object. As an example, here is the code to write for updating the data in this screen.

Private Sub DataUpdate()
    Dim oCmd As SqlClient.SqlCommand
    Dim strSQL As String
    Dim intRows As Integer

    strSQL = "UPDATE Products SET "
    strSQL &= "ProductName = " & _
     Str2Field(txtName.Text) & ", "
    strSQL &= "SupplierID = " & _
     CType(cboSupplier.Items(cboSupplier.SelectedIndex), _
     PDSAListItemNumeric).ID & ", "
    strSQL &= "CategoryID = " & _
     CType(cboCategory.Items(cboCategory.SelectedIndex), _
      PDSAListItemNumeric).ID & ", "
    strSQL &= "QuantityPerUnit = " & _
     Str2Field(cboSupplier.Text) & ", "
    strSQL &= "UnitPrice = " & txtPrice.Text & ", "
    strSQL &= "UnitsInStock = " & txtInStock.Text & ", "
    strSQL &= "UnitsOnOrder = " & txtOnOrder.Text & ", "
    strSQL &= "ReorderLevel = " & txtReorder.Text & ", "
    strSQL &= "Discontinued = " & _
     CType(IIf(chkDisc.Checked, "1", "0"), String)
    strSQL &= " WHERE ProductID =  " & _
     CType(lstProducts.SelectedItem, _
     PDSAListItemNumeric).ID

    Try
        oCmd = New SqlClient.SqlCommand()
        With oCmd
            .Connection = New _
             SqlClient.SqlConnection(ConnectStringBuild())
            .Connection.Open()
            .CommandText = strSQL
            intRows = .ExecuteNonQuery()
            If intRows <> 1 Then
                MessageBox.Show("Did not insert row")
            End If
            .Connection.Close()
        End With

    Catch oException As Exception
        MessageBox.Show(oException.Message)

    End Try
End Sub

The code shown above uses the standard Command object to submit the UPDATE SQL statement. The Str2Field function you see referenced adds a single quote around any string values. Here is the String2Field function.

Private Function Str2Field(ByVal strValue As String) _
 As String
    If strValue.Trim() = "" Then
        Return "Null"
    Else
        Return "'" & strValue.Trim() & "'"
    End If
End Function

Using ADO.NET DataTables and DataSets

An ADO.NET DataSet object is like an in-memory database. This object holds a collection of DataTable objects. Each DataTable object is a representation of the data that was retrieved via a SELECT statement or stored procedure execution. The data in a DataSet can be written out or read in as XML. DataSets also store schema information, constraints, and relationships between multiple DataTable objects. Through a DataSet you can add, edit, and delete data.

DataSets can be used, among other things, to retrieve data from a data source to be displayed in controls on a form. In this section, you will learn to create a data entry form using DataTable and DataSet objects. Figure 3 shows the sample screen you will build which is basically the same form you created earlier in this document. You will use the Products table in the Northwind database. This sample database comes as an Access database and is installed as a part of SQL Server.

Figure 3: Add/Edit/Delete Screen using DataSets

To create this form, copy the form you created earlier by following these steps.

  1. In Solution Explorer, click the frmProducts.vb form.
  2. Press Ctrl+C to copy the form to the clipboard.
  3. Press Ctrl+V to paste a copy of this form back into Solution Explorer.
  4. Rename the form to frmProductsDS.vb.
  5. Open the code window for the form and change the line that reads Public Class Form1 to Public Class frmProductsDS.
  6. When you copy the form, all of the procedures you wrote will be copied as well. You can either keep this code, or you can delete all of the procedures.

You now have a new form that you can use to re-build the routines that you built earlier using the DataReader to use DataTables and DataSets.

Loading a ComboBox Using a DataTable Object

There are two combo boxes on our Product Information form. One is for categories of products and the other is for suppliers of products. Load the data into these combo boxes using the DataTable object.

Follow these steps to load the Supplier combo box using the DataTable object.

  1. Click the frmProductDS.vb form and then click the View Code icon (or, on the View menu, click Code).

  2. Create the SupplierLoad procedure just below the line that reads Windows Form Designer Generated Code. Add the code shown below to this new procedure.

    Private Sub SupplierLoad()
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim oTable As DataTable = New DataTable()
        Dim oItem As PDSAListItemNumeric
        Dim strSQL As String
        Dim strConn As String
        Dim intLoop As Integer
    
        strConn = ConnectStringBuild()
    
        strSQL = "SELECT SupplierID, CompanyName "
        strSQL &= "FROM Suppliers"
    
        Try
            oAdapter = _
             New SqlClient.SqlDataAdapter(strSQL, strConn)
            oAdapter.Fill(oTable)
    
            For intLoop = 0 To oTable.Rows.Count - 1
                oItem = New PDSAListItemNumeric()
                With oTable.Rows(intLoop)
                    oItem.Value = _
                     .Item("CompanyName").ToString()
                    oItem.ID = CInt(.Item("SupplierID"))
                End With
    
                cboSupplier.Items.Add(oItem)
            Next
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
    
        End Try
    End Sub
    

You are going to need three objects for loading this combo box. You will need a DataAdapter, which is the object that is used to fill up a DataTable or DataSet with data. You will need a DataTable object, which holds all of the data retrieved by the SELECT statement submitted to the data source. You will also need a PDSAListItemNumeric class into which you will place the value to display in the combo box, along with the primary key value from the Suppliers table. The PDSAListItemNumeric class is already built for you and is contained within the solution you loaded at the beginning of this document.

Building a Connection String

Before you can submit a SQL statement to a data source through ADO.NET, you need to give ADO.NET directions regarding where this data source resides and what provider to use to get at this data. This is done via a connection string. In the SupplierLoad procedure you built, you called a function called ConnectStringBuild that returns this provider string. Let's create this function.

Add the following function just below the End Sub of the SupplierLoad procedure you just created.

Private Function ConnectStringBuild() As String
    Dim strConn As String

    strConn &= "Data Source=(local);"
    strConn &= "Initial Catalog=Northwind;"
    strConn &= "User ID=sa"

    Return strConn
End Function

You may need to change this connection string if you are using SQL Server that is somewhere on your network, rather than on your local machine. If you do not have SQL Server, you can use the Northwind.mdb file that comes with Microsoft Access as a sample database. If you are using Access, you need to use the OleDbConnection, OleDbCommand, and OleDbDataAdapter object found in the System.Data.OleDb namespace and change your connection string to something like the following:

Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Access\Northwind.mdb

Change the path in the Data Source attribute to the path where your Northwind.mdb is located.

Use a Data Adapter to Fill a DataTable

After you have built the connection string and the SQL string, create a new instance of a DataAdapter object and pass to it the SQL string and the connection string. Invoke the Fill method on the DataAdapter and pass to it the DataTable object. The Fill method opens a connection to the database using the supplied connection string, fills the DataTable with data, and then closes the connection.

Once you have the data loaded into the DataTable, you can loop through each DataRow object that makes up the DataTable. Each time through the loop, you create a new PDSAListItemNumeric object, add the primary key (SupplierID) to the ID property, and the CompanyName column to the Value property. You then add this PDSAListItemNumeric object to the ListBox.

Because there is no ItemData property in the list box control like there was in Visual Basic 6.0, you will need to use the generic PDSAListItemNumeric class you built earlier to hold the primary key data as well as the text value to display.

Load the Categories Combo Box

To load the categories into the combo box on this product's form, you can copy and paste the SupplierLoad procedure, and then change the name, the columns, and the table name in the SELECT statement.

  1. Copy the complete SupplierLoad procedure into memory by highlighting the code and pressing Ctrl+C.
  2. Paste the code immediately below the End Sub of the SupplierLoad procedure by placing the cursor and pressing Ctrl+V.
  3. Change the name of this new procedure to CategoryLoad.
  4. Change the SELECT statement to use the columns CategoryID and CategoryName.
  5. Change the SELECT statement to use the table name Categories.
  6. Change the columns that load the PDSAListItemNumeric class to CategoryID and CategoryName.

Try It Out

Now that you have typed in all the code to use the DataTable object to load Suppliers and Categories, let's see if they work.

  1. Open the form so that you see the form in design mode.

  2. Double-click anywhere on the form itself (not on a control), and write code within the frmProduct_Load event procedure to call each of these routines you just wrote.

    Private Sub frmProduct_Load( _ 
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        ' Load Suppliers
        SupplierLoad()
        ' Load Categories
        CategoryLoad()
    End Sub
    
  3. Now run the program by pressing F5.

If everything worked OK, you should see a blank product form, but the Supplier and Category combo boxes should have data in them.

Creating a DataSet Object

The DataTable object holds a single table's amount of data and is useful for loading combo boxes and list boxes. A DataSet object is like a wrapper around one or more DataTable objects. Let's learn to use the DataSet object.

When you are putting together a data entry screen, it is helpful to create one DataSet that you declare as a property of your Form class. This property can then be used throughout the form to load data into the list box, and to add, edit, and delete data on the form.

  1. Open the frmProducts.vb file in the sample project.

  2. Open the code window and go to the very top of the form code.

  3. Immediately after the Inherits statement, add a Private variable called moDS that is declared as a DataSet class (see the bold text in the following code).

    Public Class frmProduct
        Inherits System.Windows.Forms.Form
    
        Private moDS As DataSet
    

    This will create a private variable that can be used throughout the form. Next, you will create the DataSet and load it up with data.

  4. Create a new procedure called DataSetCreate and type in the code shown below into this new procedure.

    Private Sub DataSetCreate()
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim strSQL As String
        Dim strConn As String
    
        ' Get Connection String
        strConn = ConnectStringBuild()
    
        ' Build SQL String
        strSQL = "SELECT * "
        strSQL &= "FROM Products"
    
        moDS = New DataSet()
        Try
            ' Create New Data Adapter
            oAdapter = _
             New SqlClient.SqlDataAdapter(strSQL, strConn)
            ' Fill DataSet From Adapter and give it a name
            oAdapter.Fill(moDS , "Products")
            ' Create a Primary Key
            With moDS.Tables("Products")
                 .PrimaryKey = New DataColumn() _
                  {.Columns("ProductID")}
            End With
    
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
    
        End Try
    End Sub
    

Most of the above code should look familiar by now. You build a connection string and a SQL SELECT statement. You also create DataAdapter to load the DataSet, just like you did with the DataTable. From there, things get a little different.

You still use the Fill method, but you pass it a DataSet object and the name that you wish to associate with the new DataTable that is created within this DataSet. In the case above, you will give this new DataTable the name Products. You can then use this name to reference which table in the Tables collection of the DataSet you wish to use.

You can reference an individual DataTable by using the Tables collection. For example, moDS.Tables("Products") returns the DataTable object created when you performed the Fill method on the DataAdapter. Remember that you passed "Products" as the second parameter on the Fill method, so it assigned this name to this DataTable. You can use this technique to tell the DataTable which column is its primary key.

If you plan on doing any searching within this DataTable in the DataSet, you will need to tell the DataTable which is the PrimaryKey column. You do this by setting the PrimaryKey property to a New DataColumn array. Because tables can have one or more columns as their primary key, you need to create an array of DataColumn objects to pass to this PrimaryKey property. What you have done in the code above is to retrieve the .Columns("ProductID") column and enclose it in braces {} immediately after declaring a new DataColumn array. The braces are how you declare the elements that you wish to go into this new array. If you have multiple columns, you separate each column by a comma within the braces.

Loading a List Box from a DataSet

You have created a DataSet of product data and stored it in the variable named moDS. You can use that variable anywhere within this form. You will now build a routine that will load the product data from the DataSet into the list box on this form.

  1. Create a new procedure named ListLoad somewhere within the form.

  2. Type in the following code into this procedure.

    Private Sub ListLoad()
        Dim oItem As PDSAListItemNumeric
        Dim oRow As DataRow
    
        LstProducts.Items.Clear()
        ' Loop through each row and get a DataRow
        For Each oRow In moDS.Tables("Products").Rows
            ' Create New Item to hold PK and Description
            oItem = New PDSAListItemNumeric()
            With oItem
                .ID = CInt(oRow.Item("ProductID"))
                .Value = oRow.Item("ProductName").ToString()
            End With
    
            ' Add Item to list box
            lstProducts.Items.Add(oItem)
        Next
    
        lstProducts.SetSelected(0, True)
    End Sub
    

The code in the ListLoad procedure is very simple. Each DataTable in a DataSet is made up of DataRow objects. You can loop through each DataRow in the DataTable and each time through the loop, you can build a PDSAListItemNumeric object to put the ProductID and ProductName into. You then add this new object to the List Box.

Try It Out

You should now be able to run the code you typed in and have it load the list box full of product names.

  1. Change the frmProduct_Load event procedure to call these two new routines you created.

  2. Add the lines of code shown below in bold.

    Private Sub frmProduct_Load( _ 
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        ' Load Suppliers
        SupplierLoad()
        ' Load Categories
        CategoryLoad()
    
        ' Initialize the DataSet
        DataSetCreate()
        ' Load List Box of Products
        ListLoad()
    End Sub
    
  3. Press F5 to run this program.

When the form appears, you should have a list box full of product names.

Finding a Specific Row in the DataSet

When a user clicks on a product in the list box, the detail data should display for each product in the appropriate controls to the right of the list box. The first thing you need to do is to write the code that will respond to the user clicking the list box.

  1. Bring up the Products form in design mode.

  2. Double-click the list box to display the SelectedIndexChanged event procedure.

  3. Add the code to call a procedure named FormShow as shown below.

    Private Sub lstProducts_SelectedIndexChanged( _
     ByVal sender As Object, ByVal e As System.EventArgs) _
     Handles lstProducts.SelectedIndexChanged
        FormShow()
    End Sub
    
  4. Write the FormShow procedure that will find a specific row within the DataSet, retrieve the information from that row, and fill in all of the appropriate controls on the Product form.

  5. Add a new procedure to the Product form and name it FormShow.

  6. Write the code shown below.

    Private Sub FormShow()
        Dim oDR As DataRow
        Dim strID As String
    
        ' Get Primary Key From List Box
        strID = CType(lstProducts.SelectedItem, _
         PDSAListItemNumeric).ID.ToString()
    
        ' Find row in DataSet
        oDR = moDS.Tables("Products").Rows.Find(CInt(strID))
        txtID.Text = oDR("ProductID").ToString()
        txtName.Text = oDR("ProductName").ToString()
        strID = oDR("SupplierID").ToString()
        Call FindItem(cboSupplier, strID)
        strID = oDR("CategoryID").ToString()
        Call FindItem(cboCategory, strID)
        txtQty.Text = oDR("QuantityPerUnit").ToString()
        txtPrice.Text = oDR("UnitPrice").ToString()
        txtInStock.Text = oDR("UnitsInStock").ToString()
        txtOnOrder.Text = oDR("UnitsOnOrder").ToString()
        txtReorder.Text = oDR("ReorderLevel").ToString()
        chkDisc.Checked = CType(oDR("Discontinued"), Boolean)
    End Sub
    

The first thing the FormShow procedure does is to return the SelectedItem from the list box. Each item in the list box is a PDSAListItemNumeric object. Because the list box only holds Object data types, you need to use the CType function to convert the Object into a PDSAListItemNumeric data type. You can then retrieve the ID property from this object to get the primary key for the row you just clicked.

The Find method on the Rows returns a single DataRow object. In the FormShow procedure, you declared a variable named oDR as a DataRow object. You pass the ID property to the Find method to have it return a reference to the found DataRow. Once you have this DataRow object, you can get at the data in each individual column.

Adding Rows to a DataSet

At some point, you will want to allow users to add rows to the tables. You can do this by submitting an INSERT statement through a Command object, or you can use the DataSet object you have already created. There are a few steps to perform to add a new row to the DataSet and to the database. Because the DataSet is disconnected from the database, you first need to add the new data to the DataSet. Next, you need to build a connection to the database and build an INSERT statement. Microsoft has provided an object that will build this INSERT statement for you, called the CommandBuilder object.

  1. Add a new procedure to your product form.

  2. Name this procedure DataAdd.

  3. Write the code shown below.

    Private Sub DataAdd()
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim oBuild As SqlClient.SqlCommandBuilder
        Dim oDR As DataRow
        Dim strSQL As String
        Dim strConn As String
    
        ' Create New DataRow Object From DataSet
        oDR = moDS.Tables("Products").NewRow()
        oDR.BeginEdit()
    
        ' Load new data into row
        oDR("ProductName") = txtName.Text
        oDR("SupplierID") = CType(cboSupplier.SelectedItem, _
         PDSAListItemNumeric).ID
        oDR("CategoryID") = CType(cboCategory.SelectedItem, _
         PDSAListItemNumeric).ID
        oDR("QuantityPerUnit") = cboSupplier.Text
        oDR("UnitPrice") = CDec(txtPrice.Text)
        oDR("UnitsInStock") = CShort(txtInStock.Text)
        oDR("UnitsOnOrder") = CShort(txtOnOrder.Text)
        oDR("ReorderLevel") = CShort(txtReorder.Text)
        oDR("Discontinued") = CBool(chkDisc.Checked)
    
        ' Tell DataRow you are done adding data
        oDR.EndEdit()    
        ' Add DataRow to DataSet
        moDS.Tables("Products").Rows.Add(oDR)
    
        Try
            ' Get Connection String
            strConn = ConnectStringBuild()
            ' Build SQL String
            strSQL = "SELECT * FROM Products "
            ' Create New DataAdapter
            oAdapter = _
             New SqlClient.SqlDataAdapter(strSQL, strConn)
            ' Create CommandBuilder for Adapter
            ' This will build INSERT, UPDATE and DELETE SQL
            oBuild = New SqlClient.SqlCommandBuilder(oAdapter)
    
            ' Get Insert Command Object
            oAdapter.InsertCommand = oBuild.GetInsertCommand()
    
            ' Submit INSERT statement through Adapter
            oAdapter.Update(moDS, "Products")
            ' Tell DataSet changes to data source are complete
            moDS.AcceptChanges()
    
            ' Reload the list box
            ListLoad()
    
        Catch oException As Exception
            MessageBox.Show(oException.Message)
    
        End Try
    End Sub
    

To add a new record to the table in the database, you first need to add a new row to the DataTable in the DataSet. You can do this by using the NewRow method to create a new DataRow. Invoke the BeginEdit method on this new DataRow so that you can place data into the appropriate columns. When you have updated all of the columns, invoke the EndEdit method. Add this new DataRow to the DataTable in the DataSet by passing the DataRow to the Add method of the Rows collection in the DataTable.

Use a Command Builder Object to Create SQL

Now that the data is in the DataSet, you can build a DataAdapter to submit this new data to the database. Create the DataAdapter by passing in the same SQL statement that you used to load the DataSet and a connection string. You then pass the DataAdapter object to the constructor of the CommandBuilder class and it creates a new Builder object for you. The GetInsertCommand method can then be called on this CommandBuilder object to retrieve a command object that contains an INSERT statement for this table. The INSERT statement uses question marks as placeholders for each piece of data in the DataSet.

When using the SqlCommandBuilder, the INSERT statement will look something like this:

INSERT INTO "Products"( "ProductName" , "SupplierID" , "CategoryID" , 
"QuantityPerUnit" , "UnitPrice" , "UnitsInStock" , "UnitsOnOrder" , 
"ReorderLevel" , "Discontinued" ) VALUES ( @ProductName , @SupplierID , 
@CategoryID , @QuantityPerUnit , @UnitPrice , @UnitsInStock , 
@UnitsOnOrder , @Reorderlevel , @Discontinued )

When using the OleDbCommandBuilder the INSERT statement will look something like this:

INSERT INTO "Products"( "ProductName" , "SupplierID" , "CategoryID" , 
"QuantityPerUnit" , "UnitPrice" , "UnitsInStock" , "UnitsOnOrder" , 
"ReorderLevel" , "Discontinued" ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? 
, ? )

Each parameter marker in the statement ("@<columnname>" for the SqlCommand and "?" for the OleDbCommand) represents where the data from the DataSet will be placed when you submit this INSERT statement through the DataAdapter. This replacement is done automatically by the DataAdapter and requires no extra programming on your part. You tell the DataAdapter to submit this INSERT statement by passing in the DataSet object and the name of the table in the DataSet that is to be updated to the Update method of the DataAdapter. After this Update method is completed, invoke the AcceptChanges method on the DataSet. This informs the new DataRow that it has been updated in the database.

Try It Out

Now that you have created this routine to add a new row, you should give it a try.

  1. Bring up the form in design mode.

  2. Double-click Add.

  3. In the btnAdd_Click event procedure, call the DataAdd procedure.

    Private Sub btnAdd_Click(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles btnAdd.Click
        DataAdd()
    End Sub
    
  4. Press F5 to run the application.

  5. Type in A New Product into the Product Name text box.

  6. Click Add. You should see the new product appear in the list box.

Updating Rows in a DataSet

Updating rows in a DataSet is almost identical to adding rows. Instead of adding a new row, you find the existing row that you want to update. You then update the data into the appropriate columns in the DataRow, build a DataAdapter and a CommandBuilder object, and retrieve the UPDATE command object. You can then submit the UPDATE statement via the Update method of the DataAdapter, accept the changes, and you are finished.

  1. Type in the code below as a new procedure in this form.

    Private Sub DataUpdate()
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim oBuild As SqlClient.SqlCommandBuilder
        Dim oDR As DataRow
        Dim strSQL As String
        Dim strID As String
        Dim strConn As String
    
        ' Get Primary Key From List Box
        strID = CType(lstProducts.SelectedItem, _
         PDSAListItemNumeric).ID.ToString()
    
        ' Find Row To Update
        oDR = moDS.Tables("Products").Rows.Find(CInt(strID))
    
        ' Begin the editing process
        oDR.BeginEdit()
    
        ' Load new data into row
        oDR("ProductName") = txtName.Text
        oDR("SupplierID") = CType(cboSupplier.SelectedItem, _
         PDSAListItemNumeric).ID
        oDR("CategoryID") = CType(cboCategory.SelectedItem, _
         PDSAListItemNumeric).ID
        oDR("QuantityPerUnit") = cboSupplier.Text
        oDR("UnitPrice") = CDec(txtPrice.Text)
        oDR("UnitsInStock") = CShort(txtInStock.Text)
        oDR("UnitsOnOrder") = CShort(txtOnOrder.Text)
        oDR("ReorderLevel") = CShort(txtReorder.Text)
        oDR("Discontinued") = CBool(chkDisc.Checked)
    
        ' End the editing process
        oDR.EndEdit()
    
        Try
            ' Get Connection String
            strConn = ConnectStringBuild()
            ' Build SQL String
            strSQL = "SELECT * FROM Products "
            ' Create New DataAdapter
            oAdapter = New _
             SqlClient.SqlDataAdapter(strSQL, strConn)
            ' Create CommandBuild from Adapter
            ' This will build INSERT, UPDATE and DELETE SQL
            oBuild = New SqlClient.SqlCommandBuilder(oAdapter)
    
            ' Get Update Command Object
            oAdapter.UpdateCommand = oBuild.GetUpdateCommand()
    
            ' Submit UPDATE through Adapter
            oAdapter.Update(moDS, "Products")
            ' Tell DataSet changes to data source are complete
            moDS.AcceptChanges()
    
            ' Reload the list box
            ListLoad()
    
        Catch oException As Exception
            MessageBox.Show(oException.Message)
    
        End Try
    End Sub
    

As you can see, this code is almost identical to the DataAdd procedure you wrote previously. The biggest difference is that instead of calling the GetInsertCommand method, you call the GetUpdateCommand. You place the command object retrieved from the GetUpdateCommand into the UpdateCommand property on the DataAdapter.

Try It Out

Now that you have created this routine to add a new row, you should give it a try.

  1. Bring up the form in design mode.

  2. Double-click Update.

  3. In the btnUpdate_Click event procedure, call the DataUpdate procedure.

    Private Sub btnUpdate_Click(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles btnUpdate.Click
        DataUpdate()
    End Sub
    
  4. Press F5 to run the application.

  5. Change one of the product names and maybe a couple of the other fields on one of the records.

  6. Click Update.

  7. Click another product and then click back on the product you updated. You should see the updated data appear in the controls on the product form.

Deleting Rows in a DataSet

By this time, you should be seeing a pattern for updating data through a DataSet object. In fact, to delete data from a DataSet, you write almost the same code, but instead of updating the appropriate data in the DataRow, you apply the Delete method instead. Then you submit this change through the DataAdapter, just like you did in the DataAdd and DataUpdate procedures you wrote.

  1. Add a new procedure called DataDelete in the form.

  2. Write the following code in this new procedure.

    Private Sub DataDelete()
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim oBuild As SqlClient.SqlCommandBuilder
        Dim oDR As DataRow
        Dim strSQL As String
        Dim strID As String
        Dim strConn As String
    
        ' Get Connection String
        strConn = ConnectStringBuild()
    
        ' Get Primary Key From List Box
        strID = CType(lstProducts.SelectedItem, _
         PDSAListItemNumeric).ID.ToString()
    
        ' Find DataRow To Delete
        oDR = moDS.Tables("Products").Rows.Find(CInt(strID))
        ' Mark DataRow for deletion
        oDR.Delete()
    
        Try
            ' Build SQL String
            strSQL = "SELECT * FROM Products "
            ' Create New DataAdapter
            oAdapter = New _
             SqlClient.SqlDataAdapter(strSQL, strConn)
            ' Create CommandBuild from Adapter
            ' This will build INSERT, UPDATE and DELETE SQL
            oBuild = New SqlClient.SqlCommandBuilder(oAdapter)
    
            ' Get Delete Command Object
            oAdapter.DeleteCommand = oBuild.GetDeleteCommand()
    
            ' Submit DELETE through Adapter
            oAdapter.Update(moDS, "Products")
            ' Tell DataSet changes to data source are complete
            moDS.AcceptChanges()
    
            ' Reload the list box
            ListLoad()
    
        Catch oException As Exception
            MessageBox.Show(oException.Message)
    
        End Try
    End Sub
    

In the above code, you find the row you wish to delete and then apply the Delete method to that DataRow object. This marks the row for deletion in the DataSet. You again use the CommandBuilder object to get the DeleteCommand object, and invoke the Update method on the DataAdapter to submit this DELETE statement to the database.

Try It Out

Now that you have created this routine to delete a row, you should give it a try.

  1. Bring up the form in design mode.

  2. Double-click the Delete button.

  3. In the btnDelete_Click event procedure, call the DataDelete procedure.

    Private Sub btnDelete_Click(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles btnUpdate.Click
        DataDelete()
    End Sub
    
  4. Press F5 to run the application.

  5. Click one of the products in the list box.

  6. Click Delete. You should now see this product disappear from the list box.

What's Different Between Visual Basic 6.0 and ADO?

In Visual Basic 6.0, the primary object you use to work with data is the RecordSet object. This RecordSet object is generally obtained by creating a Connection object, and it associates that connection with a Command object and calls Execute on the Command. Depending on properties set on the Command and RecordSet objects, and on functionality of the database, the resulting RecordSet might behave in very different ways; the RecordSet might be forward-only or might support scrolling, it may or may not be able to give you an accurate count of rows, you may or may not be able to update it, it may require a continuous connection to the database or it may be entirely cached in memory.

With ADO.NET, you still have the Connection and Command objects, but you use a different type of object depending on how you want to use the data.

If you are just looping through the data once, and not trying to make changes to the data as you read it, you can call the ExecuteReader method on the Connection to return a DataReader. The DataReader is a forward only, read-only stream of results that gives you the best possible performance for reading data from the database.

If you want to scroll through and update the data, you can call the Fill method on the DataAdapter to put the results in a DataSet. The DataSet provides an in-memory cache of the data that allows you to navigate through results and update data in a disconnected, in-memory cache. Changes are then sent back to the database by calling the Update method on the DataAdapter.

This separation of functionality into separate components in ADO.NET gives the developer more control over how data is accessed, represented, and used within the .NET Framework.

Summary

In this document, you learn to create a client server application using the DataReader, DataTable and DataSet objects. Although each of the data access methods presented in this document work just fine, you will probably want to choose the one that you like best and use that one consistently when creating your applications.

About the Author

Paul D. Sheriff is the owner of PDSA, Inc., a custom software development and consulting company in Southern California. Paul is the MSDN Regional Director for Southern California, is the author of a book on Visual Basic 6.0 called Paul Sheriff Teaches Visual Basic, and has produced over 72 videos on Visual Basic, SQL Server, .NET and Web Development for Keystone Learning Systems. Paul has co-authored a book entitled ASP.NET Jumpstart. Visit the PDSA, Inc. Web site (www.pdsa.com) for more information.

About Informant Communications Group

Informant Communications Group, Inc. (www.informant.com) is a diversified media company focused on the information technology sector. Specializing in software development publications, conferences, catalog publishing and Web sites, ICG was founded in 1990. With offices in the United States and the United Kingdom, ICG has served as a respected media and marketing content integrator, satisfying the burgeoning appetite of IT professionals for quality technical information.

Copyright © 2002 Informant Communications Group and Microsoft Corporation

Technical editing: PDSA, Inc.