Search the Catalog
Programming Visual Basic .NET

Programming Visual Basic .NET

By Dave Grundgeiger
December 2001
0-596-00093-6, Order Number: 0936
460 pages, $39.95

Chapter 8
ADO.NET: Developing Database Applications

Many software applications benefit from storing their data in database management systems. A database management system is a software component that performs the task of storing and retrieving large amounts of data. Examples of database management systems are Microsoft SQL Server and Oracle Corporation's Oracle.

TIP:  Microsoft SQL Server and Microsoft Access both include a sample database called Northwind. The Northwind database is used in the examples throughout this chapter.

All examples in this chapter assume that the following declaration appears in the same file as the code:

Imports System.Data

Examples that use SQL Server also assume this declaration:

Imports System.Data.SqlClient

and examples that use Access assume this declaration:

Imports System.Data.OleDb

A Brief History of Universal Data Access

Database management systems provide APIs that allow application programmers to create and access databases. The set of APIs that each manufacturer's system supplies is unique to that manufacturer. Microsoft has long recognized that it is inefficient and error prone for an applications programmer to attempt to master and use all the APIs for the various available database management systems. What's more, if a new database management system is released, an existing application can't make use of it without being rewritten to understand the new APIs. What is needed is a common database API.

Microsoft's previous steps in this direction included Open Database Connectivity (ODBC), OLE DB, and ADO (not to be confused with ADO.NET). Microsoft has made improvements with each new technology.

With .NET, Microsoft has released a new mechanism for accessing data: ADO.NET. The name is a carryover from Microsoft's ADO (ActiveX Data Objects) technology, but it no longer stands for ActiveX Data Objects--it's just ADO.NET. To avoid confusion, I will refer to ADO.NET as ADO.NET and to ADO as classic ADO.

If you're familiar with classic ADO, be careful--ADO.NET is not a descendant, it's a new technology. In order to support the Internet evolution, ADO.NET is highly focused on disconnected data and on the ability for anything to be a source of data. While you will find many concepts in ADO.NET to be similar to concepts in classic ADO, it is not the same.

Managed Providers

When speaking of data access, it's useful to distinguish between providers of data and consumers of data. A data provider encapsulates data and provides access to it in a generic way. The data itself can be in any form or location. For example, the data may be in a typical database management system such as SQL Server, or it may be distributed around the world and accessed via web services. The data provider shields the data consumer from having to know how to reach the data. In ADO.NET, data providers are referred to as managed providers.

A data consumer is an application that uses the services of a data provider for the purposes of storing, retrieving, and manipulating data. A customer-service application that manipulates a customer database is a typical example of a data consumer. To consume data, the application must know how to access one or more data providers.

ADO.NET is comprised of many classes, but five take center stage:

Connection
Represents a connection to a data source.

Command
Represents a query or a command that is to be executed by a data source.

DataSet
Represents data. The DataSet can be filled either from a data source (using a DataAdapter object) or dynamically.

DataAdapter
Used for filling a DataSet from a data source.

DataReader
Used for fast, efficient, forward-only reading of a data source.

With the exception of DataSet, these five names are not the actual classes used for accessing data sources. Each managed provider exposes classes specific to that provider. For example, the SQL Server managed provider exposes the SqlConnection, SqlCommand, SqlDataAdapter, and SqlDataReader classes. The DataSet class is used with all managed providers.

Any data-source vendor can write a managed provider to make that data source available to ADO.NET data consumers. Microsoft has supplied two managed providers in the .NET Framework: SQL Server and OLE DB.

The examples in this chapter are coded against the SQL Server managed provider, for two reasons. The first is that I believe that most programmers writing data access code in Visual Basic .NET will be doing so against a SQL Server database. Second, the information about the SQL Server managed provider is easily transferable to any other managed provider.

Connecting to a SQL Server Database

To read and write information to and from a SQL Server database, it is necessary first to establish a connection to the database. This is done with the SqlConnection object, found in the System.Data.SqlClient namespace. Here's an example:

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )

This code fragment instantiates an object of type SqlConnection, passing its constructor a connection string. Calling the SqlConnection object's Open method opens the connection. A connection must be open for data to be read or written, or for commands to be executed. When you're finished accessing the database, use the Close method to close the connection:

' Close the database connection.
cn.Close(  )

The connection string argument to the SqlConnection class's constructor provides information that allows the SqlConnection object to find the SQL Server database. The connection string shown in the earlier code fragment indicates that the database is located on the same machine that is running the code snippet (Data Source=localhost), that the database name is Northwind (Initial Catalog=Northwind), and that the user ID that should be used for logging in to SQL Server is the current Windows login account (Integrated Security=True). Table 8-1 shows the valid SQL Server connection string settings.

Table 8-1: SQL Server connection string settings

Setting

Default Value

Description

Addr

 

Synonym for Data Source.

Address

 

Synonym for Data Source.

Application Name

 

The name of the client application. If provided, SQL Server uses this name in its sysprocesses table to help identify the process serving this connection.

AttachDBFilename

 

Synonym for Initial File Name.

Connect Timeout

15

Synonym for Connection Timeout.

Connection Timeout

15

The number of seconds to wait for a login response from SQL Server. If no response is received during this period, an SqlException exception is thrown.

This setting corresponds to the SqlConnection object's ConnectionTimeout property.

Current Language

 

The language to use for this session with SQL Server. The value of this setting must match one of the entries in either the "name" column or the "alias" column of the "master.dbo.syslanguages" system table. If this setting is not specified, SQL Server uses either its system default language or a user-specific default language, depending on its configuration.

The language setting affects the way dates are displayed and may affect the way SQL Server messages are displayed.

Search for "SQL Server Language Support" in SQL Server Books Online for more information.

Data Source

 

The name or network address of the computer on which SQL Server is located.

This setting corresponds to the SqlConnection object's DataSource property.

extended properties

 

Synonym for Initial File Name.

Initial Catalog

 

The name of the database to use within SQL Server.

This setting corresponds to the SqlConnection object's Database property.

Initial File Name

 

The full pathname of the primary file of an attachable database.

If this setting is specified, the Initial Catalog setting must also be specified.

Search for "Attaching and Detaching Databases" in SQL Server Books Online for more information.

AttachDBFilename and extended properties are synonyms for Initial File Name.

Integrated Security

`false'

Indicates whether to use NT security for authentication. A value of `true' or `sspi' (Security Support Provider Interface) indicates that NT security should be used. A value of `false' indicates that SQL Server security should be used.

Search for "How SQL Server Implements Security" in SQL Server Books Online for more information.

Net

`dbmssocn'

Synonym for Network Library.

