The word Dataset does not need an introduction because it is one of most commonly used objects in the .NET world. So proceeding with this assumption, a typed dataset is an object that derives from the Dataset class and additionall, it provides strongly typed access to its containing tables and columns. To justify the above line still in depth, to access tables and columns in a dataset we use:

Listing 1: Dataset and Typed Dataset

ds.Tables["0"].Rows[0][0].ToString();

or

ds.Tables["Customers"].Rows[0]["Name"].ToString();

The same can be accessed in typed dataset as:

ds.Customers[0].Name;

·         The readability of our code increases.

·         It gives compile time error for datatype mismatch.

·         With Visual Studio 2005 you will get the intellisense support for tables and columns.

Going ahead we will see how to create a simple typed dataset and use it with the help of visual studio. I am using NorthWind database to explain typed dataset in this article.

1) Open a web application project in Visual Studio 2005.

2) To add a typed dataset, right click the solution and click “Add New Item.” It will open a new item dialog box like below.

Figure 1 – Add New Item

3) Select Dataset and change the name; click Add.

4) Open the server explorer in visual studio. If you cannott find the server explorer, click “Server Explorer" from the view menu of visual studio.

5) It will open Add Connection window. Select the Server name and select the “NorthWind” database or your own database as below.

Figure 2 – Add Connection

Select Server name, select the database name and click OK.

6) Drag and drop the Employees table into the dataset designer to create the typed dataset from the server explorer. The designer will look like figure 3.

Figure 3 – Dataset Designer

By default, it will have a TableAdapter (EmployeesTableAdapter here) with Fill() and GetData() methods that can be used to fill and fetch data from the database without implementing a line of code.

Intellisense Support in Visual Studio 2005

Visual Studio 2005 helps us with the intellisense support (Refer to Figure 4) by listing tables, columns and methods for the constructed typed dataset.

Figure 4 – Visual Studio Intellisense

“_Employees[0]” in the intellisense is the table name and “_” is because we used the same name for the dataset name as the table name. If the Dataset name is different then the table, the name will be “Employees.”

How to use it?

We can use the typed dataset and fill it in the normal way as we do for a non-typed dataset.

Listing 2: Filling Typed Dataset in a usual way

com = new SqlCommand("Select * from Employees", sqlcon); 
ada = new SqlDataAdapter(com);          
emp = new Employees();
da.Fill(emp,"Employees");

The table name in the fill method should match the table name in the typed dataset.

Using EmployeesTableAdapter:

Listing 3: Filling Typed Dataset using TableAdapter

BO.EmployeesTableAdapters.EmployeesTableAdapter ada = new 
    BO.EmployeesTableAdapters.EmployeesTableAdapter();
gvEmployees.DataSource = ada.GetData();
gvEmployees.DataBind();

In the above code “BO” is the namespace name since I have used a separate Class library project with name BO and created the typed dataset there. If you are creating typed dataset in the same web project then the above code will be:

Listing 4: Filling Typed Dataset using TableAdapter in Same project

EmployeesTableAdapters.EmployeesTableAdapter ada = new 
    EmployeesTableAdapters.EmployeesTableAdapter();
gvEmployees.DataSource = ada.GetData();
gvEmployees.DataBind();

Here, “gvEmployees” is the gridview. In the next sections we will see how to use TableAdapter and extend the typed dataset to provide custom methods that serve specific purposes. We use the “customer” table in NorthWind database for this article.

Figure 5 – Customer Table

What is a TableAdapter?

TableAdapters provide communication between your application and a database. More specifically, a TableAdapter connects to a database, executes queries or stored procedures, and either returns a new data table populated with the returned data or fills an existing DataTable with the returned data. TableAdapters are also used to send updated data from your application back to the database.

Adding New Functionality

We will try to add a new Selection functionality and a new update functionality using TableAdapter in Typed dataset using visual studio.

You retrieve a particular customer detail by giving the CustomerID.

Right click dataset and click add>Query as shown below.

Figure 6 – Customer Table

This will bring up a new window.

Figure 7 – TableAdapter Configuration for Command Type

Select the appropriate option; I have selected “Use SQL statements.”

Clicking Next will bring a window like the one below.

Figure 8 – TableAdapter Configuration for Query Type

Select the query type. I have selected the first option: “Select which returns rows.” Click Next and this will bring up a window to specify the query. Give the parameters with “@” character pre-pended to it.

