Wednesday, June 23, 2010

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…

image

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…

image

Name the model NorthwindModel and click add.

image

Select Generate from Database and click Next.

image

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

image

“Just say No”

image

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

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

Select Project, Add New Item…

image

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!




image 




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



image



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):



image



Access the service from Excel 2010 PowerPivot:



image

8 comments:

Joel Karr said...

Erik -
Excellent post. I have never worked with SQL CE before but have an application that currently uses WCF Data Services to communicate with a network server. I am trying to give the user the option to use SQL CE on their local machines instead.

I am having trouble using my ddl script to generate the SQL CE Tables. It works fine with SQL 2008 R2. Can you create CE databases with ddl scripts?

ErikEJ said...

Hi Joel,
I have tools to create SQL CE compatible DDL scripts (Microsft does not supply such a tool). Go to http://exportsqlce.codeplex.com, you can use Export2SqlCe command line or one of the addins.

Roderick Teed said...

Great set of instructions however my problem is that my mobile devises do not have web access but need to do lookups in MS Access data during the day and get an updated database at night, any suggestions?

ErikEJ said...

Roderick: Not really, suggest you ask elsewhere

guypb said...

I'm sure this must have been covered but I have searched and searched... can a SQL Compact Database be accessed via ODC direct from Excel without the need for VBA?
I am trying to connect a pivottable direct to a SQL Compact sdf without running VBA to bring in all the data to a table.

guypb said...

Is it possible to use ODC or similar to connect an Excel 2010 pivottable to a SQL Compact SDF.

I don't want to use VBA to bring in all the data into a table or sheet in Excel if at all possible.

Works fantastic with ODC off SQL Server - must be a way to do something similar without Power Pivot??

Tim Daly said...

Got all the way to Step 3; but WCF Data Services was not available in the dropdown.

I am using VS2012, SQL Server 2012 CE, and Excel 2010.

Do you have an updated walkthrough?

ErikEJ said...

Tim: No, not currently, but a good idea for a new blog post.