Introduction
This article teaches how to create a strongly-typed dataset class library in your C# database applications. Our objectives are as follows:
- Learn what a strongly-Typed DataSet is
- Let Visual Studio Create an ST Data Set
- (Semi)Manually create an ST DataSet using XSD (XML Schema Definition)
For this article, you will need to use Visual Studio .NET and a relational database. SQL Server is required for the automatic generation of ST DataSets, and I will use Sybase ASE for the manual ST DataSet. Sybase ASE is a cross-platform database server with Java-based administration tools similar to MS SQL Server tools. Sybase ASE is available in a free developer version at www.sybase.com. (Tip: If you install Sybase ASE, be sure to install Jisql which is under the “Free Utilities” option. This utility lets you type in SQL statements to execute.)
strong-Typing
C# itself is a strongly-typed language. Variables must be assigned a type, in contrast to languages like JavaScript or VBScript where a variable can hold any data type. The same is true with your database server, whether it is Oracle, Sybase, or Microsoft SQL Server. Database columns must be assigned a type: whether it is Text, Char, VarChar, Numeric, Int, or Binary.
Using ADO.NET, you can choose to use a generic (untyped) Data Set, or you can create a strongly-typed DataSet by creating an XSD. When you use a dataset to assign values to instance members in your business objects, the data set type must match your business object member or you must convert the type from object to the type. Creating a strongly-typed dataset also ensures that a dataset does not change (in the case of a modified stored procedure) and enables intellisense rather than dictionary-based access.
By default a data set is not strongly-typed; to create a strongly-typed dataset you must first create it as a DataSet Class. Thankfully, Visual Studio .NET automates much of this task.
Creating a strongly-Typed DataSet from SQL Server
While you can create an ST Data Set anywhere, I strongly recommend creating a data transport DLL. In distributed applications, this DLL may be placed both on multiple servers, and you do not want the entire data access DLL on each server. This will be a lightweight DLL with nothing but DataSet classes and will typically be referenced by the Data Access DLL and the Business Logic DLL.
The first step is to create a DS Common project and give it a proper namespace. I’ll give mine the namespace Demo.DSCommon and I’ll have a good way of referencing it. The next step is to create a DataSet class. In the Visual Studio Solution Explorer, Right click your project and select “Add New Item”. Click DataSet and name it. We’ll call it “SalesByCategory.xsd” as we’ll be using that procedure in the Pubs database. We’ll actually be creating an XSD (XML Schema definition) that Visual Studio will use to generate the class with. We’ll later look at writing these XSDs ourselves, which is handy for “unsupported” databases.
Open the Server Explorer pane and add a new data connection to the Pubs database on your DB server. It’s fine to use the “SA” account here as we won’t be using this connection in our application, only to administer the database.
Next, select the “SalesByCategory” Stored Procedure and drag it to the design surface. You’ll see that it creates an XML “table” for us, and generates the required XML code. Change the table name to Sales (from SalesByCategory) as it cannot share the same table name as the class.
Listing 17-3. Autogenerated XSD Code
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="SalesByCategory" targetNamespace="https://tempuri.org/SalesByCategory.xsd"
elementformDefault="qualified" attributeformDefault="qualified"
xmlns="https://tempuri.org/SalesByCategory.xsd"
xmlns:mstns="https://tempuri.org/SalesByCategory.xsd"
xmlns:xs="https://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="SalesByCategory" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Sales">
<xs:complexType>
<xs:sequence>
<xs:element name="ProductName" type="xs:string" />
<xs:element name="TotalPurchase" msdata:ReadOnly="true"
type="xs:decimal" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
To use this dataset, simply compile this class library and create a method in a data access class that will return it. To see the files it created, select “view all files” in the solution explorer. It is only the SalesByCategory class we will use, but since we will only use the DLL, there is no need to be concerned about extra XSD files.
We will now create a Data Access project and give it the default namespace of Demo.Data. In your data access project, be sure to include a reference to System.Data and the DSCommon dll we just created. Create a “Sales” class in Data.
Listing 17-4 Data Access Code
using System;
using System.Data;
using System.Data.SqlClient;
using Demo.DSCommon;
using System.Diagnostics;
namespace Demo.Data
{
public class Sales
{
public static DSCommon.SalesByCategory ByCategory(string cat, string year){
//Create the dataset
DSCommon.SalesByCategory ds = new DSCommon.SalesByCategory();
//Store the connection string (preferably not here!!!)
string conn = "server=dbserver;database=Northwind;uid=sa;pwd=secure";
//Create a connection
System.Data.SqlClient.SqlConnection connect =
new SqlConnection(conn);
//Create the proc string
string proc = "dbo.SalesByCategory";
//Create the command
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand(proc,connect);
command.CommandType = CommandType.StoredProcedure;
//Create the params (create 1 and reuse it)
System.Data.SqlClient.SqlParameter param;
param = command.Parameters.Add("@CategoryName", SqlDbType.VarChar, 15);
param.Direction= System.Data.ParameterDirection.Input;
param.Value = cat;
//param = command.Parameters.Add("@OrdYear", SqlDbType.NVarChar, 4);
//param.Direction= System.Data.ParameterDirection.Input;
//param.Value = year;
connect.Open();
//Create a SQL Adapter and fill the ds
System.Data.SqlClient.SqlDataAdapter da =
new SqlDataAdapter(command);
//Add the table mapping(s)
da.tableMappings.Add("table", "Sales");
//--- Syntax for multiple DS tables:
//da.tableMappings.Add("table1", "SalesPerson");
//da.tableMappings.Add("table2", "SalesTeam");
da.Fill(ds);
connect.Close();
connect.Dispose();
return ds;
}
}
}
Listing 17-4 illustrates the data access class. The elusive key of getting Data Sets to fill properly is to add the table Mappings. If your DataSets aren’t getting filled, this is probably your mistake. You won’t get a compile or run-time error either making it an extremely annoying bug in your code!
All that is left now is implementing a client application. Our client application will simply call the static method of Demo.Data.Sales and will receive the typed dataset. Typically this will be done in a middle-tier business object, but we will just write the data to the console.
Listing 17-5. Implementing the Client: Class1.cs
using System;
namespace Demo.Client
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
Console.WriteLine("Dataset Demo");
Demo.DSCommon.SalesByCategory ds =
new Demo.DSCommon.SalesByCategory();
ds = Demo.Data.Sales.ByCategory("Seafood","1997");
foreach (DSCommon.SalesByCategory.SalesRow row in ds.Sales)
{
Console.WriteLine(row.ProductName + " " + row.TotalPurchase.ToString());
}
//Keep it open long enough to read.
string foo = Console.ReadLine();
}
}
}
strongly Typed DataSets with Sybase ASE
To access ODBC data sources such as Sybase ASE from .NET, you will need to install the ODBC .NET Data Provider. You can download it from https://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/668/msdncompositedoc.xml.
On my Sybase ASE Server, I have an Accounts table in the Utility database. I have a simple stored procedure named UserList that I want to create a dataset from. Since I don’t have a native provider to Visual Studio I’ll have to create the XSD manually this time. We’ll use the same application as before to do this. Open the DS Common project and add a new blank DataSet. (Hint: to get the schema definition correct, refer to an existing DataSet XSD! You can also refer to Wrox press Professional ADO.NET for full details.) We’ll call it UserList.xsd, and it will get a list of names and phone numbers of our account users and a list of companies. The Data tables we want are Users and Companies.
Listing 17-6. Empty XSD Shell
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="UserList"
targetNamespace="https://tempuri.org/UserList.xsd"
elementformDefault="qualified" attributeformDefault="qualified"
xmlns="https://tempuri.org/UserList.xsd"
xmlns:mstns="https://tempuri.org/UserList.xsd"
xmlns:xs="https://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="UserList" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded"></XS:CHOICE>
</xs:complexType>
</xs:element>
</xs:schema>
Starting with the empty XSD shell, we will only need to add two Data tables to the XSD. While you can do this visually, we will look at the XSD method. Choose the XML view tab in Visual Studio. You may even notice that the XSD is initially not well formed- it is missing a closing tag on xs:choice!
To add an Data table, we’ll add the following element and attributes. The element is the Data table, and the attributes are its columns. To add multiple data tables, just create multiple elements.
Listing 17-7. UserList.xsd
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="UserList"
targetNamespace="https://tempuri.org/UserList.xsd"
elementformDefault="qualified" attributeformDefault="qualified"
xmlns="https://tempuri.org/UserList.xsd"
xmlns:mstns="https://tempuri.org/UserList.xsd"
xmlns:xs="https://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="UserList" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Users"> <xs:complexType> <xs:sequence> <xs:element
name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Phone"
type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType>
</xs:element>
<xs:element name="Companies"> <xs:complexType> <xs:sequence>
<xs:element name="CompanyName" type="xs:string" minOccurs="0" /> </xs:sequence>
</xs:complexType> </xs:element>
</XS:CHOICE>
</xs:complexType>
</xs:element>
</xs:schema>
After compiling the DSCommon dll, we will now return to the Demo.Data project and create a new class (.cs) file. This time we’ll call it User.cs. The code will be similar to the SQLClient code in Sales.cs, but this time we’ll use OleDB.
Listing 17-8 User.cs
using System;
using System.Data;
using Microsoft.Data.Odbc;
namespace Demo.Data
{
public class User
{
public static DSCommon.UserList UserData()
{
//Create the dataset
DSCommon.UserList ds = new DSCommon.UserList();
//Store the connection string (preferably not here!!!)
string conn = "DSN=nathanUtility;NA=192.168.0.14,2048;DB=Utility;UID=
sa;PWD="; //Create a
connection Microsoft.Data.Odbc.OdbcConnection connect
= new
OdbcConnection(conn); //Create the proc
string string proc =
"dbo.UserList"; //Create the
command Microsoft.Data.Odbc.OdbcCommand command
= new
OdbcCommand(proc,connect); command.CommandType =
System.Data.CommandType.StoredProcedure;
connect.Open(); //Create an Adapter and fill the ds
Microsoft.Data.Odbc.OdbcDataAdapter da =
new Microsoft.Data.Odbc.OdbcDataAdapter(command);
//Add the table mapping(s)
da.tableMappings.Add("table", "Users");
da.tableMappings.Add("table1", "Companies");
da.Fill(ds);
connect.Close();
connect.Dispose();
return ds;
}
}
}
We will now modify the Console Application code to use the Sybase data. Below is the code listing for the Main method, which simply receives the DataSet and writes it to the Console.
Listing 17-8 Updated User.cs Main method
static void Main(string[] args)
{
Console.WriteLine("Dataset Demo");
Demo.DSCommon.UserList ds = Demo.Data.User.UserData();
foreach (DSCommon.UserList.UsersRow row in ds.Users)
{
Console.WriteLine(row.UserName + " " + row.Phone);
}
foreach (DSCommon.UserList.CompaniesRow row in ds.Companies)
{
Console.WriteLine(row.CompanyName);
}
//Keep it open long enough to read.
string foo = Console.ReadLine();
}
As you saw, creating strongly-Typed Data Sets manually is not difficult, and we have seen the plumbing of the DataSet in the process. There are additional attributes you can add to the XSD to further modify the generated DataSet classes, and they make a nice data transport mechanism.
There are several cons to creating datasets, however. They are more expensive than the lightweight DataReader and do have slight performance hits in their creation, however, this is negligible considering the data transport mechanism they provide. You may, however, opt to consider other data transport mechanisms depending on your project, and how much development resources you want to put into this tier. There are also multiple benefits of using datasets, especially in using ADO.NET’s “out-of-the-box” functionality. You can also use a DataSet to transport data regardless of its provider. You can fill a DataSet programmatically from business objects, XML data, text files, database sources, and just about anything else. Data Sets enable us to loosely integrate multiple data sources, and make it easier to swap data sources.
For further exploration, I recommend the Wrox press book “Professional ADO.NET Programming”, an in-depth study of the ADO.NET data tier including advanced topics such as creating a custom .NET data provider.
Summary
This has been an introduction to strongly-Typed Data Sets. By now, you should have a good understanding of what a strongly-Typed DataSet is and how to create them in your projects. You should be able to create them from a managed data provider (SQL Server or Oracle) or an ODBC data source such as Sybase ASE. Please feel free to contact me with any questions or comments you may have about this lesson.
About Daniel Larson
Daniel Larson is a Microsoft Certified Solutions Developer (MCSD) and independent .NET software development consultant, specializing in SQL Server-based .NET applications.