Passing an array to a db2 stored procedure from c#

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

Scenario summary:
Hand over an array of IDs to a stored procedure and get the rows from the database matching those IDs

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<idocument> GetDocList(int[] array)
        {
            IList</idocument><idocument> DocList = new List</idocument><idocument>();
 
            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;
        }
</idocument>

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

Some tips:
Make your life easier and install the IBM Visual studio 2010 plugin by installing these two packages from IBM:

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

A useful tool for testing the initial setup and troubleshooting is the IBM testconn20.exe / testconn40.exe which is usually in the „C:Program FilesIBMSQLLIBbin“ or somewhat similar named folder.
Have a look at the examples in „C:ProgrammeIBMSQLLIBsamples.NETcs“, especially „TbUse.cs“, which shows basic SQL commands as select, insert, update, and delete in a transaction.

Happy coding 🙂

Mocking objects in C# with Moq

Moq can come in handy for unit tests. Let us assume you have a View and a Presenter. The View as part of the GUI manages user input and therefore hands over events to the Presenter. The presenter then computes something based on these events. When unit testing triggering these events can be very difficult.
So why not just mock the View with Moq?

Here are two nifty ideas for your unit tests:

[TestMethod]
public void UnitTest1()
{
	IPresenter presenter = new Presenter();
	int initial = presenter.MyProperty;
	var viewMock = new Mock<iview>();
 
	//we now raise ViewEvent from the view
	viewMock.Raise(e => e.ViewEvent += null);
 
	//the presenter listens on it and does something to MyProperty
	int final = presenter.MyProperty;
	Assert.IsTrue(initial != final);
}
</iview>
[TestMethod]
public void UnitTest2()
{
	IPresenter presenter = new Presenter();
	var viewMock = new Mock<iview>();
 
	//we now run a method on presenter that should update the view
	presenter.RunUpdateView();
 
	//Now we want to check that the update method of the view
        //has been called with an IEnumerable of MyData
	viewMock.Verify(f => f.Update(It.IsAny<ienumerable <MyData>>(), Times.AtLeastOnce());
}
</ienumerable></iview>

For more information on Moq check out http://code.google.com/p/moq/wiki/QuickStart

compound files in c#

Compound files are an somewhat obsolete concept of storing several files and objects into one compound file (http://en.wikipedia.org/wiki/Compound_File_Binary_Format), old microsoft office documents are compound files.

Sadly there is no managed code for c# to use compound files, so one has to use a lot of native  Com calls, which makes stuff a bit messy.

After the jump I’ll show the basic steps to write a simple compound file …

Weiterlesen