Chapter 5: Retrieving Data Using Objects

Creating and Using DataAdapter Objects

You now know what a DataAdapter is and what it can do for you, so let's examine how to create and use one.

Creating a DataAdapter

When you create a DataAdapter, you generally want to set its SelectCommand property to a valid Command object. The following code snippet sets the SelectCommand for a new DataAdapter.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim strSQL As String = "SELECT CustomerID, CompanyName FROM Customers" Dim cmd As New OleDbCommand(strSQL, cn) Dim da As New OleDbDataAdapter() da.SelectCommand = cmd

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); string strSQL = "SELECT CustomerID, CompanyName FROM Customers"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = cmd;

DataAdapter Constructors

The DataAdapter class has three constructors that you can use to simplify the process of creating a DataAdapter, as shown in the following code. One constructor accepts a query string and a connection string.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim strSQL As String = "SELECT CustomerID, CompanyName FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; string strSQL = "SELECT CustomerID, CompanyName FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);

There's a potential drawback to this approach. Say you're going to use a series of DataAdapter objects in your application. Creating your DataAdapter objects in this fashion will create a new Connection object for each DataAdapter. You can ensure that your DataAdapter objects use the same Connection object by using the DataAdapter constructor that accepts a query string and a Connection object. The following code snippet creates two DataAdapter objects, each using the same Connection object:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim daCustomers, daOrders As OleDbDataAdapter daCustomers = New OleDbDataAdapter("SELECT ... FROM Customers", cn) daOrders = New OleDbDataAdapter("SELECT ... FROM Orders", cn)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter daCustomers, daOrders; daCustomers = new OleDbDataAdapter("SELECT ... FROM Customers", cn); daOrders = new OleDbDataAdapter("SELECT ... FROM Orders", cn);

The DataAdapter also offers a third constructor that accepts a Command object. If you've already created a Command object and want to create a DataAdapter that uses your Command object to populate a DataSet, you can use the following code:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim strSQL As String = "SELECT CustomerID, CompanyName FROM Customers" Dim cmd As New OleDbCommand(strSQL, cn) Dim da As New OleDbDataAdapter(cmd)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;"                  "Initial Catalog=Northwind;Trusted_Connection;"; OleDbConnection cn = new OleDbConnection(strConn); string strSQL = "SELECT CustomerID, CompanyName FROM Customers" OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataAdapter da = new OleDbDataAdapter(cmd);

Retrieving Results from a Query

Now that we've looked at a few different ways to create a DataAdapter programmatically, let's look at how to use one to store the results of a query into a DataSet. First let's create a simple DataAdapter to fetch data from the Customers table in the Northwind database.

Using the Fill Method

Calling the DataAdapter object's Fill method executes the query stored in the DataAdapter object's SelectCommand property and stores the results in a DataSet. The following code calls the Fill method:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds)

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds);

In this code snippet, calling the Fill method creates a new DataTable in the DataSet. The new DataTable contains columns that correspond to the columns returned by the query—CustomerID, CompanyName, ContactName, and Phone.

Creating DataTable Objects and DataColumn Objects Using the Fill Method

Calling the Fill method in the previous example created a new DataTable in the DataSet. The new DataTable has columns named CustomerID, CompanyName, ContactName, and Phone, but the name of the DataTable object is Table, not Customers.

We already touched on this behavior when we discussed the DataAdapter object's TableMappings collection. We can add an item to this collection to inform the DataAdapter that we want to map the results of the query to a DataTable named Customers, as shown here:

Visual Basic .NET

'Same initialization of connection and query strings  'as in previous snippet Dim da As New OleDbDataAdapter(strSQL, strConn) da.TableMappings.Add("Table", "Customers") Dim ds As New DataSet() da.Fill(ds)

Visual C# .NET

//Same initialization of connection and query strings  //as in previous snippet OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.TableMappings.Add("Table", "Customers"); DataSet ds = new DataSet(); da.Fill(ds);

We'll cover the TableMappings collection in more detail shortly.

Using Overloaded Fill Methods

There's more than one way to use the DataAdapter object's Fill method to fill a DataSet. Let's look at the available Fill methods in groups.

