Generating a Schema
As shown in the rules above, the Fill method will ultimately populate one or more DataTable objects that are currently empty (or missing) or that already exist in the DataSet and contain schemas. In either case, the generation of the schema information for an individual DataTable is controlled through the MissingSchemaAction property of the data adapter, which can be set to any of the values in the MissingSchemaAction enumeration as shown in Table 1.

Obviously, the choice of the MissingSchemaAction value can have a major impact on the resulting data and affect where you look for exceptions. Both the first and second values will not throw exceptions and will add any columns from the database to the existing columns in the DataTable. The AddWithKey option may also populate the AllowDBNull, MaxLength, AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties of the DataColumn objects as well as the PrimaryKey property of the DataTable, depending on the provider.

The Error value is the strictest and can be used to make sure that the incoming data maps exactly to the schema of the DataTable. This might be useful when you are using a typed (or derived) DataSet created by the XSD schema designer. Note that an exception will be thrown even if the DataTable does not contain any columns.

The Ignore value will not cause an exception and is useful for populating a DataTable from multiple commands that return variant numbers of columns or for protecting yourself against changes made to a stored procedure. Generally, of course, using Ignore is not recommended since you don't want to get into the habit of requesting more columns from the database than you will use, thereby increasing the workload of the database unnecessarily.

Using Table Mappings
When passed a DataSet, the Fill method must first determine if a DataTable exists in which to place the rows returned from the SelectCommand. It does this by looking at both the names of the existing tables and any table and column mappings that the data adapter has. By default, if no tables exist in the DataSet, or none that are named "Table", a new table with the name "Table" is created and its columns are created using the names and data types returned from the database. As an alternative, the name of a table can be passed as the second argument to the Fill method and it will be used to map the result set. If the command returns multiple result sets, additional tables are created with the names "Table1", "Table2", and so on. If the DataSet already contains tables then they will be used, as long as there are table mappings or their names are "Table", "Table1", etc.

If the Fill method encounters duplicate column names they will be named "columnname1", "columnname2", and so on. Unnamed columns (such as those resulting from an aggregate function) will be named "Column1", "Column2", and so on. As a result, you'll want to make sure to avoid these names (as you should anyway) and always use explicit names for your columns.

When using the overloaded signature and passed a DataTable, the Fill method first looks for a table mapping and if one is not found, simply fills the table, regardless of its name, as you might expect. Both the MissingSchemaAction and MissingMappingAction properties influence this process. The end result is that these defaults ensure that the data can be added to the DataSet or DataTable without any table or column mappings, regardless of what tables or columns already exist.

While this process allows all the data to be mapped to the DataSet or DataTable, there are times when you want to make sure the data is mapped to particular tables and columns. This might be the case if you are populating a table in a typed DataSet that contains column names that are not the same as those in the database, or when the DataSet was created using an XSD schema supplied by a trading partner so that its data can be written to XML and sent to the partner. In these cases you can create custom table and column mappings by adding items to the DataTableMappingCollection object exposed by the TableMappings property of the data adapter.

da.TableMappings.Add("Titles","myTitles");
da.TableMappings[0].ColumnMappings.Add("Description","Desc");
da.TableMappings[0].ColumnMappings.Add("Title","BookTitle");
da.TableMappings[0].ColumnMappings.Add("Price","RetailPrice");

da.Fill(ds,"Titles");

In this example, a new table mapping is added to the data adapter with the source name of "Titles" and the data table name of "myTitles". Within the table mapping the DataColumnMapping is populated by passing the column name in the database along with the column name in the DataTable. The overloaded Fill method is then called in order to use the table mapping. If the DataSet ds does not already contain a DataTable named "myTitles", a new table named "myTitles" will be created with all of the columns returned from the result set.

However, the three columns added to the column mappings will be named accordingly, rather than as they were named in the database. If the table already exists then it will contain any existing columns, new columns from the database that are not in the column mappings collection, and the three columns in the collection. This is the case since the default for the MissingMappingAction property is Passthrough.

Passing the name of a table mapping to the Fill method, as shown in the previous snippet, is at first confusing to many developers. This is likely the case because you can either pass in the name of the table mapping (the first argument to the Add method of the DataTableMappingCollection object) or the name of the table to create if no table mappings have been defined for the data adapter. As you can imagine, if you create a table mapping like:

da.TableMappings.Add("Table", "myTitles");

then you needn't pass the source name ("Table" in this case) to the Fill method. This default table mapping (as reflected by the DefaultSourceTableName field of the data adapter) will be used and the name of the new table set to "myTitles". You can also create default mappings for "Table1", "Table2", and so on in the event that the SelectCommand of the data adapter returns multiple result sets.

