Languages: VB | C# | JScript
Technologies: ADO | Typed Data Sets
Create Typed Data Tables ... without VS .NET
Make It Easy to Reference Information with ASP.NET
By Wayne S. Freeze
Visual Studio .NET includes a powerful tool that helps you create your own typed data sets. However, if you choose to write your ASP.NET applications without VS .NET, and you want to use typed objects in them, you'll need another way to create those typed objects.
Fortunately, it isn't difficult to create your own typed objects using inheritance, although the design of ADO.NET makes doing so somewhat more difficult than simply creating a new class and inheriting the DataTable class. In this article, I'll show you how you can build an ASP.NET program that allows you to create your own typed objects.
Benefits of Typed Tables
You might want to use typed tables for several reasons, all of which are based on the fact that it's easier to reference information in a typed table than in a normal expression. For instance, consider the following expression:
It starts with a call to CType, which ensures the data type the expression returns is the proper type - a string in this case. Then, the expression starts with a DataTable object, uses the Rows property to access a DataRowsCollection object, and finally uses the Item property to access the object containing the actual data value. However, the following code is much easier to understand:
This expression begins with an object derived from the DataTable object and references an appropriately typed property to retrieve the information contained in the associated column.
Both these expressions are interchangeable, allowing you to use the Option Strict statement to avoid automatic type conversions where information may be lost. However, the typed object is easier to understand because it avoids unnecessary properties and conversions. Typed objects also can help prevent errors by detecting invalid column names at compile time rather than at run time.
Inherit a Data Row
You create a typed table by creating a class derived from the DataTable class. The properties associated with each column are defined in a second class derived from the DataRow class. FIGURE 1 contains a typical property definition for a column that sets and returns the value from the underlying data row.
Public Property CDId () As System.Int32
Return CType(Me.Item("CDId"), System.Int32)
Set(ByVal Value As System.Int32)
Me.Item("CDId") = Value
FIGURE 1: A typical property definition for a column sets and returns the value from the underlying data-row object.
Notice the property's type is Int32 rather than Object, which typically would be returned by the DataRow.Item property. The fact that the property's type is Int32 also implies the CType function is necessary in the Get property routine because Option Strict will not permit you to assign a less specific type to a more restrictive type without an explicit type conversion. The Set routine doesn't suffer from this problem because Option Strict permits assignments as long as the destination type is less restrictive than the type of the source value.
In addition to the property definitions, you need to create a class constructor that calls the base class' constructor. The only complicating factor is that the constructor takes a single parameter. Fortunately, it's sufficient to accept the parameter and pass it along to the base-class constructor routine:
Friend Sub New(ByVal rb As DataRowBuilder)
Build a New Table
After you create a custom class for the columns in your table, you need to create a custom version of the DataTable class. This class inherits the DataTable class and includes the custom DataRow class I mentioned before.
The class' constructor shown below is used to create a new instance of the class. The first step is to call the data table's constructor using the name of the table from the database. Although the name isn't necessary, it's a good way to document the new object:
Friend Sub New()
Me.Columns.Add(New DataColumn("CDId", _
GetType(System.Int32), Nothing, _
Once the base class has been initialized, each of the columns associated with the table should be added. A new DataColumn object needs to be created for each column in the database's table. For the sake of consistency, you should use the column name you used for the property, along with the proper data type for the column.
The following routine is required to make the NewRow method work properly. The routine is called automatically when the NewRow method is invoked, and all the routine needs to do is create a new instance of the custom DataRow class by passing along the DataRowBuilder argument:
Protected Overrides Function NewRowFromBuilder( _
ByVal builder As DataRowBuilder) As DataRow
Return New CDsRow(builder)
Two additions to this class will make your custom DataTable class easier to use. The first addition establishes a default property for the class, which allows you to retrieve a specific row from the object. The second addition allows a For Each statement to iterate through all of the rows in the data table.
The Item property allows you to retrieve a single row from the collection of rows associated with the DataTable. Normally, these rows are exposed through the Rows property. However, by adding the Item property, you need not specify the property in order to use it. Specifying the object name by itself is sufficient:
Default Public ReadOnly Property Item( _
ByVal index As Integer) As CDsRow
The Item property takes a single parameter, which is the index of the row you wish to return from the collection. All the property does is call the Rows method using the same index value and then convert the return value to the appropriate type. You also can choose to override the base class' Rows property to return the custom DataRow class. However, I felt that using Item as the property name was more consistent with the way other classes work.
The second addition to this class allows you to use the For Each statement to process the collection of data. The For Each statement calls the GetEnumerator method to retrieve the next item in the collection. Like many routines you inherit, this routine simply calls the underlying method in the base class:
Public Function GetEnumerator() As IEnumerator _
The only interesting part of this routine is the Implements keyword. This identifies the corresponding method in the IEnumerable class the routine implements. Note that for this routine to work properly, you need to use the following statement after the Class statement to indicate which interface the class is implementing:
Other Useful Features
The code I've described so far is the minimum required to implement a typed data table. However, as with any class you create, you can add other properties and methods. For instance, you could include logic that validates the information in your custom data row. You could add routines that return formatted values suitable for display on your Web page. Or, you could add a routine that loads data into the table, such as the one shown in FIGURE 2.
Public Sub Fill()
Dim i As Integer
Dim dr As System.Data.DataRow
Dim rdr As System.Data.SqlClient.SqlDataReader
If Not SelectCommand Is Nothing Then
rdr = SelectCmd.ExecuteReader()
Do While rdr.Read()
dr = Me.NewRow
For i = 0 To rdr.FieldCount - 1
dr(i) = rdr.GetValue(i)
FIGURE 2: Loading data into the typed table using the Fill method.
To make life easier, the Fill method assumes that the SelectCommand property shown in the next code snippet has been initialized by the programmer properly, using the typed object class. The rest of these properties (DeleteCommand, InsertCommand, and UpdateCommand) are available in case the programmer chooses to extend the class to handle database updates:
Public SelectCommand As SqlCommand
Public DeleteCommand As SqlCommand
Public InsertCommand As SqlCommand
Public UpdateCommand As SqlCommand
The Fill method is a simple routine that uses a SqlDataReader object to retrieve the rows from the database and store them in the typed table. The method begins by verifying that a value has been assigned to the SelectCommand property and then opens the connection to the database. Then, the command is used to create a new SqlDataReader.
A Do loop is used to copy the rows from the SqlDataReader into the data table's Rows collection. When the loop is finished, the reader and connection are closed.
Time to Be Lazy
This approach has one big drawback, though: Building these classes manually can be very tedious and introduces the possibility of typos. However, because this is the type of thing at which computers excel, it makes a great deal of sense to let your computer build your classes for you.
All the information needed to create the classes can be easy to find in your database. The Typed Table Generator prompts the user for the values needed to get the information from the database (see FIGURE 3). This information includes the name of the database server, the name of the database, the login ID and password, the SQL Select statement used to create the table, and the name you want to use for the class.
FIGURE 3: Running the Typed Table Generator.
After entering the information in the Web page, click the Add Typed Table button, which calls the AddTable_Click event handler (see FIGURE 4). This code will add the table to a DataSet object stored in the Session object, which means you can generate all the tables you need for your program and paste the results into your application.
Private Sub AddTable_Click(sender As Object, e As EventArgs)
Dim ds As DataSet
StatusInfo.Text = ""
Code.Text = ""
If Not Session("ds") Is Nothing Then
ds = CType(Session("ds"), DataSet)
ds = New DataSet
Code.Text = BuildTDS(ds)
If StatusInfo.Text.Length = 0 Then
StatusInfo.Text = TableName.Text & " has been added."
Session("ds") = ds
FIGURE 4: The AddTable_Click event processes each new table.
After retrieving the DataSet object, the GetDBInfo routine is called to collect information about the new table. Then, the BuildTDS routine is called to generate the typed tables. If no error messages are displayed in the StatusInfo control, a success message appears. Finally, the DataSet is saved in the Session object for the next click.
Collect Database Information
The GetDBInfo routine begins processing by calling the BuildConnectionStr function to build a connection string using the information the user entered on the form. Then, the routine initializes a SqlCommand object with the Select statement entered on the form. After that, the routine uses a SqlDataAdapter to get information about the table from the database server using the FillSchema method (see FIGURE 5).
Private Sub GetDBInfo(ds As DataSet)
Dim conn As New SQLConnection(BuildConnectionStr())
Dim adpt As New SqlDataAdapter()
Dim cmd As New SqlCommand()
cmd.CommandText = QueryName.Text
cmd.Connection = conn
adpt.SelectCommand = cmd
adpt.FillSchema(ds, SchemaType.Source, _
Catch ex As Exception
StatusInfo.Text = ex.Message
FIGURE 5: The GetDBInfo subroutine collects information from the database for the new table.
The FillSchema method doesn't transfer any data. It simply transfers the schema information related to the query and stores it as a DataTable object in the data set. This information includes the name of each column, its data type, and size. Other information, such as the primary keys, also will be returned if you are querying a single table.
The rest of the program is devoted to building the classes and objects I described earlier in this article. The BuildColumn function (see FIGURE 6) is typical of this code. It begins by declaring a new instance of the StringBuilder object and then appending the various statements to the data already in the StringBuilder object. I could have simply used the concatenation operator (&), but the StringBuilder class is much more efficient.
Private Function BuildColumn(dc as DataColumn) As String
Dim s As New StringBuilder
s.Append(" Public Property ")
s.Append("() As ")
s.Append(" Return CType(Me.Item(""")
s.Append(" End Get")
s.Append(""") = Value")
s.Append(" End Set")
s.Append(" End Property")
FIGURE 6: The BuildColumn function uses schema information to build a column's property routine.
When appropriate, values are extracted from the DataColumn object and appended to the StringBuilder object. Perhaps the only tricky part in this code is using the ToString method to return the data type of a column. It takes advantage of the fact that when there is nothing appropriate to return, ToString simply returns the name of the object. In this case, this is exactly the value needed to build the class.
Sprinkled throughout the code are calls that append vbCrLf. This is necessary to prevent all of the code from appearing on a single line. Occasionally, two instances of vbCrLf will be appended. This isn't required and was done simply to make the generated code more readable.
Using a Typed Table
By using a typed table, you can reduce the amount of code required to build your application. Consider the simple Web page shown in FIGURE 7. This application displays selected fields about the music CD specified by the query string.
FIGURE 7: Displaying information about a CD using a typed table.
The code used to retrieve the data is shown in FIGURE 8. Notice that the code to build the SqlCommand object is about as long as the code to retrieve the data from the database and display it on the form.
Dim conn As New SqlConnection(ConnectionStr)
Dim cmd As SqlCommand
Dim QueryStr As String
Dim CDsTable As New CDInfo
QueryStr ="Select CDId, ArtistName, CDTitle "
QueryStr &= "From CDs, Artists "
QueryStr &= "Where CDs.ArtistId = Artists.ArtistId "
QueryStr &= "And CDId="
QueryStr &= Request.QueryString("CDId")
cmd = New SqlCommand(QueryStr, conn)
CDsTable.SelectCommand = cmd
If CDsTable.Count > 0 Then
TextBox1.Text = CDsTable(0).CDId
TextBox2.Text = CDsTable(0).CDTitle
TextBox3.Text = CDsTable(0).ArtistName
FIGURE 8: The GetData routine illustrates one way you might use a typed table.
Building a typed DataTable object through inheritance is fairly straightforward, but the process has a few idiosyncrasies. Otherwise, the biggest problem is typing all the code manually. Fortunately, it's easy to extract the necessary information from the database using standard ADO.NET classes and to create a program that generates the code line by line. The final result is worth the effort because it results in applications that are easier to understand.
The source code for the Typed Table Generator is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including ASP.NET Database Programming Bible [Hungry Minds, 2002], Windows Game Programming with Visual Basic and DirectX [QUE, 2001], and Unlocking OLAP with SQL Server and Excel 2000 [Hungry Minds, 2000]. He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves receiving e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.