Google analytics script

Latest jQuery CDN with code tiggling.

Wednesday, 23 April 2014

Mapping SQL stored procedures to C# class methods with matching strong type parameters using T4 template

Blog post provides T4 template that generated strong typed C# code which maps to SQL stored procedures. It doesn't actually call those SPs, but rather prepares SQL queries to call stored procedures with various parameters. These provided T4 template queries are NPoco/PetaPoco compatible, so if you use a different DAL lib (maybe even simple .net Sql client), you'll have to adjust methods to generate and return different results (with .net Sql client it will likely be SqlCommand instance).

NPoco/PetaPoco DAL libraries use strings to provide database optimised queries and these are prone to typing errors which we aren't able to catch during compile time but rather during runtime, when effects can be much more devastating. That's why it would be great if we could somehow avoid these magic string values and just used C# code with string types and not make a mistake. We can't really avoid magic strings when writing direct TSQL but if we write stored procedures, we can do something about it.

We could self-write those C# calls that use stored procedures internally but that would just mean we moved typing errors to those methods, so this is not really a solution. Enter code generation with T4 Visual Studio templates. A t4 template will help us automate the process of writing C# methods while also making sure we get compile time errors when we change particular stored procedure's signature (sp name, parameter count or their types). So let's do just that.

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 with C# code in mind as `ClassName_Method` i.e. User_Create; underscore is important as it delimits these two parts;
  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# code as it will only be accessible to other stored procedures within SQL Server;
And that's it really. T4 will pick up all matching stored procedures, read their parameter 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 found in StoredProcedures.tt file.

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 already a nullable (reference) type.

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 (change using part to use PetaPoco) 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, some 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.

   1:  create procedure dbo.User_Add (
   2:      @FirstName nvarchar(50),
   3:      @LastName nvarchar(50),
   4:      @Email nvarchar(200),
   5:      @PasswordHash char(60),
   6:      @ActivationCode char(32) out
   7:  )
   8:  as
   9:  begin
  10:      -- generate new activation code
  11:      set @ActivationCode = ...
  12:   
  13:      -- insert user with activation code
  14:      ...
  15:   
  16:      -- return newly created user
  17:      select Id, FirstName, LastName, Email
  18:      from dbo.User
  19:      where Id = @@scope_identity;
  20:  end
  21:  go

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

   1:  internal static class StoredProcedures
   2:  {
   3:      // other classes
   4:   
   5:      internal static partial class User
   6:      {
   7:          // other User methods
   8:          
   9:          public static Sql Create(string firstName, string lastName, string email, string passwordHash, out SqlParameter activationCode)
  10:          {
  11:              activationCode = new SqlParameter("@ActivationCode", SqlDbType.Char);
  12:              activationCode.Direction = ParameterDirection.Output;
  13:              activationCode.Size = 32;
  14:              
  15:              Sql result = Sql.Builder.Append(";exec dbo.[User_Create] @FirstName, @LastName, @Email, @PasswordHash, @ActivationCode out", new {
  16:                  FirstName = firstName,
  17:                  LastName = lastName,
  18:                  Email = email,
  19:                  PasswordHash = passwordHash,
  20:                  ActivationCode = activationCode
  21:              });
  22:              
  23:              return result;
  24:          }
  25:          
  26:          // other User methods
  27:      }
  28:      
  29:      //other classes
  30:  }

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

   1:  // created NPoco/PetaPoco IDatabase instance
   2:  using (var db = Database.GetDatabase())
   3:  {
   4:      // password has already been hashed elsewhere above in code
   5:      User result = db.SingleOrDefault<User>(StoredProcedures.User.Create(firstName, lastName, email, passwordHash, generatedCode));
   6:      
   7:      // use generatedCode to likely send user an activation email
   8:      this.SendEmail(email, generatedCode);
   9:      
  10:      return result;
  11:  }

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.

T4 stored procedure parser template

Following code can also be found on GitHub as Gist where it may be forked and upgraded. In case you need additional (or different) functionality, simply fork it and change it to match your requirements. Maybe others will find your version useful and use it.

