Linking to an Existing SQLDB in ASP MVC4.0 and EF5.x

I spent quite a bit of time trying to link Visual Studio 2012 C# with an existing database using MVC4 and EF5.x. All of the examples I could find were allowing Entity to create and manage the database which is not something I wanted to do. I have numerous databases I want to start writing front ends for.

Surprisingly enough, when I did figure it out it was amazing how much work Visual Studio did for me to build a rough data-linked site in minutes. The default CSS for the site looks great on cells as well as computer displays.

First step is to load VS2012 and create a new project based off of Web – ASP.NET MVC Web Application; for this demo we will call it "ExistingSQL".

The next window asks what type of template you want to use. Internet application will use form based authentication and Intranet application will use AD/Windows authentication. The others you can read about on Microsoft’s site. For this application we will pick Intranet application.

Leave everything else default.

Once the project is created we will see a readme.txt showing what must be done in IIS. We’ll just close that file and remember it’s there if you ever want to move this program to a full blown IIS server. We don’t have to worry about this as we’re running IIS Express in VS 2012.

One thing we need to do is enable and enforce Windows authentication. Select the project in the solution explorer and in the property windows below select the "Windows Authentication" to Enabled.

In our SQL server we have a database I want to use on the web called Aviation

Let’s set this up as a data connection in the project.

In VS 2012 select View – Server Explorer

Then right click on "Data Connection" and select "add connection…" and select "Microsoft SQL Server"

Click on Continue.

The next window asks us how we connect to our database server.

For the server name enter: .\SQLExpress

Leave "Use Windows Authentication" checked. If you’re wanting to secure this down further you’ll want to look at disabling the "Anonymous Authenticaiton" as well.

Then in the drop down box select the Aviation database.

Test the connection if you’d like.

Click on OK to save the database connector.

In the Server Explorer you’ll now see the data connecter we just created.

The next step is to create the Entity Data Model.

In the solution explorer right click the "Models" folder and "Add - New Item…".

Select Data – ADO.NET Entity Data Model

For the name, call it the same as your database, in our case it’s "Aviation.edmx".

Click on Add.  

Now we will have to generate the Entity classes from the database.

Click on Next.

This window links your Entity classes with the connector we created in the last step.

Click on Next.

Now we can select the different schema from the existing SQL database.

If we had more tables then they would show in the window above.

Select the "Airplanes" table and click on Finish.

When that closes we will see the database appear in the programming window.

Again, if we had more tables they would show here as well as their relations if they existed.

Before we can continue we need to COMPILE this project to have it build the models. Right click on the project "ExistingSQL" and Build.

In the solution explorer you’ll see the following model:

Now we will add the controller to build the views to manage this table.

Right click the "Controllers" folder in the solution explorer and select "add – Controller…"

Name the controller "AviationController"
Select the model class: Airplane(ExistingSQL.Models)
Select the data context class: AviationEntities(ExistingSQL.Models)

Then click on Add.

In the solution explorer you will see the following controller:

Now you’re ready to try the new application out.

Click on "Play" and your internet explorer will show with a the base site:

In the URL window add in "Aviation" to the end of the URL

Cool eh? It took me about 10 minutes to write and create this framework of site to an existing DB.

Now if we want to enable the links in that window we need to do the following….

Open the Index.cshtml file under ExistingSQL-Views-Aviation and scroll to the bottom and look for the following.

@Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })

Add in the PrimaryKey, note the code hinting works in item.

@Html.ActionLink("Edit", "Edit", new {  id=item.pkAPID }) |
@Html.ActionLink("Details", "Details", new { id=item.pkAPID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.pkAPID })

Then rerun the application and click on "Edit"

That’s it. This is a really quick and dirty link into an existing SQL DB. It’s a lot easier than I was originally making it out to be. Granted you’ll probably want to add some security and change up the display a bit. Every bit of this example creates the MVC.