Executing *.sql file form C#

Sometimes we need to run *.sql script from our application for installing data base. SqlCommand is not right for running installation script because installation script consists of DDL and GO command. Here we use smo library for executing *.sql script.

For doing above operation we need to add following references

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo

Took me a few minutes to find at first. If you can’t find them in the .NET tab, you might not have it installed. The paths on my computer to these files are:
• c:Program FilesMicrosoft SQL Server90SDKAssembliesMicrosoft.SqlServer.ConnectionInfo.dll
• c:Program FilesMicrosoft SQL Server90SDKAssembliesMicrosoft.SqlServer.Smo.dll

Then we need to add following namespaces

using System.IO;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Then we need to usefollowing code for executing *.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);