Developer

Which dataset should you choose?

The choice between untyped and typed datasets rests in the hands of the developer. Examine the differences between the two.

I've recently noticed some confusion among coworkers and clients regarding dataset options. This confusion stems from the fact that a dataset may or may not be typed. The developer makes the choice, which affects how the dataset is utilized in the code. In this article, I examine dataset types to further clarify their differences.

Not my type

A dataset is considered untyped if you don't have access to the dataset schema at design time. It isn't based on a dataset schema and a custom DataSet class; instead, it's based on the generic ADO.NET DataSet class. That is, an untyped dataset doesn't have an inherent structure.

The developer is responsible for creating the tables, columns, constraints, and any other necessary objects at design time in the Properties window, at run time in code, or by allowing a data adapter's mappings to do so when using the data adapter to fill the dataset.

Developers manually create untyped datasets. You may drag-and-drop elements in the design environment, or they're created via code. The following VB.NET code creates an untyped dataset that connects to the Northwind database and displays column values from the query results:

Imports System.Data
Imports System.Data.SqlClient
Module BuilderSample
Sub Main()
Dim cntString As String
Dim strSQL As String
cntString = "Data Source=localhost;Initial Catalog=Northwind;Integrated
Security=SSPI"
strSQL = "SELECT * FROM Employees"
Dim conn As SqlConnection = Nothing
Dim comm As SqlCommand = Nothing
Dim rdr As SqlDataReader = Nothing
Try
conn = New SqlConnection(cntString)
conn.Open()
comm = New SqlCommand(strSQL, conn)
rdr = comm.ExecuteReader()
While (rdr.Read())
Console.Write("Name: ")
Console.Write(rdr.GetValue(2))
Console.WriteLine(rdr.GetValue(1))
End While
Catch ex As Exception
Console.WriteLine("Error encountered: " + ex.ToString())
Finally
If Not (conn Is Nothing) Then
conn.Close()
End If
End Try
End Sub
End Module

The C# equivalent is almost identical:

using System;
using System.Data;
using System.Data.SqlClient;
namespace BuilderSamples {
class BuilderUnTypedDataSet {
static void Main(string[] args) {

string ctnString, strSQL;
cntString = "Data Source=localhost;Initial Catalog=Northwind;Integrated
Security=SSPI";
strSQL = "SELECT * FROM Employees";
SqlConnection conn = null;
SqlCommand comm = null;
SqlDataReader rdr = null;
try {
conn = new SqlConnection(cntString);
conn.Open();
rdr = comm.ExecuteReader();
while (rdr.Read()) {
Console.Write("Name: ");
Console.Write(rdr.GetValue(2));
Console.WriteLine(rdr.GetValue(1));
} } catch (Exception ex ) {
Console.WriteLine("Error: " + ex.ToString());
} finally {
if (conn != null) {
conn.Close(); } } } } }

The important aspect of this code is that individual data items are accessed by index values (column). I'm familiar with the Northwind database, so I'm aware of the fact that the second column holds the first name and the first column stores the last name. This doesn't create any problems when the developer is familiar with the data source.

A developer may easily utilize a tool such as SQL Server Enterprise Manager to gather a database table's design; however, this option isn't always available. Large organizations restrict database design access, so the developer may not have the tools and/or the necessary access.

Another problem may arise when the code is accessed in the future. The original developer or someone completely foreign to the initial process may view the code. The presentation of columns one and two mean nothing to this person. This is a situation where the advantages of a typed dataset are clear.

Choose your type

A typed dataset is a class that derives from the base DataSet class. As such, it inherits all the methods, events, and properties of a DataSet. It also provides strongly typed methods, events, and properties for its unique elements. This means you can access tables and columns by name instead of using collection-based methods (such as the column index value used in previous examples). Aside from the improved readability of the code, a typed dataset allows the Visual Studio .NET code editor to automatically complete lines as you type.

Additionally, the strongly typed dataset provides access to values as the correct type at compile time. With a strongly typed dataset, type mismatch errors are caught when the code is compiled rather than at run time.

