Creating an On Premise Database and MVC Web Application and Migrating to Windows Azure and SQL Azure-Step 2 of 10

This post is going to focus on data. There are a few things we need to do with our data:

1. We want to create a database and insert data

2. We want to make use of the ADO.NET Entity Framework.

It will help us tremendously when we want to write queries and retrieve data

3. We want to expose the data so that our MVC Web Application can consume it

Let’s start by adding a new “Class Library” project. We will call it “DataLayer.”

Remember, we are adding a new project to our MVC Application.

Our solution has 2 projects:

1. MVC Web App

2. Class Library (DataLayer)



We won’t need Class1.cs. So delete it and “Add a new item.” To our data layer project.


We need to add an “ADO.NET Entity Framework” to our DataLayer project.

A key point is that we have no database. We have nothing. We are creating everything from scratch.

Why ADO.NET Entity Framework?

A primary goal of the ADO.NET Entity Framework is to raise the level of abstraction for data programming, thus helping to eliminate the impedance mismatch between data models and between languages that application developers would otherwise have to deal with.

To create a database, do the following:

Choose Empty Model from the Entity Data Model Wizard as seen below.


The entity model is added to Solution Explorer as an .EDMX file in Solution Explorer and a new tab (named Model Browser) that gives you a tree view of the model's components.

To create your entity model, you drag an Entity control from the Toolbox onto the design surface of your .EDMX file, give the Entity some properties, map the Entity to a table (or a view or stored procedure), and finish by mapping those properties to columns in the table.


First, Be sure to rename your entity to “Stocks.”

Next, you’ll then add the properties to the Entity to represent the data that will be stored in the database.

You get one property free (named Id with a datatype of Int32).

To add new properties, just right-click on the Entity's Scalar Properties bar and select Add | Scalar Property. You can name each property and set its data type in the Properties window. These scalar properties will end up as columns in our Stocks table.


At this point we are ready to generate the database. As you can see, we have added “TickerSymbol” and “Description” to our entity.

Table = Stocks

Column 1 = TickerSymbol

Column 2 = Description


The important work that the ADO.NET Entity Framework provides is in ModelStocks.edmx.

The ADO.NET Entity Framework provides tools to automatically generate an object layer based on the conceptual schema definition language (CSDL) content of an .edmx file.

When data classes are generated by the Entity Data Model tools, they are implemented in partial classes.

You can add functionality to objects by extending the generated partial data classes.

Having partial classes enables you to extend these classes with custom methods and properties in a separate source file without having to worry about losing your customization when the generated files are refreshed.


Notice that we automatically inherit from “EntityObject.” Our Stocks object represents our table.


Notice the code that has been generated for us. It provides a constructor to create a “Stocks” object for us. As developers we don’t need to write a bunch of “plumbing” code.


There is still one more step to actually create the “Stocks” table in the database. Below you are looking at some DDL code that was generated for us. We just need to run it. To run the ModelStocks.edmx.sql file, simply right mouse click as follows:



Our web application (MVcOnPremise) now needs to connect to our data layer. That means we will need to set some references. Simply right mouse click from the “References” hive of solution explorer and select “Add Reference".”


Navigate to the Projects tab and select DataLayer.


We need to add yet another reference for System.Data.Entity




Throughout this tutorial we will need to remember to change the properties of System.Data.Entity to “Copy Local”




Do the same thing for the DataLayer project.



Key Point about the connection string

There is a very important connection string that we want to manage properly. The thing to remember is that the connection string needs to live in two places:

1. The App.config file of the DataLayer project
2. The Web.config file of the MvcOnPremise project


The next few screens cover the following:

1. Open up app.config from the DataLayer project
2. Copy the connection string
3. Open up web.config from the MvcOnPremise project and paste in the connection string






Now get ready to paste the connection string.


Be sure to paste the connection string to the right spot in web.config


That’s the correct spot.


We will add a stored procedure to insert some data.

    insert into dbo.Stocks(TickerSymbol, Description) 
        values ('ABC', 'The ABC company')
    insert into dbo.Stocks(TickerSymbol, Description) 
        values ('DEF', 'The DEF company')
    insert into dbo.Stocks(TickerSymbol, Description) 
        values ('GHI', 'The GHI company')

Save and run the stored procedure so we can populate our tables.




We have some live data.


Comments (0)

Skip to main content