15.3 Updating a Data Source Using Custom Logic

The CommandBuilder provides an extremely convenient way to create the required Command objects, but it has definite limitations. Here are some reasons to avoid the CommandBuilder and use your own custom updating logic:

Stored procedures

Most significant applications use stored procedures because of their benefits, which include maintainability, security, and performance over SQL statements.

Table joins

In some cases, table joins are needed to retrieve aggregate information. However, even if you edit only fields from a single table, the CommandBuilder can't automatically generate the Command objects.

More flexible concurrency handling

The UPDATE and DELETE statements generated by the CommandBuilder search the data source for a row that matches all fields in the original row exactly. If any original values have changed, the update will fail for that row. In some cases, this approach isn't ideal.

There is only one real difference between using the CommandBuilder and custom update logic. The CommandBuilder generates the DeleteCommand, InsertCommand, and UpdateCommand objects used by the DataAdapter to reconcile changes made to the DataSet with the data source. With custom update logic, those update objects have to be defined.

The SourceColumn and SourceVersion properties of the Parameter object bind associate a Parameter with a DataColumn. The DataAdapter uses these properties to determine the source of the values within the DataRow; these values are loaded into the Parameter for the appropriate update Command for the DataRow before the update is performed for the row. The default value for SourceVersion is the Current row, so this value needs to be set only when a different version is required. The following two examples illustrate the effect of the SourceColumn and SourceVersion properties. The first example maps the CustomerID column from the Current version of the DataRow to the Parameter named @CustomerID:

params.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

The second example maps the OrderID column from the Original version of the DataRow to the Parameter named @OrderID:

params.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
params["@OrderID"].SourceVersion = DataRowVersion.Original;

It's important to understand that this mapping occurs each time a row is updated and is defined separately for each update Command object. The actual Command object that updates a particular row when the Update( ) method is called is based on the DataRowState of that row.

The update Command objects can be based on parameterized SQL statements, as is demonstrated by CommandBuilder, or on stored procedures, as is more commonly the case. One obvious drawback to using SQL statements is that updated values in the data source can't be returned back to the DataSet. Refreshing the DataSet after an update using stored procedures is discussed in more detail later in this chapter.

The following example uses the Orders table from Northwind database to demonstrate how to use stored procedures to define the update Command objects and then how to use these custom update Command objects. The stored procedures are presented first followed by the code that uses these stored procedures.

Example 15-1 uses the stored procedures that delete, select, insert and update the data source.

Example 15-1. Stored procedures for commands

-- stored procedure for DeleteCommand
CREATE PROCEDURE DeleteOrders
    @OrderID int
AS
    SET NOCOUNT ON
    
    delete
    from
        Orders
    where
        OrderID=@OrderID
        
    return
GO

--stored procedure for SelectCommand
CREATE PROCEDURE GetOrders
AS
    SET NOCOUNT ON
    
    select
        OrderID,
        CustomerID,
        EmployeeID,
        OrderDate,
        RequiredDate,
        ShippedDate,
        ShipVia,
        Freight,
        ShipName,
        ShipAddress,
        ShipCity,
        ShipRegion,
        ShipPostalCode,
        ShipCountry
    from
        Orders

    return
GO

--stored procedure for InsertCommand
CREATE PROCEDURE InsertOrders
    @OrderID int output,
    @CustomerID nchar(5),
    @EmployeeID int,
    @OrderDate datetime,
    @RequiredDate datetime,
    @ShippedDate datetime,
    @ShipVia int,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipAddress nvarchar(60),
    @ShipCity nvarchar(15),
    @ShipRegion nvarchar(15),
    @ShipPostalCode nvarchar(10),
    @ShipCountry nvarchar(15)
AS
    SET NOCOUNT ON
        
    insert Orders(
        CustomerID,
        EmployeeID,
        OrderDate,
        RequiredDate,
        ShippedDate,
        ShipVia,
        Freight,
        ShipName,
        ShipAddress,
        ShipCity,
        ShipRegion,
        ShipPostalCode,
        ShipCountry)
    values (
        @CustomerID,
        @EmployeeID,
        @OrderDate,
        @RequiredDate,
        @ShippedDate,
        @ShipVia,
        @Freight,
        @ShipName,
        @ShipAddress,
        @ShipCity,
        @ShipRegion,
        @ShipPostalCode,
        @ShipCountry)
    
    if @@rowcount=0
        return 1
    
    set @OrderID=Scope_Identity()
    
    select @OrderId OrderId

    return
GO