Specifying the DataTable

The DataAdapter offers two Fill methods that allow you more control over the DataTable that it will use.

Rather than having to add an entry to the DataAdapter object's TableMappings collection, you can specify a table name in the Fill method:

DataAdapter.Fill(DataSet, "MyTableName")

I often use this Fill method to fill a table in my DataSet without having to use the TableMappings collection.

You can also specify a DataTable to the Fill method instead of a DataSet:

DataAdapter.Fill(DataTable)

This Fill method is useful when you've already created the DataTable you want to populate.

Paging with the DataAdapter object's Fill method

You've browsed through product catalogs on line that display items a page at a time. If the catalog has a hundred items, the Web site might display 20 of the items per page. The DataAdapter has a Fill method that you can use to fetch only a portion of the results of your query, as shown here:

DataAdapter.Fill(DataSet, intStartRecord, intNumRecords, "TableName")

Remember that the parameter for the starting record is zero-based. So, the following code snippet fetches the first 20 rows:

DataAdapter.Fill(DataSet, 0, 20, "Products")

It's also important to keep in mind that using this Fill method affects only which rows are stored in your DataSet. Let's say you're querying a table that contains 1000 rows and you're fetching this data in pages of 20 records each. The following call

DataAdapter.Fill(DataSet, 980, 20, "Products")

stores the last 20 rows from the query in your DataSet. But the actual query still returns 1000 rows. The DataAdapter simply discards the first 49 pages of data.

So, while this Fill method can make it easy to break your query into pages, it's not terribly efficient. There are more efficient (but more complex) ways to achieve paging with DataSet objects and DataReader objects, which we'll discuss in Chapter 14 when I explain how to build efficient Web applications.

Using a DataAdapter to fill a DataSet with the contents of a Recordset

The OLE DB .NET Data Provider has two Fill methods you can use to copy data from an ADO Recordset into an ADO.NET DataSet:

OleDbDataAdapter.Fill(DataSet, AdoRecordset, "TableName") OleDbDataAdapter.Fill(DataTable, AdoRecordset)

These methods can be helpful if want to use existing code or components that return ADO Recordset objects in your .NET application.

Opening and Closing Connections

In the code snippets that showed how to use the Fill method, you might have noticed a major difference between how the DataAdapter and the Command handle Connection objects. In Chapter 4, before calling one of the Command object's execute methods, we opened the Connection object associated with the Command. Otherwise, the Command would throw an exception. The DataAdapter has no such requirement.

If you call a DataAdapter object's Fill method and the SelectCommand property's Connection is closed, the DataAdapter will open the connection, submit the query, fetch the results, and then close the Connection. You might say that the DataAdapter is very tidy. It always returns the SelectCommand property's Connection to its initial state. If you open the Connection before calling the Fill method, the Connection will still be open afterwards.

The way the DataAdapter handles Connection objects can come in handy because you're not required to open your Connection. However, there are times when you should write code to open your Connection explicitly.

Let's say that as your application starts up, you use multiple DataAdapter objects to populate your DataSet with the results of a few queries. You've already learned how to use one of the DataAdapter object's constructors to force each DataAdapter to use the same Connection object. So, your code looks something like this:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim daCustomers, daOrders As OleDbDataAdapter daCustomers = New OleDbDataAdapter("SELECT ... FROM Customers", cn) daOrders = New OleDbDataAdapter("SELECT ... FROM Orders", cn) Dim ds As New DataSet() daCustomers.Fill(ds) daOrders.Fill(ds)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter daCustomers, daOrders; daCustomers = new OleDbDataAdapter("SELECT ... FROM Customers", cn); daOrders = new OleDbDataAdapter("SELECT ... FROM Orders", cn); ds = new DataSet(); daCustomers.Fill(ds); daOrders.Fill(ds);

You're actually opening and closing the Connection object twice, once each time you call a DataAdapter object's Fill method. To keep from opening and closing the Connection object, call the Connection object's Open method before you call the Fill method on the DataAdapter objects. If you want to close the Connection afterwards, you call the Close method as shown here:

