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 SAParameter object. Use a question mark as a placeholder for parameters, as follows: 

sp_producttype( ?, ? )

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

To execute a stored procedure

  1. Declare and initialize an SAConnection object.

    SAConnection conn = new SAConnection(
    "Data Source=SQL Anywhere 11 Demo" );

  2. Open the connection.

    conn.Open();

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

    SACommand cmd = new SACommand( "ShowProductInfo",
    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:

    SACommand cmd = new SACommand(
    "call ShowProductInfo(?)", conn );
cmd.CommandType = CommandType.Text;

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

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

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

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

    SADataReader 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 SADataReader and SAConnection 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: 

SACommand cmd = new SACommand(
    "call ShowProductInfo( 301 )", conn );

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

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

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0