.NET applications can call stored procedures with a DB2Command object.
Procedure
- Make a connection to a target database. For steps to establish
database connection, see Connecting to a database from an application using the IBM Data Server Provider for .NET.
- Create the DB2Command object and set
the CommandType property as either CommandType.StoredProcedure or CommandType.Text.
The default value of the CommandType property is CommandType.Text.
The CommandType.Text value can be used to call
stored procedures. However, calling stored procedures is easier when
you set the
CommandType
property to CommandType.StoredProcedure.
When you use the CommandType.StoredProcedure object
to call a stored procedure, you must specify the stored procedure
name and parameters that are associated with the stored procedure.
A stored procedure with same name and same parameters can exist under
different schemas. To avoid calling an incorrect stored procedure,
fully qualify the stored procedure name with the correct schema name.
A C# code example of the
CommandType.Text object
follows:
DB2
Command cmd = conn.CreateCommand();
String procCall = "CALL TEST_PROC (@input_param1)";
cmd.CommandType = CommandType.Text;
cmd.CommandText = procCall;
Note: When the CommandType
property
is CommandType.Text
, both CALL
and EXECUTE
PROCEDURE
statements are supported.
A C# code example
of the
CommandType.StoredProcedure
object follows:
DB2
Command cmd = conn.CreateCommand();
String procName = "TEST_PROC";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
Note: When the CommandType
property
is CommandType.StoredProcedure
, named parameters
are not supported.
- Create the
DB2Command.Parameters
objects
that correspond to the IN, INOUT and OUT parameters. If you are using
parameter markers for stored procedure parameters, create the DB2Parameter objects
and bind the DB2Parameter objects to the DB2Command.Parameters object
with the Add method. A C# code example
follows:
DB2Parameter p1 = new DB2Parameter(“input_param1”, DB2Type.Integer);
p1.Value = 123;
db2Command.Parameters.Add(p1);
You can pass the store procedure
parameters with host variables, named parameters, or positioned parameters.
However, you cannot use different methods to pass the stored procedure
parameters within the same SQL statement. Parameters can be passed
to the stored procedure in any order, when qualified by the parameter
name as shown in following C# code example:
CREATE PROCEDURE schema.my_proc ( IN var1 int, INOUT var2 int )
LANGUAGE SQL
BEGIN
-- procedure code here
END
String procCall = "CALL my_proc (var2=>@param2, var1=>@param1");
IBM Data Server Provider for
.NET
supports calling stored procedures with ARRAY data types as input (IN) parameters in following
database servers:
- Db2®.
- Db2 for z/OS® Version 11 server
in new function mode (NFM).
- Db2 for IBM i V7R1 and
later servers.
ARRAY data types are not supported for the OUT and INOUT parameters. The ARRAY length value
must be specified in the
DB2Parameter.ArrayLength object for each ARRAY
parameter. A C# code example
follows:
Int32 integerArray = new Int32[] { 12, 34, 45, 67 };
DB2Parameter p1 = new DB2Parameter(“input_param1”, Type.Integer);
p1.Value = integerArray;
p1.ArrayLength = 3;
db2Command.Parameters.Add(p1);
The
Cursor enumeration member can be used when binding INOUT
(
InputOutput) or OUT (
Output) parameters of the type cursor. A
C# code example of output parameters
follows:
Command cmd = new Command("cursor_test", conn)
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cursor1", Type.Cursor).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("cursor2", Type.Cursor).Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
If
your application is connecting to Db2 for z/OS Version
10 and later servers, your application must specify the correct data
type for the input parameters of the stored procedure that you are
calling. If your application specifies parameters that do not match
the data type of the input parameter, an invalid conversion error
is returned.
-
Run the Command.ExecuteNonQuery() function to call a stored procedure.
A C# code example follows:
cmd.ExecuteNonQuery();
If there are any OUT
or INOUT parameters, you can obtain the parameter values with DataReader object. A C# code example
follows:
DataReader drOutput2 = cmd.Parameters[1].Value;
DataReader drOutput1 = cmd.Parameters[0].Value
IBM Data Server Provider for
.NET saves extra
network traffic that is associated with sending the implicit COMMIT statement when the following
conditions are met:
- The connected database server is Db2 for z/OS Version 11 in new
function mode (NFM).
- The BeginTransaction method is not called by the application.
- There are no open result-sets when the stored procedure completes the execution.
Example
A C# code with CommandType.Text example
follows:// assume a DB2
Connection conn
DB2
Transaction trans = conn.BeginTransaction();
DB2
Command cmd = conn.CreateCommand();
String procName = "INOUT_PARAM";
String procCall = "CALL INOUT_PARAM (@param1, @param2, @param3)";
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = procCall;
// Register input-output and output parameters for the DB2
Command
cmd.Parameters.Add( new DB2Parameter("@param1", "Value1");
cmd.Parameters.Add( new DB2Parameter("@param2", "Value2");
DB2Parameter param3 = new DB2Parameter("@param3", IfxType.Integer);
param3.Direction = ParameterDirection.Output;
cmd.Parameters.Add( param3 );
// Call the stored procedure
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
A Visual Basic code with CommandType.Text example
follows:' assume a DB2
Connection conn
Dim trans As DB2
Transaction = conn.BeginTransaction()
Dim cmd As DB2
Command = conn.CreateCommand()
Dim procName As String = "INOUT_PARAM"
Dim procCall As String = "CALL INOUT_PARAM (?, ?, ?)"
cmd.Transaction = trans
cmd.CommandType = CommandType.Text
cmd.CommandText = procCall
' Register input-output and output parameters for the DB2
Command
...
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
A C# code with CommandType.StoredProcedure example
follows:// assume a DB2
Connection conn
DB2
Transaction trans = conn.BeginTransaction();
DB2
Command cmd = conn.CreateCommand();
String procName = "INOUT_PARAM";
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
// Register input-output and output parameters for the DB2
Command
...
// Call the stored procedure
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
A Visual Basic code with CommandType.StoredProcedure example
follows:' assume a DB2
Connection conn
Dim trans As DB2
Transaction = conn.BeginTransaction()
Dim cmd As DB2
Command = conn.CreateCommand()
Dim procName As String = "INOUT_PARAM"
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
' Register input-output and output parameters for the DB2
Command
...
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
A C# code example with ARRAY input
parameter follows:db2Command.CommandText = “arrayparamprocedure”;
db2Command.CommandType = CommandType.StoredProcedure;
Int32 integerArray = new Int32[] { 12, 34, 45, 67 };
DB2Parameter p1 = new DB2Parameter(“numbers_in”, DB2Type.Integer);
p1.Value = integerArray;
p1.ArrayLength = 3;
String[] stringArray = new String[] {“i think i know”, “but you never know”, “how much i know” };
DB2Parameter p2 = new DB2Parameter(“varchars_in”, DB2Type.Varchar, 30);
p2.Value = stringArray;
p2.ArrayLength = 2;
db2Command.Parameters.Add(p1);
db2Command.Parameters.Add(p2);
db2Command.ExecuteNonQuery();