欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-05 08:45:45
获取数据库表名、表结构 public static DataTable GetSqlTables(string filterSql) { StringBuilder sb = new StringBuilder(); sb.Append(" select name from sysobjects w ......

获取数据库表名、表结构

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];
        }
View Code

自动生成实体类Mapping

SQL Table 自动生成Net底层-生成实体层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;
        }
View Code