private IEnumerable<string> DeployRun(string action, ComponentRunContext runContext) { var sb = new StringBuilder(); var dbName = SqlUtils.ToBracketedIdentifier(mDatabaseInfo.Name); var sqlParams = new SqlParamCollection(); sb.AppendFormat( "USE [master]\n" + "IF db_id(@dbName) IS NOT NULL\n" + " ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\n" + "RESTORE DATABASE {0} FROM DISK = @backupFile WITH FILE = 1, NOUNLOAD, STATS = 5", dbName); sqlParams.Add("@dbName", SqlDbType.NVarChar).Value = mDatabaseInfo.Name; sqlParams.Add("@backupFile", SqlDbType.NVarChar).Value = mDatabaseInfo.BackupFilePath; if (mDatabaseInfo.Relocate) { var names = SqlUtils.GetLogicalAndPhysicalNamesFromBackupFile(mDatabaseInfo.Connection, mDatabaseInfo.BackupFilePath); var i = 0; foreach (var tuple in names) { var logical = string.Format("@l{0}", i); var physical = string.Format("@p{0}", i); sb.AppendFormat(",\n MOVE {0} TO {1}", logical, physical); var destPath = GetRelocation(tuple.Item2); sqlParams.Add(logical, SqlDbType.NVarChar).Value = tuple.Item1; sqlParams.Add(physical, SqlDbType.NVarChar).Value = destPath; ++i; } } sb.AppendFormat( "\nALTER DATABASE {0} SET MULTI_USER\n", dbName); yield return string.Format("Restoring {0}", mDatabaseInfo.Name); if (!runContext.DryRun) { using (var process = SqlUtils.Exec(mDatabaseInfo.Connection, sb.ToString(), sqlParams)) { foreach (var processOutputLine in process.GetOutput()) { yield return processOutputLine.Line; } } } yield return "Database restore completed."; }
public static IProcessExecutionResult BackupDatabase(DatabaseConnectionConfig databaseConnection, string dbName, string backupFilePath, bool compress) { var sb = new StringBuilder(); sb.AppendFormat("BACKUP DATABASE {0} TO DISK = @backupFile WITH FORMAT, INIT, NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10", ToBracketedIdentifier(dbName)); var parameters = new SqlParamCollection(); parameters.Add("@backupFile", SqlDbType.NVarChar).Value = backupFilePath; parameters.Add("@name", SqlDbType.NVarChar).Value = dbName; if (compress) sb.Append(", COMPRESSION"); return new SqlCommandExecutionResult(databaseConnection, dbName, sb.ToString(), parameters); }
/// <summary> /// Fixed: /// </summary> private static DataSet ExecuteDataSet( Context context, string name, Dictionary <string, object> _params) { var extendedSql = Parameters.ExtendedSqls ?.Where(o => o.Api) .Where(o => o.Name == name) .ExtensionWhere <ParameterAccessor.Parts.ExtendedSql>(context: context) .FirstOrDefault(); if (extendedSql == null) { return(null); } var param = new SqlParamCollection(); _params?.ForEach(part => param.Add( variableName: part.Key, value: part.Value)); var dataSet = Repository.ExecuteDataSet( context: context, statements: new SqlStatement( commandText: extendedSql.CommandText .Replace("{{SiteId}}", context.SiteId.ToString()) .Replace("{{Id}}", context.Id.ToString()), param: param)); return(dataSet); }
/// <summary> /// Fixed: /// </summary> public static System.Web.Mvc.ContentResult Sql(Context context) { var extendedApi = context.RequestDataString.Deserialize <ExtendedApi>(); if (extendedApi == null) { return(ApiResults.BadRequest(context: context)); } var extendedSql = ExtensionWhere <ParameterAccessor.Parts.ExtendedSql>( context: context, extensions: Parameters.ExtendedSqls ?.Where(o => o.Api) .Where(o => o.Name == extendedApi.Name)) .FirstOrDefault(); if (extendedSql == null) { return(ApiResults.BadRequest(context: context)); } var param = new SqlParamCollection(); extendedApi.Params?.ForEach(part => param.Add( variableName: part.Key, value: part.Value)); var dataSet = Rds.ExecuteDataSet( context: context, statements: new SqlStatement( commandText: extendedSql.CommandText, param: param)); var data = new Dictionary <string, List <Dictionary <string, object> > >(); foreach (DataTable dataTable in dataSet.Tables) { var table = new List <Dictionary <string, object> >(); foreach (DataRow dataRow in dataTable.Rows) { var row = new Dictionary <string, object>(); foreach (DataColumn dataColumn in dataTable.Columns) { row.AddIfNotConainsKey( dataColumn.ColumnName, dataRow[dataColumn.ColumnName]); } table.Add(row); } data.AddIfNotConainsKey(dataTable.TableName, table); } return(ApiResults.Get( statusCode: 200, limitPerDate: 0, limitRemaining: 0, response: new { Data = data })); }
public static void SqlAddParam(SqlParamCollection pCols, string parameterName, System.Data.SqlDbType dbType, int size, System.Data.ParameterDirection direction, dynamic defaultVal, dynamic elementValue) { pCols.Add(parameterName, dbType, size, direction, elementValue == null ? defaultVal : elementValue); }
private void RestoreDatabase(DatabaseBackupInfo db, bool dryRun, ILog log) { var sb = new StringBuilder(); var dbName = SqlUtils.ToBracketedIdentifier(db.Name); var sqlParams = new SqlParamCollection(); sb.AppendFormat( "USE [master]\n" + "IF db_id(@dbName) IS NOT NULL\n" + " ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\n" + "RESTORE DATABASE {0} FROM DISK = @backupFile WITH FILE = 1, NOUNLOAD, STATS = 5", dbName); sqlParams.Add("@dbName", SqlDbType.NVarChar).Value = db.Name; sqlParams.Add("@backupFile", SqlDbType.NVarChar).Value = db.BackupFilePath; if (mDatabaseConnection.Relocate) { var names = SqlUtils.GetLogicalAndPhysicalNamesFromBackupFile(mDatabaseConnection, db.BackupFilePath); var i = 0; foreach (var tuple in names) { var logical = string.Format("@l{0}", i); var physical = string.Format("@p{0}", i); sb.AppendFormat(",\n MOVE {0} TO {1}", logical, physical); var destPath = GetRelocation(db.Name, mDatabaseConnection.RelocatePath, tuple.Item2); sqlParams.Add(logical, SqlDbType.NVarChar).Value = tuple.Item1; sqlParams.Add(physical, SqlDbType.NVarChar).Value = destPath; ++i; } } sb.AppendFormat( "\nALTER DATABASE {0} SET MULTI_USER\n", dbName); log.LogFormat("Restoring {0} from {1}", db.Name, db.BackupFilePath); if (!dryRun) { using (var process = SqlUtils.Exec(mDatabaseConnection, db.Name, sb.ToString(), sqlParams)) using (log.IndentScope()) { foreach (var processOutputLine in process.GetOutput()) { log.Log(processOutputLine.Line); } } } log.Log("Database restore completed."); }