SubSonic T4 Templates / Stored Procedures / Nullable Parameters

One thing I noticed recently when working with Subsonic 3.0 and the T4 templates was that there was no way to detect if a stored procedure has nullable parameters.  After a bit of research I found this msdn article that explains:

“SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.”

Well that kind of stinks.  I want my cake and I want to eat it too.

I want my generated code to
a) Declare the parameters to stored procedures as nullable types
b) Ensure the code passes these parameters in as nullable types.

In order to accomplish this I changed the following in the T4 Templates.

1. Inside SQLServer.ttinclude

a. Add methods

public string GetStoredProcedureText(string procedureName)
{
string sql;
sql = @"EXEC sp_helptext '" + procedureName + "'";
SqlDataReader sprocReader;
StringBuilder sprocBuilder = new StringBuilder();
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
sprocReader = cmd.ExecuteReader();
while (sprocReader.Read())
{
sprocBuilder.Append(sprocReader.GetString(0));
}
return sprocBuilder.ToString();
}
}

public List<string> GetNullableParams(string procedureText)
{
string arguments = string.Empty;
string[] argumentArray = new string[0];
List<string> parameterNullibility = new List<string>();
procedureText = Regex.Replace(procedureText, @"\r\n", " ");
procedureText = Regex.Replace(procedureText, @"\s+", " ");
procedureText = Regex.Replace(procedureText, @"\s*,\s*", ",");
procedureText = Regex.Replace(procedureText, @"/\*.*?\*/", "");
string regex = @"CREATE\s+((PROC)|(PROCEDURE))\s+((\[.+?\]\.?){*}|(\S*))(?<Params>.*?)\s{1}as\s{1}";
RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture;
MatchCollection matches = Regex.Matches(procedureText, regex, options);
if (matches.Count > 0 && matches[0].Groups["Params"] != null)
{
Match match = matches[0];
arguments = match.Groups["Params"].ToString();
argumentArray = Regex.Split(arguments, ",");
foreach (string sArgument in argumentArray)
{
MatchCollection paramMatches = Regex.Matches(sArgument, @"(?<ParamName>@\S*)", options);
MatchCollection nullMatches = Regex.Matches(sArgument, @"(?<NullString>=\s*)", options);
if (paramMatches.Count > 0 && paramMatches[0].Groups["ParamName"] != null)
{
bool isNullDefault = (nullMatches.Count > 0 && nullMatches[0].Groups["NullString"] != null);
parameterNullibility.Add(paramMatches[0].Groups["ParamName"].ToString());
}
}
}
return parameterNullibility;
}

b. Change GetSPPArams

List GetSPParams(string spName)
{
var result = new List();
string[] restrictions = new string[3] { null, null, spName };
string procedureText;0
procedureText = GetStoredProcedureText(spName);
List nullableParams = this.GetNullableParams(procedureText);
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
var sprocs = conn.GetSchema("ProcedureParameters", restrictions);
using (SqlCommand cmd = new SqlCommand(spName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
}
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.ParameterMode = GetParamDirection(row["PARAMETER_MODE"].ToString()).ToString();
p.CleanName = CleanUp(p.Name);
if (nullableParams.Contains(row["PARAMETER_NAME"].ToString()))
p.Nullable = true;
result.Add(p);
}
}
return result;
}

2.  Inside Setting.ttinclude

a. Change SSParam class as follows


public class SPParam{
public string Name;
public string CleanName;
public string SysType;
public string DbType;
public string ParameterMode;
public bool Nullable;
}


b. Change ArgList property as follows.


public string ArgList{
get{
StringBuilder sb=new StringBuilder();
int loopCount=1;
foreach(var par in Parameters){
if (par.Nullable)
{
if ( par.SysType.ToString() != "string" && par.SysType.ToString() != "byte[]")
{
sb.AppendFormat("{0}{1} {2}", par.SysType, "?", par.CleanName);
}
else
{
sb.AppendFormat("{0} {1}", par.SysType, par.CleanName);
}
}
else
{
sb.AppendFormat("{0} {1}", par.SysType, par.CleanName);
}
if(loopCount<Parameters.Count)
sb.Append(",");
loopCount++;
}
return sb.ToString();
}
}
}

3.  Inside StoredProcedures.tt change the code to account for 1 and 2 above.

<#@ 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 StoredProcedure <#=sp.CleanName#>(<#=sp.ArgList#>){
StoredProcedure sp=new StoredProcedure(“<#=sp.Name#>”,this.Provider);
<#      foreach(var par in sp.Parameters){
if ( par.Nullable == true )
{
if (par.SysType == “string” || par.SysType == “byte[]”)
{
#>

sp.Command.AddParameter(“<#=par.Name#>”,<#=par.CleanName#>,DbType.<#=par.DbType#>,<#=par.ParameterMode#>);
<#                }
else
{
#>
sp.Command.AddParameter(“<#=par.Name#>”,(<#=par.SysType#>?)<#=par.CleanName#>,DbType.<#=par.DbType#>,<#=par.ParameterMode#>);
<#
}

}
else
{
#>
sp.Command.AddParameter(“<#=par.Name#>”,<#=par.CleanName#>,DbType.<#=par.DbType#>,<#=par.ParameterMode#>);
<#
}
}#>
return sp;
}
<#  }#>

}

}
<#  }#>