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:
- 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.
- 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]