Using Typed DataRows
Using Typed DataRows
Typed DataSets are quite nice, but they add a fair amount of overhead especially if you use only a little bit of data . Specifically when ever you load any data into any table all the tables and DataColumns are created. This is serious overkill especially if you're looking at a single record to retrieve some data.
To get around the overhead, but still provide typed access to your data, the wwBusiness class framework ships with a utility that creates DataRowContainer classes that provide typed access to DataRows from your database on an as needed basis. Here's what the utility looks like in action:
To use this utility provide a connection string to your database and a namespace. The namespace will be the namespace that the classes are generated in. You can also specify an output file, which should be a CSharp file in your Business Objects project. You'll have to add this file once generation is complete.
The tool creates wrapper classes for all the tables in the database. You can optionally specify a table name prefix to pull only in tables that start with a certain name prefix. For example in the Web Store all tables have a wws_ prefix and only those tables are pulled in while some of the support tables are not pulled in. If you don't do this you will get wrappers for these other tables as well, but it's no big deal (other than code size) because these wrappers will just not be used although you certainly can.
Important:
This tool is a code generator, so you will need to re-generate whenever the database changes. However, this is a one step process (unlike the DataSet generators in VS.Net) as it generates everything directly off your Database.
This tool generates one class per database table as well as a base class wwDataRowContainer which performs some of the base tasks. The generated code looks something like this:
//
// *** Auto Generated Code by DataRowContainerGenerator
// *** Created by West Wind Technologies, http://www.west-wind.com
// ***
// *** Generated on: 12/12/2003 7:46:30 PM
//
using System;
using System.Data;
namespace DataRowContainers {
/// <summary>
/// This class is a base class container class that holds typed members
/// of a data row. The content of these subclasses is usually generated
/// throgh the DataRowContainerGenerator tool provided in the Tools
/// directory.
/// </summary>
public abstract class wwDataRowContainer
{
protected DataRow DataRow;
protected bool ColumnsCreated = false;
/// <summary>
/// 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.
/// </summary>
public bool UseColumns = false;
/// <summary>
/// Base parmeterless constructor
/// </summary>
public wwDataRowContainer()
{
}
/// <summary>
/// Constructor that allows passing in a reference to
/// a datarow that can automatically parse the object.
/// </summary>
public wwDataRowContainer(DataRow Row)
{
this.DataRow = Row;
if (this.UseColumns && !ColumnsCreated)
this.CreateColumns();
}
/// <summary>
/// Sets the current DataRow member
/// </summary>
public void SetDataRow(DataRow Row)
{
this.DataRow = Row;
if (this.UseColumns && !ColumnsCreated)
this.CreateColumns();
}
protected abstract void CreateColumns();
}
//
// *** Start of DataRow classes that map to each table
//
[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 Company
{
get
{
if (this.UseColumns)
return (String) this.DataRow[this.CompanyColumn];
else
return (String) this.DataRow["Company"];
}
set
{
this.DataRow["Company"] = 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 typed column Properties
// *** Column Definitions
DataColumn PkColumn;
DataColumn CompanyColumn;
DataColumn EnteredColumn;
... more columns
protected override void CreateColumns()
{
PkColumn = this.DataRow.Table.Columns["Pk"];
AddressColumn = this.DataRow.Table.Columns["Address"];
EnteredColumn = this.DataRow.Table.Columns["Entered"];
... more column assignments
this.ColumnsCreated = true;
}
}
... More classes for each table in the database
}Note that there are two modes: UseColumns can be set to true or false. Using Columns creates a DataColumn object for each field which can be more efficient when retrieving column values, but there's some overhead in creating the columns in the first place. When columns are not retrieved the data is simply returned using the FieldName index. Otherwise the Column itself is used for an index.
Using Columns makes sense if you retrieve a lot of fields, such as when going through multiple rows of data. Otherwise using the Indexed properties probably provides better performance. In either case you get a typed interface to the DataRow.
How to use it
Once you've generated the DataRowContainer classes you can use them in your code by creating an instance and assigning a DataRow to it. Note that usage is not automatic - it requires a manual assignment to utilize the the typed row. The assignment is simple though:Customer.Load(1000) // *** Load a customer by PK
DataRowContainers.wws_customersRow CustRow = new DataRowContainers.wws_customersRow(Customer.DataRow);
this.txtAddress.Text = CustRow.Address;
CustRow.Address = "13 Elm Street";
Customer.Save();Note that there are no type conversions in your code and you get full Intellisense as you type your code.
In the above code the DataRow is created directly and without Column binding. You can also create the class with a parameterless constructor and then assign the UseColumns property:
DataRowContainers.wws_customersRow CustRow = new DataRowContainers.wws_customersRow();
CustRow.UseColumns = true;
CustRow.SetDataRow(Customer.DataRow);Using SetDataRow is also a good idea if you're running operations in a loop, since you can have the column created once then have them reused in subsequent cycles. For example:
Invoice.LoadLineItems(); // *** Creates a DataTable of lineitmes
DataTable tbLineItems = Invoice.LineItems.GetDetailTable();
DataRowContainers.wws_lineitemsRow LineItem = new DataRowContainers.wws_lineitemsRow();
LineItem.UseColumns = true;
foreach (LineItemRow in tbLineItems.Rows) {
LineItem = LineItem.SetDataRow( LineItemRow );
LineItem.Descript = "Item " + x.ToString();
... more code with the typed lineitem
}Repeated calls to SetDataRow() will reuse the same column definitions created the first time a DataRow is created.
Hooking up the generator in the IDE
The DataRowGenerator.exe file can be hooked into the Visual Studio IDE. The generated source file actually contains the generation settings embedded at the bottom of the file as an XML Serialization signature. Thus you can simply open the file from within DataRowGenerator or pass a filename as a parameter and it will regenerate the source code with the updated database structure.To hook this up in VS.Net you can right click on the generated file (once it's been added to your project) and select Open With. You will see a list of configured editors.
Click Add and add the DataGenerator.exe file. Click Ok, then Open and DataRowGenerator will popup open with the settings from the generated file loaded.
You can simply click Generate to regenerate your source file.
Comment or report problem with topic