SqlScriptInstaller
In my previous post I described a general-purpose Installer that can be used to create a SQL Server database during installation (and delete it again during uninstallation). As promised, this post picks up where the other stopped to describe another Installer that can execute arbitrary T-SQL scripts against a database.
You would typically use this SqlScriptInstaller in conjunction with the SqlDatabaseInstaller to execute T-SQL scripts against the database that SqlDatabaseInstaller creates.
One of the challenges of executing T-SQL scripts from managed code lies in the frequently encountered need to support SQLCMD commands such as GO (as you may know, GO is not a T-SQL keyword, so if you try to execute a T-SQL script with GO commands from ADO.NET, you will get an exception). While you could write a simple parser that splits up a script according to its GO commands, a more correct and full-featured solution is to use SQL Server Management Objects (SMO) to execute the script.
SMO is available if you have installed the SQL Server Client Tools. If that constraint is not acceptable, you will have to write your own SQLCMD parser and use ADO.NET, but you should still be able to reuse the principles outlined below.
SqlScriptInstaller uses SMO to execute T-SQL scripts:
public class SqlScriptInstaller : Installer
{
private readonly string sqlScript_;
public SqlScriptInstaller(string sqlScript)
{
this.sqlScript_ = sqlScript;
}
public SqlScriptInstaller(Stream sqlStream)
{
using (StreamReader sr = new StreamReader(sqlStream))
{
this.sqlScript_ = sr.ReadToEnd();
}
}
public override void Install(IDictionary stateSaver)
{
string connectionString = this.Context.Parameters["ConnectionString"];
using (SqlConnection conn = new SqlConnection(connectionString))
{
ServerConnection serverConn = new ServerConnection(conn);
Server sqlServer = new Server(serverConn);
sqlServer.ConnectionContext.ExecuteNonQuery(this.sqlScript_);
}
base.Install(stateSaver);
}
}
The serverConn variable is an instance of Microsoft.SqlServer.Management.Common.ServerConnection and sqlServer an instance of Microsoft.SqlServer.Management.Smo.Server, so you will need to add references to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo, respectively. The ExecuteNonQuery method on the ConnectionContext understands SQLCMD commands, so your script can have as many GO commands as you need.
Notice that the SqlScriptInstaller uses the same ConnectionString parameter as SqlDatabaseInstaller, but this time uses the connection string unmodified.
In this incarnation, I haven't implemented Uninstall, since I tend to rely on SqlDatabaseInstaller to simply delete the database during uninstallation, but strictly speaking, to be truly general-purpose, SqlScriptInstaller should be able to work independently. If this feature is needed, it would be simple to implement Uninstall by executing a second, compensating T-SQL script, but I'll leave that as an exercise to the reader :)
By supplying T-SQL scripts to SqlScriptInstaller you can use SQL DDL statements to populate the database schema in a database you previously created with SqlDatabaseInstaller.