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"); 


ADO. NET in a Nutshell
ADO.NET in a Nutshell
ISBN: 0596003617
EAN: 2147483647
Year: 2005
Pages: 415

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net