在Visual Studio中为表生成c#类代码
本文关键字：代码 Visual Studio | 更新日期: 2023-09-27 18:12:10
我的Server Explore数据库文件中有多个表。我想为表生成自动代码的类，所以所有类及其属性，构造函数和getter setter方法自动生成。
请告诉我该怎么做
不是自动生成的，但使用sql和information_schema输出类定义并不难，类以表命名，列映射到属性。从那里你可以有它生成创建，更新和删除(我喜欢使用合并在SQL Server 2008下的crate/updates)。
一次做一个，主要是字符串连接。下面应该可以让您开始....
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;
其余部分留给读者作为练习，因为他们说主要是因为细节取决于你，而不是自动属性，你可以使用备用变量，在属性中放入标准逻辑，使值类型为空，当制作自己的代码生成器时，使其适合你的模式/风格/需求。
如果您使用实体框架，请检查本文的步骤:
从现有数据库生成EF Code First模型类
你可以试试这样做…
创建一个名为ModelCreator.cs的主类，它执行所有的关键操作。此应用程序的入口点是Connect和Create按钮单击事件。它将触发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 !!");
}
}
一旦这个方法被调用，它会为你做所有的事情。
我希望它会帮助你....
我已经修改了上面的类，并包含了所有缺失的方法
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());
}
}
}
我认为从表中生成cs类的最简单方法是对数据库进行查询，例如在SQL服务器中，您可以使用此查询:
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的New Class
中我希望对你有用
祝你好运