Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Typed DataRow Generation


:P
On this page:

Yesterday I talked about some of the things that I don’t like about typed DataSets, so I decided to do something about it. My problem with the DataSet generator is that it generates A LOT of code. And a lot of that code runs even if you don't use a table in the database for example. The generated code does things like create new datatables for all the tables even if you don't ask for all of them. This is relatively slow because the table fields are parsed into Column collections for all fields. The use of DataColumns is a mixed bag - it can improve performance considerably if you're doing things in a loop where something like DataRow["Company"] is comparatively slow as a dictionary lookup occurs. With the actual column,ADO.Net can go directly to the column which is much faster. However it also takes time to create these column objects in the first place even if you’re just assigning a reference so with single record reads performance with index lookups is still faster.

 

Anyway, I thought that I don't need all that the xsd generated dataset creates. Having the tables as non indexed objects is nice, but the real value to me is in getting typed access to the DataRow fields.

 

So, I started to build a generator that creates just a 'DataRowContainer' object which wraps a DataRow and provides a typed interface to the DataRow. It's also a generator so there's also a manual step but it unlike the xsd generator which deals with one dataset at a time, it can do the whole database at once.

 

 

What it does is create a separate DataRowContainer for each table in the database. Whenever you have a data row that includes the fields of the data table (such as BusObject.Load() that loads up a single datarow) you can then assign the DataRow to a container:

 

Customer.Load(1000); // Load by PK;

 

wws_customersRow CustRow = new wws_customersRow(LineItems.DataRow);

 

Response.Write( CustRow.Lastname );

CustRow.Lastname = "Wonka";

 

Customer.Save();

 

.Lastname in this case is a field in the table, but the value is now available as a typed string value (note no conversion). In the simple case the wws_customersRow class Lastname property is a wrapper that returns the value from the underlying DataRow. The wrapper class looks something like this (truncated to a few fields):

 

[Serializable()]

public class wws_customersRow : wwDataRowContainer

{                

      public wws_customersRow() : base() {}

      public wws_customersRow(DataRow Row) : base(Row) {}

 

      public Int32 Pk

      {

            get

            {

                  if (this.UseColumns)

                        return (Int32) this.DataRow[this.PkColumn];

                  else

                        return (Int32) this.DataRow["Pk"];

            }

            set

            {

                  this.DataRow["Pk"] = value;

            }

      }

 

      public String Lastname

      {

            get

            {

                  if (this.UseColumns)

                        return (String) this.DataRow[this.LastnameColumn];

                  else

                        return (String) this.DataRow["Lastname"];

            }

            set

            {

                  this.DataRow["Lastname"] = value;

            }

      }

 

 

      public DateTime Entered

      {

            get

            {

                  if (this.UseColumns)

                        return (DateTime) this.DataRow[this.EnteredColumn];

                  else

                        return (DateTime) this.DataRow["Entered"];

            }

            set

            {

                  this.DataRow["Entered"] = value;

            }

      }

      … more properties

 

      // *** Column Definitions

      DataColumn PkColumn;

      DataColumn LastnameColumn;

      DataColumn EnteredColumn;

      … more columns

 

 

      protected override void CreateColumns()

      {

            PkColumn = this.DataRow.Table.Columns["Pk"];

            LastnameColumn = this.DataRow.Table.Columns["Lastname"];

            EnteredColumn = this.DataRow.Table.Columns["Entered"];

            this.ColumnsCreated = true;

      }

} 

 

… one class per table in the database.

 

Note that if you set the UseColumns flag to true, the class will like the typed dataset parse all the fields of the table into Column objects and then use those for accessing the DataRow fields which is more efficient. If you access more fields than the fields in the table (most likely in a loop) then UseColumns can gain a fair amount of performance.

 

In addition to the individual classes there’s also a base class wwDataRowContainer created:

 

public abstract class wwDataRowContainer

{

      protected DataRow DataRow;

      protected bool ColumnsCreated = false;

 

      ///

      /// Determines whether the class creates columns for each

      /// fields and then accesses the fields through these columns

      /// for more efficient access.

      ///

      /// More efficient for access, but overhead in creating the column

      /// objects in the first place. Use this option only if you plan

      /// to access a lot of field value probably in a loop.

      ///

      public bool UseColumns = false;

 

 

      ///

      /// Base parmeterless constructor

      ///

      public wwDataRowContainer()

      {

      }

 

      ///

      /// Constructor that allows passing in a reference to

      /// a datarow that can automatically parse the object.

      ///

