Invoking a Stored Procedure Example
After all definitions are in place, the call to ExecuteNonQuery invokes the stored procedure. The LookupProduct procedure (in the TDV directory path ~/shared/examples/LookupProduct) must be published before this sample code is executed; otherwise, the return value of ExecuteNonQuery is not valid.
The procedure SQL statement is the same for JDBC and ODBC.
The sample gets a CompositeCommand object, and then defines the CommandType as a StoredProcedure using the following line::
cmd.CommandType = CommandType.StoredProcedure;
An exception can occur if the CompositeCommand object is called without this specification.
A new ID parameter is added with the CompositeParameter object type:
CompositeParameter id = new CompositeParameter("?ProductID", 12, CompositeDbType.INTEGER);
The CompositeParameter ID parameter object gets a name, value, and type:
"?ProductID", 12, and CompositeDbType.INTEGER.
Procedure parameters must have a specified direction. There are four parameter directions in the ADO.NET standard:
• Input
• Output
• InputOutput
• ReturnValue
In this example, ProductID is an input parameter, so the example code defines:
id.Direction = ParameterDirection.Input;
The example also has an output parameter that is declared with the following line:
CompositeParameter cursor = new CompositeParameter("?cursor", CompositeDbType.OTHER);
The name is set to a ?cursor placeholder of type CompositeDbType.OTHER. When the parameter is a cursor, you must specify CompositeDbType.OTHER as the data type. Setting the direction is required:
cursor.Direction = ParameterDirection.Output;
The sample code adds those parameter objects with the lines:
cmd.Parameters.Add(id);
cmd.Parameters.Add(cursor);
To read a valid output value the sample uses:
CompositeDataReader reader = (CompositeDataReader)cursor.Value;
Cursor output must be fetched by the CompositeDataReader. All cursor data is mapped to the CompositeDbType.OTHER data type, so returned values are CompositeDataReader objects. The CompositeDataReader object can be obtained from cursor.Value.
The following sample code illustrates one way to invoke a procedure:
public void TestSelect()
{
CompositeConnection conn = GetConnection();
String sql = "{call LookupProduct(?ProductID,?cursor)}";
CompositeCommand cmd = new CompositeCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
CompositeParameter id = new CompositeParameter("?ProductID", 12, CompositeDbType.INTEGER);
id.Direction = ParameterDirection.Input;
CompositeParameter cursor = new CompositeParameter("?cursor", CompositeDbType.OTHER);
cursor.Direction = ParameterDirection.Output;
cmd.Parameters.Add(id);
cmd.Parameters.Add(cursor);
try
{
cmd.ExecuteNonQuery();
CompositeDataReader reader = (CompositeDataReader)cursor.Value;
if (reader != null)
{
String ProductName;
int ProductID;
String ProductDescription;
if (reader.Read())
{
//ProductName=reader.GetString("ProductName");
ProductName = (String)reader["ProductName"];
//ProductID=reader.GetInt32("ProductID");
ProductID = (int)reader["ProductID"];
//ProductDescription=reader.GetString("ProductDescription");
ProductDescription = (String)reader["ProductDescription"];
}
}
}
catch (Exception ex)
{
throw ex;
}
}
The following sample code defines a method with a SQL string, SQL, that can be used to call a stored procedure, p_integer, with a placeholder parameter, ?int, bound to intValue.
The definition of the CompositeParameter is the following:
CompositeParameter intValue = new CompositeParameter("?int", CompositeDbType.INTEGER);
The ?int is an InputOutput parameter, with a type of CompositeDbType.INTEGER.
Set a procedure parameter value with a code line like:
intValue.Value = 12
Retrieve a procedure parameter value using a line like:
int value = (int)intValue.Value
The following code sample is another way to invoke a stored procedure:
public void TestInOut()
{
string sql = "{call p_integer(?int)}";
CompositeCommand cmd = new CompositeCommand(sql, conn);
cmd.CommandType = Command60
Type.StoredProcedure;
CompositeParameter intValue = new CompositeParameter("?int", CompositeDbType.INTEGER);
cmd.Parameters.Add(intValue);
intValue.Direction = ParameterDirection.InputOutput;
intValue.Value = 12;
try
{
cmd.ExecuteNonQuery();
int value = (int)intValue.Value;
}
catch (Exception ex)
{
throw ex;
}
}