Saturday, September 24, 2016

CodeFirst with .NET Core Command Line App using SQL Server

In this post we will look at building a simple command line .NET Core application that works with SQL Server. The database is created using the Code First development paradigm. The database table will look like this:

image

1) Create a working directory named InstituteSqlServer.

2) From within a command prompt in that folder, type: dotnet new. This creates a Hello-World .NET Core command-line application.

3) Using Visual Studio Code, navigate to the InstituteSqlServer folder to open the project.

4) Open Program.cs , change the namespace to InstituteSqlServer and save the file.

5) To build the “Hello World” command-line app and run it, execute the following commands from the command prompt:

dotnet restore
dotnet build
dotnet run

6) Since we will be accessing SQL Server using Entity Framework, we will need to add the following to the dependencies block of the project.json file:

"Microsoft.EntityFrameworkCore": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
"Microsoft.Extensions.Configuration": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0"

7) Add another tools section to the project.json file as follows:

"tools": {
  "Microsoft.EntityFrameworkCore.Tools": {
    "version": "1.0.0-preview2-final",
    "imports": [
      "portable-net45+win8+dnxcore50",
      "portable-net45+win8"
    ]
  }
}

8) At this stage, it is appropriate to restore all the additional dependencies that we will be using. Therefore, run the following command at the command prompt:

dotnet restore

9) Add a folder named “Models” and add to it a C# class file named Student.cs. Add the following code to Student.cs:

using System;
using System.Collections.Generic;

namespace InstituteSqlServer.Models {
  public class Student {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Major { get; set; }
    public DateTime DateOfBirth { get; set; }

    public static List<Student> GetSampleStudents()   {
      List<Student> students = new List<Student>() {
        new Student {
          FirstName = "Ann",
          LastName = "Lee",
          Major = "Medicine",
          DateOfBirth = Convert.ToDateTime("2004/09/09")
        },
        new Student
        {
          FirstName = "Bob",
          LastName = "Doe",
          Major = "Engineering",
          DateOfBirth = Convert.ToDateTime("2005/09/09")
        },
        new Student {
          FirstName = "Sue",
          LastName = "Douglas",
          Major = "Pharmacy",
          DateOfBirth = Convert.ToDateTime("2006/01/01")
        },
        new Student {
          FirstName = "Tom",
          LastName = "Brown",
          Major = "Business",
          DateOfBirth = Convert.ToDateTime("2000/09/09")
        },
        new Student {
          FirstName = "Joe",
          LastName = "Mason",
          Major = "Health",
          DateOfBirth = Convert.ToDateTime("2001/01/01")
        }
      };
      return students;
    }
  }
}

The above code defines the properties of a Student class and adds a static method GetSampleStudents() that retrieves some sample data.

10) We will save the database connection string in a configuration JSON file. Create a file named appsettings.json in the root of your project with the following content:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=InstituteDB;Trusted_Connection=True;"
  }
}

11) It is necessary to have a helper method that reads name/value pairs from the appsettings.json configuration file. To this end, we will create a class file named Utility.cs in the Models folder that fulfills this task. This file contains the following code:

using Microsoft.Extensions.Configuration;

namespace InstituteSqlServer.Models {
    public class Utility {
        public static string GetConnectionString(string key) {
            // Defines the sources of configuration information for the
            // application.
            var builder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json");

            // Create the configuration object that the application will
            // use to retrieve configuration information.
            var configuration = builder.Build();

            // Retrieve the configuration information.
            var configValue = configuration[key];

            return configValue;
        }
    }
}

12) Add another C# class file to the Models folder named InstituteContext.cs with the following code:

using Microsoft.EntityFrameworkCore;

namespace InstituteSqlServer.Models {
    public class InstituteContext : DbContext {
        public DbSet<Student> Students { get; set; }
        protected override void OnModelCreating(ModelBuilder builder) {
            builder.Entity<Student>().HasKey(m => m.Id);
            base.OnModelCreating(builder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {
            string constr = Utility.GetConnectionString("ConnectionStrings:DefaultConnection");

            optionsBuilder.UseSqlServer(constr);
        }
    }
}

13) When the application is built, the .dll file is created in a directory somewhere under the bin folder. We need to make sure that the appsettings.json file is also copied to the same directory as the .dll file so that it can be read by the application. This is accomplished by adding the following to the “buildOptions” section of the project.json file:

"copyToOutput": {
  "include": [ "appsettings.json" ]
}

14) It is time to build our application. At a command-prompt in the project folder, type the following command:

dotnet build

15) It is now possible for us to create the database using Entity Framework’s code-first paradigm. While in the command prompt, execute the following two EF commands:

dotnet ef migrations add FirstMigration
dotnet ef database update

Upon completion of the above EF commands, the database will have been created.

16) We are now in a position to add and retrieve data to and from the database. Add the following two methods to your Program.cs file:

private static void addStudents() {
    using (var db = new InstituteContext()) {
        db.Students.AddRange(Student.GetSampleStudents());
        var count = db.SaveChanges();
        Console.WriteLine("{0} records saved to database", count);
    }
}
private static void displayStudents() {
    using (var db = new InstituteContext()) {
        Console.WriteLine();
        Console.WriteLine("All students in database:");
        foreach (var s in db.Students) {
            Console.WriteLine("{0}\t{1}\t{2}", s.Id, s.FirstName, s.LastName);
        }
    }
}

17) Resolve the namespace for the Student and InstituteContext classes in Program.cs.

