ASA Programming Guide
  Developing Applications with the .NET Data Provider

Using stored procedures

You can use stored procedures with the .NET data provider. The ExecuteReader method is used to call stored procedures that return a result set, while the ExecuteNonQuery method is used to call stored procedures that do not return a result set. The ExecuteScalar method is used to call stored procedures that return only a single value.

When you call a stored procedure, you must create an AsaParameter object. Use a question mark as a placeholder for parameters, as follows:

sp_producttype( ?, ? )

For more information about the Parameter object, see AsaParameter class.

To execute a stored procedure

1. Declare and initialize an AsaConnection object.

   AsaConnection conn = new AsaConnection(
       "Data Source=ASA 9.0 Sample" );

2. Open the connection.

   conn.Open();

3. Add an AsaCommand object to define and execute a SQL statement. The following code uses the CommandType property to identify the command as a stored procedure.

   AsaCommand cmd = new AsaCommand( "sp_product_info",
       conn );
   cmd.CommandType = CommandType.StoredProcedure;

   If you do not specify the CommandType property, then you must use a question mark as a placeholder for parameters, as follows:

   AsaCommand cmd = new AsaCommand(
       "call sp_product_info(?)", conn );
   cmd.CommandType = CommandType.Text;

4. Add an AsaParameter object to define the parameters for the stored procedure. You must create a new AsaParameter object for each parameter the stored procedure requires.

   AsaParameter param = cmd.CreateParameter();
   param.AsaDbType = AsaDbType.Int32;
   param.Direction = ParameterDirection.Input;
   param.Value = 301;
   cmd.Parameters.Add( param );

   For more information about the Parameter object, see AsaParameter class.

5. Call the ExecuteReader method to return the DataReader object. The Get methods are used to return the results in the desired data type.

   AsaDataReader reader = cmd.ExecuteReader();
   reader.Read();
   int id = reader.GetInt32(0);
   string name = reader.GetString(1);
   string descrip = reader.GetString(2);
   decimal price = reader.GetDecimal(6);   

6. Close the AsaDataReader and AsaConnection objects.

   reader.Close();
   conn.Close();

Alternative way to call a stored procedure 

Step 3 in the above instructions presents two ways you can call a stored procedure. Another way you can call a stored procedure, without using a Parameter object, is to call the stored procedure from your source code, as follows:

AsaCommand cmd = new AsaCommand(
    "call sp_product_info( 301 )", conn );

For information about calling stored procedures that return a result set or a single value, see Getting data using the AsaCommand object.

For information about calling stored procedures that do not return a result set, see Inserting, updating, and deleting rows using the AsaCommand object.