BookStoreWithData

BookStoreWithData (database)

This page discusses how to use ASP.NET Core Web App to connect to MS SQL Server database. It is based on

The result screenshots are as follows:

Let’s add a new class Book into the Models folder with some properties:

C:\ASP.NET-workspace\BookStoreWithData\Models\Book.cs

 using System.ComponentModel.DataAnnotations;

 namespace BookStoreWithData.Models {

   public class Book {

     public int Id { get; set; }

     [ Display( Name = "Book Title" ) ]
     [ Required ]
     public string Title { get; set; }

     public string Genre { get; set; }

     [ DataType( DataType.Currency ) ]
     [ Range( 1, 100 ) ]
     public decimal Price { get; set; }

     [ Display( Name = "Publish Date" ) ]
     [ DataType( DataType.Date ) ]
     public DateTime PublishDate { get; set; }
   }
 }

The next step is to scaffold the model to generate the controller action methods and views. Scaffolding will create a new fully functional controller. Right-click on the Controllers folder > Add > New Scaffolded Item:

In the Add Scaffold dialog, we are going to select MVC Controller with views, using Entity Framework > Add:

Let’s complete the Add Controller dialog:

    Model class: Book (BookStoreWithData.Models)
    Data context class: Select the + icon.
    Leave the default name (BookStoreWithData.Models.BookStoreWithDataContext)
    Click Add

    Views: Keep the default of each option checked
    Controller name: Keep the default BooksController
    Select Add

C:\ASP.NET-workspace\BookStore\Views\Home\Index.cshtml

 @{ ViewData["Title"] = "Home Page"; }

 <div class="text-center">
  <h1 class="display-4">Welcome</h1>
  <p>Learn about <a href="https://learn.microsoft.com/aspnet/core">
    building Web apps with ASP.NET Core</a>.</p>
 </div>

  First, let’s run the following command in the Package Manager console:

Add-Migration BookStoreWithData.Data.BookStoreWithDataContext

This will create the classes for supporting migrations.

  Now, we need to apply those changes to the database. Before that, make sure
  the connection string specified in the appsettings.json file points to the
  database server that we want to connect. By default, the scaffolding process
  makes it point to the SQL Server Express local DB.

For applying the changes to the database, let’s run the following command:

PM> update-database

This will update the database based on our models.

  We have covered the way to do this in detail in the ASP.NET Core Web API
  with EF Core Code-First Approach article.

Details.cshtml, which shows the details of a book:

C:\ASP.NET-workspace\BookStore\Views\Home\Details.cshtml

 @model BookStore.Models.Book

 @{ ViewData["Title"] = "Details"; }

 <h1>Details</h1>
 <div>
  <h4>Book</h4>
  <hr />
  <dl class="row">
   <dt class="col-sm-2">
    @Html.DisplayNameFor( model => model.Title )
   </dt>
   <dd class="col-sm-10">
    @Html.DisplayFor( model => model.Title )
   </dd>
   <dt class="col-sm-2">
    @Html.DisplayNameFor( model => model.Genre )
   </dt>
   <dd class="col-sm-10">
    @Html.DisplayFor( model => model.Genre )
   </dd>
   <dt class="col-sm-2">
    @Html.DisplayNameFor( model => model.Price )
   </dt>
   <dd class="col-sm-10">
    @Html.DisplayFor( model => model.Price )
   </dd>
   <dt class="col-sm-2">
    @Html.DisplayNameFor( model => model.PublishDate )
   </dt>
   <dd class="col-sm-10">
    @Html.DisplayFor( model => model.PublishDate )
   </dd>
  </dl>
  <table>
   <thead>
    <tr><th>Authors</th></tr>
   </thead>
   <tbody>
    @foreach ( var item in Model.Authors ) {
     <tr>
      <td>
       @Html.DisplayFor( modelItem => item )
      </td>
     </tr>
    }
   </tbody>
  </table>
 </div>
 <hr />
 <div>
  <a asp-action="Edit" asp-route-id="@Model.Id">Edit</a> |
  <a asp-action="Index">Back to List</a>
 </div>

Create.cshtml, which shows the data-input interface, but does NOT function:

