public void Analyze_columns()
{
var repo = frb.ToRepoReports();
var ctxt = frb.ToFactory().Get();
var list = repo.GetAllQ();
foreach (var l in list)
{
l.columnsJson = SqlServerUtils.getUIColumnsJson(ctxt, l.sqlquery);
}
frb.ToFactory().Get().Commit();
}
public async Task BackupDatabase(string databaseName, int?timeout = default)
{
string filePath = BuildBackupPathWithFilename(databaseName);
var query = $"BACKUP DATABASE [{databaseName}] TO DISK='{filePath}'";
await SqlServerUtils.WithDatabaseCommand(_connectionString,
async command =>
{
command.CommandTimeout = timeout ?? DefaultCommandTimeout;
await command.ExecuteNonQueryAsync();
},
query);
Console.WriteLine($"The database {databaseName} is backed up to the file {filePath}");
}
public void getDynamicQuery_test_all_metadata()
{
// arrange
var context = frb.ToFactory().Get();
var reports = frb.ToRepoReports().GetAllQ();
foreach (var r in reports)
{
var result = SqlServerUtils.getMetadata(context, r.sqlquery);
Assert.IsTrue(result.Count > 2);
}
// act
// assert
}
public static async Task RestoreDatabase(string connectionString,
string bakFilePath,
string dbName,
int?timeout = default)
{
await SqlServerUtils.WithDatabaseConnection(connectionString,
async connection =>
{
var query = $@"RESTORE FILELISTONLY
FROM DISK='{bakFilePath}'";
using var command = new SqlCommand(query, connection);
using var reader = await command.ExecuteReaderAsync();
var columns = Enumerable.Range(0, reader.FieldCount)
.Select(reader.GetName)
.ToList();
var logicalNameColumnIndex = columns.IndexOf(LogicalNameColumn);
var logicalNames = new List <string>();
while (reader.Read())
{
logicalNames.Add(reader.GetString(logicalNameColumnIndex));
}
reader.Close();
var logicalDbName = logicalNames[0];
var logicalLogName = logicalNames[1];
query = "SELECT [name], [physical_name] FROM sys.master_files";
using var command2 = new SqlCommand(query, connection);
using var reader2 = await command2.ExecuteReaderAsync();
// Move to first row and then retrieve the physical_name column
reader2.Read();
var physicalFilePath = reader2.GetString(1);
reader2.Close();
var dataDir = new FileInfo(physicalFilePath).Directory.FullName;
query = $@"RESTORE DATABASE {dbName}
FROM DISK='{bakFilePath}'
WITH MOVE '{logicalDbName}' TO '{Path.Combine(dataDir, dbName + ".mdf")}',
MOVE '{logicalLogName}' TO '{Path.Combine(dataDir, dbName + ".ldf")}'
";
using var command3 = new SqlCommand(query, connection)
{
CommandTimeout = timeout ?? DefaultCommandTimeout
};
await command3.ExecuteNonQueryAsync();
});
}
public async Task <IEnumerable <ItemIdEntity> > GetItemIdData(string prefix, IReadOnlyCollection <Guid> ids)
{
Condition.Requires(prefix, nameof(prefix)).IsNotNullOrEmpty();
Condition.Requires(ids, nameof(ids)).IsNotNull();
var result = await _connection.ExecuteAsync(async conn =>
await conn.QueryAsync <ItemIdEntity>(
Schema.Queries.IdTableByIdAndPrefixQuery,
new
{
Prefix = prefix,
Ids = SqlServerUtils.BuildIdTable(ids)
},
null,
_connection.CommandTimeout,
CommandType.Text).ConfigureAwait(false)).ConfigureAwait(false);
return(result);
}
public List <dynamic> getDynamicQuery(int id, DTO.SearchOptions o)
{
var rdef = dbset.Single(a => a.ID == id);
var meta = SqlServerUtils.getMetadata(DataContext, rdef.sqlquery);
var queryType = buildQueryType(meta);
Task <List <object> > raw = dbFactory.Get().Database.SqlQuery(
queryType,
rdef.sqlquery,
new SqlParameter {
ParameterName = "beginDate", Value = o.beginDate
},
new SqlParameter {
ParameterName = "endDate", Value = o.endDate
},
new SqlParameter {
ParameterName = "dwccardnum", Value = o.dwccardnum
}).ToListAsync();
// TODO catch exception and handle here
raw.Wait();
var results = raw.Result;
return(results);
}
/// <summary>
/// Sort data
/// 数据排序
/// </summary>
/// <param name="sortData">Data to sort</param>
/// <returns>Rows affected</returns>
public virtual async Task <int> SortAsync(Dictionary <T, short> sortData)
{
var parameters = new DynamicParameters();
parameters.Add("Items", App.DB.DictionaryToParameter(sortData, null, null, (keyType, valueType) => SqlServerUtils.GetDicCommand(keyType, valueType, App.BuildCommandName)));
AddSystemParameters(parameters);
var command = CreateCommand(GetCommandName("sort"), parameters);
return(await ExecuteAsync(command));
}
