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

Question

I need to convert a C # class set (class library) to SQL tables that SQL Server will use so that data can be stored in the database and processed through the database.

The problem is that the number of these classes is large (more than 1000 classes), and it will take a lot of time to manually configure such a database schema (tables, indexes, stored procedures, etc.) - not to mention the class hierarchy, which I need to support.

So the question is:

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

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

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

Answer 1

If you can use the Visual Studio 2010 v4.0 framework, there are several new features for generating scripts using the First Model and entity structures. This is clearly not useful for v2.0, but I thought it was worth mentioning.

" " (CSDL) Entity Designer, (SSDL), (MSL) . , T-SQL . Model First, Designer Entity "Generate Database from Model...". . , .

Answer 2

, , , Reflection, create table statement . script , , , , .

Answer 3

CTP "Code First" Entity Framework. .

1000 .

Answer 4

SQL Create Table EF-, , script .edmx SQL.

T4 CodeDom, , , , 1000 .

EDIT: , . Subsonic Simple Repositroy. , , .

. http://subsonicproject.com/docs/Using_SimpleRepository

Answer 5

- .

, , NHibernate Fluent NHibernate. #, - # .

:

• NHibernate ( Fluent NH) , - ; .
• - - DROP . , . .

program.cs. : , , Fluent NHibernate (, )

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; }
    }
}

schema.sql :

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

, , ORM . EF4, NHibernate Telerik Open Access, Subsonic (http://www.subsonicproject.com/) , .

- .

---

, , . : , (, ). - .

(.. 100 )? ? ? ? ? (.. Employees Factories).

? , , .. string Name, Name 80 ? , , Mandarin? , ( ). - 8k-64k. . text , .

It is also important how to implement the database structure, index it and use referential integrity. Does the number of classes / tables increase over time? Maybe a more abstract structure with fewer tables would be appropriate.

Just my 2c. Hope this helps.