Retrieving Data

for RuBoard

The first method that most developers will invoke when using a data adapter is the Fill method. The Fill method is typically overloaded to support a variety of arguments that are used to populate data either in a DataTable or in one or more DataTable objects contained in a DataSet . For example, the Fill method of OleDbDataAdapter contains six public signatures that enable you to populate a DataTable , a DataSet , a DataTable with a specific mapping name , a specific range of rows in table in a DataSet , and even a DataTable or DataSet with data from an ADO Recordset object.

Note

In addition, OleDbDataAdapter supports four protected signatures for the Fill method that are inherited from DbDataAdapter but can't be accessed because OleDbDataAdapter is a sealed class ( NotInheritable in VB). This implies that developers writing their own .NET Data Providers can inherit their data adapter from DbDataAdapter and override these methods to provide custom functionality.

The SqlDataAdapter provides the same set of signatures with the exception of not being able to read from an ADO Recordset .

When the Fill method executes, it actually executes the command object referenced by its SelectCommand property. Typically, the SelectCommand is populated using the constructor either by passing in a string that equates to the CommandText property of the command object or the instantiated command object itself. If only the command text is passed, the connection object must also be passed in order for the Fill method to know which data store to execute the SelectCommand against. As a result, the typical pattern is shown in Listing 12.1.

