public static void DropDatabase(string server, string db) { using (ReliableSqlConnection conn = new ReliableSqlConnection( Configuration.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(); } } }
private RestoreDatabaseTaskDataObject CreateRestoreForNewSession(string ownerUri, string targetDatabaseName = null) { ConnectionInfo connInfo; DisasterRecoveryService.ConnectionServiceInstance.TryFindConnection( ownerUri, out connInfo); if (connInfo != null) { SqlConnection connection; DbConnection dbConnection = connInfo.AllConnections.First(); ReliableSqlConnection reliableSqlConnection = dbConnection as ReliableSqlConnection; SqlConnection sqlConnection = dbConnection as SqlConnection; if (reliableSqlConnection != null) { connection = reliableSqlConnection.GetUnderlyingConnection(); } else if (sqlConnection != null) { connection = sqlConnection; } else { Logger.Write(LogLevel.Warning, "Cannot find any sql connection for restore operation"); return(null); } Server server = new Server(new ServerConnection(connection)); RestoreDatabaseTaskDataObject restoreDataObject = new RestoreDatabaseTaskDataObject(server, targetDatabaseName); return(restoreDataObject); } return(null); }
/// <summary> /// Executes this batch and captures any server messages that are returned. /// </summary> /// <param name="conn">The connection to use to execute the batch</param> /// <param name="cancellationToken">Token for cancelling the execution</param> public async Task Execute(DbConnection conn, CancellationToken cancellationToken) { // Sanity check to make sure we haven't already run this batch if (HasExecuted) { throw new InvalidOperationException("Batch has already executed."); } // Notify that we've started execution if (BatchStart != null) { await BatchStart(this); } // Register the message listener to *this instance* of the batch // Note: This is being done to associate messages with batches ReliableSqlConnection sqlConn = conn as ReliableSqlConnection; if (sqlConn != null) { sqlConn.GetUnderlyingConnection().InfoMessage += ServerMessageHandler; } try { await DoExecute(conn, cancellationToken); } catch (TaskCanceledException) { // Cancellation isn't considered an error condition await SendMessage(SR.QueryServiceQueryCancelled, false); throw; } catch (Exception e) { HasError = true; await SendMessage(SR.QueryServiceQueryFailed(e.Message), true); throw; } finally { // Remove the message event handler from the connection if (sqlConn != null) { sqlConn.GetUnderlyingConnection().InfoMessage -= ServerMessageHandler; } // Mark that we have executed HasExecuted = true; executionEndTime = DateTime.Now; // Fire an event to signify that the batch has completed if (BatchCompletion != null) { await BatchCompletion(this); } } }
/// <summary> /// Returns true if we can connect to the database. /// </summary> public static bool TryConnectToSqlDatabase() { string connectionString = Configuration.GetConnectionString( Configuration.ShardMapManagerServerName, MasterDatabaseName); try { using (ReliableSqlConnection conn = new ReliableSqlConnection( connectionString, SqlRetryPolicy, SqlRetryPolicy)) { conn.Open(); } return(true); } catch (SqlException e) { //ConsoleUtils.WriteWarning("Failed to connect to SQL database with connection string:"); // Console.WriteLine("\n{0}\n", connectionString); // ConsoleUtils.WriteWarning("If this connection string is incorrect, please update the Sql Database settings in App.Config.\n\nException message: {0}", e.Message); return(false); } }
public static void ExecuteNonQueryStatement(string statement, ReliableSqlConnection sqlConnection) { if (statement != "") { //SqlCommand sqlCommandGenerateTables = new SqlCommand(statement, sqlConnection); SqlCommand sqlCommandGenerateTables = sqlConnection.CreateCommand(); sqlCommandGenerateTables.CommandText = statement; try { sqlCommandGenerateTables.Connection.OpenWithRetry(); sqlCommandGenerateTables.ExecuteNonQueryWithRetry(); sqlCommandGenerateTables.Connection.Close(); } catch (Exception e) { Console.WriteLine("ExecuteNonQueryException: '" + e.Message + "'"); Console.WriteLine("ADO.NET Connection Pooling may require a retry."); Console.WriteLine("Trying again...."); //Try again (ADO.NET Connection Pooling may require a retry) sqlCommandGenerateTables.Connection.Close(); sqlCommandGenerateTables.Connection.OpenWithRetry(); sqlCommandGenerateTables.ExecuteNonQueryWithRetry(); sqlCommandGenerateTables.Connection.Close(); } } }
private DbCommand CreateCommand(DbConnection conn) { // Register the message listener to *this instance* of the batch // Note: This is being done to associate messages with batches ReliableSqlConnection sqlConn = conn as ReliableSqlConnection; DbCommand dbCommand; if (sqlConn != null) { dbCommand = sqlConn.GetUnderlyingConnection().CreateCommand(); // Add a handler for when the command completes SqlCommand sqlCommand = (SqlCommand)dbCommand; sqlCommand.StatementCompleted += StatementCompletedHandler; } else { dbCommand = conn.CreateCommand(); } // Make sure we aren't using a ReliableCommad since we do not want automatic retry Debug.Assert(!(dbCommand is ReliableSqlConnection.ReliableSqlCommand), "ReliableSqlCommand command should not be used to execute queries"); return(dbCommand); }
private static void MergeShardletAndDropTempTables(ShardConnection destinationConnection, string uniqueProcessString) { using (var destinationSqlConnection = new ReliableSqlConnection(destinationConnection.ConnectionString)) { // sync the header, detail and map data, drop the temp tables destinationSqlConnection.Open(); // todo: need to check rows affected and return error if none (same on all updates in this method) MergeSalesOrderHeaders(uniqueProcessString, destinationSqlConnection); MergeSalesOrderDetails(uniqueProcessString, destinationSqlConnection); MergeShoppingCartItems(uniqueProcessString, destinationSqlConnection); // drop the temporary table data var dropTempTablesCommand = new SqlCommand("[Shardlets].[DropShardletSalesOrder]", destinationSqlConnection.Current) { CommandType = CommandType.StoredProcedure }; dropTempTablesCommand.Parameters.Add(CreateUniqueProcessIDParameter(uniqueProcessString)); dropTempTablesCommand.ExecuteNonQuery(); } }
public OperationStatus GetOperationStatus() { using (var cnn = new ReliableSqlConnection(this.Parent.ConnectionString)) { cnn.Open(); cnn.UseFederationRoot(); using (var cmd = cnn.CreateCommand()) { cmd.CommandText = OPERATION_STATUS_QUERY; using (var rdr = cmd.ExecuteReaderWithRetry()) { if (rdr.Read()) { return(new OperationStatus() { OperationId = rdr.GetGuid(0), OperationType = rdr.GetString(1), FederationId = rdr.GetInt32(2), FederationName = rdr.GetString(3), PercentComplete = rdr.GetFloat(6) }); } else { return(null); } } } } }
private static DataAccessResponse CreateDatabaseOnMaster(SqlSettings sqlSettings, string databaseName, RetryPolicy retryPolicy) { //Connect to 'master' ReliableSqlConnection sqlConnectionMaster = new ReliableSqlConnection( Helpers.SqlConnectionStrings.GenerateConnectionString(sqlSettings, "master"), retryPolicy); //Create Database: var sqlStatement = new StringBuilder(); sqlStatement.Append("IF NOT EXISTS "); sqlStatement.Append("(SELECT 1 FROM sys.databases WHERE name = N'"); sqlStatement.Append(databaseName); sqlStatement.Append("') "); sqlStatement.Append("BEGIN "); sqlStatement.Append("CREATE DATABASE "); sqlStatement.Append(databaseName); sqlStatement.Append(" END"); SqlCommand sqlCommand = sqlConnectionMaster.CreateCommand(); sqlCommand.CommandText = sqlStatement.ToString(); sqlCommand.Connection.OpenWithRetry(); int result = sqlCommand.ExecuteNonQueryWithRetry(); // returns Int indicating number of rows affected sqlCommand.Connection.Close(); return(new DataAccessResponse { isSuccess = true }); }
/// <summary> /// Removes the specified queue item from the persistence queue. /// </summary> /// <param name="queueItemId">The unique identity of the queue item.</param> /// <returns>A flag indicating whether or not the queue item has been actually deleted.</returns> public bool Remove(Guid queueItemId) { var callToken = TraceManager.DataAccessComponent.TraceIn(queueItemId.ToString()); try { using (var dbConnection = new ReliableSqlConnection(this.dbConnectionString, this.connectionRetryPolicy, this.commandRetryPolicy)) using (var removeCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateRemoveCommand(dbConnection, queueItemId)) { TraceManager.DataAccessComponent.TraceCommand(removeCommand); int recordsAffected = dbConnection.ExecuteCommand <int>(removeCommand); return(recordsAffected > 0); } } catch (Exception ex) { TraceManager.DataAccessComponent.TraceError(ex, callToken); return(false); } finally { TraceManager.DataAccessComponent.TraceOut(callToken); } }
public void ExecutesNonQuerySqlCommandWithConnectionRetryPolicyAndSqlCommandRetryPolicy() { RetryManager.SetDefault(this.retryPolicySettings.BuildRetryManager(), false); var connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString; var reliableConnection = new ReliableSqlConnection(connectionString); int count = 0; try { var retryPolicy = this.retryManager.GetRetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>("Retry 5 times"); retryPolicy.ExecuteAction(() => { SqlCommand command = new SqlCommand("SELECT 1", reliableConnection.Current); count = (int)command.ExecuteNonQueryWithRetry(retryPolicy, retryPolicy); }); Assert.AreEqual(-1, count); } catch (SqlException) { } catch (Exception) { Assert.Fail(); } }
public void RetriesToExecuteActionWhenSqlExceptionDuringCommandExecution() { RetryManager.SetDefault(this.retryPolicySettings.BuildRetryManager(), false); var connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString; var reliableConnection = new ReliableSqlConnection(connectionString); int count = 0; try { var retryPolicy = this.retryManager.GetRetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>("Retry 5 times"); retryPolicy.ExecuteAction(() => { SqlCommand command = new SqlCommand("FAIL"); count = reliableConnection.ExecuteCommand(command); }); Assert.AreEqual(-1, count); } catch (SqlException) { Assert.AreEqual <ConnectionState>(ConnectionState.Closed, reliableConnection.Current.State); } catch (Exception) { Assert.Fail(); } }
public void ExecutesXmlReaderWithRetryPolicy() { RetryManager.SetDefault(this.retryPolicySettings.BuildRetryManager(), false); var connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString; var reliableConnection = new ReliableSqlConnection(connectionString); XmlReader reader; int count = 0; try { var retryPolicy = this.retryManager.GetRetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>("Retry 5 times"); retryPolicy.ExecuteAction(() => { SqlCommand command = new SqlCommand("SELECT 1 FOR XML AUTO", reliableConnection.Current); reader = command.ExecuteXmlReaderWithRetry(retryPolicy); while (reader.Read()) { reader.MoveToFirstAttribute(); reader.ReadAttributeValue(); count = reader.ReadContentAsInt(); } }); Assert.AreEqual(1, count); } catch (SqlException) { } catch (Exception) { Assert.Fail(); } }
/// <summary> /// Returns the databse connectionstring of the databasename passed in: /// </summary> /// <param name="AccountDatabasePartitionName"></param> /// <returns></returns> public ReliableSqlConnection DatabasePartitionSqlConnection(string databasePartitionName) { //_accountDatabasePartitionName = AccountDatabasePartitionName; ReliableSqlConnection sqlConnection = new ReliableSqlConnection(_generateConnectionString(databasePartitionName), retryPolicy); return(sqlConnection); }
private ReliableSqlConnection GetConnection(bool ReadOny) { String primary = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; String roSecondary = ConfigurationManager.ConnectionStrings["ROSecondaryConnection"].ConnectionString; String conString = String.Empty; if (ReadOny == true) { conString = roSecondary; Random r = new Random(); int random = r.Next(); if (random % 2 == 0) { conString = primary; } else { conString = roSecondary; } } else { conString = primary; } ReliableSqlConnection sqlConnection = new ReliableSqlConnection(conString); return(sqlConnection); }
public DataSet ExecuteDataSet() { var dataSet = new DataSet(); SetCommand(); Try(action: () => { switch (SqlContainer.RdsProvider) { case "Azure": var retryPolicy = Azures.RetryPolicy(); retryPolicy.ExecuteAction(() => { using (var con = new ReliableSqlConnection( SqlCommand.Connection.ConnectionString)) { con.Open(retryPolicy); SqlCommand.Connection = con.Current; SqlContainer.SqlDataAdapter(SqlCommand).Fill(dataSet); } }); break; default: SqlContainer.SqlDataAdapter(SqlCommand).Fill(dataSet); break; } }); Clear(); return(dataSet); }
/// <summary> /// Returns true if we can connect to the database. /// </summary> public static bool TryConnectToSqlDatabase() { string connectionString = Configuration.GetConnectionString( Configuration.ShardMapManagerServerName, MasterDatabaseName); try { using (ReliableSqlConnection conn = new ReliableSqlConnection( connectionString, SqlRetryPolicy, SqlRetryPolicy)) { conn.Open(); } return true; } catch (SqlException e) { ConsoleUtils.WriteWarning("Failed to connect to SQL database with connection string:"); Console.WriteLine("\n{0}\n", connectionString); ConsoleUtils.WriteWarning("If this connection string is incorrect, please update the Sql Database settings in App.Config.\n\nException message: {0}", e.Message); return false; } }
// private SqlConnection GetConnection() // { // String conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; // SqlConnection sqlConnection = new SqlConnection(conString); // return sqlConnection; // } private ReliableSqlConnection GetConnection() { String conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; ReliableSqlConnection sqlConnection = new ReliableSqlConnection(conString); return(sqlConnection); }
private IDbCommand CreateCommand(string sqlScript, IDictionary <string, object> @params) { //SqlConnection connection = GetConnection(); //var command = new SqlCommand(); //command.Connection = connection; ReliableSqlConnection connection = GetConnection(); var command = SqlCommandFactory.CreateCommand(connection); command.CommandText = sqlScript; command.CommandType = CommandType.Text; if (@params != null) { foreach (var param in @params) { var dbParam = command.CreateParameter(); dbParam.ParameterName = param.Key; dbParam.Value = param.Value; command.Parameters.Add(dbParam); } } command.Connection.Open(); return(command); }
public void BeginTransaction(string connectionString, ref RetryPolicy <SqlDatabaseTransientErrorDetectionStrategy> retryPolicy) { Connection = new ReliableSqlConnection(connectionString, retryPolicy); Connection.Open(retryPolicy); Transaction = (SqlTransaction)Connection.BeginTransaction(TransactionIsolationLevel); }
private static void executeNonQueryStatement(string statement, ReliableSqlConnection sqlConnection) { if (statement != "") { //SqlCommand sqlCommandGenerateTables = new SqlCommand(statement, sqlConnection); SqlCommand sqlCommandGenerateTables = sqlConnection.CreateCommand(); sqlCommandGenerateTables.CommandText = statement; try { sqlCommandGenerateTables.Connection.OpenWithRetry(); sqlCommandGenerateTables.ExecuteNonQueryWithRetry(); sqlCommandGenerateTables.Connection.Close(); } catch { //Try again (ADO.NET Connection Pooling may require a retry) sqlCommandGenerateTables.Connection.Close(); sqlCommandGenerateTables.Connection.OpenWithRetry(); sqlCommandGenerateTables.ExecuteNonQueryWithRetry(); sqlCommandGenerateTables.Connection.Close(); } } }
[Ignore] // Unstable test public void TestServerNameSubstitutionWithIPAddress() { using (var conn = new ReliableSqlConnection(connectionString)) { Assert.AreEqual(connectionString, conn.Current.ConnectionString, "Connection string managed by ReliableSqlConnection class must not be modified at this point."); Assert.AreNotEqual <Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); } Thread.Sleep(1000); using (var conn = new ReliableSqlConnection(connectionString)) { Assert.AreNotEqual(connectionString, conn.Current.ConnectionString, "Connection string managed by ReliableSqlConnection class has not been modified."); Assert.AreNotEqual <Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); IPAddress hostAddress = null; var conStringBuilder = new SqlConnectionStringBuilder(conn.Current.ConnectionString); string hostName = conStringBuilder.DataSource.StartsWith("tcp:") ? conStringBuilder.DataSource.Remove(0, "tcp:".Length) : conStringBuilder.DataSource; Assert.IsTrue(IPAddress.TryParse(hostName, out hostAddress), "The data source doesn't seem to be represented by an IP address."); conn.Current.Close(); conn.Current.ConnectionString = conn.Current.ConnectionString.Replace("0", "1").Replace("2", "3").Replace("4", "5").Replace("6", "7"); conn.Open(); Assert.AreNotEqual <Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); } }
/// <summary> /// Adds test sales orders to database at a specific connection string. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="initialTestCustomerID">The initial test customer identifier.</param> /// <param name="numberOfTestCustomers">The number of test customers.</param> /// <param name="numberOfTestOrders">The number of test orders per customer.</param> public void AddTestSalesOrdersInDatabase(string connectionString, int initialTestCustomerID, int numberOfTestCustomers, int numberOfTestOrders) { // set up insert command for test data var headerInsertCommand = new SqlCommand("Sales.CreateSalesOrderHeader") { CommandType = CommandType.StoredProcedure }; var customerIDParameter = headerInsertCommand.Parameters.Add(new SqlParameter("CustomerID", SqlDbType.Int)); var salesOrderIDParam = headerInsertCommand.Parameters.Add(new SqlParameter("SalesOrderID", SqlDbType.Int)); AddTestSalesOrderParametersTo(headerInsertCommand); // set up detail insert commands for test data var detailInsertCommand = new SqlCommand("Sales.CreateSalesOrderLineItem") { CommandType = CommandType.StoredProcedure }; var detailSalesOrderIDParam = detailInsertCommand.Parameters.Add(new SqlParameter("SalesOrderID", SqlDbType.Int)); var salesOrderDetailLineNum = detailInsertCommand.Parameters.Add(new SqlParameter("SalesOrderLineNum", SqlDbType.Int)); AddTestSalesDetailParametersTo(detailInsertCommand); using (var connection = new ReliableSqlConnection(connectionString)) { connection.Open(); for (var uniqueCustomerID = initialTestCustomerID; uniqueCustomerID < initialTestCustomerID + numberOfTestCustomers; uniqueCustomerID++) { // get unique ids for sales orders var uniqueSalesOrderIDList = GetUniqueSalesOrderIDs(numberOfTestOrders); // create some test data foreach (var salesOrderID in uniqueSalesOrderIDList) { headerInsertCommand.Connection = connection.Current; customerIDParameter.Value = uniqueCustomerID; salesOrderIDParam.Value = salesOrderID; headerInsertCommand.ExecuteNonQuery(); detailInsertCommand.Connection = connection.Current; for (var i = 1; i < 5; i++) { detailSalesOrderIDParam.Value = salesOrderID; salesOrderDetailLineNum.Value = i; detailInsertCommand.ExecuteNonQuery(); } } } } }
private IEnumerable <int> GetUniqueSalesOrderIDs(int numberOfTestShardlets) { var uniqueIDList = new List <int>(numberOfTestShardlets); using (var masterConnection = new ReliableSqlConnection(_referenceDatabaseConnectionString)) { masterConnection.Open(); var command = new SqlCommand("[Sales].[GetCustomerSequenceIDSet]", masterConnection.Current) { CommandType = CommandType.StoredProcedure }; command.Parameters.Add(new SqlParameter("SetSize", SqlDbType.Int) { Value = numberOfTestShardlets }); var reader = command.ExecuteReader(); while (reader.Read()) { uniqueIDList.Add(reader.GetInt32(0)); } reader.Close(); } return(uniqueIDList); }
public Product GetProductById(int productId) { Product product; using (var reliableSqlConnection = new ReliableSqlConnection(connectionString, retryPolicy)) { reliableSqlConnection.Open(); var command = reliableSqlConnection.CreateCommand(); command.CommandText = "SELECT * FROM TartorOchKakor WHERE Id = @productId"; command.Parameters.AddWithValue("@productId", productId); var reader = command.ExecuteReaderWithRetry(retryPolicy); reader.Read(); product = new Product { Id = (int)reader["Id"], Name = (string)reader["Name"], Description = (string)reader["Description"], Price = (decimal)reader["Price"], ImageName = (string)reader["ImageName"] }; } return(product); }
public IEnumerable <Product> ReadAll() { List <Product> products; using (var reliableSqlConnection = new ReliableSqlConnection(connectionString, retryPolicy)) { reliableSqlConnection.Open(); var command = reliableSqlConnection.CreateCommand(); command.CommandText = "SELECT * FROM TartorOchKakor"; command.ExecuteReaderWithRetry(retryPolicy); var reader = command.ExecuteReader(); products = new List <Product>(); while (reader.Read()) { var product = new Product { Id = (int)reader["Id"], Name = (string)reader["Name"], Description = (string)reader["Description"], Price = (decimal)reader["Price"], ImageName = (string)reader["ImageName"] }; products.Add(product); } } return(products.AsReadOnly()); }
public void VerifyAnsiNullAndQuotedIdentifierSettingsReplayed() { using (ReliableSqlConnection conn = (ReliableSqlConnection)ReliableConnectionHelper.OpenConnection(CreateTestConnectionStringBuilder(), useRetry: true, azureAccountToken: null)) { VerifySessionSettings(conn, true); VerifySessionSettings(conn, false); } }
public void TestSuccessfulTransactionWithRetryableError() { int customerId = 0; int addressId = 0; int executeCount = 0; Action action = new Action(() => { using (ReliableSqlConnection connection = InitializeConnection()) { using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand()) { command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]"; customerId = (int)command.ExecuteScalarWithRetry(); } if (executeCount == 0) { executeCount++; throw new TimeoutException(); } using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand()) { command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]"; addressId = (int)command.ExecuteScalarWithRetry(); } using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand()) { command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)"; command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId; command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId; command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address"; command.ExecuteNonQueryWithRetry(); } } }); RetryPolicy retryPolicy = null;// RetryPolicyFactory.GetRetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>("FixedIntervalDefault"); using (TransactionRetryScope scope = new TransactionRetryScope(retryPolicy, action)) { try { scope.InvokeUnitOfWork(); scope.Complete(); } catch (Exception ex) { Trace.WriteLine(ex.Message); } } Assert.IsTrue(VerifyCustomerAddress(customerId, addressId)); DeleteCustomerAddress(customerId, addressId); }
/// <summary> /// Gets a default reliable connection with retry count of 3. /// </summary> /// <param name="connectionString">The connection string.</param> /// <returns>ReliableSqlConnection.</returns> protected ReliableSqlConnection GetReliableConnection(String connectionString) { RetryPolicy myRetryPolicy = new RetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>(3); var reliableConn = new ReliableSqlConnection(connectionString, myRetryPolicy); return(reliableConn); }
public void TestSingleCommandWithoutClosing() { ReliableSqlConnection connection = new ReliableSqlConnection(connectionString); SqlCommand command = new SqlCommand("SELECT 1"); connection.ExecuteCommand(command); connection.ExecuteCommand(command); }
public void TestFailedTransaction() { int expectedCount = RetrieveCountofTable(); int customerId = 0; int addressId = 0; Action action = new Action(() => { using (ReliableSqlConnection connection = InitializeConnection()) { using (SqlCommand command = (SqlCommand)connection.CreateCommand()) { command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]"; customerId = (int)command.ExecuteScalarWithRetry(); } using (SqlCommand command = (SqlCommand)connection.CreateCommand()) { command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]"; addressId = (int)command.ExecuteScalarWithRetry(); } using (SqlCommand command = (SqlCommand)connection.CreateCommand()) { command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)"; command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId; command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId; command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address"; command.ExecuteNonQueryWithRetry(); } using (SqlCommand command = (SqlCommand)connection.CreateCommand()) { command.CommandText = "RAISEERROR('ERROR', 16, 1)"; command.ExecuteNonQueryWithRetry(); } } }); using (TransactionRetryScope scope = new TransactionRetryScope(action)) { try { scope.InvokeUnitOfWork(); scope.Complete(); } catch (Exception ex) { Trace.WriteLine(ex.Message); } } int actualCount = RetrieveCountofTable(); Assert.AreEqual(expectedCount, actualCount, "Rollback failed"); }
public static void Initialize(TestContext context) { connectionString = ConfigurationManager.ConnectionStrings["SqlAWTest"].ConnectionString; connection = new ReliableSqlConnection(connectionString); connection.ConnectionRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format("[Connection Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); connection.CommandRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format("[Command Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); }
public void TestDoubleCommandsWithClosing() { ReliableSqlConnection connection = new ReliableSqlConnection(connectionString); SqlCommand command = new SqlCommand("SELECT 1"); SqlCommand command2 = new SqlCommand("SELECT 2"); connection.ExecuteCommand(command); connection.Close(); connection.ExecuteCommand(command2); }
public void Initialize() { connectionString = TestSqlSupport.SqlDatabaseConnectionString; RetryPolicyFactory.CreateDefault(); connection = new ReliableSqlConnection(connectionString); connection.ConnectionRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format("[Connection Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); connection.CommandRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format("[Command Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); }
protected override void Arrange() { this.command = new SqlCommand(TestSqlSupport.InvalidSqlText); this.connectionStrategy = new TestRetryStrategy(); this.commandStrategy = new TestRetryStrategy(); this.reliableConnection = new ReliableSqlConnection( TestSqlSupport.SqlDatabaseConnectionString, new RetryPolicy<AlwaysTransientErrorDetectionStrategy>(this.connectionStrategy), new RetryPolicy<AlwaysTransientErrorDetectionStrategy>(this.commandStrategy)); }
protected override void Arrange() { this.command = new SqlCommand(TestSqlSupport.ValidForXmlSqlQuery); this.connectionStrategy = new TestRetryStrategy(); this.commandStrategy = new TestRetryStrategy(); this.reliableConnection = new ReliableSqlConnection( TestSqlSupport.SqlAWTestConnectionString, new RetryPolicy<AlwaysTransientErrorDetectionStrategy>(this.connectionStrategy), new RetryPolicy<AlwaysTransientErrorDetectionStrategy>(this.commandStrategy)); }
/// <summary> /// 建立 db /// </summary> /// <param name="server"></param> /// <param name="db"></param> public static void CreateDatabase(string server, string db) { ConsoleUtils.WriteInfo("Creating database {0}", db); // Retry 機制 ,並取得 Connection String using (ReliableSqlConnection conn = new ReliableSqlConnection( Configuration.GetConnectionString(server, MasterDatabaseName), SqlRetryPolicy, SqlRetryPolicy)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); // 確定我們是否連接到SQL Azure的數據庫 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)) { // 開始建立 async for Standard/Premium cmd.CommandText = string.Format( "CREATE DATABASE {0} (EDITION = '{1}')", BracketEscapeName(db), Configuration.DatabaseEdition); cmd.CommandTimeout = 60; cmd.ExecuteNonQuery(); } }); // 等待操作完成 while (!DatabaseIsOnline(server, db)) { ConsoleUtils.WriteInfo("Waiting for database {0} to come online...", db); Thread.Sleep(TimeSpan.FromSeconds(5)); } ConsoleUtils.WriteInfo("Database {0} is online", db); } else { // 其他版本 SQL DB cmd.CommandText = string.Format("CREATE DATABASE {0}", BracketEscapeName(db)); conn.ExecuteCommand(cmd); } } }
protected override ReliableSqlConnection CreateReliableConnection() { var retryStrategy = new FixedInterval("Incremental Retry Strategy", 10, TimeSpan.FromSeconds(1)); var connection = new ReliableSqlConnection(null, new RetryPolicy<SqlExpressTransientErrorDetectionStrategy>(retryStrategy), new RetryPolicy<SqlExpressTransientErrorDetectionStrategy>(retryStrategy) ); connection.CommandRetryPolicy.Retrying += LogRetry(); connection.ConnectionRetryPolicy.Retrying += LogRetry(); return connection; }
public IDbConnection GetConnection(string connectionString = null) { //If connection string is null, use the default sql ce connection if (connectionString == null) { connectionString = ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; } var connection = new ReliableSqlConnection(connectionString, DefaultPolicy, DefaultPolicy); //var connection = new SqlConnection(connectionString); connection.Open(); return connection; }
private ReliableSqlConnection InitializeConnection() { ReliableSqlConnection connection = new ReliableSqlConnection(connectionString); connection.ConnectionRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format(CultureInfo.CurrentCulture, "[Connection Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); connection.CommandRetryPolicy.Retrying += (sender, args) => Trace.WriteLine(string.Format(CultureInfo.CurrentCulture, "[Command Retry] Current Retry Count: {0}, Last Exception: {0}, Delay (ms): {0}", args.CurrentRetryCount, args.LastException.Message, args.Delay.TotalMilliseconds)); connection.Current.StateChange += (sender, e) => Trace.WriteLine(string.Format(CultureInfo.CurrentCulture, "OriginalState: {0}, CurrentState {1}", e.OriginalState, e.CurrentState)); return connection; }
protected override ReliableSqlConnection CreateReliableConnection() { var retryStrategies = new List<RetryStrategy>(); const string incremental = "Incremental Retry Strategy"; const string interval = "Fixed Interval Retry Strategy"; const string backoff = "Backoff Retry Strategy"; retryStrategies.Add(new Incremental(incremental, 10, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(1))); retryStrategies.Add(new FixedInterval(interval, 10, TimeSpan.FromSeconds(1))); retryStrategies.Add(new ExponentialBackoff(backoff, 10, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(30), TimeSpan.FromSeconds(10), false)); var retryManager = new RetryManagerImpl(retryStrategies, interval, backoff, incremental, interval, interval, interval); var connection = new ReliableSqlConnection(null, retryManager.GetDefaultSqlConnectionRetryPolicy(), retryManager.GetDefaultSqlCommandRetryPolicy()); connection.ConnectionRetryPolicy.Retrying += RetryEventHandler(); connection.CommandRetryPolicy.Retrying += RetryEventHandler(); return connection; }
public void CreateTable() { // Uses specified connection for open, default for execute String commandText = @"CREATE TABLE Writer ( Id int PRIMARY KEY NOT NULL, Name nvarchar(20) NOT NULL, CountBooks int NULL)"; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString, connectionRetryPolicy)) { connection.Open(); using (SqlCommand sqlCommand = connection.CreateCommand()) { sqlCommand.CommandText = commandText; sqlCommand.ExecuteNonQueryWithRetry(); } } }
public static bool DatabaseIsOnline(string server, string db) { using (ReliableSqlConnection conn = new ReliableSqlConnection( Configuration.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; } }
private IEnumerable<ActivityDetailDataContract> getActivityDetailsUsingADO() { //var connectionString = getAzureConnectionString().ConnectionString; //var connectionString = ConfigurationManager.ConnectionStrings["mediaHubActivityConnectionString"].ConnectionString; var connectionString = CloudConfigurationManager.GetSetting("mediaHubActivityConnectionString"); using (var connection = new ReliableSqlConnection(connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "SELECT TOP 10000 * FROM j30t_changelog ORDER BY j30c_date DESC"; using (var reader = command.ExecuteReader()) { while (reader.Read()) yield return reader.Transform(); } } connection.Close(); } }
public List<KeyValuePair<string, string>> GetPairs() { try { List<KeyValuePair<string, string>> pairs = new List<KeyValuePair<string, string>>(); ReliableSQL sql = new ReliableSQL(); sql.Connection.ExecuteAction(() => { using (ReliableSqlConnection connection = new ReliableSqlConnection("Data Source=plasne-sql01.database.windows.net; Initial Catalog=plasne-db01; Authentication=Active Directory Integrated;")) { connection.Open(); using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT [key], [value] FROM [pairs]"; sql.Command.ExecuteAction(() => { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { pairs.Add(new KeyValuePair<string, string>((string)reader[0], (string)reader[1])); } } }); } } }); return pairs; } catch (Exception ex) { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.InternalServerError) { ReasonPhrase = Regex.Replace(ex.Message, @"\t|\n|\r", "") }); } }
public static void ExecuteSqlScript(string server, string db, string schemaFile) { ConsoleUtils.WriteInfo("Executing script {0}", schemaFile); using (ReliableSqlConnection conn = new ReliableSqlConnection( Configuration.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); } } }
private void DeleteCustomerAddress(int customerId, int addressId) { using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString)) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "DELETE FROM [SalesLT].[CustomerAddress] WHERE [CustomerID] = @CustomerID AND [AddressID] = @AddressID"; command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId; command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId; command.ExecuteNonQueryWithRetry(); } connection.Close(); } }
private bool VerifyCustomerAddress(int customerId, int addressId) { int count = 0; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString)) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT COUNT(*) FROM [SalesLT].[CustomerAddress] WHERE [CustomerID] = @CustomerID AND [AddressID] = @AddressID"; command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId; command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId; count = (int)command.ExecuteScalarWithRetry(); } connection.Close(); } return count > 0; }
private int RetrieveCountofTable() { int count = 0; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString)) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT COUNT(*) FROM [SalesLT].[CustomerAddress]"; count = (int)command.ExecuteScalarWithRetry(); } connection.Close(); } return count; }
public String GetSessionTracingId() { // using all default policies String commandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())"; String sessionTracingId; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString)) { connection.Open(); using (SqlCommand sqlCommand = connection.CreateCommand()) { sqlCommand.CommandText = commandText; sessionTracingId = sqlCommand.ExecuteScalarWithRetry() as String; } } return sessionTracingId; }
private ReliableSqlConnection GetConnection() { String conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; ReliableSqlConnection sqlConnection = new ReliableSqlConnection(conString); return sqlConnection; }
public void QueryTable() { //uses provided policies String commandText = "SELECT * FROM Writer"; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString, connectionRetryPolicy, commandRetryPolicy)) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(commandText, connection.Current)) { using (IDataReader reader = connection.ExecuteCommand<IDataReader>(sqlCommand)) { Int32 idColumn = reader.GetOrdinal("Id"); Int32 nameColumn = reader.GetOrdinal("Name"); Int32 countBooksColumn = reader.GetOrdinal("CountBooks"); while (reader.Read()) { Int32 id = (Int32)reader[idColumn]; String name = reader[nameColumn] as String; Int32? countBooks = reader[countBooksColumn] as Int32?; } } } } }
/// <summary> /// Constructs a <see cref="ReliableSqlDbConnection"/> to wrap around the given <see cref="ReliableSqlConnection"/>. /// </summary> /// <param name="connection">The <see cref="ReliableSqlConnection"/> to wrap</param> public ReliableSqlDbConnection(ReliableSqlConnection connection) { ReliableConnection = connection; }
public void DropTable() { // Uses default for connection open and specified for command String commandText = "DROP TABLE Writer"; using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString)) { connection.Open(connectionRetryPolicy); using (SqlCommand sqlCommand = connection.CreateCommand()) { sqlCommand.CommandText = commandText; sqlCommand.ExecuteNonQueryWithRetry(commandRetryPolicy); } } }
private static void SQLAzureRetry() { var settings = DatabaseSettings.GetDatabaseSettings( new SystemConfigurationSource()); // Create a retry policy that uses a default retry strategy from the // configuration. var retryPolicy = RetryPolicyFactory.GetDefaultSqlConnectionRetryPolicy(); using (var conn = new ReliableSqlConnection( ConfigurationManager.ConnectionStrings[settings.DefaultDatabase].ConnectionString, retryPolicy)) { // Attempt to open a connection using the retry policy specified // when the constructor is invoked. conn.Open(); // ... execute SQL queries against this connection ... } }
void purchaseWorker_DoWork(object sender, DoWorkEventArgs e) { try { object[] args = e.Argument as object[]; if (args.Length != 8 || Convert.ToInt32(args[3]) <= 0 || Convert.ToInt32(args[4]) <= 0 || Convert.ToInt32(args[5]) <= 0 || Convert.ToInt32(args[6]) <= 0 || Convert.ToInt32(args[7]) <= 0) { e.Result = "Please ensure you have selected a concert, ticket level, and customer, as well as supplied the ticket count and bulk purchase quantity."; return; } string conn = ConstructConn(args[0].ToString(), TenantDbName, args[1].ToString(), args[2].ToString()); string rootQuery = string.Format("Insert Into Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate) Values ({0}, '', {1}, {2}, GETDATE())", Convert.ToInt32(args[5]).ToString(), Convert.ToInt32(args[4]).ToString(), Convert.ToInt32(args[3]).ToString()); string runtimeQuery = string.Empty; int bulkPurchase = Convert.ToInt32(args[7]); for (int i = 0; i < bulkPurchase; i++) runtimeQuery += rootQuery + "; "; int ticketCount = Convert.ToInt32(args[6]); int purchaseCounter = 0; var retryPolicy = new RetryPolicy<CustomTransientErrorDetectionStrategy>(exponentialBackoffStrategy); retryPolicy.ExecuteAction(() => { using (ReliableSqlConnection reliableSqlConnection = new ReliableSqlConnection(conn, retryPolicy)) { reliableSqlConnection.Open(retryPolicy); IDbTransaction transaction = reliableSqlConnection.BeginTransaction(); using (var cmd = new SqlCommand(runtimeQuery, reliableSqlConnection.Current, (SqlTransaction)transaction)) { while (purchaseCounter < ticketCount) { if (purchaseWorker.CancellationPending) break; if (ticketCount - purchaseCounter < bulkPurchase) { runtimeQuery = string.Empty; bulkPurchase = ticketCount - purchaseCounter; for (int i = 0; i < bulkPurchase; i++) runtimeQuery += rootQuery; cmd.CommandText = runtimeQuery; } cmd.ExecuteNonQueryWithRetry(retryPolicy); purchaseWorker.ReportProgress(Convert.ToInt32(((purchaseCounter * 1.0) / ticketCount) * 100), purchaseCounter); purchaseCounter = purchaseCounter + bulkPurchase; } transaction.Commit(); } } }); e.Result = purchaseCounter + " tickets purchased"; } catch (Exception ex) { e.Result = ex.Message; } }
public static void DropDatabase(string server, string db) { ConsoleUtils.WriteInfo("Dropping database {0}", db); using (ReliableSqlConnection conn = new ReliableSqlConnection( Configuration.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(); } } }
[Ignore] // Unstable test public void TestServerNameSubstitutionWithIPAddress() { using (var conn = new ReliableSqlConnection(connectionString)) { Assert.AreEqual(connectionString, conn.Current.ConnectionString, "Connection string managed by ReliableSqlConnection class must not be modified at this point."); Assert.AreNotEqual<Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); } Thread.Sleep(1000); using (var conn = new ReliableSqlConnection(connectionString)) { Assert.AreNotEqual(connectionString, conn.Current.ConnectionString, "Connection string managed by ReliableSqlConnection class has not been modified."); Assert.AreNotEqual<Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); IPAddress hostAddress = null; var conStringBuilder = new SqlConnectionStringBuilder(conn.Current.ConnectionString); string hostName = conStringBuilder.DataSource.StartsWith("tcp:") ? conStringBuilder.DataSource.Remove(0, "tcp:".Length) : conStringBuilder.DataSource; Assert.IsTrue(IPAddress.TryParse(hostName, out hostAddress), "The data source doesn't seem to be represented by an IP address."); conn.Current.Close(); conn.Current.ConnectionString = conn.Current.ConnectionString.Replace("0", "1").Replace("2", "3").Replace("4", "5").Replace("6", "7"); conn.Open(); Assert.AreNotEqual<Guid>(conn.SessionTracingId, Guid.Empty, "Unable to resolve the connection's session ID."); } }
public AzureSqlConnection(RetryPolicy connectionRetryPolicy, RetryPolicy commandRetryPolicy) { this.reliableConnection = new ReliableSqlConnection(null, connectionRetryPolicy, commandRetryPolicy); }