Earlier this week I needed a way to insert test data into my database that was being regenerated at the start of each test fixture. For most scripts, you can use a simple command with CommandText to get the job done. But this doesn’t work with certain Sql statements, such as GO. Luckily, I stumbled across Microsoft’s Sql Server Management classes which turned out to be exactly what I was looking for.

To use them, we first need to create references to the Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo assemblies.

CreateRef1

CreateRef2

Now, we need to reference those namespaces in our code file.

using System.Data.SqlClient;


using System.IO;


using Microsoft.SqlServer.Management.Smo;


using Microsoft.SqlServer.Management.Common;


 


And now for the code to actually run the script. (I’m calling this method from within RebuildDatabase() which is called in the establish_context(), or 

					

						[TestFixtureSetup], method of the repository tests.)


public void InsertTestData()


{


    using (var conn = new SqlConnection("<connection string>"))


    {


        FileInfo fileInfo = new FileInfo(@"Sql\InsertSeedData.sql");


        var sql = fileInfo.OpenText().ReadToEnd();


        Server server = new Server(new ServerConnection(conn));


        server.ConnectionContext.ExecuteNonQuery(sql);


    }


}


It’s worth noting that I’m including the Sql script that’s being executed here as a linked file inside the project. The physical file is actually located in an upper level directory outside of the project. Including it as a linked file makes sure that the script is included when the test dlls are being ran. (Just be sure to set the “Copy to Output Directory” property of the file to True in Visual Studio.) Alternatively, you could also you an absolute path to point to your Sql script.


DotNetKicks Image