在 Visual Studio 中为表生成 C# 类代码

Question

我的服务器浏览数据库文件中有多个表。我想为表生成类的自动代码，因此所有类及其属性、构造函数和 getter setter 方法都会自动生成。

请告诉我这样做的步骤。

Answer 1

不是自动生成，但使用 sql 和 information_schema 输出类定义并不难，其中类以表命名，列映射到属性。从那里你可以让它生成创建、更新和删除（我喜欢在 SQL Server 2008 下使用合并来进行包/更新）。

一次做一个，主要是字符串连接。下面的内容应该让您开始......

declare @class varchar(max);

; with typemapping as (
 Select 'varchar' as DATA_TYPE, 'string' ctype
 union
 Select 'int', 'int'
)
select @class = isnull(@class + char(10), '') + 'public ' +
       tm.ctype +' ' + column_name +
       ' { get; set; }'
from information_schema.columns sc
   inner join typemapping tm on sc.data_type = tm.data_type
where table _name ='yourtbl'

print @class;

剩下的留给读者作为练习，正如他们所说，主要是因为细节取决于您，您可以使用支持变量而不是自动属性，在属性中放置标准逻辑，使值类型可为空，在制作自己的代码生成器时，使其适合您的模式/样式/需求。

Answer 2

如果您使用实体框架，请检查本文的步骤：

从现有数据库生成 EF Code First 模型类

Answer 3

你可以尝试这样的事情...

创建一个名为 ModelCreator.cs 的主类来执行所有关键操作。该应用程序的入口点是“连接”和“创建”按钮单击事件。它将触发 CreateConnectionString() 方法，该方法基本上从用户获取输入并动态创建连接字符串

private void lbtnConnect_Click(object sender, System.EventArgs e)
{
   if (CreateConnectionString())
  CreateModelClassFiles(tcGetDataReader());
}

// <summary>
/// Get the SqlDataReader object
/// SqlDataReader
/// </summary>

public SqlDataReader tcGetDataReader()
{
SqlConnection connection = null;
try
{
  connection = GetConnection(SQL_CONN_STRING);
  if (connection == null)
  return null;
  SqlDataReader dr = SqlHelper.ExecuteReader(
         connection,
         CommandType.StoredProcedure,
         "getData");
if (dr.HasRows)
  return dr;
else
  return null;
 }
 catch(Exception ex)
 {
  MessageBox.Show(ex.Message);
  return null;
 }   
}

从数据库获取表名称、属性及其类型

 CREATE PROCEDURE getData AS 
 select table_name, column_name, data_type
  from information_schema.columns
  where table_name in
  (
   select table_name
   from Information_Schema.Tables
   where Table_Type='Base Table'
  ) order by table_name
GO

主方法、CreateModelClassFiles

  /// <summary>
 /// Create the Model class list iterating through the tables
/// </summary>
/// <param name="dr">Sql Data reader for the database schema</param>

    private void CreateModelClassFiles(SqlDataReader dr)
    {
     if (dr != null)
    {
    string lstrOldTableName = string.Empty;
    StreamWriter sw = null;
    System.Text.StringBuilder sb = null;
    System.Text.StringBuilder sbAttr = null;
    while(dr.Read())
    {
      string lstrTableName = dr.GetString(0);
      string lstrAttributeName = dr.GetString(1);
      string lstrAttributeType = GetSystemType(dr.GetString(2));
      if (lstrOldTableName != lstrTableName)
      {
        if (sw != null)
        {
          this.CreateClassBottom(sw, sb.ToString().TrimEnd(
                     new char[]{',', ' ', '\r', '\t', '\n'}),
                     sbAttr.ToString());
            sw.Close();
        }
        sb = new System.Text.StringBuilder(lstrTableName);
        sb.Append(".cs");
        FileInfo lobjFileInfo = new FileInfo(sb.ToString());
        sw = lobjFileInfo.CreateText();
        this.CreateClassTop(sw, lstrTableName);
        sb = new System.Text.StringBuilder("\r\n\t/// \r\n\t" + 
             "/// User defined Contructor\r\n\t/// \r\n\tpublic ");
        sbAttr = new System.Text.StringBuilder();
        sb.Append(lstrTableName);
        sb.Append("(");
      }
      else
      {
        this.CreateClassBody(sw, lstrAttributeType, lstrAttributeName);
        sb.AppendFormat("{0} {1}, \r\n\t\t", 
           new object[]{lstrAttributeType, lstrAttributeName});
        sbAttr.AppendFormat("\r\n\t\tthis._{0} = {0};", 
           new object[]{lstrAttributeName});
      }
      lstrOldTableName = lstrTableName;
      this.progressBarMain.Increment(1); 
    }
    MessageBox.Show("Done !!");
  }
}

