Clouds have been on the IT horizon for a while, but now, they are on us and well and truly raining down with services and features on a daily basis! Yes, I am talking about the ‘Cloud Computing’ kind of clouds not the ‘Cumulonimbus’ clouds!
In the last couple of articles, we have showed you how to deploy your ASP.NET applications on Azure’s IaaS offering Azure Websites. You may have noticed we’ve not talked about Databases in them and in fact the sample application doesn’t use a database. Why? Well while we try the Azure service out, we would stay away from cost incurring pieces of Azure and Databases, unlike AzureWebsites, are not free. Now, cost for a piece of infrastructure is perfectly valid, but often our needs don’t justify the cost. For example if we have a low traffic hobby site that we would like to host on Azure Web Site, having a free alternative to SQL Server is welcome!
Today, we’ll see how we can use SQL Server Compact 4.0 as a backing store for our ASP.NET web app. Thanks once again to Sumit Maitra for co-authoring this article with me.
Building and Deploying a Sample ASP.NET MVC Application on Azure
Let’s start off with a simple MVC App.
- To add some pizazz, we’ll use one of the new SPA templates and call our App ‘TwoNotes’ (as opposed to the real thing called ‘One Note’).
- We use the regular ‘Single Page Application’ template
- Once the App is ready, we right click on the App_Data folder and select Add > New Item. From the Add New Item dialog, we’ll select SQL Server Compact 4.0 Local Database.
Note: You can skip this step and simply setup a correct DB Connection, the DB will get created at runtime the first time around.
- This will add a reference to the System.Data.SqlServerCe assembly
This however does not come from Nuget. If it does not come from Nuget, how can we be sure that the Azure VM will have this dependency? We can’t be and in fact it is not available by default on Azure WebSites. The following steps become very important now, whether you deploy from Git or deploy using Web Deployment Wizard.
Setting up SqlServerCompact for proper deployment on Azure WebSites
Step 1: Get the SqlServerCompact dependencies
Open up the Package Management Console and fire the following request
PM> Install-Package SqlServerCompact
It will first check if you have the above dependency in place. If not, it will add it (from GAC) and then download a bunch of dependencies for SqlServerCompact to work correctly on Azure Web Sites as we can see below.
Once Nuget has added these dependencies by enabling Nuget Package Restore, we can be rest assured these will be downloaded when not available.
Step 2: Next we have to add Reference to System.Data.SqlServerCe.dll such that it is available at build/deploy time.
The System.Data.SqlServerCe reference that we saw above, does not come from Nuget, so do the following:
- Select it from the Reference folder and hit F4
- In the properties panel, set the “Copy Local” property to True. Now rebuild the app.
- Make sure the bin folder is visible in Solution Explorer by clicking on Show all files and from the bin folder, Copy (Ctrl+C) the System.Data.SqlServerCe.dll to the _bin_deployableAssemblies folder.
- Remove reference of the DLL from the project.
- Add the reference back by browsing to the above folder and selecting the dll you just copied.
Step 3: Finally install Entity Framework Support SQL Server Compact using the following Nuget command.
PM> Install-Package EntityFramework.SqlServerCompact -Version 4.3.6
With that all the dependencies for SqlServerCompact dependencies are set. Let’s go ahead with the app now.
The SQL Compact Connection String
We open the Database Explorer, expand the Data Connections node, select TwoNotes.sdf and click on the Refresh button. This will initialize the connection and the Node will show a Green plug instead of a red cross.
- Now right-click on it and select Properties. This will select the Properties pane with data similar to what’s shown below
Note that the Connection String only has Data Source=”<Full Path>”. Also the Provider information is not Assembly information, rather a description! So how do we get the connection string?
- Open the web.config and replace the existing DefaultConnection string with the following
<add name="DefaultConnection" connectionString="Data Source=|DataDirectory|\TwoNotes.mdf"
providerName="System.Data.SqlServerCe.4.0" />
As we can see above, we are using the |DataDirectory| notation to retrieve the Application Path + App_Data folder and then adding the name of our DB to complete the path. The Provider Name took a little bit of searching and the correct values as we can see is “System.Data.SqlServerCe.4.0”.
The App and the DB
- Now let’s run the App and Ta-Da! The default login page comes up
- We have to register first, so we’ll create an account with the follow credentials and log in
UserName: twonotestest
Password: tw0Not3sTest
- After a brief pause for the first time to create the required tables, the system logs in and we are presented with the following
- Mark the first two Items as complete and add a Third Todo Item (and tab out to commit). Switch back to the Database Explorer in Visual Studio. Expand the Tables Node for the DefaultConnection (TwoNotes) and there you see all the required tables for both Authentication as well as the Data Items for the ToDo list in place.
- Right click on the TodoItems table and select ‘Show Table Data’. You’ll see something similar
The first two items are marked IsDone=True whereas the Third one is added with IsDone=False.
So now we have the app fully functional at our end. Let’s commit the code to GitHub and Pull the source into Azure via GitHub integration
Checking in code to Source Control
I am assuming here that you have read our GitHub with Visual Studio 2012 integration article and are on the cutting edge here. If you don’t use GitHub, feel free to use your favorite source control. It’s likely to be supported by Azure. If you don’t want source control integration and want to do the old style Web Publishing Wizard, we are game there too. Essentially put your code in GitHub/BitBucket/DropBox/TFS or SkyDrive. Azure can pull it from all these locations. Here, I am assuming our code is in GitHub.
Make sure the TwoNotes.sdf file is checked into source control, some Version Control Clients tend to ignore it.
Deploying to Azure by pulling build in from GitHub
For details of how to do this is already explained in our previous article. Essentially select the website, choose to Integrate with Source Control, Select Git Hub, give Azure permissions to your GitHub Repo.
Finally pick the correct repository and branch and hit ‘Complete’. Azure will start the deployment immediately.
If all goes well, deployment will succeed and you’ll get the following Message
If you note above, the two Deployment IDs are different and that’s because my first deployment failed. Some Git clients leave out the App_Data folder by default that resulted in a build failure for me. So if you get a failure, check that out. Also make sure you have not missed the step to add the Nuget Package for SqlServerCompact and its Entity Framework dependencies.
With everything deployed, let’s visit out site and see if the data we had in Dev has moved over or not. We navigate to http://twonotes.azurewebsites.net/ and login using same credentials as earlier! Bingo!
Deploying using Web Publishing Wizard
If deploying via source control is too cutting edge for you, you could always deploy using Web Publishing Wizard. Simply download the publishing profile, Start the Web Publishing Wizard and deploy! Easy!
Wrapping up
Even though it seemed rather seamless, there are a few rough edges to deploying SqlServerCompact. You have to remember that the System.Data.SqlServerCe.dll doesn’t get uploaded automatically, so you’ve to somehow get it into the bin folder at runtime. We showed you one way. Also worth keeping in mind is the little documented requirement for the EntityFramework for SqlServerCE. It usually works in Dev environment without that particular dependency added explicitly, but bombs in production.
There you go, all the caveats we found, we’ve shared and now you have a Web Application Hosted on Azure WebSites for free and using a SQL Backing Store that you are not paying for separately!
Download the entire source code of this article (Github)
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.
Get in touch with him on Twitter @suprotimagarwal or at LinkedIn