Unit Test to Run .sql script to SQL Create Database

Question

Can anyone point me in the direction of how I could get a NUnit test to run a .sql file to Create / Setup a database.

I know about the the TestFixtureSetUp and TestFixtureTearDown attributes / methods in NUnit.

So I KNOW how to call methods before and after all or each unit tests.

I'm just unsure of how to load and execute the contents of a .sql file agains a SQL Server 2005 database programatically.

Any examples?

This is part of our TDD / CI. We are wanting to create the database before and tear down the database after executing unit tests.

Cheers,

-- Lee

UPDATE : I've now pulled out the creation of the database / running of the .sql scripts outside of the unit tests using a batch file to call sqlcmd. And that seems to work fine.

Answer 1

i use a text file with one sql statement per line. the TestFixtureSetUp method reads this file (line by line) and executes each sql statement with ADO.NET's IDbCommand:

something like:

using (IDbConnection cnn = OpenConnection(connectionString))
  foreach (string line in ReadFile(sqlFile))
    using (IDbCommand cmd = cnn.CreateCommand()) {
      cmd.CommandText = line;
      cmd.ExecuteNonQuery();
    }

you might also want to have a look at something like SQL Installer.NET

Answer 2

Have a look at Fluent-Migrator.
It has the ability to run SQL scripts against SQL Server 2005 and can be run from nant.

The purpose of the project is to allow the developer to migrate the database as the domain changes, particularly TDD, but also allow the reversal of changes if so desired.

Answer 3

I've used sqlcmd.exe in a .bat file to run the .sql scripts as an separate part of the CI build to execute immediately before the Unit Tests.

This seems to be an acceptable way of doing it.