As I just mentioned, the MissingMappingAction property influences the runtime behavior when table and column mappings are involved and can be set to one of the values of the MissingMappingAction enumeration, as shown in Table 2.

As you can see from Table 2, the Error value is the strictest and ensures that you have mappings and that those mappings consider all of the result sets and columns returned from the SelectCommand. As with the MissingSchemaAction the Ignore value can be used to ignore any table or columns that are not a part of the mapping while Passthrough is the default and allows a new table and columns to be integrated with those defined in the mapping.

To give you an example of the strictest case in which you want to make sure the result set is fully mapped to a DataTable with a custom set of columns, consider the code in Listing 1. This snippet creates table and column mappings for use by the Fill method of the data adapter.

In Listing 1, the same usp_GetTitles stored procedure is used but is passed a parameter populated with the variable author. The DataSet ds that is used to hold the results has its schema loaded from a stream variable called schemaStream. Both the MissingSchemaAction and MissingMappingAction properties are set to Error to ensure that the schema from the result set matches exactly with that in the DataSet and that the table and column mappings fully map to the result set. A default mapping is then created that maps all of the columns from the Titles table in the database to columns in the XSD schema loaded from the stream. Note that the source and destination column names in the column mappings needn't have different names. In the case of ISBN, Author, and Discount, the names are the same.

The Fill method is then called, which uses the default mapping to load the result set into the table named "Titles" in the DataSet. Note that when the schema was loaded it must then have created a table named "Titles" in order for an exception not to be thrown. The two catch blocks handle errors resulting from the schema and mapping operations and any SQL Server exceptions that are thrown.

Retrieving Partial Result Sets
If you have built applications that require data access, you've no doubt have encountered the situation in which you want to incrementally retrieve results from a database. This might be the case, for example, when the potential number of rows a user would like to see is very large, but you would like to avoid having to initially retrieve them all. This comes up quite often in ASP.NET applications in which you want to allow users to page through a DataGrid. In fact, the DataGrid offers a very flexible interface to allow you to provide the appropriate rows in a just-in-time fashion by setting its AllowCustomPaging property to true and implementing the PageIndexChanged method. In order to get those rows using ADO.NET, there are two primary techniques you might use.

First, you could use one of the overloaded signatures of the Fill method, which allows you to pass in the row to start with (that is, the row in the result set returned by the SelectCommand to start with) and the maximum number of rows to use to populate the DataTable. For example, in order to populate the DataTable with the first 50 rows returned in the result set you would use the following syntax:

da.Fill(ds,0,50,"Titles");

This signature requires a DataSet to be passed to the Fill method along with a table mapping. Just as discussed previously, in the event that a table mapping doesn't exist you can pass in the name of the new DataTable. Of course, rather than hard-coding the starting row and the number of rows to add to the table, it is trivial to make a calculation to incrementally add rows to the DataSet using variables as the user requests more data. By passing 0 into the max records parameter (the third argument) all of the rows after the starting row will be added to the table. As you might expect, the starting and max records arguments only apply to the first result set in the event that the Select Command returns multiple results. Finally, if the table already exists, the data will be appended to it based on the rules discussed earlier. This technique has the advantage of working with the automatic paging feature of the ASP.NET DataGrid control.

The downside of this method, and its fatal flaw in all but the simplest applications, is that the query encapsulated in the SelectCommand will be executed in its entirety even though ultimately only a subset of the rows returned will be used. This wastes resources on the server and violates the cardinal rule that you only ask the database for data that you are going to use. As a result this technique is not recommended.

An alternate and more efficient technique you can use to incrementally populate a DataSet is to pass arguments to the SelectCommand that selects only the specific rows. Typically, this requires that you pass to the command arguments that specify a range of rows. For example, consider the GetOrdersByDate method shown in Listing 2.

In this example, the method accepts a DataSet and parameters that specify both the start and end dates to query on. The stored procedure usp_OrdersByDate is then called and passed the parameters as shown in Listing 3. Since the DataSet is passed into the method the client can call this method repeatedly and vary the arguments each time to retrieve a subset of the data with each invocation. For example, in order to retrieve all of the orders from January 1, 2000, to the present, the client could make the following two calls:

DataSet ds = new DataSet();

GetOrdersByDate(ds,new DateTime(2002,1,1),DateTime.Now);
GetOrdersByDate(ds, new DateTime(2000,1,1), new DateTime(2001,12,31));

