How can I generate database tables from C# classes?

Answer 1

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 which classes 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:

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 2

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.

Answer 3

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.

Answer 4

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/

Answer 5

I know you're looking for an entire persistence layer, but NHibernate's hbm2ddl task can do this almost as a one-liner.

There is a NAnt task available to call it which may well be of interest.

Answer 6

@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.

Answer 7

@PortMan,

The SQL DataObjects are cool, I was not aware of them. That would definitly take some of the grunt work out of my code.

Actually, my original code was a lot neater and spread across 3 classes, but after it worked, I decided to merge it all into a single paste for here.

I'd consider adding a [SqlTable] Attribute to domain classes, as well as [PrimaryKey] to the main ID key, instead of having the code autogenerate an ID.

By doing that, the code would be able to properly place PK's, Index and FK constraints on the correct columns.

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

Answer 8

Subsonic is also another option. I often use it to generate entity classes that map to a database. It has a command line utility that lets you specify tables, types, and a host of other useful things

Answer 9

Try DaoliteMappingTool for .net. It can help you generate the classes. Download form Here

Answer 10

You can do the opposite, database table to C# classes here: http://pureobjects.com/dbCode.aspx