Any way, let's start a little journey in which we will create a small Visual Studio project with several simple domain models, the relevant mapping classes and the code to generate the MS SQL Server database scripts.
1. Create a C# console application project named "OrderingSystem".
2. In the project add reference to nhibernate.dll and fluentnhibernate.dll. You can download them from Fluent NHibernate or through NuGet. It is version 1.2 at the time of writing this post which is an open source software under BSD license.
3. In the project add a folder named "Domain". In the folder add a class file named "Name.cs" which contains the code of the value object Name. Please add the relevant usings and default namespaces.
public class Name
{
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
}
4. Still in this folder added another class file named "Customer.cs" which represents the entity of Customer.
public class Customer
{
public virtual Guid Id { get; set; }
public virtual Name Name { get; set; }
}
5. Add other model classes in this folder.
public class Employee
{
public virtual Guid Id { get; set; }
public virtual string EmployeeId { get; set; }
public virtual Name Name { get; set; }
}
public class Product
{
public virtual Guid Id { get; set; }
public virtual string Name { get; set; }
public virtual decimal CostPrice { get; set; }
public virtual DateTime AddedDate { get; set; }
}
public class OrderItem
{
public virtual Guid Id { get; set; }
public virtual Product Product { get; set; }
public virtual int Quantity { get; set; }
public virtual Order Order { get; set; }
}
public class Order
{
public virtual Guid Id { get; set; }
public virtual Customer Customer { get; set; }
public virtual Employee Employee { get; set; }
public virtual IList Items { get; set; }
public virtual DateTime Date { get; set; }
}
We have created six domain model classes which contains one value object and five entities. The models we created only contain properties and they don't have any behaviors. Since the purpose of this post is about generating the database scripts from models and their mappings so let's keep it simple and move one.
6. In the project add another folder called "Mapping". In this folder add a map file named "NameMap.cs" for the value object "Name" with default namespace. If you have installed ReSharper then it will prompt you to add correct usings in the file.
public class NameMap : ComponentMap<Name>
{
public NameMap()
{
Map(n => n.FirstName).Not.Nullable();
Map(n => n.LastName).Not.Nullable();
}
}
7. In the folder add a map file named "CustomerMap.cs" for the entity "Customer".
public class CustomerMap : ClassMap<Customer>
{
public CustomerMap()
{
Id(c => c.Id).GeneratedBy.GuidComb();
Component(c => c.Name);
}
}
From the above two mappings we can see that because of the beauty of strongly typed C# it is very readable and concise.
8. Let's add the rest mapping files.
public class EmployeeMap : ClassMap<Employee>
{
public EmployeeMap()
{
Id(e => e.Id).GeneratedBy.GuidComb();
Map(e => e.EmployeeId).Not.Nullable();
Component(e => e.Name);
}
}
public class ProductMap : ClassMap<Product>
{
public ProductMap()
{
Id(p => p.Id).GeneratedBy.GuidComb();
Map(p => p.Name).Not.Nullable();
Map(p => p.CostPrice).Not.Nullable();
Map(p => p.AddedDate).Not.Nullable();
}
}
public class OrderItemMap : ClassMap<OrderItem>
{
public OrderItemMap()
{
Id(i => i.Id).GeneratedBy.GuidComb();
References(i => i.Product).Not.Nullable();
Map(i => i.Quantity).Not.Nullable();
References(i => i.Order).Not.Nullable();
}
}
public class OrderMap : ClassMap<Order>
{
public OrderMap()
{
Id(o => o.Id).GeneratedBy.GuidComb();
References(o => o.Customer).Not.Nullable();
References(o => o.Employee).Not.Nullable();
HasMany(o => o.Items).Inverse();
Map(o => o.Date).Not.Nullable();
}
}
We have finished creating the models and mappings. So far so simple. Press F6 and make sure it builds successfully.
9. Open Microsoft SQL Server Management Studio and log in to the local database server with Windows Authentication. Create a database named "OrderingSystem".
10. In the project create a class file named "DbScriptBuilder.cs" which contains the code for generating the database script file.
using System.Text;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Tool.hbm2ddl;
using OrderingSystem.Domain;
namespace OrderingSystem
{
public class DbScriptBuilder
{
private const string ConnString = "server=(local);database=OrderingSystem;Integrated Security=SSPI;";
public void Build(string filePath)
{
var sb = new StringBuilder();
var config = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2008.ConnectionString(ConnString))
.Mappings(m => m.FluentMappings.AddFromAssemblyOf())
.BuildConfiguration();
var exporter = new SchemaExport(config);
exporter.Create(s => sb.Append(s), false);
using (var writer = new StreamWriter(filePath))
{
writer.WriteLine(sb);
writer.Flush();
}
}
}
}
If you use SQL Server Express then please change "server=(local)" to "server=.\\SQLEXPRESS".
11. Open program.cs and add code into the Main method
var fileName = string.Format(@"c:\temp\OrderingSystem_{0}.sql",
DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss"));
new DbScriptBuilder().Build(fileName);
Console.WriteLine("Finished generating script file.");
12. Press F6 and make sure it builds the project successfully. Press Ctl+F5 and you should be able to see the text "Finished generating script file." from the screen.
13. Go the the folder c:\temp and find the sql script file just generated. Open it in SSMS and run it in database OrderingSystem. Congratulations! You have successfully generated the tables and their relationships from the models and mapping classes without writing any sql code!
Source code download >>
Thanks!!!!
ReplyDeletemy script is empty :(
ReplyDelete