public bool ExecuteReadWriteCheck(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 = SqlConnectionFactory.CreateAndOpen(_builder))
using (var command = new SqlCommand(
$"CREATE TABLE {validationTableName}(TestID int); DROP TABLE {validationTableName};",
connection))
{
command.ExecuteNonQuery();
return(true);
}
}
catch
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
}
return(false);
}
public string GetProperty(string propertyName)
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Server_Property_Get, connection))
{
command.Parameters.AddWithValue("@Property", propertyName);
var reader = command.ExecuteReader();
return(reader.Read() ? reader[0].ToString() : null);
}
}
public bool Exists()
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_ID, connection))
{
command.Parameters.AddWithValue("@Database", _builder.InitialCatalog);
var reader = command.ExecuteReader();
return(reader.Read() && !reader.IsDBNull(0));
}
}
public void Drop(bool closeConnections = false)
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_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);
command.ExecuteNonQuery();
}
}
public string[] GetSchemas()
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_builder))
using (var command = new SqlCommand(Commands.Database_Schemas_Get_All, connection))
{
var results = new List <string>();
var reader = command.ExecuteReader();
while (reader.Read())
{
results.Add(reader[0].ToString());
}
return(results.OrderBy(s => s).ToArray());
}
}
public string[] GetAll()
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_Get_All, connection))
{
var result = new List <string>();
var reader = command.ExecuteReader();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
return(result.OrderBy(s => s).ToArray());
}
}
public void Create(AzureDatabaseTierDetails azureDetails = null)
{
using (var connection = SqlConnectionFactory.CreateAndOpen(_builder.CloneForMaster().ConnectionString))
using (var command = new SqlCommand(Commands.Database_Create, connection))
{
if (azureDetails != null && _server.IsAzure())
{
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);
command.ExecuteNonQuery();
}
}
