O'Reilly Book Excerpts: Programming Visual Basic .NET
ADO.NET, Part 5
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.
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
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
/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
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:
- The DataReader object is initially positioned just prior to the first row of the result set (and therefore has to be repositioned before reading any data).
- The Read method repositions the DataReader to the next row, returning
Trueif the DataReader is positioned onto a valid row and
Falseif the DataReader is positioned past the last row in the result set.
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:
is equivalent to this line:
Pages: 1, 2