public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
List <Todo> todos;
using (IDbConnection con = new SqlConnection(_connectionString))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (show == TodoShow.Completed)
{
todos = (await con.QueryAsync <Todo>("GetCompleted")).ToList();
}
else if (show == TodoShow.Pending)
{
todos = (await con.QueryAsync <Todo>("GetPending")).ToList();
}
else
{
todos = (await con.QueryAsync <Todo>("GetAllTodos")).ToList();
}
}
return(todos);
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
var todos = new List <Todo>();
int totalCount;
using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
var offset = (page - 1) * pageSize;
string sql;
SqlCommand command;
if (show == TodoShow.All)
{
command = new SqlCommand("Select COUNT(*) From [dbo].[Todo]", connection);
totalCount = (int)await command.ExecuteScalarAsync();
command = new SqlCommand(
"Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo ORDER BY CreatedAt " +
$"OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY", connection);
}
else
{
command = new SqlCommand("Select COUNT(*) From [dbo].[Todo] Where Completed=@Completed",
connection);
command.Parameters.AddWithValue("Completed", show == TodoShow.Completed ? true : false);
totalCount = (int)await command.ExecuteScalarAsync();
command = new SqlCommand(
"Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo Where Completed = @Completed ORDER BY CreatedAt " +
$"OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY",
connection);
command.Parameters.Add(new SqlParameter("Completed", show == TodoShow.Completed ? true : false));
}
using (var dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
var todo = new Todo();
todo.Id = Convert.ToInt32(dataReader["Id"]);
todo.Title = Convert.ToString(dataReader["Title"]);
todo.Completed = Convert.ToBoolean(dataReader["Completed"]);
todo.CreatedAt = Convert.ToDateTime(dataReader["CreatedAt"]);
todo.UpdatedAt = Convert.ToDateTime(dataReader["UpdatedAt"]);
todos.Add(todo);
}
}
connection.Close();
}
return(Tuple.Create(totalCount, todos));
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
// Retrieve hwo many articles with our criteria(All, Completed or Pending)
IQueryable <Todo> queryable = _dbContext.Todos.OrderBy(t => t.CreatedAt);
int offset = (page - 1) * pageSize;
if (show == TodoShow.Completed)
{
queryable = queryable.Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = queryable.Where(t => !t.Completed);
}
queryable = queryable.Skip(offset).Take(pageSize);
int totalCount;
List <Todo> todos;
if (show != TodoShow.All)
{
totalCount = await queryable.CountAsync();
// https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query
// for complete/pending
todos = (await queryable
.Select(t => new // let's create an anonymous type
{
// t.Id is the same as Id = t.Id
t.Id,
t.Title,
t.Completed,
t.CreatedAt,
t.UpdatedAt
})
.ToListAsync())
.Select(anonymousType => new Todo
{
Id = anonymousType.Id,
Title = anonymousType.Title,
Completed = anonymousType.Completed,
CreatedAt = anonymousType.CreatedAt,
UpdatedAt = anonymousType.UpdatedAt
})
.ToList();
}
else
{
totalCount = await _dbContext.Todos.CountAsync();
todos = await queryable.ToListAsync();
}
return(Tuple.Create(totalCount, todos));
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
List <Todo> todos = new List <Todo>();
int totalCount = 0;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
SqlCommand command;
if (show == TodoShow.All)
{
command = new SqlCommand("GetAllTodosCount", connection);
totalCount = (int)await command.ExecuteScalarAsync();
command = new SqlCommand("GetAllTodosWithPagination", connection);
}
else
{
command = show == TodoShow.Pending
? new SqlCommand("GetAllPendingTodosCount", connection)
: new SqlCommand("GetAllCompletedTodosCount", connection);
totalCount = (int)await command.ExecuteScalarAsync();
command = show == TodoShow.Pending
? new SqlCommand("GetPendingWithPagination", connection)
: new SqlCommand("GetCompletedWithPagination", connection);
}
command.Parameters.AddWithValue("@Page", page);
command.Parameters.AddWithValue("@PageSize", pageSize);
command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
Todo todo = new Todo();
todo.Id = Convert.ToInt32(dataReader["Id"]);
todo.Title = Convert.ToString(dataReader["Title"]);
todo.Completed = Convert.ToBoolean(dataReader["Completed"]);
todo.CreatedAt = Convert.ToDateTime(dataReader["CreatedAt"]);
todo.UpdatedAt = Convert.ToDateTime(dataReader["UpdatedAt"]);
todos.Add(todo);
}
}
connection.Close();
}
return(Tuple.Create(totalCount, todos));
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
using (var session = _sessionFactoryBuilder.GetSessionFactory().OpenSession())
{
var offset = (page - 1) * pageSize;
IQueryable <Todo> queryable = null;
if (show == TodoShow.Completed)
{
queryable = session.Query <Todo>().Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = session.Query <Todo>().Where(t => !t.Completed);
}
int totalCount;
List <Todo> todos;
if (queryable != null)
{
totalCount = await queryable.CountAsync();
todos = await queryable.Skip(offset).Take(pageSize).Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
else
{
totalCount = await session.Query <Todo>().CountAsync();
todos = await session.Query <Todo>().Skip(offset).Take(pageSize).Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
return(Tuple.Create(totalCount, todos));
}
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
List <Todo> todos = new List <Todo>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
string sql;
SqlCommand command;
if (show == TodoShow.All)
{
command = new SqlCommand("Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo", connection);
}
else
{
command = new SqlCommand(
"Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo Where Completed = @Completed",
connection);
command.Parameters.Add(new SqlParameter("@Completed", show == TodoShow.Completed ? true : false));
}
using (SqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
Todo todo = new Todo();
todo.Id = Convert.ToInt32(dataReader["Id"]);
todo.Title = Convert.ToString(dataReader["Title"]);
todo.Completed = Convert.ToBoolean(dataReader["Completed"]);
todo.CreatedAt = Convert.ToDateTime(dataReader["CreatedAt"]);
todo.UpdatedAt = Convert.ToDateTime(dataReader["UpdatedAt"]);
todos.Add(todo);
}
}
connection.Close();
}
return(todos);
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
// Retrieve hwo many articles with our criteria(All, Completed or Pending)
var offset = (page - 1) * pageSize;
IQueryable <Todo> queryable = null;
if (show == TodoShow.Completed)
{
queryable = _context.Todos.Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = _context.Todos.Where(t => !t.Completed);
}
int totalCount;
List <Todo> todos;
if (queryable != null)
{
// for complete/pending
totalCount = await queryable.CountAsync();
todos = await queryable.Skip(offset).Take(pageSize).Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Description = t.Description,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
else
{
// for show all
totalCount = await _context.Todos.CountAsync();
todos = await _context.Todos.Skip(offset).Take(pageSize).ToListAsync();
}
return(Tuple.Create(totalCount, todos));
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
List <Todo> todos = new List <Todo>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
SqlCommand command;
if (show == TodoShow.All)
{
command = new SqlCommand("GetAllTodos", connection);
}
else
{
command = show == TodoShow.Pending
? new SqlCommand("GetPending", connection)
: new SqlCommand("GetCompleted", connection);
}
command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
Todo todo = new Todo();
todo.Id = Convert.ToInt32(dataReader["Id"]);
todo.Title = Convert.ToString(dataReader["Title"]);
todo.Completed = Convert.ToBoolean(dataReader["Completed"]);
todo.CreatedAt = Convert.ToDateTime(dataReader["CreatedAt"]);
todo.UpdatedAt = Convert.ToDateTime(dataReader["UpdatedAt"]);
todos.Add(todo);
}
}
connection.Close();
}
return(todos);
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
using (var session = _sessionFactoryBuilder.GetSessionFactory().OpenSession())
{
IQueryable <Todo> queryable = null;
if (show == TodoShow.Completed)
{
queryable = session.Query <Todo>().Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = session.Query <Todo>().Where(t => !t.Completed);
}
List <Todo> todos;
if (queryable != null)
{
todos = await queryable.Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
else
{
todos = await session.Query <Todo>().Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
return(todos);
}
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
using (var session = _sessionFactoryBuilder.GetSessionFactory().OpenSession())
{
// Retrieve hwo many articles with our criteria(All, Completed or Pending)
IQueryable <Todo> queryable = session.Query <Todo>().OrderBy(t => t.CreatedAt);
if (show == TodoShow.Completed)
{
queryable = queryable.Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = queryable.Where(t => !t.Completed);
}
List <Todo> todos;
if (show != TodoShow.All)
{
// https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query
// for complete/pending
todos = await queryable
.Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
})
.ToListAsync();
}
else
{
todos = await queryable.ToListAsync();
}
return(todos);
}
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
IQueryable <Todo> queryable = null;
if (show == TodoShow.Completed)
{
queryable = _context.Todos.Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = _context.Todos.Where(t => !t.Completed);
}
List <Todo> todos;
if (queryable != null)
{
todos = await queryable.Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
else
{
todos = await _context.Todos.Select(t => new Todo
{
Id = t.Id,
Title = t.Title,
Completed = t.Completed,
CreatedAt = t.CreatedAt,
UpdatedAt = t.UpdatedAt
}).ToListAsync();
}
return(todos);
}
public async Task <List <Todo> > FetchManyVulnerable(TodoShow show = TodoShow.All)
{
List <Todo> todos = new List <Todo>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
string sql;
if (show == TodoShow.All)
{
sql = "Select * From Todo";
}
else
{
sql = $"Select * From Todo Where Completed={(int) show}";
}
SqlCommand command = new SqlCommand(sql, connection);
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Todo todo = new Todo();
todo.Id = Convert.ToInt32(dataReader["Id"]);
todo.Title = Convert.ToString(dataReader["Title"]);
todo.Description = Convert.ToString(dataReader["Description"]);
todo.Completed = Convert.ToBoolean(dataReader["Completed"]);
todo.CreatedAt = Convert.ToDateTime(dataReader["CreatedAt"]);
todo.UpdatedAt = Convert.ToDateTime(dataReader["UpdatedAt"]);
todos.Add(todo);
}
}
connection.Close();
}
return(todos);
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
using (var conn = Connection)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sql;
if (show == TodoShow.Completed || show == TodoShow.Pending)
{
sql = "Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo Where " +
$"Completed={(show == TodoShow.Completed ? 1 : 0)} Order By CreatedAt";
}
else
{
sql = "Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo Order By CreatedAt";
}
var todos = (await conn.QueryAsync <Todo>(sql)).ToList();
return(todos);
}
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page = 1, int pageSize = 5,
TodoShow show = TodoShow.All)
{
using (var conn = Connection)
{
var offset = (page - 1) * pageSize;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var todos = new List <Todo>();
int totalCount;
if (show == TodoShow.All)
{
totalCount = (int)await conn.ExecuteScalarAsync("Select COUNT(*) From [dbo].[Todo]");
var reader = await conn.ExecuteReaderAsync(
"Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo ORDER BY CreatedAt " +
"OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY", new
{
Offset = offset, PageSize = pageSize
});
while (reader.Read())
{
todos.Add(new Todo
{
Id = reader.GetInt32(0),
Title = reader.GetString(1),
Completed = reader.GetBoolean(2),
CreatedAt = reader.GetDateTime(3),
UpdatedAt = reader.GetDateTime(4)
});
}
// Or
/*
* todos.Add(new Todo
* {
* Id = Convert.ToInt32(reader["Id"]),
* Title = Convert.ToString(reader["title"]),
* Completed = Convert.ToBoolean(reader["Completed"]),
* CreatedAt = Convert.ToDateTime(reader["CreatedAt"]),
* UpdatedAt = Convert.ToDateTime(reader["UpdatedAt"]),
* });
*/
return(Tuple.Create(totalCount, todos));
}
var parameters = new DynamicParameters();
parameters.Add("@Completed", show == TodoShow.Completed);
totalCount = await conn.ExecuteScalarAsync <int>(
"Select COUNT(*) From [dbo].[Todo] Where Completed=@Completed", parameters);
todos = (await conn.QueryAsync <Todo>(
"Select Id, Title, Completed, CreatedAt, UpdatedAt From Todo Where Completed = @Completed ORDER BY CreatedAt " +
$"OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY", parameters)).ToList();
return(Tuple.Create(totalCount, todos));
}
}
public async Task <Tuple <int, List <Todo> > > FetchMany(int page, int pageSize, TodoShow show = TodoShow.All)
{
using (IDbConnection con = new SqlConnection(_connectionString))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
List <Todo> todos;
if (show == TodoShow.All)
{
var totalCount = await con.ExecuteScalarAsync <int>("GetAllTodosCount",
commandType : CommandType.StoredProcedure);
todos = (await con.QueryAsync <Todo>("GetAllTodosWithPagination", new
{
Page = page, PageSize = pageSize
}, commandType: CommandType.StoredProcedure)).ToList();
return(Tuple.Create(totalCount, todos));
}
else
{
var parameters = new DynamicParameters();
parameters.Add("@Page", page);
parameters.Add("@PageSize", pageSize);
var totalCount = show == TodoShow.Completed
? await con.ExecuteScalarAsync <int>("GetAllCompletedTodosCount")
: await con.ExecuteScalarAsync <int>("GetAllPendingTodosCount");
todos = show == TodoShow.Completed
? (await con.QueryAsync <Todo>("GetCompletedWithPagination", parameters,
commandType: CommandType.StoredProcedure)).ToList()
: (await con.QueryAsync <Todo>("GetPendingWithPagination", parameters,
commandType: CommandType.StoredProcedure)).ToList();
return(Tuple.Create(totalCount, todos));
}
}
}
public async Task <List <Todo> > FetchMany(TodoShow show = TodoShow.All)
{
using (ApplicationDbContext dbContext = new ApplicationDbContext())
{
/* .Select(t => new Todo
* {
* Id = t.Id,
* Title = t.Title,
* Description = t.Description,
* Completed = t.Completed,
* CreatedAt = t.CreatedAt,
* UpdatedAt = t.UpdatedAt
* })
*/
// Retrieve hwo many articles with our criteria(All, Completed or Pending)
IQueryable <Todo> queryable = dbContext.Todos.OrderBy(t => t.CreatedAt);
if (show == TodoShow.Completed)
{
queryable = queryable.Where(t => t.Completed);
}
else if (show == TodoShow.Pending)
{
queryable = queryable.Where(t => !t.Completed);
}
List <Todo> todos;
if (show != TodoShow.All)
{
// https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query
// for complete/pending
todos = (await queryable
.Select(t => new // let's create an anonymous type
{
// t.Id is the same as Id = t.Id
t.Id,
t.Title,
t.Completed,
t.CreatedAt,
t.UpdatedAt
})
.ToListAsync())
.Select(anonymousType => new Todo
{
Id = anonymousType.Id,
Title = anonymousType.Title,
Completed = anonymousType.Completed,
CreatedAt = anonymousType.CreatedAt,
UpdatedAt = anonymousType.UpdatedAt
})
.ToList();
}
else
{
todos = await queryable.ToListAsync();
}
return(todos);
}
}
