Using Typed DataSet in .Net

Tuesday, November 4, 2008
A typed DataSet is a strongly-typed container for collections of business entity data and inherits from the System.Data.DataSet class. However, unlike the standard DataSet that features late binding and weak typing, typed DataSets provide strongly-typed access to data.

This means that they expose table, column, and relation objects as named properties, rather than generic collection elements. Essentially, we can write code as MyRow.ClientName, as opposed to MyRow["ClientName"].

Here we can see how to create a Typed DataSet

Typed DataSets also give us the ability to use IntelliSense for statement completion
and type checking at compile time. In the context of reporting, they aid in data-driven report design and provide an excellent form of self-documentation of stored procedure result sets.

Benefits of Typed DataSets
Typed DataSets have many benefits over standard DataSets. Let’s take a look at how they simplifyand enhance the development process.

Strong Typing
Typed DataSets give you the ability to use IntelliSense for statement completion and type checking at compile time. Where as in Untyped we must type the column name correctly (or receive a runtime error), and you must cast the column object to the correct DataType.
Typed DataSets also make it easy to define default column values when you add new

Easier Traversing of Hierarchical Data
If a typed DataSet contains table relations, the XML Schema Definition tool adds methods to the typed DataSet class for traversing hierarchical data

Using Find
Additionally, if you define a primary key for any table in a typed DataSet, the class exposes a FindXXX() method associated with the column name. We can perform a find against a table with a primary key of OrderID by using the pregenerated method FindByOrderID(). If the primary key is a concatenation of two columns (for example, CustomerID and OrderID), then it can be as FindByCustomerIDOrderID().

Null Value Handling
Working with null values is a little easier when using typed DataSets. Each typed DataRow contains a method to check whether a column value is null: MyRow.IsAddressNull(). The DataRow also contains a second method to set a column value to null: MyRow.SetAddressNull().

Typed DataSets are valuable in the reporting process. We can design report content from a typed DataSet definition, even before the stored procedures are constructed. Once again, the typed DataSet becomes a form of documentation for the report DataSource.

Since a typed DataSet is a class that inherits from System.DataSet, we can further subclass a typed DataSet to add validation code or any other methods. Alternatively, we can also take advantage of the new partial class capability in Visual Studio 2005 to add method code for our DataSet.

Simplify Data Binding
Typed DataSets can simplify design-time data binding—we can define a DataSource in the property sheet. Because our sample application stores all typed DataSets as a separate project DLL, we can add the DLL to the Visual Studio toolbox, and then drop an instance of the typed DataSet onto a form as a precursor to design-time data binding. Developers who define DataSources in code still gain the advantage of table/column name discovery through IntelliSense.

DataSet Operations

Adding Rows to Dataset

  1. //Adding Rows to Dataset

  2. Company cm = new Company();

  3. cm.Employee.AddEmployeeRow("123", "Ram", "Krishna", "9866", "Hyderabad","22/08/2008");

  4. //Adding Rows in Another way.

  5. Company.EmployeeRow enewRow = cm.Employee.NewEmployeeRow();

  6. enewRow.EMPID = "235";

  7. enewRow.FName = "Ashfaq";

  8. enewRow.LName = "Sajju ";

  9. enewRow.Mobile = "123";

  10. enewRow.City = "Sec-Bad";

  11. cm.Employee.AddEmployeeRow(enewRow);

  12. GridView1.DataSource = cm;

  13. GridView1.DataBind();

Looping Through Dataset

  1. Company cmp = new Company();

  2. // cmp = Genertate some method for rows();

  3. foreach (Company.EmployeeRow rw in cmp.Employee.Rows)

  4. {

  5. string name = rw.FName;

  6. }

Using Date Math

  1. // To Check the emps who joined b/w 3 months from current date

  2. Company com1 = new Company();

  3. int totDays;

  4. TimeSpan ts;

  5. DateTime today = DateTime.Today;

  6. foreach (Company.EmployeeRow erow in com1.Employee.Rows)

  7. {

  8. ts = today.Subtract(erow.DOJ);

  9. totDays = ts.Days;

  10. if (totDays > 90)

  11. {

  12. // Process Here

  13. }

  14. }

Defining Primary key programmetically

  1. Company com2 = new Company();

  2. com2.Employee.PrimaryKey = new DataColumn[]

  3. {

  4. com2.Employee.EMPIDColumn

  5. };

  6. com2.Employee.PrimaryKey = new DataColumn[]

  7. {

  8. com2.Employee.Columns["EMPID"]

  9. };

  10. // If the EMPID is defined are primary key..

  11. // Then only we get FindByEMPID

  12. Company.EmployeeRow eow = com2.Employee.FindByEMPID("123");

  13. string empid = eow.EMPID;


  1. // We can apply filtering for Dates, Strings, Numerics, etc

  2. Company cm = new Company();

  3. string cFilterExpr = "";

  4. // Date handling

  5. cFilterExpr = "DOJ >= '01/31/2006' AND InvoiceDate <= '02/28/2006'";

  6. cFilterExpr = "DOJ >= #01/31/2006";

  7. // Full and partial text searches

  8. cFilterExpr = "FName = 'Ram'";

  9. // Search for "Construction" anywhere

  10. cFilterExpr = "FName LIKE '%Ram%'";

  11. // Numeric data

  12. cFilterExpr = "Sal > 1000";

  13. // Combining AND/OR

  14. cFilterExpr = "(FName LIKE '%Ram%' OR FName LIKE %Krishna%') AND Sal > 5000";

  15. // Using the IN function

  16. cFilterExpr = "Sal IN (1000,2000,3000)";

  17. cFilterExpr = "FName IN ('Ram','Rama')";

  18. cFilterExpr = "FName NOT IN ('Ashfaq','Sajju')";

  19. // Using the ISNULL function

  20. cFilterExpr = "ISNULL(InvoiceAmount,-9999)=-9999";

  21. // Using the PARENT (or CHILD) function

  22. cFilterExpr = "Parent(RelName) = like '%NATIONAL'";

  23. DataRow[] dr = cm.Employee.Select(cFilterExpr);

We can peroform more operations like Data Relations, etc with Typed Datasets.