public IEnumerable <AccountOperation> Get(AccountOperationSearchCriteria searchCriteria)
{
IQueryable <AccountOperation> query = context.AccountOperations.AsNoTracking().AsQueryable();
if (!string.IsNullOrEmpty(searchCriteria.Account))
{
query = query.Where(p => p.Account.StartsWith(searchCriteria.Account));
}
if (searchCriteria.From.HasValue)
{
query = query.Where(p => p.OperationDate >= searchCriteria.From);
}
if (searchCriteria.To.HasValue)
{
query = query.Where(p => p.OperationDate >= searchCriteria.To);
}
//var results = context.AccountOperations.AsNoTracking()
// .Where(p => p.Account.StartsWith(searchCriteria.Account))
// .Where(p => p.OperationDate >= searchCriteria.From)
// .Where(p => p.OperationDate <= searchCriteria.To)
// .ToList();
return(query.ToList());
}
// FastMember
//public void BulkCopy(IEnumerable<AccountOperation> operations)
//{
// using (var bcp = new SqlBulkCopy(connection))
// using (var reader = ObjectReader.Create(operations, "Id", "Account", "OperationDate"))
// {
// bcp.DestinationTableName = "Santander.AccountOperations";
// bcp.BatchSize = 500;
// bcp.WriteToServer(reader);
// }
//}
public IEnumerable <AccountOperation> Get(AccountOperationSearchCriteria searchCriteria)
{
string sql = "SELECT AccountOperationId, Account, OperationDate, OperationTypeId, OperationStatus FROM Santander.AccountOperations WHERE ";
if (!string.IsNullOrEmpty(searchCriteria.Account))
{
sql += "Account like @Account + '%'";
}
if (searchCriteria.From.HasValue && searchCriteria.To.HasValue)
{
sql += "AND OperationDate BETWEEN @From AND @To";
}
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Account", searchCriteria.Account);
command.Parameters.AddWithValue("@From", searchCriteria.From);
command.Parameters.AddWithValue("@To", searchCriteria.To);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
ICollection <AccountOperation> accountOperations = new List <AccountOperation>();
while (reader.Read())
{
yield return(Map(reader));
}
connection.Close();
}
private static void GetAccountOperationsTest()
{
string connectionString = GetConnectionString("SULMAR-PC", "AdventureWorks", applicationName: "Santander");
SqlConnection connection = new SqlConnection(connectionString);
IAccountOperationService accountOperationService = new DbAccountOperationService(connection);
AccountOperationSearchCriteria criteria = new AccountOperationSearchCriteria
{
Account = "12348",
From = DateTime.Parse("2019-05-01"),
To = DateTime.Parse("2019-12-01"),
};
var accountOperations = accountOperationService.Get(criteria);
foreach (var operation in accountOperations)
{
// Console.WriteLine(operation.Account);
}
}