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 …
You can download the tool and play around with it at:
http://www.west-wind.com/files/tools/DataRowGenerator.zip
?>
Other Posts you might also like