CodeSmith使用存档

IDAL

生成之前要在sqlserver配置管理器确认sql服务是否开启。

<%@ CodeTemplate Language="C#" TargetLanguage="Text" PageEncoding="UTF-8" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Property Name="SourceTable" PageEncosing="UTF-8" Type="SchemaExplorer.TableSchema" Default="" Optional="True" Category="Tables" Description="This is a Table." %>
<%@ Property Name="ProjectName" Type="System.String" Default="IBeiFeng" Optional="False" Category="Strings" Description="This is Project Name"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
​
//
//:<%= DateTime.Now.ToLongDateString()%>
///
​
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System;
using <%= ProjectName%>Model;
​
​
namespace <%= ProjectName%>IDAL
{
    ///
    //<%= GetClassName(SourceTable)%>
    ///
    public interface <%= GetInterfaceName(SourceTable)%>
    {
         <%= GetClassName(SourceTable)%>Entity Select<%= GetClassName(SourceTable)%>ByID(<%= GetCSharpVariableType(SourceTable.PrimaryKey.MemberColumns[0])%> t<%= GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>);
        
         IList<<%= GetClassName(SourceTable)%>Entity> GetAll<%= GetClassName(SourceTable)%>();
                
        <%
        foreach(ColumnSchema column in SourceTable.ForeignKeyColumns)
        {
        %>      
         IList<<%= GetClassName(SourceTable)%>Entity> GetAll<%= GetClassName(SourceTable)%>By<%= column.Name%>(<%= GetCSharpVariableType(column)%> t<%= GetMemberVariableName(column)%>);
        
        <%
        }       
        %>
    
          int Insert<%= GetClassName(SourceTable)%>(<%= GetClassName(SourceTable)%>Entity t_<%= GetClassName(SourceTable)%>);
        
          int Update<%= GetClassName(SourceTable)%>(<%= GetClassName(SourceTable)%>Entity t_<%= GetClassName(SourceTable)%>);
        
