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() == "0")
                {
                    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();
        }

        private static string ChangeType(string type)
        {
            switch (type)
            {
                case "varchar": type = "string"; break;
                case "datetime": type = "DateTime"; break;
                case "bit": type = "bool"; break;
                case "DateTime": type = "DateTime"; break;
            }
            return type;
        }