Network Address

 

Synonym for Data Source.

Network Library

`dbmssocn'

The name of the .dll that manages network communications with SQL Server. The default value, `dbmssocn', is appropriate for clients that communicate with SQL Server over TCP/IP.

Search for "Communication Components" and "Net-Libraries and Network Protocols" in SQL Server Books Online for more information.

Password

 

The SQL Server login password for the user specified in the User ID setting.

Persist Security Info

`false'

Specifies whether SqlConnection object properties can return security-sensitive information while a connection is open.

Before a connection is opened, its security-sensitive properties return whatever was placed in them. After a connection is opened, properties return security-sensitive information only if the Persist Security Info setting was specified as `true'.

For example, if Persist Security Info is `false' and the connection has been opened, the value returned by the SqlConnection object's ConnectionString property does not show the Password setting, even if the Password setting was specified.

Pwd

 

Synonym for Password.

Server

 

Synonym for Data Source.

Trusted_Connection

`false'

Synonym for Integrated Security.

User ID

 

The SQL Server login account to use for authentication.

Workstation ID

the client computer name

The name of the computer that is connecting to SQL Server.

SQL Server Authentication

Before a process can access data that is located in a SQL Server database, it must log in to SQL Server. The SqlConnection object communicates with SQL Server and performs this login based on information provided in the connection string. Logging in requires authentication. Authentication means proving to SQL Server that the process is acting on behalf of a user who is authorized to access SQL Server data. SQL Server recognizes two methods of authentication:

  • SQL Server Authentication, which requires the process to supply a username and password that have been set up in SQL Server by an administrator. Beginning with SQL Server 2000, this method of authentication is no longer recommended (and is disabled by default).
  • Integrated Windows Authentication, in which no username and password are provided. Instead, the Windows NT or Windows 2000 system on which the process is running communicates the user's Windows login name to SQL Server. The Windows user must be set up in SQL Server by an administrator in order for this to work.

To use SQL Server Authentication:

  1. (SQL Server 2000 only) Enable SQL Server Authentication. In Enterprise Manager, right-click on the desired server, click Properties, and then click the Security tab. Select "SQL Server and Windows" and click OK.
  2. The network administrator sets up a login account using Enterprise Manager, specifying that the account will use SQL Server Authentication and supplying a password. Programming books (including this one) typically assume the presence of a user named "sa" with an empty password, because this is the default system administrator account set up on every SQL Server installation (good administrators change the password, however).
  3. The network administrator assigns rights to this login account as appropriate.
  4. The data access code specifies the account and password in the connection string passed to the SqlConnection object. For example, the following connection string specifies the "sa" account with a blank password:

"Data Source=SomeMachine; Initial Catalog=Northwind; User ID=sa; Password="

To use Integrated Windows Authentication:

  1. The network administrator sets up the login account using Enterprise Manager, specifying that the account will use Windows Authentication and supplying the Windows user or group that is to be given access.
  2. The network administrator assigns rights to this login account as appropriate.
  3. The data access code indicates in the connection string that Integrated Windows Security should be used, as shown here:

"Data Source= SomeMachine; Initial Catalog=Northwind; Integrated Security=True"

When using Integrated Windows Authentication, it is necessary to know what Windows login account a process will run under and to set up appropriate rights for that login account in SQL Server Enterprise Manager. A program running on a local machine generally runs under the login account of the user that started the program. A component running in Microsoft Transaction Server (MTS) or COM+ runs under a login account specified in the MTS or COM+ Explorer. Code that is embedded in an ASP.NET web page runs under a login account specified in Internet Information Server (IIS). Consult the documentation for these products for information on specifying the login account under which components run. Consult the SQL Server Books Online for information on setting up SQL Server login accounts and on specifying account privileges.

Connecting to an OLE DB Data Source

OLE DB is a specification for wrapping data sources in a COM-based API so that data sources can be accessed in a polymorphic way. The concept is the same as ADO.NET's concept of managed providers. OLE DB predates ADO.NET and will eventually be superseded by it. However, over the years, OLE DB providers have been written for many data sources, including Oracle, Microsoft Access, Microsoft Exchange, and others, whereas currently only one product--SQL Server--is natively supported by an ADO.NET managed provider. To provide immediate support in ADO.NET for a wide range of data sources, Microsoft has supplied an ADO.NET managed provider for OLE DB. That means that ADO.NET can work with any data source for which there is an OLE DB data provider. Furthermore, because there is an OLE DB provider that wraps ODBC (an even older data-access technology), ADO.NET can work with virtually all legacy data, regardless of the source.

Connecting to an OLE DB data source is similar to connecting to SQL Server, with a few differences: the OleDbConnection class (from the System.Data.OleDb namespace) is used instead of the SqlConnection class, and the connection string is slightly different. When using the OleDbConnection class, the connection string must specify the OLE DB provider that is to be used as well as additional information that tells the OLE DB provider where the actual data is. For example, the following code opens a connection to the Northwind sample database in Microsoft Access:

' Open a connection to the database.
Dim strConnection As String = _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
   & "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open(  )

Similarly, this code opens a connection to an Oracle database:

' Open a connection to the database.
Dim strConnection As String = _
   "Provider=MSDAORA.1;User ID=MyID;Password=MyPassword;" _
   & "Data Source=MyDatabaseService.MyDomain.com"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open(  )

The values of each setting in the connection string, and even the set of settings that are allowed in the connection string, are dependent on the specific OLE DB provider being used. Refer to the documentation for the specific OLE DB provider for more information.

Table 8-2 shows the provider names for several of the most common OLE DB providers.

Table 8-2: Common OLE DB provider names

Data source

OLE DB provider name

Microsoft Access

Microsoft.Jet.OLEDB.4.0

Microsoft Indexing Service

MSIDXS.1

Microsoft SQL Server

SQLOLEDB.1

Oracle

MSDAORA.1

Reading Data into a DataSet

The DataSet class is ADO.NET's highly flexible, general-purpose mechanism for reading and updating data. Example 8-1 shows how to issue a SQL SELECT statement against the SQL Server Northwind sample database to retrieve and display the names of companies located in London. The resulting display is shown in Figure 8-1.

Figure 8-1. The output generated by the code in Example 8-1
Figure 1

 

Example 8-1: Retrieving data from SQL Server using a SQL SELECT statement

' Open a connection to the database.
Dim strConnection As String = _
   "Data Source=localhost; Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data set command object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim dscmd As New SqlDataAdapter(strSelect, cn)
 
' Load a data set.
Dim ds As New DataSet(  )
dscmd.Fill(ds, "LondonCustomers")
 
' Close the connection.
cn.Close(  )
 