cn.Open() daCustomers.Fill(ds) daOrders.Fill(ds) cn.Close()

Making Multiple Calls to the Fill Method

What do you do if you want to refresh the data in your DataSet? Maybe your DataAdapter fetches the contents of a table when your application starts up and you want to add a feature so that the user can see more timely data. The simple solution is to clear your DataSet (or DataTable) and then call the DataAdapter object's Fill method again.

Hypothetically speaking, let's say that you didn't realize that this was the best way to go. Instead, you just called the DataAdapter object's Fill method a second time, as shown here:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Customers") ... da.Fill(ds, "Customers")

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;"           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); ... da.Fill(ds, "Customers");

By calling the Fill method twice, you're asking the DataAdapter to execute the specified query and to store the results in the DataSet twice. The first call to the Fill method creates a new table within the DataSet called Customers. The second call to the Fill method copies the results of the query into that same table in the DataSet. Thus, each customer will appear twice in the DataSet.

With only this snippet of code, the DataAdapter has no way to know which customers are duplicates. Database administrators generally define primary keys on tables in a database. One of the benefits of this practice is that it prevents users from creating duplicate rows. The DataTable object has a PrimaryKey property. If the DataTable that the DataAdapter is filling has a primary key, the DataAdapter will use this key to determine which rows are duplicates.

For more information on setting the PrimaryKey property of a DataTable, see the section titled "Fetching Schema Information" later in the chapter, as well as the discussion of the DataTable object's PrimaryKey property in Chapter 6.

Getting back to the example at hand: if we define a primary key on the Customers DataTable in the DataSet before we call the DataAdapter object's Fill method the second time, the DataAdapter will locate the duplicate rows and discard the old values.

For example, say a customer's name and phone number have changed in the database. Calling the Fill method again will retrieve this new information. The DataAdapter will use the DataTable object's primary key to determine whether the DataTable already has a row for this particular customer. If the customer already exists within the DataTable, that row will be discarded and the newly retrieved information will be added to the DataTable. However, rows deleted from the database will not be removed from your DataTable.

Say that a deadbeat customer was in the database the first time you called the DataAdapter object's Fill method and the DataAdapter added that customer to your DataTable. Afterwards, someone realized that the customer was a deadbeat and purged the customer from your database. If you call the DataAdapter a second time, the DataAdapter will not find information for that customer in the results of your query but will not remove the row from your DataTable.

