Name

UpdatedRowSource

Synopsis

UpdateRowSource rowSource = Command.UpdateRowSource;

Command.UpdateRowSource = rowSource;

When this Command is used to commit changes with a DataAdapter, the UpdateRowSource property defines how the results from the Command will be applied to the original DataRow. This is primarily useful if your Command is invoking a stored procedure, and the stored procedure returns some type of generated information (such as a new value for a unique identifier column).

To specify how this returned information will be applied, use one of the values from the UpdateRowSource enumeration, as shown in Table 19-3.

Table 19-3. CommandType values

Value	Description
None	All output parameters and any returned rows are ignored.
FirstReturnedRecord	The column values from the first returned record are applied to the source DataRow. In other words, the stored procedure uses a SELECT statement to return the changed row after performing the update.
Parameters	The information from the output parameters is applied to the source DataRow, based on the Parameter.SourceColumn property.
Both	Information from the output parameters and the first returned record is applied to the source DataRow. This is the default. Note that it is uncommon for a stored procedure to return both types of information; the Both value simply ensures that the source information is updated no matter which approach you use.

Example

The following code snippet shows a Command that calls a CategoryAdd stored procedure when the DataAdapter inserts a new category record. This stored procedure returns an output parameter with the new unique CategoryID, which the Command maps to the source DataRow. For a full example of this technique, refer to Chapter 15.

// Create the command.

SqlCommand cmdInsert = new SqlCommand("CategoryAdd", con);

cmdInsert.CommandType = CommandType.StoredProcedure;

cmdInsert.UpdatedRowSource = UpdateRowSource.OutputParameters;



SqlParameter param;



// Add an input parameter to the command.

param = cmdInsert.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);

param.SourceColumn = "CategoryName";

param.SourceVersion = DataRowVersion.Current;



// Add an output parameter to the command. The value returned by this 

// parameter will be applied to the source DataRow once the insertion is

// complete.

param = cmdInsert.Parameters.Add("@CategoryID", SqlDbType.Int);

param.SourceColumn = "CategoryID";

param.SourceVersion = DataRowVersion.Original;

param.Direction = ParameterDirection.Output;



// Assign the command to the DataAdapter.

adapter.InsertCommand = cmdInsert;

Notes

You can also use this technique to map the information from a stored procedure return value. Remember, the return value is represented by a Parameter object with a Direction of ParameterDirection.ReturnValue. If you set the Command.UpdateRowSource property to Both or Parameters, this value updates the DataRow.

The UpdateRowSource property is almost always used with a stored procedure. Ordinary SQL statements simply returns the number of affected rows, not the new row. Similarly, parameterized queries use only input parameters, not output parameters. To use the UpdateRowSource property effectively, you must add stored procedure code to return the updated information you need.