Convert C# classes (class library) to SQL DDL (tab

Question

I have to convert a set of C# classes (class library) to SQL tables to be used by SQL Server, so that the data can be stored in a database and manipulated through the database.

The problem is that the number of these classes is big (more than 1000 classes), and it would take a long time to setup manually such a database schema (tables, indexes, stored procedures, etc.) - not to mention the class hierarchies that I need to maintain.

So, the question is:

Is there a tool that would help me to create a database schema from a C# class library?

I am not seeking for the perfect tool (but if there is such a tool I would be very happy to know), but for a tool that will help me at least create a starting point.

Please, also note that I would prefer your suggested solution to refer to version 2.0 of the .NET Framework, and being based on traditional practices (T-SQL code like: CREATE TABLE ...), although every suggestion is welcomed.

Answer 1

If the class structure is relational and relatively simple to convert to SQL Create Table or and EF model perhaps you can write your own code generation script to create the .edmx or SQL.

You can use T4 or CodeDom and it would probably take a lot less time than manually creating 1000 tables.

EDIT: I forgot about this when I first wrote my answer. A while back I saw a screencast by Rob Conery about Subsonic Simple Repositroy. Simple Repository allows you to insert an object into the DB and if there is no table for it it creates one for you.

Check out the video here http://subsonicproject.com/docs/Using_SimpleRepository

Answer 2

I don't know of any tool, but for that many tables its perhaps worth writing a small tool using Reflection to get all the properties and spit out create table statement with column name and type defined. A perfect script will be hard to achieved, but like stated in your question, this can give you a very good starting point to then tweak the field type and size.

Answer 3

Just to expand a bit on what others are saying--most ORM-type tools have model-first capability. Much like EF4, NHibernate, and Telerik Open Access, Subsonic (http://www.subsonicproject.com/) has the simple repository pattern which can generate tables from classes.

An alternate option here, is to write a simple reflection-based tool to traverse your classes.

---

Although you didn't ask, I'm curious about your data access needs. We hire relational databases for three primary tasks: persistence, efficient retrieval of information, and declarative referential integrity (e.g. foreign keys). Just creating a table per class is only half the battle.

Do you have a high volume of data (i.e. 100's of gigs or multiple-terabytes)? Lots of reads? lots of writes? Do the classes have natural primary keys? Surrogate keys? Do your classes define relations between each other (i.e. Employees work in Factories).

Do classes have lots of fields? Do you have any meta data that indicates data types with precision, i.e. not just string Name but that Name can be a maximum of 80 characters and is not nullable? Do you need to store English only or do you use languages that require extended character sets like Mandarin? If you don't have a method to indicate precision your database will end up having seriously wide rows (or potentially wide). Many databases limit maximum row size to something in the 8k-64k range. Wide rows impact read & write performance. Using text fields may get around the page size limits, but will result in much more expensive read performance.

Just as important as implementing the database structure is indexing it and using referential integrity. Do the number of classes/tables grow over time? Maybe a more abstract structure with fewer tables would be appropriate.

Just my 2c. Hope that helps.

Answer 4

There's a new CTP out of "Code First" Entity Framework. You may be able to use that to generate your database directly from code after a bit more code writing.

It'll still take a while with 1000 tables to create.

Answer 5

Most object-relational mapping tools include an option to create a database schema based on your mapped classes.

Here's a complete example of how you'd do this using NHibernate and Fluent NHibernate. This is a standalone C# console app that'll map a collection of C# business objects and create a database schema based on them.

Two important caveats:

1. NHibernate (and Fluent NH) require that all properties and methods of your business objects must be declared virtual; this may require modifying some of your existing code.
2. Don't run this code against a live database, ever - by default, it generates DROP statements for all your existing tables and recreates them. It will kill all your data, and this will make you Sad. You have been warned.

Here's program.cs. Note the three namespaces - one containing the program itself, one containing the entities, and one containing an example of a mapping override for Fluent NHibernate (useful if your objects don't adhere to the built-in mapping conventions)

using System;
using System.Collections.Generic;
using System.IO;
using FluentNHibernate.Automapping;
using FluentNHibernate.Automapping.Alterations;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using Schematica.Entities;

namespace Schematica.ConsoleApp {

    class Program {

        const string SCHEMA_FILENAME = "schema.sql";
        const string CONNECTION_STRING = "Data Source=spotgeek;Initial Catalog=dylanhax;Integrated Security=True";

        public static void Main(string[] args) {

            if (File.Exists(SCHEMA_FILENAME)) File.Delete(SCHEMA_FILENAME);

            ConfigureNHibernate(CONNECTION_STRING, MapEntities);

            Console.WriteLine("Exported schema to " + (Path.GetFullPath(SCHEMA_FILENAME)));
            Console.ReadKey(false);
        }


        private static void MapEntities(MappingConfiguration map) {

            // Notice how we're constraining the auto-mapping to only map those entities
            // whose namespace ends with "Entities" - otherwise it'll try to 
            // auto-map every class in the same assembly as Customer.

            map.AutoMappings.Add(
                AutoMap.AssemblyOf<Customer>()
                .Where(type => type.Namespace.EndsWith("Entities"))
                .UseOverridesFromAssemblyOf<Customer>());
        }

        private static Configuration ConfigureNHibernate(string connectionString, Action<MappingConfiguration> mapper) {
            var database = Fluently.Configure().Database(MsSqlConfiguration.MsSql2005.ConnectionString(connectionString));
            return (database.Mappings(mapper).ExposeConfiguration(ExportSchema).BuildConfiguration());
        }

        private static void WriteScriptToFile(string schemaScript) {
            File.AppendAllText(SCHEMA_FILENAME, schemaScript);
        }

        private static void ExportSchema(Configuration config) {
            bool createObjectsInDatabase = false;
            new SchemaExport(config).Create(WriteScriptToFile, createObjectsInDatabase);
        }
    }
}

// This demonstrates how to override auto-mapped properties if your objects don't 
// adhere to FluentNH mapping conventions.
namespace Schematica.Mappings {
    public class ProductMappingOverrides : IAutoMappingOverride<Product> {
        public void Override(AutoMapping<Product> map) {

            // This specifies that Product uses ProductCode as the primary key, 
            // instead of the default Id field.
            map.Id(product => product.ProductCode);
        }
    }
}


// This is the namespace containing your business objects - the things you want to export to your database.
namespace Schematica.Entities {
    public class Customer {
        public virtual int Id { get; set; }
        public virtual string Forenames { get; set; }
        public virtual string Surname { get; set; }
    }

    public class Product {
        public virtual Guid ProductCode { get; set; }
        public virtual string Description { get; set; }
    }

    public class Order {
        public virtual int Id { get; set; }
        private IList<Product> products = new List<Product>();
        public virtual IList<Product> Products {
            get { return products; }
            set { products = value; }
        }
        public virtual Customer Customer { get; set; }
    }
}

And here's what's exported to schema.sql by the above code:

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK952904EBD5E0278A]') AND parent_object_id = OBJECT_ID('[Product]'))
    alter table [Product]  drop constraint FK952904EBD5E0278A

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FKD1436656C882C014]') AND parent_object_id = OBJECT_ID('[Order]'))
    alter table [Order]  drop constraint FKD1436656C882C014

if exists (select * from dbo.sysobjects where id = object_id(N'[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Customer]
if exists (select * from dbo.sysobjects where id = object_id(N'[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Product]
if exists (select * from dbo.sysobjects where id = object_id(N'[Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Order]

create table [Customer] (
    Id INT IDENTITY NOT NULL,
   Forenames NVARCHAR(255) null,
   Surname NVARCHAR(255) null,
   primary key (Id)
)

create table [Product] (
    ProductCode UNIQUEIDENTIFIER not null,
   Description NVARCHAR(255) null,
   Order_id INT null,
   primary key (ProductCode)
)

create table [Order] (
    Id INT IDENTITY NOT NULL,
   Customer_id INT null,
   primary key (Id)
)

alter table [Product] 
    add constraint FK952904EBD5E0278A 
    foreign key (Order_id) 
    references [Order]

alter table [Order] 
    add constraint FKD1436656C882C014 
    foreign key (Customer_id) 
    references [Customer]

Answer 6

If you could use Visual Studio 2010 v4.0 framework there are some new capabilities to generate scripts using "Model First" and entity framework. This isn't helpful for v2.0 obviously but I thought it was worth a mention.

The "Model First" feature lets you design a conceptual (CSDL) model in the Entity Designer, and then generate the storage (SSDL) model, as well as the mapping (MSL) between these. In addition, we also generate T-SQL scripts to create the database for you. To run the Model First wizard, just right-click on the Entity Designer surface, and choose "Generate Database from Model...". There's an in-depth blog post here. Also, check this article out.