DB2 Version 10.1 for Linux, UNIX, and Windows

Calling stored procedures from .NET applications

.NET applications can call stored procedures with a DB2Command object.

Procedure

  1. 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.
  2. 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: 
    DB2Command 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: 
    DB2Command 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.
  3. 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 = (Int32) 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 DB2® for Linux, UNIX, and Windows 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”, DB2Type.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:
    DB2Command cmd = new DB2Command("cursor_test", conn)
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cursor1", DB2Type.Cursor).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("cursor2", DB2Type.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.

  4. Run the DB2Command.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 DB2DataReader object. A C# code example follows: 
    DB2DataReader  drOutput2 = cmd.Parameters[1].Value;
DB2DataReader  drOutput1 = cmd.Parameters[0].Value

Example

A C# code with CommandType.Text example follows:
// assume a DB2Connection conn
DB2Transaction trans = conn.BeginTransaction();
DB2Command 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 DB2Command
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 DB2Connection conn
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = 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 DB2Command
...

' Call the stored procedure
Console.WriteLine("  Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
A C# code with CommandType.StoredProcedure example follows:
// assume a DB2Connection conn
DB2Transaction trans = conn.BeginTransaction();
DB2Command 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 DB2Command
...

// Call the stored procedure
Console.WriteLine("  Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
A Visual Basic code with CommandType.StoredProcedure example follows:
' assume a DB2Connection conn
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = 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 DB2Command
...

' 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();