// GET: Default public ActionResult Index(string OrderBy, string OrderDirection = "ASC") { // Instantiate a repository class so you can start using data, since we're using SQL Repository, pass in either the // local file connection string or MsSql connection string, as the libraries they use are the same. SqlRepository repository = new SqlRepository(_LocalFileConnectionString); // Create a list of employees to return to the view based on our SQL statement // Basic select statement, however we do not get all the information we need. string sql = "SELECT * FROM Employee"; #region Bonus - Joining another table // TODO: Bonus - Joining another table // remember relational databased store additional data in other tables. If we join // our employee on the Department table and Position table then we get that information to display //string sql = "SELECT * FROM Employee E INNER JOIN Department D ON D.Id = E.DepartmentId INNER JOIN Position P ON P.Id = E.PositionId"; #endregion // TODO: How to we order the data by a column, enable sorting? ViewBag.EnableSorting = false; if (!String.IsNullOrEmpty(OrderBy)) { // sql += ?? // TODO: Bonus - How do we persist the OrderDirection? } List <Employee> allEmployees = repository.GetEmployees(sql); return(View(allEmployees)); }
// GET: Default public ActionResult Index(string OrderBy, string OrderDirection = "ASC") { SqlRepository repository = new SqlRepository(_LocalFileConnectionString); string sql = "SELECT * FROM Employee"; ViewBag.EnableSorting = true; if (!String.IsNullOrEmpty(OrderBy)) { sql += String.Format(" ORDER BY {0} {1}", OrderBy, OrderDirection); if (OrderDirection == "ASC") { _OrderDirection = "DESC"; } else { _OrderDirection = "ASC"; } ViewBag.OrderDirection = _OrderDirection; } List <Employee> allEmployees = repository.GetEmployees(sql); return(View(allEmployees)); }
// GET: Default public ActionResult Index(string OrderBy, string OrderDirection = "ASC") { // Instantiate a repository class so you can start using data, since we're using SQL Repository, pass in either the // local file connection string or MsSql connection string, as the libraries they use are the same. SqlRepository repository = new SqlRepository(_LocalFileConnectionString); // Create a list of employees to return to the view based on our SQL statement string sql = "SELECT E.*, D.DepartmentName, P.PositionName FROM Employee E "; sql += "INNER JOIN Department D ON D.Id = E.DepartmentId INNER JOIN Position P ON P.Id = E.PositionId"; ViewBag.EnableSorting = true; if (!String.IsNullOrEmpty(OrderBy)) { sql += " ORDER BY " + OrderBy + " " + OrderDirection; ViewBag.OrderDirection = OrderDirection == "ASC" ? "DESC" : "ASC"; ; } List <Employee> allEmployees = repository.GetEmployees(sql); return(View(allEmployees)); }