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

If you are unable to find 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 it 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 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);