C#查表自动生成相应的实体类文件

思路

1. 查询数据库中某个表的字段名、字段类型、字段说明
2. 拼接 该表对应的实体类 的字符串
3. 将字符串写入txt文件，即.cs文件

sql语句

通过sql语句查询表中的字段名、字段类型、字段说明

--  FinanceDetail 是表名，可以替换成其他表
SELECT temp1.FieldName,
         temp1.FieldType,
         temp2.FieldDescription
FROM 
    (SELECT syscolumns.name AS FieldName,
         systypes.name AS FieldType
    FROM syscolumns
    INNER JOIN systypes
        ON systypes.xtype = syscolumns.xtype
    WHERE id = 
        (SELECT id
        FROM sysobjects
        WHERE name = 'FinanceDetail' ) ) temp1
    LEFT JOIN 
    (SELECT B.name AS column_name,
         C.value AS FieldDescription
    FROM sys.tables A
    INNER JOIN sys.columns B
        ON B.object_id = A.object_id
    LEFT JOIN sys.extended_properties C
        ON C.major_id = B.object_id
            AND C.minor_id = B.column_id
    WHERE A.name = 'FinanceDetail' ) temp2
    ON temp1.FieldName = temp2.column_name

查询结果如下图：

C#拼接字符并写入cs文件

新建控制台程序

关键代码

在DAL层引入dapper或其他orm框架进行数据库查询操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ShanNan.Entities;
using Dapper;
using System.Data.SqlClient;
using System.Data;
using ShanNan.Helper;

namespace ShanNan.DAL
{
        /// <summary>
        /// sql语句查询表中的字段名、字段类型、字段说明
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <returns>数据库表字段名、类型、说明集合</returns>
 public   class TableFieldDAL
    {
        public List<TableField> GetTableFieldList(string tableName)
        {
            List<TableField> tabFieldList = new List<TableField>();
            using (IDbConnection connection = new SqlConnection(DBHelper.GetConnectString()))
            {
                string sqlstr = "SELECT temp1.FieldName, temp1.FieldType, temp2.FieldDescription FROM ( SELECT syscolumns.name AS FieldName, systypes.name AS FieldType FROM syscolumns INNER JOIN systypes ON systypes.xtype = syscolumns.xtype WHERE id = ( SELECT id FROM sysobjects WHERE name = @TableName ) ) temp1 LEFT JOIN ( SELECT B.name AS column_name, C.value AS FieldDescription FROM sys.tables A INNER JOIN sys.columns B ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id WHERE A.name =  @TableName ) temp2 ON temp1.FieldName = temp2.column_name";
  
                tabFieldList = connection.Query<TableField>(sqlstr, new { TableName = tableName }).ToList();
            }
            return tabFieldList;
        }
    }
}

在BLL层拼接字符串并写入cs文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ShanNan.Result;
using ShanNan.DAL;
using ShanNan.Entities;
using ShanNan.Helper;

namespace ShanNan.BLL
{
 public   class TableFieldBLL
    {
        /// <summary>
        /// 拼接字符串形成类文件
        /// </summary>
        /// <param name="tableName">数据库中的表名</param>
        /// <returns>统一返回值类</returns>
        public ResultData  Code2Txt(string tableName)
        {
            ResultData Result = new ResultData();

            TableFieldDAL dal = new TableFieldDAL();
            List<TableField> list = dal.GetTableFieldList(tableName);           
            string className = "public class  " + tableName + "{\n";
            //存放数据库某个表类的字符串
            StringBuilder classStr = new StringBuilder();
            classStr.Append(className);
            //拼接表的字段、类型、注释   
            foreach (var tabField in list)
            {
                string field = tabField.FieldName;
                string privateFieldStr = "  private string  " + field.ToLower() + " ;\n"; //私有属性，全小写。 
                string summary = "/// <summary>\n" + "///" + tabField.FieldDescription + "\n/// <summary>\n";
                string publicFiledStr = "public " + StringHelper.DBType2CsType(tabField.FieldType) + "  "
                    + field + "{get{return " + field.ToLower() + ";}set{" + field.ToLower() + "=value;}}";
                classStr.Append(privateFieldStr + summary + publicFiledStr);              
            }
            classStr.Append("\n }");
            //设置自动生成的代码放置路径
            string path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + tableName+".cs";
            //写入txt文件
          Result.Code   =  TxtHelper.Wrtie2Txt(path, classStr.ToString());
            if (Result.Code=="1")
            {
                Result.Message = "成功写入";
            }
            else
            {
                Result.Message = "写入失败";
            }
            Result.Data = "";
            return Result;  
        }       

    }
}

这里就涉及到将数据库字段类型转换成c#数据类型。借鉴了 SQL SERVER与C#的数据类型对应表中的代码，并做修改。具体代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ShanNan.Helper
{
public    class StringHelper
    {
        #region sqlserver数据类型与C#数据类型的转换
        /// <summary>
        /// 数据库中与c#中的数据类型对照
        /// </summary>
        /// <param name="DBtype">数据库中的数据类型</param>
        /// <returns>C#数据类型</returns>   
        public static string DBType2CsType(string DBtype)
        {
            string CsType = "object";
            switch (DBtype.ToLower())
            {
                case "int":
                    CsType = "int";
                    break;
                case "text":
                    CsType = "string";
                    break;
                case "bigint":
                    CsType = "int";
                    break;
                case "binary":
                    CsType = "byte";
                    break;
                case "bit":
                    CsType = "boolean";
                    break;
                case "char":
                    CsType = "string";
                    break;
                case "datetime":
                    CsType = "DateTime";
                    break;
                case "decimal":
                    CsType = "decimal";
                    break;
                case "float":
                    CsType = "float";
                    break;
                case "image":
                    CsType = "byte";
                    break;
                case "money":
                    CsType = "decimal";
                    break;
                case "nchar":
                    CsType = "string";
                    break;
                case "ntext":
                    CsType = "string";
                    break;
                case "numeric":
                    CsType = "decimal";
                    break;
                case "nvarchar":
                    CsType = "string";
                    break;
                case "real":
                    CsType = "system.single";
                    break;
                case "smalldatetime":
                    CsType = "system.datetime";
                    break;
                case "smallint":
                    CsType = "int";
                    break;
                case "smallmoney":
                    CsType = "double";
                    break;
                case "timestamp":
                    CsType = "DateTime";
                    break;
                case "tinyint":
                    CsType = "int";
                    break;
                case "uniqueidentifier":
                    CsType = "guid";
                    break;
                case "varbinary":
                    CsType = "byte";
                    break;
                case "varchar":
                    CsType = "string";
                    break;
                case "variant":
                    CsType = "object";
                    break;
                default:
                    CsType = "string";
                    break;
            }
            return CsType;
        }
        #endregion
    }
}

最后效果