C#：CodeSmith根据数据库中的表创建C#数据模型Model + 因为没有钱买正版，所以附加自己写的小代码

对于C#面向对象的思想，我们习惯于将数据库中的表创建对应的数据模型；

但假如数据表很多时，我们手动增加模型类会显得很浪费时间；

这个时候有些人会用微软提供的EntityFrameWork，这个框架很强大，编写代码效率也很高，但很由于性能差，在复杂查询的时候生成的sql脚本效率不是很高，所以有的时候不会去使用它；

这个时候就会有CodeSmith来协助我们去完成那些费时费力的工作：

CodeSmith如何使用，网上也有很详细的介绍了，下面代码只是简单介绍

属性SourceDataBase是连接的数据库，CodeSmith提供连接数据库的方法很方便

属性NameSpace顾名思义就是命名空间

<%-- 
Name:批量生成实体类
Author: TitanChen
Description:批量将数据库中的表结构生成数据模型 
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Template description here." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Context" %>
<%@ Property Name="NameSpace" Type="String" Category="参数" Description="命名空间" Default="Blog.Core.Model" Optional="True"%>
<%@ Import Namespace="System.Text.RegularExpressions" %>
using System;
using System.Collections.Generic;
using System.Text;
 
namespace <%=NameSpace%>
{
    <% foreach(TableSchema SourceTable in SourceDatabase.Tables) { %>
    /// <summary>
    /// <%=GetClassName(SourceTable) +"模型"%>
    /// </summary>
    [Serializable]
    public class <%=GetClassName(SourceTable) %> : BaseModel
    {   
        /// <summary>
        /// 表名
        /// </summary>
        public static readonly string TableName = "<%=GetClassName(SourceTable) %>";
        
        /// <summary>
        /// 构造函数
        /// </summary>
        public <%=GetClassName(SourceTable) %>() : base(TableName)
        {
        }
        
        private Guid Id = Guid.Empty;
        <% foreach (ColumnSchema column in SourceTable.Columns) {%>
        /// <summary>
        /// <%=column.Description %>
        /// </summary>
        <% if(column.IsPrimaryKeyMember){ %>
            public Guid <%= GetPascalName(column) %> 
            {
                get{ return Id;}
                set
                {
                    Id = value;
                    if (value != null)
                    {
                        base.BaseId = value;
                    }
                }
            }
            
        <% }else{ %>
            public <%=GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; }
            
        <% } %>
        <% }%>
    }
    
    /// <summary>
    /// <%=GetClassName(SourceTable) +"数据模型"%>
    /// </summary>
    [Serializable]
    public class <%=GetClassName(SourceTable)+"ListData" %>
    {   
        /// <summary>
        /// 总记录数
        /// </summary>
        public int RecordCount { get; set; }
        
        /// <summary>
        /// 数据列表
        /// </summary>
        public List<<%=GetClassName(SourceTable)+"ListModel" %>> RecordList { get; set; }
    }
    
    /// <summary>
    /// <%=GetClassName(SourceTable) +"列表模型"%>
    /// </summary>
    [Serializable]
    public class <%=GetClassName(SourceTable)+"ListModel" %>
    {        
        <% foreach (ColumnSchema column in SourceTable.Columns) {%>
        <%if(new string[]{"IsDeleted"}.Contains(column.Name)){continue;} %>
        /// <summary>
        /// <%=column.Description %>
        /// </summary>
        public <%=GetCSharpVariableType(column)=="Guid" || GetCSharpVariableType(column)=="DateTime"?"string":GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; }
        
        <% }%>
    }
    <%} %>
}
<script runat="template">
 
public string MakeSingle(string name)
{
    return name;
}
public string GetCamelName(ColumnSchema column)
{
    return column.Name.Substring(0, 1).ToLower() + column.Name.Substring(1);
}
public string GetCamelName(string value)
{
    return value.Substring(0, 1).ToLower() + value.Substring(1);
}
public string GetPascalName(ColumnSchema column)
{
    return column.Name.Substring(0, 1).ToUpper() + column.Name.Substring(1);
}
public string GetPascalName(string value)
{
    return value.Substring(0, 1).ToUpper() + value.Substring(1);
}
public string GetClassName(TableSchema table)
{
    return GetPascalName(MakeSingle(table.Name));
}    
public string GetForeignKeyColumnType(ColumnSchema column)
{        
    return column.Table.ForeignKeys[0].PrimaryKeyTable.Name;
}
public string GetForeignKeyColumnName(ColumnSchema column)
{    
    if(column.Name.Substring(column.Name.Length-2).ToLower() == "id")
    {
        return column.Name.Substring(0,column.Name.Length-2);
    }
    else
    {
        return column.Name;
    }
}
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 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;
        }
    }
}    
</script>

　CodeSmith虽然方便，但是要安装和激活，这个是很麻烦的；而且每次生成都要打开CodeSmith去生成，不是很方便；

    于是我就照着原先在CodeSmith上模板写了个控制台应用程序，可以改写配合着bat使用，贼方便

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.IO;

