Azure database, database project and EDMX

In the previous article we have created Solution that has Web Role. It allows us to publish this solution on Windows Azure. Also, in this solution we have a connection to created storage account in Azure. We have log manager Log4net that allows us to write some logs in Blob table. In last article we have created a database in Windows Azure SQL server and a database project for this database. So now we can start to use this database in our solution. We will use Entity Framework for this purpose. Entity Framework allows generating entity classes from the database. 

EDMX

We will use EDMX file to do this. A .edmx file is an XML file that defines a conceptual model, a storage model, and the mapping between these models. An .edmx file also contains information that is used by the ADO.NET Entity Data Model Designer (Entity Designer) to render a model graphically. The recommended practice for creating an .edmx file is to use the Entity Data Model Wizard.

In the first step we should open our “MvcApplicationWebRole” solution. Now let’s add a new class library in our solution. Right click on the solution then “Add – New project”. In occurred window enter name “MvcApplicationWebRole.Database” and select “Class Library” as project type:

Add new project in solution

Add new project in solution

  

Delete item “Class1.cs” from this class library. We do need this. Now right click on this “MvcApplicationWebRole.Database”, select “Add – New item”. A new window will occur. Find template “ADO.NET Entity Data Model”. As name enter “DatabaseContext” and press button Add (Type of model). A new window will occur. Select “Generate from database” and press Next:

 Type of model

Type of model

 

Press next. A new window will occur. In this window we should select our source database “MvcApplicationWebRoleDatabase”:

Source database to generate model from

Source database to generate model from

 

Press button “Next”. A new window will occur. In this window we should select items to be used during generation. We will select just tables. Not “Views” or “Stored Procedures and Functions”:

Items to be used during generation

Items to be used during generation

 

After press button “Finish” we will receive a .edmx file in our solution. Also, It will show our database diagram:

Database diagram

Database diagram

 

We will rename generated class in .edmx file from “AzureDatabaseConnectionString” to “AzureDatabaseContext”. Also, we need to check our connection string in App.config file. By default it has next value:

Auto generated connection string

Auto generated connection string
 
We need to move this connection string in Web.config file of our MvcApplicationWebRole project:
Move Connection String to Web.config file
 
Move Connection String to Web.config file
 
Also, we changed the key for connection string from “AzureDatabaseContext” to “AzureConnectionString”. To make it works, we should also change class “AzureDatabaseContext”:
Change key for connection string in AzureDatabaseContext
Change key for connection string in AzureDatabaseContext

Database initializer

So now we have finished all basic actions and can work with the database in our solution. Let's create a simple controller and add few lines of code that will show how to use all this database stuff (Call Database initializer).
But let’s do last and create database initializer. It will set default data to our database in moments – depend on what interface it inherits: DropCreateDatabaseIfModelChanges or DropCreateDatabaseAlways:
Database initializer
Database initializer
 
We should call this database initializer. During developing it could be the constructor of AzureDatabaseContext. But when a site in production – better call it from Application_Startup method:
Call Database initializer.
Call Database initializer
 
Now let’s add some code to show how to work with database:
Add document and person to database
Add document and person to database
 
Now we can run solution. Go to “PersonManager” controller. After this step we can check our database for changes:

Data in database

Data in database


Best Regards. Andrew Tsopych CTO of Diceus

Loading