ASP.NET MVC 5 + Entity Framework 5 – Manipulate the Data from Stored Procedure
Create SQL Table Statement |
CREATE PROCEDURE [dbo].[InventoryBySystem] @System nvarchar(100) AS BEGIN SELECT [ID] ,[Equipment] ,[Model] ,[SN] ,[Building] ,[Location] ,[Rack] ,[StartUnit] ,[EndUnit] ,[Status] ,[ItemStatus] ,[PO] ,[PODate] ,[Remarks] ,[System] FROM [dbo].[Inventory] WHERE [System] = @System ORDER BY [ID] END |
InventoryBySystem_Result.cs ( Project : InventoryDAO / Model Layer / generated from Entity Framework ) |
namespace InventoryDAO { using System; public partial class InventoryBySystem_Result { public int ID { get; set; } public string Equipment { get; set; } public string Model { get; set; } public string SN { get; set; } public string Building { get; set; } public string Location { get; set; } public string Rack { get; set; } public Nullable<double> StartUnit { get; set; } public Nullable<double> EndUnit { get; set; } public string Status { get; set; } public string ItemStatus { get; set; } public string PO { get; set; } public Nullable<System.DateTime> PODate { get; set; } public string Remarks { get; set; } public string System { get; set; } } } |
DAO\Inventory\InventoryItemDAO.cs ( Project : InventoryDAO / Business Logic Layer ) |
using System; using System.Collections.Generic; using System.Linq; namespace InventoryDAO.DAO.Inventory { public class InventoryItemDAO { private InventoryEntities entity; public InventoryItemDAO() { this.entity = new InventoryEntities(); } … … public List<InventoryDAO.InventoryBySystem_Result> getItemBySystem(string System) { return this.entity.InventoryBySystem(System).ToList(); } } } |
Controllers\InventoryController.cs ( Project : InventoryWeb / Controller Layer ) |
using System; using System.Collections.Generic; using System.Web.Mvc; namespace InventoryWeb.Controllers { public class InventoryController : Controller { private InventoryDAO.DAO.Inventory.InventoryItemDAO DAO; private InventoryDAO.DAO.Inventory.AuthDAO Auth; private InventoryDAO.DAO.Inventory.EventLogDAO Log; private string LoginName; private bool isAuth; public InventoryController() { this.DAO = new InventoryDAO.DAO.Inventory.InventoryItemDAO(); this.Auth = new InventoryDAO.DAO.Inventory.AuthDAO(); this.Log = new InventoryDAO.DAO.Inventory.EventLogDAO(); this.LoginName = System.Web.HttpContext.Current.User.Identity.Name; this.isAuth = this.Auth.isAuth(this.LoginName); } … … public ActionResult AccessDenied() { return View(); } … … public ActionResult ItemBySystem(string system) { List<InventoryDAO.InventoryBySystem_Result> list = this.DAO.getItemBySystem(system); if (this.isAuth && list != null && list.Count > 0) return View(list); else return RedirectToAction("Index"); } … … } } |