|
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Data;
using Deployer.Logic;
namespace Deployer.DeployTypes
{
class DatabaseScript : BaseDeployType
{
public DatabaseScript()
{
_isolationLevel = IsolationLevel.ReadCommitted;
}
public DatabaseScript(XmlNode node) : this()
{
_connectionString = node.SelectSingleNode("connectionString").InnerXml;
string isoLvl = XmlUtil.ParseStringNode(node.SelectSingleNode("isolationLevel"), "ReadCommitted");
_isolationLevel = (IsolationLevel)Enum.Parse(typeof(IsolationLevel), isoLvl);
}
private string _connectionString;
private IsolationLevel _isolationLevel;
public override void Execute(System.IO.DirectoryInfo sourceDirectory)
{
foreach (FileInfo fi in sourceDirectory.GetFiles("*.sql"))
{
ExecuteScript(File.ReadAllText(fi.FullName));
}
}
private void ExecuteScript(string sqlText)
{
Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] sqlLines = regex.Split(sqlText);
SqlConnection conn = new SqlConnection(_connectionString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction(_isolationLevel);
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
cmd.Connection = conn;
cmd.Transaction = trans;
foreach (string line in sqlLines)
{
if (line.Trim().Length > 0)
{
cmd.CommandText = line;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
If you liked this article, consider reading
other articles by me. For republishing article on other websites, please contact me by leaving a comment.