Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
NPoco/PetaPoco strong typed and named stored procedures T4 generator

NPoco/PetaPoco stored procedures with named strong type parameters

StoredProcedures.tt file automatically generates a C# code file with calsses and methods corresponding to your database stored procedure definitions. This is then used to simply call stored procedures within NPoco/PetaPoco and avoid magic strings and parameter type guessing. It also supports output parameters.

Stored procedures naming conventions

In order to have your stored procedure calls well structured there are particular yet simple naming conventions you should follow when creating your stored procedures:

  1. Name your stored procedures as ClassName_Method
  2. If a particular stored procedure shouldn't be parsed you should omit underscore character in its name; this will make it private from the perspective of your C# as it will only be accessible to other stored procedures but won't be parsed.

And that's it really. T4 will pick up all matching stored procedures, read their information and generate C# calling code with correctly typed and named parameters.

What about types and their compatibility?

There's a subset of all available types that this parser understands and uses. I've included most commonly used SQL types. If you'd like to add additional ones, simply add new ones to typeMapper dictionary.

All SQL types map to nullable value or reference C# equivalent types to satisfy possible value nullability. Parser can't know whether particular parameters can be nullable or not, therefore all generated C# code uses nullable typed parameters. This means that i.e. bit will be mapped to bool?, datetime to DateTime?, but nvarchar to string as it's a nullable (reference) type already.

End result

After you configure SQL database connection string (found early in T4 template in code line 37) parser generates static classes with matching static methods that return NPoco.Sql object instances that can be used with your C# directly when manipulating data. This means that it doesn't generate any new NPoco methods, but rather parameters for them. Majority of NPoco/PetaPoco methods have several overloads, one of them accepting Sql type parameter. This parser takes advantage of this fact.

Example

Suppose we write a stored procedure that creates a new user and then returns the newly generated record back so we can know user's ID right after it's been created (along with all other data). In modern web applications users usually get an activation email to activate their account and prove their email ownership.

create procedure dbo.User_Create (
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Email nvarchar(200),
    @PasswordHash char(60),
    @ActivationCode char(32) out
)
as
begin
    -- generate new activation code
    set @ActivationCode = ...

    -- insert user with activation code
    ...

    -- return newly created user
    select Id, FirstName, LastName, Email
    from dbo.User
    where Id = @@scope_identity;
end
go

Parser will generate Create method as part of a User class that can have several other methods as well if there are other User_... stored procedures.

internal static class StoredProcedures
{
    // other classes

    internal static partial class User
    {
        // other User methods
        
        public static Sql Create(string firstName, string lastName, string email, string passwordHash, out SqlParameter activationCode)
        {
            activationCode = new SqlParameter("@ActivationCode", SqlDbType.Char);
            activationCode.Direction = ParameterDirection.Output;
            activationCode.Size = 32;
            
            Sql result = Sql.Builder.Append(";exec dbo.[User_Create] @FirstName, @LastName, @Email, @PasswordHash, @ActivationCode out", new {
                FirstName = firstName,
                LastName = lastName,
                Email = email,
                PasswordHash = passwordHash,
                ActivationCode = activationCode
            });
            
            return result;
        }
        
        // other User methods
    }
    
    //other classes
}

You would then call/use this code in your C# code like so this:

// created NPoco/PetaPoco IDatabase instance
using (var db = Database.GetDatabase())
{
    SqlParameter generatedCode;
    
    // password has already been hashed elsewhere above in code
    User result = db.Single<User>(StoredProcedures.User.Create(firstName, lastName, email, passwordHash, out generatedCode));
    
    // use generatedCode to likely send user an activation email
    this.SendEmail(email, generatedCode.Value);
    
    return result;
}

