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

  • Article

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 data3. 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)

 

snap0014

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

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.
snap0016

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.
snap0017

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.
image

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
snap0020
snap0022

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.
snap0023

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

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.
snap0026

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:

image

snap0027

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".”
snap0028

Navigate to the Projects tab and select DataLayer.
snap0029

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

 

snap0031

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

 

snap0033

Do the same thing for the DataLayer project.
snap0034

 

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
snap0037

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
snap0038

 

snap0039

 

snap0040

Now get ready to paste the connection string.
snap0041

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

That’s the correct spot.
snap0043

We will add a stored procedure to insert some data.
snap0045 
 ALTER PROCEDURE dbo.AddData
AS
    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')
    RETURN

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

 

snap0047

We have some live data.
snap0048