I am currently working on the database access layer of a business application. We have to access a DB2 database from our .net program, due to design decisions we cannot use the Entity Framework, so we are stuck with plain ADO.net. In my blog post I want to present a way to send an Input Array to a stored procedure in DB2 and retrieving the result set in C#.

First we have to declare the Array as a Type in the DB2 database:

CREATE TYPE InArrayType AS INTEGER ARRAY[];

This creates an integer array which we want to use as an input parameter for our stored procedure.

Next we create the stored procedure:

CREATE OR REPLACE PROCEDURE ArrayStoredProcedure(IN IDs InArrayType)
 RESULT SETS 1
 SPECIFIC ArrayStoredProcedure
P1: BEGIN
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
-- Declare cursors
DECLARE cur CURSOR WITH RETURN FOR
SELECT * FROM V_View, UNNEST(ids) AS T(id)
    WHERE V_View.ID = T.id;

-- Cursor left open for client application.
    OPEN cur;

END P1

Our newly created stored procedure takes the above created array type as an input parameter with the name "IDs". The array is then "unnested" and treated as a Table, so that we can use a simple Select statement. The result set of this select statement is then returned with the cursor.

And now for the easy part - retrieving the information in ADO.net:

// ..
using IBM.Data.DB2;
using System.Data;
// ...
public IList GetDocList(int[] array)
        {
            IList DocList = new List();

            try
            {
                using (DB2Connection conn = new DB2Connection(connString))
                {
                    // Create new Command with Stored Procedure name
                    DB2Command cmd = conn.CreateCommand();
                    String procName = "SchemaName.StoredProcedure";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    //create Array Parameter and add to Command
                    DB2Parameter param = cmd.CreateParameter();
                    param.ArrayLength = array.Length;
                    param.DB2Type = DB2Type.DynArray;
                    param.Direction = ParameterDirection.Input;
                    param.ParameterName = "IDs";
                    param.Value = array;
                    cmd.Parameters.Add(param);

                    conn.Open();

                    using (DB2DataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            // Read Data ...

                            string name = dr.IsDBNull(0) ? String.Empty : dr.GetString(0);
                            IDocument Doc = new Document(name);
                            DocList.Add(Doc);
                        }
                    }
                }
            }
            catch (DB2Exception ex)
            {
                string msg = "Error in StoredProcedure: ArrayStoredProcedure";
                // log msg
                // rethrow msg
            }
            return DocList;
        }

The c# method GetDocList wraps the database access and gets a list of "Documents" from the database based on the integer parameter array. The code is pretty straightforward and self-explanatory (the using statements ensure correct object disposal, the connection string is the connection string to your db2 database, the DataReader populates the Document domain object and adds it to a List).

• v9.7fp4_nt32_client (IBM DB2 Client package)
• v9.7fp4_nt32_vsai (Visual Studio 2010 update package)

Happy coding :)