如何从 C# 类生成数据库表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I generate database tables from C# classes?
提问by VanOrman
Does anyone know a way to auto-generate database tables for a given class? I'm not looking for an entire persistence layer - I already have a data access solution I'm using, but I suddenly have to store a lot of information from a large number of classes and I really don't want to have to create all these tables by hand. For example, given the following class:
有谁知道为给定类自动生成数据库表的方法？我不是在寻找一个完整的持久层——我已经有了一个我正在使用的数据访问解决方案，但我突然不得不存储来自大量类的大量信息，我真的不想创建所有这些桌子都是手工制作的。例如，给定以下类：
class Foo
{
private string property1;
public string Property1
{
get { return property1; }
set { property1 = value; }
}
private int property2;
public int Property2
{
get { return property2; }
set { property2 = value; }
}
}
I'd expect the following SQL:
我期望以下 SQL：
CREATE TABLE Foo
(
Property1 VARCHAR(500),
Property2 INT
)
I'm also wondering how you could handle complex types. For example, in the previously cited class, if we changed that to be :
我也想知道你如何处理复杂的类型。例如，在前面引用的类中，如果我们将其更改为：
class Foo
{
private string property1;
public string Property1
{
get { return property1; }
set { property1 = value; }
}
private System.Management.ManagementObject property2;
public System.Management.ManagementObject Property2
{
get { return property2; }
set { property2 = value; }
}
}
How could I handle this?
我怎么能处理这个？
I've looked at trying to auto-generate the database scripts by myself using reflection to enumerate through each class' properties, but it's clunky and the complex data types have me stumped.
我曾尝试使用反射来枚举每个类的属性，尝试自己自动生成数据库脚本，但它很笨重，而且复杂的数据类型让我感到困惑。
采纳答案by FlySwat
It's really late, and I only spent about 10 minutes on this, so its extremely sloppy, however it does work and will give you a good jumping off point:
真的很晚了，我只花了大约 10 分钟，所以它非常草率，但是它确实有效并且会给你一个很好的起点：
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
namespace TableGenerator
{
class Program
{
static void Main(string[] args)
{
List<TableClass> tables = new List<TableClass>();
// Pass assembly name via argument
Assembly a = Assembly.LoadFile(args[0]);
Type[] types = a.GetTypes();
// Get Types in the assembly.
foreach (Type t in types)
{
TableClass tc = new TableClass(t);
tables.Add(tc);
}
// Create SQL for each table
foreach (TableClass table in tables)
{
Console.WriteLine(table.CreateTableScript());
Console.WriteLine();
}
// Total Hacked way to find FK relationships! Too lazy to fix right now
foreach (TableClass table in tables)
{
foreach (KeyValuePair<String, Type> field in table.Fields)
{
foreach (TableClass t2 in tables)
{
if (field.Value.Name == t2.ClassName)
{
// We have a FK Relationship!
Console.WriteLine("GO");
Console.WriteLine("ALTER TABLE " + table.ClassName + " WITH NOCHECK");
Console.WriteLine("ADD CONSTRAINT FK_" + field.Key + " FOREIGN KEY (" + field.Key + ") REFERENCES " + t2.ClassName + "(ID)");
Console.WriteLine("GO");
}
}
}
}
}
}
public class TableClass
{
private List<KeyValuePair<String, Type>> _fieldInfo = new List<KeyValuePair<String, Type>>();
private string _className = String.Empty;
private Dictionary<Type, String> dataMapper
{
get
{
// Add the rest of your CLR Types to SQL Types mapping here
Dictionary<Type, String> dataMapper = new Dictionary<Type, string>();
dataMapper.Add(typeof(int), "BIGINT");
dataMapper.Add(typeof(string), "NVARCHAR(500)");
dataMapper.Add(typeof(bool), "BIT");
dataMapper.Add(typeof(DateTime), "DATETIME");
dataMapper.Add(typeof(float), "FLOAT");
dataMapper.Add(typeof(decimal), "DECIMAL(18,0)");
dataMapper.Add(typeof(Guid), "UNIQUEIDENTIFIER");
return dataMapper;
}
}
public List<KeyValuePair<String, Type>> Fields
{
get { return this._fieldInfo; }
set { this._fieldInfo = value; }
}
public string ClassName
{
get { return this._className; }
set { this._className = value; }
}
public TableClass(Type t)
{
this._className = t.Name;
foreach (PropertyInfo p in t.GetProperties())
{
KeyValuePair<String, Type> field = new KeyValuePair<String, Type>(p.Name, p.PropertyType);
this.Fields.Add(field);
}
}
public string CreateTableScript()
{
System.Text.StringBuilder script = new StringBuilder();
script.AppendLine("CREATE TABLE " + this.ClassName);
script.AppendLine("(");
script.AppendLine("\t ID BIGINT,");
for (int i = 0; i < this.Fields.Count; i++)
{
KeyValuePair<String, Type> field = this.Fields[i];
if (dataMapper.ContainsKey(field.Value))
{
script.Append("\t " + field.Key + " " + dataMapper[field.Value]);
}
else
{
// Complex Type?
script.Append("\t " + field.Key + " BIGINT");
}
if (i != this.Fields.Count - 1)
{
script.Append(",");
}
script.Append(Environment.NewLine);
}
script.AppendLine(")");
return script.ToString();
}
}
}
I put these classes in an assembly to test it:
我将这些类放在一个程序集中进行测试：
public class FakeDataClass
{
public int AnInt
{
get;
set;
}
public string AString
{
get;
set;
}
public float AFloat
{
get;
set;
}
public FKClass AFKReference
{
get;
set;
}
}
public class FKClass
{
public int AFKInt
{
get;
set;
}
}
And it generated the following SQL:
它生成了以下 SQL：
CREATE TABLE FakeDataClass
(
ID BIGINT,
AnInt BIGINT,
AString NVARCHAR(255),
AFloat FLOAT,
AFKReference BIGINT
)
CREATE TABLE FKClass
(
ID BIGINT,
AFKInt BIGINT
)
GO
ALTER TABLE FakeDataClass WITH NOCHECK
ADD CONSTRAINT FK_AFKReference FOREIGN KEY (AFKReference) REFERENCES FKClass(ID)
GO
Some further thoughts...I'd consider adding an attribute such as [SqlTable] to your classes, that way it only generates tables for the classes you want. Also, this can be cleaned up a ton, bugs fixed, optimized (the FK Checker is a joke) etc etc...Just to get you started.
一些进一步的想法......我会考虑向您的类添加一个属性，例如 [SqlTable]，这样它只会为您想要的类生成表。此外，这可以大量清理、修复错误、优化（FK Checker 是个笑话）等等……只是为了让您入门。
回答by Mark Cidade
For complex types, you can recursively convert each one that you come across into a table of its own and then attempt to manage foreign key relationships.
对于复杂类型，您可以递归地将遇到的每个类型转换为自己的表，然后尝试管理外键关系。
You may also want to pre-specify whichclasses will or won't be converted to tables. As for complex data that you want reflected in the database without bloating the schema, you can have one or more tables for miscellaneous types. This example uses as many as 4:
您可能还想预先指定哪些类将或不会转换为表。对于您希望在不使架构膨胀的情况下反映在数据库中的复杂数据，您可以拥有一个或多个用于杂项类型的表。此示例使用多达 4 个：
CREATE TABLE MiscTypes /* may have to include standard types as well */
( TypeID INT,
TypeName VARCHAR(...)
)
CREATE TABLE MiscProperties
( PropertyID INT,
DeclaringTypeID INT, /* FK to MiscTypes */
PropertyName VARCHAR(...),
ValueTypeID INT /* FK to MiscTypes */
)
CREATE TABLE MiscData
( ObjectID INT,
TypeID INT
)
CREATE TABLE MiscValues
( ObjectID INT, /* FK to MiscData*/
PropertyID INT,
Value VARCHAR(...)
)
回答by Vaibhav
I think for complex data types, you should extend them by specifying a ToDB() method which holds their own implementation for creating tables in the DB, and this way it becomes auto-recursive.
我认为对于复杂的数据类型，您应该通过指定一个 ToDB() 方法来扩展它们，该方法保存它们自己在数据库中创建表的实现，这样它就变成了自动递归。
回答by Vaibhav
Also... maybe you can use some tool such as Visio (not sure if Visio does this, but I think it does) to reverse engineer your classes into UML and then use the UML to generate the DB Schema... or maybe use a tool such as this http://www.tangiblearchitect.net/visual-studio/
另外...也许您可以使用诸如 Visio 之类的工具（不确定 Visio 是否这样做，但我认为确实如此）将您的类反向工程为 UML，然后使用 UML 生成 DB 模式...或者可能使用像这样的工具http://www.tangiblearchitect.net/visual-studio/
回答by Cheekysoft
回答by Portman
@Jonathan Holland
@乔纳森荷兰
Wow, I think that's the most raw work I've ever seen put into a StackOverflow post. Well done. However, instead of constructing DDL statements as strings, you should definitely use the SQL Server Management Objectsclasses introduced with SQL 2005.
哇，我认为这是我见过的 StackOverflow 帖子中最原始的工作。做得好。但是，您绝对应该使用SQL 2005 中引入的SQL Server 管理对象类，而不是将 DDL 语句构造为字符串。
David Hayden has a post entitled Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generationthat walks through how to create a table using SMO. The strongly-typed objects make it a breeze with methods like:
David Hayden 发表了一篇题为使用 C# 和 SQL Server 管理对象 (SMO) 在 SQL Server 2005 中创建表 - 代码生成的博文，其中介绍了如何使用 SMO 创建表。强类型对象使用以下方法轻而易举：
// Create new table, called TestTable
Table newTable = new Table(db, "TestTable");
and
和
// Create a PK Index for the table
Index index = new Index(newTable, "PK_TestTable");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
VanOrman, if you're using SQL 2005, definitely make SMO part of your solution.
VanOrman，如果您使用的是 SQL 2005，那么一定要让 SMO 成为您解决方案的一部分。
回答by IanStallings
回答by IanStallings
回答by IanStallings
You can do the opposite, database table to C# classes here: http://pureobjects.com/dbCode.aspx
您可以在此处对 C# 类执行相反的数据库表操作：http: //pureobjects.com/dbCode.aspx
回答by Kai Sevelin
Try out my CreateSchema extension method for objects at http://createschema.codeplex.com/
在http://createschema.codeplex.com/ 上试试我的 CreateSchema 对象扩展方法
It returns a string for any object containing CREATE TABLE scripts.
它为包含 CREATE TABLE 脚本的任何对象返回一个字符串。
