我怎样才能生成的C＃类数据库表？ [英] How can I generate database tables from C# classes?
问题描述
有谁知道一个方法来自动生成给定类数据库中的表？我不是在寻找一个完整的持久层 - 我已经有我使用一个数据访问解决方案，但我突然有大量的信息存储从大量的类，我真的不希望有创建所有这些表由手工。例如，给定下面的类：
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; }
}
}
我期望的SQL语句：
I'd expect the following SQL:
CREATE TABLE Foo
(
Property1 VARCHAR(500),
Property2 INT
)
我也想知道，你如何处理复杂类型。例如，在previously引类，如果我们改变了是：
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; }
}
}
我怎么能处理？
我看着使用反射通过每个类的属性来枚举通过自己努力来自动生成数据库脚本，但它的笨重和复杂的数据类型有我难住了。
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.
推荐答案
这真的晚了，我只花了这10分钟左右，因此它非常马虎，但它的工作，并给你一个很好的起跳点：
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:
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;
}
}
和它生成的SQL语句：
And it generated the following 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
一些进一步的想法...我会考虑增加一个属性，如[SQLTABLE]你的班级，这样它只能产生你想要的课程表。此外，这可以被清理一吨，修复的bug，优化（FK的检查是一个笑话）等等等等...只是让你开始。
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.
这篇关于我怎样才能生成的C＃类数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
