4. Using Stored Procedures with DataSet Objects
Stored procedures can be specified in the CommandText property of the command objects that reside within a data adapter; that is, the SELECT, INSERT, UPDATE, and DELETE
commands.
The purpose of the data adapter is to move data
between the database and the data set, and stored procedures are an
excellent way to retrieve and update data (sometimes the only way, as
when the updating requirements are very complex or access to the
database is channeled through stored procedures for security reasons).
Thus, it is beneficial to know how to use command objects based on
stored procedures with the data adapter.
Using a stored procedure in a SELECT command is quite simple; any command object based on a stored procedure that consists of a single SELECT statement can be used as the data adapter’s SelectCommand property. As long as you have permission to execute the procedure and set the parameter values before calling the adapter’s Fill method, the procedure can be used to select the rows that are then loaded into the receiving data table.
Using a command object based on a stored procedure for the adapter’s INSERT, UPDATE, and DELETE
commands requires an additional piece of information and an extra step.
The extra information is required during the creation of each parameter
because the value of each parameter must now come from a field in the
data table whose data is being updated to the server. Because the data
adapter updates one row at a time, the values from each row are loaded
into the parameters, the procedure is executed, and then the process
continues with the next row. You must specify which column goes with
which parameter.
The extra step comes from the fact that the
values assigned to the parameters come from the “new” version of the
row, which is what you would expect and want. But the parameter(s) that
comprise the primary key of the row, which are the values to be used to
specify the database row(s) to be updated, must come from the “old”
version of the rows. Because primary key values should not be modified,
this is, hopefully, a moot point. However, you need to specify it just
in case.
The
changes in the bound control data are automatically propagated to the
underlying data table. We discussed the added code necessary for the
data adapter to propagate the changes from the data table back to the
database.
Now we create a very simple stored procedure, shown in Listing 5, which modifies the category name for a given category ID.
Listing 5. The Stored Procedure for Category Modification
CREATE PROCEDURE procModifyCategoryInfo
@CategoryID int = null,
@CategoryName nvarchar(40) = null
AS
BEGIN
IF @CategoryID is null
BEGIN
RAISERROR('Category ID not supplied.',10,1)
RETURN 1
END
IF not exists (SELECT *
FROM Categories
WHERE CategoryID = @CategoryID)
BEGIN
RAISERROR('Category ID not on file.',10,1)
RETURN 1
END
BEGIN TRANSACTION
IF @CategoryName is not null
UPDATE Categories
SET CategoryName = @CategoryName
WHERE CategoryID = @CategoryID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Unable to update Categories table.',10,1)
RETURN 1
END
COMMIT TRANSACTION
RETURN 0
END
|
This procedure requires two parameters: the
category ID of the category to be updated and the new name to be given
to that category. Both of these values must come from the rows of the
data table.
The code that builds the UpdateCommand object for use by the data adapter is shown in Listing 6. This code must execute before the Update method executes; it can execute either before or after the Fill method executes.
Listing 6. Building a Command Object That Uses a Stored Procedure
// Initialize the command (including
// creating the parameters).
SqlCommand cmndDB = new SqlCommand();
cmndDB.CommandText = "procModifyCategoryInfo";
cmndDB.CommandType = CommandType.StoredProcedure;
cmndDB.Connection =
daptCategories.SelectCommand.Connection;
cmndDB.Parameters.Add(
new SqlParameter(
"@RETURN_VALUE", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false,
((System.Byte)(0)), ((System.Byte)(0)),
"", DataRowVersion.Current, null));
cmndDB.Parameters.Add(
new SqlParameter(
"@CategoryID", SqlDbType.Int, 4, "CategoryID"));
cmndDB.Parameters.Add(
new SqlParameter(
"@CategoryName", SqlDbType.NVarChar, 40, "CategoryName"));
cmndDB.Parameters["@CategoryID"].SourceVersion =
DataRowVersion.Original;
|
In the next-to-last statement in this code, the fourth parameter specifies the column of the data table with the value for the "@CategoryName" command object parameter.
The last statement in Listing 6.16
specifies that the primary key value should be the value that was
loaded into the data table from the database, not any new value that is
now in the data table.
We have discussed the remaining code before; most
of it was generated in a Windows Application project with a
drag-and-drop operation.
Using stored procedures in a command object
provides you a number of benefits. First, it frees you from the details
of the SQL code (especially if someone else writes the stored procedure
for you). Second, it provides for a more efficient execution of code on
the server side.
As each stored procedure executes, your program
receives the stored procedure’s return value, which your program can use
to decide whether the procedure executed successfully. Procedure-based
command objects are helpful whether they are being used by a data
adapter to move data between the database and ADO.NET objects or by your
application to operate directly on a database without the involvement
of ADO.NET classes.
5. DataSet Objects and Concurrency
Unlike SQL Server CE, SQL Server provides concurrency protection; that is, if data within a row of a data table is modified on the server (probably by some other user) after the Fill method has executed and before the Update method executes, the update of that row fails. After the Update
method has completed, your application can determine which rows did not
successfully update and retrieve their new values from the database.
There is no automatic response for this situation. Your application must
decide whether to resubmit its values, submit revised values, notify
the user, forget it, and so on.
6. Using Multi SELECT Stored Procedures
When doing data modifications via data sets, it is very advantageous to have each adapter’s command contain just one SELECT statement. As the preceding paragraphs mentioned, one DataAdapter
should map to one database table. However, if you are retrieving data
for display only, this restriction does not apply; and with good reason.
Often you have a stored procedure that executes several SELECT
statements to gather related information about a certain subject. Such a
stored procedure might gather information about an employee, including
that employee’s orders, the customers for those orders, the line items,
and the products for those line items. Listing 7 shows a simplified version of such a stored procedure.
Listing 7. A Multi SELECT Stored Procedure
CREATE PROCEDURE dbo.procGetEmployeeII
@EmployeeID int
AS
BEGIN
SELECT *
FROM dbo.Employees
WHERE EmployeeID = @EmployeeID
SELECT *
FROM dbo.Customers
WHERE CustomerID IN
( SELECT CustomerID
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID )
SELECT *
FROM dbo.Products
WHERE ProductID IN
( SELECT ProductID
FROM dbo.[Order Details]
WHERE OrderID IN
( SELECT OrderID
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID ))
SELECT *
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID
SELECT *
FROM dbo.[Order Details]
WHERE OrderID IN
( SELECT OrderID
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID )
END
|
As
before, we’ll use a data adapter object to retrieve the data from the
stored procedure and place it in a data set. But this time we have five SELECT
statements in our stored procedure. Therefore, our adapter will need to
generate five tables in our data set and load each with the results
from a different SELECT statement. Before executing the adapter’s Fill
method, we will need to tell the adapter what name to give each table
in the data set. We do not need to tell the adapter what data source
tables were used, as the adapter knows each incoming result set within
the output of a multi SELECT command by the names Table, Table1, Table2, and so on, regardless of the underlying database table names.
We match the name we want the adapter to give each data table to the incoming result set’s—Table, Table1, Table2, and so on—by adding entries to the adapter’s TableMappings collection. In our case, five entries are required; as shown in the following code snippet:
dapt.TableMappings.Add("Table", "Employees");
dapt.TableMappings.Add("Table1", "Customers");
dapt.TableMappings.Add("Table2", "Products");
dapt.TableMappings.Add("Table3", "Orders");
dapt.TableMappings.Add("Table4", "Details");
The complete code necessary to execute the stored
procedure and populate the tables, including the preceding snippet,
appears in Listing 8. In all, it does the following:
1. | Creates an empty data set
|
2. | Creates the connection, command, and adapter objects
|
3. | Sets the TableMappings collection property
|
4. | Retrieves the data and loads it into the data set
|
Listing 8. Client-Side Code to Load a Multitable Data Set
DataSet dsetEmployee = new DataSet();
SqlDataAdapter dapt;
SqlConnection conn =
new SqlConnection(connectionString);
SqlCommand cmnd = conn.CreateCommand();
cmnd.Parameters.AddWithValue("@Return", 0);
cmnd.Parameters[0].Direction =
ParameterDirection.ReturnValue;
dapt = new SqlDataAdapter(cmnd);
dapt.SelectCommand.CommandType =
CommandType.StoredProcedure;
dapt.SelectCommand.CommandText =
"dbo.procGetEmployee";
dapt.SelectCommand.Parameters
.AddWithValue("@EmployeeID", employeeID);
dapt.TableMappings.Add("Table", "Employees");
dapt.TableMappings.Add("Table1", "Customers");
dapt.TableMappings.Add("Table2", "Products");
dapt.TableMappings.Add("Table3", "Orders");
dapt.TableMappings.Add("Table4", "Details");
dapt.Fill (dsetEmployee);
|
The connectionString and employeeID are passed to into the function containing the code.
When the preceding Fill
statement has completed its execution, we have our fully loaded,
five-table data set, loaded within a single round trip to the server
machine.