18) Replace the Main() method on Program.cs with the following:

public static void Main(string[] args) {
    addStudents();
    displayStudents();
}

19) This final step is the most exciting. If you run the application, it should add data to the Students table in the database and retrieve its contents. Can’t wait, lets do it. Execute these commands from the command prompt:

dotnet build
dotnet run

20) You can celebrate if you see the following results:

Project InstituteSqlServer (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
5 records saved to database
All students in database:

1 Ann Lee
2 Bob Doe
3 Sue Douglas
4 Tom Brown
5 Joe Mason

In order to use this application on Linux or the Mac, it is advisable to use the SQLite database instead of SQL Server. This is the topic for my next post.

Building a simple command-line application using .NET Core and Visual Studio Code

I am planning to write a series of blogs posts to introduce application and web developers to the all new .NET Core framework. This article is intended as an introductory lesson on the very basics of .NET Core. Unlike previous version of the .NET framework, .NET Core is open source, cross platform, cross device, small, and fast.

Our first task is to build a simple command-line application with .NET Core version 1.0.0. We will use the light weight Visual Studio Code for our text editor.

1) Create a working folder named “Institution”

2) Change directory into the “Institution” node and type in

dotnet new

This creates a .NET Core “Hello World” project containing two files: Program.cs and project.json.

3) Start Visual Studio Code and open this project folder. There are two others ways, I know of, that can achieve the same result:

a. In the command-line, change directory to the “Institution” folder and type “code .”. This will open up your project folder contents in Visual Studio Code in the current directory (.).

b. Alternatively, navigate to the “Institution” folder using File Explorer (in Windows). Right-click on the folder and select “Open with Code”.

4) Once in Visual Studio Code, you can view the contents of this file. The Program.cs file looks like this:

using System;
namespace ConsoleApplication {
  public class Program {
    public static void Main(string[] args) {
      Console.WriteLine("Hello World!");
    }
  }
}

The project.json file looks like this:

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {},
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

The project.json file has NuGet dependencies necessary to build our console app. You may also notice the presence of another file named project.lock.json. This file is auto-generated and expands on project.json with more detailed dependencies required by your application. There is no need to commit file project.lock.json to source control.

5) Change the namespace from ConsoleApplication to Institution. Also, change the statement Console.WriteLine("Hello World!"); to

Console.WriteLine("Welcome to our institution!");

6) Save and back in the command prompt, type in:

dotnet restore

This instruction causes dependencies to be brought in from the NuGet repository

7) To execute the application, you can simple run the following command from the command-line:

dotnet run

“dotnet run” calls “dotnet build” to ensure that the app has been built, and then calls “dotnet institution.dll” to run the application. You can find the resulting institution.dll file in the bin\Debug\netcoreapp1.0 directory. Notice that the build process does not produce a institution.exe file. This is because we just created a portable app. Instead of creating a portable app, let us produce a self-contained .exe app.

Note that the name of the .dll file is dictated by the name of the primary project folder, and not by the name of your primary class file (Program.cs).

Compiling a self-contained .exe app

We will need to make some changes to the project.json file.

1) Delete the "type": "platform" element from all dependencies.

2) Next, add a new runtimes node as follows:

"runtimes": {
  "win10-x64": {},
  "osx.10.11-x64": {}
}

This causes the build system to generate native executables for the current environment. In Windows, you will build a Windows executable. On the Mac, you will build the OS X executable.

Save the project.json file, then run the following commands from the command-line:

dotnet restore
dotnet build

The self-contained .exe file can be found at bin\Debug\netcoreapp1.0\win10-x64\Institution.exe.

You can execute the institution.exe file by typing the following on the command line while in the Institution folder:

      bin\Debug\netcoreapp1.0\win10-x64\hello.exe

or simply:

      dotnet run

Let us add a Student class to the project. It is good practice to organize our files in folders. This is especially beneficial when you are dealing with large projects. Therefore, create a folder named Model and add to it a Student.cs class file with the following code:

using System;
using System.Collections.Generic;
namespace Institution.Models {
  public class Student {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Major { get; set; }
    public DateTime DateOfBirth { get; set; }

    public static List<Student> GetSampleStudents()   {
      List<Student> students = new List<Student>() {
        new Student {
          Id = 1,
          FirstName = "Ann",
          LastName = "Lee",
          Major = "Medicine",
          DateOfBirth = Convert.ToDateTime("2004/09/09")
        },
        new Student
        {
          Id = 2,
          FirstName = "Bob",
          LastName = "Doe",
          Major = "Engineering",
          DateOfBirth = Convert.ToDateTime("2005/09/09")
        },
        new Student {
          Id = 3,
          FirstName = "Sue",
          LastName = "Douglas",
          Major = "Pharmacy",
          DateOfBirth = Convert.ToDateTime("2006/01/01")
        },
        new Student {
          Id = 4,
          FirstName = "Tom",
          LastName = "Brown",
          Major = "Business",
          DateOfBirth = Convert.ToDateTime("2000/09/09")
        },
        new Student {
          Id = 5,
          FirstName = "Joe",
          LastName = "Mason",
          Major = "Health",
          DateOfBirth = Convert.ToDateTime("2001/01/01")
        }
      };
      return students;
    }
  }
}

The above code describes a Student class with properties: Id, FirstName, LastName, Major, and DateOfBirth. It also generates some dummy data that can be accessed through the GetSampleStudents() static method.

