Home » Articles


Sponsored links

CodeDigest Navigation

Technology News
No News Feeds available at this time.

Community News
No News Feeds available at this time.
Typed Dataset and its usages

By Satheesh Babu
Posted On Feb 24, 2008
Article Rating:
Average Rating: 5
No of Ratings: 1
No of Comments: 2
Print this article.

Typed Dataset and its usages


The word Dataset does not need introduction because it is one of most commonly used object in .net world. So proceeding with this assumption, a typed dataset is an object that derives from Dataset class and additionally 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




The same can be accessed in typed dataset as,


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();


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();


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();


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");



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.

DataSet Element

Equivalent Typed DataSet Element



DataTable methods






DataSet Events


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.

Column Name

Setting Null

Checking Null







.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);






In the same way to check whether the column has null value the method is,


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,


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();



decimal price = prod[0].UnitPrice


Thus 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 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"




              <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" />





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="" xmlns:mstns="" xmlns="" xmlns:xs="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="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.


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.

DataSet Element

Default Naming

Annotation to Modify




DataTable methods









DataSet Events



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.


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,

Figure 16– Server explorer with dumb connections

Deleting those connections will solve the problem.

Download Source:

Download Source


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


Similar Articles
You can contribute to CodeDiget.Com:
Donate to
Article Feedback
excellent buddy