Header Ads

ASP.NET MVC5: Entity Framework Simple Code First Database Approach

April 01, 2017 , , , ,

There has been a lot of buzz about entity framework in tech world.
The reason is simple entity framework is an overwhelming development communicator between our physical database engines and our code base. Entity framework in ASP.NET MVC5 platform offers following three approaches to connect with database i.e.
  1. Code First Approach.
  2. Model First Approach.
  3. Database First Approach.
Today, I shall be demonstrating a simple code first approach using entity framework in ASP.NET MVC5 platform.

Following are some prerequisites before you proceed further in this tutorial:
  1. Knowledge of ASP.NET MVC5.
  2. Knowledge of HTML.
  3. Knowledge of JavaScript.
  4. Knowledge of Bootstrap.
  5. Knowledge of Jquery.
  6. Knowledge of C# Programming.
You can download the complete source code for this tutorial or you can follow the step by step discussion below. The sample code is being developed in Microsoft Visual Studio 2013 Ultimate.

Download Now!

Let's begin now!

1) Create a new MVC project in visual studio and name it "EFCodeFirstMvc".  
2) On "Models" folder, right click and click "New Item" as shown below i.e.


3) Now click "ADO.NET Entity Data Model" and name it "EFCodeFirstDbContext" as shown below i.e.



4) Choose "Empty Code First model" and click finish as shown below i.e.



You will see that "EFCodeFirstDbContext.cs" file has been created under Models folder. We will change it later in this tutorial.  

5) Let's create our empty database without any tables into SQL server database engine, I am using below script to create an empty database named "db_code_first" i.e.
  
