public void Cleanup()
{
//RetryPolicyFactory.SetRetryManager(null, false);
if (connection != null)
{
// Work around, close the connection manually.
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
public void TestSingleCommandWithClosing()
{
ReliableSqlConnection connection = new ReliableSqlConnection(connectionString);
SqlCommand command = new SqlCommand("SELECT 1");
connection.ExecuteCommand(command);
connection.Close();
connection.ExecuteCommand(command);
}
public void TestSingleCommandWithClosing()
{
ReliableSqlConnection connection = new ReliableSqlConnection(connectionString);
SqlCommand command = new SqlCommand("SELECT 1");
connection.ExecuteCommand(command);
connection.Close();
connection.ExecuteCommand(command);
}
public void Cleanup()
{
if (connection != null)
{
// Work around, close the connection manually.
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
private void DeleteCustomerAddress(int customerId, int addressId)
{
using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString))
{
using (SqlCommand command = (SqlCommand)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 int RetrieveCountofTable()
{
int count = 0;
using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString))
{
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM [SalesLT].[CustomerAddress]";
count = (int)command.ExecuteScalarWithRetry();
}
connection.Close();
}
return(count);
}
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 bool VerifyCustomerAddress(int customerId, int addressId)
{
int count = 0;
using (ReliableSqlConnection connection = new ReliableSqlConnection(connectionString))
{
using (SqlCommand command = (SqlCommand)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 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 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);
}
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 override void Close()
{
_connection.Close();
}
/// <summary>
/// Closes the connection to the database. This is the preferred method of closing any open connection.
/// </summary>
public override void Close()
{
ReliableConnection.Close();
}
public bool HasAccess(string accessDeviceId, int accessDeviceType, int locationId)
{
var command = _sqlConnection.CreateCommand();
command.CommandText = string.Format(@"select Id, PoolId from AccessControlList where AccessDevice = '{0}' and AccessDeviceType = {1} and LocationId = {2}", accessDeviceId, accessDeviceType, locationId);
int?aclId = null;
int?poolId = null;
try
{
_sqlConnection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
aclId = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
poolId = reader.GetInt32(1);
}
}
}
catch (Exception e)
{
//either the number of retries specified in the policy is exceeded or there is another exception?
throw e;
}
finally
{
_sqlConnection.Close();
}
if (aclId == null)
{
return(false);
}
if (poolId == null)
{
return(true);
}
//check pool
List <Pool> poolList = new List <Pool>();
try
{
_sqlConnection.Open();
var poolCommand = _sqlConnection.CreateCommand();
poolCommand.CommandText = string.Format(@"
select p.Id, p.MaxAllowed, p.Occupied, p.Hard
from Pool p where p.Id = {0}", poolId);
using (var reader = poolCommand.ExecuteReader())
{
while (reader.Read())
{
poolList.Add(new Pool
{
Id = reader.GetInt32(0),
MaxAllowed = reader.GetInt32(1),
Occupied = reader.GetInt32(2),
Hard = reader.GetBoolean(3)
});
}
}
}
catch (Exception e)
{
//either the number of retries specified in the policy is exceeded or there is another exception?
throw e;
}
finally
{
_sqlConnection.Close();
}
if (poolList.Any())
{
var pool = poolList.Single();
if (pool.Hard && pool.MaxAllowed <= pool.Occupied)
{
return(false);
}
var updatePoolCommand = _sqlConnection.CreateCommand();
updatePoolCommand.CommandText = string.Format(@"Update Pool set Occupied= {0} where Id = {1}", ++pool.Occupied, pool.Id);
try
{
_sqlConnection.Open();
updatePoolCommand.ExecuteNonQuery();
}
catch (Exception e)
{
//either the number of retries specified in the policy is exceeded or there is another exception?
throw e;
}
finally
{
_sqlConnection.Close();
}
}
return(true);
}