You may create a typed dataset using the .NET Framework SDK's command-line xsd.exe tool or by way of the Visual Studio .NET IDE. The xsd.exe tool uses an XML schema file for input and dataset creation, while the Visual Studio .NET IDE can automate the typed dataset creation process. Here are the simple steps for the process:

  1. Select Add New Item | DataSet to your Visual Studio .NET project (provide appropriate name).
  2. Drag the DataSet source from the Server Explorer (data source) toolbar to the newly created DataSet design window.
  3. You may remove columns from the data source added to the workspace (highlight the column and select Delete).

The result is an XML schema and a corresponding class file; the class is derived from the base System.Data.DataSet class. In this example, I added the Northwind employees table to new DataSet with the following XML schema generated (only the employee id, last name, and first name were kept):

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1"
targetNamespace="http://tempuri.org/Dataset1.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified"
xmlns="http://tempuri.org/Dataset1.xsd"
xmlns:mstns=http://tempuri.org/Dataset1.xsd
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Dataset1" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Employees">
<xs:complexType>
<xs:sequence>
<xs:element name="EmployeeID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="LastName" type="xs:string" />
<xs:element name="FirstName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Dataset1Key1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Employees" />
<xs:field xpath="mstns:EmployeeID" />
</xs:unique>
</xs:element>
</xs:schema>

In turn, a class is created using the XML schema file as input (the xsd.exe command-line tool using the generated XML Schema file). I kept the system-generated name of Dataset1 for the example XML schema. This name is important when utilizing the accompanying class because it's the name of the class.

A Dataset1 object is instantiated when necessary. The generated class may be viewed in the Class View tab (select Class View from the View file dropdown menu in Visual Studio .NET). The class generates methods for accessing individual rows as well as other elements, but that is beyond this article.

However, individual properties and corresponding methods are created for accessing column values. In the example XML schema, I included the employee id as well as the first and last name columns. Consequently, methods are added to access these values as class properties. These methods are easily accessed with Visual Studio .NET's IntelliSense feature (which is another great feature of typed datasets). The following C# sample takes advantage of the new typed dataset:

using System;
using System.Data;
using System.Data.SqlClient;
namespace BuilderSamples {
class TypedDataSet {
static void Main(string[] args) {
string conn = "Data Source=localhost;Initial Catalog=Northwind;Integrated
Security=SSPI";
string strSQL = "SELECT * FROM Employees";
SqlDataAdapter sda = null;
try {
Dataset1 emp = new Dataset1();
sda = new SqlDataAdapter(strSQL, conn);
sda.Fill(emp);
foreach (Dataset1.EmployeesRow er in emp.Employees) {
Console.WriteLine("Name: {0}", er.FirstName + " " + er.LastName);
} }
catch (Exception ex ) {
Console.WriteLine("Error: " + ex.ToString());
} } } }

The VB.NET equivalent is just as simple:

Imports System.Data
Imports System.Data.SqlClient
Module BuilderSample
Sub Main()
Dim cntString As String = "Data Source=localhost;Initial
Catalog=Northwind;Integrated Security=SSPI"
Dim strSQL As String = "SELECT * FROM Employees"
Dim sda As SqlDataAdapter = Nothing
Try
Dim emp As New Dataset1
sda = New SqlDataAdapter(strSQL, cntString)
sda.Fill(emp)
Dim empRow As Dataset1.EmployeesRow
For Each empRow In emp.Employees
Console.WriteLine("Name: " + empRow.FirstName.ToString() + " " +
empRow.LastName.ToString())
Next
Catch ex As Exception
Console.WriteLine("Error encountered: " + ex.ToString())
End Try
End Sub
End Module

The code is much cleaner and easier to read. The column names are now properties, so perusing the code months later doesn't require having a database schema at your fingertips.

The choice between untyped and typed datasets ultimately rests in the hands of the developer, and situations may arise when one choice is warranted over the other.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox