<p>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:</p>

<pre><code>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; }
    }
}
</code></pre>

<p>I'd expect the following SQL:</p>

<pre><code>CREATE TABLE Foo
(
    Property1 VARCHAR(500),
    Property2 INT
)
</code></pre>

<p>I'm also wondering how you could handle complex types.  For example, in the previously cited class, if we changed that to be :</p>

<pre><code>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; }
    }
}
</code></pre>

<p></p>

<p>How could I handle this?</p>

<p>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.</p>

[{'answer_id': 47251, 'author': 'Mark Cidade', 'author_id': 1659, 'author_profile': 'https://Stackoverflow.com/users/1659', 'pm_score': 1, 'selected': False, 'text': "<p>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. </p>\n\n<p>You may also want to pre-specify <em>which</em> 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:</p>\n\n<pre><code>CREATE TABLE MiscTypes /* may have to include standard types as well */\n ( TypeID INT,\n   TypeName VARCHAR(...)\n )\n\nCREATE TABLE MiscProperties\n ( PropertyID INT,\n   DeclaringTypeID INT, /* FK to MiscTypes */\n   PropertyName VARCHAR(...),\n   ValueTypeID INT /* FK to MiscTypes */\n )\n\nCREATE TABLE MiscData\n (  ObjectID INT,\n    TypeID  INT\n )\n\nCREATE TABLE MiscValues\n ( ObjectID INT, /* FK to MiscData*/\n   PropertyID INT,\n   Value VARCHAR(...)\n )\n</code></pre>\n"}, {'answer_id': 47273, 'author': 'FlySwat', 'author_id': 1965, 'author_profile': 'https://Stackoverflow.com/users/1965', 'pm_score': 8, 'selected': True, 'text': '<p>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:</p>\n\n<pre><code>using System;\nusing System.Collections.Generic;\nusing System.Text;\nusing System.Reflection;\n\nnamespace TableGenerator\n{\n    class Program\n    {\n        static void Main(string[] args)\n        {\n            List&lt;TableClass&gt; tables = new List&lt;TableClass&gt;();\n\n            // Pass assembly name via argument\n            Assembly a = Assembly.LoadFile(args[0]);\n\n            Type[] types = a.GetTypes();\n\n            // Get Types in the assembly.\n            foreach (Type t in types)\n            {\n                TableClass tc = new TableClass(t);                \n                tables.Add(tc);\n            }\n\n            // Create SQL for each table\n            foreach (TableClass table in tables)\n            {\n                Console.WriteLine(table.CreateTableScript());\n                Console.WriteLine();\n            }\n\n            // Total Hacked way to find FK relationships! Too lazy to fix right now\n            foreach (TableClass table in tables)\n            {\n                foreach (KeyValuePair&lt;String, Type&gt; field in table.Fields)\n                {\n                    foreach (TableClass t2 in tables)\n                    {\n                        if (field.Value.Name == t2.ClassName)\n                        {\n                            // We have a FK Relationship!\n                            Console.WriteLine("GO");\n                            Console.WriteLine("ALTER TABLE " + table.ClassName + " WITH NOCHECK");\n                            Console.WriteLine("ADD CONSTRAINT FK_" + field.Key + " FOREIGN KEY (" + field.Key + ") REFERENCES " + t2.ClassName + "(ID)");\n                            Console.WriteLine("GO");\n\n                        }\n                    }\n                }\n            }\n        }\n    }\n\n    public class TableClass\n    {\n        private List&lt;KeyValuePair&lt;String, Type&gt;&gt; _fieldInfo = new List&lt;KeyValuePair&lt;String, Type&gt;&gt;();\n        private string _className = String.Empty;\n\n        private Dictionary&lt;Type, String&gt; dataMapper\n        {\n            get\n            {\n                // Add the rest of your CLR Types to SQL Types mapping here\n                Dictionary&lt;Type, String&gt; dataMapper = new Dictionary&lt;Type, string&gt;();\n                dataMapper.Add(typeof(int), "BIGINT");\n                dataMapper.Add(typeof(string), "NVARCHAR(500)");\n                dataMapper.Add(typeof(bool), "BIT");\n                dataMapper.Add(typeof(DateTime), "DATETIME");\n                dataMapper.Add(typeof(float), "FLOAT");\n                dataMapper.Add(typeof(decimal), "DECIMAL(18,0)");\n                dataMapper.Add(typeof(Guid), "UNIQUEIDENTIFIER");\n\n                return dataMapper;\n            }\n        }\n\n        public List&lt;KeyValuePair&lt;String, Type&gt;&gt; Fields\n        {\n            get { return this._fieldInfo; }\n            set { this._fieldInfo = value; }\n        }\n\n        public string ClassName\n        {\n            get { return this._className; }\n            set { this._className = value; }\n        }\n\n        public TableClass(Type t)\n        {\n            this._className = t.Name;\n\n            foreach (PropertyInfo p in t.GetProperties())\n            {\n                KeyValuePair&lt;String, Type&gt; field = new KeyValuePair&lt;String, Type&gt;(p.Name, p.PropertyType);\n\n                this.Fields.Add(field);\n            }\n        }\n\n        public string CreateTableScript()\n        {\n            System.Text.StringBuilder script = new StringBuilder();\n\n            script.AppendLine("CREATE TABLE " + this.ClassName);\n            script.AppendLine("(");\n            script.AppendLine("\\t ID BIGINT,");\n            for (int i = 0; i &lt; this.Fields.Count; i++)\n            {\n                KeyValuePair&lt;String, Type&gt; field = this.Fields[i];\n\n                if (dataMapper.ContainsKey(field.Value))\n                {\n                    script.Append("\\t " + field.Key + " " + dataMapper[field.Value]);\n                }\n                else\n                {\n                    // Complex Type? \n                    script.Append("\\t " + field.Key + " BIGINT");\n                }\n\n                if (i != this.Fields.Count - 1)\n                {\n                    script.Append(",");\n                }\n\n                script.Append(Environment.NewLine);\n            }\n\n            script.AppendLine(")");\n\n            return script.ToString();\n        }\n    }\n}\n</code></pre>\n\n<p>I put these classes in an assembly to test it:</p>\n\n<pre><code>public class FakeDataClass\n{\n    public int AnInt\n    {\n        get;\n        set;\n    }\n\n    public string AString\n    {\n        get;\n        set;\n    }\n\n    public float AFloat\n    {\n        get;\n        set;\n    }\n\n    public FKClass AFKReference\n    {\n        get;\n        set;\n    }\n}\n\npublic class FKClass\n    {\n        public int AFKInt\n        {\n            get;\n            set;\n        }\n    }\n</code></pre>\n\n<p>And it generated the following SQL:</p>\n\n<pre><code>CREATE TABLE FakeDataClass\n(\n         ID BIGINT,\n         AnInt BIGINT,\n         AString NVARCHAR(255),\n         AFloat FLOAT,\n         AFKReference BIGINT\n)\n\n\nCREATE TABLE FKClass\n(\n         ID BIGINT,\n         AFKInt BIGINT\n)\n\n\nGO\nALTER TABLE FakeDataClass WITH NOCHECK\nADD CONSTRAINT FK_AFKReference FOREIGN KEY (AFKReference) REFERENCES FKClass(ID)\nGO\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 47274, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 47275, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 0, 'selected': False, 'text': '<p>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 <a href="http://www.tangiblearchitect.net/visual-studio/" rel="nofollow noreferrer">http://www.tangiblearchitect.net/visual-studio/</a></p>\n'}, {'answer_id': 47350, 'author': 'Cheekysoft', 'author_id': 1820, 'author_profile': 'https://Stackoverflow.com/users/1820', 'pm_score': 0, 'selected': False, 'text': '<p>I know you\'re looking for an entire persistence layer, but NHibernate\'s hbm2ddl task can do this almost as a one-liner.</p>\n\n<p>There is a <a href="http://biasecurities.com/blog/2005/nhibernate-hbm2ddl-nant-task/" rel="nofollow noreferrer">NAnt task</a> available to call it which may well be of interest.</p>\n'}, {'answer_id': 47389, 'author': 'Portman', 'author_id': 1690, 'author_profile': 'https://Stackoverflow.com/users/1690', 'pm_score': 4, 'selected': False, 'text': '<p>@Jonathan Holland</p>\n\n<p>Wow, I think that\'s the most raw work I\'ve ever seen put into a StackOverflow post. Well done. <strong>However</strong>, instead of constructing DDL statements as strings, you should definitely use the <a href="http://msdn.microsoft.com/en-us/library/ms162169.aspx" rel="noreferrer">SQL Server Management Objects</a> classes introduced with SQL 2005.</p>\n\n<p>David Hayden has a post entitled <a href="http://www.davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx" rel="noreferrer">Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation</a> that walks through how to create a table using SMO. The strongly-typed objects make it a breeze with methods like:</p>\n\n<pre><code>// Create new table, called TestTable\nTable newTable = new Table(db, "TestTable");\n</code></pre>\n\n<p>and</p>\n\n<pre><code>// Create a PK Index for the table\nIndex index = new Index(newTable, "PK_TestTable");\nindex.IndexKeyType = IndexKeyType.DriPrimaryKey;\n</code></pre>\n\n<p>VanOrman, if you\'re using SQL 2005, definitely make SMO part of your solution.</p>\n'}, {'answer_id': 48152, 'author': 'IanStallings', 'author_id': 4933, 'author_profile': 'https://Stackoverflow.com/users/4933', 'pm_score': 0, 'selected': False, 'text': '<p><a href="http://subsonicproject.com/" rel="nofollow noreferrer">Subsonic</a> 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</p>\n'}, {'answer_id': 415162, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': -1, 'selected': False, 'text': '<p>Try DaoliteMappingTool for .net. It can help you generate the classes.\nDownload form <a href="http://www.idevlite.com/download/daolitemappingtool(net).zip" rel="nofollow noreferrer">Here</a></p>\n'}, {'answer_id': 750311, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': -1, 'selected': False, 'text': '<p>You can do the opposite, database table to C# classes here:\n<a href="http://pureobjects.com/dbCode.aspx" rel="nofollow noreferrer">http://pureobjects.com/dbCode.aspx</a></p>\n'}, {'answer_id': 10033792, 'author': 'Kai Sevelin', 'author_id': 1315993, 'author_profile': 'https://Stackoverflow.com/users/1315993', 'pm_score': 2, 'selected': False, 'text': '<p>Try out my CreateSchema extension method for objects at <a href="http://createschema.codeplex.com/" rel="nofollow">http://createschema.codeplex.com/</a></p>\n\n<p>It returns a string for any object containing CREATE TABLE scripts. </p>\n'}, {'answer_id': 30419749, 'author': 'Nitin', 'author_id': 290174, 'author_profile': 'https://Stackoverflow.com/users/290174', 'pm_score': 0, 'selected': False, 'text': '<p>There is a free app, Schematrix which generates classes from DB, check out if does the reverse too :) <a href="http://www.schematrix.com/products/schemacoder/download.aspx" rel="nofollow">http://www.schematrix.com/products/schemacoder/download.aspx</a></p>\n'}, {'answer_id': 39836180, 'author': 'tatigo', 'author_id': 906258, 'author_profile': 'https://Stackoverflow.com/users/906258', 'pm_score': 2, 'selected': False, 'text': '<p>As of 2016 (I think), you can use Entity Framework 6 Code First to generate SQL schema from poco c# classes or to use Database First to generate c# code from sql.\n<a href="https://msdn.microsoft.com/en-ca/data/jj193542.aspx" rel="nofollow noreferrer">Code First to DB walkthrough </a></p>\n'}]
['https://Stackoverflow.com/questions/47239', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4550/']