C#

 

  • Using Microsoft SQL Server Management Studio, create a database on your SQL Server and name it itemsCatalog
  • Create a table with the following fields and name it "items"
    Column Name Data Type  Allow Nulls Remarks
    id bigint no Identity Specification: Yes
    Is Identity: Yes
     itemName varchar(500) no  
     itemDescription nvarchar(500)  no  
  • Click on the "itemsCatalog" database and click on New Query button located in the toolbar.
  • Each created stored procedures will be visible on Programability\Stored Procedures tree.
    • Use the following codes to Add new records on items table
      ALTER procedure [dbo].[items_list]
          @id bigint = 0
      as
      begin try
          if (not exists (select id from items where id = @id)
              and (@id > 0) ) begin
              raiserror('sp|no records found', 11, 1)
          end
      select    id, itemName, itemDescription
      from    items
      where    (id = @id or @id = 0)
      end try
      begin catch
          declare @errorMessage nvarchar(max), @errorSeverity int, @errorState int
          select    @errorMessage = ERROR_MESSAGE(),
                  @errorSeverity = ERROR_SEVERITY(),
                  @errorState = ERROR_STATE()
          raiserror(@errorMessage, @errorSeverity, @errorState)
      end catch
    • Use the following codes to Delete records from items table
      ALTER procedure [dbo].[items_delete]
          @id bigint
      as
      begin try
          if not exists (select id from items where id = @id) begin
              raiserror('sp|no records found', 11, 1)
          end
          declare @itemName varchar(500)
          select @itemName = itemName from items where id = @id
          delete from items where id = @id
          select @itemName as itemName
      end try
      begin catch
          declare @errorMessage nvarchar(max), @errorSeverity int, @errorState int
          select    @errorMessage = ERROR_MESSAGE(),
                  @errorSeverity = ERROR_SEVERITY(),
                  @errorState = ERROR_STATE()
          raiserror(@errorMessage, @errorSeverity, @errorState)
      end catch
    • Use the following codes to List records from items table. If the parameter is 0, this stored procedure will list all records, else it will look for a specified record. If the specified record does not exist, the stored procedure will return an error
      ALTER procedure [dbo].[items_list]
          @id bigint = 0
      as
      begin try
          if (not exists (select id from items where id = @id)
              and (@id > 0) ) begin
              raiserror('sp|no records found', 11, 1)
          end
      select    id, itemName, itemDescription
      from    items
      where    (id = @id or @id = 0)
      end try
      begin catch
          declare @errorMessage nvarchar(max), @errorSeverity int, @errorState int
          select    @errorMessage = ERROR_MESSAGE(),
                  @errorSeverity = ERROR_SEVERITY(),
                  @errorState = ERROR_STATE()
          raiserror(@errorMessage, @errorSeverity, @errorState)
      end catch
    • Use the following codes to Update records from items table. To ensure that there will be no duplicate records, the stored procedure will if the new itemName exists on the database. It will not return an error if the old and new name matches.
      ALTER procedure [dbo].[items_update]
          @id bigint,
          @itemName varchar(500),
          @itemDescription nvarchar(max)    
      as
      begin try
          if not exists (select id from items where id = @id) begin
              raiserror('sp|no records found', 11, 1)
          end
          declare @oldItemName varchar(500)
          select @oldItemName = itemName from items where id = @id
          
          if (@oldItemName <> @itemName) begin
              if exists (select id from items where upper(itemName) = upper( @itemName)) begin
                  raiserror('sp|item already exists', 11, 1)
              end
          end
          update    items
          set        itemName = @itemName,
                  itemDescription = @itemDescription
          where    id = @id
      end try
      begin catch
          declare @errorMessage nvarchar(max), @errorSeverity int, @errorState int
          select    @errorMessage = ERROR_MESSAGE(),
                  @errorSeverity = ERROR_SEVERITY(),
                  @errorState = ERROR_STATE()
          raiserror(@errorMessage, @errorSeverity, @errorState)
      end catch
    • Now we're done with the database, let's proceed with the coding. Create a new project in Visual Studio, under C# -> Web, choose ASP.NET Web Application (.net Framework)
    • Create an Empty project, and check the MVC
    • Add ADO.NET Entity Data Model. It is recomended to use the database name as item name. Well name it "itemsCatalog".
    • Choose EF Designer from database.
    • Add new connection, specifiy the server name and database name. The entity name will be itemsCatalogEntities, it is auto generated but we can leave it as is.
    • Choose Entity Framework 6.x or 5.x depending on your server. You may contact your webhost to know which version they support.
    • Under Stored Procedures and Functions\dbo, check all stored procedures we created and click finish. Click on Save All button
    • Under Solution Explorer\Models, add a new model. I named it "HomeParams" and add the following codes. This will be our model class. Classes are inheritted to avoid redundancy.
      public class home_itemsCreateParam
          {
              [Required]
              [DisplayName("Item's Name")]
              public string itemName { get; set; }
              [Required]
              [DisplayName("Item's Description")]
              public string itemDescription { get; set; }
          }
          public class home_itemsDetailsParam : home_itemsCreateParam
          {
              public long id { get; set; }
          }
          public class home_itemsList : home_itemsDetailsParam 
          {
              public List<items_list_Result> items { get; set; }
          }
    • Add the follwing codes before namespace {
       using System.ComponentModel;
      using System.ComponentModel.DataAnnotations;
    • Under Solition Explorer\Controllers, add a new controller and choose "MVC 5 Controller - Empty". Name it as "HomeController"
      public class HomeController : Controller
          {
              itemsCatalogEntities db = new itemsCatalogEntities();
    • Add using WebApplication2.Models; before namespace itemsCatalogMVC.Controllers
    • Index action result will list all records from the database.
      public ActionResult Index()
              {
                  home_itemsList param = new home_itemsList();
                  param.items = new List<items_list_Result>();
                  param.items = db.items_list(0).ToList();
                  return View(param);
              }
    • Right click on it and click add View
    • On template, choose List, on Model Class, items_list_Result
    • Edit the first line from @model IEnumerable<WebApplication2.items_list_Result> to @model projectName.Models.home_itemsList
    • look for @foreach (var item in Model) {, and replace it with  @foreach (var item in Model.items) {
    • On HomeController, we will now add a create action result. This will only display the form
      public ActionResult Create()
              {
                  home_itemsCreateParam param = new home_itemsCreateParam();
                  return View(param);
              }
    • we will need a separate action result for POST. This is the one who will insert records on our database
            [HttpPost]
              public ActionResult Create (home_itemsCreateParam param)
              {
                  try
                  {
                      if (ModelState.IsValid)
                      {
                          var result = db.items_add(param.itemName, param.itemDescription).First();
                          TempData["message"] = string.Format("{0} has been added", param.itemName);
                          return RedirectToAction("Details", new { id = result });
                      }
                  }
                  catch (Exception ex)
                  {
                      string alert = "";
                      if (ex.InnerException != null)
                      {
                          alert = ex.InnerException.Message;
                      }
                      else
                      {
                          alert = ex.Message;
                      }
                      if ((alert.StartsWith("sp|")) || (alert.StartsWith("in|")))
                      {
                          alert = alert.Substring(3);
                      }
                      else
                      {
                          alert = "Unknown Error";
                      }
                      TempData["alert"] = alert;
                  }
                  return View(param);
              }
    • Let's add "Details" action result
      public ActionResult Details (long id)
              {
                  home_itemsDetailsParam param = new home_itemsDetailsParam();
                  try
                  {
                      var result = db.items_list(id).First();
                      param.id = result.id;
                      param.itemDescription = result.itemDescription;
                      param.itemName = result.itemName;
                  }
                  catch (Exception ex)
                  {
                      string alert = "";
                      if (ex.InnerException != null)
                      {
                          alert = ex.InnerException.Message;
                      }
                      else
                      {
                          alert = ex.Message;
                      }
                      if ((alert.StartsWith("sp|")) || (alert.StartsWith("in|")))
                      {
                          alert = alert.Substring(3);
                      }
                      else
                      {
                          alert = "Unknown Error";
                      }
                      TempData["alert"] = alert;
                      return RedirectToAction("Index");
                  }
                  return View(param);
              }
    • Add view for details. The template should be "Details" and the class should be home_itemsDetailsParam
    • For "Edit" action result, the code is almost the same with "Details" but the view should be "Edit". The class is same as "Details" which is home_itemsDetailsParam. This will allow us to show the "Edit" form instead of displaying it
      public ActionResult Edit(long id)
              {
                  home_itemsDetailsParam param = new home_itemsDetailsParam();
                  try
                  {
                      var result = db.items_list(id).First();
                      param.id = result.id;
                      param.itemDescription = result.itemDescription;
                      param.itemName = result.itemName;
                  }
                  catch (Exception ex)
                  {
                      string alert = "";
                      if (ex.InnerException != null)
                      {
                          alert = ex.InnerException.Message;
                      }
                      else
                      {
                          alert = ex.Message
                      }
                      if ((alert.StartsWith("sp|")) || (alert.StartsWith("in|")))
                      {
                          alert = alert.Substring(3);
                      }
                      else
                      {
                          alert = "Unknown Error";
                      }
                      TempData["alert"] = alert;
                      return RedirectToAction("Index");
                  }
                  return View(param);
              }
    • for Post,
             [HttpPost]
              public ActionResult Edit(home_itemsDetailsParam param)
              {
                  try
                  {
                      if (ModelState.IsValid)
                      {
                          var result = db.items_update(param.id, param.itemName, param.itemDescription);
                          TempData["message"] = string.Format("{0} has been edited", param.itemName);
                      }
                  }
                  catch (Exception ex)
                  {
                      string alert = "";
                      if (ex.InnerException != null)
                      {
                          alert = ex.InnerException.Message;
                      }
                      else
                      {
                          alert = ex.Message;
                      }
                      if ((alert.StartsWith("sp|")) || (alert.StartsWith("in|")))
                      {
                          alert = alert.Substring(3);
                      }
                      else
                      {
                          alert = "Unknown Error";
                      }
                      TempData["alert"] = alert;
                      return View(param);
                  }
                  return RedirectToAction("Details", new { id = param.id });
              }
    • We dont make a view for "Delete" action result, we don't need to. All we need to do is to redirect the user back to the index with alert message on it.
      public ActionResult Delete(long id)
              {
                  try
                  {
                      var result = db.items_delete(id);
                      TempData["message"] = string.Format("{0} has been deleted", result);
                  }
                  catch (Exception ex)
                  {
                      string alert = "";
                      if (ex.InnerException != null)
                      {
                          alert = ex.InnerException.Message;
                      }
                      else
                      {
                          alert = ex.Message;
                      }
                      if ((alert.StartsWith("sp|")) || (alert.StartsWith("in|")))
                      {
                          alert = alert.Substring(3);
                      }
                      else
                      {
                          alert = "Unknown Error";
                      }
                      TempData["alert"] = alert;
                  }
                  return RedirectToAction("Index");
              }
    • The TempData["alert"] and TempData["message"] should be visible on all pages. Open the Views\Shared\_Layout.cshtml and add the following codes before RenderBody()

      <h4 class="alert-danger">@TempData["alert"]</h4>
      <h4 class="alert-success">@TempData["message"]</h4>