MSSQL数据库表 批量转c#实体类

MSSQL数据库表，批量转c#实体类 ，我知道网上有现成的，可是我就想自己写个来玩玩
这个工具的关键点，在于获取到表的所有字段名和类型，然后就是拼接字符串的事了
直接上代码

首先就是获取所有数据库名，数据库下的所有表名和表里的所有字段，具体怎么获取，拿sql到数据库里跑一下就清楚了

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

namespace DataTableToEntityClass
{
    public class DAL:IDAL
    {
       
        /// <summary>
        /// 获取服务器上所有数据库名
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public List<TempModel> GetDataBaseList(string conStr)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = "Select Name From Master..SysDatabases order By Name";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql);
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel { Name = row["Name"].DBValueToTypeValue<string>() ?? "", Type = "DataBase" });
            }
            return result;
        }
        /// <summary>
        /// 获取指定数据库的所有表名
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public List<TempModel> GetTableList(string conStr)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = $"Select Name From sysobjects Where XType='U' order By Name";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel { Name = row["Name"].DBValueToTypeValue<string>() ?? "", Type = "Table" });
            }
            return result;
        }

        /// <summary>
        /// 获取指定表的所有字段
        /// </summary>
        /// <param name="conStr"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<TempModel> GetTableColumns(string conStr, string table)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = $@"Select SysColumns.name Name,systypes.name Type,extended_properties.value Description From SysColumns 
                            left join systypes on SysColumns.xtype=systypes.xusertype
                            left join sys.extended_properties ON  SysColumns.id = extended_properties.major_id AND SysColumns.colid = extended_properties.minor_id  
                            Where id = Object_Id(@table)";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql,new SqlParameter("@table",table));
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel
                {
                    Name = row["Name"].DBValueToTypeValue<string>() ?? "",
                    Type = row["Type"].DBValueToTypeValue<string>() ?? "",
                    Description = row["Description"].DBValueToTypeValue<string>() ?? ""
                });
            }
            return result;
        }
    }
}

数据拿到了，接下来就是拼接了
BLL.cs

		/// <summary>
        /// 生成指定表
        /// </summary>
        /// <param name="conStr"></param>
        /// <param name="dataBase"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool GenerateEntityClass(string conStr, string dataBase,string savePath, string table)
        {
            var col = _iDal.GetTableColumns(conStr, table);
            if (col.Count == 0)
            {
                return false;
            }
            StringBuilder strEntity = new StringBuilder(1000);
            strEntity.Append("using System;\r\n");
            strEntity.Append("using System.Collections.Generic;\r\n");
            strEntity.Append("using System.Linq;\r\n");
            strEntity.Append("using System.Text;\r\n");
            strEntity.Append("\r\n");
            strEntity.Append($"namespace {dataBase}\r\n");
            strEntity.Append("{\r\n");
            strEntity.Append($"\tpublic class {table}\r\n");
            strEntity.Append("\t{\r\n");
            foreach (var item in col)
            {
                if (!string.IsNullOrWhiteSpace(item.Description))
                {
                	//如果数据库设计时有写描述，则生成备注
                    strEntity.Append("\t\t/// <summary>\r\n");
                    strEntity.Append($"\t\t///{item.Description}\r\n");
                    strEntity.Append("\t\t/// </summary>\r\n");
                }
                strEntity.Append($"\t\tpublic {dbTypeToCsharpType(item.Type)} {item.Name} {{ get; set; }}\r\n");
            }
            strEntity.Append("\t}\r\n");
            strEntity.Append("}");

            var folder= $"{(savePath!=string.Empty?savePath:AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\'))}\\{dataBase}";
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }
            var fileUrl = $"{folder}/{table}.cs";
            using (FileStream fs = new FileStream(fileUrl, FileMode.Create))
            {
                var b = Encoding.UTF8.GetBytes(strEntity.ToString());
                fs.Write(b, 0, b.Length);
                fs.Close();
            }
            return true;
        }

然后关于数据库字段类型与c#类型的对应关系，参考来源

string dbTypeToCsharpType(string dbType)
        {
            string cSharpType=string.Empty;
            switch (dbType)
            {
                case "bit":
                    cSharpType = "bool";
                    break;
                case "text":
                case "ntext":
                case "char":
                case "nchar":
                case "nvarchar":
                case "varchar":
                    cSharpType = "string";
                    break;
                case "int":
                    cSharpType = "int";
                    break;
                case "bigint":
                    cSharpType = "Int64";
                    break;
                case "smallint":
                    cSharpType = "Int16";
                    break;
                case "decimal":
                case "money":
                case "numeric":
                case "smallmoney":
                    cSharpType = "decimal";
                    break;
                case "float":
                    cSharpType = "double";
                    break;
                case "datetime":
                case "smalldatetime":
                case "timestamp":
                    cSharpType = "DateTime";
                    break;
                case "binary":
                case "image":
                case "varbinary":
                    cSharpType = "byte[]";
                    break;
                case "tinyint":
                    cSharpType = "byte";
                    break;
                case "real":
                    cSharpType = "Single";
                    break;
                case "uniqueidentifier":
                    cSharpType = "Guid";
                    break;
                case "Variant":
                    cSharpType = "object";
                    break;
                default:
                    cSharpType = "string";
                    break;
            }
            return cSharpType;
        }

然后就是动态拼接不同数据库的访问链接了，两个不同的身份验证方式

		/// <summary>
        /// Windows身份验证方式的数据库连接
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="serverAddress"></param>
        /// <returns></returns>
        public string GetConn(string dataBase,string serverAddress)
        {
            return $"Data Source={serverAddress};Initial Catalog={dataBase};Integrated Security=SSPI;";
        }
        /// <summary>
        /// SQL Server身份验证方式的数据库连接
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="serverAddress"></param>
        /// <param name="userID"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public string GetConn(string dataBase, string serverAddress,string userID,string password)
        {
            return $"Data Source={serverAddress};Initial Catalog={dataBase};User ID={userID};Password={password};";
        }

工具核心就这几个地方了


百度盘链接下载源码
链接: https://pan.baidu.com/s/1b6Bte7q_FlS0-X7FnA7kIw 提取码: fg3y