How to execute an .SQL script file using c#

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using Microsoft.SqlServer.Management.Smo;using Microsoft.SqlServer.Management.Common;using System.IO;using System.Data.SqlClient;public partial class ExcuteScript : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");        SqlConnection conn = new SqlConnection(sqlConnectionString);        Server server = new Server(new ServerConnection(conn));        server.ConnectionContext.ExecuteNonQuery(script);    }}

I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.

string script = File.ReadAllText(@"E:\someSqlScript.sql");// split script on GO commandIEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);Connection.Open();foreach (string commandString in commandStrings){    if (!string.IsNullOrWhiteSpace(commandString.Trim()))    {        using(var command = new SqlCommand(commandString, Connection))        {            command.ExecuteNonQuery();        }    }}     Connection.Close();

This Works on Framework 4.0 or Higher. Supports "GO". Also show the error message, line, and sql command.

using System.Data.SqlClient;        private bool runSqlScriptFile(string pathStoreProceduresFile, string connectionString)    {        try        {            string script = File.ReadAllText(pathStoreProceduresFile);            // split script on GO command            System.Collections.Generic.IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",                                     RegexOptions.Multiline | RegexOptions.IgnoreCase);            using (SqlConnection connection = new SqlConnection(connectionString))            {                connection.Open();                foreach (string commandString in commandStrings)                {                    if (commandString.Trim() != "")                    {                        using (var command = new SqlCommand(commandString, connection))                        {                        try                        {                            command.ExecuteNonQuery();                        }                        catch (SqlException ex)                        {                            string spError = commandString.Length > 100 ? commandString.Substring(0, 100) + " ...\n..." : commandString;                            MessageBox.Show(string.Format("Please check the SqlServer script.\nFile: {0} \nLine: {1} \nError: {2} \nSQL Command: \n{3}", pathStoreProceduresFile, ex.LineNumber, ex.Message, spError), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);                            return false;                        }                    }                    }                }                connection.Close();            }        return true;        }        catch (Exception ex)        {            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);            return false;        }    }