In the first call, the orders from January 1, 2002, to the present are retrieved. When the DataSet is passed back to the method the orders from January 1, 2000 to December 31, 2001 are appended to the two DataTable objects in the DataSet. In this way the client can incrementally add rows by varying the arguments. This technique also allows the client to skip rows wherever necessary although it puts the burden on them to provide the correct arguments. However, the AddWithKey value is used for the MissingSchemaAction property to ensure that if the method is called more than once with overlapping date ranges the Fill method will match the rows based on the existing primary key rather than adding multiple copies of the same row. Of course, if you needn't save the existing rows, as is the case when you use custom paging in a DataGrid control, you would simply pass an empty DataSet into the method. You can also rely on the database's ability to return a specific number of rows, for example, by using the SET ROWCOUNT statement in SQL Server. In this way, your stored procedures need only accept the last key value retrieved and the number of rows to return. Of course, this assumes you have a key column on which you can order your results.

Adding Metadata for SQL Server
As you probably know, the DataColumn, DataTable, and DataSet objects all expose a set of properties that you can manipulate to make sure the data in the database is accurately represented in the DataSet. For example, the DataColumn class exposes the AllowDBNull, MaxLength, Unique, DefaultValue, Caption, and other properties that affect how the data can be manipulated. While some of these properties are populated automatically when you use the AddWithKey value of the MissingSchemaAction enumeration, not all of them are. For example, the Caption and DefaultValue properties are not populated. It turns out that SQL Server provides extended properties that allow you to easily store and retrieve user-defined metadata directly in the database. You can use extended properties to store information such as the caption and default value and then read that information dynamically into the DataSet.

In SQL Server 2000, extended properties can be placed on various database objects, including tables, views, stored procedures, rules, defaults, and functions. Using the sp_addextendedproperty stored procedure you can add any user-defined name-value pair to the extended properties collection for an object. These values (stored as a sql_variant of less than 7,500 bytes) can then be read using the fn_listextendedproperty function. For example, to specify the captions and default values that applications can use, you could execute the following statements against a SQL Server 2000 database.

EXEC sp_addextendedproperty 'caption', 'Bulk Discount', 'user',
dbo, 'table', Titles, 'column', BulkDiscount
EXEC sp_addextendedproperty 'caption', 'Bulk Amount', 'user',
dbo, 'table', Titles, 'column', BulkAmount
EXEC sp_addextendedproperty 'defaultvalue',0, 'user',
dbo, 'table', Titles, 'column', BulkAmount

(See the SQL Server Books Online for more information on the meaning of the parameters passed to the sp_addextendedproperty stored procedure.)

In this example two caption properties are added for the BulkDiscount and BulkAmount columns of the Titles table. It should be noted that the selection of the property names "caption" and "defaultvalue" are arbitrary, so you can create your own property names as you see fit. You can also add extended properties through a dialog box by right-clicking on the object in the Query Analyzer. Microsoft adds an extended property with the name "MS_Description" for a column when the description is populated in the table design window in SQL Server Enterprise Manager.

Once the properties are in place you can create a stored procedure to retrieve all of the column properties for a particular table as shown in the snippet below.

CREATE PROCEDURE usp_GetColumnProperties
@table nvarchar(40)
AS
SELECT *
FROM ::fn_listextendedproperty(null,'user','dbo',
'table',@table,'column',null)

In the fn_listextendedproperty function, the values that can vary are passed as null and the procedure will return all the properties on the given table that are associated with columns. Within your .NET code it then becomes relatively simple to create a method that can be used to populate the appropriate DataColumn properties (see Listing 4).

In Listing 4 the GetColumnProperties method accepts the name of the SQL Server table to query for and the name of the DataTable that contains the columns that map to that table. It then calls the stored procedure shown in the previous snippet and loops through the results. Within the loop it uses a switch statement to look for the appropriate property names before using the value to set the Caption and DefaultValue properties. Note that this method assumes that the names of the columns in the DataTable are the same as those in the database table. If you used column mappings to fill the DataSet you would obviously have to query the mappings to determine which column in the DataTable to manipulate. This would make the code slightly more complex.

A client could then call this method once a DataSet or DataTable has been populated, as in:

da.Fill(ds);
GetColumnProperties("Titles",ds.Tables[0]);

This technique also assumes that the caller of the GetColumnProperties knows which table in the database maps to which table in the DataSet. The obvious benefit of retrieving metadata in this way is that it can be specified once at the database server and not respecified in each application that accesses the database. However, the cost is that you incur an extra round-trip to the server to retrieve the properties. As a result, you should use this technique only when the additional metadata you retrieve will be used by the application.

In addition, both the DataSet and DataTable classes expose an ExtendedProperties property that can hold a collection of custom properties. Given the name of the object in SQL Server it would be trivial to write a method that retrieves the extended properties for any SQL Server object and adds them to the ExtendedProperties collection.

