Subsonic 3.0 missing output parameter/default parameter support in SQL Server?

Question

I have a stored proc:

CREATE PROCEDURE MyProc ( @P1 uniqueidentifier, @P2 int = NULL output, @P3 int = NULL output )

The signature on the C# side should be (Guid P1, int? P2, int? P3) I would think. But it's not, it's just (Guid,int,int). Furthermore, the output params are not created as output params, they're just straight "AddParameter(P2,arg,DbType.Int32)". Was this meant to work?

Answer 1

Well, it doesn't look like the native T4 templates handle this so well. We had actually built something more complex back in the VS2003 days, so I've updated it for Subsonic. The idea was that each stored proc gets a class instead of a function and has nullable properties for parameters. I've modified several templates, I'm sure I'm missing something here and this is not "production quality", but here's StoredProcedures.tt

<#@ template language="C#v3.5" debug="False" hostspecific="True"  #>
<#@ output extension=".cs" #>
<#@ include file="SQLServer.ttinclude" #>
<#
    var sps = GetSPs(); 
    if(sps.Count>0){ 
#>  
using System;
using SubSonic;
using SubSonic.Schema;
using SubSonic.DataProviders;
using System.Data;

namespace <#=Namespace#>{
    public partial class <#=DatabaseName#>DB{

<#  foreach(var sp in sps){#>
        public class <#=sp.CleanName#> : StoredProcedure
        {
            public <#=sp.CleanName#>(<#=DatabaseName#>DB database, <#=sp.ArgList#>) : base("<#=sp.Name#>",database.Provider)
            {
<#      foreach(var par in sp.Parameters){
            if (par.IsOutput) { #>
                Command.AddOutputParameter("<#=par.Name#>",DbType.<#=par.DbType#>);
<#
            }
            else
            {
#>
                Command.AddParameter("<#=par.Name#>",<#=par.CleanName#>,DbType.<#=par.DbType#>);
<#      
            }
        }#>
            }

<#  foreach (var par in sp.Parameters) { #>
            public <#= par.SysType #><#= par.ShouldBeNullable ? "?" : "" #> <#= par.CleanName #>
            {
                get
                {
                    object val = Command.Parameters.GetParameter("<#= par.Name #>").ParameterValue;
                    return val == DBNull.Value ? default(<#= par.SysType #><#= par.ShouldBeNullable ? "?" : "" #>) : (<#= par.SysType #><#= par.ShouldBeNullable ? "?" : "" #>)val;
                }
                set             
                {
<#          if (par.ShouldBeNullable) { #>
                    object val = value.HasValue ? (object)value : (object)DBNull.Value;
                    Command.Parameters.GetParameter("<#= par.Name #>").ParameterValue = val;
<#          } #>
                }
            }
<#  }    #>
        }


<#  } #>
    }

}
<#  }#> 

And here's the GetSPParams function from SQLServer.ttinclude

List<SPParam> GetSPParams(string spName){
    var result=new List<SPParam>();
    string[] restrictions = new string[4] { DatabaseName, null, spName, null };
    using(SqlConnection conn=new SqlConnection(ConnectionString)){
        conn.Open();
        var sprocs=conn.GetSchema("ProcedureParameters", restrictions);
        conn.Close();
        foreach(DataRow row in sprocs.Select("", "ORDINAL_POSITION")){
            SPParam p=new SPParam();
            p.SysType=GetSysType(row["DATA_TYPE"].ToString());
            p.DbType=GetDbType(row["DATA_TYPE"].ToString()).ToString();
            p.Name=row["PARAMETER_NAME"].ToString().Replace("@","");
            p.IsOutput=(row["PARAMETER_MODE"].ToString() == "INOUT");
            p.CleanName=CleanUp(p.Name);
            result.Add(p);
        }


    }
    return result;
}

And then the class declaration from Settings.ttinclude

public class SP{
    public string Name;
    public string CleanName;
    public string ClassName;
    public List<SPParam> Parameters;
    public SP(){
        Parameters=new List<SPParam>();
    }        
    public string ArgList{
        get{
            StringBuilder sb=new StringBuilder();
            foreach(var par in Parameters){
                    if (par.ShouldBeNullable) { continue; }
                if(sb.Length != 0)
                {
                    sb.Append(", ");
                }
                sb.AppendFormat("{0}{1} {2}", par.SysType, par.ShouldBeNullable ? "?" : "", par.CleanName);
            }
            return sb.ToString();
        }
    }
}
public class SPParam{
    public string Name;
    public string CleanName;
    public string SysType;
    public string DbType;
    public bool IsOutput;
    public bool ShouldBeNullable
    {
     get
     {
            return IsOutput && (SysType == "int" || SysType == "bool" || SysType == "double" || SysType == "long" || SysType == "short" || SysType == "decimal");
     }
    }
}