Back in Program.cs, replace the Main() method with the following:

public static void Main(string[] args) {
  List<Student> students = Student.GetSampleStudents();
  foreach(var i in students) {
    Console.WriteLine("{0}\t{1}\t{2}", i.Id, i.FirstName, i.LastName);
  }
}

Do not forget to resolve the Student and List classes by using the following namespaces:

using System.Collections.Generic;
using Institution.Models;

If you run the application by executing “dotnet run” from the command-line, you should see the following output:

1 Ann Lee
2 Bob Doe
3 Sue Douglas
4 Tom Brown
5 Joe Mason

In future posts, I am hoping to explore more complicated data driven apps developed using .NET Core. Meantime, I hope you benefited from this article.

Sunday, February 28, 2016

ASP.NET Core WebAPI app Using SQLite & EF7

This tutorial uses Visual Studio 2015 and ASP.NET 5 RC. This was recently renamed by Microsoft to ASP.NET Core.

My intension is to give you a practical introduction into developing ASP.NET Core apps with the SQLite database as an alternative to traditional SQL Server.

A useful utility that comes in handy when working with the SQLite database is SQLiteStudio. Download SQLiteStudio from: http://sqlitestudio.pl/?act=download. Extract the ZIP file and place contents in a separate folder. Run SQLiteStudio.exe.

We will build an ASP.NET Core app that uses the following Student entity:

image

WebAPI project

Create a new ASP.NET Core app in Visual Studio 2015:

  • File >> New >> Project
  • Templates >> Visual C# >> Web
  • Select “ASP.NET Web Application” and name the project SQLiteWebAPI.
  • Under ASP.NET 5 Templates select “Web API

image

 

Open the global.json and note the runtime version that will be used by the application:

{
   "projects": [ "src", "test" ],
   "sdk": {
      "version": "1.0.0-rc1-update1"
   }
}

The above indicates that the version of the runtime that is being used is 1.0.0-rc1-update1.

Dependencies are added to the project.json file. Open that file and have a peek at the dependencies section:

"dependencies": {
    "Microsoft.ApplicationInsights.AspNet": "1.0.0-rc1",
    "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
    "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
    "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
    "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
    "Microsoft.Extensions.Configuration.FileProviderExtensions" : "1.0.0-rc1-final",
    "Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final"
  },

We will need to add some more dependencies in order to use SQLite with Entity Framework 7. Add these dependencies:

"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.SQLite": "7.0.0-rc1-final",

As soon as you save the project.json file you will notice that the references will be installed by the Nuget package manager.

Since we will be doing EF code migrations, add the following EF command to the commands section:

"ef": "EntityFramework.Commands",

Class Library Project

It is good practice to place all your data models inside a class library. Therefore, we will add to our solution a class library:

  • Right-click on the src node in Solution Explorer
  • Add >> New Project…
  • Select Class Library (Package) >> Name it DataModel then click on OK

image

  • Delete Class1.cs
  • Open project.json in the DataModel project. Make sure that the runtime versions match those in the project.json file of the SQLiteWebAPI project. I found that the SQLiteWebAPI project used dnx451 & dnxcore50, whereas the DataModel project used net451 & dotnet5.4. Therefore, I changed dotnet5.4 in the DataModel project to dnxcore50.
  • In the DataModel project’s project.json file, add the following section right before frameworks:
"dependencies": {
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.SQLite": "7.0.0-rc1-final",
  },

Let’s create our Student class in the DataModel project. Add a folder called Models. Inside of the Models folder, add a class file named Student.cs. Use the following code for the class file:

public class Student {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string School { get; set; }
    public DateTime StartDate { get; set; }
}

Also, to the Models folder in the DataModel project, add another class file named SchoolContext.cs with the following code:

public class SchoolContext : DbContext {
    public DbSet<Student> Students { get; set; }

    protected override void OnModelCreating(ModelBuilder builder) {
        builder.Entity<Student>().HasKey(m => m.Id);
        base.OnModelCreating(builder);
    }
}

In the SQLiteWebAPI project, make a reference to the DataModel project so that the entity classes are available to the web project.

We will need to add a connection string for the SQLite database in the SQLiteWebAPI project:

  • Open the appsettings.json file in the SQLiteWebAPI project
  • Add the following section after the Logging block:



"Data": {
  "DefaultConnection": {
    "ConnectionString": "Data Source=school.sqlite"
  }
},

We will need to add both Entity Framework and SQLite to the SQLiteWebAPI project’s Startup.cs class:

  • Open the Startup.cs file in the SQLiteWebAPI project
  • Add the following instance variable to the Startup class:
private IApplicationEnvironment _appEnv;
  • Inject the argument “IApplicationEnvironment appEnv” into the Startup constructor so that it looks like this:
public Startup(IHostingEnvironment env, IApplicationEnvironment appEnv)
  • Add the following assignment inside the constructor:
_appEnv = appEnv;
  • Add the following code to the ConfigureServices() method just before services.AddMv();
var connection = Configuration["Data:DefaultConnection:ConnectionString"];
connection = connection.Replace("=", "=" + _appEnv.ApplicationBasePath + "/");
services.AddEntityFramework()    
  .AddSqlite()
  .AddDbContext<SchoolContext>(options => options.UseSqlite(connection));

Seed Data

Before we carry out code first migrations, let us first create some seed data:

  • Back in the DataModel project, in the Models folder, create a class named SeedData.cs.
  • Add the following Initialize() method code inside the SeedData class:
public static void Initialize(SchoolContext db) {
    if (!db.Students.Any()) {
        db.Students.Add(new Student
        {
            FirstName = "Bob",
            LastName = "Doe",
            School = "Engineering",
            StartDate = Convert.ToDateTime("2015/09/09")
        });
        db.Students.Add(new Student {
            FirstName = "Ann",
            LastName = "Lee",
            School = "Medicine",
            StartDate = Convert.ToDateTime("2014/09/09")
        });
        db.Students.Add(new Student {
            FirstName = "Sue",
            LastName = "Douglas",
            School = "Pharmacy",
            StartDate = Convert.ToDateTime("2016/01/01")
        });
        db.Students.Add(new Student {
            FirstName = "Tom",
            LastName = "Brown",
            School = "Business",
            StartDate = Convert.ToDateTime("2015/09/09")
        });
        db.Students.Add(new Student {
            FirstName = "Joe",
            LastName = "Mason",
            School = "Health",
            StartDate = Convert.ToDateTime("2015/01/01")
        });

        db.SaveChanges();
    }
} 
To generate seed data, we will first inject the dependency “SchoolContext context” into the arguments of the Configure() method in Startup.cs belonging to the SQLiteWebAPI project. Next, we can make a call to seed the data at the bottom of the Configure() method with the following statement:
      SeedData.Initialize(context);

Migrations

We will execute EF migrations commands from the SQLiteWebAPI project. It is necessary to let the main app know that migrations files exist in the DataModel project. In the SQLiteWebAPI project Startup class ConfigureServices() method, chain .MigrationsAssembly("DataModel") to options.UseSqlite(connection).

The ConfigureServices class will look like this:

public void ConfigureServices(IServiceCollection services) {
    // Add framework services.
    services.AddApplicationInsightsTelemetry(Configuration);

    var connection = Configuration["Data:DefaultConnection:ConnectionString"];
    connection = connection.Replace("=", "=" + _appEnv.ApplicationBasePath + "/");
    services.AddEntityFramework()
      .AddSqlite()
      .AddDbContext<SchoolContext>(options => options.UseSqlite(connection)
      .MigrationsAssembly("DataModel"));

    services.AddMvc();
}

We are now ready to do some migrations:

  • Compile your application
  • Open a command terminal inside the src\SQLiteWebAPI folder
  • To ensure that we will use the same version of the runtime as the application, execute the following command inside the command terminal:
dnvm use 1.0.0-rc1-update1 -r coreclr -arch x64
  • Next, we will add a migration in the DataModel project with the following ef command:
dnx ef migrations add InitialCreate --context SchoolContext --targetProject DataModel

This causes the migrations files to be created in the DataModel project.

  • We will then update the database with the following terminal command:
dnx ef database update
At this point, there will be a file named school.sqlite in the src\SQLiteWebAPI folder. The data will not have been seeded yet because this happens when the application is actually run. 

The WebAPI Controller

Now that we are done setting up database artifacts, let us create a WebAPI Students controller.

  • In the Controllers folder, delete the ValuesController.cs file
  • In the Controllers folder, add a new “Web API Controller Class” named StudentsController
  • Make sure the StudentsController class is annotated with:
[Route("api/[controller]")]
  • Replace contents of the StudentsController class with the following code:
private SchoolContext _context { get; set; }

public StudentsController(SchoolContext context) {
    _context = context;
}

// GET: api/student
[HttpGet]
public IEnumerable<Student> Get() {
    return _context.Students.ToList();
}

// GET api/student/5
[HttpGet("{id}")]
public Student Get(int id) {
    return _context.Students.FirstOrDefault(s => s.Id == id);
}

// POST api/student
[HttpPost]
public void Post([FromBody]Student student) {
    _context.Students.Add(student);
    _context.SaveChanges();
}

// PUT api/student/5
[HttpPut("{id}")]
public void Put(int id, [FromBody]Student student) {
    _context.Students.Update(student);
    _context.SaveChanges();
}

// DELETE api/student/5
[HttpDelete("{id}")]
public void Delete(int id) {
    var student = _context.Students.FirstOrDefault(t => t.Id == id);
    if (student != null) {
        _context.Students.Remove(student);
        _context.SaveChanges();
    }
}
  • Open the Properties\launchSettings.json and change the launchUrl value to api/students

It is now time to run the application. Hit CTRL-F5 on your keyboard. You will see the seed data appearing as JSON in the browser:

image

Testing with PostMan

If you do not already have the Chrome extension named “PostMan – REST Client”then take a moment and install it. Start PostMan in Chrome.

Retrieving data with GET method

  1. Enter URL like http://localhost:50932/api/Students
  2. Select GET
  3. Click Send button

image

Adding data with POST method

  1. Change from GET to POST
  2. Click Headers button
  3. Enter Header=Content-Type & Value=application/json
  4. Click Raw button
  5. Enter a JSON object like this: {"FirstName":"Jane","LastName":"Roberts","School":"Music","StartDate":"2014-10-09T00:00:00"}
  6. Click on Send.

image

Updating data with PUT method

Let us update the last record we added with Id=7. We will change the school to Tourism.

  1. Add /7 to the URL
  2. Change POST to PUT
  3. The JSON object will be complete with all data items, including Id. The only changed data is School=Tourism as follows:{"Id":7,"FirstName":"Jane","LastName":"Roberts","School":"Tourism","StartDate":"2014-10-09T00:00:00"}
  4. Click Send

image

 Deleting data with DELETE method
  1. Add /7 to the URL
  2. Change PUT to DELETE
  3. Click Send

image

Thursday, January 14, 2016

Deploy ASP.NET MVC 5 application to IIS using SQL Server 2014 LocalDb

Although not used in production scenarios, it is sometimes desirable to use a localdb database server with ASP.NET MVC 5 hosted by IIS. This post will help you setup this environment.

Download and install localdb 2014 and SQL Server 2014 Express

1) Download SQL Server Express 2014 from http://www.microsoft.com/en-us/download/details.aspx?id=42299
2) Choose the version that includes SQL Server Management Studio: ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
image
3) After you download SQL Server 2014 Express, extract launch the .exe file.
image
image
image
image
NOTE: If you do not see “LocalDB” the the features list, then after completing the current installation, return back to the download page and install “LocalDB 64BIT\SqlLocalDB.msi”.
image
image
image
I entered P@$$w0rd for the sa account. You can, of course, enter any other password. Make sure, though, that you remember this password as it is the admin account for SQL Server authentication.
Finally:
image
4) It is recommended that you pin the following applications to your OS taskbar:
  • SQL Server Management Studio
  • Internet Information Services (IIS) Manager

Deploy ASP.NET application with LocalDB to Full IIS

The following assumptions are being made here:
  • we are deploying a web application to a physical directory at C:\inetpub\wwwroot\MyWebApp
  • the website name in IIS is MyWebApp
  • we will access the site internally (on the server) using http://localhost:8055
  • we will access the site externally using host header asp.acme.com. This means that the website’s external URL is http://asp.acme.com.
1) Create a directory at C:\inetpub\wwwroot\MyWebApp
2) In IIS, create a new web site named MyWebApp and set the physical IIS path to the above directory.
  • To start with, use a port number like 8055 and no host header. This helps you trouble-shoot problems because detailed errors are displayed when accessing the website using localhost on any port number.
  • The name of the web site (in this case MyWebApp) determines the name of the application pool.
3) Configure Application Pool
  • In IIS, identify the application pool account by clicking on the “Application pools” node
  • Right click on the application pool account and select “Advanced Settings
image
  • Set “Load User Profile” to True.
image
  • Click OK
4) Publish site from Visual Studio
  • In VS.NET, open the solution. Right click the web project and choose Publish…
image
  • Choose Custom then give it a profile name.
image
  • On the next dialog, choose File System then set the “Target location” to a physical location somewhere on your computer’s file system
image
  • Click Publish:
5) Ensure that data files are published: Inspect the publish target directory. If the App_Data directory does not contain any files, do the following:
  • Return to Visual Studio and make sure that the database files in the App_Data directory are included in the project
  • In Visual Studio, select the App_Data folder. Right click and select “Publish App_Data”. This will copy over the database files to C:\inetpub\wwwroot\MyWebApp
6) Copy all the files in your publish target directory.
image
7) Paste the  files into the website directory on the IIS server. In the above example this would be: C:\inetpub\wwwroot\MyWebApp
image
8) Give C:\inetpub\wwwroot\MyWebApp\App_Data the appropriate access privileges:
  • In File Explorer, navigate to C:\inetpub\wwwroot\MyWebApp.
  • Right-click App_Data.
  • Select Properties >> Security tab
  • Click Edit
  • Click Add… on the “Permissions for App_Data” dialog
  • In the “Enter the object names to select” field, enter “iis apppool\MyWebApp”. Note that MyWebApp is the name of the application pool in question. This represents the “ApplicationPoolIdentity” assigned to our application pool.
  • Click on Check Names to verify that the account exists
image
  • Click OK
  • Select all permissions except “Full Control”
  • Click OK twice
5) If you are using LocalDB, ensure that the signature for the data source is “(LocalDb)\MSSQLLocalDB”’. Here’s a typical connection string that works with SQL Server Express 2014 LocalDB:
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB; Initial Catalog=my-catalog; Integrated Security=SSPI; AttachDBFilename=|DataDirectory|my-data-file.mdf;" providerName="System.Data.SqlClient" />
6) Testing that site works as expected.
  • Return to IIS
  • Select MyWebApp website
  • On the right-hand side click on Browse *:8055
  • When the website opens in a browser, carry out a task that involves database access (like registering a user). If all goes well and there are no errors then you will know that all is good. Otherwise, a detailed error message will display since we are using localhost.
7) Enabling external access to the website:
  • In IIS, click on the website
  • Click on Bindings on the right-hand side
  • Click on the Add button
  • Enter asp.acme.com for host name
image
  • Click OK
image
  • Click Close


Thursday, December 17, 2015

Building an ASP.NET 5 code-first MVC 6 app with EF7