          int Delete<%= GetClassName(SourceTable)%>(<%= GetCSharpVariableType(SourceTable.PrimaryKey.MemberColumns[0])%> t<%= GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>);
    }
}
​
​
<script runat="template">
    public string GetMemberVariableDeclarationStatement(ColumnSchema column)
{
    return GetMemberVariableDeclarationStatement("protected", column);
}
​
public string GetColumns(TableSchema table)
{
    string temp="";
    foreach(ColumnSchema column in table.Columns)
    {
        temp+="@"+GetPascalMemberVariableName(column)+",";
    }
    return temp.Substring(0,temp.Length-1);
}
​
public string GetUpdateColumns(TableSchema table)
{
    string temp="";
    foreach(ColumnSchema column in table.NonPrimaryKeyColumns)
    {
        temp+=column.Name+"=@"+GetPascalMemberVariableName(column)+",";
    }
    temp=temp.Substring(0,temp.Length-1);
    temp+=" where "+table.PrimaryKey.MemberColumns[0].Name+"="+"@"+GetPascalMemberVariableName(table.PrimaryKey.MemberColumns[0]);
    return temp;
}
​
public string GetPascalMemberVariableName(ColumnSchema column)
{
    string propertyName = GetPropertyName(column);  
    
    string temp= propertyName.Substring(0,1).ToUpper()+propertyName.Substring(1);
    
    return temp;
}
​
public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
{
    string statement = protectionLevel + " ";
    statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
    
    string defaultValue = GetMemberVariableDefaultValue(column);
    if (defaultValue != "")
    {
        statement += " = " + defaultValue;
    }
    
    statement += ";";
    
    return statement;
}
​
public string GetReaderAssignmentStatement(ColumnSchema column, int index)
{
    string statement = "if (!reader.IsDBNull(" + index.ToString() + ")) ";
    statement += GetMemberVariableName(column) + " = ";
    
    if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
    
    statement += "reader." + GetReaderMethod(column) + "(" + index.ToString() + ");";
    
    return statement;
}
​
public string GetCamelCaseName(string value)
{
    return value.Substring(0, 1).ToLower() + value.Substring(1);
}
​
public string GetMemberVariableName(ColumnSchema column)
{
    string propertyName = GetPropertyName(column);
    string memberVariableName = "_" + GetCamelCaseName(propertyName);
    
    return memberVariableName;
}
​
public string GetPropertyName(ColumnSchema column)
{
    string propertyName = column.Name;
    
    if (propertyName == column.Table.Name + "Name") return "Name";
    if (propertyName == column.Table.Name + "Description") return "Description";
    
    if (propertyName.EndsWith("TypeCode")) propertyName = propertyName.Substring(0, propertyName.Length - 4);
    
    return propertyName;
}
        
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
    switch (column.DataType)
    {
        case DbType.Guid:
        {
            return "Guid.Empty";
        }
        case DbType.AnsiString:
        case DbType.AnsiStringFixedLength:
        case DbType.String:
        case DbType.StringFixedLength:
        {
            return "String.Empty";
        }
        default:
        {
            return "";
        }
    }
}
​
public string GetCSharpVariableType(ColumnSchema column)
{
    if (column.Name.EndsWith("TypeCode")) return column.Name;
    
    switch (column.DataType)
    {
        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.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";
        default:
        {
            return "__UNKNOWN__" + column.NativeType;
        }
    }
}
​
public string GetReaderMethod(ColumnSchema column)
{
    switch (column.DataType)
    {
        case DbType.Byte:
        {
            return "GetByte";
        }
        case DbType.Int16:
        {
            return "GetInt16";
        }
        case DbType.Int32:
        {
            return "GetInt32";
        }
        case DbType.Int64:
        {
            return "GetInt64";
        }
        case DbType.AnsiStringFixedLength:
        case DbType.AnsiString:
        case DbType.String:
        case DbType.StringFixedLength:
        {
            return "GetString";
        }
        case DbType.Boolean:
        {
            return "GetBoolean";
        }
        case DbType.Guid:
        {
            return "GetGuid";
        }
        case DbType.Currency:
        case DbType.Decimal:
        {
            return "GetDecimal";
        }
        case DbType.DateTime:
        case DbType.Date:
        {
            return "GetDateTime";
        }
        case DbType.Binary:
        {
            return "GetBytes";
        }
        default:
        {
            return "__SQL__" + column.DataType;
        }
    }
}
​
public string GetClassName(TableSchema table)
{
    if (table.Name.EndsWith("s"))
    {
        return table.Name.Substring(0, table.Name.Length - 1);
    }
    else
    {
        return table.Name;
    }
}
​
public string GetInterfaceName(TableSchema table)
{
    string t="";
    if (table.Name.EndsWith("s"))
    {
        t= table.Name.Substring(0, table.Name.Length - 1);
    }
    else
    {
        t= table.Name;
    }
    return "I"+t[0].ToString().ToUpper()+t.Substring(1);
}
public string GetPascalCaseName(string value)
{
    return value.Substring(0, 1).ToUpper() + value.Substring(1);
}
​
public string GetSqlDbType(ColumnSchema column)
{
    switch (column.NativeType)
    {
        case "bigint": return "BigInt";
        case "binary": return "Binary";
        case "bit": return "Bit";
        case "char": return "Char";
        case "datetime": return "DateTime";
        case "decimal": return "Decimal";
        case "float": return "Float";
        case "image": return "Image";
        case "int": return "Int";
        case "money": return "Money";
        case "nchar": return "NChar";
        case "ntext": return "NText";
        case "numeric": return "Decimal";
        case "nvarchar": return "NVarChar";
        case "real": return "Real";
        case "smalldatetime": return "SmallDateTime";
        case "smallint": return "SmallInt";
        case "smallmoney": return "SmallMoney";
        case "sql_variant": return "Variant";
        case "sysname": return "NChar";
        case "text": return "Text";
        case "timestamp": return "Timestamp";
        case "tinyint": return "TinyInt";
        case "uniqueidentifier": return "UniqueIdentifier";
        case "varbinary": return "VarBinary";
        case "varchar": return "VarChar";
        default: return "__UNKNOWN__" + column.NativeType;
    }
}
​
public string GetPrimaryKeyType(TableSchema table)
{
    if (table.PrimaryKey != null)
    {
        if (table.PrimaryKey.MemberColumns.Count == 1)
        {
            return GetCSharpVariableType(table.PrimaryKey.MemberColumns[0]);
        }
        else
        {
            throw new ApplicationException("This template will not work on primary keys with more than one member column.");
        }
    }
    else
    {
        throw new ApplicationException("This template will only work on tables with a primary key.");
    }
}
​
public override string GetFileName()
{
    return this.GetClassName(this.SourceTable) + ".cs";
}
​
​
</script>

