public async Task <bool> ExecuteReadWriteCheckAsync(TimeSpan?timeout = null)
{
var waitTime = timeout ?? TimeSpan.FromMinutes(10);
var waitStartTime = DateTime.Now;
while (DateTime.Now.Subtract(waitStartTime).TotalSeconds < waitTime.TotalSeconds)
{
try
{
var validationTableName = $"ReadWriteCheck{DateTime.Now.Ticks}";
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder))
using (var command = new SqlCommand(
$"CREATE TABLE {validationTableName}(TestID int); DROP TABLE {validationTableName};",
connection))
{
await command.ExecuteNonQueryAsync();
return(true);
}
}
catch
{
await Task.Delay(TimeSpan.FromSeconds(1));
}
}
return(false);
}
public async Task <string> GetPropertyAsync(string propertyName)
{
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Server_Property_Get, connection))
{
command.Parameters.AddWithValue("@Property", propertyName);
var reader = await command.ExecuteReaderAsync();
return(await reader.ReadAsync() ? reader[0].ToString() : null);
}
}
public async Task DropAsync(bool closeConnections = false)
{
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder.CloneForMaster().ConnectionString))
using (var command =
new SqlCommand(closeConnections ? Commands.Database_Drop_CloseConnections : Commands.Database_Drop,
connection))
{
command.CommandText = command.CommandText.Replace("@Database", _builder.InitialCatalog);
await command.ExecuteNonQueryAsync();
}
}
public async Task <bool> ExistsAsync()
{
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_ID, connection))
{
command.Parameters.AddWithValue("@Database", _builder.InitialCatalog);
var reader = await command.ExecuteReaderAsync();
return(await reader.ReadAsync() && !await reader.IsDBNullAsync(0));
}
}
public async Task <string[]> GetAllAsync()
{
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_Get_All, connection))
{
var result = new List <string>();
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
result.Add(reader.GetString(0));
}
return(result.OrderBy(s => s).ToArray());
}
}
public async Task CreateAsync(AzureDatabaseTierDetails azureDetails = null)
{
using (var connection = await SqlConnectionFactory.CreateAndOpenAsync(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_Create, connection))
{
if (azureDetails != null && await _server.IsAzureAsync())
{
if (!string.IsNullOrWhiteSpace(azureDetails.ElasticPool))
{
command.CommandText = Commands.Database_Create_Azure_Pool
.Replace("@ElasticPool", azureDetails.ElasticPool);
}
else
{
command.CommandText = Commands.Database_Create_Azure_Single
.Replace("@Edition", azureDetails.Edition)
.Replace("@ServiceObjective", azureDetails.ServiceObjective);
}
}
command.CommandText = command.CommandText.Replace("@Database", _builder.InitialCatalog);
await command.ExecuteNonQueryAsync();
}
}
