Fundamentals of the Data Adapter

Introduction

As mentioned already, a DataSet object is primarily used to create a list, not a formal database in the strict sense of Microsoft SQL Server, Microsoft Access, or Corel Paradox, etc. This means that a list-based application lead by a DataSet object is primarily a list. In order to read information of a formal database and use it in a DataSet list, you must "convert" or adapt it.

A data adapter is an object that takes data from a database, reads that data, and "fills" a DataSet object with that data. Put it in reverse, a data adapter can get the data stored in, or manipulated by, a DataSet object and fill or update a database with that data. To be able to apply these scenarios to any database, the .NET Framework provides various data adapters, each adapted for a particular category of database.

In order to read information from a Microsoft SQL Server database and make it available to a DataSet object, you can use an object created from the SqlDataAdapter class. This class is defined in the System.Data.SqlClient namespace of the System.Data.dll library. The SqlDataAdapter class is derived from the DbDataAdapter class, itself derived from the DataAdapter class. The DbDataAdapter and the DataAdapter classes are defined in the System.Data.Common namespaces of the System.Data.dll library.

1. Start Microsoft Visual Basic and create a Windows Application named spr2
2. In the Solution Explorer, right-click Form1.vb and click Rename
3. Type RentalProperties.vb and press Enter
4. Design the form as follows:
    

   Control Text Name Other Properties DataGridView   dgvProperties Anchor: Top, Bottom, Left, Right GroupBox Fields to Show grpFieldsToShow Anchor: Bottom, Left, Right RadioButton Show all Fields rdoShowAllFields   RadioButton Show Only rdoShowSomeFields   CheckedListBox   clbColumns CheckOnClick: True MultiColumn: True Anchor: Bottom, Left, Right Button Execute btnExecute Anchor: Bottom, Right Button Close btnClose Anchor: Bottom, Right

5. Double-click the checked list box and implement its event as follows:
    

   Imports System.Data.SqlClient Public Class RentalProperties Private Sub clbColumnsSelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clbColumns.SelectedIndexChanged If clbColumns.CheckedItems.Count < 1 Then rdoShowAllFields.Checked = True Else rdoShowSomeFields.Checked = True End If End Sub End Class

6. Execute the application to preview the form
7. Close the form and return to your programming environment

There are two ways you can create a data adapter: visually or programmatically.

In previous versions of Microsoft Visual Studio (2002 and 2003), the Toolbox was equipped with various data adapters (one for each category of database type). It was removed in the 2005 version. If you want to visually create a data adapter, you must manually add it to the Toolbox. To do this, you can right-click the Data section of the Toolbox and click Choose Items... In the .NET Framework Component property page, scroll down and put a check mark on a data adapter. For our lesson, this would be SqlDataAdapter:

After making the selection, you can click OK. This would add a SqlDataAdapter object to the Toolbox. From there, you can click the SqlDataAdapter button and click the form. The Data Adapter Configuration Wizard would start. In the first page of the wizard, you must create or select a connection you will use:

After selecting the connection, you can click Next. In the second page of the wizard, you must specify what mechanism you will use to get your data. You have three options:

In the second page of the wizard, for a data selection scenario, you accept or select the first option: Use SQL Statements:

In Lesson 40, we will study the issues of the second two options. After making the selection in the wizard, you can click Next. In the third page of the wizard, if you already know the SQL statement you want to use, you can type it. Otherwise, you can click the Query Builder button. This would display the Query Builder dialog box. The Add Table would first display:

In the Add Table dialog box, you can click the desired table and click Add or you can double-click the table. After making the table selection, you can click Close. The Query Builder dialog box would display. In the Query Builder dialog box, you select the columns as we described already. Here is an example:

After making the selections, you can click OK. The third page of the Data Adapter Configuration Wizard would display again, with the SQL statement written for you:

If the statement does not look fine, you can manually edit it or you can click the Query Builder button to get back to the Query Builder and re-configure the statement. To get more options, you can click the Advanced Options button:

Once you are ready, you can click Next (or Finish). The fourth page presents a summary of what would be created, namely based on the Advanced Options dialog box:

Once you are ready, you can click Finish.

To programmatically create a SQL data adapter, declare a variable of type SqlDataAdapter and initialize it using one of its constructors, such as the default constructor. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    dim sdaVideoCollection as SqlDataAdapter  = new SqlDataAdapter
End Sub

To allow the data adapter to use values produced from reading a table, you must create a command object for it. To support this, a data adapter (such as the SqlDataAdapter class) is equipped with a property named SelectCommand (of type SqlCommand for a SqlDataAdapter object). If you create data adapter using the Data Adapter Configuration Wizard, the command to select would be specified for you. Otherwise, you can access the SelectCommand field in the Properties window and configure it.

