这里提供两个CodeSmith代码模板示例,关于使用教程下面提供了下载:
Entity.cst
DAL.cst
Entity.cst
<%@ CodeTemplate Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8"
Description="Generates a class including a special informational header" %>
<%@ Property Name="NameSpace" Type="String"
Category="Context"
Description="命名空间"%>
<%@ Property Name="ClassDescription" Type="String"
Category="Context"
Description="类注释" %>
<%@ Property Name="ClassName" Type="String"
Category="Context"
Description="类名" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="数据库表名" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.IO" %>
<script runat="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
string param = "@" + column.Name + " " + column.NativeType;
switch (column.DataType)
{
case DbType.Decimal:
{
param += "(" + column.Precision + ", " + column.Scale + ")";
break;
}
default:
{
if (column.Size > 0)
{
param += "(" + column.Size + ")";
}
break;
}
}
return param;
}
//获取UPDATE语句
public string GetUpdateSQL()
{
string str = "UPDATE " + SourceTable.Name + " SET ";
for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++)
{
str = str + SourceTable.NonPrimaryKeyColumns[i].Name + "=@" + SourceTable.NonPrimaryKeyColumns[i].Name;
if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) {
str = str + ", ";
}
}
str = str + " WHERE ";
for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++)
{
str = str + SourceTable.PrimaryKey.MemberColumns[i].Name + "=@" + SourceTable.PrimaryKey.MemberColumns[i].Name;
if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) {
str = str + ", ";
}
}
return str;
}
//获取INSERT语句
public string GetInsertSQL()
{
string str1 = "";
string str2 = "";
string str = "";
for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++)
{
str1 = str1 + SourceTable.NonPrimaryKeyColumns[i].Name;
str2 = str2 + "@" + SourceTable.NonPrimaryKeyColumns[i].Name;
if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) {
str1 = str1 + ", ";
str2 = str2 + ", ";
}
}
str = "INSERT INTO " + SourceTable.Name + " (" + str1 + ") VALUES (" + str2 + ")";
return str;
}
//获取构造函数上面的参数
public string GetAllColunmsWithType()
{
string str = "";
for (int i = 0; i < SourceTable.Columns.Count; i++)
{
str = str + GetDataType(SourceTable.Columns[i]) + " " + SourceTable.Columns[i].Name;
if (i < SourceTable.Columns.Count - 1) {
str = str + ", ";
}
}
return str;
}
public string GetColunmFuction(ColumnSchema column)
{
return "";
//column.Description
}
//获取字段名注释
public string GetColunmDescription(ColumnSchema column)
{
return column.Description;
}
//获取数据库字段对应的C#数据类型
public string GetDataType(ColumnSchema column)
{
return DataType2CSharpType(column.DataType);
}
public string GetFunctionForDr(ColumnSchema column)
{
System.Data.DbType dbType = column.DataType;
string functionName = "";
switch (dbType)
{
case DbType.AnsiString:
functionName = "String";
break;
case DbType.AnsiStringFixedLength:
functionName = "String";
break;
case DbType.Binary:
functionName = "Byte";
break;
case DbType.Boolean:
functionName = "Boolean";
break;
case DbType.Byte:
functionName = "Byte";
break;
case DbType.Currency:
functionName = "Decimal";
break;
case DbType.Date:
functionName = "DateTime";
break;
case DbType.DateTime:
functionName = "DateTime";
break;
case DbType.DateTime2:
functionName = "DateTime";
break;
case DbType.DateTimeOffset:
functionName = "DateTime";
break;
case DbType.Decimal:
functionName = "Decimal";
break;
case DbType.Double:
functionName = "Double";
break;
case DbType.Guid:
functionName = "Guid";
break;
case DbType.Int16:
functionName = "Short";
break;
case DbType.Int32:
functionName = "Int";
break;
case DbType.Int64:
functionName = "Long";
break;
case DbType.Object:
functionName = "Object";
break;
case DbType.SByte:
functionName = "Sbyte";
break;
case DbType.Single:
functionName = "Float";
break;
case DbType.String:
functionName = "String";
break;
case DbType.StringFixedLength:
functionName = "String";
break;
case DbType.Time:
functionName = "TimeSpan";
break;
case DbType.UInt16:
functionName = "Ushort";
break;
case DbType.UInt32:
functionName = "Uint";
break;
case DbType.UInt64:
functionName = "Ulong";
break;
case DbType.VarNumeric:
functionName = "Decimal";
break;
case DbType.Xml:
functionName = "String";
break;
default:
functionName = "Object";
break;
}
return "Get" + functionName;
}
//将数据库类型转换为C#类型
public string DataType2CSharpType(System.Data.DbType dbType)
{
switch (dbType)
{
case DbType.AnsiString:
return "string";
case DbType.AnsiStringFixedLength:
return "string";
case DbType.Binary:
return "byte[]";
case DbType.Boolean:
return "bool";
case DbType.Byte:
return "byte";
case DbType.Currency:
return "decimal";
case DbType.Date:
return "DateTime";
case DbType.DateTime:
return "DateTime";
case DbType.DateTime2:
return "DateTime";
case DbType.DateTimeOffset:
return "DateTime";
case DbType.Decimal:
return "decimal";
case DbType.Double:
return "double";
case DbType.Guid:
return "Guid";
case DbType.Int16:
return "short";
case DbType.Int32:
return "int";
case DbType.Int64:
return "long";
case DbType.Object:
return "object";
case DbType.SByte:
return "sbyte";
case DbType.Single:
return "float";
case DbType.String:
return "string";
case DbType.StringFixedLength:
return "string";
case DbType.Time:
return "TimeSpan";
case DbType.UInt16:
return "ushort";
case DbType.UInt32:
return "uint";
case DbType.UInt64:
return "ulong";
case DbType.VarNumeric:
return "decimal";
case DbType.Xml:
return "string";
default:
return "object";
}
}
public string GetClassPropertyName()
{
string str = ClassName.ToString().Substring(0,1).ToLower() + ClassName.Substring(1,ClassName.Length -1);
return str;
}
private string templateOutputDirectory = "";
[Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))]
[Optional, NotChecked]
[Category("OutputInfo")]
[Description("输出结果的目录。")]
[DefaultValue("")]
public string OutputDirectory
{
get
{
return templateOutputDirectory;
}
set
{
if (value.EndsWith("\\")) value = value.Substring(0, value.Length - 1);
templateOutputDirectory = value;
}
}
</script>
using System;
namespace <%=NameSpace %>
{
/// <summary>
/// <%=ClassDescription %>
/// </summary>
public class <%=ClassName %>
{
//<%=GetUpdateSQL()%>
//<%=GetInsertSQL()%>
/// <summary>
/// 构造函数
/// </summary>
public <%=ClassName %>()
{
}
/// <summary>
/// 带参数构造函数
/// </summary>
<%foreach (ColumnSchema column in SourceTable.Columns)
{
%>
/// <param name="<%=column.Name%>"><%=GetColunmDescription(column)%></param>
<%
}
%>
public <%=ClassName %>(<%=GetAllColunmsWithType()%>)
{
<%foreach (ColumnSchema column in SourceTable.Columns)
{
%>
this.<%=column.Name%> = <%=column.Name%>;
<%
}
%>
}
<% foreach (ColumnSchema column in SourceTable.Columns)
{
%>
///<summary>
/// <%=GetColunmDescription(column)%>
/// </summary>
public <%=GetDataType(column)%> <%=column.Name%>
{
get; set;
}
<%
}
%>
/* private class <%=ClassName%>Mapper : IRowMapper<<%=ClassName%>>
{
<%=ClassName%> <%=GetClassPropertyName()%> = null;
public <%=ClassName%> mapRow(DataReader dr, int rowNum)
{
<%=GetClassPropertyName()%> = new <%=ClassName%>();
<%foreach (ColumnSchema column in SourceTable.Columns)
{
%>
<%=GetClassPropertyName()%>.<%=column.Name%> = dr.<%=GetFunctionForDr(column)%>("<%=column.Name%>");
<%
}
%>
return <%=GetClassPropertyName()%>;
}
}*/
/* <%foreach (ColumnSchema column in SourceTable.Columns)
{
%>sqlData.addParamter("<%=column.Name%>", <%=GetClassPropertyName()%>.<%=column.Name%>);
<%
}
%>*/
}
}
<%
//if(OutputDirectory != "")
//{
// this.RenderToFile(@"" + OutputDirectory + "\\" + ClassName + ".cs",true);
//}
%>
DAL.cst
<%@ CodeTemplate Language="C#" TargetLanguage="Text" ResponseEncoding="UTF-8" Src="" Inherits="" Debug="False" CompilerVersion="v3.5" Description="Template description here." %>
<%@ Property Name="NameSpace" Type="String"
Category="Context"
Description="DAL接口命名空间"%>
<%@ Property Name="ClassDescription" Type="String"
Category="Context"
Description="类注释" %>
<%@ Property Name="EntityName" Type="String"
Category="Context"
Description="实体名" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="数据表" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.IO" %>
<script runat="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
string param = "@" + column.Name + " " + column.NativeType;
switch (column.DataType)
{
case DbType.Decimal:
{
param += "(" + column.Precision + ", " + column.Scale + ")";
break;
}
default:
{
if (column.Size > 0)
{
param += "(" + column.Size + ")";
}
break;
}
}
return param;
}
//获取UPDATE语句
public string GetUpdateSQL()
{
string str = "UPDATE " + SourceTable.Name + " SET ";
for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++)
{
str = str + SourceTable.NonPrimaryKeyColumns[i].Name + "=@" + SourceTable.NonPrimaryKeyColumns[i].Name;
if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) {
str = str + ", ";
}
}
str = str + " WHERE ";
for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++)
{
str = str + SourceTable.PrimaryKey.MemberColumns[i].Name + "=@" + SourceTable.PrimaryKey.MemberColumns[i].Name;
if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) {
str = str + ", ";
}
}
return str;
}
//获取INSERT语句
public string GetInsertSQL()
{
string str1 = "";
string str2 = "";
string str = "";
for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++)
{
str1 = str1 + SourceTable.NonPrimaryKeyColumns[i].Name;
str2 = str2 + "@" + SourceTable.NonPrimaryKeyColumns[i].Name;
if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) {
str1 = str1 + ", ";
str2 = str2 + ", ";
}
}
str = "INSERT INTO " + SourceTable.Name + " (" + str1 + ") VALUES (" + str2 + ")";
return str;
}
//获取delete语句
public string GetDeleteSQL()
{
string str = "DELETE FROM " + SourceTable.Name +" WHERE ";
for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++)
{
str = str + SourceTable.PrimaryKey.MemberColumns[i].Name + "=@" + SourceTable.PrimaryKey.MemberColumns[i].Name;
if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) {
str = str + ", ";
}
}
return str;
}
//获取按主键
public string GetSelectByIdSQL()
{
string str = "SELECT * FROM " + SourceTable.Name + " WHERE ";
for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++)
{
str = str + SourceTable.PrimaryKey.MemberColumns[i].Name + "=@" + SourceTable.PrimaryKey.MemberColumns[i].Name;
if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) {
str = str + ", ";
}
}
return str;
}
//获取构造函数上面的参数
public string GetAllColunmsWithType()
{
string str = "";
for (int i = 0; i < SourceTable.Columns.Count; i++)
{
str = str + GetDataType(SourceTable.Columns[i]) + " " + SourceTable.Columns[i].Name;
if (i < SourceTable.Columns.Count - 1) {
str = str + ", ";
}
}
return str;
}
public string GetColunmFuction(ColumnSchema column)
{
return "";
//column.Description
}
//获取字段注释
public string GetColunmDescription(ColumnSchema column)
{
return column.Description;
}
//获取数据库字段对应的C#数据类型
public string GetDataType(ColumnSchema column)
{
return DataType2CSharpType(column.DataType);
}
public string GetFunctionForDr(ColumnSchema column)
{
System.Data.DbType dbType = column.DataType;
string functionName = "";
switch (dbType)
{
case DbType.AnsiString:
functionName = "String";
break;
case DbType.AnsiStringFixedLength:
functionName = "String";
break;
case DbType.Binary:
functionName = "Byte";
break;
case DbType.Boolean:
functionName = "Boolean";
break;
case DbType.Byte:
functionName = "Byte";
break;
case DbType.Currency:
functionName = "Decimal";
break;
case DbType.Date:
functionName = "DateTime";
break;
case DbType.DateTime:
functionName = "DateTime";
break;
case DbType.DateTime2:
functionName = "DateTime";
break;
case DbType.DateTimeOffset:
functionName = "DateTime";
break;
case DbType.Decimal:
functionName = "Decimal";
break;
case DbType.Double:
functionName = "Double";
break;
case DbType.Guid:
functionName = "Guid";
break;
case DbType.Int16:
functionName = "Short";
break;
case DbType.Int32:
functionName = "Int";
break;
case DbType.Int64:
functionName = "Long";
break;
case DbType.Object:
functionName = "Object";
break;
case DbType.SByte:
functionName = "Sbyte";
break;
case DbType.Single:
functionName = "Float";
break;
case DbType.String:
functionName = "String";
break;
case DbType.StringFixedLength:
functionName = "String";
break;
case DbType.Time:
functionName = "TimeSpan";
break;
case DbType.UInt16:
functionName = "Ushort";
break;
case DbType.UInt32:
functionName = "Uint";
break;
case DbType.UInt64:
functionName = "Ulong";
break;
case DbType.VarNumeric:
functionName = "Decimal";
break;
case DbType.Xml:
functionName = "String";
break;
default:
functionName = "Object";
break;
}
return "Get" + functionName;
}
//将数据库类型转换为C#类型
public string DataType2CSharpType(System.Data.DbType dbType)
{
switch (dbType)
{
case DbType.AnsiString:
return "string";
case DbType.AnsiStringFixedLength:
return "string";
case DbType.Binary:
return "byte[]";
case DbType.Boolean:
return "bool";
case DbType.Byte:
return "byte";
case DbType.Currency:
return "decimal";
case DbType.Date:
return "DateTime";
case DbType.DateTime:
return "DateTime";
case DbType.DateTime2:
return "DateTime";
case DbType.DateTimeOffset:
return "DateTime";
case DbType.Decimal:
return "decimal";
case DbType.Double:
return "double";
case DbType.Guid:
return "Guid";
case DbType.Int16:
return "short";
case DbType.Int32:
return "int";
case DbType.Int64:
return "long";
case DbType.Object:
return "object";
case DbType.SByte:
return "sbyte";
case DbType.Single:
return "float";
case DbType.String:
return "string";
case DbType.StringFixedLength:
return "string";
case DbType.Time:
return "TimeSpan";
case DbType.UInt16:
return "ushort";
case DbType.UInt32:
return "uint";
case DbType.UInt64:
return "ulong";
case DbType.VarNumeric:
return "decimal";
case DbType.Xml:
return "string";
default:
return "object";
}
}
public string GetClassPropertyName()
{
string str = EntityName.ToString().Substring(0,1).ToLower() + EntityName.Substring(1,EntityName.Length -1);
return str;
}
private string templateOutputDirectory = "";
[Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))]
[Optional, NotChecked]
[Category("OutputInfo")]
[Description("输出结果的目录。")]
[DefaultValue("")]
public string OutputDirectory
{
get
{
return templateOutputDirectory;
}
set
{
if (value.EndsWith("\\")) value = value.Substring(0, value.Length - 1);
templateOutputDirectory = value;
}
}
</script>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using GTJ.Model;
namespace <%=NameSpace %>.SQLImpl
{
/// <summary>
/// <%=ClassDescription %>
/// </summary>
public class <%=EntityName%>DALImpl : I<%=EntityName%>DAL
{
private class <%=EntityName%>Mapper : IRowMapper<<%=EntityName%>>
{
<%=EntityName%> <%=GetClassPropertyName()%> = null;
public <%=EntityName%> mapRow(DataReader dr, int rowNum)
{
<%=GetClassPropertyName()%> = new <%=EntityName%>();
<%foreach (ColumnSchema column in SourceTable.Columns)
{
%>
<%=GetClassPropertyName()%>.<%=column.Name%> = dr.<%=GetFunctionForDr(column)%>("<%=column.Name%>");
<%
}
%>
return <%=GetClassPropertyName()%>;
}
}
//添加
public int add(<%=EntityName%> <%=GetClassPropertyName()%>)
{
string sql = "<%=GetInsertSQL()%>";
SQLData sqlData = new SQLData(sql);
<%foreach (ColumnSchema column in SourceTable.NonPrimaryKeyColumns)
{
%>
sqlData.addParamter("<%=column.Name%>", <%=GetClassPropertyName()%>.<%=column.Name%>);
<%
}
%>
return SqlHelper.ExecuteNonQuery(SqlHelper.ConnString,sqlData);
}
//更新
public int update(<%=EntityName%> <%=GetClassPropertyName()%>)
{
string sql = "<%=GetUpdateSQL()%>";
SQLData sqlData = new SQLData(sql);
<%foreach (ColumnSchema column in SourceTable.Columns)
{
%>
sqlData.addParamter("<%=column.Name%>", <%=GetClassPropertyName()%>.<%=column.Name%>);
<%
}
%>
return SqlHelper.ExecuteNonQuery(SqlHelper.ConnString,sqlData);
}
//删除
public int delete(int id)
{
string sql = "<%=GetDeleteSQL()%>";
SQLData sqlData = new SQLData(sql);
<%foreach (ColumnSchema column in SourceTable.PrimaryKey.MemberColumns)
{
%>
sqlData.addParamter("<%=column.Name%>", id);
<%
}
%>
return SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, sqlData);
}
//按主键查询
public <%=EntityName%> findById(int id)
{
<%=EntityName%> <%=GetClassPropertyName()%> = null;
string sql = "<%=GetSelectByIdSQL()%>";
SQLData sqlData = new SQLData(sql);
<%foreach (ColumnSchema column in SourceTable.PrimaryKey.MemberColumns)
{
%>
sqlData.addParamter("<%=column.Name%>", id);
<%
}
%>
IList<<%=EntityName%>> <%=GetClassPropertyName()%>s = SqlHelper.ExecuteReader<<%=EntityName%>>(SqlHelper.ConnString, sqlData, new <%=EntityName%>Mapper());
if (<%=GetClassPropertyName()%>s.Count > 0)
{
<%=GetClassPropertyName()%> = <%=GetClassPropertyName()%>s.ElementAt(0);
}
return <%=GetClassPropertyName()%>;
}
//查询所有
public IList<<%=EntityName%>> findAllForList()
{
string sql = "SELECT * FROM <%=SourceTable.Name%>";
SQLData sqlData = new SQLData(sql);
IList<<%=EntityName%>> <%=GetClassPropertyName()%>s = SqlHelper.ExecuteReader<<%=EntityName%>>(SqlHelper.ConnString, sqlData, new <%=EntityName%>Mapper());
return <%=GetClassPropertyName()%>s;
}
//查询所有
public DataTable findAllForTable()
{
string sql = "SELECT * FROM <%=SourceTable.Name%>";
SQLData sqlData = new SQLData(sql);
return SqlHelper.ExecuteTable(SqlHelper.ConnString,sqlData);
}
}
}
namespace <%=NameSpace %>
{
/// <summary>
/// <%=ClassDescription %>
/// </summary>
public interface I<%=EntityName%>DAL
{
//添加
int add(<%=EntityName%> <%=GetClassPropertyName()%>);
//更新
int update(<%=EntityName%> <%=GetClassPropertyName()%>);
//删除
int delete(int id);
//按主键查询
<%=EntityName%> findById(int id);
//查询所有
IList<<%=EntityName%>> findAllForList();
//查询所有
DataTable findAllForTable();
}
}
<%
//if(OutputDirectory != "")
//{
// this.RenderToFile(@"" + OutputDirectory + "\\" + EntityName + ".cs",true);
//}
%>