Mayo Software - C# and .NET Custom Software Development Services

Introduction to strongly Typed Data Sets

by Daniel Larson, 01/25/03

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 an strongly-Typed Data Set is
Let Visual Studio Create a ST Data Set
(Semi)Manually create a ST Data Set 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 Data Set. 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 Data Set by creating an XSD. When you use a data set 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 Data Set Class. Thankfully, Visual Studio .NET automates much of this task.

Creating a strongly-Typed Data Set from SQL Server

While you can create a 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 Data Set classes and will typically be referenced by the Data Access DLL and the Business Logic DLL.

The first step is to create a DSCommon 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 Data Set class. In the Visual Studio Solution Explorer, Right click your project and select "Add New Item". Click Data Set 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 tablename as the class.

Listing 17-3. Autogenerated XSD Code

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="SalesByCategory" targetNamespace="http://tempuri.org/SalesByCategory.xsd" 
      elementformDefault="qualified" attributeformDefault="qualified" 
      xmlns="http://tempuri.org/SalesByCategory.xsd" 
      xmlns:mstns="http://tempuri.org/SalesByCategory.xsd" 
      xmlns:xs="http://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 tableMappings. If your Data Sets 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 data set. 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 Data Sets 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 http://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 DSCommon project and add a new blank Data Set. (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="http://tempuri.org/UserList.xsd" 
		elementformDefault="qualified" attributeformDefault="qualified"
		xmlns="http://tempuri.org/UserList.xsd"
		xmlns:mstns="http://tempuri.org/UserList.xsd"
		xmlns:xs="http://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="http://tempuri.org/UserList.xsd" 
		elementformDefault="qualified" attributeformDefault="qualified"
		xmlns="http://tempuri.org/UserList.xsd"
		xmlns:mstns="http://tempuri.org/UserList.xsd"
		xmlns:xs="http://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 siumilar 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 Data Set 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 Data Set in the process. There are additional attributes you can add to the XSD to further modify the generated Data Set classes, and they make a nice data transport mechanism..

There are several cons to creating datasets, however. They are more expensive than the lightwieght DataReader and do have slight performance hits in their creation, however this is negligable 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 Data Set to transport data irregardless of its provider. You can fill a Data Set programatically 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 Data Set 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 for any questions or comments you may have about this lesson.

Feedback

About Daniel Larson

Daniel Larson is a Microsoft Certified Solutions Developer (MCSD) and independant .NET software development consultant, specializing in SQL Server based .NET applications.