How can I generate database tables from C # classes?

Question

Does anyone know a way to automatically generate database tables for a given class? I'm not looking for a whole layer of persistence - I already have a data access solution that I use, but I suddenly have to store a lot of information from a large number of classes, and I really do not want to create all these tables manually. 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 would expect the following SQL:

 CREATE TABLE Foo ( Property1 VARCHAR(500), Property2 INT ) 

I am also wondering how you can handle complex types. For example, in a previously quoted class, if we changed this:

 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 can I handle this?

I tried to automatically generate database scripts myself, using reflection to list the properties of each class, but this is inconvenient, and complex data types puzzled me.

Answer 1

It is very late, and I spent about 10 minutes on it, so it is very messy, however it really works and will give you a good jump 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 he 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 would think about adding an attribute to your classes, such as [SqlTable], so it only generates tables for the classes you want. In addition, it can be cleaned per ton, fixed bugs, optimized (FK Checker - a joke), etc. Etc. Just to get you started.

Answer 2

@ Jonathan Holland

Wow, I think the rawest work I've ever seen put in a StackOverflow post. Well done. However, instead of creating DDL statements as strings, you should definitely use the SQL Server Management Objects classes introduced with SQL 2005.

David Hayden has a message called Create a table in SQL Server 2005 using C # and SQL Server (SMO) management objects - code generation that goes through how to create a table using SMO. Strongly typed objects make it easy with methods such as:

 // 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 are using SQL 2005, definitely make SMO part of your solution.

Answer 3

I think that for complex data types you should expand them by specifying the ToDB () method, which contains its own implementation for creating tables in the database, and thus it becomes autorecursive.

Answer 4

Try using the CreateSchema extension method for http://createschema.codeplex.com/ objects

It returns a string for any object containing CREATE TABLE scripts.

Answer 5

As of 2016 (I think), you can use Entity Framework 6 Code First to generate the SQL schema from C # poco classes or to use the First database to generate C # code from sql. Passing code from first to DB

Answer 6

For complex types, you can recursively convert each one that falls into its own table, and then try to manage relationships with foreign keys.

You can also pre-specify which classes will or will not be converted to tables. As for complex data that you want to reflect in the database without inflating the schema, you can have one or more tables for different types. This example uses as many as 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(...) ) 

Answer 7

Here you can do a reverse database table into C # classes: http://pureobjects.com/dbCode.aspx

Answer 8

Also ... maybe you can use some kind of tool like Visio (not sure if Visio does this, but I think it is) to reverse engineer your classes in UML and then use UML to create a DB schema ... or perhaps use a tool like http://www.tangiblearchitect.net/visual-studio/

Answer 9

I know that you are looking for the entire persistence layer, but the NHibernate hbm2ddl task can do this almost as a single line.

There is a NAnt task that may be of interest, which may be of interest.

Answer 10

@PortMan,

SQL DataObjects are cool, I did not know about them. This would definitely get some of the rough work out of my code.

In fact, my original code was much neat and spread over 3 classes, but after its work I decided to combine all this into one paste for this.

I would prefer to add the [SqlTable] attribute to the domain classes, as well as the [PrimaryKey] to the main ID key, instead of having a code that generates an identifier.

This way, the code can correctly place PK, Index, and FK constraints in the correct columns.

However, VanOrMan could probably use my code to generate 99% of what it needs, and then just tweak the SQL in the editor before running it.

Answer 11

Subsonic is another option. I often use it to create entity classes that map to a database. It has a command line utility that allows you to specify tables, types, and many other useful things.

Answer 12

Try DaoliteMappingTool for .net. This can help you generate classes. Download Form Here

Answer 13

There is a free Schematrix application that generates classes from the database, checks to see if it does the opposite too :) http://www.schematrix.com/products/schemacoder/download.aspx