Working with a Typed DataSet

We frequently use dataset object to pass queried data (from our DBMS queries & XML) to and fro between functions or data access layer & business access layer.

    This simple use of Dataset class is an example un typed dataset, here we retrieve data as collection of rows, each row in turn is collection of objects representing value e.g.

string MyColValue =     Ds.Table [0].Rows[0][“MyColName”].ToString ();

                                   Or

string MyColValue =     Ds.Table[0].Rows [0] [MyColIndex].ToString ();

   Point to note that here we used collection of tables, rows & objects in same order to come at data, our data may be from column of any Sql data type in DBMS table its output is still an object.

What is typed dataset?

      A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties. This means you can access tables and columns by name, instead of using collection-based methods.

    In a strongly typed DataSet, type mismatch errors are caught when the code is compiled rather than at run time.

   An untyped dataset differ from typed dataset in way the tables and columns in the untyped dataset are exposed. Untyped dataset exposes it as collections while typed dataset exposes them as strongly typed properties.

Why to use strongly typed dataset?

     The benefits we receive with strongly-typing our relational data is reliability, fewer mistakes, and less time spent debugging.

 

    The strongly typed DataSet offers advantages over the untyped DataSet in terms of speed and easy maintainability. The speed in accessing a typed DataSet is comparable to the faster techniques in accessing an untyped DataSet (since a typed DataSet is just a layer over an untyped DataSet) and the readability of the typed DataSet is the best.

     The XSD file stores the XML that defines the schema for the strongly typed DataSet.

It is hence easier to minimize impact of database schema changes and implement them by modifying underlying xml.

How to create strongly Typed DataSet?

I am using adventureworks sample SQL Server Database

a) Open Visual Studio, and create a new ASP.NET Website.

b) In Solution Explorer, right-click to add a new item, and select DataSet. Give it the name AdventureWorks.xsd (say). Visual Studio will recommend placing the DataSet file inside the App_Code folder, which you should allow it to do for you.

c) The AdventureWorks.xsd will open in design mode

d) Locate the Server Explorer Toolbox; navigate to your SQL Server database, and the AdventureWorks database.

e) Drag the SalesOrderHeader and SalesOrderDetail tables to your DataSet Designer window. For each table we added, Visual Studio created a strongly typed DataTable (the name is based on the original table) and a TableAdapter. The DataTable has each column defined for us. The table adapter is the object we will use to fill the table. By default we have a Fill() method that will find every row from that table.

      We add our own method to along with Fill().

How we can add our own method?

     To add own method

a)     right click on SalesOrderHeaderTableAdapter then select Add->Query

b)     Select Query Type here I am selecting “Select which return rows”

c)      Next We Need to specify actual select query

What I am specifying is

“SELECT

   SalesOrderID,RevisionNumber,OrderDate,DueDate,ShipDate,Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber,CustomerID,ShipToAddressID,BillToAddressID,ShipMethod,CreditCardApprovalCode,SubTotal,TaxAmt,Freight,TotalDue,Comment,rowguid,ModifiedDate

FROM

             SalesLT.SalesOrderHeader

WHERE

            (OrderDate > @OrderDate)”

Now Compile your Project.

How to Use this Type dataset?

     To your webpage say default.aspx first add reference to namespace

Like in my case

using WebApplication1.AdventureWorksTableAdapters;

Here point to note is WebApplication1 is name of website while  AdventureWorks is name of Dataset.

   Now I add a grid view to page say GridView1 ,In Page Load call BindGrid() Method.

BindGrid() method could be as follows

  public void BindGrid()

        {

            // Create the SalesOrderHeaderTableAdapter

            SalesOrderHeaderTableAdapter salesAdapter =  new SalesOrderHeaderTableAdapter();

            // Get orders that took place after July 1st, 2004

            AdventureWorks.SalesOrderHeaderDataTable Orders =   salesAdapter.GetDataBy(new DateTime(2004, 5, 1));

            // Bind the order results to the GridView

            this.GridView1.DataSource = Orders;

            this.GridView1.DataBind();    

        }

Here SalesOrderHeaderDataTable acts as a collection of SalesOrderHeader table entity.

    Let’s add following code line somewhere before binding gridview

 

DateTime dt = Orders[0].OrderDate;

Here in this code line ‘Orders’ is collection of SalesOrderHeader table entity out of which we are concern with entity at first index, from this entity we are looking at ‘OrderDate’ property, Here unlike untyped dataset we are not getting data as an object but it’s datatype is analogous to it’s actual datatype in dbms.

i.e. OrderDate is datetime column in DBMS and it’s analogous type in .Net is datetime.

We don’t to first call string then cast it as date time isn’t it a nice improvement.

Has there any utility to help us in this regard?

XSD.exe is a tool in .NET framework SDK that generates the typed dataset using xml (schema) file generated when we drag a table into Dataset Item of Visual Studio

A) xsd.exe /d /l:CS XSDSchemaFileName.xsd /n:XSDSchema.Namespace

    In this syntax, the /d directive tells the tool to generate a DataSet, and the /l: tells the tool what language to use (for example, C# or Visual Basic .NET). The optional /n: directive tells the tool to also generate a namespace for the DataSet called XSDSchema.Namespace.
     The output of the command is XSDSchemaFileName.cs, which can be compiled and used in an ADO.NET application. The generated code can be compiled as a library or a module.
The following code shows the syntax for compiling the generated code as a library using the C# compiler (csc.exe).

B) csc.exe /t:library XSDSchemaFileName.cs /r:System.dll    

   /r:System.Data.dll

The /t: directive tells the tool to compile to a library, and the /r: directives specify dependent libraries required to compile. The output of the command is XSDSchemaFileName.dll, which can be passed to the compiler when compiling an ADO.NET application with, the /r: directive.

What is alternative?

   There are alternative methods to accomplish strong typing, one can create custom classes that are more lightweight than Datasets and correspond exactly to your database.  

Conclusion

      The names of the tables and columns that strongly typed DataSet represent are properties of the typed DataSet class, writing code with typed DataSets is more intuitive and easier to maintain. By making development time faster, easier, less prone to typing errors, and by making the code more maintainable, strongly typed DataSets are a great help to developers who want to write more effective code more efficiently.

We Can also add existing stored procedure to TableAdapter we will go into it in our next blog on this subject.