What is the advantage?

The readability of our code increases.

Gives compile time error for datatype mismatch.

With visual studio you will get the intellisense support for tables and columns.

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

Creating typed dataset:

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 new item dialog box like below (Figure 1),

Figure 1 – Add New Item

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

4) Open server explorer in visual studio, if you can’t 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 and select the database name and click OK.

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

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:

Visual studio helps us with the intellisense support (Refer 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 same name for the dataset name as the table name. If the Dataset name is different then the table name will be “Employees”.

How to use it?

We can use the typed dataset and fill it in 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 fill method should match the table name in 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 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 coming sections we will see how to use TableAdapter and extend the typed dataset to provide custom methods that serves for specific purpose. We use “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.

New Select Functionality:

To retrieve a particular customer details by giving the CustomerID.

Right click dataset click add>Query as shown in below figure.

Figure 6 – Customer Table

This will bring a window like in the Figure 7,

Figure 7 – TableAdapter Configuration for Command Type

Select appropriate option, I have selected “Use SQL statements”. Click Next.

Clicking Next will bring a window like,

Figure 8 – TableAdapter Configuration for Query Type

Select the query type. I have selected the first option “Select which returns rows”. Click Next.

This will bring 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 like,

Select * from Customers where CustomerID = @CustomerID

Click Finish. Thus, we have added a select functionality to the typed dataset using TableAdapter writing a single line of code.

New Update Functionality:

Repeat the above steps to update the address of the customers. Select “Update” option in step 3, provide a method name in next step and give the following query that will update 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 Table adapter like,

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 like,

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 coming section we will construct a custom typed dataset using visual studio instead of dragging and dropping from server explorer.

Constructing Typed Dataset:

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 Tool box for constructing the typed dataset. Next sections will discuss how to use the typed dataset as an alternative to C# custom business objects. Below figure 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 name as you want.

Figure 13– Add a column

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

So the final typed dataset will look like,

Figure 14– Final Dataset

Using the constructed Typed Dataset:

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

To create a new Products row,

Listing 7: Create new Prodcuts row

BO.Products.ProductsDataTable prod = new BO.Products.ProductsDataTable();

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

Once if 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. If we see the above lines of code “NewProductsRow()” function is created by the framework which is the equivalent of “NewRow()” function of normal datatable.

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

The following code will set value for the column.

ProRow.ProductName = txtProdName.Text;

Handling Nulls:

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 Columns property,

AllowDBNull = true;

Unfortunately typed dataset does not allow Nullable types to be specified for column. So we can’t 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 method 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 8: 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 null value the method is,

ProRow.IsUnitPriceNull();

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

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, on 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 a error “Property value is not valid”.

So the default value will be an exception for Null value in a column that is of 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 like,

Listing 9: Get a column value

BO.Products.ProductsDataTable prod = new BO.Products.ProductsDataTable();

if(!prod[0].IsUnitPriceNull())

 {       

decimal price = prod[0].UnitPrice

 }

Thus we have constructed a typed dataset programmatically.

Annotations:

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 much 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 retains the original names without modifying it in the schema so that typed dataset schema and database table schema remains same i.e. it is something similar to adding a alias.

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

Modified XSD:

Listing 10: 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 11: 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 primitive datatype column returns 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 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 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 this 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 below that is disconnected. Refer figure 16 below,