public IEnumerable <Note> GetItemsByCategoryId(int categoryId, int pageNo = 1, int pageSize = 0)
{
try
{
int count = GetTotalCount();
if ((pageNo - 1) * pageSize > count)
{
throw new NotePageDoesntExistsException(pageNo);
}
using (IDbCommand command = _db.CreateCommand())
{
command.CommandText = $"SELECT * FROM {_viewName} WHERE CategoryId = {categoryId} ORDER BY Id";
if (pageSize > 0)
{
command.CommandText += $" OFFSET {(pageNo - 1) * pageSize} ROWS FETCH FIRST {pageSize} ROWS ONLY ";
}
List <Note> items = new List <Note>();
using (IDataReader reader = command.ExecuteReader())
{
DataTable table = reader.GetSchemaTable();
INotesDbMapper mapper = new NotesDbMapper(typeof(Note), table);
while (reader.Read())
{
items.Add(mapper.Map <Note>(reader));
}
}
return(items);
}
}
catch (Exception e)
{
throw new NoteDataException(e.Message);
}
}
public List <T> GetAll()
{
try
{
using (IDbCommand command = _db.CreateCommand())
{
command.CommandText = $"SELECT * FROM {_viewName}";
List <T> items = new List <T>();
using (IDataReader reader = command.ExecuteReader())
{
DataTable table = reader.GetSchemaTable();
INotesDbMapper mapper = new NotesDbMapper(typeof(T), table);
while (reader.Read())
{
items.Add(mapper.Map <T>(reader));
}
}
return(items);
}
}
catch (Exception e)
{
throw new NoteDataException(e.Message);
}
}