' Do something with the data set.
Dim dt As DataTable = ds.Tables.Item("LondonCustomers")
Dim rowCustomer As DataRow
For Each rowCustomer In dt.Rows
   Console.WriteLine(rowCustomer.Item("CompanyName"))
Next

The code in Example 8-1 performs the following steps to obtain data from the database:

  1. Opens a connection to the database using a SqlConnection object.
  2. Instantiates an object of type SqlDataAdapter in preparation for filling a DataSet object. In Example 8-1, a SQL SELECT command string and a Connection object are passed to the SqlDataAdapter object's constructor.
  3. Instantiates an object of type DataSet and fills it by calling the SqlDataAdapter object's Fill method.

The DataSet Class

The DataSet class encapsulates a set of tables and the relations between those tables. Figure 8-2 shows a class model diagram containing the DataSet and related classes. The DataSet is always completely disconnected from any data source. In fact, the DataSet has no knowledge of the source of its tables and relations. They may be dynamically created using methods on the DataSet, or they may be loaded from a data source. In the case of the SQL Server managed provider, a DataSet can be loaded from a SQL Server database using an SqlDataAdapter object. This is what was done in Example 8-1.

Figure 8-2. A class model diagram for the DataSet and related classes
Figure 2

After a DataSet is loaded, its data can be changed, added to, or deleted, all without affecting the data source. Indeed, a database connection does not need to be maintained during these updates. When ready, the updates can be written back to the database by establishing a new connection and calling the SqlDataAdapter object's Update method. Examples of writing updates to a database are shown later in this chapter.Navigating the DataSet

In this section you'll learn how to find specific data in a DataSet object, how to make changes to that data, and how to write those changes back to a database.

Finding Tables

The DataSet object's Tables property holds a TablesCollection object that contains the DataTable objects in the DataSet. The following code loops through all the tables in the DataSet and displays their names:

' Iterate through the tables in the DataSet ds.
Dim dt As DataTable
For Each dt In ds.Tables
   Console.WriteLine(dt.TableName)
Next

This code does the same thing, using a numeric index on the TablesCollection object:

' Iterate through the tables in the DataSet ds.
Dim n As Integer
For n = 0 To ds.Tables.Count - 1
   Console.WriteLine(ds.Tables(n).TableName)
Next

The TablesCollection object can also be indexed by table name. For example, if the DataSet ds contains a table named "Categories", this code gets a reference to it:

Dim dt As DataTable = ds.Tables("Categories")

Finding Rows

The DataTable object's Rows property holds a DataRowCollection object that in turn holds the table's DataRow objects. Each DataRow object holds the data for that particular row. The following code loops through all the rows in the DataTable and displays the value of the first column (column 0) in the row:

' Iterate through the rows.
Dim row As DataRow
For Each row In dt.Rows
   Console.WriteLine(row(0))
Next

This code does the same thing, using a numeric index on the RowsCollection object:

' Iterate through the rows.
Dim n As Integer
For n = 0 To dt.Rows.Count - 1
   Console.WriteLine(dt.Rows(n)(0))
Next

To assist with locating specific rows within a table, the DataTable class provides a method called Select. The Select method returns an array containing all the rows in the table that match the given criteria. The syntax of the Select method is:

Public Overloads Function Select( _
   ByVal filterExpression As String, _
   ByVal sort As String, _
   ByVal recordStates As System.Data.DataViewRowState _
) As System.Data.DataRow(  )

The parameters of the Select method are:

filterExpression
This parameter gives the criteria for selecting rows. It is a string that is in the same format as the WHERE clause in an SQL statement.

sort
This parameter specifies how the returned rows are to be sorted. It is a string that is in the same format as the ORDER BY clause in an SQL statement.

recordStates
This parameter specifies the versions of the records that are to be retrieved. Record versions are discussed in the later section "Changing, Adding, and Deleting Rows." The value passed in this parameter must be one of the values given by the System.Data.DataViewRowState enumeration. Its values are:

CurrentRows
Returns the current version of each row, regardless of whether it is unchanged, new, or modified.

Deleted
Returns only rows that have been deleted.

ModifiedCurrent
Returns only rows that have been modified. The values in the returned rows are the current values of the rows.

ModifiedOriginal
Returns only rows that have been modified. The values in the returned rows are the original values of the rows.

New
Returns only new rows.

None
Returns no rows.

OriginalRows
Returns only rows that were in the table prior to any modifications. The values in the returned rows are the original values.

Unchanged
Returns only unchanged rows.

These values can be combined using the And operator to achieve combined results. For example, to retrieve both modified and new rows, pass this value:

DataViewRowState.ModifiedCurrent And DataViewRowState.New

The return value of the Select method is an array of DataRow objects.

The Select method is overloaded. It has a two-parameter version that is the same as the full version, except that it does not take a recordStates parameter:

Public Overloads Function Select( _
   ByVal filterExpression As String, _
   ByVal sort As String _
) As System.Data.DataRow(  )

Calling this version of the Select method is the same as calling the full version with a recordStates value of DataViewRowState.CurrentRows.

Similarly, there is a one-parameter version that takes only a filterExpression:

Public Overloads Function Select( _
   ByVal filterExpression As String _
   ) As System.Data.DataRow(  )

This is the same as calling the three-parameter version with sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.

Lastly, there is the parameterless version of Select:

Public Overloads Function Select(  ) As System.Data.DataRow(  )

This is the same as calling the three-parameter version with filterExpression and sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.

As an example of using the Select method, this line of code returns all rows whose Country column contains the value "Mexico":

Dim rows(  ) As DataRow = dt.Select("Country = 'Mexico'")

Because the sort and recordStates parameters were not specified, they default to "" (the empty string) and DataViewRowState.CurrentRows, respectively.

The Select method versus the SQL SELECT statement

If an application is communicating with a database over a fast, persistent connection, it is more efficient to issue SQL SELECT statements that load the DataSet with only the desired records, rather than to load the DataSet with a large amount of data and then pare it down with the DataTable's Select method. The Select method is useful for distributed applications that might not have a fast connection to the database. Such an application might load a large amount of data from the database into a DataSet object, then use several calls to the DataTable's Select method to locally view and process the data in a variety of ways. This is more efficient in this case because the data is moved across the slow connection only once, rather than once for each query.

Finding Column Values

The DataRow class has an Item property that provides access to the value in each column of a row. For example, this code iterates through all the columns of a row, displaying the value from each column (assume that row holds a reference to a DataRow object):

' Iterate through the column values.
Dim n As Integer
For n = 0 To row.Table.Columns.Count - 1
   Console.WriteLine(row(n))