一旦调用此方法，它就会为您完成所有操作。

我希望它能帮助你......

Answer 4

我已经修改了上面的类并包含了所有缺少的方法

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;

using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

namespace CodeGenerator

{
    public partial class Form1 : Form
    {

    public Form1()
    {
        InitializeComponent();
    }

    private void btnGenerateCode_Click(object sender, EventArgs e)
    {
        string conStrJobsDB = ConfigurationSettings.AppSettings["jobsDBConStrKey"].ToString();
        CreateEntitiesFromDBTables(GetDataReader(conStrJobsDB));
    }

    private void CreateEntitiesFromDBTables(SqlDataReader dr)
    {
        if (dr != null)
        {
            string lstrOldTableName = string.Empty;
            StreamWriter swClassWriter = null;
            System.Text.StringBuilder sbFileName = null;
            System.Text.StringBuilder sbConstructorCode = null;
            System.Text.StringBuilder sbClassCode = null;
            FileInfo tableClassFile = null;

            while (dr.Read())
            {
                string lstrTableName = dr.GetString(0);
                string lstrAttributeName = dr.GetString(1);
                string lstrAttributeType = GetDotNetType(dr.GetString(2));

                //If table name is changed...
                if (lstrOldTableName != lstrTableName)
                {
                    //and stream writer is already opened so close this class generation...
                    if (swClassWriter != null)
                    {
                        CreateClassBottom(swClassWriter);
                        swClassWriter.Close();
                    }

                    sbFileName = new System.Text.StringBuilder(lstrTableName);
                    sbFileName.Append("Entity.cs");
                    tableClassFile = new FileInfo(tbPath.Text + "\\" + sbFileName.ToString());
                    swClassWriter = tableClassFile.CreateText();
                    CreateClassTop(swClassWriter, lstrTableName);

                    //sbConstructorCode = new System.Text.StringBuilder("\r\n\t/// \r\n\t" +
                    //     "/// User defined Contructor\r\n\t/// \r\n\tpublic ");
                    //sbConstructorCode = new System.Text.StringBuilder();
                    //sbConstructorCode.Append(lstrTableName);
                    //sbConstructorCode.Append("(");
                }
                else
                {
                    this.CreateClassBody(swClassWriter, lstrAttributeType, lstrAttributeName);
                    //sbConstructorCode.AppendFormat("{0} {1}, \r\n\t\t",
                    //   new object[] { lstrAttributeType, lstrAttributeName });
                    //sbConstructorCode.AppendFormat("\r\n\t\tthis._{0} = {0};",
                    //   new object[] { lstrAttributeName });
                }

                lstrOldTableName = lstrTableName;
                this.pBarMain.Increment(1);
            }

            MessageBox.Show("All classes generated.", "Done");
        }
    }

