Nhibernate and sql scripts

Question

Dont suppose anyone knows how to execute a sql script from nhibernate. Ive got some static data that I need in a database, that is contained, in a staticData.sql file. When I run my integration tests, I recreate the database with the schema export command, and I need to run this data in. I realise I can get it in using .net, but Id really like to only have one data access technique in my project...

Thanks

Answer 1

There's nothing wrong using plain ADO.NET to insert data into the database in integration test. For batch inserts it is even better than NHibernate. The way you put your database in a known state for a test doesn't matter that much, what is important is to test the way you access your data in the application.

Answer 2

Try using NHibernate.ISession.CreateSQLQuery(string queryString)

    private NHibernate.ISession session; // Initialized somewhere

    public void ExecuteSQLFile(string sqlFilePath)
    {
        string sql;

        using (FileStream strm = File.OpenRead(sqlFile))
        {
            var reader = new StreamReader(strm);
            sql = reader.ReadToEnd();
        }

        var regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
        string[] lines = regex.Split(sql);

        foreach (string line in lines)
        {
            IQuery query = session.CreateSQLQuery(line);
            query.ExecuteUpdate();
        }
    }

Here is the documentation: Chapter 16. Native SQL