Next

Note the expression used to find the number of columns: row.Table.Columns.Count. The DataRow object's Table property holds a reference to the DataTable object of which the row is a part. As will be discussed shortly, the Table object's Columns property maintains a collection of column definitions for the table. The Count property of this collection gives the number of columns in the table and therefore in each row.

The DataRow object's Item property is overloaded to allow a specific column value to be accessed by column name. The following code assumes that the DataRow row contains a column named "Description". The code displays the value of this column in this row:

Console.WriteLine(row("Description"))

Finding Column Definitions

The DataTable object's Columns property holds a ColumnsCollection object that in turn holds the definitions for the columns in the table. The following code iterates through the columns in the table and displays their names:

' Iterate through the columns.
Dim column As DataColumn
For Each column In dt.Columns
   Console.WriteLine(column.ColumnName)
Next

This code does the same thing, using a numeric index on the ColumnsCollection object:

' Iterate through the columns.
Dim n As Integer
For n = 0 To dt.Columns.Count - 1
   Console.WriteLine(dt.Columns(n).ColumnName)
Next

The ColumnsCollection object can also be indexed by column name. For example, if DataTable dt contains a column named "Description", this code gets a reference to the associated DataColumn object:

Dim column As DataColumn = dt.Columns("Description")

Changing, Adding, and Deleting Rows

To change data in a DataSet, first navigate to a row of interest and then assign new values to one or more of its columns. For example, the following line of code assumes that row is a DataRow object that contains a column named "Description". The code sets the value of the column in this row to be "Milk and cheese":

row("Description") = "Milk and cheese"

Adding a new row to a table in a DataSet is a three-step process:

  1. Use the DataTable class's NewRow method to create a new DataRow. The method takes no parameters.
  2. Set the values of the columns in the row.
  3. Add the new row to the table.

For example, assuming that dt is a DataTable object, and that the table has columns named "CategoryName" and "Description", this code adds a new row to the table:

' Add a row.
Dim row As DataRow = dt.NewRow(  )
row("CategoryName") = "Software"
row("Description") = "Fine code and binaries"
dt.Rows.Add(row)

The DataRow object referenced by row in this code can be indexed by the names "CategoryName" and "Description" because the DataRow object was created by the DataTable object's NewRow method and so has the same schema as the table. Note that the NewRow method does not add the row to the table. Adding the new row to the table must be done explicitly by calling the DataRowCollection class's Add method through the DataTable class's Rows property.

Deleting a row from a table is a one-liner. Assuming that row is a reference to a DataRow, this line deletes the row from its table:

row.Delete(  )

When changes are made to a row, the DataRow object keeps track of more than just the new column values. It also keeps track of the row's original column values and the fact that the row has been changed. The Item property of the DataRow object is overloaded to allow you to specify the desired version of the data that you wish to retrieve. The syntax of this overload is:

Public Overloads ReadOnly Property Item( _
   ByVal columnName As String, _
   ByVal version As System.Data.DataRowVersion _
) As Object

The parameters are:

columnName
The name of the column whose value is to be retrieved.

version
The version of the data to retrieve. This value must be a member of the System.Data.DataRowVersion enumeration. Its values are:

Current
Retrieve the current (changed) version.

Default
Retrieve the current version if the data has been changed, the original version if not.

Original
Retrieve the original (unchanged) version.

Proposed
Retrieve the proposed change. Proposed changes are changes that are made after a call to a DataRow object's BeginEdit method but before a call to its EndEdit or CancelEdit methods. For more information, see "Relations Between DataTables in a DataSet" later in this chapter.

For example, after making some changes in DataRow row, the following line displays the original version of the row's Description column:

Console.WriteLine(row("Description", DataRowVersion.Original))

The current value of the row would be displayed using any of the following lines:

Console.WriteLine(row("Description", DataRowVersion.Current))
Console.WriteLine(row("Description", DataRowVersion.Default))
Console.WriteLine(row("Description"))

Calling the DataSet object's AcceptChanges method commits outstanding changes. Calling the DataSet object's RejectChanges method rolls records back to their original versions.

TIP:  The code shown in this section affects only the DataSet object, not the data source. To propagate these changes, additions, and deletions back to the data source, use the Update method of the SqlDataAdapter class, as described in the next section, "Writing Updates Back to the Data Source."

If there are relations defined between the DataTables in the DataSet, it may be necessary to call the DataRow object's BeginEdit method before making changes. For more information, see "Relations Between DataTables in a DataSet" later in this chapter.

Writing Updates Back to the Data Source

Because DataSets are always disconnected from their data sources, making changes in a DataSet never has any effect on the data source. To propagate changes, additions, and deletions back to a data source, call the SqlDataAdapter class's Update method, passing the DataSet and the name of the table that is to be updated. For example, the following call to Update writes changes from the DataTable named Categories back to the SQL Server table of the same name:

da.Update(ds, "Categories")

Before using the Update method, however, you should understand how an SqlDataAdapter object performs updates. To change, add, or delete records, an SqlDataAdapter object must send SQL UPDATE, INSERT, or DELETE statements, respectively, to SQL Server. The forms of these statements either can be inferred from the SELECT statement that was provided to the SqlDataAdapter object or can be explicitly provided to the SqlDataAdapter object.

Example 8-2 shows an example of allowing an SqlDataAdapter object to infer the SQL UPDATE, INSERT, and DELETE statements required for applying updates to a database.

Example 8-2: Allowing an SqlDataAdapter object to infer SQL UPDATE, INSERT, and DELETE statements from a SELECT statement

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Create a data adapter object and set its SELECT command.
Dim strSelect As String = _
   "SELECT * FROM Categories"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, cn)
 
' Set the data adapter object's UPDATE, INSERT, and DELETE
' commands. Use the SqlCommandBuilder class's ability to auto-
' generate these commands from the SELECT command.
Dim autogen As New SqlCommandBuilder(da)
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Categories")
 
' Get a reference to the "Categories" DataTable.
Dim dt As DataTable = ds.Tables("Categories")
 
' Modify one of the records.
Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0)
row("Description") = "Milk and stuff"
 
' Add a record.
row = dt.NewRow(  )
row("CategoryName") = "Software"
row("Description") = "Fine code and binaries"
dt.Rows.Add(row)
 
' Delete a record.
row = dt.Select("CategoryName = 'MyCategory'")(0)
row.Delete(  )
 
' Update the database.
da.Update(ds, "Categories")
 
' Close the database connection.
cn.Close(  )

