Typed vs. Untyped Datasets

In last few posts we discussed how you can use data adapter, datatables and datasets to fetch data from and save it to SQL Server. In those examples, we created untyped datasets. Untyped dataset is simply an in memory declaration of a dataset. It is a collection of datatables, which themselves are in memory declaration of datatables which are similar to database tables.

While it is possible to create constraints such as column type, relationship between columns, whether a column is nullable or not in a datatable declared in memory, but you have to write extra code for it. Contrast that with a typed dataset which inherits from dataset class and has an XML schema. This allows for you to define constraints and relationships on the design surface. Additionally, you can also drag tables from the database on the design surface and relationships/constraints of your database will automatically follow.

In this example, we will create a dataset using AdventureWorks database and then dragging the tables on the design surface. I am using VS 2010 for this project.

1. Create a sample test project.
2. From the top menu select Data > Add New Data Source and connect to the database you would like to use.

3. Select database and click on Next

4. Create a New Connection or select an existing connection. Here we will create a new connection

5. Define parameters and select your database.

6. Select one or more tables/views/stored procedures/function you would like to include in this dataset. Here we will select all tables.

7. Click on Finish and it will create an xsd file with all the selected tables.

8. If you open the file, you will see all tables that are part of this xsd

Alternatively, you can also create an xsd file manually and drag the tables that you want on the design surface. Click on Add new file and then select an xsd file. Then drag the tables to the design surface. As you drag the tables, note the data type and relationship between the tables.

In addition to dragging the tables on the design surface, you can also add table(s) by right clicking on the design surface and then clicking on Add > New Table or create a new relationship constraint.

Using Typed Dataset

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TypedDataSets
{
    class Program
    {
        static void Main(string[] args)
        {
            Customers customer1 = new Customers();
            //populate the dataset
            // Step 2: Specify a command (request)
            string connectionstring = System.Configuration.ConfigurationManager.
            ConnectionStrings["TypedDataSets.Properties.Settings.
            AdventureWorksConnectionString"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
            myConnection.Open(); 
            SqlCommand myCommand = myConnection.CreateCommand();

            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,AccountNumber, " + 
                "CustomerType,rowguid,ModifiedDate FROM Sales.Customer";

            // now use data sets instead of data read
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = myCommand;
            da.Fill(customer1.Customer);

            Console.WriteLine("Customer ID: " + customer1.Customer[0].CustomerID);
            Console.Read();
        }
    }
}

Typed datasets allow for strong typing and allows for cleaner code, in the sense you don't have to convert data from one type to another or check for the data type before using it.

Thank you.