Microsoft has embarked on a complete re-thinking of the ASP.NET MVC framework from version 5 to 6. Although most of the concepts, tools and approaches are similar, there is certainly lots that one needs to get familiar with if you want to work your way into MVC 6.
In this tutorial, I will build a simple ASP.NET MVC application using a Speaker model using Visual Studio 2015 and the Beta 7 version of the SDK.
At the time of writing, the latest version of the Visual Studio 2015 tooling for ASP.NET is version Beta 7. If you have not done so already, download the latest tooling for Visual Studio 2015 from https://www.microsoft.com/en-us/download/details.aspx?id=48738. Since I have a 64-bit computer, I chose the following two downloads:
image
If you have a 32-bit computer, you would choose DotNetVersionManager-x86.msi. Make sure you install DotNetVersionManager-x64.msi (or DotNetVersionManager-x86.msi) before WebToolsExtensionsVS14.msi. Note that the second download (WebToolsExtensionsVS14.msi) is a much bigger download and lakes much longer (around 30 minutes), so be patient.
It is very possible that some of the code in this post may need to change once the final version of ASP.NET MVC 6 is released. I shall attempt, as much as possible, to keep the code current.
Create a new ASP.NET 5 app in Visual Studio:
  • File >> New >> Project
  • Templates >> Visual C# >> Web >> ASP.NET Web Application
  • Give your application a suitable name. Name it MvcNext if you want the code below to match your environment.
image
  • Click on OK. On the next screen, under ASP.NET 5 Preview Templates, choose Web Application. This gives you a template with “Individual User Accounts” authentication. I also unchecked “Host in the cloud”.
image
  • After you click on OK, your app will get assembled. You will notice a new structure for both your solution and project. Highlights:
    • all configuration files are based on JSON rather than XML
    • The global.json file contains information about the solution, including the SDK version.
    • The project.json file holds information about the installed packages as well as other information about the project.
    • All static files that pertain to your web app are placed in the wwwroot folder. These include your CSS, JavaScript, and images.
    • The config.json file contains any configuration settings such as the database connection string.
  • When you run your application, you will see a different looking home page:
image
  • Like Node.js, ASP.NET 5 is modular and allows you to only use the required components for your web application.
  • Since Packet Manager Console will be used quite often, make sure the package manager is visible at the bottom of Visual Studio by selecting Tools >> NuGet Package Manager >> Package Manager Console:
image
  • Add the following Speaker class to the Models folder:
public class Speaker {
    public int SpeakerId { get; set; }
    [StringLength(40)]
    [Required]
    [Display(Name = "First Name")]
    public string FirstName { get; set; }
    [StringLength(40)]
    [Required]
    [Display(Name = "Last Name")]
    public string LastName { get; set; }
    [StringLength(15)]
    [Display(Name = "Mobile Phone")]
    public string MobilePhone { get; set; }
    [StringLength(50)]
    public string Email { get; set; }
    [StringLength(200)]
    [Display(Name = "Blog URL")]
    public string Blog { get; set; }
    [StringLength(15)]
    [Display(Name = "Twitter Handle")]
    public string Twitter { get; set; }
    [StringLength(40)]
    public string Specialization { get; set; }
    public string Bio { get; set; }
    [StringLength(200)]
    [Display(Name = "URL of Picture")]
    public string PhotoUrl { get; set; }
}


  • Resolve any required namespaces.
  • Also in the Models folder, create the following Entity Framework DbContext class named SpeakerContext:

public class SpeakerContext : DbContext {
    public DbSet<Speaker> Speakers { get; set; }
}


  • Add the following class named DummyData.cs - this class will help seed some initial data into the Speaker database entity:

public static class DummyData {
  public static void Initialize(SpeakerContext context) {
      if (!context.Speakers.Any()) {
          context.Speakers.Add(new Speaker { FirstName = "Richard", LastName = "Stone" });
          context.Speakers.Add(new Speaker { FirstName = "Anthony", LastName = "Lee" });
          context.Speakers.Add(new Speaker { FirstName = "Tommy", LastName = "Douglas" });
          context.Speakers.Add(new Speaker { FirstName = "Charles", LastName = "Brown" });
          context.Speakers.Add(new Speaker { FirstName = "Peter", LastName = "Mason" });

          context.SaveChanges();
      }
  }
}


  • Open the Startup.cs file and find this code in the ConfigureServices() method:.



AddDbContext<ApplicationDbContext>(options =>
                    options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

You will add code to identify the connection string that will be used for our SpeakerContext. After you add the following code just after the above code, make sure you move the ; (semicolon) to its new location:

.AddDbContext<SpeakerContext>(options =>
    options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));


  • Recompile your application.

What is DNVM?


DNVM is a version manager command line tool. DNVM allows you to configure your .NET runtime. Use DNVM to specify which version of the .NET Execution Environment you need at the process, user, or machine level.

To list available DNX runtimes:

dnvm list
To download and install the latest stable version of the regular .NET framework:
dnvm install latest

To install the latest 64bit CoreCLR:

dnvm install latest -r coreclr -arch x64

Switch to a Different Runtime for the Current Process

dnvm use 1.0.0-beta6 -r coreclr -arch x64

Upgrade runtime 32-bit runtime:

dnvm upgrade -arch x86 -r clr

If you want to remove older versions of the runtime, go to c:\Users\{your profile}\.dnx\runtimes

image

Simply delete the runtime versions that you do not need.

What is this DNX?


The .NET Execution Environment (DNX) is a software development kit (SDK) and runtime environment that has everything you need to build and run .NET applications for Windows, Mac and Linux. It provides a host process, CLR hosting logic and managed entry point discovery. DNX was built for running cross-platform ASP.NET Web applications, but it can run other types of .NET applications, too, such as cross-platform console apps.

What is DNU?


DNU is a command-line tool which provides a variety of utility to install and manage library packages in our application, and/or to package and publish our own application. Under the hood, DNU uses Nuget for package management and deployment.

Creating the EF7 Code 1’sr Migrations


