Executing *.sql file form C#

How to execute sql script file from C#

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);