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