Introduction
Many times we need to execute multiple script files from a specific directory/subdirectory. I will explain very simple way to execute multiple script files (*.sql) from a directory.
Using the code
I use a simple ADO.NET connection and command object. I also use System.Transactions.TranscationScope
object to manage transaction. Just one thing you should remember is the GO
statement. Actually it is not a TSQL statement. So I need to remove that. But if you use osql, Enterprise Manager etc., then they support Go
statement as a separator.
public static OperationResult ExecuteScript(string connectionString, string directory, string scriptFileExtension)
{
string[] files = Directory.GetFiles(directory, scriptFileExtension, SearchOption.AllDirectories);
if (null == files || files.Length == 0)
return new OperationResult(false, "no script file found to directory " + directory);
var result = new OperationResult(true, null);
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; var regex = new Regex(Environment.NewLine + "go", RegexOptions.IgnoreCase);
foreach (string fileFullName in files)
{
string content = File.ReadAllText(fileFullName);
content = regex.Replace(content, string.Empty);
cmd.CommandText = content;
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
return new OperationResult(false, "File Name: " + fileFullName + " Error Message:" + ex.Message);
}
}
}
}
if (result.Success)
scope.Complete();
}
return result;
}
Client code
string scriptDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ScriptFiles");
bool exists = Directory.Exists(scriptDirectory);
string connString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
OperationResult result = ScriptExecutor.ExecuteScript(connString, scriptDirectory, "*.sql");
if (result.Success)
Console.WriteLine("Scripts executed successfully");
else
Console.WriteLine("Failed to execute:" + result.Error);
Console.ReadKey();
Conclusion
I have shown this with a console application. You can build any tool with window/WPF technology or anything else that you might be easily reusable and disputable. I uploaded source code that is developed by Visual Studio 2012 with .NET Framework 4.5 But no such framework feature I used there. So with any framework version you can use it.