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