And now we've come full circle. If you need to refresh all of the data, you should clear the DataSet or DataTable and call the DataAdapter object's Fill method again. Using this methodology ensures that you will not have duplicate rows (even if you haven't defined a primary key for your DataTable) and you will not see rows in your DataSet that no longer exist in your database.

Mapping the Results of Your Query to Your DataSet

Earlier in the chapter, I described the role of the DataAdapter object's TableMappings collection. Now it's time to take a closer look at how to use this collection in code.

The DataAdapter Object's TableMappings Collection

The TableMappings collection controls how the DataAdapter maps your DataSet to your database. If you leave a DataAdapter object's TableMappings collection empty, call the Fill method, and supply a DataSet as a parameter without specifying a table name, the DataAdapter will assume that you want to work with a DataTable called Table.

The TableMappings property returns a DataTableMappingCollection object. This object contains a collection of DataTableMapping objects. Adding the following line of code adds a DataTableMapping object to the TableMappings collection to tell the DataAdapter that it should communicate with a DataTable called Employees instead:

DataAdapter.TableMappings.Add("Table", "Employees")

Once you've created a DataTableMapping object, you can create column mappings for the table. In an example earlier in the chapter, we mapped columns in the database named EmpID, LName, and FName to columns in the DataSet named EmployeeID, LastName, and FirstName using the following code:

Visual Basic .NET

Dim da As OleDbDataAdapter 'Initialize DataAdapter. Dim TblMap As DataTableMapping Dim ColMap As DataColumnMapping TblMap = da.TableMappings.Add("Table", "Employees") ColMap = TblMap.ColumnMappings.Add("EmpID", "EmployeeID") ColMap = TblMap.ColumnMappings.Add("LName", "LastName") ColMap = TblMap.ColumnMappings.Add("FName", "FirstName")

Visual C# .NET

OleDbDataAdapter da; //Initialize DataAdapter. DataTableMapping TblMap; DataColumnMapping ColMap; TblMap = da.TableMappings.Add("Table", "Employees"); ColMap = TblMap.ColumnMappings.Add("EmpID", "EmployeeID"); ColMap = TblMap.ColumnMappings.Add("LName", "LastName"); ColMap = TblMap.ColumnMappings.Add("FName", "FirstName");

Both the DataTableMappingCollection and DataColumnMappingCollection objects have an AddRange method that you can use to add an array of mappings to the collection in a single call, as shown here:

Visual Basic .NET

Dim da As New OleDbDataAdapter() 'Initialize DataAdapter. Dim TblMap As DataTableMapping Dim ColMapArray As DataColumnMapping() TblMap = da.TableMappings.Add("Table", "Employees") ColMapArray = New DataColumnMapping() _               {New DataColumnMapping("EmpID", "EmployeeID"), _                New DataColumnMapping("LName", "LastName"), _                New DataColumnMapping("FName", "FirstName")} TblMap.ColumnMappings.AddRange(ColMapArray)

Visual C# .NET

OleDbDataAdapter da = new OleDbDataAdapter(); //Initialize DataAdapter. DataTableMapping TblMap; DataColumnMapping[] ColMapArray; TblMap = da.TableMappings.Add("Table", "Employees"); ColMapArray = new DataColumnMapping[]                {new DataColumnMapping("EmpID", "EmployeeID"),                new DataColumnMapping("LName", "LastName"),                new DataColumnMapping("FName", "FirstName")}; TblMap.ColumnMappings.AddRange(ColMapArray);

The MissingMappingAction Property

You now understand how to populate a DataAdapter object's TableMappings collection with table and column information. However, you might have noticed that you don't have to supply this information. Earlier in the chapter, you saw examples that used a DataAdapter object's Fill method to create and fill a new DataTable even though the DataAdapter had no column mapping information.

In the majority of cases, developers use the same column names in the DataSet as in the database. The ADO.NET development team wisely realized that developers would not appreciate having to populate the DataAdapter object's TableMappings collection with identical database and DataSet column names in order to fetch data into their DataSet. When the DataAdapter examines the results of your query, if it finds a column that does not exist in its mappings collection, it checks its MissingMappingAction property to determine what to do with those columns.

The MissingMappingAction property accepts values from the MissingMappingAction enumeration in the System.Data namespace. By default, this property is set to Passthrough. When the MissingMappingAction property is set to this value, the DataAdapter maps missing columns in your results to columns with the same name in your DataSet. Setting this property to Ignore tells the DataAdapter to ignore columns that don't appear in the mappings collection. You can also set the MissingMappingAction property to Error, which will cause the DataAdapter to throw an exception if it detects a column in the results of your query that does not exist in the mappings collection.

Working with Batch Queries

All of the queries in the chapter so far have retrieved a single set of results. Some databases, such as Microsoft SQL Server, let you submit a batch of queries that return multiple result sets, as shown here:

SELECT CustomerID, CompanyName, ContactName, Phone        FROM Customers WHERE CustomerID = 'ALFKI'; SELECT OrderID, CustomerID, EmployeeID, OrderDate         FROM Orders WHERE CustomerID = 'ALFKI'

If you build a DataAdapter with the query shown above and fetch the results into a DataSet using the code

DataAdapter.Fill(DataSet)

you'll fetch the results into two DataTable objects within the DataSet. The results of the first portion of the query, which references the Customers table, will be stored in a DataTable named Table. The results of the second portion, which references the Orders table, will be stored in a DataTable named Table1. Chances are, you'll want to choose more descriptive names for your DataTable objects.

The DataAdapter object's TableMappings collection can contain multiple DataTableMapping objects. You can add entries to the collection to control the table names that the DataAdapter will use to store the results of the batch query. The following code fetches the results of the batch queries into two DataTable objects, named Customers and Orders, within the DataSet.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers WHERE CustomerID = 'ALFKI'; " & _          "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _           "FROM Orders WHERE CustomerID = 'ALFKI'" Dim da As New OleDbDataAdapter(strSQL, strConn) da.TableMappings.Add("Table", "Customers") da.TableMappings.Add("Table1", "Orders") Dim ds As New DataSet() da.Fill(ds)

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers WHERE CustomerID = 'ALFKI'; " +          "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +           "FROM Orders WHERE CustomerID = 'ALFKI'"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.TableMappings.Add("Table", "Customers"); da.TableMappings.Add("Table1", "Orders"); DataSet ds = new DataSet(); da.Fill(ds);

Retrieving Rows from a Stored Procedure

If you have stored procedures that return result sets, you can use a DataAdapter to fetch those results into a DataSet or DataTable. Say you have the following stored procedure definition:

CREATE PROCEDURE GetAllCustomers AS     SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers RETURN

You can fetch the results of the stored procedure call into a DataSet by using either of the following query strings:

{CALL GetAllCustomers}

or

EXEC GetAllCustomers

You can also base the DataAdapter on a Command with a CommandType of StoredProcedure. For more information on using Command objects with CommandType values other than the default, see the discussion of the Command object's CommandType property in Chapter 4.

Oracle Stored Procedures

Oracle stored procedures cannot return rows from a query in the way that SQL Server stored procedures can. Oracle stored procedures can return data only through output parameters. However, the Microsoft OLE DB Provider for Oracle and the Microsoft ODBC Driver for Oracle allow you to call Oracle stored procedures and fetch the results of a query through output parameters. This feature is documented in various articles in the Microsoft Knowledge Base. A query string would follow this syntax:

{CALL PackageName.ProcName (?, {resultset 20, OutParam1,                                  OutParam2, ... })}

Fetching Schema Information

The DataTable object, which we'll discuss in detail in the following chapter, is designed to enforce constraints on your data such as a primary key, maximum length of string fields, and nullability constraints. Fetching this information at run time can be costly, and in many cases developers have no need to retrieve this information. So, by default the DataAdapter does not fetch this information. However, if you encounter a situation in which you're willing to pay the performance penalty to retrieve schema information about your results, there are a couple key features of the DataAdapter that you can use: the MissingSchemaAction property and the FillSchema method. (Yes, I know that's an absolutely terrible pun.)

The MissingSchemaAction Property

You might have noticed that, so far, all of the examples that use the DataAdapter object's Fill method use DataSet objects and DataTable objects that contain no schema information. By default, the DataAdapter will add columns to store the results of your query if those columns do not already exist in your DataSet or DataTable. This behavior is governed by the MissingSchemaAction property.

This property accepts values from the MissingSchemaAction enumeration in the System.Data namespace. The default value for this property is Add. As with MissingMappingAction, you can ignore missing columns by setting the property to Ignore or throw an exception in such circumstances by setting the property to Error.

There's another value in the MissingSchemaAction enumeration: AddWithKey. The name of this value is slightly misleading. If you set the property to this value and the DataAdapter encounters a column that does not exist in your DataSet or DataTable, the DataAdapter adds the column and sets two additional schema attributes of the property: MaxLength and AllowDBNull. If the DataTable does not yet exist or does not contain any columns, this value also causes the DataAdapter to query the database for primary key information.

The FillSchema Method

The DataAdapter also has a FillSchema method that you can use to fetch only schema information into your DataSet or DataTable. The FillSchema method's signatures mirror the basic Fill signatures. You can supply a DataSet, a DataTable, or a DataSet and a table name in the FillSchema method.

Each FillSchema method also requires a value from the SchemaType attribute: Mapped or Source. The value you specify in this parameter determines whether the DataAdapter will apply the settings in its TableMappings collection to the results of the query. If you call the FillSchema method and use Source as the SchemaType, the DataAdapter will use the column names that the query returns. Using Mapped as the SchemaType will cause the DataAdapter to apply the settings in its TableMappings to the columns returned by the query.

FillSchema will set the AutoIncrement, AllowDBNull, and MaxLength properties on the columns returned and will also create a primary key on the resulting DataTable if the database indicates that a column or set of columns represents a primary or unique key.