To programmatically specify how data would be read, you can first create a SqlCommand object that would carry a SQL statement:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
    New SqlConnection("Data Source=(local);" & 
                      "Database='VideoCollection';" & 
                      "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
End Sub

Equipped with a SqlCommand object that holds a SQL statement, you can assign it to the SqlDataAdapter.SelectCommand property of your data adapter. This would be done as follows:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
 	New SqlConnection("Data Source=(local);" & 
     			  "Database='VideoCollection';" & 
     			  "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)

    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter
    sdaVideoCollection.SelectCommand = cmdVideos

    Connect.Open()
    Connect.Close()
End Sub

If you do not want to use the default constructor and the SelectCommand property separately, you can use the second constructor of the SqlDataAdapter class. Its syntax is:

Public Sub New(selectCommand As SqlCommand)

This constructor takes as argument a SqlCommand object. This time, instead of assigning the command to the SelectCommand property, you can pass that SqlCommand object to the SqlDataAdapter variable when declaring it. This would be done as follows:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
      			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)

    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)

    Connect.Open()
    Connect.Close()
End Sub

Notice that with both constructors reviewed above, you must pass the connection to a SqlCommand object. As an alternative, you can create a connection but pass it directly to the data adapter when declaring its variable. To do this, you can use the third constructor of the SqlDataAdapter class. Its syntax is:

Public Sub New(selectCommandText As String, 
	       selectConnection As SqlConnection)

The first argument of this constructor expects a SQL statement, passed as string, that specifies how the data would be read. The second argument is a SqlConnection object that specifies how the connection to the database would be handled. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"

    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(strVideos, Connect)

    Connect.Open()
    Connect.Close()
End Sub

Instead of separately defining a SqlConnection and a SqlDataAdapter objects, you can directly provide a connection string to the SqlDataAdapter object when declaring it. To do this, you can use the fourth constructor of the SqlDataAdapter class. Its syntax is:

Public Sub New(selectCommandText As String, 
	       selectConnectionString As String)

The first argument to this constructor is the statement that specifies how data would be read. The second argument is a connection string. Here is an example of declaring a data adapter using this version of the SqlDataAdapter class:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim strVideos As String = "SELECT * FROM dbo.Videos"

    Dim sdaVideoCollection As SqlDataAdapter = 
          New SqlDataAdapter(strVideos, "Data Source=(local);" & 
		             "Database='VideoCollection';" & 
		             "Integrated Security=yes")
End Sub

Before using a data set in your application, you would need a DataSet object. You can declare a DataSet variable. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)

    Dim dsVideos As DataSet = New DataSet("VideoCollection")

    Connect.Open()
    Connect.Close()
End Sub

If you declare your own DataSet variable, you would also eventually have to take care of some detailed operations (such as reading from XML, writing to XML, or serializing).

After reading data using a SqlDataAdapter object, you can used it to fill a DataSet object. To support this operation, the SqlDataAdapter class inherits the Fill() method from the DbDataAdapter class. This method is overloaded with 8 versions. The first version of this method uses the following syntax:

Public Overrides Function Fill(dataSet As DataSet) As Integer

This version takes as argument an object of type DataSet. After this call, the dataset argument would be filled with the records of the table read by the data adapter. When calling this method, you can pass it a DataSet variable created as described above. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"

    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)

    Dim dsVideos As DataSet = New DataSet("VideoCollection")

    sdaVideoCollection.Fill(dsVideos)

    Connect.Open()
    Connect.Close()
End Sub

Once a DataSet contains records, you can use it as a data source for Windows controls. For example, you can use it to populate a DataGrid control. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"

    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)

    Dim dsVideos As DataSet = New DataSet("VideoCollection")

    sdaVideoCollection.Fill(dsVideos)

    dgvVideos.DataSource = dsVideos

    Connect.Open()
    Connect.Close()
End Sub

Once a DataSet object has received data from a data adapter, it is made aware of the table(s), the column(s), and the record(s) that belong to the SQL statement of the data adapter. Based on this, you can bind the Windows controls of your application's form to the columns of a DataSet.

When visiting the records of a table using a form of your application, if you provide the means for the user to move from one record to another, if the user gets to a record and changes something in it, that record would not be automatically updated when the user moves to another record. To update a record using the data adapter, the SqlDataAdapter class inherits the Update() method from its parent the DbDataAdapter. The Update() method is overloaded with 5 versions. One of its versions uses the following syntax:

Public Overrides Function Update(dataSet As DataSet) As Integer

This version takes a DataSet object as argument. This means that the data adapter would read the information stored in the DataSet object and update the database with it. This is probably one of the easiest or fastest means of updating data of a table.

The tables of a DataSet object are stored in the DataSet.Tables property that is of type DataTableCollection. After filling up a DataSet object, if the selection statement of the data adapter includes only one table, as done in the above data adapter, the first table of the statement can be identified with the index of 0 as in DataTableCollection(0). If the statement includes only one table, only a 0 index can be used. As the DataTableCollection(0) value allows you to identify a table, you can retrieve any table-related information with this information. For example, you can get the object name of the table and specify it as the DataMember property of a DataGridView control. Here is an example:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)
    Dim dsVideos As DataSet = New DataSet("VideoCollection")

    sdaVideoCollection.Fill(dsVideos)

    dgvVideos.DataSource = dsVideos
    dgvVideos.DataMember = dsVideos.Tables(0).TableName

    Connect.Open()
    Connect.Close()
