domain driven design with nhibernate and sql server

Question

I have a reference application that I use to work through DDD issues, and my current focus is on persistence. An alternate title for this post could have been DDD / TDD persistence tools and methods, but that is a (very) broad topic and I do have a specific question on SQL Server.

I'm going to outline my current tool set in this paragraph as background, so feel free to skip it to just answer the question. I'm using NHibernate, Fluent NHibernate, Rhino, Visual Studio 2008 and SQL Server. My current thinking is to use NHibernate to generate the db after any data mapping change, look at the generated tables in VS 2008, and see if my persistence mapping tests all pass. I had originally wanted to use SQLite as the test db, but I am skeptical that it is an accurate gauge of correct mapping in some ways (like referential integrity). I then tried using SQL Ce, but found it frustrating to eye ball the generated tables with VS. That's why I'm now figuring to just use SQL Server in the first place.

The following command object generates the db:

public class GenerateNewDb_SqlServer : ICommand
    {
        public GenerateNewDb_SqlServer(Configuration cfg)
            : base(string.Format("Update a Sql Server Database"))
        {
            _cfg = cfg;
        }

        public override void Execute()
        {
            try
            {
                var schema = new SchemaExport(_cfg);
                schema.Create(true, true);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Schema Export Error Message: " + ex);
            }
        }
    }

The problem is that old database objects are still there. I need to somehow delete the whole sql server db each time before using the NHibernate schema tool to create it. Can someone please tell me how to do that in code?

---

Is there something off the NHibernate.Cfg.Configuration object I can use to drop the db? What would the code look like?

Cheers, Berryl

Answer 1

You just need to execute a "drop database" operation on the DB you want, using an ODBC driver.

Edit: I don't believe there's anything in the NHibernate configuration that will do this automatically for you; it needs to be part of an external process you define.

Answer 2

consider using migration scripts for your SQL entities, you can try

Ruby migration script or Subsonic

Answer 3

Thankfully, it's already part of SchemaExport. I'm not sure if it will drop the entire database, but it will drop all the referenced SQL objects. Something like this?

private void DropSchema(Configuration _cfg)
 {  
        new SchemaExport(_cfg)
            .Drop(
                false, //script to the console
                true, //execute against db
            );
 }

P.S. Usually, you'll see this command run directly before SchemaExport.Create so you get a fresh database...

P.P.S. bunch of great model-driven stuff for NHibernate here (this screencast series is amazing!)