public IDbConnection CreateConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
return(CreateConnection(ConnectionString));
}
}
public Result Initialise()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
Result result;
try
{
using (TransactionScope scope = new TransactionScope())
{
SqlConnection conn = (SqlConnection)DatabaseManager.DatabaseEngine.GetConnection();
result = ExecuteScript(conn, ResourceLoader.LoadTextResource("Sprocket.Data.SqlServer2005.scripts.sql"));
if (result.Succeeded && OnInitialise != null)
{
OnInitialise(result);
}
if (result.Succeeded)
{
scope.Complete();
}
return(result);
}
}
finally
{
DatabaseManager.DatabaseEngine.ReleaseConnection();
}
}
}
public IDbConnection CreateConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
return CreateConnection(ConnectionString);
}
}
public IDbConnection GetConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
if (stack == null)
{
stack = new Stack <bool>();
}
stack.Push(true);
if (stack.Count == 1)
{
SqlConnection conn = (SqlConnection)CreateConnection();
Conn = conn;
return(conn);
}
else
{
SqlConnection c = Conn as SqlConnection;
if (c != null)
{
if (c.State == ConnectionState.Open)
{
return(Conn as SqlConnection);
}
c.Dispose();
Conn = null;
}
stack = null;
return(GetConnection());
}
}
}
public Result ExecuteScript(SqlConnection conn, string script)
{
using (ProgramFlowLog log = new ProgramFlowLog("conn", "script"))
{
using (TransactionScope scope = new TransactionScope())
{
string[] sql = Regex.Split(script, @"^[ \t]*go[ \t\r\n]*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
for (int i = 0; i < sql.Length; i++)
{
if (sql[i].Trim() == "")
{
continue;
}
using (TransactionScope innerscope = new TransactionScope())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql[i];
cmd.CommandType = CommandType.Text;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return(new Result(ex.Message + Environment.NewLine + Environment.NewLine + "The offending SQL was:" + Environment.NewLine + sql[i]));
}
innerscope.Complete();
}
}
scope.Complete();
return(new Result());
}
}
}
public IDbConnection CreateConnection(string connectionString)
{
using (ProgramFlowLog log = new ProgramFlowLog("connectionString"))
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
return conn;
}
}
public IDbConnection CreateConnection(string connectionString)
{
using (ProgramFlowLog log = new ProgramFlowLog("connectionString"))
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
return(conn);
}
}
public void ForceCloseConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
SqlConnection c = Conn as SqlConnection;
if (c != null)
{
if (c.State == ConnectionState.Open)
{
c.Close();
}
c.Dispose();
Conn = null;
stack = null;
}
}
}
public Result CheckConfiguration()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
connectionString = SprocketSettings.GetValue("ConnectionString");
if (connectionString == null)
return new Result("No value exists in Web.config for ConnectionString. SqlServer2005Database requires a valid connection string.");
try
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
return new Result("The ConnectionString value was unable to be used to open the database. The error was: " + ex.Message);
}
return new Result();
}
}
public long GetUniqueID()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("GetUniqueID", conn);
SqlParameter prm = new SqlParameter("@ID", SqlDbType.BigInt);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
return((long)prm.Value);
}
}
}
}
public Result CheckConfiguration()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
connectionString = SprocketSettings.GetValue("ConnectionString");
if (connectionString == null)
{
return(new Result("No value exists in Web.config for ConnectionString. SqlServer2005Database requires a valid connection string."));
}
try
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
return(new Result("The ConnectionString value was unable to be used to open the database. The error was: " + ex.Message));
}
return(new Result());
}
}
public void ReleaseConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
if (stack == null)
{
stack = new Stack <bool>();
}
if (stack.Count == 1)
{
SqlConnection conn = Conn as SqlConnection;
if (conn != null)
{
conn.Close();
conn.Dispose();
Conn = null;
}
}
if (stack.Count > 0)
{
stack.Pop();
}
}
}
public void ForceCloseConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
SqlConnection c = Conn as SqlConnection;
if (c != null)
{
if (c.State == ConnectionState.Open)
c.Close();
c.Dispose();
Conn = null;
stack = null;
}
}
}
public long GetUniqueID()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("GetUniqueID", conn);
SqlParameter prm = new SqlParameter("@ID", SqlDbType.BigInt);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
return (long)prm.Value;
}
}
}
}
public Result Initialise()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
Result result;
try
{
using (TransactionScope scope = new TransactionScope())
{
SqlConnection conn = (SqlConnection)DatabaseManager.DatabaseEngine.GetConnection();
result = ExecuteScript(conn, ResourceLoader.LoadTextResource("Sprocket.Data.SqlServer2005.scripts.sql"));
if (result.Succeeded && OnInitialise != null)
OnInitialise(result);
if (result.Succeeded)
scope.Complete();
return result;
}
}
finally
{
DatabaseManager.DatabaseEngine.ReleaseConnection();
}
}
}
public void ReleaseConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
if (stack == null)
stack = new Stack<bool>();
if (stack.Count == 1)
{
SqlConnection conn = Conn as SqlConnection;
if (conn != null)
{
conn.Close();
conn.Dispose();
Conn = null;
}
}
if (stack.Count > 0)
stack.Pop();
}
}
public Result ExecuteScript(SqlConnection conn, string script)
{
using (ProgramFlowLog log = new ProgramFlowLog("conn", "script"))
{
using (TransactionScope scope = new TransactionScope())
{
string[] sql = Regex.Split(script, @"^[ \t]*go[ \t\r\n]*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
for (int i = 0; i < sql.Length; i++)
{
if (sql[i].Trim() == "")
continue;
using (TransactionScope innerscope = new TransactionScope())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql[i];
cmd.CommandType = CommandType.Text;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return new Result(ex.Message + Environment.NewLine + Environment.NewLine + "The offending SQL was:" + Environment.NewLine + sql[i]);
}
innerscope.Complete();
}
}
scope.Complete();
return new Result();
}
}
}
public IDbConnection GetConnection()
{
using (ProgramFlowLog log = new ProgramFlowLog())
{
if (stack == null)
stack = new Stack<bool>();
stack.Push(true);
if (stack.Count == 1)
{
SqlConnection conn = (SqlConnection)CreateConnection();
Conn = conn;
return conn;
}
else
{
SqlConnection c = Conn as SqlConnection;
if (c != null)
{
if (c.State == ConnectionState.Open)
return Conn as SqlConnection;
c.Dispose();
Conn = null;
}
stack = null;
return GetConnection();
}
}
}
