SQL Compact

This article I wrote using Host ASP.NET MVC Apps on Azure WebSite Without Spending a Cent on Databases

Start new project, choose MVC4 (probably it works with MVC3 also, but I was playing with MVC4).

Right click on App_Data -> Add -> New Item:

Then select SQL Server Compact 4.0 Local Database:

Then, open server explorer and click refresh:

With this you will be connected to sql compact.

Now, right click on database (in my case SqlCompact.sdf), and click on properties:

And there you can see connection string:

There you can see connection string, but instead of full path we will use |DataDirectory|, so in my case connection string looks like: 

<connectionStrings>
	<add name="DefaultConnection" connectionString="Data Source=|DataDirectory|SqlCompact.sdf" providerName="System.Data.SqlClient"/>
</connectionStrings>

Note that providerName is System.Data.SqlClient, not System.Data.SqlServerCe.4.0, that is because I was receiving error:

Unable to retrieve metadata for 'SqlCompact.Models.SqlCompact'. Using the same DbCompiledModel to create contexts against different types of database servers is not supported. Instead, create a separate DbCompiledModel for each type of server being used.

Taken from here, later we will go back to System.Data.SqlClient. Also, note that Data Source is without slashes, I will show you later why.

Now lets try to create controllers and views, using scaffolding.

First add model like: 

using System.ComponentModel.DataAnnotations;
using System.Data.Entity;

namespace SqlCompact.Models
{
    public class SqlCompactTable
    {
        [Key]
        public int Id { get; set; }
        public string MyName { get; set; }
    }

    public class DefaultConnection : DbContext
    {
        public DbSet SqlCompactTable { get; set; }

        public DbSet SqlCompacts { get; set; }
    }
}

It seems that EF doesn't like name of the column to be "Name", that's why I am using "MyName", also it seems that table will not be automatically created, so I created it manually, where I put ID as identity column.

Create controllers:

After creating controller change connectionString to use System.Data.SqlClient, something like:

<connectionStrings>
	<add name="DefaultConnection" connectionString="Data Source=|DataDirectory|SqlCompact.sdf" providerName="System.Data.SqlClient"/>
</connectionStrings>

Otherwise, when you start application, you will receive error like:

Exception Details: System.ComponentModel.Win32Exception: The network path was not found

My application you can download from here. Version with created table, few records, and prepared for publishing is here.

Next thing is to deploy application to windows azure.

Go to manage.windowsazure.com, register / sign in -> click on web sites -> click new:

 Click quick create, and in url write name of your web site:

Now, click on your web site (in my case milosev), and click download the publish profile:

Now, right click on your project, and click publish:

 Click on import:

And choose file which you downloaded in previous step. After you can click publish, or you can go further, by clicking next and change the setup as you like.

In your Web.config add <customErrors mode="Off"/> under the <system.web> node, to be able to see errors, and if you are receiving error like:

Unable to find the requested .Net Framework Data Provider.  It may not be installed.

Then you will have to install EntityFramework.SqlServerCompact, using NuGet:

 Taken from here.