SubSonic and T4 Templates

By brianmrushSubsonic, T4 Templates

Recently I have been using Subsonic 3.0 for a project. I really like this tool because I think it does just what it claims to do, namely generate a DAL layer. Granted the NHibernates / Entity Frameworks of the world may have more bells and whistles but sometimes those features really are not necessary. (I know shoot me but its true)

As I went through the process of generating my code, I noticed that I needed to make a few tweaks to the T4. More specifically, I tweaked the templates in sections that dealt with stored procedures. What I wanted was my generated code to be aware of input/output parameters within the stored procedures.

Here are the tweaks I made to the current T4 templates.

1) Settings.ttinclude

Change the SSParam class as follows

public class SPParam{
public string Name;
public string CleanName;
public string SysType;
public string DbType;
public string ParameterDirection;
}

We add the ParameterDirection property to hold the direction of the stored procedure parameter. A case in point is knowing if a parameter is an input or output param.

2) In SQLServer.ttinclude

Change the method GetSPParams as follows.


List GetSPParams(string spName){
var result=new List();
string[] restrictions = new string[3] { null, null, spName };
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.ParameterDirection = GetParamDirection(row["PARAMETER_MODE"].ToString());
p.CleanName=CleanUp(p.Name);
result.Add(p);
}

}
return result;
}

Add the method

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";
}

}

3. Inside StoredProcedure.tt

Change this line.


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

One thing that stinks is we can’t determine if a parameter has a default value or is a nullable parameter. It would be nice so we could generate parameters as nullable types.

Leave a Reply