public void TestSingleCommandWithoutClosing()
{
ReliableSqlConnection connection = new ReliableSqlConnection(connectionString);
SqlCommand command = new SqlCommand("SELECT 1");
connection.ExecuteCommand(command);
connection.ExecuteCommand(command);
}
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 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);
}
}