BLL

<%@ Template Language="C#" TargetLanguage="Text"  Encoding="utf-8"%>
<%@ Property Name="SourceTable" Default="" Type="SchemaExplorer.TableSchema" %>
<%@ Property Name="ProjectName" Type="System.String" Default="eyblog" Optional="False" Category="Strings" Description="this is my blog" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer"%>
​
​
<%@ Map Name="SqlCSharp" Src="Sql-CSharp" Description="System to C# Type Map" %>
<%@ Map Name="DbDataReader" Src="DbType-DataReaderMethod" Description="DbType to DataReader Method Map" %>
<%@ Map Name="SqlNativeSqlDb" Src="SqlNativeType-SqlDbType" Description="SqlNativeType to SqlDbType Map" %>
<%@ Map Name="DbTypeCSharp" Src="DbType-CSharp" Description="DbType to CSharp Map" %>
​
using System.Collections.Generic;
using System.Data.SqlClient;
using System;
using <%=ProjectName%>Model;
using <%=ProjectName%>DBUtility;
using <%=ProjectName%>IDAL;
using <%=ProjectName%>DALFactory;
namespace <%=ProjectName%>BLL
{
​
    public static class <%=GetClassName(SourceTable)%>
    {
        public static <%=ProjectName%>IDAL.<%= GetInterfaceName(SourceTable)%> dal=<%=ProjectName%>DALFactory.DataAccess.Create<%=GetClassName(SourceTable)%>();
        //查询单个数据
        public  static <%=GetClassName(SourceTable)%>Entity Select<%=GetClassName(SourceTable)%>ByID(<%=GetCSharpVariableType(SourceTable.PrimaryKey.MemberColumns[0])%> t<%=GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>)
        {
            <%=GetClassName(SourceTable)%>Entity  temp=null;
            try
            {
               temp=dal.Select<%=GetClassName(SourceTable)%>ByID( t<%=GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            } 
            return temp;
        }
        //外键查询,几个外键生成几个方法
        <%
        foreach(ColumnSchema column in SourceTable.ForeignKeyColumns)
        {%>
             public static IList<<%=GetClassName(SourceTable)%>Entity> GetAll<%=GetClassName(SourceTable)%>By<%=column.Name%>(<%=GetCSharpVariableType(column)%> t<%=GetMemberVariableName(column)%>)
             {
                IList<<%=GetClassName(SourceTable)%>Entity> temp=null;
                   try
                {
                   temp=dal.GetAll<%=GetClassName(SourceTable)%>By<%=column.Name%>( t<%=GetMemberVariableName(column)%>);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                } 
                return temp;
             } 
        <%}
        %>
        //添加数据
        public  static int Insert<%=GetClassName(SourceTable)%>(<%=GetClassName(SourceTable)%>Entity T_<%=GetClassName(SourceTable)%>)
        {
                int i=-1;
                try
                {
                   i=dal.Insert<%=GetClassName(SourceTable)%>(T_<%=GetClassName(SourceTable)%>);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                } 
                return i;
        }
        //修改数据
        public  static int Update<%=GetClassName(SourceTable)%>(<%=GetClassName(SourceTable)%>Entity T_<%=GetClassName(SourceTable)%>)
        {
             int i=-1;
                try
                {
                   i=dal.Update<%=GetClassName(SourceTable)%>(T_<%=GetClassName(SourceTable)%>);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                } 
                return i;
        }
        //删除数据，主键，不考虑联合主键
        public  static int Delete<%=GetClassName(SourceTable)%>(<%=GetCSharpVariableType(SourceTable.PrimaryKey.MemberColumns[0])%> t<%=GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>)
        {
                int i=-1;
                try
                {
                   i=dal.Delete<%=GetClassName(SourceTable)%>(t<%=GetMemberVariableName(SourceTable.PrimaryKey.MemberColumns[0])%>);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                } 
                return i;
        }
    }
}
​
<script runat="template">
//自定义方法
public string GetPascalMemberVariableName(ColumnSchema column)  
{  
    string propertyName = GetPropertyName(column);  
    string memberVariableName = "_" + GetCamelCaseName(propertyName);  
      
    string temp= propertyName.Substring(0,1).ToUpper()+propertyName.Substring(1);  
    return temp;  
} 
public string GetColumns(TableSchema table)
{
    string temp="";
    foreach(ColumnSchema column in table.Columns)
    {
        temp+="@"+GetPascalMemberVariableName(column)+",";
    }
    return temp.Substring(0,temp.Length-1);
}
​
public string GetUpdateColumns(TableSchema table)
{
    string temp="";
    foreach(ColumnSchema column in table.NonPrimaryKeyColumns)
    {
        temp+=column.Name+"=@"+GetPascalMemberVariableName(column)+",";
    }
    temp=temp.Substring(0,temp.Length-1);
    //空格是Sql语句控制符
    temp+=" "+"where "+table.PrimaryKey.MemberColumns[0].Name+"="+"@"+GetPascalMemberVariableName(table.PrimaryKey.MemberColumns[0]);
    return temp;
}
​
//------end
public string GetMemberVariableDeclarationStatement(ColumnSchema column)
{
    return GetMemberVariableDeclarationStatement("private", column);
}
​
public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
{
    string statement = protectionLevel + " ";
    statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
    
    string defaultValue = GetMemberVariableDefaultValue(column);
    if (defaultValue != "")
    {
        statement += " = " + defaultValue;
    }
    
    statement += ";";
    
    return statement;
}
​
public string GetReaderAssignmentStatement(ColumnSchema column, int index)
{
    string statement = "if (!reader.IsDBNull(" + index.ToString() + ")) ";
    statement += GetMemberVariableName(column) + " = ";
    
    if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
    
    statement += "reader." + GetReaderMethod(column) + "(" + index.ToString() + ");";
    
    return statement;
}
​
public string GetCamelCaseName(string value)
{
    return value.Substring(0, 1).ToLower() + value.Substring(1);
}
​
public string GetMemberVariableName(ColumnSchema column)
{
    string propertyName = GetPropertyName(column);
    string memberVariableName = "_" + GetCamelCaseName(propertyName);
    
    return memberVariableName;
}
​
public string GetPropertyName(ColumnSchema column)
{
    string propertyName = column.Name;
    
    if (propertyName == column.Table.Name + "Name") return "Name";
    if (propertyName == column.Table.Name + "Description") return "Description";
    
    if (propertyName.EndsWith("TypeCode")) propertyName = propertyName.Substring(0, propertyName.Length - 4);
    
    return propertyName;
}
        
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
    switch (column.DataType)
    {
        case DbType.Guid:
        {
            return "Guid.Empty";
        }
        case DbType.AnsiString:
        case DbType.AnsiStringFixedLength:
        case DbType.String:
        case DbType.StringFixedLength:
        {
            return "String.Empty";
        }
        default:
        {
            return "";
        }
    }
}
​
public string GetCSharpVariableType(ColumnSchema column)
{
    if (column.Name.EndsWith("TypeCode")) return column.Name;
​
    return DbTypeCSharp[column.DataType.ToString()];    
}
​
public string GetReaderMethod(ColumnSchema column)
{
    return DbDataReader[column.DataType.ToString()];
}
​
public string GetClassName(TableSchema table)
{
    if (table.Name.EndsWith("s"))
    {
        return table.Name.Substring(0, table.Name.Length - 1);
    }
    else
    {
        return table.Name;
    }
}
public string GetInterfaceName(TableSchema table)
{
    string t="";
    if (table.Name.EndsWith("s"))
    {
        t= table.Name.Substring(0, table.Name.Length - 1);
    }
    else
    {
        t= table.Name;
    }
    return "I"+t[0].ToString().ToUpper()+t.Substring(1);
}
public string GetSqlDbType(ColumnSchema column)
{
    return SqlNativeSqlDb[column.NativeType.ToString()];
}
​
public string GetPrimaryKeyType(TableSchema table)
{
    if (table.PrimaryKey != null)
    {
        if (table.PrimaryKey.MemberColumns.Count == 1)
        {
            return GetCSharpVariableType(table.PrimaryKey.MemberColumns[0]);
        }
        else
        {
            throw new ApplicationException("This template will not work on primary keys with more than one member column.");
        }
    }
    else
    {
        throw new ApplicationException("This template will only work on tables with a primary key.");
    }
}
​
public override string GetFileName()
{
    return this.GetClassName(this.SourceTable) + ".cs";
}
</script>