This is T4 file that you simply put in your Visual Studio project. Just make sure you give it an appropriate file extension (*.tt), so Visual Studio will automatically execute it for you.

   1:  <#@ template  debug="true" hostSpecific="true" #>
   2:  <#@ output extension=".generated.cs" #>
   3:  <#@ assembly name="System.Core" #>
   4:  <#@ assembly name="System.Data" #>
   5:  <#@ import namespace="System" #>
   6:  <#@ import namespace="System.Collections" #>
   7:  <#@ import namespace="System.Collections.Generic" #>
   8:  <#@ import namespace="System.Data" #>
   9:  <#@ import namespace="System.Data.SqlClient" #>
  10:  <#@ import namespace="System.Linq" #>
  11:  <#@ import namespace="System.Text" #>
  12:  <#@ import namespace="System.Text.RegularExpressions" #>
  13:  using System.CodeDom.Compiler;
  14:  using System.Data;
  15:  using System.Data.SqlClient;
  16:  using NPoco;
  17:   
  18:  namespace Your.Beloved.Namespace
  19:  {
  20:      #region Generated code
  21:   
  22:      /// <summary>Defines all strongly typed methods for calling stored procedures.</summary>
  23:      [GeneratedCode("TextTemplatingFileGenerator", "10")] // this attribute makes code analysis to skip this class
  24:      internal static class StoredProcedures
  25:      {
  26:  <#
  27:      ParseData();
  28:      Generate();
  29:  #>
  30:      }
  31:   
  32:      #endregion
  33:  }
  34:   
  35:  <#+ // Variables
  36:   
  37:      string connectionString = "Data Source=YourDBServer;Initial Catalog=YourDB;User ID=SQLUserName;Password=Password";
  38:   
  39:      string command = @"
  40:          select    substring(sp.name, 0, patindex('%[_]%', sp.name)) as ClassName,
  41:                  substring(sp.name, patindex('%[_]%', sp.name) + 1, len(sp.name)) as ProcName,
  42:                  right(p.name, len(p.name) - 1) as ParamName,
  43:                  t.name as ParamType,
  44:                  p.max_length as ParamLength,
  45:                  p.is_output as ParamOut
  46:          from sys.procedures sp
  47:              left join sys.parameters p
  48:                  left join sys.types t
  49:                  on t.system_type_id = p.system_type_id
  50:              on p.object_id = sp.object_id
  51:          where t.name <> 'sysname' and sp.name like '%[_]%'
  52:          order by sp.object_id, p.parameter_id";
  53:   
  54:      Dictionary<string, ParameterTypeMap> typeMapper = new Dictionary<string, ParameterTypeMap> {
  55:          {"bit", new ParameterTypeMap { ClrType = "bool?", DbType = "SqlDbType.Bit", LengthDivisor = null }},
  56:          {"tinyint", new ParameterTypeMap { ClrType = "byte?", DbType = "SqlDbType.TinyInt", LengthDivisor = null }},
  57:          {"smallint", new ParameterTypeMap { ClrType = "short?", DbType = "SqlDbType.SmallInt", LengthDivisor = null }},
  58:          {"int", new ParameterTypeMap { ClrType = "int?", DbType = "SqlDbType.Int", LengthDivisor = null }},
  59:          {"bigint", new ParameterTypeMap { ClrType = "long?", DbType = "SqlDbType.BigInt", LengthDivisor = null }},
  60:          {"varchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.VarChar", LengthDivisor = 1 }},
  61:          {"nvarchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.NVarChar", LengthDivisor = 2 }},
  62:          {"char", new ParameterTypeMap { ClrType ="string" , DbType = "SqlDbType.Char", LengthDivisor = 1 }},
  63:          {"nchar", new ParameterTypeMap { ClrType = "string", DbType = "SqlDbType.NChar", LengthDivisor = 2 }},
  64:          {"date", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.Date", LengthDivisor = null }},
  65:          {"datetime", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.DateTime", LengthDivisor = null }},
  66:          {"smalldatetime", new ParameterTypeMap { ClrType = "DateTime?", DbType = "SqlDbType.SmallDateTime", LengthDivisor = null }},
  67:          {"time", new ParameterTypeMap { ClrType = "TimeSpan?", DbType = "SqlDbType.Time", LengthDivisor = null }},
  68:          {"varbinary", new ParameterTypeMap { ClrType = "byte[]", DbType = "SqlDbType.VarBinary", LengthDivisor = null }},
  69:          {"money", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Money", LengthDivisor = null }},
  70:          {"numeric", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Decimal", LengthDivisor = null }},
  71:          {"decimal", new ParameterTypeMap { ClrType = "decimal?", DbType = "SqlDbType.Decimal", LengthDivisor = null }},
  72:          {"real", new ParameterTypeMap { ClrType = "float?", DbType = "SqlDbType.Real", LengthDivisor = null }},
  73:          {"float", new ParameterTypeMap { ClrType = "double?", DbType = "SqlDbType.Float", LengthDivisor = null }},
  74:          {"uniqueidentifier", new ParameterTypeMap { ClrType = "Guid?", DbType = "SqlDbType.UniqueIdentifier", LengthDivisor = null }}
  75:      };
  76:   
  77:      List<ClassGroup> parsedClasses = new List<ClassGroup>();
  78:  #>
  79:  <#+ // Types
  80:   
  81:      private class ClassGroup
  82:      {
  83:          public string Name = string.Empty;
  84:          public List<Procedure> Procedures = new List<Procedure>();
  85:      }
  86:   
  87:      private class Procedure
  88:      {
  89:          public string Name = string.Empty;
  90:          public List<Parameter> Parameters = new List<Parameter>();
  91:      }
  92:   
  93:      private class Parameter
  94:      {
  95:          public string Name = string.Empty;
  96:          public string Type = string.Empty;
  97:          public string ParamType = string.Empty;
  98:          public int ParamTypeLength = 0;
  99:          public bool IsOutput = false;
 100:          public bool IsLengthRequired = false;
 101:   
 102:          public string AsVariable()
 103:          {
 104:              return this.Name[0].ToString().ToLowerInvariant() + Name.Substring(1);
 105:          }
 106:      }
 107:   
 108:      private class ParameterTypeMap
 109:      {
 110:          public string ClrType = string.Empty;
 111:          public string DbType = string.Empty;
 112:          public int? LengthDivisor = null;
 113:      }
 114:  #>
 115:   
 116:  <#+ // ParseData
 117:   
 118:      private void ParseData()
 119:      {
 120:          using (var conn = new SqlConnection(connectionString))
 121:          {
 122:              using (var comm = new SqlCommand(command, conn))
 123:              {
 124:                  conn.Open();
 125:                  using (var dr = comm.ExecuteReader())
 126:                  {
 127:                      string className = string.Empty;
 128:                      string procName = string.Empty;
 129:   
 130:                      while(dr.Read())
 131:                      {
 132:                          if (dr["ClassName"].ToString() != className)
 133:                          {
 134:                              className = dr["ClassName"].ToString();
 135:                              procName = string.Empty;
 136:                              
 137:                              parsedClasses.Add(new ClassGroup { Name = className });
 138:                          }
 139:   
 140:                          if (dr["ProcName"].ToString() != procName)
 141:                          {
 142:                              procName = dr["ProcName"].ToString();
 143:   
 144:                              parsedClasses.Last().Procedures.Add(new Procedure { Name = procName });
 145:                          }
 146:   
 147:                          if (dr["ParamName"] != DBNull.Value)
 148:                          {
 149:                              string paramType = dr["ParamType"].ToString();
 150:                              parsedClasses.Last().Procedures.Last().Parameters.Add(new Parameter {
 151:                                  Name = dr["ParamName"].ToString(),
 152:                                  Type = typeMapper[paramType].ClrType,
 153:                                  ParamType = typeMapper[paramType].DbType,
 154:                                  IsLengthRequired = typeMapper[paramType].LengthDivisor != null,
 155:                                  ParamTypeLength = Convert.ToInt32(dr["ParamLength"]) / (typeMapper[paramType].LengthDivisor ?? 1),
 156:                                  IsOutput = Convert.ToBoolean(dr["ParamOut"])
 157:                              });
 158:                          }
 159:                      }
 160:                  }
 161:              }
 162:          }
 163:      }
 164:  #>
 165:  <#+ // Generate
 166:   
 167:      private void Generate()
 168:      {
 169:          foreach (ClassGroup c in parsedClasses)
 170:          {
 171:  #>
 172:          #region <#= c.Name #> class
 173:   
 174:          /// <summary>Defines all <#= c.Name #> related stored procedure calls.</summary>
 175:          internal static partial class <#= c.Name #>
 176:          {
 177:  <#+
 178:              foreach (Procedure sp in c.Procedures)
 179:              {
 180:                  IList<Parameter> inputParams = sp.Parameters.Where(p => !p.IsOutput).ToList();
 181:                  IList<Parameter> outputParams = sp.Parameters.Where(p => p.IsOutput).ToList();
 182:  #>
 183:   
 184:              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()))) #>)
 185:              {
 186:  <#+
 187:                  foreach(Parameter p in outputParams)
 188:                  {
 189:  #>
 190:                  <#= p.AsVariable() #> = new SqlParameter("@<#= p.Name #>", <#= p.ParamType #>);
 191:                  <#= p.AsVariable() #>.Direction = ParameterDirection.Output;
 192:  <#+
 193:                      if (p.IsLengthRequired)
 194:                      {
 195:  #>
 196:                  <#= p.AsVariable() #>.Size = <#= p.ParamTypeLength #>;
 197:   
 198:  <#+
 199:                      }
 200:                  }
 201:  #>
 202:                  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 {
 203:                      <#= string.Join(",\n\t\t\t\t\t", sp.Parameters.Select(p => string.Format("{0} = {1}", p.Name, p.AsVariable()))) #>
 204:                  });
 205:   
 206:                  return result;
 207:              }
 208:  <#+
 209:              }
 210:  #>
 211:          }
 212:   
 213:          #endregion
 214:   
 215:  <#+
 216:          }
 217:      }
 218:  #>

No comments:

Post a Comment