15.5 Retrieving Updated Values from the Data Source

There are three techniques that can retrieve updated data from the data source.

The first technique is to use a batch query to return the new value using a query after the update command has executed. Example 15-3 demonstrates this for an AutoIncrement column by creating a batch insert statement containing the statements:

    "SET @OrderID = Scope_Identity();" +
    "SELECT @OrderID OrderID;";

If the row also contains a timestamp column named rowversion, the following code can be used with the insert statement to retrieve the new timestamp value:

    "SET @OrderID=Scope_Identity();" + 
    "SELECT @OrderID OrderId, rowversion WHERE OrderID = @OrderID;";

This technique requires that the UpdatedRowSource property update commands be set to either Both or FirstReturnedRecord.

The second technique uses output parameters to retrieve updated data. Example 15-3 demonstrates the use of this method with an AutoIncrement column by creating an output parameter for the OrderID on the InsertCommand:

params.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
params.Direction = ParameterDirection.Output;

This technique requires that the UpdatedRowSource property for the update command be set to either Both or OutputParameters.

The third technique handles the DataAdapter RowUpdated event. An event handler is first attached to the data adapter:

da.RowUpdated += new SqlRowUpdateEventHandler(da_RowUpdated);

The event handler retrieves the new AutoIncrement value and stores it in the OrderID field if the update was a successful insert. AcceptChanges( ) is called so that the row doesn't appear to be modified:

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    if(e.Status == UpdateStatus.Continue &&
        e.StatementType == StatementType.Insert)
    {
        e.Row["OrderId"] = (Int32)cmdIdentity.ExecuteScalar();
        e.Row.AcceptChanges();
    }
}

Example 15-3 demonstrates the three techniques for returning updated data from the data source described earlier. All three techniques have been included in the same example in the interest of saving space. Normally, only one technique is used.

Example 15-3. Returning updated data

// following variable has class scope
private SqlCommand insertCommand;

// ...

// the SQL statements, select and update
String sqlSelect = "SELECT OrderID, CustomerID, EmployeeID, " +
    "OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, " +
    "ShipName, ShipAddress, ShipCity, ShipRegion, " +
    "ShipPostalCode, ShipCountry FROM Orders";
String sqlDelete = "DELETE FROM Orders WHERE OrderID=@OrderID";
String sqlInsert = "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);" +
    "SET @OrderID=Scope_Identity();" +
    "SELECT @OrderID OrderID;";
String sqlUpdate = "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";

// build the connection
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
SqlConnection conn = new SqlConnection(connString);

// create command objects using SQL statements
// UpdatedRowSource property of Command objects defaults to Both
SqlCommand selectCmd = new SqlCommand(sqlSelect, conn);
SqlCommand deleteCommand = new SqlCommand(sqlDelete, conn);
insertCommand = new SqlCommand(sqlInsert, conn);
SqlCommand updateCommand = new SqlCommand(sqlUpdate, conn);

// set up the parameters
SqlParameterCollection cparams;

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

// insert command parameters
cparams = insertCommand.Parameters;
SqlParameter orderid =
  cparams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
orderid.Direction = ParameterDirection.Output;
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.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 and an event handler for after row inserts
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn);
da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);

// load all of Orders data from the data source into the DataSet
DataSet ds = new DataSet();
da.Fill(ds, "Orders");

// ... code to modify the data in the DataSet

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

//...

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
  if(e.Status == UpdateStatus.Continue &&
    e.StatementType == StatementType.Insert)
  {
    e.Row["OrderId"] = (Int32)insertCommand.ExecuteScalar();
    e.Row.AcceptChanges();
  }
}