namespace Blog.Core.Test
{
    public class Program
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string _connstr = "Data Source=localhost;Initial Catalog=Test;User Id=sa;Password=123456";

        /// <summary>
        /// 主函数
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            Console.Write("命名空间：");
            string namespaces = Console.ReadLine();
            Console.Write("文件名：");
            string filename = Console.ReadLine();
            Console.WriteLine("开始生成，请等待...");
            new Program().Generate(namespaces, filename);
            Console.WriteLine("生成成功...");
            Console.ReadKey();
        }

        /// <summary>
        /// 生成Model文件
        /// </summary>
        /// <param name="namespaces"></param>
        /// <param name="filename"></param>
        private void Generate(string namespaces, string filename)
        {
            byte[] myByte = Encoding.UTF8.GetBytes(BuildTemplete(namespaces));
            string filepath = Environment.CurrentDirectory + "\" + filename;
            if (File.Exists(filepath))
            {
                File.Delete(filepath);
            }
            using (FileStream fsWrite = new FileStream(filepath, FileMode.Append))
            {
                fsWrite.Write(myByte, 0, myByte.Length);
            };
        }

        /// <summary>
        /// 创建模板
        /// </summary>
        /// <param name="namespaces"></param>
        /// <returns></returns>
        private string BuildTemplete(string namespaces)
        {
            StringBuilder templete = new StringBuilder("using System;");
            templete.Append("using System.Collections.Generic;

");
            templete.AppendFormat("namespace {0}
{{
", namespaces);
            List<TableModel> tables = GetTables();
            foreach (var table in tables)
            {
                templete.AppendFormat("    #region {0}
", table.name);
                templete.Append("    /// <summary>
");
                templete.AppendFormat("    /// {0}模型
", table.name);
                templete.Append("    /// </summary>
");
                templete.Append("    [Serializable]
");
                templete.AppendFormat("    public class {0} : BaseModel
    {{", table.name);
                templete.Append("
");
                templete.Append("        /// <summary>
");
                templete.Append("        /// 表名
");
                templete.Append("        /// </summary>
");
                templete.AppendFormat("        public static readonly string TableName = "{0}";
", table.name);
                templete.Append("
");
                templete.Append("        /// <summary>
");
                templete.Append("        /// 构造函数
");
                templete.Append("        /// </summary>
");
                templete.AppendFormat("        public {0}() : base(TableName) {{ }}
", table.name);
                templete.Append("        private Guid Id = Guid.Empty;
");
                table.columns.ForEach(columu =>
                {
                    templete.Append("
");
                    templete.Append("        /// <summary>
");
                    templete.AppendFormat("        /// {0}
", columu.ColComment);
                    templete.Append("        /// </summary>
");
                    if (columu.IsPk)
                    {
                        templete.AppendFormat("        public Guid {0}
", columu.ColName);
                        templete.Append("        {
");
                        templete.Append("            get { return Id; }
");
                        templete.Append("            set
");
                        templete.Append("            {
");
                        templete.Append("                Id = value;
");
                        templete.Append("                if (value != null)
");
                        templete.Append("                {
");
                        templete.Append("                    base.BaseId = value;
");
                        templete.Append("                }
");
                        templete.Append("            }
");
                        templete.Append("        }
");
                    }
                    else
                    {
                        templete.AppendFormat("        public {0} {1} {{ get; set; }} {2}
", GetCSType(columu.ColType), columu.ColName, GetCSDefault(columu.ColDefault));
                    }
                });
                templete.Append("    }");

                templete.Append("
");

                templete.Append("    /// <summary>
");
                templete.AppendFormat("    /// {0}数据模型
", table.name);
                templete.Append("    /// </summary>
");
                templete.Append("    [Serializable]
");
                templete.AppendFormat("    public class {0}ListData
    {{", table.name);
                templete.Append("
");
                templete.Append("        /// <summary>
");
                templete.Append("        /// 总记录数
");
                templete.Append("        /// </summary>
");
                templete.Append("        public int RecordCount { get; set; }
");
                templete.Append("        /// <summary>
");
                templete.Append("
");
                templete.Append("        /// 数据列表
");
                templete.Append("        /// </summary>
");
                templete.AppendFormat("        public List<{0}ListModel> RecordList {{ get; set; }}
", table.name);
                templete.Append("    }");

                templete.Append("
");

                templete.Append("    /// <summary>
");
                templete.AppendFormat("    /// {0}列表模型
", table.name);
                templete.Append("    /// </summary>
");
                templete.Append("    [Serializable]
");
                templete.AppendFormat("    public class {0}ListModel
    {{", table.name);
                templete.Append("
");
                table.columns.ForEach(columu =>
                {
                    if (columu.ColName != "IsDeleted")
                    {
                        templete.Append("
");
                        templete.Append("        /// <summary>
");
                        templete.AppendFormat("        /// {0}
", columu.ColComment);
                        templete.Append("        /// </summary>
");
                        if (new string[] { "Guid", "DateTime" }.Contains(GetCSType(columu.ColType)))
                        {
                            templete.AppendFormat("        public string {0} {{ get; set; }}
", columu.ColName);
                        }
                        else
                        {
                            templete.AppendFormat("        public {0} {1} {{ get; set; }}
", GetCSType(columu.ColType), columu.ColName);
                        }
                    }
                });
                templete.Append("    }
");
                templete.Append("    #endregion
");
                templete.Append("
");
            }
            templete = templete.Remove(templete.Length - 2, 1);
            templete.Append("}");
            return templete.ToString();
        }

        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <returns></returns>
        private List<TableModel> GetTables()
        {
            List<TableModel> tables = new List<TableModel>();
            DataTable tabName = Query("SELECT name AS TableName FROM sysobjects WHERE xtype = 'U'");
            DataTable colName = Query(@"--获取表名、字段名称、字段类型、字段说明、字段默认值
                                        SELECT obj.name  AS TableName,--表名
                                               col.name  AS ColName,--列名
                                               typ.name  AS ColType,--字段类型
                                               cmt.value AS ColComment,--字段说明
                                               dft.text  AS ColDefault--字段默认值
                                        FROM   syscolumns col--字段
                                               INNER JOIN sysobjects obj--表
                                                       ON col.id = obj.id
                                                          AND obj.xtype = 'U'--表示用户表
                                               LEFT JOIN systypes typ--类型
                                                      ON col.xtype = typ.xusertype
                                               LEFT JOIN sys.extended_properties cmt--字段说明
                                                      ON col.id = cmt.major_id--表Id
                                                         AND col.colid = cmt.minor_id--字段Id
                                               LEFT JOIN syscomments dft--默认值
                                                      ON col.cdefault = dft.id
                                        ORDER  BY obj.name,
                                                  col.id ASC 
                                        ");
            DataTable pk = Query(@"--获取表的主键字段名
                                   SELECT CCU.COLUMN_NAME,
                                          TC.TABLE_NAME
                                   FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                                          INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
                                                  ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
                                   WHERE  TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                                   ");
            foreach (DataRow row in tabName.Rows)
            {
                TableModel table = new TableModel();
                table.name = row["TableName"].ToString(); ;
                table.columns = new List<ColumnModel>();
                DataRow[] cols = colName.Select(string.Format("TableName = '{0}'", row["TableName"].ToString()));
                DataRow[] pks = pk.Select(string.Format("TABLE_NAME = '{0}'", row["TableName"].ToString()));
                string primarykey = pks == null || pks.Length == 0 ? "" : pks[0]["COLUMN_NAME"].ToString();
                foreach (DataRow col in cols)
                {
                    ColumnModel column = new ColumnModel();
                    column.IsPk = primarykey == col["ColName"].ToString();
                    column.ColName = col["ColName"].ToString();
                    column.ColType = col["ColType"].ToString();
                    column.ColComment = col["ColComment"].ToString();
                    column.ColDefault = col["ColDefault"].ToString();
                    table.columns.Add(column);
                }
                tables.Add(table);
            }
            return tables;

        }

        /// <summary>
        /// 简单的SQL查询
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        private DataTable Query(string sqlString)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(_connstr))
            {
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sqlString;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取C#类型
        /// </summary>
        /// <param name="sqlType"></param>
        /// <returns></returns>
        private string GetCSType(string sqlType)
        {
            switch (sqlType)
            {
                case "datetime":
                    return "DateTime";
                case "int":
                    return "int";
                case "nchar":
                    return "string";
                case "nvarchar":
                    return "string";
                case "varchar":
                    return "string";
                case "text":
                    return "string";
                case "ntext":
                    return "string";
                case "uniqueidentifier":
                    return "Guid";
                case "decimal":
                    return "decimal";
                case "float":
                    return "float";
                case "bit":
                    return "byte";
                case "binary":
                    return "byte []";
                case "varbinary":
                    return "byte []";
                case "timestamp":
                    return "int";
                default:
                    return "";
            }
        }

        /// <summary>
        /// 获取C#默认值
        /// </summary>
        /// <param name="sqlValue"></param>
        /// <returns></returns>
        private string GetCSDefault(string sqlValue)
        {
            switch (sqlValue)
            {
                case "((0))":
                    return "= 0;";
                case "('')":
                    return "= string.Empty;";
                case "('00000000-0000-0000-0000-000000000000')":
                    return "= Guid.Empty;";
                default:
                    return "";
            }
        }
    }

    /// <summary>
    /// 表模型
    /// </summary>
    public class TableModel
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string name { get; set; }

        /// <summary>
        /// 表字段
        /// </summary>
        public List<ColumnModel> columns { get; set; }
    }

    /// <summary>
    /// 字段模型
    /// </summary>
    public class ColumnModel
    {
        /// <summary>
        /// 是否主键
        /// </summary>
        public bool IsPk { get; set; }

        /// <summary>
        /// 列名
        /// </summary>
        public string ColName { get; set; }

        /// <summary>
        /// 列类型
        /// </summary>
        public string ColType { get; set; }

        /// <summary>
        /// 列说明
        /// </summary>
        public string ColComment { get; set; }

        /// <summary>
        /// 列默认值
        /// </summary>
        public string ColDefault { get; set; }
    }
}