Listing 12.1 Using a data adapter. This listing shows the typical pattern used to populate a DataSet with a data adapter.

 SqlConnection con = new SqlConnection(_connect); SqlCommand com = new SqlCommand("usp_GetTitles",con); com.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(com); DataSet ds = new DataSet(); try {   da.Fill(ds); }   catch (SqlException e) {   // Handle error } 

As you can see from Listing 12.1, the connection object is first instantiated and passed to the constructor of the SqlCommand object, which is in turn passed to the constructor of the SqlDataAdapter . Note that the DataSet must be instantiated before passing it to the Fill method. Not doing so results in an exception.

Tip

As you learned on Day 9, "Using Connections and Transactions," an exception might be raised in the constructor of the connection object if the connection string is invalid. As a result, if you allow the connection string to be built dynamically, you should wrap the instantiation of the SqlConnection object in a try catch block.

One of the things you should notice in this code snippet is that the connection needn't be opened before calling the Fill method. If the connection associated with the SelectCommand isn't open already, the data adapter will open and close it as needed. Behind the scenes, the data adapter opens a data reader using the command object and uses it to populate both the schema and the rows of the DataTable or DataSet passed to the method.

The general rules that the Fill method uses are as follows :

• Tables and columns are created only if they don't already exist.

• Column types are created based on a mapping of the Common Types System (CTS) types to the types for a particular provider. You can find the complete list in the online documentation under the topic "Mapping .Net Data Provider Data Types to .NET Framework Data Types."

• By default, the Fill method maps the result sets returned from the command to data tables named Table, Table1, and so on. It then attempts to map the column names returned from the data store to the columns of a DataTable . This can be specified using table and column mappings.

• If tables and columns already exist, the existing schema is used and the value of the MissingSchemaAction property is used to determine the course to take.

• As you learned on Day 4, "DataSet Internals," primary keys aren't created unless they exist in the data store and the MissingSchemaAction property is set to AddWithKey .

• When populating the rows, if the Fill method finds a matching primary key, the data from the data store will be used to overwrite the data in the DataTable .

• If no primary key is found, the rows returned from the data store are simply appended to the DataTable .

In the remainder of this section, we'll take a look at how the schema is generated when filling a DataSet and how table mappings work. We'll also discuss a couple of advanced techniques for populating data using a data adapter.

Schema Generation

As shown in the bulleted rules earlier, ultimately, the Fill method will populate either one or more DataTable objects that are currently empty or that already contain schemas. We'll discuss exactly how this determination happens in detail in the next section. However, in either case, the generation of the schema information for an individual DataTable is controlled through the MissingSchemaAction property of the data adapter. This property can be set to any of the values in the MissingSchemaAction enumeration, as shown in Table 12.1.

Table 12.1. Schema generation. The MissingSchemaAction enumeration controls the behavior of the data adapter during the schema generation process.

Value	Description
Add	The default. Adds any columns necessary to complete the schema.
AddWithKey	Adds any columns and primary key information necessary to complete the schema.
Error	Throws an InvalidOperationException if the incoming schema doesn't map exactly to the existing schema of the DataTable .
Ignore	Ignores any extra columns from the data store that don't map to columns in the DataTable .

Obviously, the choice of the MissingSchemaAction value can have a major impact on the resulting data. It can also affect where you look for exceptions. Both the first and second values won't throw exceptions and will add any columns from the data store to the already existing columns in the DataTable . As you learned on Day 4, the AddWithKey option might 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.

Tip

When using any of these options, the DataTable can contain additional columns not populated by the Fill method. You can then populate these programmatically, through user input, or even through calculated values using the Expression property.

The Error value is the strictest of the MissingSchemaAction values and can be used to make sure that the incoming data maps exactly to the schema of the DataTable . This might be the case when you're using a strongly typed DataSet , as we discussed on Day 6, "Building Strongly Typed DataSet Classes." Note that an exception will be thrown even if the DataTable doesn't contain any columns. The Ignore value won't cause an exception and is useful when you might be populating a DataTable from multiple commands that return variant numbers of columns. It is also useful if you want to protect yourself against changes made to a stored procedure. Generally, of course, using Ignore isn't recommended. This is because you don't want to get into the habit of requesting more columns from the data store than you'll use, thereby increasing the workload of the data store unnecessarily.

Table Mappings

When passed a DataSet , the Fill method must first determine whether an existing DataTable exists into 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 are named Table, a new table with the name Table is created. Its columns are created using the names and data types returned from the data store. As an alternative, the name of a table can be passed as the second argument to the Fill method and it'll 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, they'll be used as long as there are either table mappings or their names are Table, Table1, and so on.

Note

If the Fill method encounters duplicate column names, they'll 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 when passed a DataTable , the Fill method first looks for a table mapping. If one isn't found, it 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 and regardless of what tables or columns already exist.

Although this process allows all the data to be mapped to the DataSet or DataTable , there are times when you want to make sure that the data is mapped to particular tables and columns. This might be the case if you're populating a table in a strongly typed DataSet that contains column names that aren't the same as those in the data store. In addition, this might be the case 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 the DataTableMappingCollection object exposed by the TableMappings property of the data adapter. As an example, consider the following snippet:

 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 data store 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 doesn't already contain a DataTable named myTitles, a new table named myTitles will be created with all 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 data store. If the table already exists, it will contain any existing columns, new columns from the data store that aren't in the column mappings collection, and the three columns in the collection. This is the case because the default for the MissingMappingAction property is Passthrough .

Note

Passing the name of a table mapping to the Fill method as shown in the previous code 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 so

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

you don't need to 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 will be 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 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 12.2.

Table 12.2. Mapping tables and columns. The MissingMappingAction enumeration controls the behavior of the data adapter when mappings are being applied.

Value	Description
Error	Throws an InvalidOperationException if either the table mapping or an individual column mapping is missing when the Fill method attempts to populate a DataTable .
Ignore	Any table or column that doesn't have a defined mapping is ignored.
Passthrough	The default. The table and columns are added to the DataSet and if a mapping exists, it's used as well.

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

Of course, the MissingSchemaAction property works in combination so that, for example, by using the Error value, you can ensure that the columns defined in the column mappings already exist in the DataTable . Likewise, if the MissingSchemaAction prop-erty is set to Add , the columns needn't already have been created in the DataTable .

To give you an example of the strictest case where 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 12.2.

Listing 12.2 Using mappings. This code snippet creates table and column mappings for use by the Fill method of the data adapter.

 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 } 