      public wwDataRowContainer(DataRow Row)

      {

            this.DataRow = Row;

            if (this.UseColumns && !ColumnsCreated)

                  this.CreateColumns();

      }

 

      ///

      /// Sets the current DataRow member

      ///

      public void SetDataRow(DataRow Row)

      {

            this.DataRow = Row;

            if (this.UseColumns && !ColumnsCreated)

                  this.CreateColumns();

 

      }

 

      protected abstract void CreateColumns();

}

 

This class handles the basic assignment of the DataRow which can occur in one of two ways: Through the constructor or using the SetDataRow method. The latter is useful if you need to run in a loop and use lots of data rows. In this case you probably want to set UseColumns to true and then use SetDataRow() to make the assignment of the row.

 

DataRowCollection LineItems = this.LineItems.GetDetailTable().Rows;

 

DataRowContainers.wws_lineitemsRow LineItem = new DataRowContainers.wws_lineitemsRow();

LineItem.UseColumns = true;

 

foreach( DataRow lineItemRow in LineItems)

{

      // *** Assign the DataRow

      LineItem.SetDataRow(  lineItemRow );

 

      // *** Now use the Typed DataRow

      string lcSku = LineItem.Sku;

      decimal lnQty = LineItem.Qty;

      string lcQty = String.Format("{0:f0}",lnQty);

 

      LineItem.Updated = DateTime.Now();

}

 

// *** Updated the DataTable – now we can save in the bus object

this.LineItems.SaveLineItems();

 

 

If UseColumns is used the code must be able to find all fields. If a field is missing the code will immediately blow up. Which is a good thing actually because now you also get runtime notification error notification that the data and code class are out of sync.

 

If you rather not deal with that issue you can just not use the UseColumns flag and data will still be returned using the Name collection which means there's no code running through the table first to pick up the columns and there’s no tight coupling. This means you can use this DataRow wrapper even if the table returns only a couple of fields instead of the whole select *.

 

As mentioned I built a small, at this point crude tool for this which runs through a full database and picks up all tables and generates this datarow container class. You can filter by a prefix. Ultimately it’d be nice to allow selection of tables and maybe even queries to use, but for now it’s all or nothing

 

 DataRow Container Screen shot

 

You can download the tool and play around with it at:

 

http://www.west-wind.com/files/tools/DataRowGenerator.zip

?>


The Voices of Reason


 

aneesh
November 16, 2004

# re: Typed DataRow Generation

this article is useful in understanding the difference between typed and untyped dataset

chamaco regal
February 23, 2005

# re: Typed DataRow Generation

vb.net version?

LegacyProgrammer(VB6)
February 27, 2006

# re: Typed DataRow Generation

Fantastic Article!!

I was planning to use the Typed Dataset in my Business Objects and I was debating myself to rather convert the Dataset into Collection/Objects in the Busienss objects per table.

Could you create this article using VB code/examples than C#.

Its very much helpfull on what it is already but VBUsers still get into hard time reading C# examples. Its not that quick to understand!

Rick Strahl's Web Log
September 27, 2006

# Typed DataSet and Business Objects - Rick Strahl's Web Log

I've been giving some thought to the typed DataSet debate recently. For the most part my preference has been to use untyped DataSets because frankly it is easier to not have to worry about synching database and code every time a change is made to the database. For a long time I've completely ignored typed DataSets, but last week I dug in and took a closer look at what is really involved to make this work in applications. There's no doubt that typed DataSet provide a good amount of functionality

Sanjay Pais
April 24, 2007

# re: Typed DataRow Generation

How about using a third party utility like CodeSmith to generate the typed dataset?

Peter Strömblad
August 25, 2008

# re: Typed DataRow Generation

Thanks for making your generator available. I managed to alter it to allow C# nullable types, and it now generates a dataprovider class as well. The structure I use is an entity, and a provider that handles the entity. It is an excellent starting point and there are no typos :-}

In my weblog you can see what type of code it generates.

Mark Abel
November 22, 2008

# re: Typed DataRow Generation

I have the following question:

1. Instead of
foreach( DataRow lineItemRow in LineItems)
{ LineItem.SetDataRow( lineItemRow ); ...

I would like to write
foreach( DataRowContainers.wws_lineitemsRow LineItem in LineItems) ...

It is not essential but would be more natural. Anyway your DataRowContainer class is really great.
A tip for all those people requesting translations VB <-> C# is to download the free IDE at http://www.sharpdevelop.net/. It has an integrated tool for translation.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024