public void LoadMultipleRowsWithParameters() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId
FROM Merchant
INNER JOIN MerchantConfiguration ON MerchantConfiguration.MerchantId = Merchant.MerchantId
WHERE MerchantConfiguration.IsEnabled = @IsEnabled;";
IEnumerable<MerchantData> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Define o parâmetro IsEnabled da query como true, para retornar apenas as lojas habilitadas.
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { IsEnabled = true });
}
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Count());
Assert.AreEqual("Merchant Number One", actual.ElementAt(0).Name);
Assert.AreEqual(1, actual.ElementAt(0).MerchantId);
Assert.AreEqual("Merchant Test", actual.ElementAt(1).Name);
Assert.AreEqual(3, actual.ElementAt(1).MerchantId);
}
public void LoadSingleRowWithParametersFromEntityWithInvalidParameter() {
string query = @"SELECT Merchant.Name FROM Merchant WHERE Merchant.MerchantId = @MerchantId AND Merchant.Name = @MerchantConfiguration;";
MerchantData actual = null;
MerchantEntity merchantEntity = new MerchantEntity();
merchantEntity.MerchantId = 2;
merchantEntity.MerchantConfiguration = new MerchantConfigurationEntity();
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.OnOutput += databaseConnector_OnOutput;
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, merchantEntity).FirstOrDefault();
}
string output = this.ConnectorOutput.ToString();
}
public void LoadSingleRowWithJoin() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId, MerchantConfiguration.Url, MerchantConfiguration.IsEnabled, MerchantConfiguration.OptionalId
FROM Merchant
INNER JOIN MerchantConfiguration ON MerchantConfiguration.MerchantId = Merchant.MerchantId
WHERE Merchant.MerchantId IN (2, 3)
ORDER BY Merchant.MerchantId ASC;";
IEnumerable<MerchantData> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.OnOutput += databaseConnector_OnOutput;
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query);
}
string output = this.ConnectorOutput.ToString();
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Count());
Assert.AreEqual("Another Merchant", actual.ElementAt(0).Name);
Assert.AreEqual(2, actual.ElementAt(0).MerchantId);
Assert.AreEqual("http://www.anothermerchant.com.br", actual.ElementAt(0).Url);
Assert.IsFalse(actual.ElementAt(0).IsEnabled);
Assert.AreEqual("Merchant Test", actual.ElementAt(1).Name);
Assert.AreEqual(3, actual.ElementAt(1).MerchantId);
Assert.IsNull(actual.ElementAt(1).Url);
Assert.IsTrue(actual.ElementAt(1).IsEnabled);
Assert.AreEqual(13, actual.ElementAt(1).OptionalId);
}
public void ParseIntValueToBooleanProperty() {
string query = @"SELECT Merchant.MerchantId FROM Merchant;";
IEnumerable<MerchantBool> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantBool>(query);
}
Assert.IsNotNull(actual);
Assert.AreEqual(3, actual.Count());
Assert.IsFalse(actual.Any(p => p.MerchantId == false));
}
public void LoadSingleRowUsingQueryWithComment() {
string query = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Campos a serem retornados.
SELECT Merchant.Name, Merchant.MerchantId
FROM Merchant
WHERE Merchant.MerchantId = 1;";
MerchantData actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query).FirstOrDefault();
}
Assert.IsNotNull(actual);
Assert.AreEqual(1, actual.MerchantId);
}
public void LoadMerchantsAndRollbackWithLocalTransaction() {
string query = @"SELECT Merchant.Name FROM Merchant WHERE MerchantId = @MerchantId;";
MerchantData actual = null;
DatabaseConnector databaseConnector = new DatabaseConnector(connectionString);
try {
// Inicializa uma transação de banco de dados.
databaseConnector.BeginTransaction();
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { MerchantId = 1 }).FirstOrDefault();
Assert.IsNotNull(actual);
Assert.AreEqual("Merchant Number One", actual.Name);
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { MerchantId = 2 }).FirstOrDefault();
Assert.IsNotNull(actual);
Assert.AreEqual("Another Merchant", actual.Name);
}
finally {
databaseConnector.Rollback();
databaseConnector.Close();
}
}
public void LoadSingleRowWithInvalidEnumProperty() {
string query = @"SELECT Merchant.Name, Merchant.Status FROM Merchant WHERE Merchant.MerchantId = 3;";
MerchantData actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query).FirstOrDefault();
}
}
public void MapNonEntityClass() {
string query = @"SELECT m.Name AS MistypedName, m.MerchantId AS MistypedMerchantId,
mc.Url AS 'SubProperty.Address', mc.IsEnabled AS 'SubProperty.AnotherSubProperty.IsEnabled'
FROM Merchant m
INNER JOIN MerchantConfiguration mc ON mc.MerchantId = m.MerchantId
WHERE m.MerchantId = 1;";
MistypedClass result = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
result = databaseConnector.ExecuteReaderFetchAll<MistypedClass>(query).FirstOrDefault();
}
Assert.IsNotNull(result);
}
public void UntrustedColumnNameWithJoin() {
string query = @"SELECT sr.Id, sr.Name, sr.SimpleTableId FROM SimpleTableRelationship AS sr INNER JOIN SimpleTable s ON s.Id = sr.SimpleTableId WHERE sr.Name LIKE 'Main configuration'";
SimpleTableRelationship result = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
result = databaseConnector.ExecuteReaderFetchAll<SimpleTableRelationship>(query).FirstOrDefault();
}
Assert.IsNotNull(result);
Assert.AreEqual(result.Id, 2);
Assert.AreEqual(result.SimpleTableId, 1);
Assert.AreEqual(result.Name, "Main configuration");
}
public void LoadMerchantsWithInClause() {
string query = @"SELECT Name FROM Merchant WHERE MerchantKey IN (@MerchantKeyCollection)";
IEnumerable<Guid> merchantKeyCollection = new Guid[] { Guid.Parse("fee2437e-c810-4c2b-a836-5f619f80bb76"), Guid.Parse("c5d66001-8a39-4a04-a22c-d3a190018c46") };
IEnumerable<MerchantData> actual;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.OnOutput += databaseConnector_OnOutput;
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { MerchantKeyCollection = merchantKeyCollection });
}
string output = this.ConnectorOutput.ToString();
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Count());
Assert.IsTrue(actual.Any(p => p.Name.Equals("Merchant Number One")));
Assert.IsTrue(actual.Any(p => p.Name.Equals("Merchant Test")));
}
public void LoadMerchantDataForComposedClass() {
string query = @"SELECT m.Name,
m.MerchantId,
mc.Url,
mc.IsEnabled
FROM Merchant m
INNER JOIN MerchantConfiguration mc ON mc.MerchantId = m.MerchantId
WHERE m.MerchantId = @MerchantId";
ComposedMerchant actual;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<ComposedMerchant>(query, new { MerchantId = 1 }).FirstOrDefault();
}
Assert.IsNotNull(actual);
}
public void LoadSimpleDateTimes() {
string query = @"SELECT CreateDate FROM Merchant;";
IEnumerable<DateTime> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<DateTime>(query);
}
Assert.IsNotNull(actual);
Assert.AreEqual(3, actual.Count());
}
public void LoadSimpleTypeKeys() {
string query = @"SELECT MerchantKey FROM Merchant;";
IEnumerable<Guid> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<Guid>(query);
}
Assert.IsNotNull(actual);
Assert.AreEqual(3, actual.Count());
Assert.IsTrue(actual.Any(p => p == Guid.Parse("fee2437e-c810-4c2b-a836-5f619f80bb76")));
Assert.IsTrue(actual.Any(p => p == Guid.Parse("eb04aaaa-e8ca-4e14-8068-0f3008a716b9")));
Assert.IsTrue(actual.Any(p => p == Guid.Parse("c5d66001-8a39-4a04-a22c-d3a190018c46")));
}
public void LoadSimpleTypeIds() {
string query = @"SELECT MerchantId FROM Merchant;";
IEnumerable<int> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<int>(query);
}
Assert.IsNotNull(actual);
Assert.AreEqual(3, actual.Count());
Assert.IsTrue(actual.Any(p => p == 1));
Assert.IsTrue(actual.Any(p => p == 2));
Assert.IsTrue(actual.Any(p => p == 3));
}
public void LoadRowWithEmptyQuery() {
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.ExecuteReaderFetchAll<int>(string.Empty);
}
}
public void LoadSubProperties() {
string query = @"SELECT Id, AddressId AS 'Address.Id', ContactId AS 'Contact.Id', Name AS 'Contact.Name' FROM Lead WHERE Id = @LeadId;";
Lead result = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
result = databaseConnector.ExecuteReaderFetchAll<Lead>(query, new { @LeadId = 1 }).FirstOrDefault();
}
}
public void LoadSingleRowWithNestedEntity() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId, MerchantConfiguration.IsEnabled, MerchantConfiguration.Url
FROM Merchant
INNER JOIN MerchantConfiguration ON MerchantConfiguration.MerchantId = Merchant.MerchantId
WHERE Merchant.MerchantId = 1";
MerchantEntity actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.OnOutput += databaseConnector_OnOutput;
actual = databaseConnector.ExecuteReaderFetchAll<MerchantEntity>(query).FirstOrDefault();
}
string output = this.ConnectorOutput.ToString();
Assert.IsNotNull(actual);
Assert.IsNotNull(actual.MerchantConfiguration);
Assert.AreEqual("Merchant Number One", actual.Name);
Assert.AreEqual(1, actual.MerchantId);
Assert.IsTrue(actual.MerchantConfiguration.IsEnabled);
Assert.AreEqual("http://www.merchantnumberone.com.br", actual.MerchantConfiguration.Url);
}
public void LoadSingleRow() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId, Merchant.CreateDate, Merchant.MerchantKey FROM Merchant WHERE MerchantId = 1;";
MerchantData actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query).FirstOrDefault();
}
Assert.IsNotNull(actual);
Assert.AreEqual("Merchant Number One", actual.Name);
Assert.AreEqual(1, actual.MerchantId);
Assert.AreEqual(Guid.Parse("fee2437e-c810-4c2b-a836-5f619f80bb76"), actual.MerchantKey);
Assert.AreEqual("2014-07-30 13:06:10", actual.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
}
public void LoadMultipleRowsWithEnumProperty() {
string query = @"SELECT Merchant.Name, Merchant.Status FROM Merchant WHERE Merchant.MerchantId IN (1, 2) ORDER BY Merchant.MerchantId;";
IEnumerable<MerchantData> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query);
}
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Count());
Assert.AreEqual(StatusType.Created, actual.ElementAt(0).Status);
Assert.AreEqual(StatusType.Disabled, actual.ElementAt(1).Status);
}
public void LoadMerchantByEntityId_Test() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId, Merchant.CreateDate, Merchant.MerchantKey FROM Merchant WHERE MerchantId = @MerchantId;";
MerchantData request = new MerchantData();
request.MerchantId = 1;
MerchantData actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
databaseConnector.OnOutput += databaseConnector_OnOutput;
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, request).FirstOrDefault();
}
string output = this.ConnectorOutput.ToString();
Assert.IsNotNull(actual);
Assert.AreEqual("Merchant Number One", actual.Name);
Assert.AreEqual(1, actual.MerchantId);
Assert.AreEqual(Guid.Parse("fee2437e-c810-4c2b-a836-5f619f80bb76"), actual.MerchantKey);
Assert.AreEqual("2014-07-30 13:06:10", actual.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
}
public void LoadSingleRowWithEnumParameter() {
string query = @"SELECT Merchant.Name FROM Merchant WHERE Merchant.Status = @Status;";
MerchantData actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { Status = StatusType.Disabled }).FirstOrDefault();
}
Assert.IsNotNull(actual);
Assert.AreEqual("Another Merchant", actual.Name);
}
public void LoadMultipleRows() {
string query = @"SELECT Merchant.Name, Merchant.MerchantId, Merchant.CreateDate, Merchant.MerchantKey FROM Merchant ORDER BY Merchant.MerchantId ASC;";
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
IEnumerable<MerchantData> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query);
}
stopwatch.Stop();
System.Diagnostics.Debug.WriteLine(string.Format("ELAPSED: {0}", stopwatch.ElapsedMilliseconds));
Assert.IsNotNull(actual);
Assert.AreEqual(3, actual.Count());
Assert.AreEqual("Merchant Number One", actual.ElementAt(0).Name);
Assert.AreEqual(1, actual.ElementAt(0).MerchantId);
Assert.AreEqual(Guid.Parse("fee2437e-c810-4c2b-a836-5f619f80bb76"), actual.ElementAt(0).MerchantKey);
Assert.AreEqual("2014-07-30 13:06:10", actual.ElementAt(0).CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual("Another Merchant", actual.ElementAt(1).Name);
Assert.AreEqual(2, actual.ElementAt(1).MerchantId);
Assert.AreEqual(Guid.Parse("eb04aaaa-e8ca-4e14-8068-0f3008a716b9"), actual.ElementAt(1).MerchantKey);
Assert.AreEqual("2014-07-30 13:06:47", actual.ElementAt(1).CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual("Merchant Test", actual.ElementAt(2).Name);
Assert.AreEqual(3, actual.ElementAt(2).MerchantId);
Assert.AreEqual(Guid.Parse("c5d66001-8a39-4a04-a22c-d3a190018c46"), actual.ElementAt(2).MerchantKey);
Assert.AreEqual("2014-07-30 13:07:07", actual.ElementAt(2).CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
}
public void SearchWithEmptyStringParameter() {
string query = @"SELECT Merchant.MerchantId FROM Merchant WHERE Merchant.Name = @Name;";
IEnumerable<MerchantData> actual = null;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<MerchantData>(query, new { Name = string.Empty });
}
Assert.IsNotNull(actual);
Assert.IsFalse(actual.Any());
}
public void LoadSingleRowWithColumnAlias() {
string query = @"SELECT Merchant.Name, MerchantConfiguration.Url AS 'Value', Merchant.CreateDate
FROM Merchant
INNER JOIN MerchantConfiguration ON MerchantConfiguration.MerchantId = Merchant.MerchantId
WHERE Merchant.MerchantId = 1;";
BulkData actual;
using (DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
actual = databaseConnector.ExecuteReaderFetchAll<BulkData>(query).FirstOrDefault();
}
Assert.IsNotNull(actual);
Assert.AreEqual("Merchant Number One", actual.Name);
Assert.AreEqual("http://www.merchantnumberone.com.br", actual.Value);
}