Executing a Stored Procedure

Execute a stored procedure.

1. Declare and initialize an AseConnection object:

   For C#:

   AseConnection conn = new AseConnection(
      c_connStr );

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )

2. Open the connection:

   For C#:

   conn.Open();

   For Visual Basic .NET:

   conn.Open()

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

   For C#:

   AseCommand cmd = new AseCommand( 
      "titleid_proc", conn );
   cmd.CommandType = CommandType.StoredProcedure;

   For Visual Basic .NET:

   Dim cmd As New AseCommand( _
      "titleid_proc", conn )
   cmd.CommandType = CommandType.StoredProcedure

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

   For C#:

   AseParameter param = cmd.CreateParameter();
   param.ParameterName = "@title_id";
   param.AseDbType = AseDbType.VarChar;
   param.Direction = ParameterDirection.Input;
   param.Value = "BU";
   cmd.Parameters.Add( param );

   For Visual Basic .NET:

   Dim param As AseParameter = cmd.CreateParameter()
   param.ParameterName = "@title_id"
   param.AseDbType = AseDbType.VarChar
   param.Direction = ParameterDirection.Input
   param.Value = "BU"
   cmd.Parameters.Add( param )

   For more information about the Parameter object, see AseParameter Class.

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

   For C#:

   AseDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      string title = reader.GetString(0);
      string id = reader.GetString(1);
      decimal price = reader.GetDecimal(2);
      // do something with the data....
   }

   For Visual Basic .NET:

   Dim reader As AseDataReader = cmd.ExecuteReader()
   While reader.Read()
      Dim title As String = reader.GetString(0)
      Dim id As String = reader.GetString(1)
      Dim price As Decimal = reader.GetDecimal(2)
      ' do something with the data....
   End While

6. Close the AseDataReader and AseConnection objects:

   For C#:

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

   For Visual Basic .NET:

   reader.Close()
   conn.Close()