C:\ASP.NET-workspace\BookStore\Views\Home\Create.cshtml

 @model BookStore.Models.Book

 @{ ViewData["Title"] = "Create"; }

 <h1>Create</h1>
 <h4>Book</h4>
 <hr />
 <div class="row">
  <div class="col-md-4">
   <form asp-action="Create">
    <div asp-validation-summary="ModelOnly" class="text-danger"></div>
    <div class="form-group">
     <label asp-for="Id" class="control-label"></label>
     <input asp-for="Id" class="form-control" />
      <span asp-validation-for="Id" class="text-danger"></span>
    </div>
    <div class="form-group">
     <label asp-for="Title" class="control-label"></label>
     <input asp-for="Title" class="form-control" />
     <span asp-validation-for="Title" class="text-danger"></span>
    </div>
    <div class="form-group">
     <label asp-for="Genre" class="control-label"></label>
     <input asp-for="Genre" class="form-control" />
     <span asp-validation-for="Genre" class="text-danger"></span>
    </div>
    <div class="form-group">
     <label asp-for="Price" class="control-label"></label>
     <input asp-for="Price" class="form-control" />
     <span asp-validation-for="Price" class="text-danger"></span>
    </div>
    <div class="form-group">
     <label asp-for="PublishDate" class="control-label"></label>
     <input asp-for="PublishDate" class="form-control" />
     <span asp-validation-for="PublishDate" class="text-danger"></span>
    </div>
    <div class="form-group">
     <input type="submit" value="Create" class="btn btn-primary" />
    </div>
   </form>
  </div>
 </div>

 <div>
  <a asp-action="Index">Back to List
 </div>

Privacy.cshtml:

C:\ASP.NET-workspace\BookStore\Views\Home\Privacy.cshtml

 @{ ViewData["Title"] = "Privacy Policy"; }

 <h1>@ViewData["Title"]</h1>

 <p>Use this page to detail your site's privacy policy.</p>

Controllers, which has one controller HomeController.cs as follows:

C:\ASP.NET-workspace\BookStore\Controllers\HomeController.cs

 using System.Diagnostics;
 using BookStore.Models;
 using Microsoft.AspNetCore.Mvc;

 namespace BookStore.Controllers {
  public class HomeController: Controller {
   private readonly ILogger<HomeController> _logger;

   public HomeController( ILogger<HomeController> logger ) {
    _logger = logger;
   }

   public IActionResult Index( ) {
    return View( );
   }
        
   public IActionResult Privacy( ) {
    return View( );
   }
   [ ResponseCache( Duration = 0, Location = ResponseCacheLocation.None,
     NoStore = true ) ]

   public IActionResult Error( ) {
    return View( new ErrorViewModel { RequestId = Activity.Current?.Id ??
     HttpContext.TraceIdentifier } );
   }

   public IActionResult Details( ) {
    Book book = new Book( ) {
     Id = 1,
     Title = "Learning ASP.NET Core 2.0",
     Genre = "Programming & Software Development",
     Price = 45,
     PublishDate = new System.DateTime( 2012, 04, 23 ),
     Authors = new List<string> { "Jason De Oliveira", "Michel Bruchet" }
    };
    return View( book );
   }

   // GET: Books/Create
   public ActionResult Create( ) {
    return View( );
   }

   // POST: Books/Create
   [HttpPost]
   [ValidateAntiForgeryToken]
   public ActionResult Create( [ Bind( "Id,Title,Genre,Price,PublishDate" )] Book book ) {
    try {
     // TODO: Add insert logic here
     return RedirectToAction( nameof( Index ) );
    }
    catch {
     return View( book );
    }
   }

   // GET: Books/Edit/5
   public ActionResult Edit( int id ) {
    return View( );
   }

   // POST: Books/Edit/5
   [HttpPost]
   [ValidateAntiForgeryToken]
   public ActionResult Edit( int id, IFormCollection collection ) {
    try {
     // TODO: Add update logic here
     return RedirectToAction( nameof( Index ) );
    }
    catch {
     return View( );
    }
   }

   // GET: Books/Delete/5
   public ActionResult Delete( int id ) {
    return View( );
   }

   // POST: Books/Delete/5
   [HttpPost]
   [ValidateAntiForgeryToken]
   public ActionResult Delete( int id, IFormCollection collection ) {
    try {
     // TODO: Add delete logic here
     return RedirectToAction( nameof( Index ) );
    }
    catch {
     return View( );
    }
   }

   public IEnumerable<Book> GetBooks( ) {
    List<Book> books = new List<Book>( );
    Book book1 = new Book( ) {
     Id = 1,
     Title = "Learning ASP.NET Core 2.0",
     Genre = "Programming & Software Development",
     Price = 45,
     PublishDate = new System.DateTime( 2017, 12, 14 )
    };
    books.Add( book1 );

    Book book2 = new Book( ) {
     Id = 1,
     Title = "Pro ASP.NET Core MVC",
     Genre = "Programming & Software Development",
     Price = 85,
     PublishDate = new System.DateTime( 2016, 09, 15 ),
    };
    books.Add( book2 );

    return books;
   }
  }
 }