Note the following in Example 8-2:

  1. A SqlDataAdapter object is constructed with an argument of "SELECT * FROM Categories". This initializes the value of the SqlDataAdapter object's SelectCommand property.
  2. A SqlCommandBuilder object is constructed with the SqlDataAdapter object passed as an argument to its constructor. This step hooks the SqlDataAdapter object to the SqlCommandBuilder object so that later, when the SqlDataAdapter object's Update method is called, the SqlDataAdapter object can obtain SQL UPDATE, INSERT, and DELETE commands from the SqlCommandBuilder object.
  3. The SqlDataAdapter object is used to fill a DataSet object. This results in the DataSet object containing a DataTable object, named "Categories", that contains all the rows from the Northwind database's Categories table.
  4. One record each in the table is modified, added, or deleted.
  5. The SqlDataAdapter object's Update method is called to propagate the changes back to the database.

Step 5 forces the SqlCommandBuilder object to generate SQL statements for performing the database update, insert, and delete operations.When the Update method is called, the SqlDataAdapter object notes that no values have been set for its UpdateCommand, InsertCommand, and DeleteCommand prperties, and therefore queries the SqlCommandBuilder object for these commands. If any of these properties had been set on the SqlDataAdapter object, those values would have been used instead.

The SqlCommandBuildObject can be examined to see what commands were created. To see the commands that are generated in Example 8-2, add the following lines anywhere after the declaration and assignment of the autogen variable:

Console.WriteLine("UpdateCommand: " & autogen.GetUpdateCommand.CommandText)
Console.WriteLine("InsertCommand: " & autogen.GetInsertCommand.CommandText)
Console.WriteLine("DeleteCommand: " & autogen.GetDeleteCommand.CommandText)

The auto-generated UPDATE command contains the following text (note that line breaks have been added for clarity in the book):

UPDATE Categories
SET CategoryName = @p1 , Description = @p2 , Picture = @p3
WHERE ( 
  (CategoryID = @p4)
  AND
  ((CategoryName IS NULL AND @p5 IS NULL) OR (CategoryName = @p6)) )

Similarly, the INSERT command is:

INSERT INTO Categories( CategoryName , Description , Picture )
VALUES ( @p1 , @p2 , @p3)

And the DELETE command is:

DELETE FROM  Categories
WHERE (
  (CategoryID = @p1)
  AND
  ((CategoryName IS NULL AND @p2 IS NULL) OR (CategoryName = @p3)) )

Note the use of formal parameters (@p0, @p1, etc.) in each of these statements. For each row that is to be changed, added, or deleted, the parameters are replaced with values from the row, and the resulting SQL statement is issued to the database. The choice of which value from the row to use for which parameter is controlled by the SqlCommand object's Parameters property. This property contains an SqlParameterCollection object that in turn contains one SqlParameter object for each formal parameter. The SqlParameter object's ParameterName property matches the name of the formal parameter (including the "@"), the SourceColumn property contains the name of the column from which the value is to come, and the SourceVersion property specifies the version of the value that is to be used. Row versions were discussed in the previous section, "Changing, Adding, and Deleting Rows."

If desired, a DataSet object's UpdateCommand, InsertCommand, and DeleteCommand properties can be set directly. Example 8-3 sets the value of UpdateCommand and then performs an update using this command.

Example 8-3: Setting a DataSet object's UpdateCommand property

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Categories", cn)
 
' Create an UPDATE command.
'
' This is the command text.
' Note the parameter names: @Description and @CategoryID.
Dim strUpdateCommand As String = _
   "UPDATE Categories" _
   & " SET Description = @Description" _
   & " WHERE CategoryID = @CategoryID"
'
' Create a SqlCommand object and assign it to the UpdateCommand property.
da.UpdateCommand = New SqlCommand(strUpdateCommand, cn)
'
' Set up parameters in the SqlCommand object.
Dim param As SqlParameter
'
' @CategoryID
param = da.UpdateCommand.Parameters.Add( _
   New SqlParameter("@CategoryID", SqlDbType.Int))
param.SourceColumn = "CategoryID"
param.SourceVersion = DataRowVersion.Original
'
' @Description
param = da.UpdateCommand.Parameters.Add( _
   New SqlParameter("@Description", SqlDbType.NChar, 16))
param.SourceColumn = "Description"
param.SourceVersion = DataRowVersion.Current
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Categories")
 
' Get the table.
Dim dt As DataTable = ds.Tables("Categories")
 
' Get a row.
Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0)
 
' Change the value in the Description column.
row("Description") = "Milk and stuff"
 
' Perform the update.
da.Update(ds, "Categories")
 
' Close the database connection.
cn.Close(  )

Relations Between DataTables in a DataSet

The DataSet class provides a mechanism for specifying relations between tables in a DataSet. The DataSet class's Relations property contains a RelationsCollection object, which maintains a collection of DataRelation objects. Each DataRelation object represents a parent/child relationship between two tables in the DataSet. For example, there is conceptually a parent/child relationship between a Customers table and an Orders table, because each order must belong to some customer. Modeling this relationship in the DataSet has these benefits:

Example 8-4 loads a Customers table and an Orders table from the Northwind database and then creates a relation between them. The statement that actually creates the relation is shown in bold.

Example 8-4: Creating a DataRelation between DataTables in a DataSet

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = "SELECT * FROM Customers" _
   & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = "SELECT Orders.*" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.City = 'Buenos Aires')" _
   & "    AND (Customers.Country = 'Argentina')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))

As shown in Example 8-4, the DataRelationCollection object's Add method creates a new relation between two tables in the DataSet. The Add method is overloaded. The syntax used in Example 8-4 is:

Public Overloads Overridable Function Add( _
   ByVal name As String, _
   ByVal parentColumn As System.Data.DataColumn, _
   ByVal childColumn As System.Data.DataColumn _
) As System.Data.DataRelation

The parameters are:

name
The name to give to the new relation. This name can be used later as an index to the RelationsCollection object.

parentColumn
The DataColumn object representing the parent column.

childColumn
The DataColumn object representing the child column.

The return value is the newly created DataRelation object. Example 8-4 ignores the return value.

The DataSet's XML Capabilities

The DataSet class has several methods for reading and writing data as XML, including:

GetXml
Returns a string containing an XML representation of the data in the DataSet object.

GetXmlSchema
Returns a string containing the XSD schema for the XML returned by the GetXml method.

WriteXml
Writes the XML representation of the data in the DataSet object to a Stream object, a file, a TextWriter object, or an XmlWriter object. This XML can either include or omit the corresponding XSD schema.

WriteXmlSchema
Writes the XSD schema for the DataSet to a Stream object, a file, a TextWriter object, or an XmlWriter object.

ReadXml
Reads the XML written by the WriteXml method.

