CodeSmith学习笔记------

1.新建一个Code Smith Generator Template(C sharp)

2.一些常见标签的解释：

①外部变量：

<%@ Property Name="SampleStringProperty" Default="SomeValue" Type="System.String" %>

表示定义一个string类型的外部变量，在需要在生成的时候才输入，此属性有默认值，也可以由用户在右下角的属性栏里修改属性的值。

还有Optional：是否允许为空(即不输入)，Category：是说你声明的这个属性的类别(CodeSmith会按分类分开展示让你输入)。

②与数据库交互

CodeSmith与数据库的联系，在CodeSmith中自带一个程序集SchemaExplorer.dll，这个程序集中的类主要用于获取数据库中各种对象的结构。

1 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Optional="False" Description="源表名" %>
2 
3 <%@ Property Name="SourceDB" Type="SchemaExplorer.DatabaseSchema" Optional="False" %>
4 
5 <%@ Assembly Name="SchemaExplorer" %>
6 
7 <%@ Import Namespace="SchemaExplorer" %>

Assembly：引用程序集，Import：相当于using命名空间。

Type="SchemaExplorer.DatabaseSchema"此类型会在属性栏生成一个数据库的选择框，Type="SchemaExplorer.TableSchema"即表的选择框。
③自定义方法：

1 <script runat="template">
2 // My methods here.
3 public string SampleMethod()
4 {
5   return "Method output.";
6 }
7 </script>

<script runat="template"></script>标签内写的是自定义函数（C#代码）
④模板部分书写：
C#代码要用<%%>包括，值类型要使用<%=%>
例如：生成一个Model层的模板

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 Namespace Model
 6 {
 7     Class <%=TargetTable.Name%>
 8     {
 9         <%  for (int i=0;i<TargetTable.Columns.Count;i++)
10         {
11             SchemaExplorer.ColumnSchema col = TargetTable.Columns[i];%>
12             public <%=col.SystemType%> <%=col.Name%>{get;set;}
13       <%}%>
14     }
15 }

