Stored procedure connection wrapper

Question

I have a wrapper that I use to query a DB2 database. The way I have the wrapper set up, the connections are created and disposed within the query method. This way the consumers of my wrapper do not have to worry about managing (opening and closing) connections. Is there a way to do this with a stored procedure? Some of my users send in outbound parameters, is there a way to turn those parameters into a datatable, like I do above in my query?

    /// <summary>
    /// Query A database and return a DataTable of strings with no null
    /// </summary>
    /// <param name="txtQuery">The Query</param>
    /// <param name="list">the paramaters for the query</param>
    /// <returns>Datatable with results</returns>
    public DataTable Query(string txtQuery, params string[] list)
    {
     //create return ovject
        DataTable dt = new DataTable();
        //pull dbconnection out of pool
        using (var conn = new DB2Connection(connectionstring))
        {
            //open connection
            if (!OpenConn(conn))
            {
                throw new Exception(“failed to connect”);
            }
            try
            {
                //query db
                using (DB2Command cmd = new DB2Command())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = txtQuery;
                    for (int i = 0; i < list.Length; i++)
                    {
                        DB2Parameter param = new DB2Parameter(i.ToString(), list[i]);
                        cmd.Parameters.Add(param);
                    }
                    //fill datatable
                    using (DB2DataAdapter adap = new DB2DataAdapter())
                    {
                        adap.SelectCommand = cmd;
                        adap.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }                
        }
       return dt;
    }

I'm not sure if i've understood the problem. You can set the `CommandType` to `StoredProcedure` and use it for a `DataAdapter` to fill the `DataTable`. — Tim Schmelter, Nov 20 '12 at 15:14
my understanding is that for a stored procedure you can send in output parameters that are then read using a datareader that needs the original open connection — user889829, Nov 20 '12 at 15:17
What does the `SP` return? If it's a query that returns rows you could simply use the adapter to fill the DataTable. — Tim Schmelter, Nov 20 '12 at 15:19
It's simple as this to fill a DataTable from Stored-Procedure: http://stackoverflow.com/a/13402124/284240 — Tim Schmelter, Nov 20 '12 at 15:44
@TimSchmelter: He does explicitly mention "Outbound parameters" which means its not as simple as just using a data adapter unless I'm very behind on the cleverness that DataAdapter does... — Chris, Nov 20 '12 at 16:03
@Chris: Are "Outbound parameters" parameters with `ParameterDirection.Output`? Then they will be added to the DataTable as column if i remember correctly. — Tim Schmelter, Nov 20 '12 at 16:07
I assume that is what they are. And I didn't know it did that though I will admit that does seem like a strange thing to do to me... — Chris, Nov 20 '12 at 16:13
Looking at http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.71%29.aspx it looks like you can do stuff with mapping output parameters but I will admit from reading that page that I don't understand how it works. Time to go read some more msdn... :) — Chris, Nov 20 '12 at 16:19
BTW, `catch (Exception ex){throw ex;}` is worse than nothing. Just remove that try/catch block. — John Saunders, Nov 20 '12 at 16:27
Good catch john. http://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex goes into details of why that is actually an actively bad piece of code. — Chris, Nov 20 '12 at 16:35

score 3 · Accepted Answer · answered Nov 20 '12 at 16:01

Its not going to be as easy to do it but it is possible. I'm assuming the DB2Parameter is essentially the same as the SqlParameter in this answer...

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx and related pages will be useful reading after (or during) what I'm saying here.

Essentially when calling a procedure with output parameters you call it in the same way as you normally would. For the output parameters you pass an object in as normal but there is a Direction property on the parameter object that allows you to specify that it is an output parameter.

Once you have called the appropriate execute method on the procedure then the values of those output parameters will be populated and can be retrieved from the parameter object.

Putting them into a DataTable is then up to you. You can create a datatable, adding a column to it for each output parameter and then add a row in with all the values.

The main problem with this is of course that when setting up the database call you need to know which parameters are goign to return something so you can set their direction appropriately. While you are passing just a string array then this is almost certainly not going to be possible.

The best approach to this would be to as others have suggested in comments return all data as a dataset. Any existing procedures that currently use output parameters could have wrapper procedures created that just capture the output parameters and then put them into a select statement that you can process as above.

The other option would be to have the caller of your Query method pass in more details about the parameters they are using (such as in or out). Personally I prefer returning all data as selects rather than using output parameters though because it makes this sort of code simpler.

Stored procedure connection wrapper

1 Answers1