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 command
IEnumerable<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;
}
}