Summary
In order to effectively use ADO.NET you need to be aware of how it is customized to suit the scenarios you'll run into when developing applications. In this article I've attempted to show some typical but more advanced techniques for retrieving data using ADO.NET. If you're interested in going deeper with ADO.NET, check out my book, Teach Yourself ADO.NET in 21 Days (Sams).

Author Bio
Dan Fox is a technical director for Quilogy (www.quilogy.com). Dan evangelizes technology within Quilogy and to its customers through writing and speaking. He is the author of Building Distributed Applications with Visual Basic .NET and Teach Yourself ADO.NET in 21 Days (Sams). dfox@quilogy.com

	



Listing 1: Using Mappings 

SqlConnection con = new SqlConnection(_connect);
SqlCommand com = new SqlCommand("usp_GetTitles",con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@author",author));

SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
ds.ReadXmlSchema(schemaStream);

try
{
  da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  da.MissingMappingAction = MissingMappingAction.Error;

  da.TableMappings.Add("Table","Titles");
  da.TableMappings[0].ColumnMappings.Add("ISBN","ISBN");
  da.TableMappings[0].ColumnMappings.Add("Author","Author");
  da.TableMappings[0].ColumnMappings.Add("Description","Desc");
  da.TableMappings[0].ColumnMappings.Add("Title","BookTitle");
  da.TableMappings[0].ColumnMappings.Add("Price","RetailPrice");
  da.TableMappings[0].ColumnMappings.Add("Discount","Discount");
  da.TableMappings[0].ColumnMappings.Add("BulkAmount","BulkQualify");
  da.TableMappings[0].ColumnMappings.Add("BulkDiscount","Bulk");
  da.TableMappings[0].ColumnMappings.Add("Publisher","Publisher");
  da.TableMappings[0].ColumnMappings.Add("PubDate","PublicationDate");
  da.TableMappings[0].ColumnMappings.Add("CatID","CategoryId");
  da.TableMappings[0].ColumnMappings.Add("Cover","CoverImage");

  da.Fill(ds);
}
catch (InvalidOperationException e)
{
  // Handle mapping errors
}
catch (SqlException e)
{
  // Handle error

Listing 2: Incrementally Retrieving Data

}


private virtual void GetOrdersByDate(DataSet ds, 
  DateTime startDate, DateTime endDate)
{
   SqlConnection con = new SqlConnection (_connect);
   SqlCommand com = new
   	SqlCommand("usp_OrdersByDate",con);
   SqlDataAdapter da = new SqlDataAdapter(com);
   da.MissingSchemaAction = 
   	MissingSchemaAction.AddWithKey;

   // Setup the parameters
   com.CommandType = CommandType.StoredProcedure;
   com.Parameters.Add(new SqlParameter("@start date", SqlDbType.SmallDateTime));
   com.Parameters[0].Value  = startDate;            
   com.Parameters.Add(new SqlParameter("@end date", SqlDbType.SmallDateTime));
   com.Parameters[1].Value  = endDate;

   try
   {
       da.Fill(ds);
   }
   catch (SqlException e)
   {
       // Handle Exception
   }
}

Listing 3: Selecting Data Incrementally

CREATE PROCEDURE usp_OrdersByDate
@startdate smalldatetime = null,
@enddate smalldatetime = null
AS

IF @startdate IS NULL 
 SET @startdate = convert(smalldatetime,'1/1/1900')

IF @enddate IS NULL 
 SET @enddate = convert(smalldatetime,'1/1/2079')

SELECT * FROM Orders
WHERE OrderDate BETWEEN @startdate AND @enddate
ORDER By OrderDate DESC

SELECT a.* FROM OrderDetails a JOIN Orders b on a.OrderID = b.OrderID
WHERE b.OrderDate BETWEEN @startdate AND @enddate
GO

Listing 4: Retrieving Extended Properties

private virtual void GetColumnProperties(String tableName, DataTable dt) 
{
    SqlConnection con = new SqlConnection(_connect);
    SqlDataReader dr;

    // Setup the call to the stored procedure
    SqlCommand com = new
	SqlCommand("usp_GetColumnProperties", con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.Add(new
	SqlParameter("@table",tableName));

    con.Open();
    dr = com.ExecuteReader(CommandBehavior.CloseConnection);
    while (dr.Read())
    {
        switch (dr["name"].ToString())
        {
           // Handle captions and default values
           case "caption":
               dt.Columns[dr["objname"].ToString()].Caption = 
                 dr["value"].ToString();
               break;
           case "defaultvalue":
               dt.Columns[dr["objname"].ToString()].DefaultValue = dr["value"];
               break;
         }
       dr.Close();
     }
}