End Sub

Remember that the DataSet.Tables(Index) value gives you access to a table as an object and you can use it as necessary.

Just as you can use the filled DataSet object to locate a table by its index, inside of the identified table, you can also locate a particular column you need. As reviewed in previous lessons, the columns of a table are stored in the Columns property of a DataTable object and the Columns property is of type DataColumnCollection. Each column inside of the table can be identified by its index. The first column has an index of 0. The second has an index of 1, and so on. Once you have identified a column, you can manipulate it as you see fit. In the following example, since we (behave like we) do not know the name of the second column, a message box displays that information for us:

Private Sub ExerciseLoad(ByVal sender As Object, 
                          ByVal e As System.EventArgs) 
                          Handles Me.Load
    Dim Connect As SqlConnection = 
	 New SqlConnection("Data Source=(local);" & 
     			   "Database='VideoCollection';" & 
     			   "Integrated Security=yes")

    Dim strVideos As String = "SELECT * FROM dbo.Videos"
    Dim cmdVideos As SqlCommand = New SqlCommand(strVideos, Connect)
    Dim sdaVideoCollection As SqlDataAdapter = New SqlDataAdapter(cmdVideos)
    Dim dsVideos As DataSet = New DataSet("VideoCollection")

    sdaVideoCollection.Fill(dsVideos)

    DataGridView1.DataSource = dsVideos
    DataGridView1.DataMember = dsVideos.Tables(0).TableName

    Dim colSecond As DataColumn = dsVideos.Tables(0).Columns(1)
    MsgBox("The name of the second column is " & colSecond.ColumnName)

    Connect.Open()
    Connect.Close()
End Sub

After filling out a DataSet object with information from a data adapter, the records of the table(s) included in the selection statement become available from the DataSet object. As reviewed in Lesson 17, the records of a table are stored in the Rows property of the table. We have already seen how to locate a table and how to identify a column. To get a record, you can use the techniques reviewed in Lesson 17 for locating a record and Lesson 20 for finding a record.

Data entry with a data adapter is performed just a few steps once you have properly bound the controls of your form to a DataSet object. To start, you can access the form's BindingContext property to get its BindingContext.Item property. The second version of this property allows you to specify the data source and the table name. After specifying the DataSet object that holds the records and the table that holds the data, you can first call the EndCurrentEdit() method to suspend any record editing that was going on. After this, call the AddNew() method to get the table ready for a new record. This allows the user to enter values in the Windows control.

1. In the Class Name combo box, select (RentalProperties Events)
2. In the Method Name combo box, select Load and implement the event as follows:
    

   Private Sub RentalPropertiesLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Using Connect As SqlConnection = New SqlConnection("Data Source=(local);" & "Database='SolasPropertyRental1';" & "Integrated Security='SSPI';") Dim strSelect As String = "SELECT * FROM RentalProperties;" Dim cmdProperties As SqlCommand = New SqlCommand(strSelect, Connect) Dim sdaProperties As SqlDataAdapter = New SqlDataAdapter(cmdProperties) Dim bsProperties As BindingSource = New BindingSource Dim dsProperties As DataSet = New DataSet("PropertiesSet") sdaProperties.Fill(dsProperties) Connect.Open() bsProperties.DataSource = dsProperties.Tables(0) dgvProperties.DataSource = bsProperties For Each col As DataColumn In dsProperties.Tables(0).Columns clbColumns.Items.Add(col.ColumnName) Next End Using rdoShowAllFields.Checked = True End Sub

3. Execute the application to see the result
    
   
4. Close the form and return to your programming environment
5. In the Class Name combo box, select btnExecute
6. In the Method Name combo box, select Click and implement the event as follows:
    

   Private Sub btnExecuteClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click If clbColumns.CheckedItems.Count < 1 Then Exit Sub End If Using Connect As SqlConnection = New SqlConnection("Data Source=(local);" & "Database='SolasPropertyRental1';" & "Integrated Security='SSPI';") Dim strColumns As String = "" For Each str As String In clbColumns.CheckedItems strColumns = strColumns & ", " & str Next Dim strResult As String If rdoShowAllFields.Checked = True Then strResult = "SELECT * FROM RentalProperties" Else strResult = "SELECT " & strColumns.Substring(1) & " FROM RentalProperties" End If Dim cmdProperties As SqlCommand = New SqlCommand(strResult, Connect) Dim sdaProperties As SqlDataAdapter = New SqlDataAdapter(cmdProperties) Dim bsProperties As BindingSource = New BindingSource Dim dsProperties As DataSet = New DataSet("PropertiesSet") sdaProperties.Fill(dsProperties) Connect.Open() bsProperties.DataSource = dsProperties.Tables(0) dgvProperties.DataSource = bsProperties End Using End Sub

7. In the Class Name combo box, select btnClose
8. In the Method Name combo box, select Click and implement the event as follows:
    

   Private Sub btnCloseClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click End End Sub

9. Execute the application to see the result
10. Click a few check boxed in the bottom control
11. Click the Execute button
     
    
12. Close the form and return to your programming environment