Click here to Skip to main content
Click here to Skip to main content

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

, 20 Nov 2012
Rate this:
Please Sign up or sign in to vote.
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.

License

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

About the Author

SEJohnson
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).
 
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.
 
My main job over the past 20+ years has been computer networking, servers and security where I'm well known in the industry after catching numerous hackers and their subsequent prosecution. I used my programming skills and networking knowledge to write custom code for the purpose of catching hackers. In my network consulting role I wrote numerous utilities to repair issues I encountered for my clients.
 
Currently I use C#/C/C++, Flex, AS3, Coldfusion, PHP, jQuery, VBscript, and Powershell in Windows and Linux on MSSQL or MySQL.
 
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 schools.
 
Outside of technology I love aviation and am a private pilot.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberpaulofloresjunior7-Mar-13 1:21 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 20 Nov 2012
Article Copyright 2012 by SEJohnson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid