/// <summary>
/// Creates a new instance of <see cref="BatchQueryRequest"/> object.
/// </summary>
/// <param name="type">The target type.</param>
/// <param name="connection">The connection object.</param>
/// <param name="transaction">The transaction object.</param>
/// <param name="fields">The list of the target fields.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of order fields.</param>
/// <param name="where">The query expression.</param>
/// <param name="hints">The hints for the table.</param>
/// <param name="statementBuilder">The statement builder.</param>
public BatchQueryRequest(Type type,
IDbConnection connection,
IDbTransaction transaction,
IEnumerable <Field> fields,
int page,
int rowsPerBatch,
IEnumerable <OrderField> orderBy,
QueryGroup where = null,
string hints = null,
IStatementBuilder statementBuilder = null)
: this(ClassMappedNameCache.Get(type, true, connection.GetDbSetting()),
connection,
transaction,
fields,
page,
rowsPerBatch,
orderBy,
where,
hints,
statementBuilder)
{
Type = type;
}
public void TestMySqlConnectionInsertViaTableNameAsExpandoObjectForNonIdentity()
{
// Setup
var table = Helper.CreateNonIdentityCompleteTablesAsExpandoObjects(1).First();
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Act
var result = connection.Insert(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
table);
// Assert
Assert.AreEqual(1, connection.CountAll <NonIdentityCompleteTable>());
Assert.IsTrue(Convert.ToInt64(result) > 0);
// Act
var queryResult = connection.Query <NonIdentityCompleteTable>(result);
// Assert
Assert.AreEqual(1, queryResult?.Count());
Helper.AssertMembersEquality(queryResult.First(), table);
}
}
public void TestSqlConnectionInsertAllAsyncForIdentityTableViaTableName()
{
// Setup
var tables = Helper.CreateDynamicIdentityTables(10);
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
connection.InsertAllAsync(ClassMappedNameCache.Get <IdentityTable>(),
tables).Wait();
// Act
var result = connection.QueryAll <IdentityTable>().AsList();
// Assert
Assert.AreEqual(tables.Count, result.Count);
tables.ForEach(table =>
{
var entity = result.FirstOrDefault(item => item.Id == table.Id);
Helper.AssertPropertiesEquality(table, entity);
});
}
}
public void TestSQLiteConnectionMergeAllAsyncAsDynamicsViaTableNameForIdentityForNonEmptyTable()
{
using (var connection = new SQLiteConnection(Database.ConnectionStringMDS))
{
// Setup
var tables = Database.CreateSdsCompleteTables(10, connection).AsList();
tables.ForEach(table => Helper.UpdateSdsCompleteTableProperties(table));
// Act
var result = connection.MergeAllAsync(ClassMappedNameCache.Get <SdsCompleteTable>(),
tables).Result;
// Assert
Assert.AreEqual(tables.Count, connection.CountAll <SdsCompleteTable>());
Assert.AreEqual(tables.Count, result);
// Act
var queryResult = connection.QueryAll <SdsCompleteTable>();
// Assert
tables.ForEach(table => Helper.AssertPropertiesEquality(table, queryResult.First(e => e.Id == table.Id)));
}
}
public void TestSqLiteConnectionUpdateAllAsyncAsExpandoObjectViaTableName()
{
using (var connection = new SqliteConnection(Database.ConnectionStringMDS))
{
// Setup
Database.CreateMdsCompleteTables(10, connection);
var tables = Helper.CreateMdsCompleteTablesAsExpandoObjects(10);
// Act
var result = connection.UpdateAllAsync(ClassMappedNameCache.Get <MdsCompleteTable>(),
tables).Result;
// Assert
Assert.AreEqual(10, result);
// Act
var queryResult = connection.QueryAll <MdsCompleteTable>();
// Assert
tables.AsList().ForEach(table =>
Helper.AssertMembersEquality(queryResult.First(e => e.Id == ((dynamic)table).Id), table));
}
}
public void TestSQLiteConnectionMergeAsyncViaTableNameForIdentityForNonEmptyTable()
{
using (var connection = new SQLiteConnection(Database.ConnectionStringMDS))
{
// Setup
var table = Database.CreateSdsCompleteTables(1, connection).First();
Helper.UpdateSdsCompleteTableProperties(table);
// Act
var result = connection.MergeAsync(ClassMappedNameCache.Get <SdsCompleteTable>(),
table).Result;
// Assert
Assert.AreEqual(1, connection.CountAll <SdsCompleteTable>());
Assert.AreEqual(table.Id, result);
// Act
var queryResult = connection.Query <SdsCompleteTable>(result);
// Assert
Helper.AssertPropertiesEquality(table, queryResult.First());
}
}
public void TestSQLiteConnectionMergeAsyncViaTableNameAsExpandoObjectForIdentityForNonEmptyTable()
{
using (var connection = new SQLiteConnection(Database.ConnectionStringMDS))
{
// Setup
Database.CreateSdsCompleteTables(1, connection).First();
var table = Helper.CreateSdsCompleteTablesAsExpandoObjects(1).First();
// Act
var result = connection.MergeAsync(ClassMappedNameCache.Get <SdsCompleteTable>(),
table).Result;
// Assert
Assert.AreEqual(1, connection.CountAll <SdsCompleteTable>());
Assert.AreEqual(((dynamic)table).Id, result);
// Act
var queryResult = connection.Query <SdsCompleteTable>(result);
// Assert
Helper.AssertMembersEquality(queryResult.First(), table);
}
}
public void TestMySqlConnectionMergeAllViaTableNameForNonIdentityForEmptyTable()
{
// Setup
var tables = Helper.CreateNonIdentityCompleteTables(10);
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Act
var result = connection.MergeAll(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
tables);
// Assert
Assert.AreEqual(tables.Count, connection.CountAll <NonIdentityCompleteTable>());
Assert.AreEqual(tables.Count, result);
// Act
var queryResult = connection.QueryAll <NonIdentityCompleteTable>();
// Assert
Assert.AreEqual(tables.Count, queryResult.Count());
tables.ForEach(table => Helper.AssertPropertiesEquality(table, queryResult.First(e => e.Id == table.Id)));
}
}
public void TestDbHelperForInsert()
{
// Prepare
var dbHelper = GetMockedDbHelper();
var connection = new DbHelperDbConnection();
// Act
CommandTextCache.Flush();
DbFieldCache.Flush();
DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true);
// Act
connection.Insert <DbHelperDataEntity>(new DbHelperDataEntity {
Id = 1, Name = "Name"
});
// Assert
dbHelper.Verify(builder =>
builder.GetFields(
It.Is <IDbConnection>(s => s == connection),
It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()),
It.IsAny <IDbTransaction>()), Times.Exactly(1));
}
public void TestPostgreSqlConnectionInsertViaTableNameAsDynamicForNonIdentity()
{
// Setup
var table = Helper.CreateNonIdentityCompleteTablesAsDynamics(1).First();
using (var connection = new NpgsqlConnection(Database.ConnectionString))
{
// Act
var result = connection.Insert(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
(object)table);
// Assert
Assert.AreEqual(1, connection.CountAll <NonIdentityCompleteTable>());
Assert.AreEqual(table.Id, result);
// Act
var queryResult = connection.Query <NonIdentityCompleteTable>(result);
// Assert
Assert.AreEqual(1, queryResult?.Count());
Helper.AssertMembersEquality(queryResult.First(), table);
}
}
public void TestPostgreSqlConnectionInsertViaTableNameAsyncForNonIdentity()
{
// Setup
var table = Helper.CreateNonIdentityCompleteTables(1).First();
using (var connection = new NpgsqlConnection(Database.ConnectionString))
{
// Act
var result = connection.InsertAsync(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
table).Result;
// Assert
Assert.AreEqual(1, connection.CountAll <NonIdentityCompleteTable>());
Assert.IsTrue(Convert.ToInt64(result) > 0);
// Act
var queryResult = connection.Query <NonIdentityCompleteTable>(result);
// Assert
Assert.AreEqual(1, queryResult?.Count());
Helper.AssertPropertiesEquality(table, queryResult.First());
}
}
/// <summary>
/// Creates a new instance of <see cref="QueryMultipleRequest"/> object.
/// </summary>
/// <param name="index">The index value.</param>
/// <param name="type">The target type.</param>
/// <param name="connection">The connection object.</param>
/// <param name="transaction">The transaction object.</param>
/// <param name="fields">The list of the target fields.</param>
/// <param name="where">The query expression.</param>
/// <param name="orderBy">The list of order fields.</param>
/// <param name="top">The filter for the rows.</param>
/// <param name="hints">The hints for the table.</param>
/// <param name="statementBuilder">The statement builder.</param>
public QueryMultipleRequest(int?index,
Type type,
IDbConnection connection,
IDbTransaction transaction,
IEnumerable <Field> fields = null,
QueryGroup where = null,
IEnumerable <OrderField> orderBy = null,
int?top = null,
string hints = null,
IStatementBuilder statementBuilder = null)
: this(index,
ClassMappedNameCache.Get(type),
connection,
transaction,
fields,
where,
orderBy,
top,
hints,
statementBuilder)
{
Type = type;
}
public void TestMicrosoftSqlConnectionDbRepositoryBulkDeleteAsyncForTableNameEntities()
{
// Setup
var tables = Helper.CreateBulkOperationIdentityTables(10);
using (var repository = new BulkOperationIdentityTableRepository())
{
// Act
repository.InsertAll(tables);
// Act
var bulkDeleteResult = repository.BulkDeleteAsync(ClassMappedNameCache.Get <BulkOperationIdentityTable>(), tables).Result;
// Assert
Assert.AreEqual(tables.Count, bulkDeleteResult);
// Act
var countResult = repository.CountAll();
// Assert
Assert.AreEqual(0, countResult);
}
}
public void TestMySqlConnectionUpdateAsyncViaTableNameViaQueryField()
{
// Setup
var table = Database.CreateCompleteTables(1).First();
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Setup
Helper.UpdateCompleteTableProperties(table);
// Act
var result = connection.UpdateAsync(ClassMappedNameCache.Get <CompleteTable>(), table, new QueryField("Id", table.Id)).Result;
// Assert
Assert.AreEqual(1, result);
// Act
var queryResult = connection.Query <CompleteTable>(table.Id).First();
// Assert
Helper.AssertPropertiesEquality(table, queryResult);
}
}
public void TestSqlConnectionInsertAsyncViaTableNameAsDynamicForIdentity()
{
// Setup
var table = Helper.CreateCompleteTablesAsDynamics(1).First();
using (var connection = new SqlConnection(Database.ConnectionString))
{
// Act
var result = connection.InsertAsync(ClassMappedNameCache.Get <CompleteTable>(),
(object)table).Result;
// Assert
Assert.AreEqual(1, connection.CountAll <CompleteTable>());
Assert.IsTrue(Convert.ToInt64(result) > 0);
// Act
var queryResult = connection.Query <CompleteTable>(result);
// Assert
Assert.AreEqual(1, queryResult?.Count());
Helper.AssertMembersEquality(queryResult.First(), table);
}
}
public void TestSqlConnectionInsertViaTableNameForNonIdentity()
{
// Setup
var table = Helper.CreateNonIdentityCompleteTables(1).First();
using (var connection = new SqlConnection(Database.ConnectionString))
{
// Act
var result = connection.Insert(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
table);
// Assert
Assert.AreEqual(1, connection.CountAll <NonIdentityCompleteTable>());
Assert.AreEqual(table.Id, result);
// Act
var queryResult = connection.Query <NonIdentityCompleteTable>(result);
// Assert
Assert.AreEqual(1, queryResult?.Count());
Helper.AssertPropertiesEquality(table, queryResult.First());
}
}
public void TestSqlConnectionExistsViaAsyncViaTableNameViaQueryFields()
{
// Setup
var tables = Helper.CreateIdentityTables(10);
var fields = new[]
{
new QueryField(nameof(IdentityTable.ColumnInt), Operation.GreaterThan, 5),
new QueryField(nameof(IdentityTable.ColumnInt), Operation.LessThanOrEqual, 8)
};
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
connection.InsertAll(tables);
// Act
var result = connection.ExistsAsync(ClassMappedNameCache.Get <IdentityTable>(),
fields).Result;
// Assert
Assert.IsTrue(result);
}
}
public void TestSqlConnectionExecuteNonQueryFromQueryBuilderCreateMerge()
{
// Setup
var table = Helper.CreateIdentityTables(1).First();
var fields = FieldCache.Get <IdentityTable>();
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
var id = connection.Insert(table);
// Set the properties
table.ColumnNVarChar = $"{table.ColumnNVarChar}-Merged";
// Setup
var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <IdentityTable>(), null);
var builder = connection.GetStatementBuilder();
var sql = builder.CreateMerge(null,
ClassMappedNameCache.Get <IdentityTable>(),
fields: fields,
qualifiers: fields.Where(f => dbFields.FirstOrDefault(df => (df.IsPrimary || df.IsIdentity) && df.Name == f.Name) != null),
primaryField: dbFields.FirstOrDefault(e => e.IsPrimary),
identityField: dbFields.FirstOrDefault(e => e.IsIdentity));
// Act
var affectedRow = connection.ExecuteNonQuery(sql, table);
// Assert
Assert.AreEqual(1, affectedRow);
// Setup
var result = connection.QueryAll <IdentityTable>().First();
// Assert
Helper.AssertPropertiesEquality(table, result);
}
}
public void TestMySqlConnectionInsertAllAsyncViaTableNameAsDynamicsForNonIdentity()
{
// Setup
var tables = Helper.CreateNonIdentityCompleteTablesAsDynamics(10);
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Act
var result = connection.InsertAllAsync(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
tables).Result;
// Assert
Assert.AreEqual(tables.Count(), result);
// Act
var queryResult = connection.QueryAll <NonIdentityCompleteTable>();
// Assert
tables.ForEach(table =>
{
Helper.AssertMembersEquality(table, queryResult.ElementAt((int)tables.IndexOf(table)));
});
}
}
public void TestMySqlConnectionMergeAllAsyncAsDynamicsViaTableNameForNonIdentityForNonEmptyTable()
{
// Setup
var tables = Database.CreateNonIdentityCompleteTables(10).AsList();
var entities = tables.Select(table => new
{
Id = table.Id,
ColumnInt = int.MaxValue
}).AsList();
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Act
var result = connection.MergeAllAsync(ClassMappedNameCache.Get <NonIdentityCompleteTable>(),
entities).Result;
// Act
var queryResult = connection.QueryAll <NonIdentityCompleteTable>();
// Assert
Assert.AreEqual(tables.Count(), queryResult.Count());
entities.ForEach(table => Assert.AreEqual(table.ColumnInt, queryResult.ElementAt((int)entities.IndexOf(table)).ColumnInt));
}
}
public void TestSqlConnectionDatesCrudViaTableNameAsync()
{
// Setup
var dateTime = new DateTime(1970, 1, 1, 12, 50, 30, DateTimeKind.Utc);
var dateTime2 = dateTime.AddMilliseconds(100);
var entity = new
{
SessionId = Guid.NewGuid(),
ColumnDate = dateTime.Date,
ColumnDateTime = dateTime,
ColumnDateTime2 = dateTime2,
ColumnSmallDateTime = dateTime,
ColumnDateTimeOffset = new DateTimeOffset(dateTime.Date).ToOffset(TimeSpan.FromHours(2)),
ColumnTime = dateTime.TimeOfDay
};
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act Insert
var insertResult = connection.InsertAsync(ClassMappedNameCache.Get <DatesClass>(), entity);
var id = insertResult.Result;
// Act Query
var queryResult = connection.QueryAsync(ClassMappedNameCache.Get <DatesClass>(), new { SessionId = (Guid)id });
var data = queryResult.Result.FirstOrDefault();
// Assert
Assert.IsNotNull(data);
Assert.AreEqual(entity.ColumnDate, data.ColumnDate);
Assert.AreEqual(entity.ColumnDateTime, data.ColumnDateTime);
Assert.AreEqual(entity.ColumnDateTime2, data.ColumnDateTime2);
Assert.AreEqual(dateTime.AddSeconds(30), data.ColumnSmallDateTime); // Always in a fraction of minutes, round (off/up)
Assert.AreEqual(entity.ColumnDateTimeOffset, data.ColumnDateTimeOffset);
Assert.AreEqual(entity.ColumnTime, data.ColumnTime);
}
}
public void TestMicrosoftSqlConnectionBulkMergeAsyncForTableNameDataEntitiesForEmptyTable()
{
// Setup
var tables = Helper.CreateBulkOperationIdentityTables(10);
using (var repository = new BulkOperationIdentityTableRepository())
{
// Act
var bulkMergeResult = repository.BulkMergeAsync(ClassMappedNameCache.Get <BulkOperationIdentityTable>(), tables).Result;
// Assert
Assert.AreEqual(tables.Count, bulkMergeResult);
// Act
var queryResult = repository.QueryAll();
// Assert
Assert.AreEqual(tables.Count, queryResult.Count());
tables.AsList().ForEach(t =>
{
Helper.AssertPropertiesEquality(t, queryResult.ElementAt(tables.IndexOf(t)));
});
}
}
public void TestSqlConnectionAverageAsyncTypedResultViaTableNameViaQueryFields()
{
// Setup
var tables = Helper.CreateIdentityTables(10);
var fields = new[]
{
new QueryField(nameof(IdentityTable.ColumnInt), Operation.GreaterThan, 5),
new QueryField(nameof(IdentityTable.ColumnInt), Operation.LessThanOrEqual, 8)
};
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
connection.InsertAll(tables);
// Act
var result = connection.AverageAsync <double?>(ClassMappedNameCache.Get <IdentityTable>(),
new Field("ColumnInt"),
fields).Result;
// Assert
Assert.AreEqual(tables.Where(t => t.ColumnInt > 5 && t.ColumnInt <= 8).Average(t => t.ColumnInt), result);
}
}
public void TestSqlServerConnectionUpdateAsyncViaTableNameViaExpandoObject()
{
// Setup
var table = Database.CreateCompleteTables(1).First();
using (var connection = new SqlConnection(Database.ConnectionString))
{
// Setup
var entity = Helper.CreateCompleteTablesAsExpandoObjects(1).First();
// Act
var result = connection.UpdateAsync(ClassMappedNameCache.Get <CompleteTable>(),
entity).Result;
// Assert
Assert.AreEqual(1, result);
// Act
var queryResult = connection.Query <CompleteTable>(table.Id).First();
// Assert
Helper.AssertPropertiesEquality(queryResult, entity);
}
}
public void TestSqlServerConnectionUpdateViaTableNameViaDataEntity()
{
// Setup
var table = Database.CreateCompleteTables(1).First();
using (var connection = new SqlConnection(Database.ConnectionString))
{
// Setup
Helper.UpdateCompleteTableProperties(table);
// Act
var result = connection.Update(ClassMappedNameCache.Get <CompleteTable>(),
table);
// Assert
Assert.AreEqual(1, result);
// Act
var queryResult = connection.Query <CompleteTable>(table.Id).First();
// Assert
Helper.AssertPropertiesEquality(table, queryResult);
}
}
public void TestSqlConnectionSpatialsNullCrudViaTableName()
{
// Setup
var entity = new
{
SessionId = Guid.NewGuid(),
ColumnGeography = (object)null,
ColumnGeometry = (object)null
};
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act Insert
var id = connection.Insert(ClassMappedNameCache.Get <SpatialsClass>(), entity);
// Act Query
var data = connection.Query(ClassMappedNameCache.Get <SpatialsClass>(), new { SessionId = (Guid)id }).FirstOrDefault();
// Assert
Assert.IsNotNull(data);
Assert.IsNull(data.ColumnGeography);
Assert.IsNull(data.ColumnGeometry);
}
}
public void TestSqlConnectionSpatialsCrudViaTableName()
{
// Setup
var entity = new
{
SessionId = Guid.NewGuid(),
ColumnGeography = "POLYGON ((0 0, 50 0, 50 50, 0 50, 0 0))",
ColumnGeometry = "LINESTRING (-122.36 47.656, -122.343 47.656)"
};
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act Insert
var id = connection.Insert(ClassMappedNameCache.Get <SpatialsClass>(), entity);
// Act Query
var data = connection.Query(ClassMappedNameCache.Get <SpatialsClass>(), new { SessionId = (Guid)id }).FirstOrDefault();
// Assert
Assert.IsNotNull(data);
Assert.AreEqual(entity.ColumnGeography.ToString(), data.ColumnGeography?.ToString());
Assert.AreEqual(entity.ColumnGeometry.ToString(), data.ColumnGeometry?.ToString());
}
}
public void TestDbHelperForInsert()
{
// Prepare
var dbHelper = new Mock <IDbHelper>();
var connection = new CustomDbConnectionForDbHelper();
// Act
CommandTextCache.Flush();
DbFieldCache.Flush();
StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true);
DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true);
// Act
connection.Insert <DataEntityForDbHelper>(new DataEntityForDbHelper {
Id = 1, Name = "Name"
});
// Assert
dbHelper.Verify(builder =>
builder.GetFields(
It.Is <IDbConnection>(s => s == connection),
It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()),
It.IsAny <IDbTransaction>()), Times.Exactly(1));
}
public void TestSqlConnectionCountViaTableNameViaQueryGroup()
{
// Setup
var tables = Helper.CreateIdentityTables(10);
var fields = new[]
{
new QueryField(nameof(IdentityTable.ColumnInt), Operation.GreaterThan, 5),
new QueryField(nameof(IdentityTable.ColumnInt), Operation.LessThanOrEqual, 8)
};
var queryGroup = new QueryGroup(fields);
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
connection.InsertAll(tables);
// Act
var result = connection.Count(ClassMappedNameCache.Get <IdentityTable>(),
queryGroup);
// Assert
Assert.AreEqual(3, result);
}
}
public void TestDbHelperForBatchQuery()
{
// Prepare
var dbHelper = GetMockedDbHelper();
var connection = new DbHelperDbConnection();
// Act
CommandTextCache.Flush();
DbFieldCache.Flush();
DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true);
// Act
connection.BatchQuery <DbHelperDataEntity>(0,
10,
OrderField.Ascending <DbHelperDataEntity>(e => e.Id).AsEnumerable(),
e => e.Id == 1);
// Assert
dbHelper.Verify(builder =>
builder.GetFields(
It.Is <IDbConnection>(s => s == connection),
It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()),
It.IsAny <IDbTransaction>()), Times.Exactly(1));
}