ReadXmlSchema
Reads the XSD schema written by the WriteXmlSchema method.

Example 8-5 shows how to write a DataSet to a file as XML using the WriteXml method.

Example 8-5: Saving a DataSet to a file as XML

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = "SELECT * FROM Customers" _
   & " WHERE CustomerID = 'GROSR'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet("MyDataSetName")
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = "SELECT Orders.*" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.CustomerID = 'GROSR')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))
 
' Save as XML.
ds.WriteXml("c:\temp.xml")

The majority of the code in Example 8-5 simply loads the DataSet with data. Actually writing the XML is done with the DataSet's WriteXml method at the end of Example 8-5. The contents of the file thus created are shown in Example 8-6. Some lines in Example 8-6 have been wrapped for printing in this book.

Example 8-6: The file produced by the code in Example 8-5

<?xml version="1.0" standalone="yes"?>
<MyDataSetName>
  <Customers>
    <CustomerID>GROSR</CustomerID>
    <CompanyName>GROSELLA-Restaurante</CompanyName>
    <ContactName>Manuel Pereira</ContactName>
    <ContactTitle>Owner</ContactTitle>
    <Address>5th Ave. Los Palos Grandes</Address>
    <City>Caracas</City>
    <Region>DF</Region>
    <PostalCode>1081</PostalCode>
    <Country>Venezuela</Country>
    <Phone>(2) 283-2951</Phone>
    <Fax>(2) 283-3397</Fax>
  </Customers>
  <Orders>
    <OrderID>10268</OrderID>
    <CustomerID>GROSR</CustomerID>
    <EmployeeID>8</EmployeeID>
    <OrderDate>1996-07-30T00:00:00.0000000-05:00</OrderDate>
    <RequiredDate>1996-08-27T00:00:00.0000000-05:00</RequiredDate>
    <ShippedDate>1996-08-02T00:00:00.0000000-05:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>66.29</Freight>
    <ShipName>GROSELLA-Restaurante</ShipName>
    <ShipAddress>5th Ave. Los Palos Grandes</ShipAddress>
    <ShipCity>Caracas</ShipCity>
    <ShipRegion>DF</ShipRegion>
    <ShipPostalCode>1081</ShipPostalCode>
    <ShipCountry>Venezuela</ShipCountry>
  </Orders>
  <Orders>
    <OrderID>10785</OrderID>
    <CustomerID>GROSR</CustomerID>
    <EmployeeID>1</EmployeeID>
    <OrderDate>1997-12-18T00:00:00.0000000-06:00</OrderDate>
    <RequiredDate>1998-01-15T00:00:00.0000000-06:00</RequiredDate>
    <ShippedDate>1997-12-24T00:00:00.0000000-06:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>1.51</Freight>
    <ShipName>GROSELLA-Restaurante</ShipName>
    <ShipAddress>5th Ave. Los Palos Grandes</ShipAddress>
    <ShipCity>Caracas</ShipCity>
    <ShipRegion>DF</ShipRegion>
    <ShipPostalCode>1081</ShipPostalCode>
    <ShipCountry>Venezuela</ShipCountry>
  </Orders>
</MyDataSetName>

The syntax of this overloaded version of the WriteXml function is:

Public Overloads Sub WriteXml(ByVal fileName As String)

The fileName parameter specifies the full path of a file into which to write the XML.

The XML document written by the DataSet class's WriteXml method can be read back into a DataSet object using the ReadXml method. Example 8-7 reads back the file written by the code in Example 8-5.

Example 8-7: Recreating a DataSet object from XML

Dim ds As New DataSet(  )
ds.ReadXml("c:\temp.xml")

The XML created by the WriteXml method contains only data--no schema information. The ReadXml method is able to infer the schema from the data. To explicitly write the schema information, use the WriteXmlSchema method. To read the schema back in, use the ReadXmlSchema method.

The GetXml and GetXmlSchema methods work the same as the WriteXml and WriteXmlSchema methods, except that each returns its result as a string rather than writing it to a file.

Binding a DataSet to a Windows Forms DataGrid

DataSet and DataTable objects can be bound to Windows Forms DataGrid objects to provide an easy way to view data. This is done by calling a DataGrid object's SetDataBinding method, passing the object that is to be bound to the grid. The syntax of the SetDataBinding method is:

Public Sub SetDataBinding( _
   ByVal dataSource As Object, _
   ByVal dataMember As String _
)

The parameters are:

dataSource
The source of the data to show in the grid. This can be any object that exposes the System.Collections.IList or System.Data.IListSource interfaces, which includes the DataTable and DataSet classes discussed in this chapter.

dataMember
If the object passed in the dataSource parameter contains multiple tables, as a DataSet object does, the dataMember parameter identifies the table to display in the DataGrid. If a DataTable is passed in the dataSource parameter, the dataMember parameter should contain either Nothing or an empty string.

Example 8-8 shows how to bind a DataSource object to a DataGrid. The DataSource object contains a Customers table and an Orders table, and a relation between them. The call to the DataGrid object's SetDataBinding method specifies that the Customers table should be shown in the grid. Figure 8-3 shows the resulting DataGrid display.

Example 8-8: Creating a DataSet and binding it to a Windows Forms DataGrid

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = _
   "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers" _
   & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = _
   "SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate," _
   & " Orders.ShippedDate" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.City = 'Buenos Aires')" _
   & "    AND (Customers.Country = 'Argentina')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))
 
' Bind the data set to a grid.
' Assumes that grid contains a reference to a 
' System.WinForms.DataGrid object.
grd.SetDataBinding(ds, "Customers")

Figure 8-3. The display generated by the code in Example 8-8
Figure 3

Note in Figure 8-3 that each row in this DataGrid has a "+" icon. The reason is that the DataGrid object has detected the relation between the Customers table and the Orders table. Clicking on the "+" reveals all of the relations for which the Customers table is the parent. In this case, there is only one, as shown in Figure 8-4.

Figure 8-4. Clicking the "+" reveals relations
Figure 4

The name of the relation in the display is a link. Clicking on this link loads the grid with the child table in the relation, as shown in Figure 8-5.

Figure 8-5. The Orders table
Figure 5

While the child table is displayed, the corresponding row from the parent table is displayed in a header (shown in Figure 8-5). To return to the parent table, click the left-pointing triangle in the upper-right corner of the grid.

Binding a DataSet to a Web Forms DataGrid

Example 8-9 shows how to bind a DataTable object to a Web Forms DataGrid object. Figure 8-6 shows the resulting display in a web browser.

Example 8-9: Creating a DataTable and binding it to a Web Forms DataGrid

<%@ Page Explicit="True" Strict="True" %>
 
