C-Sharp Code Generation Data Access

How to Auto-Generate Database Tables from C# Classes in Minutes

Creating database tables manually can be a tedious task, especially when dealing with a significant number of classes. If you find yourself in a situation where you need to generate tables quickly without having to write lengthy SQL scripts, you’re in the right place. This blog post provides a solution to auto-generate database tables directly from your C# classes using reflection and a bit of coding magic.

The Problem

Imagine you have several C# classes, and with the growth of your application, you need to create corresponding database tables for these classes. The manual creation of these tables is not only tiresome but also prone to errors. Here’s an example of a simple C# 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; }
    }
}

From this class, you would need a SQL statement like:

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

Additionally, handling complex types within classes, like System.Management.ManagementObject, can complicate things further, as shown in a modified version of the Foo class.

The Solution

Step 1: Setting Up the Environment

To begin, you’ll need to create a console application in C#. This application will inspect your classes and generate SQL scripts corresponding to them. Here’s a simplified version of how the code works:

  1. Load the Assembly: Use reflection to load the assembly containing your C# classes.
  2. Iterate through the Types: Loop through each class within the assembly and collect its properties.
  3. Create SQL Scripts: For each class, generate a CREATE TABLE SQL script based on its properties and types.

Step 2: Code Implementation

Here’s the core of the implementation that you can modify to fit your needs.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace TableGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            List<TableClass> tables = new List<TableClass>();

            Assembly assembly = Assembly.LoadFile(args[0]);
            Type[] types = assembly.GetTypes();

            foreach (Type type in types)
            {
                TableClass tableClass = new TableClass(type);
                tables.Add(tableClass);
            }

            foreach (TableClass table in tables)
            {
                Console.WriteLine(table.CreateTableScript());
                Console.WriteLine();
            }
        }
    }

    public class TableClass
    {
        private List<KeyValuePair<string, Type>> fields = new List<KeyValuePair<string, Type>>();
        public string ClassName { get; set; }

        private Dictionary<Type, string> dataMapper = new Dictionary<Type, string>
        {
            { typeof(int), "BIGINT" },
            { typeof(string), "NVARCHAR(500)" },
            // Add more mappings as needed
        };

        public TableClass(Type type)
        {
            ClassName = type.Name;
            foreach (PropertyInfo property in type.GetProperties())
            {
                fields.Add(new KeyValuePair<string, Type>(property.Name, property.PropertyType));
            }
        }

        public string CreateTableScript()
        {
            var script = new System.Text.StringBuilder();
            script.AppendLine($"CREATE TABLE {ClassName}");
            script.AppendLine("(");
            script.AppendLine("\t ID BIGINT,");

            for (int i = 0; i < fields.Count; i++)
            {
                var field = fields[i];
                script.Append($"\t {field.Key} {(dataMapper.ContainsKey(field.Value) ? dataMapper[field.Value] : "BIGINT")}");

                if (i != fields.Count - 1)
                {
                    script.Append(",");
                }
                script.AppendLine();
            }

            script.AppendLine(")");
            return script.ToString();
        }
    }
}

Step 3: Running Your Code

Once you have the assembly loaded with your data classes, simply run the code. It will produce abstract SQL scripts similar to:

CREATE TABLE FakeDataClass
(
    ID BIGINT,
    AnInt BIGINT,
    AString NVARCHAR(255)
)

CREATE TABLE FKClass
(
    ID BIGINT,
    AFKInt BIGINT
)

Additional Thoughts

  • Attributes for Filtering: Consider adding a custom attribute, such as [SqlTable], to your classes to ensure that only designated classes are converted into tables.
  • Enhancements: The code can be optimized and refactored further. For example, the foreign key relationship handling is quite rudimentary and could benefit from additional logic.

This solution provides a robust starting point for automating the generation of SQL tables based on C# class structures. Happy coding!