14.2 Retrieving Data from the Data Source

The Fill( ) method of the DataAdapter retrieves data from the data source into a DataSet or a DataTable. When the Fill( ) method for the data adapter is called, the select statement defined in the SelectCommand is executed against the data source and retrieved into a DataSet or DataTable. In addition to retrieving data, the Fill( ) method retrieves schema information for columns that don't exist. This schema that it retrieves from the data source is limited to the name and data type of the column. If more schema information is required, the FillSchema( ) method, described later in this chapter, can be used. The following example shows how to use the Fill( ) method to retrieve data from the Orders table in the Northwind database:

// connection string and the select statement
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String selectSQL = "SELECT * FROM Orders";

SqlDataAdapter da = new SqlDataAdapter(selectSQL, connString);

// create a new DataSet to receive the data
DataSet ds = new DataSet();

// read all of the data from the orders table and loads it into the
// Orders table in the DataSet
da.Fill(ds, "Orders");

A DataTable can also be filled similarly:

// ... code to create the data adapter, as above

// create the DataTable to retrieve the data
DataTable dt = new DataTable("Orders");

// use the data adapter to load the data into the table Orders
da.Fill(dt);

Notice that a connection object is never opened and closed for the data adapter. If the connection for the data adapter isn't open, the DataAdapter opens and closes it as required. If the connection is already open, the DataAdapter leaves the connection open.

The same set of records can be retrieved more efficiently using a stored procedure. Stored procedures have a number of benefits over SQL statements:

• Stored procedures allow business logic for common tasks to be consistently implemented across applications. The stored procedure to perform a task can be designed, coded, and tested. It can then be made available to any client that needs to perform the task. The SQL statements to perform the task need to be changed in only one place if the underlying business logic changes. If the parameters for the stored procedure don't change, applications using the stored procedure will not even need to be recompiled.

• Stored procedures can improve performance in situations where a group of SQL statements are executed together with conditional logic. A stored procedure allows a single execution plan to be prepared for the SQL statements together with the conditional logic. Rather than having the client submit a series of SQL statements based on client-side conditional logic, both the SQL statements and conditional logic are executed on the server, requiring only one round trip. Additionally, when a stored procedure is executed, only the parameters need to be transmitted to the server rather than the entire SQL statement.

• Stored procedures are more secure. Users can be granted permission to execute stored procedures that perform required business functions rather than having direct access to the database tables.

• Stored procedures provide a layer of abstraction for the data, making performing business function more intuitive and, at the same time, hiding database implementation from the users.

SQL Server Stored Procedures In SQL Server Version 6.5 and earlier, stored procedures were more efficient than T-SQL statements because a partially compiled execution plan for the stored procedure was stored in a system table when the stored procedure was created. SQL Server only had to optimize the stored plan. Additionally, the fully compiled plan was stored in the procedure cache so that subsequent executions of the stored procedure could use the precompiled execution plan. SQL Server Version 7.0 and later doesn't store a partially compiled execution plan for stored procedures. Stored procedures and T-SQL statements are compiled at execution time, and these execution plans are then stored in the procedure cache and reused for subsequent statements. Extending execution plan reuse to all SQL statements reduces the relative performance benefit of stored procedures compared to T-SQL statements.

The following example shows the stored procedure used to select records from the Orders table in the Northwind database. The stored procedure takes a CustomerID parameter that results in only orders for that customer being retrieved.

// the stored procedure
CREATE PROCEDURE GetOrders
    @CustomerID nchar(5)
AS
    SET NOCOUNT ON

    SELECT * FROM Orders WHERE CustomerId=@CustomerID

    RETURN

The code to retrieve the data using the stored procedure has some differences compared with the code using the SQL statements directly. The CommandText property of the SelectCommand is set to the name of the stored procedure rather than to a SQL statement. The CommandType is set to StoredProcedure rather than specifying or accepting the default value of Text. The following example illustrates retrieving orders for a specific customer using a stored procedure:

// connection string and the stored procedure
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String selectSql = "GetOrders";

// create a DataSet to receive the data
DataSet ds = new DataSet();

SqlConnection conn = new SqlConnection(connString);

// create a command object based on the stored procedure
SqlCommand selectCmd = new SqlCommand(selectSql, conn);
selectCmd.CommandType = CommandType.StoredProcedure;

// create and set the CustomerID parameter for the stored procedure
selectCmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
selectCmd.Parameters["@CustomerID"].Value = "VINET";

// create and fill the DataSet
SqlDataAdapter da = new SqlDataAdapter(selectCmd);
da.Fill(ds, "Orders");

The same result could be accomplished with a parameterized query, as shown in the following example:

// connection string and parameterized query
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String selectSql = "SELECT * FROM Orders WHERE CustomerID=@CustomerID";

DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(connString);

// create a command object based on the SQL select statement
SqlCommand selectCmd = new SqlCommand(selectSql, conn);

// create and set the CustomerID parameter for the select statement
selectCmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
selectCmd.Parameters["@CustomerID"].Value = "VINET";

// create and fill the DataSet
SqlDataAdapter da = new SqlDataAdapter(selectCmd);
da.Fill(ds, "Orders");

There are several options available to load more than one table into the same DataSet using a DataAdapter:

• The Fill( ) method can be called several times on the same DataAdapter, specifying a different DataTable in the same DataSet. The SelectCommand is modified to select the records for a different table each time Fill( ) is called.

• Multiple DataAdapter objects, each returning one table, can be created. Fill( ) is called on each DataAdapter, specifying the appropriate DataTable in the same DataSet.

• Either a batch query or a stored procedure that returns multiple result sets can be used.

In the last option, the DataAdapter automatically creates the required tables and assigns them the default names Table, Table1, Table2, if a table name isn't specified. If a table name is specified, for example MyTable, the DataAdapter names the tables MyTable, MyTable1, MyTable2, and so on. The tables can be renamed after the fill, or table mapping can map the automatically generated names to names of the underlying tables in the DataSet. The following example shows how to use a batch query with a DataAdapter to create two tables in a DataSet:

// connection string and batch query
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String selectSql = "SELECT * FROM Customers;" + 
    " SELECT * FROM Orders";

// create the data adapter
SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);

The DataSet is filled with two tables named Table and Table1, respectively, containing data from the Customers and the Orders tables in data source.

Finally, the DataAdapter provides an overloaded Fill( ) method that retrieves a subset of rows from the query and loads them into the DataSet. The starting record and maximum number of records are specified to define the subset. For example, the following code statement retrieves the first 10 records and inserts them into a DataTable named Categories:

da.Fill(ds, 0, 10, "Categories");

It is important to realize that this method actually performs the original query and retrieves the full set of results. It then discards those records that aren't in the specified range. As a result, this approach performs poorly when selecting from large result sets. A better approach is to limit the amount of data that must be transferred over the network and the work that must be performed by the data source by fine-tuning a SQL SELECT statement using a TOP n or WHERE clause.