<script language="VB" runat="server">
 
   Protected Sub Page_Load(ByVal Sender As System.Object, _
      ByVal e As System.EventArgs)
      
      If Not IsPostback Then ' True the first time the browser hits the page.
         ' Bind the grid to the data.
         grdCustomers.DataSource = GetDataSource(  )
         grdCustomers.DataBind(  )
      End If   
      
   End Sub ' Page_Load
    
   Protected Function GetDataSource(  ) As System.Collections.ICollection
 
      ' Open a database connection.
      Dim strConnection As String = _
         "Data Source=localhost;Initial Catalog=Northwind;" _
         & "Integrated Security=True"
      Dim cn As New System.Data.SqlClient.SqlConnection(strConnection)
      cn.Open(  )
      
      ' Set up a data adapter object.
      Dim strSql As String = _
         "SELECT CustomerID, CompanyName, ContactName, Phone" _
         & " FROM Customers" _
         & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
      Dim da As New System.Data.SqlClient.SqlDataAdapter(strSql, cn)
      
      ' Load a data set.
      Dim ds As New System.Data.DataSet(  )
      da.Fill(ds, "Customers")
      
      ' Close the database connection.
      cn.Close(  )
      
      ' Wrap the Customers DataTable in a DataView object.
      Dim dv As New System.Data.DataView(ds.Tables("Customers"))
         
      Return dv
      
   End Function ' GetDataSource
 
</script>
 
<html>
   <body>
      
      <asp:DataGrid id=grdCustomers runat="server" ForeColor="Black">
         <AlternatingItemStyle BackColor="Gainsboro" />
         <FooterStyle ForeColor="White" BackColor="Silver" />
         <ItemStyle BackColor="White" />
         <HeaderStyle Font-Bold="True" ForeColor="White" 
               BackColor="Navy" />
      </asp:DataGrid>
      
   </body>
</html>

Figure 8-6. The display generated by the code in Example 8-9
Figure 6

Note the following:

Typed DataSets

There is nothing syntactically wrong with this line of code:

Dim dt As System.Data.DataTable = ds.Tables("Custumers")

However, "Custumers" is misspelled. If it were the name of a variable, property, or method, it would cause a compile-time error (assuming the declaration were not similarly misspelled). However, because the compiler has no way of knowing that the DataSet ds will not hold a table called Custumers, this typographical error will go unnoticed until runtime. If this code path is not common, the error may go unnoticed for a long time, perhaps until after the software is delivered and running on thousands of client machines. It would be better to catch such errors at compile time.

Microsoft has provided a tool for creating customized DataSet-derived classes. Such classes expose additional properties based on the specific schema of the data that an object of this class is expected to hold. Data access is done through these additional properties rather than through the generic Item properties. Because the additional properties are declared and typed, the Visual Basic .NET compiler can perform compile-time checking to ensure that they are used correctly. Because the class is derived from the DataSet class, an object of this class can do everything that a regular DataSet object can do, and it can be used in any context in which a DataSet object is expected.

Consider again Example 8-1, shown earlier in this chapter. This fragment of code displays the names of the customers in the Northwind database that are located in London. Compare this to Example 8-10, which does the same thing but uses a DataSet-derived class that is specifically designed for this purpose.

Example 8-10: Using a typed DataSet

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim da As New SqlDataAdapter(strSelect, cn)
 
' Load a data set.
Dim ds As New LondonCustomersDataSet(  )
da.Fill(ds, "LondonCustomers")
 
' Close the database connection.
cn.Close(  )
 
' Do something with the data set.
Dim i As Integer
For i = 0 To ds.LondonCustomers.Count - 1
   Console.WriteLine(ds.LondonCustomers(i).CompanyName)
Next

Note that in Example 8-10, ds is declared as type LondonCustomersDataSet, and this class has properties that relate specifically to the structure of the data that is to be loaded into the DataSet. However, before the code in Example 8-10 can be written, it is necessary to generate the LondonCustomersDataSet and related classes.

First, create an XML schema file that defines the desired schema of the DataSet. The easiest way to do this is to write code that loads a generic DataSet object with data having the right schema and then writes that schema using the DataSet class's WriteXmlSchema method. Example 8-11 shows how this was done with the LondonCustomers DataSet.

Example 8-11: Using the WriteXmlSchema method to generate an XML schema

' This code is needed only once. Its purpose is to create
' an .xsd file that will be fed to the xsd.exe tool.
 
' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim da As New SqlDataAdapter(strSelect, cn)
 
' Load a data set.
Dim ds As New DataSet("LondonCustomersDataSet")
da.Fill(ds, "LondonCustomers")
 
' Close the database connection.
cn.Close(  )
 
' Save as XSD.
ds.WriteXmlSchema("c:\LondonCustomersDataSet.xsd")

Next, run Microsoft's XML Schema Definition Tool (xsd.exe) against the XML schema file you just generated. Here is the command line used for the LondonCustomers DataSet:

xsd /d /l:VB LondonCustomersDataSet.xsd

