ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Programming Visual Basic .NET

ADO.NET, Part 5

Related Reading

Programming Visual Basic .NET
By Dave Grundgeiger

by Dave Grundgeiger

This is the fifth installment from the Programming Visual Basic .NET chapter on ADO.NET, focusing on Typed DataSets, reading data into a DataReader, and executing stored procedures through a SqlCommand object.

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 _
      )

      Previous Excerpts

      ADO.NET, Part 1


      ADO.NET, Part 2


      ADO.NET, Part 3


      ADO.NET, Part 4

      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.


View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.

Copyright © 2009 O'Reilly Media, Inc.