first write a class which will execute script as follows
public class ScriptExecutor
{
public static void ExecuteScript(string connectionString, string directory)
{
string[] files = Directory.GetFiles(directory, "*.sql", SearchOption.AllDirectories);
try
{
var to = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, to))
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 0;
foreach (string fileFullName in files)
{
string content = File.ReadAllText(fileFullName);
content = content.ToLower().Replace("\r\ngo", "\r\n");
cmd.CommandText = content;
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
scope.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
How to execute this code
string scriptDirectory = @"D:\ScriptDir";
string connString = "Data Source=HABIB-PC; Initial Catalog=**; user Id=**; Password=***;";
ScriptExecutor.ExecuteScript(connString, scriptDirectory);