​

生成以上类的工具类：

生成的目标类 _mappingTemplate = CompileTemplate(CodeTemplateInfo.DirectoryName + "BLL.cst");

    private string _outputDirectory;//输出路径
    private DatabaseSchema _sourceDatabase;//数据库
    private CodeTemplate _mappingTemplate;//映射模版

Tools类

<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="This Template Generates C# domain objects for NHibernate" Debug="true" %>
<%@ Property Name="ProjectName" Type="System.String" Default="Blog" Optional="False" Category="Strings" Description="This is Project Name"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Windows.Forms.Design" %>
​
<% this.generate();%>
​
<script runat="template">
    private string _outputDirectory;//输出路径
    private DatabaseSchema _sourceDatabase;//数据库
    private CodeTemplate _mappingTemplate;//映射模版
    
    [Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))]
    [Category("Class")]
    [Description("The folder to save the generated class files.")]
    public string OutputDirectory
    {
        get {return _outputDirectory;}
        set {_outputDirectory= value;}
    }
    
    [Category("Database")]
    [Description("Database that the mapping file should be based on.")]
    public DatabaseSchema SourceDatabase {
        get { return _sourceDatabase; }
        set { _sourceDatabase = value; }
    }
    
    [Browsable(false)]
    public CodeTemplate MappingTemplate
    {
        get
        {
            if (_mappingTemplate == null)
                _mappingTemplate = CompileTemplate(CodeTemplateInfo.DirectoryName + "BLL.cst");
​
            return _mappingTemplate;
        }   
    }
    
    
    public CodeTemplate CompileTemplate(string templateName)
    {
        CodeTemplateCompiler compiler = new CodeTemplateCompiler(templateName);
        compiler.Compile();
​
        if (compiler.Errors.Count == 0)
        {
            return compiler.CreateInstance();
        }
        else
        {
            for (int i = 0; i < compiler.Errors.Count; i++)
            {
                Response.WriteLine(compiler.Errors[i].ToString());
            }
            return null;
        }
​
    }
    
    public string GetClassName(TableSchema table)
    {
        if (table.Name.EndsWith("s"))
        {
            return table.Name.Substring(0, table.Name.Length - 1);
        }
        else
        {
            return table.Name;
        }
    }
    
    public void generate()
    {
        foreach(TableSchema table in _sourceDatabase.Tables)
        {
            Response.WriteLine("generate "+table.Name+"...");   
            
            string classFileName = GetClassName(table);
            //interface
            //classFileName=classFileName[0].ToString().ToUpper()+classFileName.Substring(1);
            //classFileName ="I"+classFileName+ ".cs";
            //class 
            classFileName += ".cs";
            
            classFileName = Path.Combine(OutputDirectory, classFileName);
            
            this.MappingTemplate.SetProperty("SourceTable", table);
            this.MappingTemplate.SetProperty("ProjectName", ProjectName);
​
            this.MappingTemplate.RenderToFile(classFileName, true);
            
            Response.WriteLine("generate "+table.Name+"   Complete");   
        }
    }
</script>