ReturnValue from a TableAdapter (ASP.NET2)

<< Ruby on Rails | Home | Avoiding SQL Cursors >>

We've been using TableAdapters created using the XSD designer in VS2005 lately, and have found they have a couple of gotchas.

For example, one limitation of a TableAdapter is the lack of support for SQL stored proc return values when using a non-scalar stored proc call - or at least, lack of any useful access to them. You'd expect that maybe the return value would be the return from the auto-genned function, but you'd be wrong. The return is actually the number of rows affected, and that in itself is useless if you're turning row count off in the stored proc (as many do).

So maybe the ReturnValue is a property on the TableAdapter? Again, no... you can use 'out' parameters fine (just pass the variable as ref to the auto-genned function), but thats not very nice. I don't want to throw away the return value concept and use 'out' params just because the TableAdapter isn't clever enough.

So, what to do? There's some good blog posts around on the technique, but most of them only work really well when you have just one or two stored procs and table adapters (see this blog post for an example. In the solutions we're building, we often have lots. And lots. It's a heady mixture of designer work and cut-paste fest. Ugly maybe, but it does allow flexibility. This meant we had to make some kind of vaguely generic function to get the ReturnValue available to our UI code layer.

To achieve this, we made use of the TableAdapter partial classes that the XSD creates for us. Let's say that we have a set of stored procs to do with orders. You might have:

AddOrder
UpdateOrder
DeleteOrder
GetOrder
GetOrders

You'd probably only be bothered with the return value from the stored procs that actually wrote date - the top 3. Lets say they are returning an error code, like 0 for success, 1 if they can't find the specified record, 2 if it will break a constraint etc. What you can then do is create your TableAdapter queries for each function - lets assume you called them all the same thing as their underlying stored procs. Let's say our TableAdapter is called OrderTableAdapter.

We then need to do our clever jiggery pokery. Just outside our partial class for OrderTableAdapter, we put an enumeration, like so:

    public enum OrderCommandType
    {
        Add,
        Update,
        Delete,
        Get,
        GetAll
    }

We do this so that it's a cleaner solution in our data layer, where we'll have a manager class (I hate lobbing strings around where an enum would be better). Following this enum declaration, we have the main worker function which is a method called 'ReturnValue' which takes one parameter - the OrderCommandType. For simplicity, I've written comments inline in the method below.

partial class OrderTableAdapter
    {
        public int ReturnValue(OrderCommandType Command)
        {
            // get the return value for the appropriate command
            switch (Command)
            {
                case OrderCommandType.Add:

// the first param is the SQL command collection of the TableAdapter, the second param is the stored proc name that we want the return value from
return TableAdapterHelper.GetReturnValue(this.CommandCollection, "AddOrder");

case OrderCommandType.Update:

return TableAdapterHelper.GetReturnValue(this.CommandCollection, "UpdateOrder");

case OrderCommandType.Delete:

return TableAdapterHelper.GetReturnValue(this.CommandCollection, "DeleteOrder");

default:

                    // for cleanliness, throw an error if someone tries to get the ReturnValue from one of the method types that we have decided shouldn't return a value
                    throw new Exception("OrderCommandType requested does not support return values");
            }
        }
    }

You'll see I refer to a TableAdapterHelper - this is important as it's the bit that actually digs out the ReturnValue itself...

    public static class TableAdapterHelper
    {
        public static int GetReturnValue(SqlCommand[] aCommands, string sCommandText)
        {
            // now lets find that commmand

            // iterate through all command objects in the command collection
            foreach (SqlCommand oCommand in aCommands)
            {
                // see if the stored proc name matches
                if (oCommand.CommandText.Equals(string.Format("dbo.{0}", sCommandText)))
                {
                    if (oCommand.Parameters != null)
                    {
                        // loop thru all params
                        foreach (SqlParameter oParam in oCommand.Parameters)
                        {
                            // find the param with a direction of ReturnValue (should only ever be one)
                            if (oParam.Direction == System.Data.ParameterDirection.ReturnValue)
                            {
                                if (oParam.Value == DBNull.Value || oParam.Value == null) return 0; // assume dbnull is success

                                return (int)oParam.Value;
                            }
                        }
                    }
                }
            }

            throw new Exception("Could not locate Return Value");
        }
    }

As you'll see, my technique works on the basis that the stored proc names are known and unique across commands in a single TableAdapter, which should normally be the case. You might find it ugly that the stored proc names are embedded in code - but they're in the XSD anyway so it's still containing in one place.

So the final part of the jigsaw is the actual call itself, and the getting of the ReturnValue. Here is an example of how to make the call. Note that I've made OrderID an out param. I like to throw an error if the SQL error code is non-zero.

OrderTableAdapter ta = new OrderTableAdapter();

int iRowsAffected = ta.AddOrder(ref OrderID, OrderCode, CustomerID, OrderLineID);

int iErrorCode = ta.ReturnValue(OrderCommandType.Add);

if(iErrorCode != 0)
{
// throw an exception here
}