Subsonic 3 fails to identify Stored Procedure Output Parameter

Question

Hi using Subsonic 3.0.0.3 it appears there is some issue with Subsonic identifying Stored Procedure paramters as output parameters.

In the StoredProcedures.cs class I find my stored procedure definition but the last parameter is defined incorrectly as a 'AddParameter'.

sp.Command.AddParameter("HasPermission",HasPermission,DbType.Boolean);

When I sp.Execute() and attempt to read the value of the sp.Command.OutputValues[0] the value is null.

If the definition is edited to be like this;

sp.Command.AddOutputParameter("HasPermission", DbType.Boolean);

Then the value is returned and is correct value type

I am not sure how I 'fix' this - as everytime I regen the SP class via the 'Run Custom Tool' the parameter definitions require editing. Should I edit a T4 template somehow?

Please advise.

EDIT: I forgot to mention I am using MS SQL 2008 (10.0.2531)

Answer 1

Was hoping with subsonic 3.0.0.4 this was fixed but sadly no. Fix can be done following this blog entry; http://brianmrush.wordpress.com/2010/01/15/subsonic-and-t4-templates/

Basically add this to SQLServer.ttinclude;

p.ParameterDirection = GetParamDirection(row["PARAMETER_MODE"].ToString());

And ad this method to SQLServer.ttinclude;

string GetParamDirection(string paramMode)
{
    switch (paramMode)
    {
    case "IN":
        return "ParameterDirection.Input";
    case "INOUT":
        return "ParameterDirection.InputOutput";
        case "OUT":
        return "ParameterDirection.Output";
    case "RETURN":
        return "ParameterDirection.ReturnValue";
    default:
        return "ParameterDirection.Input";
    }
}

Then in the StoredProcedure.tt file modify line 21 to look like this;

sp.Command.AddParameter("<#=par.Name#>",<#=par.CleanName#>,DbType.<#=par.DbType#>);