public IEnumerable <BookEM> GetBooks(BaseDataTableFilterEM filter, out int totalFiltered)
{
string spName = "USPGetBooks";
var order = filter.Order.First();
var column = filter.Columns[order.Column];
DynamicParameters sqlParams = new DynamicParameters();
sqlParams.Add("OrderBy", column.Name);
sqlParams.Add("Direction", order.Dir);
sqlParams.Add("Start", filter.Start);
sqlParams.Add("Length", filter.Length);
sqlParams.Add("TotalFiltered", DbType.Int32, direction: ParameterDirection.Output);
using (IDbConnection db = new SqlConnection(base.CurrentContext.DbConnection))
{
var books = db.Query <BookEM, AuthorEM, BookEM>(spName, (book, author) =>
{
if (author != null)
{
book.Authors.Add(author);
}
else
{
book.Authors = new List <AuthorEM>();
}
return(book);
}, sqlParams, null, true, splitOn: "AuthorId", null, CommandType.StoredProcedure);
var result = books.GroupBy(b => b.BookId).Select(g =>
{
var groupedPost = g.First();
if (groupedPost.Authors.Any())
{
groupedPost.Authors = g.Select(a => a.Authors.Single()).ToList();
}
return(groupedPost);
}).ToList();
totalFiltered = sqlParams.Get <int?>("@TotalFiltered") ?? 0;
return(result);
}
}
public IEnumerable <AuthorEM> GetAuthors(BaseDataTableFilterEM filter, out int totalFiltered)
{
string spName = "USPGetAuthors";
var order = filter.Order.First();
var column = filter.Columns[order.Column];
DynamicParameters sqlParams = new DynamicParameters();
sqlParams.Add("OrderBy", column.Name);
sqlParams.Add("Direction", order.Dir);
sqlParams.Add("Start", filter.Start);
sqlParams.Add("Length", filter.Length);
sqlParams.Add("TotalFiltered", DbType.Int32, direction: ParameterDirection.Output);
using (IDbConnection db = new SqlConnection(base.CurrentContext.DbConnection))
{
var result = db.Query <AuthorEM>(spName, sqlParams, null, true, null, CommandType.StoredProcedure);
totalFiltered = sqlParams.Get <int?>("@TotalFiltered") ?? 0;
return(result);
}
}
