SQL Table 自动生成Net底层-生成实体层Mapping

获取数据库表名、表结构

        public static DataTable GetSqlTables(string filterSql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select name from sysobjects where xtype='U' ");
            if (!string.IsNullOrEmpty(filterSql))
            {
                sb.Append(filterSql);
            }
            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }

        public static DataTable GetSQLTableInfo(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT  CASE WHEN col.colorder = 1 THEN obj.name
                                      ELSE ''
                                 END AS 表名,
                                col.name AS 列名 ,
                                ISNULL(ep.[value], '') AS 列说明 ,
                                t.name AS 数据类型 ,
                                col.length AS 长度 ,
                                ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
                                CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '1'
                                     ELSE ''
                                END AS 标识 ,
                                CASE WHEN EXISTS ( SELECT   1
                                                   FROM     dbo.sysindexes si
                                                            INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                                                      AND si.indid = sik.indid
                                                            INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                                                      AND sc.colid = sik.colid
                                                            INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                                                      AND so.xtype = 'PK'
                                                   WHERE    sc.id = col.id
                                                            AND sc.colid = col.colid ) THEN '1'
                                     ELSE ''
                                END AS 主键 ,
                                CASE WHEN col.isnullable = 1 THEN '1'
                                     ELSE ''
                                END AS 允许空 ,
                                ISNULL(comm.text, '') AS 默认值
                        FROM    dbo.syscolumns col
                                LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
                                inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                                                 AND obj.xtype = 'U'
                                                                 AND obj.status >= 0
                                LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
                                LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                                              AND col.colid = ep.minor_id
                                                                              AND ep.name = 'MS_Description'
                                LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                                                 AND epTwo.minor_id = 0
                                                                                 AND epTwo.name = 'MS_Description'
                        WHERE   obj.name = '{0}'--表名
                        ORDER BY col.colorder ;
                         ;", tableName);

            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }

自动生成实体类Mapping

        public static string DataTableToClass(DataTable dt, string nameSpace, string className)
        {
            StringBuilder reval = new StringBuilder();
            StringBuilder propertiesValue = new StringBuilder();

            for (var i = 0; i < dt.Rows.Count; i++)
            {
                string typeName = ChangeType(dt.Rows[i]["数据类型"].ToString());
                propertiesValue.AppendFormat(@"
        /// <summary>
        /// {0}
        /// </summary>", dt.Rows[i]["列说明"]);
                if (dt.Rows[i]["允许空"].ToString() == "" || typeName == "string")
                {
                    propertiesValue.AppendFormat(@"
        public {0} {1} {2}", typeName, dt.Rows[i]["列名"], "{get;set;}");
                }
                else
                {
                    propertiesValue.AppendFormat(@"
        public Nullable<{0}> {1} {2}", typeName, dt.Rows[i]["列名"], "{get;set;}");
                }
            }

            reval.AppendFormat(@"       
using System;
using System.Collections.Generic;

namespace {2}.Mapping
{{
    public class {0}
    {{                  
        {1}
    }}
}}", className, propertiesValue, nameSpace);

            return reval.ToString();
        }

        public static string ChangeType(string type)
        {
            switch (type)
            {
                case "varchar": type = "string"; break;
                case "datetime": type = "DateTime"; break;
                case "bit": type = "bool"; break;
                case "tinyint": type = "int"; break;
                case "nvarchar": type = "string"; break;
                case "smallint": type = "int"; break;
                case "text": type = "string"; break;
            }
            return type;
        }