protected void ProcessScript(string Script)
{
try
{
if (SMOServer == null)
{
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
}
if (Script.Contains("CREATE DATABASE"))
{
//Replace the dbname here becuase it is a parm in the script.
Script = Script.Replace("[[DB_NAME]]", Database);
Script = Script.Replace("[[DB_PATH]]", DatabasePath);
//In this case, we need to build a new connection string, pointing to master.
string temp = this.Database;
this.Database = "master";
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
db = SMOServer.Databases["master"];
this.Database = temp;
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
if (Script.Contains("DROP DATABASE"))
{
if (SMOServer.Databases.Contains(Database) && Script.Contains("--##KILL ALL PROCESSES##--"))
{
SMOServer.KillAllProcesses(Database);
}
}
//Database is not yet created. We will NOT delete the database in this step. That can be added to the script.
//We don't want to cause undo stress if someone clicks install.
db.ExecuteNonQuery(Script);
}
else
{
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
// SMOServer.ConnectionContext.ConnectTimeout = _Timeout;
SMOServer.ConnectionContext.StatementTimeout = _Timeout;
//Connect to the DB Requested
SMOServer.ConnectionContext.BeginTransaction();
try
{
db = SMOServer.Databases[Database];
db.ExecuteNonQuery(Script);
SMOServer.ConnectionContext.CommitTransaction();
}
catch (Exception err)
{
WriteStatus("Error: " + err.Message);
try
{
SMOServer.ConnectionContext.RollBackTransaction();
}
catch (Exception err1)
{
WriteStatus("Error: " + err1.Message);
}
throw err;
}
}
}//try
catch (Exception err)
{
MMDBLogFile.Log(err);
WriteStatus("Error: " + err.Message);
throw (err);
}
finally
{
SMOServer.ConnectionContext.Disconnect();
db = null;
SMOServer = null;
}
}
protected void ProcessScript(string Script)
{
try
{
if (SMOServer == null)
{
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
}
if (Script.Contains("CREATE DATABASE"))
{
//Replace the dbname here becuase it is a parm in the script.
Script = Script.Replace("[[DB_NAME]]", Database);
Script = Script.Replace("[[DB_PATH]]", DatabasePath);
//In this case, we need to build a new connection string, pointing to master.
string temp = this.Database;
this.Database = "master";
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
db = SMOServer.Databases["master"];
this.Database = temp;
SMOServer = new Microsoft.SqlServer.Management.Smo.Server();
SMOServer.ConnectionContext.NonPooledConnection = true;
SMOServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled;
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
if (Script.Contains("DROP DATABASE"))
{
if (SMOServer.Databases.Contains(Database) && Script.Contains("--##KILL ALL PROCESSES##--"))
{
SMOServer.KillAllProcesses(Database);
}
}
//Database is not yet created. We will NOT delete the database in this step. That can be added to the script.
//We don't want to cause undo stress if someone clicks install.
db.ExecuteNonQuery(Script);
}
else
{
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
SMOServer.ConnectionContext.ConnectionString = conn.ConnectionString;
// SMOServer.ConnectionContext.ConnectTimeout = _Timeout;
SMOServer.ConnectionContext.StatementTimeout = _Timeout;
//Connect to the DB Requested
SMOServer.ConnectionContext.BeginTransaction();
try
{
db = SMOServer.Databases[Database];
db.ExecuteNonQuery(Script);
SMOServer.ConnectionContext.CommitTransaction();
}
catch (Exception err)
{
WriteStatus("Error: " + err.Message);
try
{
SMOServer.ConnectionContext.RollBackTransaction();
}
catch (Exception err1)
{
WriteStatus("Error: " + err1.Message);
}
throw err;
}
}
}//try
catch (Exception err)
{
MMDBLogFile.Log(err);
WriteStatus("Error: " + err.Message);
throw (err);
}
finally
{
SMOServer.ConnectionContext.Disconnect();
db = null;
SMOServer = null;
}
}
protected override void DoWork(object state)
{
var mainCancellationTokenSource = new CancellationToken();
try
{
var tasks = new List <Task>();
var deploymentJobs = _deploymentJobService.Get(p => p.Server.Code == ServerId &&
p.JobType == (int)JobType.Database &&
(p.IsCopySourceDone ?? false) &&
(p.IsStart ?? false) &&
!(p.IsDone ?? false))
.OrderBy(p => p.FailCount)
.Take(2).ToList();
var listJobUpdated = new List <DeploymentJob>();
foreach (var job in deploymentJobs)
{
var localJob = job;
var t = Task.Factory.StartNew(() =>
{
var dbName = localJob.Configuration.DatabaseName;
var loginName = localJob.Configuration.DatabaseUsername;
var loginPassword = EncryptHelper.Decrypt(localJob.Configuration.DatabasePassword);
var pass = PasswordHelper.HashString("123456", "camino");
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString);
var file = new FileInfo(localJob.Configuration.SqlScriptPath);
var script = file.OpenText().ReadToEnd();
script = script.Replace("{{DATABASE_NAME}}", dbName)
.Replace("{{FRANCHISEE_ADMIN_NAME}}", "camino")
.Replace("{{FRANCHISEE_ADMIN_PASSWORD}}", pass)
.Replace("{{FRANCHISEE_ADMIN_EMAIL}}", "*****@*****.**");
var server = new Server(new ServerConnection(conn));
try
{
server.ConnectionContext.ExecuteNonQuery(script);
using (var scope = new TransactionScope())
{
var db = server.Databases[dbName];
var login = server.Logins[loginName];
if (login == null)
{
// Creating Logins
login = new Login(server, loginName)
{
LoginType = LoginType.SqlLogin, PasswordPolicyEnforced = false
};
login.Create(loginPassword);
login.DefaultDatabase = dbName;
login.Alter();
}
// Creating Users in the database for the logins created
if (db.Users[loginName] != null)
{
return;
}
var dbUser = new User(db, loginName)
{
UserType = UserType.SqlLogin,
Login = login.Name,
};
dbUser.Create();
dbUser.AddToRole("db_owner");
localJob.IsDone = true;
scope.Complete();
}
}
catch (Exception exception)
{
server = new Server(new ServerConnection(conn));
server.KillAllProcesses(dbName);
server.KillDatabase(dbName);
var db = server.Databases[dbName];
db.Drop();
localJob.IsDone = false;
localJob.FailCount = (localJob.FailCount ?? 0) + 1;
_diagnosticService.Error(exception);
}
listJobUpdated.Add(localJob);
}, mainCancellationTokenSource);
tasks.Add(t);
}
Task.WaitAll(tasks.ToArray(), mainCancellationTokenSource);
_deploymentJobService.UpdateListJobs(listJobUpdated);
}
catch (Exception ex)
{
_diagnosticService.Error(ex);
}
}
