So how do I setup a DataSetCommand to use a parameterized query or stored procedure for the update method? Basically you do the exact same thing you would do to get these types of statements to work with a Command Object (see section 4.2.5 Using stored procedures with a Command). The only additional thing that you need to consider is the rowversion that you want to use in each parameter.
In the three strings below, we establish the parameterized queries to do the insert, update and delete actions for an ADODataSetCommand (that why we have ? for parameter markers).
string insertSQL = "INSERT INTO [Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; string updateSQL = "UPDATE [Customers] SET [CustomerID] = ?, [CompanyName] = ?, [ContactName] = ?, [ContactTitle] = ?, [Address] = ?, [City] = ?, [Region] = ?, [PostalCode] = ?, [Country] = ?, [Phone] = ?, [Fax] = ? WHERE [CustomerID] = ? "; string deleteSQL = "DELETE FROM [Customers] WHERE [CustomerID] = ? ";
When I build the parameters collection we need to set six items on each of the parameters. The first three we can do through the either the Parameters.Add method or the Parameter constructor. In the line below we set the parameter's name "CustomerID" in the parameters collection; the data type and the size.
workParam = workCommand.Parameters.Add("CustomerID", ADODBType.WChar, 5);
For intrinsic type like integer we don't need to include the size or simply set to the default size
workParam = workCommand.Parameters.Add("FldItemID", ADODBType.Integer, 4);
The item is to set the direction of the parameter.
workParam.Direction = ParameterDirection.Input;
The Parameter Direction enum support the following values:
public enum ParameterDirection { Input = 1, Output = 2, InputOutput = 3, ReturnValue = 6 }
The last two items that we need to set are SourceColumn and SourceVersion. SourceColumn tells the DataSetCommand which column in the table is going to provide its value. To set this, you assign the name of the column as its held in the DataSet's table.
workParam.SourceColumn = "CustomerID";
The SourceVersion tells the DataSetCommand which version of the it should actually pull the value from. The options for this are:
public enum DataRowVersion { Default = 0, Original = 1, Current = 2, Proposed = 3, }
An example of how these values fit in, is the update statement defined above. You'll notice that the "CustomerID" column is used in two place. In the "update" part of the statement and the where clause. In using this statement to change the value a primary from "ALFKI" to "AAAAA" we have to be able to say: "Update the row whose primary key is currently "ALFKI" to have a primary key of AAAA." So in this case we set all of the parameters in the upate part to use the DataRowVersion.Current. But we set the where clause parameters to use DataRowVersion.Original.
ADOParameter workParam = null; workParam = workCommand.Parameters.Add("CustomerID", ADODBType.WChar, 5); workParam.Direction = ParameterDirection.Input; workParam.SourceColumn = "CustomerID"; workParam.SourceVersion = DataRowVersion.Current; workParam = workCommand.Parameters.Add("CompanyName", ADODBType.VarWChar, 40); workParam.Direction = ParameterDirection.Input; workParam.SourceColumn = "CompanyName"; workParam.SourceVersion = DataRowVersion.Current; .. all the other parameters then workParam.SourceVersion = DataRowVersion.Current; workParam = workCommand.Parameters.Add("oldCustomerID", ADODBType.WChar, 5); workParam.Direction = ParameterDirection.Input; workParam.SourceColumn = "CustomerID"; workParam.SourceVersion = DataRowVersion.Original;