Walkthrough: Expose SQL Compact data to the world as an OData feed, and access SQL Compact data from Excel 2010 and Silverlight

The Open Data Protocol (OData) is a Web protocol for querying and updating data.

Currently, SQL Compact data is only available to ADO.NET and OLEDB based Windows client.

Exposing your SQL Compact data as an OData feed allows your data to be consumed by a variety of different client platforms at any location, including:

OData Explorer (Silverlight Application)

PowerPivot for Excel 2010 is a plugin to Excel 2010 that has OData support built-in.

LINQPad is a tool for building OData queries interactively.

Client libraries: Javascript, PHP , Java, Windows Phone 7 Series, iPhone (Objective C), .NET (Including Silverlight)

I will now show you the steps required to expose a SQL Compact database file a an OData feed using Visual Studio 2010, and how to consume this feed from Silverlight and Excel PowerPivot.

Please note that exposing SQL Compact data like this will not scale to 100s of concurrent users.

Step 1: Create the Web Application

In Visual Studio, select File, New, Project…

Select Web, ASP.NET Empty Web Application, and click OK.

Step 2: Add the Entity Framework Model

From the Project menu, select Add New Item…

Name the model NorthwindModel and click add.

Select Generate from Database and click Next.

Select an existing Data Connection or create a new one if required, and click Next.

“Just say No”

Select Tables and click “Pluralize…” – then click Finish.

Step 3: Add the WCF Data Service (OData Service)

Select Project, Add New Item…

Select Web, WCF Data Service, and name it “NWService”, then click Add.

Modify the service code (noted in italic):

    public class NwDataService : DataService<NorthwindEntities>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            // Give readonly access to all of the entities
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);

            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }

Step 4: Modify global.asax to allow SQL Compact access from ASP.NET

SQL Server Compact 3.5 is not currently optimized to serve as a database for Web sites. SQL Server Compact 3.5 can be used with ASP.NET in application scenarios where ASP.NET is used to create SQL Server Compact 3.5 databases for synchronization scenarios.

To change the default behavior, despite the warning above, open global.asax, and modify as follows (in italic):

void Application_Start(object sender, EventArgs e)
{
    // Code that runs on application startup
    AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
}

Step 5: Run your service!

Right click the NwDataService.svc, and select View in Browser:

Your OData service is now ready for consumption by any of the clients mentioned above.

Step 6 – test access from various clients

Access the service from Silverlight (installed elevated):

Access the service from Excel 2010 PowerPivot: