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); } }