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:
- 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; - 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;
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
This is a fully moderated comments section. All spam comments are marked as spam without exception. Don't even try.
Note: only a member of this blog may post a comment.