    private SqlDataReader GetDataReader(string conStrJobsDB)
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(conStrJobsDB);
            if (connection == null)
                return null;
            connection.Open();
            SqlCommand command = new System.Data.SqlClient.SqlCommand("exec spGenerateEntitiesFromTables", connection);
            SqlDataReader dr = command.ExecuteReader();
            if (dr.HasRows)
                return dr;
            else
                return null;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return null;
        }
    }

    private string GetDotNetType(string dbColumnType)
    {
        string returnType = string.Empty;
        if (dbColumnType.Equals("nvarchar"))
            returnType = "string";
        else if (dbColumnType.Equals("varchar"))
            returnType = "string";
        else if (dbColumnType.Equals("int"))
            returnType = "int";
        else if (dbColumnType.Equals("bit"))
            returnType = "bool";
        else if (dbColumnType.Equals("bigint"))
            returnType = "long";
        else if (dbColumnType.Equals("binary"))
            returnType = "byte[]";
        else if (dbColumnType.Equals("char"))
            returnType = "string";
        else if (dbColumnType.Equals("date"))
            returnType = "DateTime";
        else if (dbColumnType.Equals("datetime"))
            returnType = "DateTime";
        else if (dbColumnType.Equals("datetime2"))
            returnType = "DateTime";
        else if (dbColumnType.Equals("datetimeoffset"))
            returnType = "DateTimeOffset";
        else if (dbColumnType.Equals("decimal"))
            returnType = "decimal";
        else if (dbColumnType.Equals("float"))
            returnType = "float";
        else if (dbColumnType.Equals("image"))
            returnType = "byte[]";
        else if (dbColumnType.Equals("money"))
            returnType = "decimal";
        else if (dbColumnType.Equals("nchar"))
            returnType = "char";
        else if (dbColumnType.Equals("ntext"))
            returnType = "string";
        else if (dbColumnType.Equals("numeric"))
            returnType = "decimal";
        else if (dbColumnType.Equals("nvarchar"))
            returnType = "string";
        else if (dbColumnType.Equals("real"))
            returnType = "double";
        else if (dbColumnType.Equals("smalldatetime"))
            returnType = "DateTime";
        else if (dbColumnType.Equals("smallint"))
            returnType = "short";
        else if (dbColumnType.Equals("smallmoney"))
            returnType = "decimal";
        else if (dbColumnType.Equals("text"))
            returnType = "string";
        else if (dbColumnType.Equals("time"))
            returnType = "TimeSpan";
        else if (dbColumnType.Equals("timestamp"))
            returnType = "DateTime";
        else if (dbColumnType.Equals("tinyint"))
            returnType = "byte";
        else if (dbColumnType.Equals("uniqueidentifier"))
            returnType = "Guid";
        else if (dbColumnType.Equals("varbinary"))
            returnType = "byte[]";

        return returnType;
    }

    private void CreateClassTop(StreamWriter sw, string lstrTableName)
    {
        System.Text.StringBuilder sb = null;
        sb = new StringBuilder("public class " + lstrTableName +"Entity\n{");
        sw.Write(sb.ToString());
    }

    private void CreateClassBody(StreamWriter sw, string lstrAttributeType, string lstrAttributeName)
    {
        System.Text.StringBuilder sb = null;
        sb = new StringBuilder("\n\rpublic " + lstrAttributeType + " " + lstrAttributeName + " { get; set; }");
        sw.Write(sb.ToString());
    }

    private void CreateClassBottom(StreamWriter sw)
    {
        System.Text.StringBuilder sb = null;
        sb = new StringBuilder("\n\n}");
        sw.Write(sb.ToString());
    }



}

}

Answer 5

我认为从表生成 cs 类的最简单方法是在数据库上查询，例如在 SQL Server 中，您可以使用此查询：

declare @tblName sysname = 'SetYourTableName'
declare @ResultText varchar(max) = 'public class ' + @tblName + '
{'

select @ResultText = @ResultText + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@tblName)
) t
order by ColumnId

set @ResultText = @ResultText  + '
}'

print @ResultText

然后运行此查询并在 Visual Studio 中的新类中复制打印的结果文本

我希望对您有用，祝您

好运