⑤一份完整的DAL的模板示例：

  1 <%@ CodeTemplate Language="C#" TargetLanguage="C#" 
  2     Src="ToolsCodeTemplate.cs" Inherits="ToolsCodeTemplate"%>
  3 <%@ Property Name="TargetTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="TargetTable that the object is based on." %>
  4 <%@ Property Name="ModelsNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
  5 <%@ Property Name="DALNamespace" Default="MyOffice.DAL" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
  6 <%@ Property Name="DALClassNameSurfix" Default="Service" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
  7 <%@ Assembly Name="SchemaExplorer" %>
  8 <%@ Assembly Name="System.Data" %>
  9 <%@ Import Namespace="SchemaExplorer" %>
 10 <%@ Import Namespace="System.Data" %>
 11 <%@ Import Namespace="System.Text.RegularExpressions" %>
 12 <% PrintHeader(); %>
 13 using System;
 14 using System.Collections.Generic;
 15 using System.Text;
 16 using System.Data;
 17 using System.Data.SqlClient;
 18 using <%= ModelsNamespace %>;
 19 
 20 namespace <%= DALNamespace %>
 21 {
 22     public partial class <%= GetDALClassName() %>
 23     {
 24         <%-- public static Book AddBook(Book book) --%>
 25         public <%= GetModelClassName() %> Add
 26             (<%= GetModelClassName() %> <%= GetModelParamName() %>)
 27         {
 28             <%if(IsIdentityPK())
 29             {%>
 30                 string sql ="<%= GetAutoIncInsertSQLLine()%>";
 31                 SqlParameter[] para = new SqlParameter[]
 32                     {
 33                         <%
 34                         for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
 35                         {
 36                             ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
 37                             
 38                         %>
 39                         new SqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
 40                         <%
 41                         }
 42                         %>
 43                     };
 44                     
 45                 <%= GetPKPropertyType() %> newId = (<%= GetPKPropertyType() %>)SqlHelper.ExecuteScalar(sql, para);
 46                 return GetBy<%= GetPKPropertyName() %>(newId);
 47             <%}else
 48             {%>
 49                 string sql ="<%= GetCommonInsertSQLLine()%>";
 50                 SqlParameter[] para = new SqlParameter[]
 51                     {
 52                         <%
 53                         for(int i=0; i<TargetTable.Columns.Count; i++)
 54                         {
 55                             ColumnSchema column = TargetTable.Columns[i];                            
 56                         %>
 57                         new SqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
 58                         <%
 59                         }
 60                         %>
 61                     };
 62                 SqlHelper.ExecuteNonQuery(sql, para);
 63                 return <%= GetModelParamName() %>;                
 64             <%}%>
 65         }
 66 
 67         <%-- public static bool DeleteBookById(int id) --%>
 68         public int DeleteBy<%= GetPKPropertyName() %>(<%= GetPKPropertyType() %> <%= GetPKParamName() %>)
 69         {
 70             string sql = "DELETE <%= TargetTable.Name %> WHERE <%= GetPKPropertyName() %> = @<%= GetPKPropertyName() %>";
 71 
 72            SqlParameter[] para = new SqlParameter[]
 73             {
 74                 new SqlParameter("@<%= GetPKName() %>", <%= GetPKParamName() %>)
 75             };
 76         
 77             return SqlHelper.ExecuteNonQuery(sql, para);
 78         }
 79         
 80                 
 81         <%-- public static bool ModifyBook(Book book) --%>
 82         public int Update(<%= GetModelClassName() %> <%= GetModelParamName() %>)
 83         {
 84             string sql =
 85                 "UPDATE <%= TargetTable.Name %> " +
 86                 "SET " +
 87             " <%= TargetTable.NonPrimaryKeyColumns[0].Name %> = @<%= TargetTable.NonPrimaryKeyColumns[0].Name %>" 
 88             <%
 89             for(int i=1; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
 90             {
 91                 ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];                
 92             %>
 93                 +", <%= column.Name %> = @<%= column.Name %>" 
 94             <%
 95             }
 96             %>
 97                
 98             +" WHERE <%= GetPKName() %> = @<%= GetPKName() %>";
 99 
100 
101             SqlParameter[] para = new SqlParameter[]
102             {
103                 new SqlParameter("@<%= GetPKName() %>", <%= GetModelParamName() %>.<%= GetPKName() %>)
104                 <%
105                 for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
106                 {
107                     ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
108                 %>
109                     ,new SqlParameter("@<%= column.Name %>", ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>))
110                 <%
111                 }
112                 %>
113             };
114 
115             return SqlHelper.ExecuteNonQuery(sql, para);
116         }        
117         
118         <%-- public static Book GetBookById(int id) --%>
119         public <%= GetModelClassName() %> GetBy<%= GetPKPropertyName() %>(<%= GetPKPropertyType() %> <%= GetPKParamName() %>)
120         {
121             string sql = "SELECT * FROM <%= TargetTable.Name %> WHERE <%= GetPKPropertyName() %> = @<%= GetPKPropertyName() %>";
122             using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@<%= GetPKPropertyName() %>", <%= GetPKParamName() %>)))
123             {
124                 if (reader.Read())
125                 {
126                     return ToModel(reader);
127                 }
128                 else
129                 {
130                     return null;
131                 }
132                }
133         }
134         
135         public <%= GetModelClassName() %> ToModel(SqlDataReader reader)
136         {
137             <%= GetModelClassName() %> <%= GetModelParamName() %> = new <%= GetModelClassName() %>();
138 
139             <% foreach(ColumnSchema column in TargetTable.Columns) %>
140             <% { %>
141             <%= GetModelParamName() %>.<%= GetPropertyName(column) %> = (<%=GetPropertyType(column)%>)ToModelValue(reader,"<%=column.Name%>");
142             <% } %>
143             return <%= GetModelParamName() %>;
144         }
145         
146         public int GetTotalCount()
147         {
148             string sql = "SELECT count(*) FROM <%= TargetTable.Name %>";
149             return (int)SqlHelper.ExecuteScalar(sql);
150         }
151         
152         public IEnumerable<<%= GetModelClassName() %>> GetPagedData(int minrownum,int maxrownum)
153         {
154             string sql = "SELECT * from(SELECT *,row_number() over(order by <%=this.GetPKName()%>) rownum FROM <%= TargetTable.Name %>) t where rownum>=@minrownum and rownum<=@maxrownum";
155             using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,
156                 new SqlParameter("@minrownum",minrownum),
157                 new SqlParameter("@maxrownum",maxrownum)))
158             {
159                 return ToModels(reader);                    
160             }
161         }
162         
163         public IEnumerable<<%= GetModelClassName() %>> GetAll()
164         {
165             string sql = "SELECT * FROM <%= TargetTable.Name %>";
166             using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql))
167             {
168                 return ToModels(reader);            
169             }
170         }
171         
172         protected IEnumerable<<%= GetModelClassName() %>> ToModels(SqlDataReader reader)
173         {
174             var list = new List<<%= GetModelClassName() %>>();
175             while(reader.Read())
176             {
177                 list.Add(ToModel(reader));
178             }    
179             return list;
180         }        
181         
182         protected object ToDBValue(object value)
183         {
184             if(value==null)
185             {
186                 return DBNull.Value;
187             }
188             else
189             {
190                 return value;
191             }
192         }
193         
194         protected object ToModelValue(SqlDataReader reader,string columnName)
195         {
196             if(reader.IsDBNull(reader.GetOrdinal(columnName)))
197             {
198                 return null;
199             }
200             else
201             {
202                 return reader[columnName];
203             }
204         }
205     }
206 }
207 <script runat="template">
208 public bool IsIdentityPK()
209 {
210     foreach(ColumnSchema column in TargetTable.Columns)
211     {
212         if((bool)column.ExtendedProperties["CS_IsIdentity"].Value) 
213         {
214             return true;
215         }
216     }
217     return false;
218 }
219 
220 ///////////////////////////////////////////////////////////////
221 // CLASS NAMES by Shen Bo
222 ///////////////////////////////////////////////////////////////
223 // UserService
224 public string GetDALClassName()
225 {
226     return     GetModelClassName() + DALClassNameSurfix;
227 }
228 // User
229 public string GetModelClassName()
230 {
231     return     GetModelClassName(TargetTable);
232 }
233 // user
234 public string GetModelMemberVarName()
235 {
236     return GetModelParamName();
237 }
238 // user
239 public string GetModelParamName()
240 {
241     return MakeCamel(GetModelClassName());
242 }
243 
244 
245 ///////////////////////////////////////////////////////////////
246 // INSERT SQL LINES by Shen Bo
247 ///////////////////////////////////////////////////////////////
248 public string GetAutoIncInsertSQLLine()
249 {
250     string result;
251     result = "INSERT INTO " + TargetTable.Name + " (";
252     foreach(ColumnSchema column in TargetTable.NonPrimaryKeyColumns)
253     {
254         result += column.Name + ", ";
255     }
256     result = result.Substring(0, result.Length-2);
257     result += ") ";
258     result+=" output inserted."+GetPKName();
259     result += " VALUES (";
260     foreach(ColumnSchema column in TargetTable.NonPrimaryKeyColumns)
261     {
262         result += "@" + column.Name + ", ";
263     }
264     result = result.Substring(0, result.Length-2);
265     result += ")";
266     return result;
267 }
268 
269 public string GetCommonInsertSQLLine()
270 {
271     string result;
272     result = "INSERT INTO " + TargetTable.Name + " (";
273     foreach(ColumnSchema column in TargetTable.Columns)
274     {
275         result += column.Name + ", ";
276     }
277     result = result.Substring(0, result.Length-2);
278     result += ") ";
279     result += " VALUES (";
280     foreach(ColumnSchema column in TargetTable.Columns)
281     {
282         result += "@" + column.Name + ", ";
283     }
284     result = result.Substring(0, result.Length-2);
285     result += ")";
286     return result;
287 }
288 
289 ///////////////////////////////////////////////////////////////
290 // PRIMARY KEY TYPE by Shen Bo
291 ///////////////////////////////////////////////////////////////
292 // int
293 public string GetPKPropertyType()
294 {
295     return     GetPKType(TargetTable);
296 }
297 
298 ///////////////////////////////////////////////////////////////
299 // PRIMARY KEY NAME by Shen Bo
300 ///////////////////////////////////////////////////////////////
301 // Id
302 public string GetPKPropertyName()
303 {
304     return MakePascal(GetPKName());
305 }
306 // id
307 public string GetPKParamName()
308 {
309     return GetPKMemberVarName();    
310 }
311 // id
312 public string GetPKMemberVarName()
313 {
314     return MakeCamel(GetPKName());    
315 }
316 // Id
317 public string GetPKName()
318 {
319     return GetPKName(TargetTable);
320 }
321 
322 public override string GetFileName()
323 {
324     return this.GetDALClassName() + ".cs";
325 }
326 
327 </script>