how to create a fresh database before tests run ?

Question

how to create a fresh database (everytime) before tests run from a schema file ?

Answer 1

drop the entire database - don't drop table by table - that adds too much maintenance overhead

Answer 2

I have used the following utility methods for running SQL scripts for setting up databases and test data in a project that I am working with every now and then. It has worked rather well:

internal static void RunScriptFile(SqlConnection conn, string fileName)
{
    long fileSize = 0;
    using (FileStream stream = File.OpenRead(fileName))
    {
        fileSize = stream.Length;
        using (StreamReader reader = new StreamReader(stream))
        {
            StringBuilder sb = new StringBuilder();
            string line = string.Empty;
            while (!reader.EndOfStream)
            {
                line = reader.ReadLine();
                if (string.Compare(line.Trim(), "GO", StringComparison.InvariantCultureIgnoreCase) == 0)
                {
                    RunCommand(conn, sb.ToString());
                    sb.Length = 0;
                }
                else
                {
                    sb.AppendLine(line);
                }
            }
        }
    }
}

private static void RunCommand(SqlConnection connection, string commandString)
{
    using (SqlCommand command = new SqlCommand(commandString, connection))
    {
        try
        {
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(string.Format("Exception while executing statement: {0}", commandString));
            Console.WriteLine(ex.ToString());
        }
    }
}

I have used the Database Publishing Wizard to generate SQL scripts (and in some cases edited them to include only the data I want to use in the test), and just pass the script file paths into the RunScriptFile method before the tests. The method parses the script file and executes each part that is separated by a GO line separately (I found that this greatly helped in troubleshooting errors that happened while running the SQL scripts).

I has been a while since I wrote the code, but I think it requires the the script file ends with a GO line in order for the last part of it to be executed.

Answer 3

You can use the SchemaExport class in NHibernate to do this in code:

var schema = new SchemaExport(config);
schema.Drop(true, true);
schema.Execute(true, true, false);

Answer 4

Have a look at these posts.

Ayende Rahien - nhibernate-unit-testing
Scott Muc - unit-testing-domain-persistence-with-ndbunit-nhibernate-and-sqlite

I have found them to be very usefull and basically they are extending the example by Mike Glenn

Answer 5

I use Proteus (Unit Test Utility), available on Google code here :

http://code.google.com/p/proteusproject/

You create a set of data. Each time, you run a unit test, the current data are saved, the set of data is loaded, then you use all the time the same set of data to make your tests. At the end the original data are restored.

Very powerfull

HTH