Figure 9 – TableAdapter Configuration for SQL Statement

I would like to return details of a customer on giving the customerid in this example so I have given the query:

Select * from Customers where CustomerID = @CustomerID.

Click Finish. We have just added a select functionality to the typed dataset using TableAdapter writing a single line of code.

Repeat the above steps to update the address of the customers. Select “Update” option in step 3, provide a method name in the next step and give the following query that will update the address, city, region, postalcode, country, phone and fax by giving the customerid.

The update query will be:

UPDATE [Customers] 
SET [Address] = @Address, 
[City] = @City, 
[Region] = @Region, 
[PostalCode] = @PostalCode, 
[Country] = @Country, 
[Phone] = @Phone, 
[Fax] = @Fax 
WHERE (([CustomerID] = @Original_CustomerID)) .

So our final dataset in designer will look like:

Figure 10 – Final Customer table view

How to use the functionality?

To execute the above functionality instantiate the Table adapter.

Listing 5: Instantiating TableAdapter

TestTableAdapters.CustomersTableAdapter ada = new 
    TestTableAdapters.CustomersTableAdapter();

Now we can see the new select and update functionality with the insert, update and delete method in TableAdapter intellisense.  

Figure 11 – Visual studio intellisense for TableAdapter

We can use the above functionality:

Listing 6: Using the new functionality

//Update Address
ada.UpdateAddress("2nd Cross", "Bangalore", "Karnataka", "560061", 
"India", "123456789", "!23456789", "100");
//Get details by customerid
grdCustomers.DataSource = ada.GetDataBy("100");
grdCustomers.DataBind();

In the next section we will construct a custom typed dataset using visual studio instead of dragging and dropping from server explorer.

Add a Typed dataset in Visual Studio as we did above from add new item dialog box. Visual studio 2005 comes with a set of items in the Tool box for constructing the typed dataset. The next sections will discuss how to use the typed dataset as an alternative to C# custom business objects. Below shows those items in Visual Studio 2005 Toolbox.

Figure 12– Visual studio Tool bar

1) Drag a Datatable object from the toolbox.

2) To add a column, right click the table object and click Add>Column. Rename the column as you want.

Figure 13– Add a column

3) To specify datatype, click the properties of the corresponding column and specify it.

So the final typed dataset will look like figure 14.

Figure 14

Using the constructed Typed Dataset:

Filling this typed dataset can be done in the same way as we did above, keeping in mind that the database column name and datatype should match with typed dataset. I will explain how to construct the typed dataset programmatically instead of filling it from the database.

To create a new Products row us the following code.

Listing 7: Create new Prodcuts row

BO.Products.ProductsDataTable prod = new BO.Products.ProductsDataTable();
BO.Products.ProductsRow ProRow = prod.NewProductsRow();

Once we create a typed dataset and compile it, Framework will create all the methods and properties that will help us in using the typed dataset. The above lines of code “NewProductsRow()” function is created by the framework which is the equivalent of “NewRow()” function of the normal datatable.

The following table specifies the usual dataset elements and typed dataset equivalent.

The following code will set the value for the column.

Listing 8

ProRow.ProductName = txtProdName.Text; 

The database table can contain columns that allow null to be saved. In the same way we can make the typed dataset column to allow nulls by setting the Columns property: AllowDBNull = true;

Unfortunately, typed dataset does not allow Nullable types to be specified for a column. So we cannot specify nulls to the columns directly, instead we should use the methods that is generated to set nulls.

How to set Nulls?

The below table gives us a picture about the method it will generate for setting and checking nulls.

.NetFramework adds methods that will set Null to a column without the user setting it. So to set null for a column named “UnitPrice” the method will be SetUnitPriceNull(). So the below line will set null for unitprice column if its has null value.

Listing 9: Set a column value

if (txtUnitPrice.Text != "")
{
  ProRow.UnitPrice = decimal.Parse(txtUnitPrice.Text);
}
else
{
  ProRow.SetUnitPriceNull();
}

In the same way, to check whether the column has a null value the method is ProRow.IsUnitPriceNull(); .

The above line will return either true or false depending on the value. On calling the Add() method the constructed row can be added to the dataset:  

prod.Rows.Add(ProRow); .