The /d option indicates that a custom DataSet and related classes should be created. The /l:VB option specifies that the generated source code should be written in Visual Basic .NET (the tool is also able to generate C# source code). With this command line, the tool generates a file named LondonCustomersDataSet.vb, which contains the source code.

Finally, add the generated .vb file to a project and make use of its classes.

Reading Data Using a DataReader

As you have seen, the DataSet class provides a flexible way to read and write data in any data source. There are times, however, when such flexibility is not needed and when it might be better to optimize data-access speed as much as possible. For example, an application might store the text for all of its drop-down lists in a database table and read them out when the application is started. Clearly, all that is needed here is to read once through a result set as fast as possible. For needs such as this, ADO.NET has DataReader classes.

Unlike the DataSet class, DataReader classes are connected to their data sources. Consequently, there is no generic DataReader class. Rather, each managed provider exposes its own DataReader class, which implements the System.Data.IDataReader interface. The SQL Server managed provider exposes the SqlDataReader class (in the System.Data.SqlClient namespace). DataReader classes provide sequential, forward-only, read-only access to data. Because they are optimized for this task, they are faster than the DataSet class.

Example 8-12 shows how to read through a result set using an SqlDataReader object.

Example 8-12: Using a SqlDataReader object

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a command object.
Dim strSql As String = "SELECT * FROM Customers" _
   & " WHERE Country = 'Germany'"
Dim cmd As New SqlCommand(strSql, cn)
 
' Set up a data reader.
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader(  )
 
' Use the data.
Do While rdr.Read
   Console.WriteLine(rdr("CompanyName"))
Loop
 
' Close the database connection.
cn.Close(  )

Opening a connection to the database is done the same as when using a DataSet object. However, with a DataReader object, the connection must remain open while the data is read. Instead of an SqlDataAdapter object, an SqlCommand object is used to hold the command that will be executed to select data from the database. The SqlCommand class's ExecuteReader method is called to execute the command and to return an SqlDataReader object. The SqlDataReader object is then used to read through the result set. Note the Do While loop in Example 8-12, repeated here:

Do While rdr.Read
   Console.WriteLine(rdr("CompanyName"))
Loop

Developers who are used to coding against classic ADO will note that this loop appears to lack a "move to the next row" statement. However, it is there. The SqlDataReader class's Read method performs the function of positioning the SqlDataReader object onto the next row to be read. In classic ADO, a RecordSet object was initially positioned on the first row of the result set. After reading each record, the RecordSet object's MoveNext method had to be called to position the RecordSet onto the next row in the result set. Forgetting to call MoveNext was a common cause of infinite loops. Microsoft removed this thorn as follows:

These changes result in tight, easy-to-write loops such as the one in Example 8-12.

The DataReader provides an Item property for reading column values from the current row. The Item property is overloaded to take either an integer that specifies the column number, which is zero-based, or a string that specifies the column name. The Item property is the default property of the SqlDataReader class, so it can be omitted. For example, this line:

Console.WriteLine(rdr("CompanyName"))

is equivalent to this line:

Console.WriteLine(rdr.Item("CompanyName"))

Executing Stored Procedures Through a SqlCommand Object

To execute a stored procedure, set an SqlCommand object's CommandText property to the name of the stored procedure to be executed, and set the CommandType property to the constant CommandType.StoredProcedure (defined in the System.Data namespace). Then call the ExecuteNonQuery method. Example 8-13 does just that.

Example 8-13: Executing a parameterless stored procedure

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a command object. (Assumes that the database contains a
' stored procedure called "PurgeOutdatedOrders".)
Dim cmd As New SqlCommand("PurgeOutdatedOrders", cn)
cmd.CommandType = CommandType.StoredProcedure
 
' Execute the command.
cmd.ExecuteNonQuery(  )
 
' Close the database connection.
cn.Close(  )

TIP:  Example 8-13 assumes for the sake of demonstration that the database contains a stored procedure called "PurgeOutdatedOrders". If you would like to have a simple stored procedure that works with Example 8-13, use this one:

CREATE PROCEDURE PurgeOutdatedOrders AS
DELETE FROM Orders
WHERE OrderDate < '04-Jul-1990' 
   AND ShippedDate IS NOT NULL

See your SQL Server documentation for information on how to create stored procedures.

Some stored procedures have parameters, and some have a return value. For these stored procedures, the SqlCommand class provides the Parameters property. The Parameters property contains a reference to an SqlParameterCollection object. To pass parameters to a stored procedure and/or to read the return value of a stored procedure, add SqlParameter objects to this collection.

Example 8-14 calls a stored procedure that takes a single argument.

Example 8-14: Executing a parameterized stored procedure

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a command object. (Assumes that the database contains a
' stored procedure called "PurgeOutdatedOrders2".)
Dim cmd As New SqlCommand("PurgeOutdatedOrders2", cn)
cmd.CommandType = CommandType.StoredProcedure
 
' Set up the @BeforeDate parameter for the stored procedure.
Dim param As New SqlParameter("@BeforeDate", SqlDBType.DateTime)
param.Direction = ParameterDirection.Input
param.Value = #7/4/1990#
cmd.Parameters.Add(param)
 
' Execute the command.
cmd.ExecuteNonQuery(  )
 
' Close the database connection.
cn.Close(  )

TIP:  Example 8-14 assumes for the sake of demonstration that the database contains a stored procedure called "PurgeOutdatedOrders2". If you would like to have a simple stored procedure that works with Example 8-14, use this one:

CREATE PROCEDURE PurgeOutdatedOrders2 
@BeforeDate datetime
AS
DELETE FROM Orders
WHERE OrderDate < @BeforeDate
   AND ShippedDate IS NOT NULL

See your SQL Server documentation for information on how to create stored procedures.

The steps taken in Example 8-14 are:

  1. Open a connection to the database.
  2. Instantiate an SqlCommand object using this constructor:
    Public Overloads Sub New( _
       ByVal cmdText As String, _
       ByVal connection As System.Data.SqlClient.SqlConnection _
    )

    The cmdText parameter specifies the name of the stored procedure, and the connection parameter specifies the database connection to use.

  3. Set the SqlCommand object's CommandType property to CommandType.StoredProcedure to indicate that the cmdText parameter passed to the constructor is the name of a stored procedure.
  4. Create an SqlParameter object to pass a value in the PurgeOutdatedOrders2 stored procedure's @BeforeDate parameter. This is done as follows:

    1. Instantiate an SqlParameter object using this constructor:
    2. Public Overloads Sub New( _
         ByVal parameterName As String, _
         ByVal dbType As System.Data.SqlClient.SqlDbType _
      )

      The parameterName parameter specifies the name of the stored procedure parameter and should match the name as given in the stored procedure. The dbType parameter specifies the SQL Server data type of the parameter. This parameter can take any value from the SqlDbType enumeration.

    3. Set the SqlParameter object's Direction property to ParameterDirection.Input. This indicates that a value will be passed from the application to the stored procedure.
    4. Set the Value property of the SqlParameter object.
    5. Add the SqlParameter object to the SqlCommand object's Parameters collection by calling the SqlParameterCollection object's Add method.

  5. Execute the stored procedure.

Note the SqlParameter class's Direction property. Setting this property to the appropriate value from the ParameterDirection enumeration (declared in the System.Data namespace), can make a SqlParameter object an input parameter, an output parameter, an in/out parameter, or the stored procedure's return value. The values in the ParameterDirection enumeration are:

Input
The parameter provides a value to the stored procedure.

InputOutput
The parameter provides a value to the stored procedure and receives a new value back from the stored procedure.

Output
The parameter receives a value back from the stored procedure.

ReturnValue
The parameter receives the stored procedure's return value.

Summary

In this chapter, you learned about Microsoft's data-access technology, ADO.NET. You learned how to connect to a database, how to read data with either a DataSet object or a DataReader object, how to navigate and change data in a DataSet, how to use the DataSet's XML capabilities, how to generate typed DataSets, and how to execute stored procedures using an SqlCommand object.

Back to: Programming Visual Basic .NET


oreilly.com Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
International | About O'Reilly | Affiliated Companies | Privacy Policy

© 2001, O'Reilly & Associates, Inc.
webmaster@oreilly.com