1) Get the latest version of Entity Framework 7. Type the following command into the Package Manager Console:
Install-Package EntityFramework.SqlServer -Version 7.0.0-beta7 -Pre

We are now ready to add our initial migration. Open a Command Prompt inside of the project folder. I found the quickest way to do this is as follows:


  • Right-click on the project folder and choose “Open Folder In File Explorer” as shown below:

image


  • In File Explorer, select File >> Open Command Prompt. This opens a command prompt in the correct project folder.

To ensure that the correct version of the runtime (Beta 7) is being used in the command line window, enter the following:

dnvm use 1.0.0-beta7
Next we will add a migration specifying the context that we want. Bear in mind that there are two contexts (SpeakerContext & ApplicatioDbContext). Therefore, it is necessary to be explicit about which context we want to use.
dnx ef migrations add MyFirstMigration --context SpeakerContext
We have created a class with some dummy data. Let’s use it. In the Startup.cs file, add the following code to bottom of Configure() method.

using (var serviceScope = app.ApplicationServices
   .GetRequiredService<IServiceScopeFactory>()
   .CreateScope()) {
   
   var context = serviceScope.ServiceProvider.GetService<SpeakerContext>();

   DummyData.Initialize(context);
}
To apply the new migration to the database, run the following:

dnx ef database update --context SpeakerContext
At this stage, your database will have been created but not seeded. Unlike previous version of MVC, the database is not created in the App_Data directory. Instead, its is created inside the database server default data directory. You can view your database by using the SQL Server Object Explorer:
image
Data will be seeded once you run the application.

Creating the Controller


The current tooling for ASP.NET 5 in Visual Studio 2015 does not provide tooling for creating controllers based on a model class (I.E. Scaffolding). This could change once ASP.NET 5 is formally released. Meantime, we will create a controller class manually.


  • Right-click on the Controllers folder and a new Class
  • Name the class SpeakersController
  • Replace the class definition with the following code:
public class SpeakersController : Controller {
  private SpeakerContext _context { get; set; }

  [FromServices]
  public ILogger<SpeakersController> Logger { get; set; }

  public SpeakersController(SpeakerContext context) {
      _context = context;
  }

  public IActionResult Index() {
      return View(_context.Speakers.ToList());
  }

  public ActionResult Create() {
      ViewBag.Items = GetSpeakersListItems();
      return View();
  }

  [HttpPost]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> Create(Speaker speaker) {
      if (ModelState.IsValid) {
          _context.Speakers.Add(speaker);
          await _context.SaveChangesAsync();
          return RedirectToAction("Index");
      }
      return View(speaker);
  }

  public ActionResult Details(int id) {
      Speaker speaker = _context.Speakers
          .Where(b => b.SpeakerId == id)
          .FirstOrDefault();
      if (speaker == null) {
          Logger.LogInformation("Details: Item not found {0}", id);
          return HttpNotFound();
      }
      return View(speaker);
  }

  private IEnumerable<SelectListItem> GetSpeakersListItems(int selected = -1) {
      var tmp = _context.Speakers.ToList();

      // Create authors list for <select> dropdown
      return tmp
          .OrderBy(s => s.LastName)
          .Select(s => new SelectListItem
          {
              Text = String.Format("{0}, {1}", s.FirstName, s.LastName),
              Value = s.SpeakerId.ToString(),
              Selected = s.SpeakerId == selected
          });
  }

  public async Task<ActionResult> Edit(int id) {
      Speaker speaker = await FindSpeakerAsync(id);
      if (speaker == null) {
          Logger.LogInformation("Edit: Item not found {0}", id);
          return HttpNotFound();
      }

      ViewBag.Items = GetSpeakersListItems(speaker.SpeakerId);
      return View(speaker);
  }

  [HttpPost]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> Edit(int id, Speaker speaker) {
      try {
          speaker.SpeakerId = id;
          _context.Speakers.Attach(speaker);
          _context.Entry(speaker).State = EntityState.Modified;
          await _context.SaveChangesAsync();
          return RedirectToAction("Index");
      } catch (Exception) {
          ModelState.AddModelError(string.Empty, "Unable to save changes.");
      }
      return View(speaker);
  }

  private Task<Speaker> FindSpeakerAsync(int id) {
      return _context.Speakers.SingleOrDefaultAsync(s => s.SpeakerId == id);
  }

  [HttpGet]
  [ActionName("Delete")]
  public async Task<ActionResult> ConfirmDelete(int id, bool? retry) {
      Speaker speaker = await FindSpeakerAsync(id);
      if (speaker == null) {
          Logger.LogInformation("Delete: Item not found {0}", id);
          return HttpNotFound();
      }
      ViewBag.Retry = retry ?? false;
      return View(speaker);
  }

  [HttpPost]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> Delete(int id) {
      try {
          Speaker speaker = await FindSpeakerAsync(id);
          _context.Speakers.Remove(speaker);
          await _context.SaveChangesAsync();
      } catch (Exception ex) {
          return RedirectToAction("Delete", new { id = id, retry = true });
      }
      return RedirectToAction("Index");
  }
}

Adding The Views


We will start by creating the Index.cshtml view for our Index() action method.


  • Right-click on the Views folder and select Add >> New Folder
  • Enter Speakers as the name of the folder
  • Right-click on the Speakers folder and select Add >> New Item…
  • From the left menu select Installed >> Server-Side
  • Select the MVC View Page item template
  • Enter Index.cshtml as the name and click OK
  • Replace the contents of the Index.cshtml file with the following code:

Index.cshtml

@model IEnumerable<MvcNext.Models.Speaker>

@{
    ViewBag.Title = "Speakers";
}
<p><a asp-action="Create">Create New Speaker</a></p>

<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.FirstName)</th>
        <th>@Html.DisplayNameFor(model => model.LastName)</th>
        <th></th>
    </tr>
    @foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.FirstName)</td>
            <td>@Html.DisplayFor(modelItem => item.LastName)</td>
            <td>
                <a asp-action="Edit" asp-route-id="@item.SpeakerId">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.SpeakerId">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.SpeakerId">Delete</a>
            </td>
        </tr>
    }
</table>

Create.cshtml

@model MvcNext.Models.Speaker

<div>
    <form asp-controller="Speaker" asp-action="Create" method="post">
        <div asp-validation-summary="ValidationSummary.ModelOnly" class="text-danger"></div>
        <div class="form-group">
            <label asp-for="FirstName"></label>
            <input asp-for="FirstName" class="form-control" placeholder="First Name" />
            <span asp-validation-for="FirstName" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="LastName"></label>
            <input asp-for="LastName" class="form-control" placeholder="Last Name" />
            <span asp-validation-for="LastName" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="MobilePhone"></label>
            <input asp-for="MobilePhone" class="form-control" placeholder="Mobile Phone Number" />
            <span asp-validation-for="MobilePhone" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="Email"></label>
            <input asp-for="Email" class="form-control" placeholder="Email" />
            <span asp-validation-for="Email" class="text-danger"></span>
        </div>
        <input type="submit" class="btn btn-default" value="Create" />
    </form>
</div>

@section Scripts {
    <script src="~/lib/jquery-validation/jquery.validate.js"></script>
    <script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.js"></script>
}

Delete.cshtml

@model MvcNext.Models.Speaker

@{
    ViewBag.Title = "Confirm Delete";
}

<h3>Are you sure you want to delete this?</h3>

@if (ViewBag.Retry) {
    <p class="alert alert-danger">Error deleting. Retry?</p>
}

<div>
    <dl class="dl-horizontal">
        <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
        <dd>@Html.DisplayFor(model => model.FirstName)</dd>

        <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
        <dd>@Html.DisplayFor(model => model.LastName)</dd>
    </dl>

    <div>
        <form asp-controller="Speaker" asp-action="Delete" method="post">
            <div class="form-group">
                <input type="submit" class="btn btn-default" value="Delete" />
            </div>
        </form>

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

Details.cshtml

@model MvcNext.Models.Speaker

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>
<div>
    <dl class="dl-horizontal">
        <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
        <dd>@Html.DisplayFor(model => model.FirstName)</dd>

        <dt>@Html.DisplayNameFor(model => model.MobilePhone)</dt>
        <dd>@Html.DisplayFor(model => model.MobilePhone)</dd>

        <dt>@Html.DisplayNameFor(model => model.Email)</dt>
        <dd>@Html.DisplayFor(model => model.Email)</dd>

        <dt>@Html.DisplayNameFor(model => model.Blog)</dt>
        <dd>@Html.DisplayFor(model => model.Blog)</dd>

        <dt>@Html.DisplayNameFor(model => model.Twitter)</dt>
        <dd>@Html.DisplayFor(model => model.Twitter)</dd>

        <dt>@Html.DisplayNameFor(model => model.Specialization)</dt>
        <dd>@Html.DisplayFor(model => model.Specialization)</dd>

        <dt>@Html.DisplayNameFor(model => model.Bio)</dt>
        <dd>@Html.DisplayFor(model => model.Bio)</dd>

        <dt>@Html.DisplayNameFor(model => model.PhotoUrl)</dt>
        <dd>@Html.DisplayFor(model => model.PhotoUrl)</dd>
    </dl>
</div>
<p>
    <a asp-action="Edit" asp-route-id="@Model.SpeakerId">Edit</a> |
    <a asp-action="Index">Back to List</a>
</p> 

Edit.cshtml

@model MvcNext.Models.Speaker

<div>
    <form asp-controller="Speaker" asp-action="Update" method="post" asp-route-id="@Model.SpeakerId">
        <div asp-validation-summary="ValidationSummary.ModelOnly" class="text-danger"></div>
        <div class="form-group">
            <select asp-for="SpeakerId" asp-items="@ViewBag.Items"></select>
        </div>
        <div class="form-group">
            <label asp-for="FirstName"></label>
            <input asp-for="FirstName" class="form-control" />
            <span asp-validation-for="FirstName" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="LastName"></label>
            <input asp-for="LastName" class="form-control" />
            <span asp-validation-for="LastName" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="MobilePhone"></label>
            <input asp-for="MobilePhone" class="form-control" />
            <span asp-validation-for="MobilePhone" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="Email"></label>
            <input asp-for="Email" class="form-control" />
            <span asp-validation-for="Email" class="text-danger"></span>
        </div>
        <input type="submit" class="btn btn-default" value="Save" />
    </form>
</div>

@section Scripts {
    <script src="~/lib/jquery-validation/jquery.validate.js"></script>
    <script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.js"></script>
}

Adding a Speakers link to the main page


Add the following link to the navigation in the _Layout.cshtml file located in the Views >> Shared folder:
<li><a asp-controller="Speakers" asp-action="Index">Speakers</a></li>

Let’s try it out


You can now run the application.

image

image