While accessing a value in typed dataset, we should check for null before accessing it. Because there can be an integer column in database that can allow nulls, accessing such column with null values will throw an exception. We can understand this when we select null for NullValue property for a primitive datatype in property box of a column which will throw an error “Property value is not valid.”

So the default value will be an exception for Null value in a column that is of a primitive type. Null values can be set to the columns of type string. To access the “UnitPrice” column that allows null in database, the code should be the following.

Listing 10: Get a column value

BO.Products.ProductsDataTable prod = new BO.Products.ProductsDataTable();
if(!prod[0].IsUnitPriceNull())
 {        
decimal price = prod[0].UnitPrice
 }

Now we have constructed a typed dataset programmatically.

Annotations are simply a set of extensions to the raw XSD file that is used by .NET to generate the typed DataSet. Even after using the typed dataset, still the code is not that readable. For example, if your table is named Customers, the DataTable class will be named CustomersDataTable, the DataRowCollection will be named Customers, and the method to create a new DataTableRow is called NewCustomersRow. You need to add codegen annotations to change the typedPlural and typedName of the table element in XSD file to make it more readable. We can also change the column name using these annotations. We use annotation because it will retain the original names without modifying it in the schema so that typed dataset schema and database table schema remain the same- it is something similar to adding an alias.

To change “ProductsDatable” to BabulivesDatatble and “NewProductsRow” to “BabuLivesProductsRow” we can modify the XSD to add annotations.

Modified XSD:

Listing 11: XSD Code

<xs:element name="Products"  codegen:typedName="BabuLivesProduct" 
            codegen:typedPlural="BabuLivesProducts">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ProductID" msdata:AutoIncrement="true"
                  msprop:Generator_UserColumnName="ProductID"
                  msprop:Generator_ColumnPropNameInRow="ProductID" 
                  msprop:Generator_ColumnVarNameInTable="columnProductID" 
                  msprop:Generator_ColumnPropNameInTable="ProductIDColumn" 
                  type="xs:int" />
              <xs:element name="ProductName" 
                  msprop:Generator_UserColumnName="ProductName" 
                  msprop:nullValue="_null" 
                  msprop:Generator_ColumnPropNameInRow="ProductName" 
                  msprop:Generator_ColumnPropNameInTable="ProductNameColumn" 
                  msprop:Generator_ColumnVarNameInTable="columnProductName" 
                  type="xs:string" minOccurs="0" />
              <xs:element name="UnitPrice" 
                  msprop:Generator_UserColumnName="UnitPrice" 
                  msprop:Generator_ColumnPropNameInRow="UnitPrice" 
                  msprop:Generator_ColumnVarNameInTable="columnUnitPrice" 
                  msprop:Generator_ColumnPropNameInTable="UnitPriceColumn" 
                  type="xs:decimal" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
</xs:element>

To make this work we need to add a namespace in the XSD file, locate the following element in XSD file and add the namespace that is bolded here.

Listing 12: XSD code with modified namespace

<xs:schema id="Products" targetNamespace="http://tempuri.org/Products.xsd" 
    xmlns:mstns="http://tempuri.org/Products.xsd" 
    xmlns="http://tempuri.org/Products.xsd" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" 
    xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
    xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
    attributeFormDefault="qualified" elementFormDefault="qualified">

After compiling, the intellisense will show like:

Figure 15– Annotations intellisense

Handling Nulls using annotations

As we know, null value in a primitive datatype column returns an exception; instead we can make to it return a default value:

codegen:nullValue="0"

By adding the above attribute to a column tag in XSD it will prevent the exception by giving this default value.

The following table lists the annotations to change the elements in typed dataset.

What happens under hood?

When we drag a table into typed dataset, designer visual studio normally generates the XML schema. There is a tool called XSD.EXE in framework SDK which generates the typed dataset using xml file generated. This is the tool that is generating the typed dataset from the xsd file created.

Common hurdle in Visual Studio

Sometimes when you work with dataset designer in visual studio you will get the following error.

Configure TableAdapter Products Failed

Key not valid for use in specified state

Resolution

This error is because there may be some dump connection in server explorer of visual studio like the one below that is disconnected.

Figure 16– Server explorer with dumb connections

Deleting those connections will solve the problem.

[Download Sample]

Thus we understood how to construct and use a typed dataset with the use of visual studio in this article. Typed dataset in the real world can be used as an alternative to C# business object if our project size is really small. Enjoy Coding!!!