I've deliberately provided a more complex example using an output parameter so you can see how it's used. Majority of stored procedures usually use input parameters only and calling code is much simpler as all you have to do is provide appropriate parameters and use them.

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
using System.CodeDom.Compiler;
using System.Data;
using System.Data.SqlClient;
using NPoco;
namespace Awesomi.Data
{
#region Generated code
/// <summary>Defines all strongly typed methods for calling stored procedures.</summary>
[GeneratedCode("TextTemplatingFileGenerator", "10")] // this attribute makes code analysis to skip this class
internal static class StoredProcedures
{
<#
ParseData();
Generate();
#>
}
#endregion
}
<#+ // Variables
string connectionString = "Data Source=YourDBServer;Initial Catalog=YourDB;User ID=SQLUserName;Password=Password";
string command = @"
select substring(sp.name, 0, patindex('%[_]%', sp.name)) as ClassName,
substring(sp.name, patindex('%[_]%', sp.name) + 1, len(sp.name)) as ProcName,
right(p.name, len(p.name) - 1) as ParamName,
t.name as ParamType,
p.max_length as ParamLength,
p.is_output as ParamOut
from sys.procedures sp
left join sys.parameters p
left join sys.types t
on t.system_type_id = p.system_type_id
on p.object_id = sp.object_id
where t.name <> 'sysname' and sp.name like '%[_]%'
order by sp.object_id, p.parameter_id";
Dictionary<string, ParameterTypeMap> typeMapper = new Dictionary<string, ParameterTypeMap> {
{"bit", new ParameterTypeMap { ClrType = "bool?", DbType = "SqlDbType.Bit", LengthDivisor = null }},
{"tinyint", new ParameterTypeMap { ClrType = "byte?", DbType = "SqlDbType.TinyInt", LengthDivisor = null }},
{"smallint", new ParameterTypeMap { ClrType = "short?", DbType = "SqlDbType.SmallInt", LengthDivisor = null }},
{"int", new ParameterTypeMap { ClrType = "int?", DbType = "SqlDbType.Int", LengthDivisor = null }},
{"bigint", new ParameterTypeMap { ClrType = "long?", DbType = "SqlDbType.BigInt", LengthDivisor = null }},
{"varchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.VarChar", LengthDivisor = 1 }},
{"nvarchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.NVarChar", LengthDivisor = 2 }},
{"char", new ParameterTypeMap { ClrType ="string" , DbType = "SqlDbType.Char", LengthDivisor = 1 }},
{"nchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.NChar", LengthDivisor = 2 }},
{"date", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.Date", LengthDivisor = null }},
{"datetime", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.DateTime", LengthDivisor = null }},
{"smalldatetime", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.SmallDateTime", LengthDivisor = null }},
{"time", new ParameterTypeMap { ClrType = "TimeSpan?", DbType = "SqlDbType.Time", LengthDivisor = null }},
{"varbinary", new ParameterTypeMap { ClrType = "byte[]", DbType = "SqlDbType.VarBinary", LengthDivisor = null }},
{"money", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Money", LengthDivisor = null }},
{"numeric", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Decimal", LengthDivisor = null }},
{"decimal", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Decimal", LengthDivisor = null }},
{"real", new ParameterTypeMap { ClrType = "float?", DbType = "SqlDbType.Real", LengthDivisor = null }},
{"float", new ParameterTypeMap { ClrType = "double?", DbType = "SqlDbType.Float", LengthDivisor = null }},
{"uniqueidentifier", new ParameterTypeMap { ClrType = "Guid?", DbType = "SqlDbType.UniqueIdentifier", LengthDivisor = null }}
};
List<ClassGroup> parsedClasses = new List<ClassGroup>();
#>
<#+ // Types
private class ClassGroup
{
public string Name = string.Empty;
public List<Procedure> Procedures = new List<Procedure>();
}
private class Procedure
{
public string Name = string.Empty;
public List<Parameter> Parameters = new List<Parameter>();
}
private class Parameter
{
public string Name = string.Empty;
public string Type = string.Empty;
public string ParamType = string.Empty;
public int ParamTypeLength = 0;
public bool IsOutput = false;
public bool IsLengthRequired = false;
public string AsVariable()
{
return this.Name[0].ToString().ToLowerInvariant() + Name.Substring(1);
}
}
private class ParameterTypeMap
{
public string ClrType = string.Empty;
public string DbType = string.Empty;
public int? LengthDivisor = null;
}
#>
<#+ // ParseData
private void ParseData()
{
using (var conn = new SqlConnection(connectionString))
{
using (var comm = new SqlCommand(command, conn))
{
conn.Open();
using (var dr = comm.ExecuteReader())
{
string className = string.Empty;
string procName = string.Empty;
while(dr.Read())
{
if (dr["ClassName"].ToString() != className)
{
className = dr["ClassName"].ToString();
procName = string.Empty;
parsedClasses.Add(new ClassGroup { Name = className });
}
if (dr["ProcName"].ToString() != procName)
{
procName = dr["ProcName"].ToString();
parsedClasses.Last().Procedures.Add(new Procedure { Name = procName });
}
if (dr["ParamName"] != DBNull.Value)
{
string paramType = dr["ParamType"].ToString();
parsedClasses.Last().Procedures.Last().Parameters.Add(new Parameter {
Name = dr["ParamName"].ToString(),
Type = typeMapper[paramType].ClrType,
ParamType = typeMapper[paramType].DbType,
IsLengthRequired = typeMapper[paramType].LengthDivisor != null,
ParamTypeLength = Convert.ToInt32(dr["ParamLength"]) / (typeMapper[paramType].LengthDivisor ?? 1),
IsOutput = Convert.ToBoolean(dr["ParamOut"])
});
}
}
}
}
}
}
#>
<#+ // Generate
private void Generate()
{
foreach (ClassGroup c in parsedClasses)
{
#>
#region <#= c.Name #> class
/// <summary>Defines all <#= c.Name #> related stored procedure calls.</summary>
internal static partial class <#= c.Name #>
{
<#+
foreach (Procedure sp in c.Procedures)
{
IList<Parameter> inputParams = sp.Parameters.Where(p => !p.IsOutput).ToList();
IList<Parameter> outputParams = sp.Parameters.Where(p => p.IsOutput).ToList();
#>
public static Sql <#= sp.Name #>(<#= string.Join(", ", inputParams.Select(p => string.Format("{0} {1}", p.Type, p.AsVariable()))) #><#= outputParams.Count > 0 ? ", " : string.Empty #><#= string.Join(", ", outputParams.Select(p => string.Format("out SqlParameter {0}", p.AsVariable()))) #>)
{
<#+
foreach(Parameter p in outputParams)
{
#>
<#= p.AsVariable() #> = new SqlParameter("@<#= p.Name #>", <#= p.ParamType #>);
<#= p.AsVariable() #>.Direction = ParameterDirection.Output;
<#+
if (p.IsLengthRequired)
{
#>
<#= p.AsVariable() #>.Size = <#= p.ParamTypeLength #>;
<#+
}
}
#>
Sql result = Sql.Builder.Append(";exec dbo.[<#= c.Name #>_<#= sp.Name #>] <#= string.Join(", ", sp.Parameters.Select(p => string.Format("@{0}{1}", p.Name, p.IsOutput ? " out" : ""))) #>", new {
<#= string.Join(",\n\t\t\t\t\t", sp.Parameters.Select(p => string.Format("{0} = {1}", p.Name, p.AsVariable()))) #>
});
return result;
}
<#+
}
#>
}
#endregion
<#+
}
}
#>

Hi Robert, I did some modification on your code like resolving user defined types and fixed a join on the main query. Here is the file, I taged the changes https://dl.dropboxusercontent.com/u/43394978/StoredProcedures.tt

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment