c#实现数据库表映射成模型类

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 数据库表映射模型
{
    public class SqlTableModel
    {
        public void SqlEntityClass()
        {
            /*将数据库存在的表名存储到一张中*/
            string sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'";
            DataTable table = SqlHelper.ExcuteDataTable(sql);

            /*查询所有存在的表，并获得表的字段名称、类型*/
            Dictionary<string, DataTable> tableNames_Tables = SetTableModel(table);
            /*sql类转实体类模板*/
            Dictionary<string, string> tableNames_codes = GenerateEntityClass(tableNames_Tables);
            FileWrite(tableNames_codes);
        }

        public Dictionary<string,DataTable> SetTableModel(DataTable table)
        {
            Dictionary<string, DataTable> tableNames_Tables = new Dictionary<string, DataTable>();
            /*拼接表查询+参数化*/
            foreach (DataRow item in table.Rows)
            {
                //获得表名
                string tableName = item["TABLE_NAME"].ToString();
                string sql = $"SELECT COLUMN_NAME AS 'Name', DATA_TYPE AS 'Type', CHARACTER_MAXIMUM_LENGTH AS 'Length' " +
                                          "FROM INFORMATION_SCHEMA.COLUMNS " +
                                          "WHERE TABLE_NAME =@TableName_" + tableName + "  AND TABLE_SCHEMA = 'dbo';"; // 假设你的表在dbo架构下  
                SqlParameter p = new SqlParameter("@TableName_" + tableName, tableName);
                //sb.Append(sql);
                //ps.Add(p);
                tableNames_Tables[tableName] =SqlHelper.ExcuteDataTable(sql, CommandType.Text, p);
            }
            return tableNames_Tables;
        }

        public Dictionary<string,string> GenerateEntityClass(Dictionary<string, DataTable> tableNames_Tables)
        {
            Dictionary<string, string> tableNames_codes = new Dictionary<string, string>();
            foreach (var item in tableNames_Tables)
            {
                /*类模板拼接*/
                StringBuilder sb = new StringBuilder();
                
                sb.AppendLine("using System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Text;\nusing System.Threading.Tasks;\n ");
                sb.AppendLine("namespace Model\n{");
                sb.AppendLine($"    public class {item.Key}");
                sb.AppendLine("    {");
                /*将sql类型转换成c#类型*/
                foreach (DataRow row in item.Value.Rows)
                {
                    string propertyName = row["Name"].ToString();
                    string propertyType = MapSqlTypeToCSharpType(row["Type"].ToString(), row["Length"]?.ToString());
                    sb.AppendLine($"        public {propertyType} {propertyName} {{ get; set; }}");
                }
                sb.AppendLine("    }\n}");
                tableNames_codes[item.Key] = sb.ToString();
            }
            return tableNames_codes;
        }
        //将sql类型转换成c#类型
        public string MapSqlTypeToCSharpType(string sqlType, string length)
        {
            switch (sqlType.ToLower())
            {
                // 数值类型  
                case "bigint":
                    return "long";
                case "int":
                    return "int";
                case "smallint":
                    return "short";
                case "tinyint":
                    return "byte";
                case "bit":
                    return "bool";
                case "decimal":
                case "money":
                case "smallmoney":
                    return "decimal";
                case "numeric":
                    // 如果提供了长度和精度，可以生成更具体的 decimal 类型，例如 decimal(18,2)  
                    // 这里为了简化，我们仅返回 decimal  
                    return "decimal";
                case "float":
                    return "double";
                case "real":
                    return "float";

                // 字符和文本类型  
                case "char":
                case "nchar":
                case "varchar":
                case "nvarchar":
                case "text":
                case "ntext":
                    // 对于文本类型，我们可以根据长度返回 string 或者更具体的如 string[50]  
                    // 但C#中不直接支持指定长度的 string，所以这里仅返回 string  
                    return "string";

                // 二进制类型  
                case "binary":
                case "varbinary":
                case "image":
                    return "byte[]";

                // 日期和时间类型  
                case "date":
                    return "DateTime";
                case "datetime":
                case "smalldatetime":
                    return "DateTime";
                case "datetime2":
                    // 可以根据长度进一步指定 DateTime 的精度，但这里简化处理  
                    return "DateTime";
                case "datetimeoffset":
                    return "DateTimeOffset";
                case "time":
                    return "TimeSpan";

                // 其他类型  
                case "uniqueidentifier":
                    return "Guid";
                case "sql_variant":
                    // 难以直接映射到 C# 类型，可能需要更复杂的处理  
                    return "object";
                case "xml":
                    return "string"; // 或者使用 XElement/XDocument 等  
                case "cursor":
                case "hierarchyid":
                case "geography":
                case "geometry":
                case "rowversion":
                case "table":
                case "timestamp":
                case "variant":
                    // 这些类型可能需要特殊处理或自定义类  
                    // 这里为了简化，我们返回 object 或其他通用类型  
                    return "object";

                // 默认值或未知类型  
                default:
                    return "object"; // 或者抛出异常  
            }
        }
        public void FileWrite(Dictionary<string, string> tableNames_codes)
        {
            foreach (var item in tableNames_codes)
            {
                File.WriteAllText($"D:/Exercise Files/ASP.NET MVC/EF/EF练习/数据库表映射模型/" + item.Key+".cs",item.Value);
                Console.WriteLine(Path.GetFullPath(item.Key + ".cs"));
            }
        }
    }
}