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; }
}
}
I'd expect the following 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.
Solution 1:
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;
}
}
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
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.
Solution 2:
@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 Objects classes introduced with SQL 2005.
David Hayden has a post entitled Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation that walks through how to create a table using SMO. The strongly-typed objects make it a breeze with methods like:
// 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.
Solution 3:
Try out my CreateSchema extension method for objects at http://createschema.codeplex.com/
It returns a string for any object containing CREATE TABLE scripts.