Click here to Skip to main content
15,868,419 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (12 votes)
20 Nov 2012CPOL4 min read 44.5K   9   4
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".

 Image 1

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.

 Image 2

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

 Image 3

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"

 Image 4

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.

 Image 5

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.

 Image 6

The next step is to create the Entity Data Model.

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

 Image 7

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.

 Image 8

Click on Next.

 Image 9

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.

 Image 10

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.

 Image 11

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:

 Image 12

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…"

 Image 13

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:

 Image 14

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

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

 Image 15

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

 Image 16

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"

 Image 17

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect NetProf, Inc
United States United States
Programming since 1977 (8 years old) Started on the commodore pet (Basic and Assembly) and progressed through the Atari 400/800, Apple II/e/c, PC DOS (Pascal, C, C++, Assembly). Computers have been a passion my entire life.

During my early pre-business years I wrote two BBS systems and a MUD from scratch, a few BBS doors, and managed code from a custom circle MUD.

I've been in the computer industry for over 30 years in computer programming, networking, servers and security where I'm well known in the industry after catching numerous hackers and their subsequent prosecution.

Currently I use C#, Angular, jQuery, and JavaScript. Creating server services, background processing, web services, and data exchanges.

But have also used C/C++, VB, VBscript, PHP, and Powershell in Windows and Linux and MSSQL, MySQL, or Postgres. I've been spending a lot of time in Azure as well.

I still consult but I'm working more as an entrepreneur now creating businesses with technology and helping others realize their ideas where they lack the technical experience to bring their idea to fruition.

My current project is a security product that automates security in an active directory environment for businesses and education including IDM, SSO, and a self-services web portal.

Outside of technology I love aviation and am a private pilot.

Comments and Discussions

 
GeneralSimple Awesome !! Pin
byfour14-Sep-14 21:12
byfour14-Sep-14 21:12 
QuestionThanks and Little bit more.... Pin
anrorathod24-Aug-13 9:08
anrorathod24-Aug-13 9:08 
QuestionSimple & Clear Pin
ItalloSan21-Mar-13 9:15
ItalloSan21-Mar-13 9:15 
GeneralMy vote of 5 Pin
paulofloresjunior7-Mar-13 1:21
paulofloresjunior7-Mar-13 1:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.