Introduction
Sometimes we need to run *.sql script from our application to install the database. SqlCommand is not right for running the installation script because the installation script consists of DDL and GO commands. Here we use smo library for executing the *.sql script.
For doing the above operation, we need to add the following references
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
If you are unable to find the above references
Took me a few minutes to find at first. If you can't find them in the .NET tab, you might not have them installed. The paths on my computer to these files are:
- c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
- c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Then we need to add the following namespaces
using System.IO;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
Then we need to use the following code for executing the *.sql script
string sqlConnectionString = txtConstring.Text; //connection string
FileInfo file = new FileInfo(path + "Procedure_fn.sql"); //*.sql file path
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);