Connecting an ASP.NET MVC Web App with SQL Azure

0 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 0 0 Flares ×

azure new database

I thought I’d follow up my previous article on how to create and deploy an asp.net mvc web app to azure by writing about how to get that mvc web app and connect it to SQL Azure.

SQL Azure is Microsoft’s SQL database hosted in the cloud. It is a full relational database and is hosted in Azure data centres. It is very siilar to SQL Server, with a few differences and is a great way to take existing SQL skills to the cloud.

What we’ll do in this walkthrough is use the app from my previous article which is a standard asp.net template, and connect it to a SQL azure database, whilst maintaining a local database for development offline.

Series: ASP.NET Web Apps on Windows Azure

Where we left off

Last time with an asp.net mvc website building and deployed to a windows azure website. However, features such as registering and signing in were not configured, and as such did not work. If you viewed you app in the browser it should look something like this:

App on Azure

One thing to note about this app is that if you try to register an account you’ll be presented with a message telling you that an error has occurred. This happens as registering an account is an activity that requires database activity. If you try to run this web app directly from visual studio you’ll find that it works perfectly well – so whats going on?

The template we used to create our web app is configured to use LocalDb. LocalDb is an easy to install and management free version of sql server that requires no installation. LocalDb creates a mdf and ldf files within the App_Data folder of your app, which are the main database and log files. Windows azure websites do not allow you to use LocalDb ao the most common thing to do to resolve this is use a SQL Azure database.

Creating the SQL Azure Database

The first thing to do is creat your sql database on azure:

  1. go to your azure portal, and click the new button in the bottom left corner
    Azure New Button
  2. Then choose Data Services >> SQL Database >> Quick Create and fill in the form. My settings were:
    a. Database Name: myfirstwebappdb
    b. Server: Create New
    c. Region: North Central US
    d. Make sure you remember the username and password you choose
    e. Press “Create Database” button
    New SQL Database on Azure
  3. After waiting a few seconds your database should be created.
  4. Click SQL Databases on the left column, then select your database in the main pane
    Select Azure Database
  5. Go to the database dashboard
    Azure Database Select Dashboard
  6. Once on the dashboard you need to ensure that other azure services are allowed to communicate with you database (this should be set by default). If this option is not selected the firewall on the sql azure database will disallow connections from your website:
    a. Select ‘Manage allowed IP addresses’
    Azure Database Select Manage Allowed IPs
    b. Ensure that ‘Windows Azure Services’ under ‘allowed services’ is set to Yes
    Azure Dastabase Azure Services Allowed
  7. Finally you need to get the connection string for the database:
    a. From the database dashboard (see point 5), select “Show connection strings”
    Azure Database Select Show Connection Strings
    b. On the window that pops up copy the connection string labelled ‘ADO.NET’ for later use:
    Azure Database Connection String

Your database is now set-up and ready for you to configure in your web app.

Updating Connection Details in Your Web App

The next step is to update you mvc appication to point at the newly created sql azure database. There is one major thing we should take note of here though – generally speaking when we’re developing and working on a web app its better to be working locally (i.e. not connecting to the azure database). There are several reasons for this:

  • Any communication with sql azure will have to happen over an internet connection, and will be comparatively slow to a local connection. Once you’ve deployed your website to azure this problem goes away as it is likely that your database will be located in the same data-centre as your website.
  • Developing directly against the sql azure database will also require a constant internet connection, so you cannot work offline (or work at all if you temporarily lose your connection). developing against a local database alleviates this.
  • Finally sql azure costs money – although the volumes may be low, it all adds up!

So the changes we make will allow us to continue to work off LocalDb when developing, but have our azure hosted website point at out sql azure database. To do this we’re going to take advantage of web.config transformations.

The web.config is the main configuration point for any asp.net website – its where you can store application settings, connection strings and much more. Web.config transformations allow us to change these settings seemlessly based on predetermined criteria.

  1. Within your visual studio project, find the file called Web.Release.config and open it
    Web.Release.Config in Solution Explorer
  2. Add the following code into the file directly below the ‘cofiguration’ tag
  3. Ensure that you replace ‘enter your connection string here’ with the one copied from sql azure earlier
  4. There should also be a placeholer inside the connection string that read ‘{your_password_here}’ – this should be replaced with the password you set earlier.
  5. Save the file

How does this work?

The connection string represents a set of instructions about how your app will find and access a database. The connection string we provided tells the app how to get to the sql azure database we just created. This connection string will only be used when in the ‘Release’ configuration – note that we have 2 configurations ‘Debug’ and ‘Release’, debug is for local development, release is for our production environment.

The connection string for our LocalDb is contained within the Web.config file, and is used by default. When we decide to build and publish the release configuration, the instructions we entered into the Web.Release.config will replace those in the Web.config. Whenever we publish to azure, we are always using the release configuration.

What do the instructions in the Web.Release.config actually mean?

The xml code we entered in the Web.release.config roughly says this

  • Within the connectionStrings element
  • Locate the element using ‘Match(name)’: locate any elements with the name attribute that matches the name attribute on this element, i.e. ‘DefaultConnection’
  • Transform those elements using ‘SetAttributes’: set all attributes equal to the attributes on this element, i.e. set the connectionString attribute to our sql azure connection string.

These transformations aren’t written to disk, but simply applied as the website is published.

Deploy you website

To deploy your website you should just be able to right click on your project in the solution explorer, click ‘Publish…’ and follow the wizard through. for a more detailed step-by-step, see Deploying to the Azure Website.

On thing worth noting is within the publish dialog, if you go to the ‘Settings’ tab you should notice that the configuration selected says ‘Release’:

Release config select in publish dialog

Wrapping up

After deploying your website you should now be able to register an account and sign-in to your application hosted on azure. You newly created username and password will be stored within your sql azure database, and your development environment kept completely independent.

Windows azure is a fantastic option for hosting websites, creating deploying and managing sites is made easy and smooth, allowing you to focus on developing your apps. In future posts I’ll go over more features of azure such as table storage, sending emails and mobile services.

I’d love to hear if there are any topics you’d like me to cover, so please get in touch!

  • Yamilet Contreras

    this is very useful, thank you very much 🙂

  • Andres

    If we work on a local database when developing a project does this mean we need 2 databases (the local one and the azure one) and we have to create tables twice?

    • In short yes – its pretty much essential practice not to do development work on your live database.

      How you move those change from your development database to your live one is a much more complicated question. You could do it manually, script it or use some database deployment tool.

      Its also good practice to source control the database too, but thats whole different story!

  • james

    after updating details in your web app step i have encounted an error when publishing.
    it says ‘transform and locator attributes must contain only type name, or a type name followed by a list of attributes in parentheses’. Anyone else get this problem?
    Thanks

  • oladapo

    how do i connect to the tables in the database. is it dat my table and database on local db will be the same on azure db, then after programming , i can just change the connection string .pls i need a reply urgently

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
0 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 0 0 Flares ×