Thursday, July 1, 2010

ADS 10 Tip #24 – Derive Parameters

The AdsCommand object has a CommandType property which can be used to specify the type of command to use. By default the type is Text which means it expects a valid SQL statement. You can use TableDirect to open a table directly and StoredProcedure to run a system or stored procedure.

When using the StoredProcedure command type you can also use the DeriveParameters method to automatically populate the command parameters collection with the input parameters. With the parameters collection populated you can quickly assign their values.

The DeriveParameters method only worked for stored procedures defined in the current database in previous versions. In version 10 support was added for system procedures as well.

// Set the Application ID for the current connection
using (AdsCommand cmd = cnAds.CreateCommand())
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "sp_SetApplicationID";
  cmd.DeriveParameters();
  cmd.Parameters[0].Value = "My Application";
  cmd.ExecuteNonQuery();
}      

No comments: