public void StoredProcedure_WithSqlTransactionCommitted_DoesWriteRecords()
{
// ARRANGE
const int expectedIntermediateCount = 5;
int originalCount;
int intermediateCount;
int finalCount;
string connectionString = Settings.Default.ExampleDatabaseConnection;
var companiesToAdd = new List<CompaniesAdd.CompanyTableType>
{
new CompaniesAdd.CompanyTableType { CompanyName = "Company 1", IsActive = true, TenantId = 2 },
new CompaniesAdd.CompanyTableType { CompanyName = "Company 2", IsActive = false, TenantId = 2 },
new CompaniesAdd.CompanyTableType { CompanyName = "Company 3", IsActive = true, TenantId = 2 }
};
var companiesAddParameters = new CompaniesAdd.CompaniesAddParameters
{
Companies = companiesToAdd
};
var companyAddProcedure = new CompaniesAdd(companiesAddParameters);
var companyCountProcedure = new CompanyCountAll();
var companyDeleteParameters = new TenantIdParameters
{
TenantId = 2
};
var companyDeleteProcedure = new CompanyDeleteForTenantId(companyDeleteParameters);
// ACT
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction;
using (transaction = connection.BeginTransaction())
{
originalCount = connection.ExecuteStoredProcedure(companyCountProcedure, transaction: transaction).First().CountOfCompanies;
connection.ExecuteStoredProcedure(companyAddProcedure, transaction: transaction);
transaction.Commit();
}
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
intermediateCount = connection.ExecuteStoredProcedure(companyCountProcedure).First().CountOfCompanies;
connection.ExecuteStoredProcedure(companyDeleteProcedure);
finalCount = connection.ExecuteStoredProcedure(companyCountProcedure).First().CountOfCompanies;
connection.Close();
}
// ASSERT
Assert.AreEqual(originalCount, finalCount);
Assert.AreEqual(expectedIntermediateCount, intermediateCount);
}
public void StoredProcedure_WithSqlTransactionCommitted_DoesWriteRecords()
{
// ARRANGE
const int expectedIntermediateCount = 3;
int originalCount;
int intermediateCount;
int finalCount;
string connectionString = Settings.Default.IntegrationTestConnection;
var itemsToAdd = new List<TransactionTestParameterTableType>
{
new TransactionTestParameterTableType { Name = "Company 1", IsActive = true, Id = 1 },
new TransactionTestParameterTableType { Name = "Company 2", IsActive = false, Id = 2 },
new TransactionTestParameterTableType { Name = "Company 3", IsActive = true, Id = 3 }
};
var transactionAddParameters = new TransactionTestAddStoredProcedure.Parameter
{
TvpParameters = itemsToAdd
};
var transactionTestAddProcedure = new TransactionTestAddStoredProcedure(transactionAddParameters);
var transactionTestCountProcedure = new TransactionTestCountAllStoredProcedure();
var transactionDeleteProcedure = new TransactionTestDeleteAllStoredProcedure();
// ACT
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction;
using (transaction = connection.BeginTransaction())
{
originalCount = connection.ExecuteStoredProcedure(transactionTestCountProcedure, transaction: transaction).First().Count;
connection.ExecuteStoredProcedure(transactionTestAddProcedure, transaction: transaction);
transaction.Commit();
}
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
intermediateCount = connection.ExecuteStoredProcedure(transactionTestCountProcedure).First().Count;
connection.ExecuteStoredProcedure(transactionDeleteProcedure);
finalCount = connection.ExecuteStoredProcedure(transactionTestCountProcedure).First().Count;
connection.Close();
}
// ASSERT
Assert.AreEqual(originalCount, finalCount);
Assert.AreEqual(expectedIntermediateCount, intermediateCount);
}
public void NormalStoredProcedure_WhenCalledOnSqlConnection_ReturnsCorrectValues()
{
// ARRANGE
const int expectedId = 10;
const string expectedName = @"Dave";
const bool expectedActive = true;
var parameters = new NormalStoredProcedureParameters
{
Id = expectedId
};
List<NormalStoredProcedureReturnType> results;
var procedure = new NormalStoredProcedure(parameters);
var connectionString = ConfigurationManager.ConnectionStrings["IntegrationTestConnection"].ConnectionString;
// ACT
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
results = connection.ExecuteStoredProcedure(procedure);
}
var result = results.First();
// ASSERT
Assert.AreEqual(expectedId, result.Id);
Assert.AreEqual(expectedName, result.Name);
Assert.AreEqual(expectedActive, result.Active);
}
public void ExecuteStoredProcedure_WithNullStoredProcedure_ThrowsArgumentNullException()
{
// ARRANGE
MostBasicStoredProcedure procedure = null;
var connectionString = ConfigurationManager.ConnectionStrings["IntegrationTestConnection"].ConnectionString;
// ACT
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// ReSharper disable once ExpressionIsAlwaysNull
connection.ExecuteStoredProcedure(procedure);
}
// ASSERT
// Exception should have been thrown
}
public void CompanyGetAllForTenantId()
{
// ARRANGE
const int expectedCompanyCount = 2;
string connectionName = Settings.Default.ExampleDatabaseConnection;
var parameters = new TenantIdParameters { TenantId = 1 };
var procedure = new CompanyGetAllForTenantID(parameters);
List<CompanyDto> companies;
CompanyDto company1;
// ACT
using (SqlConnection connection = new SqlConnection(connectionName))
{
companies = connection.ExecuteStoredProcedure(procedure);
company1 = companies.FirstOrDefault();
}
// ASSERT
Assert.AreEqual(expectedCompanyCount, companies.Count);
Assert.IsNotNull(company1);
}
public void StoredProcedure_WithTransactionScopeNotCommited_DoesNotWriteRecords()
{
// ARRANGE
const int expectedIntermediateCount = 5;
int originalCount;
int intermediateCount;
int finalCount;
string connectionString = Settings.Default.ExampleDatabaseConnection;
var companiesToAdd = new List<CompaniesAdd.CompanyTableType>
{
new CompaniesAdd.CompanyTableType { CompanyName = "Company 1", IsActive = true, TenantId = 2 },
new CompaniesAdd.CompanyTableType { CompanyName = "Company 2", IsActive = false, TenantId = 2 },
new CompaniesAdd.CompanyTableType { CompanyName = "Company 3", IsActive = true, TenantId = 2 }
};
var parameters = new CompaniesAdd.CompaniesAddParameters
{
Companies = companiesToAdd
};
var companyAddProcedure = new CompaniesAdd(parameters);
var companyCountProcedure = new CompanyCountAll();
// ACT
using (new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
originalCount = connection.ExecuteStoredProcedure(companyCountProcedure).First().CountOfCompanies;
connection.ExecuteStoredProcedure(companyAddProcedure);
intermediateCount = connection.ExecuteStoredProcedure(companyCountProcedure).First().CountOfCompanies;
}
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
finalCount = connection.ExecuteStoredProcedure(companyCountProcedure).First().CountOfCompanies;
connection.Close();
}
// ASSERT
Assert.AreEqual(originalCount, finalCount);
Assert.AreEqual(expectedIntermediateCount, intermediateCount);
}
