public void TestSingleCommandWithoutClosing() { ReliableSqlConnection connection = new ReliableSqlConnection(connectionString); SqlCommand command = new SqlCommand("SELECT 1"); connection.ExecuteCommand(command); connection.ExecuteCommand(command); }
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 static void CreateDatabase(string server, string db) { ConsoleUtils.WriteInfo("Creating 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); 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), Configuration.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)); //} ConsoleUtils.WriteInfo("Database {0} is online", db); } else { // Other edition of SQL DB cmd.CommandText = string.Format("CREATE DATABASE {0}", BracketEscapeName(db)); conn.ExecuteCommand(cmd); } } }
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(); } } }
/// <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 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 TestFailedTransaction() { connection.Open(); IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable); SqlCommand command = (SqlCommand)connection.CreateCommand(); command.CommandText = "SELECT COUNT(*) FROM [SalesLT].[CustomerAddress]"; command.Transaction = (SqlTransaction)transaction; int expectedCount = connection.ExecuteCommand(command); command = (SqlCommand)connection.CreateCommand(); command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]"; command.Transaction = (SqlTransaction)transaction; int customerId = (int)command.ExecuteScalarWithRetry(); command = (SqlCommand)connection.CreateCommand(); command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]"; command.Transaction = (SqlTransaction)transaction; int addressId = (int)command.ExecuteScalarWithRetry(); 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.Transaction = (SqlTransaction)transaction; command.ExecuteNonQueryWithRetry(); transaction.Rollback(); command = (SqlCommand)connection.CreateCommand(); command.CommandText = "SELECT COUNT(*) FROM [SalesLT].[CustomerAddress]"; command.Transaction = (SqlTransaction)transaction; int actualCount = connection.ExecuteCommand(command); connection.Close(); Assert.AreEqual(expectedCount, actualCount, "Rollback failed"); }
/// <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); } } }
/// <summary> /// Retrieves data from the specified queue item stored in the persistence queue. /// </summary> /// <param name="queueItemId">The unique identity of the item.</param> /// <param name="headerXPath">The optional set of XPath expressions defining a header portion of the queue data.</param> /// <param name="bodyXPath">The optional set of XPath expressions defining a body portion of the queue data.</param> /// <param name="footerXPath">The optional set of XPath expressions defining a footer portion of the queue data.</param> /// <param name="nsManager">The optional XML namespace manager that will be used for XML namespace resolution.</param> /// <returns>An instance of the XML reader that provides non-cached, forward-only access to queue item data.</returns> public XmlReader DequeueXmlData(Guid queueItemId, IEnumerable <string> headerXPath, IEnumerable <string> bodyXPath, IEnumerable <string> footerXPath, XmlNamespaceManager nsManager) { var callToken = TraceManager.DataAccessComponent.TraceIn(queueItemId.ToString()); var scopeStartMain = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueDequeueXmlDataMain, callToken); ReliableSqlConnection dbConnection = null; bool leaveConnectionOpen = false; try { // SQL connection is intentionally left non-disposed here. It will be disposed along with XmlReader which this method returns (if at all). // This behavior is enforced through specifying CommandBehavior.CloseConnection when invoking the ExecuteCommand method. dbConnection = new ReliableSqlConnection(this.dbConnectionString, this.connectionRetryPolicy, this.commandRetryPolicy); using (IDbCommand dequeueCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateDequeueXmlDataCommand(dbConnection, queueItemId, headerXPath != null ? headerXPath.ToArray <string>() : null, bodyXPath != null ? bodyXPath.ToArray <string>() : null, footerXPath != null ? footerXPath.ToArray <string>() : null, nsManager)) { TraceManager.DataAccessComponent.TraceCommand(dequeueCommand); XmlReader xmlDataReader = dbConnection.ExecuteCommand <XmlReader>(dequeueCommand, CommandBehavior.CloseConnection); if (xmlDataReader != null && xmlDataReader.Read()) { XmlReaderSettings readerSettings = new XmlReaderSettings() { CheckCharacters = false, IgnoreComments = true, IgnoreProcessingInstructions = true, IgnoreWhitespace = true, ValidationType = ValidationType.None, ConformanceLevel = ConformanceLevel.Auto }; XmlReader nonValidatingReader = XmlReader.Create(xmlDataReader, readerSettings); leaveConnectionOpen = true; return(nonValidatingReader); } } } catch (Exception ex) { TraceManager.DataAccessComponent.TraceError(ex, callToken); } finally { if (dbConnection != null && !leaveConnectionOpen) { dbConnection.Dispose(); } TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueDequeueXmlDataMain, scopeStartMain, callToken); TraceManager.DataAccessComponent.TraceOut(callToken); } return(null); }
/// <summary> /// Retourne vrai si la base de données souhaitée est en ligne /// </summary> /// <param name="masterDbConnectionString"></param> /// <param name="expectedDbName"></param> /// <returns></returns> public static bool DatabaseIsOnline(string masterDbConnectionString, string expectedDbName) { using (ReliableSqlConnection conn = new ReliableSqlConnection(masterDbConnectionString, 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", expectedDbName); cmd.CommandTimeout = 60; int count = conn.ExecuteCommand <int>(cmd); bool exists = count > 0; return(exists); } }
public void ThrowsExceptionWhenAllRetriesFailAndCommandExecutedWithoutRetryPolicy() { RetryManager.SetDefault(this.retryPolicySettings.BuildRetryManager(), false); var connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString; var policy = this.retryManager.GetRetryPolicy <FakeSqlAzureTransientErrorDetectionStrategy>("Retry 5 times"); var reliableConnection = new ReliableSqlConnection(connectionString, policy, policy); int count = 0; policy.Retrying += (s, args) => { count = args.CurrentRetryCount; }; int rowCount = 0; try { SqlCommand command = new SqlCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "ErrorRaisingReader"; command.Parameters.Add(new SqlParameter("rowId", SqlDbType.UniqueIdentifier) { Value = Guid.NewGuid() }); command.Parameters.Add(new SqlParameter("maxCountToRaiseErrors", SqlDbType.Int) { Value = 7 }); command.Parameters.Add(new SqlParameter("error", SqlDbType.Int) { Value = 60000 }); reliableConnection.Open(); rowCount = reliableConnection.ExecuteCommand(command); } catch (Exception) { reliableConnection.Close(); Assert.AreEqual <int>(5, count); Assert.AreEqual(0, rowCount); throw; } Assert.Fail("test should throw"); }
private void StopJob(Guid activityId) { var rPolicy = new RetryPolicy <SqlAzureTransientErrorDetectionStrategy> (5, TimeSpan.FromMilliseconds(150)); using (ReliableSqlConnection con = new ReliableSqlConnection(DefaultConnectionString(), rPolicy)) { con.Open(); using (var cmdStop = con.CreateCommand()) { cmdStop.CommandType = System.Data.CommandType.StoredProcedure; cmdStop.CommandText = "StopJob"; cmdStop.Parameters.AddWithValue("@ActivityId", activityId); con.ExecuteCommand(cmdStop, rPolicy); } } }
/// <summary> /// Performs a query against the XML data stored in the queue item. /// </summary> /// <param name="queueItemId">The unique identity of the queue item.</param> /// <param name="xPathCollection">One or more XPath expressions which will be invoked against the queue item's XML data.</param> /// <param name="nsManager">The XML namespace manager that will be used for XML namespace resolution.</param> /// <returns>An instance of the XML reader that provides non-cached, forward-only access to queue item data.</returns> public XmlReader QueryXmlData(Guid queueItemId, IEnumerable <string> xPathCollection, XmlNamespaceManager nsManager) { var callToken = TraceManager.DataAccessComponent.TraceIn(queueItemId.ToString()); var scopeStartMain = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueQueryXmlDataMain, callToken); ReliableSqlConnection dbConnection = null; bool leaveConnectionOpen = false; try { // SQL connection is intentionally left non-disposed here. It will be disposed along with XmlReader which this method returns (if at all). // This behavior is enforced through specifying CommandBehavior.CloseConnection when invoking the ExecuteCommand method. dbConnection = new ReliableSqlConnection(this.dbConnectionString, this.connectionRetryPolicy, this.commandRetryPolicy); using (IDbCommand queryCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateQueryXmlDataCommand(dbConnection, queueItemId, xPathCollection != null ? xPathCollection.ToArray <string>() : null, nsManager)) { TraceManager.DataAccessComponent.TraceCommand(queryCommand); XmlReader xmlDataReader = dbConnection.ExecuteCommand <XmlReader>(queryCommand, CommandBehavior.CloseConnection); if (xmlDataReader != null && xmlDataReader.Read()) { leaveConnectionOpen = true; return(xmlDataReader); } } } catch (Exception ex) { TraceManager.DataAccessComponent.TraceError(ex, callToken); } finally { if (dbConnection != null && !leaveConnectionOpen) { dbConnection.Dispose(); } TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueQueryXmlDataMain, scopeStartMain, callToken); TraceManager.DataAccessComponent.TraceOut(callToken); } return(null); }
public static bool DatabaseExists(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"; cmd.Parameters.AddWithValue("@dbname", db); cmd.CommandTimeout = 60; int count = conn.ExecuteCommand<int>(cmd); bool exists = count > 0; return exists; } }
public static bool DatabaseExists(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"; 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 masterDbConnectionString, string schemaFile) { //ConsoleUtils.WriteInfo("Executing script {0}", schemaFile); using (ReliableSqlConnection conn = new ReliableSqlConnection(masterDbConnectionString, 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); } } }
public static void ExecuteSqlScript(string server, string db, string 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); } } }
/// <summary> /// To execute sql script /// </summary> /// <param name="server"></param> /// <param name="db"></param> /// <param name="schemaFile"></param> public static void ExecuteSqlScript(string server, string db, string schemaFile) { ConsoleUtils.WriteInfo("Executing script {0}", schemaFile); using (ReliableSqlConnection conn = new ReliableSqlConnection( MultiShardConfiguration.GetConnectionStringForSelectedDatabase(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); } } }
public void ExecutesCommandWithRetryPolicyWhenSomeRetriesFailAndThenSucceeds() { RetryManager.SetDefault(this.retryPolicySettings.BuildRetryManager(), false); var connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString; var reliableConnection = new ReliableSqlConnection(connectionString); var policy = this.retryManager.GetRetryPolicy <FakeSqlAzureTransientErrorDetectionStrategy>("Retry 5 times"); int count = 0; policy.Retrying += (s, args) => { count = args.CurrentRetryCount; }; SqlCommand command = new SqlCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "ErrorRaisingReader"; command.Parameters.Add(new SqlParameter("rowId", SqlDbType.UniqueIdentifier) { Value = Guid.NewGuid() }); command.Parameters.Add(new SqlParameter("maxCountToRaiseErrors", SqlDbType.Int) { Value = 4 }); command.Parameters.Add(new SqlParameter("error", SqlDbType.Int) { Value = 60000 }); reliableConnection.Open(); var rowCount = reliableConnection.ExecuteCommand(command, policy); reliableConnection.Close(); Assert.AreEqual <int>(3, count); Assert.AreEqual(1, rowCount); }
/// <summary> /// Execute Stored Procedure /// </summary> /// <param name="procedureName">procedure Name</param> /// <param name="parameters">parameters dictionary</param> /// <param name="timeOutSecs">Timeout for Command in Seconds</param> /// <returns>Task of string</returns> public async Task <string> ExecuteStoredProcedureAsync(string procedureName, Dictionary <string, object> parameters, int timeOutSecs) { const int RetryCountMin = 3; const int RetryCountMax = 5; const int MinBackOffTimeMsecs = 100; const int MaxBackOffTimeMsecs = 30; RetryManager.SetDefault( new RetryManager( new List <RetryStrategy> { new ExponentialBackoff( name: Default, retryCount: RetryCountMin, minBackoff: TimeSpan.FromMilliseconds(MinBackOffTimeMsecs), maxBackoff: TimeSpan.FromSeconds(MaxBackOffTimeMsecs), deltaBackoff: TimeSpan.FromSeconds(1), firstFastRetry: true), new ExponentialBackoff( name: DefaultSQLConnection, retryCount: RetryCountMin, minBackoff: TimeSpan.FromMilliseconds(MinBackOffTimeMsecs), maxBackoff: TimeSpan.FromSeconds(MaxBackOffTimeMsecs), deltaBackoff: TimeSpan.FromSeconds(1), firstFastRetry: true), new ExponentialBackoff( name: DefaultSQLCommand, retryCount: RetryCountMin, minBackoff: TimeSpan.FromMilliseconds(MinBackOffTimeMsecs), maxBackoff: TimeSpan.FromSeconds(MaxBackOffTimeMsecs), deltaBackoff: TimeSpan.FromSeconds(1), firstFastRetry: true), new ExponentialBackoff( name: AltSQL, retryCount: RetryCountMax, minBackoff: TimeSpan.FromMilliseconds(MinBackOffTimeMsecs), maxBackoff: TimeSpan.FromSeconds(MaxBackOffTimeMsecs), deltaBackoff: TimeSpan.FromSeconds(1), firstFastRetry: true), }, Default, new Dictionary <string, string> { { RetryManagerSqlExtensions .DefaultStrategyConnectionTechnologyName, DefaultSQLConnection }, { RetryManagerSqlExtensions.DefaultStrategyCommandTechnologyName, DefaultSQLCommand } }), false); return(await Task.Run( () => { var retryConnectionPolicy = RetryManager.Instance.GetDefaultSqlConnectionRetryPolicy(); var retryCommandPolicy = RetryManager.Instance.GetDefaultSqlCommandRetryPolicy(); using ( ReliableSqlConnection conn = new ReliableSqlConnection( this.connectionString, retryConnectionPolicy, retryCommandPolicy)) { SqlParameter outResult = new SqlParameter("@result", SqlDbType.NVarChar, -1) { Direction = ParameterDirection .Output }; conn.Open(); var command = conn.CreateCommand(); command.CommandText = procedureName; command.CommandType = CommandType.StoredProcedure; if (timeOutSecs > 0) { command.CommandTimeout = (timeOutSecs > MaxTimeOut) ? MaxTimeOut : timeOutSecs; } foreach (var param in parameters) { command.Parameters.AddWithValue(param.Key, param.Value); } command.Parameters.Add(outResult); conn.ExecuteCommand(command); return outResult.Value.ToString(); } }).ConfigureAwait(false)); }
private void StopJob(Guid activityId) { var rPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy> (5, TimeSpan.FromMilliseconds(150)); using (ReliableSqlConnection con = new ReliableSqlConnection(DefaultConnectionString(), rPolicy)) { con.Open(); using (var cmdStop = con.CreateCommand()) { cmdStop.CommandType = System.Data.CommandType.StoredProcedure; cmdStop.CommandText = "StopJob"; cmdStop.Parameters.AddWithValue("@ActivityId", activityId); con.ExecuteCommand(cmdStop, rPolicy); } } }
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); } } }
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(); } } }
public void TestExecuteSimpleCommand() { SqlCommand command = new SqlCommand("SELECT 1"); connection.ExecuteCommand(command); }
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> /// Persists the specified stream of data in the queue. /// </summary> /// <param name="data">The stream containing the data to be persisted.</param> /// <returns>A data transfer object carrying the details of the persisted queue item.</returns> public PersistenceQueueItemInfo Enqueue(Stream data) { Guard.ArgumentNotNull(data, "data"); Guard.ArgumentNotNullOrEmptyString(this.dbConnectionString, "dbConnectionString"); var callToken = TraceManager.DataAccessComponent.TraceIn(StreamingMode, StreamingDataType); var scopeStartEnqueueMain = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueEnqueueMain, callToken); try { PersistenceQueueItemInfo queueItemInfo = null; Guid txGuid = default(Guid); using (var txScope = new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.MaxValue)) using (var dbConnection = new ReliableSqlConnection(this.dbConnectionString, this.connectionRetryPolicy, this.commandRetryPolicy)) { if (StreamingDataType == StreamingDataType.Raw) { var scopeStartExecuteQuery = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueExecuteCommand, callToken); using (IDbCommand newItemCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateNewCommand(dbConnection)) { TraceManager.DataAccessComponent.TraceCommand(newItemCommand); txGuid = dbConnection.ExecuteCommand <Guid>(newItemCommand); using (IDbCommand readDataCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateQueueItemReadCommand(dbConnection, txGuid)) using (IDbCommand writeDataCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateQueueItemWriteCommand(dbConnection, txGuid)) using (IDbCommand getDataSizeCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateQueueItemGetSizeCommand(dbConnection, txGuid)) using (SqlStream sqlStream = new SqlStream(dbConnection, readDataCommand as SqlCommand, writeDataCommand as SqlCommand, getDataSizeCommand as SqlCommand)) { BinaryReader dataReader = new BinaryReader(data); byte[] buffer = new byte[this.initialBufferSize]; int bytesRead = 0; do { var scopeStartBufferRead = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueBufferedReadBytes, callToken); bytesRead = dataReader.ReadBuffered(buffer, 0, this.initialBufferSize); TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueBufferedReadBytes, scopeStartBufferRead, callToken); if (bytesRead > 0) { TraceManager.DataAccessComponent.TraceInfo(TraceLogMessages.SqlStreamWriteOperationDetails, bytesRead); var scopeStartSqlWriteData = TraceManager.DataAccessComponent.TraceStartScope(Resources.ScopeSqlAzurePersistenceQueueWriteData, callToken); sqlStream.Write(buffer, 0, bytesRead); TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueWriteData, scopeStartSqlWriteData, callToken); } }while (bytesRead > 0); } } using (IDbCommand enqueueCommand = CustomSqlCommandFactory.SqlAzurePersistenceQueue.CreateEnqueueCommand(dbConnection, txGuid)) { TraceManager.DataAccessComponent.TraceCommand(enqueueCommand); dbConnection.ExecuteCommand(enqueueCommand); SqlCommandView <EnqueueCommandInspector> enqueueCommandView = new SqlCommandView <EnqueueCommandInspector>(enqueueCommand); queueItemInfo = new PersistenceQueueItemInfo(txGuid, enqueueCommandView.Inspector.QueueItemSize, enqueueCommandView.Inspector.QueueItemType); } txScope.Complete(); TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueExecuteCommand, scopeStartExecuteQuery, callToken); } return(queueItemInfo); } } catch (Exception ex) { TraceManager.DataAccessComponent.TraceError(ex, callToken); throw; } finally { TraceManager.DataAccessComponent.TraceEndScope(Resources.ScopeSqlAzurePersistenceQueueEnqueueMain, scopeStartEnqueueMain, callToken); TraceManager.DataAccessComponent.TraceOut(callToken); } }