SQL转C#模型工具-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_33474360/article/details/108622986

Sql查询

SELECT  
--表名=case when a.colorder=1 then d.name else '' end, 
--表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
--字段序号=a.colorder, 
字段名=a.name, 
--标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end, 
-- 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
--   SELECT name FROM sysindexes WHERE indid in(
--    SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
--    ))) then '√' else '' end, 
类型=b.name, 
-- 占用字节数=a.length, 
-- 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
-- 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
-- 允许空=case when a.isnullable=1 then '√'else '' end, 
-- 默认值=isnull(e.text,''), 
字段说明=isnull(g.[value],''),
C#属性=('/// <summary>'+ CHAR(10)+
'//' + CAST(isnull(g.value,' ')AS VARCHAR) + CHAR(10)+
'/// <summary>'+ CHAR(10)+
'public '+
(case when CHARINDEX('uniqueidentifier',b.name) > 0 then 'Guid'  
when CHARINDEX('int',b.name) > 0 then 'int'
when CHARINDEX('decimal',b.name) > 0 then 'decimal'
when CHARINDEX('datetime',b.name) > 0 then 'DateTime'
when CHARINDEX('nvarchar',b.name) > 0 then 'string'
end
) + '  ' +a.name + '  {get;set;}' + CHAR(10))
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id 
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
where d.name='ACClearBidDetailInfo' --如果只查询指定表,加上此条件   
order by a.id,a.colorder

来个例子

新建模型

 public class Table
    {
        public string Name { get; set; }

        public string Desc { get; set; }

        public List<Column> Columns { get; set; } = new List<Column>();
    }

    public class Column
    {
        public string Name { get; set; }

        public string Type { get; set; }

        public bool IsRequired { get; set; }

        public string Desc { get; set; }
    }

新建工具抽象Tool
新建抽象方法

/// <summary>
/// 获取所有表结构信息
/// </summary>
/// <returns></returns>
public abstract List<Table> GetTables();

/// <summary>
/// 获取C#字段类型
/// </summary>
/// <param name="columnType">数据库字段类型</param>
/// <returns></returns>
public abstract string GetCSharpType(string columnType);

执行方法

/// <summary>
///  开始执行
/// </summary>
/// <param name="path">导出的路径</param>
/// <param name="refNs">需要引用的命名空间</param>
/// /// <param name="ignoreFields">忽略的属性</param>
/// <param name="ns">当前命名空间</param>
/// <param name="inheritClass">继承基类或接口</param>
/// <returns></returns>
public string Execute(string path,string[] refNs, string ignoreFields, string ns = "", string inheritClass = "")
{
    List<string> ignoreFieldList = ignoreFields.Split(',').ToList();
    StringBuilder stringBuilder = new StringBuilder();
    foreach (var table in tables)
    {
        stringBuilder.Clear();
        foreach (var usingNs in refNs)
        {
            stringBuilder.AppendLine($"using {usingNs};");
        }
        stringBuilder.AppendLine();
        stringBuilder.AppendLine($"namespace {ns}");
        stringBuilder.AppendLine("{");
        stringBuilder.AppendLine("    /// <summary>");
        stringBuilder.AppendLine($"    /// {table.Desc.Replace("\n", ";").Replace("\r", ";").Replace("\r\n", ";")}");
        stringBuilder.AppendLine("    /// <summary>");
        stringBuilder.AppendLine($"    public class {table.Name}{(string.IsNullOrEmpty(inheritClass) ? "" : ":" + inheritClass)}");
        stringBuilder.AppendLine("    {");
        foreach (var column in table.Columns)
        {
            if (ignoreFieldList.Any(x => x == column.Name))
            {
                continue;
            }
            string cSharpType = GetCSharpType(column.Type);
            
            stringBuilder.AppendLine("        /// <summary>");
            stringBuilder.AppendLine($"        /// {column.Desc.Replace("\n", ";").Replace("\r", ";").Replace("\r\n", ";")}");
            stringBuilder.AppendLine("        /// <summary>");
            stringBuilder.AppendLine($"        public {cSharpType} {column.Name} {{ get;set; }}");
            stringBuilder.AppendLine();
        }
        stringBuilder.AppendLine("    }");
        stringBuilder.AppendLine("}");
        File.AppendAllText(path + "\\" + table.Name + ".cs", stringBuilder.ToString());
    }
    return "OK";
}

下面贴出SqlServer具体实现类

public class SqlServerTool : Tool
{
    public override string GetCSharpType(string columnType)
        {
            string cSharpType = string.Empty;
            switch (columnType)
            {
                case "uniqueidentifier":
                    cSharpType = "Guid";
                    break;
                case "int":
                    cSharpType = "int";
                    break;
                case "decimal":
                    cSharpType = "decimal";
                    break;
                case "datetime":
                    cSharpType = "DateTime";
                    break;
                case "nvarchar":
                case "varchar":
                    cSharpType = "string";
                    break;
                default:
                    break;
            }
            return cSharpType;
        }

    public override List<Table> GetTables()
        {
            DataTable tableNames = SqlServerSqlHelper.Fill(CommandType.Text, "select name from sysobjects where xtype='U'").Tables[0];
            string sql = string.Empty;
            List<Table> tables = new List<Table>();
            for (int i = 0; i < tableNames?.Rows.Count; i++)
            {
                Table table = new Table();
                table.Name = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(tableNames.Rows[i]["name"].ToString());
                sql = $@"SELECT  
                --表名=case when a.colorder=1 then d.name else '' end, 
                表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
                --字段序号=a.colorder, 
                字段名=a.name, 
                -- 标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end, 
                -- 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
                --   SELECT name FROM sysindexes WHERE indid in(
                --    SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
                --    ))) then '√' else '' end, 
                类型=b.name, 
                -- 占用字节数=a.length, 
                -- 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
                -- 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
                允许空=case when a.isnullable=1 then 1 else 0 end, 
                -- 默认值=isnull(e.text,''), 
                字段说明=isnull(g.[value],'')
                FROM syscolumns a 
                left join systypes b on a.xtype=b.xusertype 
                inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
                left join syscomments e on a.cdefault=e.id 
                left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
                left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
                where d.name='{table.Name}' --如果只查询指定表,加上此条件   
                order by a.id,a.colorder";
                DataTable columnInfo = SqlServerSqlHelper.Fill(CommandType.Text, sql).Tables[0];
                if (columnInfo.Rows.Count == 0)
                    continue;
                table.Desc = columnInfo?.Rows[0]["表说明"].ToString();
                for (int j = 0; j < columnInfo?.Rows.Count; j++)
                {
                    table.Columns.Add(new Column
                    {
                        Name = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(columnInfo.Rows[j]["字段名"].ToString()),
                        Type = columnInfo.Rows[j]["类型"].ToString(),
                        Desc = columnInfo.Rows[j]["字段说明"].ToString(),
                        IsRequired = Convert.ToBoolean(columnInfo.Rows[j]["允许空"])
                    });
                }
                tables.Add(table);
            }
            return tables;
        }
}

设计器大概长这样

在这里插入图片描述

调用代码

 Tool tool = new SqlServerTool();
 FolderBrowserDialog path = new FolderBrowserDialog();
 DialogResult result=  path.ShowDialog();
 if (result == DialogResult.OK)
 {
     string msg = tool.Execute(path.SelectedPath,tb_refNs.Lines,tb_ignoreFields.Text,tb_NameSpace.Text,tb_inheritClass.Text);
     MessageBox.Show(msg);
 }

效果如下
在这里插入图片描述