Using stored procedures

You can use stored procedures with Adaptive Server ADO.NET Data Provider. The ExecuteReader method is used to call stored procedures that return a result set.

When you retrieve data from the database using a stored procedure, and the stored procedure returns both an output parameter value and a result set, then the result set will be reset and you will be unable to reference result set rows as soon as the output parameter value is referenced. Sybase recommends that in these situations you reference and exhaust all rows in the result set and leave the referencing output parameter value to the end.

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.

If the stored procedure requires parameters you must create equivalent AseParameter objects. If you specify that the CommandType is StoredProcedure, set the CommandText to the name of the stored procedure. For example:

sp_producttype

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 )

Open the connection:

For C#:

conn.Open();

For Visual Basic .NET:

conn.Open()

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

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”.

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

Close the AseDataReader and AseConnection objects:

For C#:

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

For Visual Basic .NET:

reader.Close()
conn.Close()

You can also call a stored procedure using call syntax. This syntax is compatible with ODBC and JDBC. For example:

AseCommand cmd = new AseCommand("{ call sp_product_info(?) }", conn);

In this case, do not set the Command type to CommandType.StoredProcedure. This syntax is available when you do not use named parameters and have set the AseCommand.NamedParameters property to “false.”

Using stored procedures

Alternate way to call a stored procedure