USE [master]
GO
/****** Object:  Database [db_code_first]    Script Date: 30-Mar-17 9:34:12 PM ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'db_code_first')
DROP DATABASE [db_code_first]
GO
/****** Object:  Database [db_code_first]    Script Date: 30-Mar-17 9:34:12 PM ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'db_code_first')
BEGIN
CREATE DATABASE [db_code_first]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'db_code_first', FILENAME = N'C:\SQL Server DATA Path\db_code_first.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'db_code_first_log', FILENAME = N'C:\SQL Server DATA Path\db_code_first_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

GO
ALTER DATABASE [db_code_first] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [db_code_first].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [db_code_first] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [db_code_first] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [db_code_first] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [db_code_first] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [db_code_first] SET ARITHABORT OFF 
GO
ALTER DATABASE [db_code_first] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [db_code_first] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [db_code_first] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [db_code_first] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [db_code_first] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [db_code_first] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [db_code_first] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [db_code_first] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [db_code_first] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [db_code_first] SET  DISABLE_BROKER 
GO
ALTER DATABASE [db_code_first] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [db_code_first] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [db_code_first] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [db_code_first] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [db_code_first] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [db_code_first] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [db_code_first] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [db_code_first] SET RECOVERY FULL 
GO
ALTER DATABASE [db_code_first] SET  MULTI_USER 
GO
ALTER DATABASE [db_code_first] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [db_code_first] SET DB_CHAINING OFF 
GO
ALTER DATABASE [db_code_first] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [db_code_first] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [db_code_first] SET DELAYED_DURABILITY = DISABLED 
GO
EXEC sys.sp_db_vardecimal_storage_format N'db_code_first', N'ON'
GO
ALTER DATABASE [db_code_first] SET  READ_WRITE 
GO

This script is auto-generated from SQL server, you need to replace "SQL Server DATA Path" with your SQL server data storage path in the above script.  

6) On "View" menu click "Server Explorer" as shown below i.e.



7) Let's modify our database connection settings, so that, when we create our schema from code, it will automatically updated into SQL server database. On "Server Explorer" window right click "EFCodeFirstDbContext" connection and click "Modifying connection" as shown below i.e.



8) Provide your connection settings into connection settings window and click "OK" i.e.



You will notice that your database is empty and there is no table currently existing as shown below i.e.



9) Open the "EFCodeFirstDbContext.cs" file and replace below code in it i.e. 


namespace EFCodeFirstMvc.Models
{
    using System;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    using System.Linq;

    public class EFCodeFirstDbContext : DbContext
    {
        // Your context has been configured to use a 'EFCodeFirstModel' connection string from your application's 
        // configuration file (App.config or Web.config). By default, this connection string targets the 
        // 'EFCodeFirstMvc.Models.EFCodeFirstModel' database on your LocalDb instance. 
        // 
        // If you wish to target a different database and/or database provider, modify the 'EFCodeFirstModel' 
        // connection string in the application configuration file.
        public EFCodeFirstDbContext()
            : base("name=EFCodeFirstDbContext")
        {
        }

        // Add a DbSet for each entity type that you want to include in your model. For more information 
        // on configuring and using a Code First model, see http://go.microsoft.com/fwlink/?LinkId=390109.

        public virtual DbSet<LoginEntity> LoginEntities { get; set; }
    }

    public class LoginEntity
    {
        [Display(Name = "Id")]
        public int Id { get; set; }

        [Display(Name = "Enter Username")]
        public string Username { get; set; }

        [Display(Name = "Enter Password")]
        public string Password { get; set; }

        //[Display(Name = "Enter Full Name")]
        //public string FullName { get; set; }
    }
}

In the above code, we have created our table called "LoginEntities" and tells our DB context about our table with the following line i.e. 


public virtual DbSet<LoginEntity> LoginEntities { get; set; }

Notice in our class"LoginEntity", we have commented out last property. I will come back to this property when we perform table schema changes via code in code first approach.

10) Now, create a controller and named it "AccountController.cs" under "Controllers" folder and replace following code in it i.e. 


using System;
using System.Globalization;
using System.Linq;
using System.Security.Claims;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.Owin.Security;
using EFCodeFirstMvc.Models;

namespace EFCodeFirstMvc.Controllers
{
    [Authorize]
    public class AccountController : Controller
    {
        public AccountController()
        {
        }

        //
        // GET: /Account/Register
        [AllowAnonymous]
        public ActionResult Register()
        {
            // Initialization.
            AccountViewModel model = new AccountViewModel();

            ////// DB Context.
            ////EFCodeFirstDbContext db = new EFCodeFirstDbContext();

            ////// Get Result
            ////model.ResultList = db.LoginEntities.Select(p => p).ToList();

            return View(model);
        }

        //
        // POST: /Account/Register
        [HttpPost]
        [AllowAnonymous]
        [ValidateAntiForgeryToken]
        public ActionResult Register(AccountViewModel model)
        {
            if (ModelState.IsValid)
            {
                // DB Context.
                EFCodeFirstDbContext db = new EFCodeFirstDbContext();

                // Setting.
                int idVal = db.LoginEntities.Select(p => p).ToList().Count > 0 
                            ? (db.LoginEntities.OrderByDescending(p => p.Id).Select(p => p.Id).FirstOrDefault()) + 1
                            : 1; 

                // Inserting.
                model.LoginEntityModel.Id = idVal;
                db.LoginEntities.Add(model.LoginEntityModel);
                db.SaveChanges();

                // Get Result
                model.ResultList = db.LoginEntities.Select(p => p).ToList();
            }

            // If we got this far, something failed, redisplay form
            return View(model);
        }
    }
}

In the above code, we have written both HTTP GET and HTTP POST methods for our "Register" action. You can see some commented out code in HTTP GET method, I will come back to it, while in the HTTP POST method, I have added a simple logic to add my account information into my database by using code first approach.

11) Create a new model called "AccountViewModel.cs" under "Models" folder and replace below code in it i.e. 


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace EFCodeFirstMvc.Models
{
    public class AccountViewModel
    {
        public LoginEntity LoginEntityModel { get; set; }

        public List<LoginEntity> ResultList { get; set; } 
    }
}

The above code is a simple model, that I will attach with my account registration view.    

12) Now create "Register.cshtml" file under "Views\Account" folder and replace following in it as shown below i.e. 


@model EFCodeFirstMvc.Models.AccountViewModel
@{
    ViewBag.Title = "Register";
}

<h2>@ViewBag.Title.</h2>

@using (Html.BeginForm("Register", "Account", FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{
    @Html.AntiForgeryToken()
    <h4>Create a new account.</h4>
    <hr />
    @Html.ValidationSummary("", new { @class = "text-danger" })

    <div class="form-group">
        @Html.LabelFor(m => m.LoginEntityModel.Username, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.LoginEntityModel.Username, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.LoginEntityModel.Password, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.PasswordFor(m => m.LoginEntityModel.Password, new { @class = "form-control" })
        </div>
    </div>
    @*<div class="form-group">
        @Html.LabelFor(m => m.LoginEntityModel.FullName, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.LoginEntityModel.FullName, new { @class = "form-control" })
        </div>
    </div>*@

    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" class="btn btn-default" value="Register" />
        </div>
    </div>
}

<h2>Result List</h2>

@if (Model.ResultList != null)
{
    for (int i = 0; i < Model.ResultList.Count; i++)
    {
        <div class="row">
            <div class="col-md-2">
                <p>@Model.ResultList[i].Id</p>
            </div>
            <div class="col-md-2">
                <p>@Model.ResultList[i].Username</p>
            </div>
            <div class="col-md-2">
                <p>@Model.ResultList[i].Password</p>
            </div>
            @*<div class="col-md-2">
                <p>@Model.ResultList[i].FullName</p>
            </div>*@
        </div>
    }
}
@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

In the above code, I have created a simple form for account registration and a result list which will display my data from "LoginEntities" table. You will see commented out property which we will come back to soon.  

13) Let's first execute the project and create a sample account, you will see output as shown below i.e.



14) As we have added a new account, let's see, if our table has been created in the database or not, so, refresh the database connection in server explorer and expand the "Tables" folder, you will notice that two tables have been created as shown below i.e.




You will notice that there are two tables which have been created one is the table that we have defined at code level and other is the migration history table . The migration history table will keep the history version of the changes that you have made into database tables and its structure. When you expand your table, you will see your defined columns via code as shown below i.e.



15) Now, let's add a new property into our table via code and by entity framework provided migration commands, we will signal our physical database about schema changes. So, uncomment all the code that I have mentioned previously about being commented out in the models, views & controllers folders.

16) Let's signal our SQL server about this schema, before that make sure that your nuget package is installed, if not than install it via "Tools-> Extensions & Updates" as shown below i.e.



  
17) Now open "Package Manager Console" via "Tools->Package Manager Console" and type "Enable-Migrations" command and hit enter, you will see details as shown below i.e.





You will notice that a "Migration" folder has been created with a history version .cs file and "configuration.cs" file which will maintain migration history at code level and migration command settings in configuration file.



18) Now, enter "Add-Migration AddFullName" command and you will see result as shown below i.e.





In the above command, notice that at"AddFullName" portion we have written the name of our new column property after "Add" keyword i.e. FullName.

19) Finally, update our SQL server database about the schema changes by entering "Update-Database" command i.e.




20) Refresh the SQL server connection and you will see that our new column is being reflected in the SQL server database as shown below i.e.



21) Now, execute the project and register new account, you will see following result i.e.




22) Checkout your database in SQL server, it will show your register accounts and migration history i.e.




Conclusion

In this article, you will learn about entity frame work code first approach in ASP.NET MVC5 platform. You will also learn about creating table schema via code and making table schema changes via code along with entity frame work migration commands.

Post a Comment

No comments

Subscribe to: Post Comments ( Atom )