Enhancing TableAdapters
I've been playing around and researching different ways to enhance TableAdapters when more custom functionality is needed. If you're not familiar with TableAdapters, they act as the glue between a data source and a strongly-typed DataSet/DataTable. You can create them visually using the Visual Studio .NET 2005 DataSet designer and add multiple queries to them that call SQL statements or stored procedures. You can even create new stored procedures right in the VS.NET DataSet designer wizard.
There are several reasons you might want to enhance a TableAdapter:
- Support transactions (since these aren't exposed directly from the TableAdapter classes generated by VS.NET 2005)
- Have more control over when a connection begins and ends
- Perform custom functionality such as sorting after data is retrieved from the database (in cases where you want to override the default sort.
- Many others....the sky's the limit!
Fortunately, .NET 2.0 introduces the concept of partial classes which means that more than one class with the same name (but marked with the "partial" keyword) can be stored in separate files. At compile time all of the "partial" classes (with the same name) across one or more files are combined together to create a single class. Because of this great feature you can easily enhance the functionality of a TableAdapter by simply adding a partial class with the same name as the TableAdapter class generated by VS.NET. At compile time, your class and the auto-generated TableAdapter class will be combined into one class that includes your custom enhancements. You could of course inherit from the TableAdapter class as well if you want to override some of its default functionality.
Sahil Malik has a great post about how to leverage partial classes to add transactional functionality and connection lifetime management to a TableAdapter. In his blog you'll see how you can easily create a partial class with the transactional and connection-oriented methods needed. Sometimes you might need to perform simple operations such as overriding the default sort of records retrieved from the database. This can easily be done by adding a partial class with your own custom method. A basic example of adding a custom sort method is shown below:
using System;
using System.Data;
namespace LANLDataTableAdapters
{
public partial class TutorialTableAdapter : System.ComponentModel.Component
{
public virtual LANLData.TutorialRow[] GetAlphaSortedData()
{
//DB defines a SortOrder field. This method allows
//us to override that default sort and sort alphabetically.
LANLData.TutorialDataTable dt = this.GetData();
LANLData.TutorialRow[] sortedRows =
(LANLData.TutorialRow[])dt.Select("1=1", "Description");
return sortedRows;
}
}
}
While it's certainly advisable to perform any sorts in the database if possible, this example provides a way to sort the rows by description after they've been retrieved. There are several ways to do the sort (Array.Sort or a DataView) but using the Select() method proved to be one of the easiest for what I needed (and required the least amount of code). By using partial classes you can easily add whatever enhancements you need to your TableAdapters.