public IList <IncidentModel> GetAmbulanceIncidents(int cityId)
{
string connectionString = ShardManagmentConfig.GetCredentialsConnectionString();
Shard shard = _shardMap.Value.GetMappingForKey((int)DepartmentType.Ambulance).Shard;
return(QueryHelper.ExecuteMultiShardQuery(connectionString, GetIncidentsQuery(cityId), shard));
}
public IList <IncidentModel> GetFireIncidents()
{
string connectionString = ShardManagmentConfig.GetCredentialsConnectionString();
Shard shard = _shardMap.Value.GetMappingForKey((int)DepartmentType.Fire).Shard;
return(QueryHelper.ExecuteMultiShardQuery(connectionString, IncidentQuery, shard));
}
/// <summary>
/// Tries to get the ShardMapManager that is stored in the specified database.
/// </summary>
public static ShardMapManager TryGetShardMapManager(string shardMapManagerServerName, string shardMapManagerDatabaseName)
{
string shardMapManagerConnectionString =
ShardManagmentConfig.GetConnectionString(
ShardManagmentConfig.ShardMapManagerServerName,
ShardManagmentConfig.ShardMapDatabase);
if (!SqlDatabaseUtils.DatabaseExists(shardMapManagerServerName, shardMapManagerDatabaseName))
{
// Shard Map Manager database has not yet been created
return(null);
}
ShardMapManager shardMapManager;
bool smmExists = ShardMapManagerFactory.TryGetSqlShardMapManager(
shardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy,
out shardMapManager);
if (!smmExists)
{
// Shard Map Manager database exists, but Shard Map Manager has not been created
return(null);
}
return(shardMapManager);
}
public static void DropDatabase(string server, string db)
{
//ConsoleUtils.WriteInfo("Dropping database {0}", db);
using (ReliableSqlConnection conn = new ReliableSqlConnection(
ShardManagmentConfig.GetConnectionString(server, MasterDatabaseName),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Determine if we are connecting to Azure SQL DB
cmd.CommandText = "SELECT SERVERPROPERTY('EngineEdition')";
cmd.CommandTimeout = 60;
int engineEdition = conn.ExecuteCommand <int>(cmd);
// Drop the database
if (engineEdition == 5)
{
// Azure SQL DB
cmd.CommandText = string.Format("DROP DATABASE {0}", BracketEscapeName(db));
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = string.Format(
@"ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE {0}",
BracketEscapeName(db));
cmd.ExecuteNonQuery();
}
}
}
public IList <IncidentModel> GetAllIncidents()
{
string connectionString = ShardManagmentConfig.GetCredentialsConnectionString();
Shard[] shards = _shardMap.Value.GetShards().ToArray();
return(QueryHelper.ExecuteMultiShardQuery(connectionString, IncidentQuery, shards));
}
public IEnumerable <IncidentModel> GetIncidents(int cityId)
{
string connectionString = ShardManagmentConfig.GetCredentialsConnectionString();
Shard[] shards = _shardMap.Value.GetShards().ToArray();
var query = GetIncidentsQuery(cityId);
var incidents = QueryHelper.ExecuteMultiShardQuery(connectionString, query, shards);
return(incidents);
}
public IncidentModel GetIncident(int city, int incidentId)
{
string connectionString = ShardManagmentConfig.GetCredentialsConnectionString();
Shard[] shards = _shardMap.Value.GetShards().ToArray();
var query =
IncidentQuery
+ $@"WHERE i.Id = {incidentId} AND i.CityId = {city};";
var incidents = QueryHelper.ExecuteMultiShardQuery(connectionString, query, shards);
var incident = incidents.FirstOrDefault(i => i.CityId == city && i.Id == incidentId);
return(incident);
}
public static void CreateDatabase(string server, string db)
{
using (ReliableSqlConnection conn = new ReliableSqlConnection(
ShardManagmentConfig.GetConnectionString(server, MasterDatabaseName),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Determine if we are connecting to Azure SQL DB
cmd.CommandText = "SELECT SERVERPROPERTY('EngineEdition')";
cmd.CommandTimeout = 60;
int engineEdition = conn.ExecuteCommand <int>(cmd);
if (engineEdition == 5)
{
// Azure SQL DB
SqlRetryPolicy.ExecuteAction(() =>
{
if (!DatabaseExists(server, db))
{
// Begin creation (which is async for Standard/Premium editions)
cmd.CommandText = string.Format(
"CREATE DATABASE {0} (EDITION = '{1}')",
BracketEscapeName(db),
ShardManagmentConfig.DatabaseEdition);
cmd.CommandTimeout = 60;
cmd.ExecuteNonQuery();
}
});
// Wait for the operation to complete
while (!DatabaseIsOnline(server, db))
{
//ConsoleUtils.WriteInfo("Waiting for database {0} to come online...", db);
Thread.Sleep(TimeSpan.FromSeconds(5));
}
}
else
{
// Other edition of SQL DB
cmd.CommandText = string.Format("CREATE DATABASE {0}", BracketEscapeName(db));
conn.ExecuteCommand(cmd);
}
}
}
/// <summary>
/// Returns true if we can connect to the database.
/// </summary>
public static bool TryConnectToSqlDatabase()
{
string connectionString =
ShardManagmentConfig.GetConnectionString(
ShardManagmentConfig.ShardMapManagerServerName,
MasterDatabaseName);
using (ReliableSqlConnection conn = new ReliableSqlConnection(
connectionString,
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
}
return(true);
}
public static bool DatabaseIsOnline(string server, string db)
{
using (ReliableSqlConnection conn = new ReliableSqlConnection(
ShardManagmentConfig.GetConnectionString(server, MasterDatabaseName),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(*) from sys.databases where name = @dbname and state = 0"; // online
cmd.Parameters.AddWithValue("@dbname", db);
cmd.CommandTimeout = 60;
int count = conn.ExecuteCommand <int>(cmd);
bool exists = count > 0;
return(exists);
}
}
public static void ExecuteSqlScript(string server, string db, string schemaFile)
{
using (ReliableSqlConnection conn = new ReliableSqlConnection(
ShardManagmentConfig.GetConnectionString(server, db),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Read the commands from the sql script file
IEnumerable <string> commands = ReadSqlScript(schemaFile);
foreach (string command in commands)
{
cmd.CommandText = command;
cmd.CommandTimeout = 60;
conn.ExecuteCommand(cmd);
}
}
}