In Listing 12.2, the same usp_GetTitles stored procedure is used, but in this case it 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 the columns from the Titles table in the ComputeBooks database to columns in the XSD schema loaded from the stream.

Note

The source and destination column names in the column mappings don't need to 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, respectively.

Advanced Retrieval

The techniques shown thus far illustrate the most common ways that a DataSet can be populated. There are, however, additional techniques that you can use to add information to the DataSet and to retrieve data incrementally.

Adding Metadata for SQL Server

As you learned during Week 1, the DataColumn , DataTable , and DataSet objects all expose a set of properties that you can manipulate to make sure that the data in the data store 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. Although 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 aren't populated. It turns out that SQL Server provides extended properties that enable 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 GO EXEC  sp_addextendedproperty 'caption', 'Bulk Amount', 'user',   dbo, 'table', Titles, 'column', BulkAmount GO EXEC  sp_addextendedproperty 'defaultvalue',0, 'user',   dbo, 'table', Titles, 'column', BulkAmount GO 

Note

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 is arbitraryyou 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.

Note

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.

After the properties are in place, you can create a stored procedure to retrieve all the column properties for a particular table, as shown in the following code snippet:

 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, so 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, as shown in Listing 12.3.

Listing 12.3 Retrieving extended properties. This method retrieves the caption and default value extended properties and associates them with a DataColumn in a DataTable .

 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();      } } 

In Listing 12.3, 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 code 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 after a DataSet or DataTable has been populated, like so:

 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 re-specified in each application that accesses the database. However, the cost is that you incur an extra roundtrip 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.

Tip

As you learned on Day 4, each of these classes also exposes 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.

Retrieving Partial Result Sets

If you are a developer who has built applications that require data access, you'll no doubt have encountered a 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. In ADO.NET, you can use two primary techniques to address this scenario.

First, as discussed previously, the Fill method of data adapter classes is overloaded. One of the overloaded signatures enables 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, 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"); 

Note that this signature requires a DataSet to be passed to the Fill method along with a table mapping. Just as we 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's trivial to make a calculation in order 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 the rows after the starting row will be added to the table. As you might expect, the starting and max records arguments apply to only the first result set in the event that the SelectCommand returns multiple results. Finally, if the table already exists, the data will be appended to it based on the rules discussed at the beginning of today's lesson.

Note

Although the documentation states that the names of tables in a DataSet are case-sensitive, they are in fact not. In other words, adding a table named titles to a DataSet and then using the Fill method to populate a table named Titles in the same DataSet won't result in the creation of two tables.

The downside of this methodand its fatal flaw in all but the simplest applicationsis 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 ask the data store for only data that you're going to use. As a result, this technique isn't recommended.

An alternative and more efficient technique you can use to incrementally populate a DataSet is to pass arguments to the SelectCommand , which 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 12.4 and that calls the stored procedure shown in Listing 12.5.

Listing 12.4 Incrementally retrieving data. This method calls the usp_OrdersByDate stored procedure to incrementally populate a DataSet based on the date range.

 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("@startdate", SqlDbType.SmallDateTime));    com.Parameters[0].Value  = startDate;    com.Parameters.Add(new SqlParameter("@enddate", SqlDbType.SmallDateTime));    com.Parameters[1].Value  = endDate;    try    {        da.Fill(ds);    }    catch (SqlException e)    {        // Handle Exception    } } 

Listing 12.5 Selecting data incrementally. This stored procedure is called by the method in Listing 12.4 to retrieve a range of rows from the Orders and OrderDetails tables.

 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 

In Listing 12.4, the method accepts a DataSet and parameters that specify both the start and end dates to query on. The stored procedure usp_OrdersByDate in Listing 12.5 is then called and passed the parameters. Because 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, to retrieve all 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 then appended to the two DataTable objects in the DataSet . In this way, the client can incrementally add rows by varying the arguments. Note that this technique also allows the client to skip rows wherever they deem necessary. 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.

for RuBoard