SubSonic-3.0

SubSonic-3.0 copied to clipboard

When executing a stored procedure that results in a DataReader and a return value the return parameter is never initialized (even after the DataReader is closed or has read to the end).

This seems to be related to KB Article Q308621 (http://support.microsoft.com/kb/308621/EN-US/). The issue is that Command.AddReturnParameter() adds the parameter to the end of the parameters collection, but C#/ado.net require the return value as the first parameter.

WorkAround

Rather than calling AddReturnParameter() you can work with the command object directly and insert the return value in the begining. Something like ...

            retVal  = new SubSonic.Query.QueryParameter();
            retVal.ParameterName ="@ReturnValue";
            retVal.DataType = System.Data.DbType.Int16;
            retVal.Mode = System.Data.ParameterDirection.ReturnValue;
            sproc.Command.Parameters.Insert(0, retVal );

Fix

One possible fix is edit AddParameter(string, object, int, DbType, ParameterDirection). Just change the last line (parameters.Add(param)) to read as follows:

if (param.Mode == ParameterDirection.ReturnValue) {
        parameters.Insert(0, param);
} else {
            parameters.Add(param);
    }

Environment

• C#
• Subsonic 3.0.0.3
• .NET 3.5
• SQL Server 2008

Can you confirm that this is still an issue with the latest version of the source Doogie004?

I'm not to familiar with GitHub, but I had downloaded the zip file labeled 3.0.0.3 from http://github.com/subsonic/SubSonic-3.0/downloads to sort out what was happening. Is that is the version you want -- yes I can confirm. If you want something else just let me know.

-eric

Hi eric

If you go to http://github.com/subsonic/SubSonic-3.0 in the top right is a 'Download Source' button that will let you download the latest version of the source code.

Thanks Adam

I had a fairly recent version when I posted this issue (Version 0f6248f from 3/3/2010). So as of 3/3 the issue existed.

Using the latest version (19b5d44 from 3/10/2010) i have the same issue. However, the 3/3 version had a method sproc.Command.GetReturnParameter() which i was using. This method seems to have been removed in the 3/10 version. I assume in favor of sproc.Command.GetOutputParameters(). Calling sproc.Command.GetOutputParameters(sproc.Command.Provider) the OutPut list is filled, however the item is uninitialized as I described above. The value in the underlying sproc.Command.Parameters collection is also uninitialized.

Is there an alternate way I should be getting the return value ? something like "returnVal = (int)sproc.Output" doesn't work either --- .Output is null.

Adam,

Looking at this a little deeper, I'm not really clear how stored procedure output values are ever captured by ExecuteReader(). The function creates a new DBCommand from the QueryCommand passed into the function. The DBCommand is locally scoped, so output values pushed into its parameters are lost as soon as the function ends. There is no attempt to harvest the values back into the QueryCommand object.

As the 2nd to last line in ExecuteReader I added qry.GetOutputParameters(cmd). This change works if the stored procedure doesn't return a recordset. But given KB Q308621 GetOutputParmeters needs a way to hold onto the command object so values can be retrieved after the recordset is closed.

Since the area seems to be in flux can you share some of your thoughts on how output params should be captured? Am I missing something?

To be honest you probably know more about this issue right now than I do, this isn't an area of code I'm familiar with so right now I'm just trying to identify the severity of the issue and how long it might take to fix. You're not missing anything as far as I can tell, you should be able to use sproc.Output to get output parameters but the more I investigate the more I see other possible issues. I'm going to mark this to be fixed for 3.1 as it really needs a bunch of new tests in our test project before I can safely confirm the fix. Please keep adding any more info you find, it's extremely helpful.

I don't really know this area either :-D I'm not familiar enough with the structure of subsonic to claim this a good solution, but I needed a workaround (even a temporary one). It only involved changing 10-20 lines of code.

DataProvider.cs
Created a new get/set property (to create the retention of the actual command object) :

 public DbCommand NativeDBCommand { get; private set; }

In ExecuteReader I added the second (2nd) of these two line of code (although, one could replace the local cmd variable altogether):

 DbCommand cmd = scope.Connection.CreateCommand();
 NativeDBCommand = cmd;

QueryCommand.cs I added the following snippet of code:

 public void GetOutputParameters(DbDataReader readerToClose ) {
    if (readerToClose != null) { readerToClose.Close(); }
    readerToClose = null;
    GetOutputParameters();
}

public void GetOutputParameters() {
    GetOutputParameters( ((DbDataProvider)this.Provider).NativeDBCommand );
}

In AddParameter I changed the last line of code to read (makes sure return param is first - see KB Q308621):

if (param.Mode == ParameterDirection.ReturnValue) {
    parameters.Insert(0, param);
} else {
    parameters.Add(param);
}

StoredProcedures.cs I changed the Output property (which didn't seem to do anything) to get the first output value from the OutputValues list in the Command.

public object Output { 
    get {
        if (Command.OutputValues != null  && Command.OutputValues.Count > 0  && Command.OutputValues[0] != null  ) {
            return Command.OutputValues[0];
        } else {
            return null;
        }
    } 
    set{
        if ( Command.OutputValues == null ) {
            Command.OutputValues = new List<object>();
        }
        Command.OutputValues.Add(value);
    }
}

Typical Syntax for Calling...

db = new MyNamespace.MyDB();
sproc = db.FancyStoredProc( paramA, paramB);
sproc.Command.AddReturnParameter();
reader      = sproc.ExecuteReader();

//do fancy stuff with reader here

//passing in reader is just a convenient way to close the reader
sproc.Command.GetOutputParameters(reader); 
returnVal = (int)sproc.Output;