--stored procedure for UpdateCommand
CREATE PROCEDURE UpdateOrders
    @OrderID int,
    @CustomerID nchar(5),
    @EmployeeID int,
    @OrderDate datetime,
    @RequiredDate datetime,
    @ShippedDate datetime,
    @ShipVia int,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipAddress nvarchar(60),
    @ShipCity nvarchar(15),
    @ShipRegion nvarchar(15),
    @ShipPostalCode nvarchar(10),
    @ShipCountry nvarchar(15)
AS
    SET NOCOUNT ON
        
    update
        Orders
    set
        CustomerID = @CustomerID,
        EmployeeID = @EmployeeID,
        OrderDate = @OrderDate,
        RequiredDate = @RequiredDate,
        ShippedDate = @ShippedDate,
        ShipVia = @ShipVia,
        Freight = @Freight,
        ShipName = @ShipName,
        ShipAddress = @ShipAddress,
        ShipCity = @ShipCity,
        ShipRegion = @ShipRegion,
        ShipPostalCode = @ShipPostalCode,
        ShipCountry = @ShipCountry
    where
        OrderID = @OrderID
    
    if @@rowcount = 0
        return 1
    
    return
GO

Example 15-2 demonstrates how to create the update Command objects that retrieve and update the data, configure the stored procedure parameters, create the DataAdapter, and assign the command objects to the data adapter.

Example 15-2. Creating update objects

// connection and select command strings
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
SqlConnection conn = new SqlConnection(connString);

// create command objects using stored procedures
SqlCommand selectCommand = new SqlCommand("GetOrders", conn);
selectCommand.CommandType = CommandType.StoredProcedure;
SqlCommand deleteCommand = new SqlCommand("DeleteOrders", conn);
deleteCommand.CommandType = CommandType.StoredProcedure;
SqlCommand insertCommand = new SqlCommand("InsertOrders", conn);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlCommand updateCommand = new SqlCommand("UpdateOrders", conn);
updateCommand.CommandType = CommandType.StoredProcedure;

// set up the parameters
SqlParameterCollection cparams;

// delete command parameters
cparams=deleteCommand.Parameters;
cparams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
cparams["@OrderID"].SourceVersion=DataRowVersion.Original;

// insert command parameters
cparams = insertCommand.Parameters;
cparams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
cparams["@OrderID"].Direction = ParameterDirection.Output;
cparams["@OrderID"].SourceVersion = DataRowVersion.Original;
cparams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cparams.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
cparams.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
cparams.Add("@RequiredDate", SqlDbType.DateTime, 0, "RequiredDate");
cparams.Add("@ShippedDate", SqlDbType.DateTime, 0, "ShippedDate");
cparams.Add("@ShipVia", SqlDbType.Int, 0, "ShipVia");
cparams.Add("@Freight", SqlDbType.Money, 0, "Freight");
cparams.Add("@ShipName", SqlDbType.NVarChar, 40, "ShipName");
cparams.Add("@ShipAddress", SqlDbType.NVarChar, 60, "ShipAddress");
cparams.Add("@ShipCity", SqlDbType.NVarChar, 15, "ShipCity");
cparams.Add("@ShipRegion", SqlDbType.NVarChar, 15, "ShipRegion");
cparams.Add("@ShipPostalCode", SqlDbType.NVarChar, 10, "ShipPostalCode");
cparams.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry");

// update command parameters
cparams = updateCommand.Parameters;
cparams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
cparams["@OrderID"].SourceVersion=DataRowVersion.Original;
cparams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cparams.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
cparams.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
cparams.Add("@RequiredDate", SqlDbType.DateTime, 0, "RequiredDate");
cparams.Add("@ShippedDate", SqlDbType.DateTime, 0, "ShippedDate");
cparams.Add("@ShipVia", SqlDbType.Int, 0, "ShipVia");
cparams.Add("@Freight", SqlDbType.Money, 0, "Freight");
cparams.Add("@ShipName", SqlDbType.NVarChar, 40, "ShipName");
cparams.Add("@ShipAddress", SqlDbType.NVarChar, 60, "ShipAddress");
cparams.Add("@ShipCity", SqlDbType.NVarChar, 15, "ShipCity");
cparams.Add("@ShipRegion", SqlDbType.NVarChar, 15, "ShipRegion");
cparams.Add("@ShipPostalCode", SqlDbType.NVarChar, 10, "ShipPostalCode");
cparams.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry");

// create the data adapter
SqlDataAdapter da = new SqlDataAdapter(selectCommand.CommandText, conn);

// assign the custom update logic to the DataAdapter
da.DeleteCommand = deleteCommand;
da.InsertCommand = insertCommand;
da.UpdateCommand = updateCommand;

// create a new DataSet to receive the data and load the data
DataSet ds = new DataSet();
da.Fill(ds, "Orders");

// ... code to modify the DataSet

// update the data source using the custom update logic
da.Update(ds, "Orders");