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); } }