Wednesday, April 13, 2011

Hosting SQL CE based Entity Models in WCF Data Service

While developing an application in ASP.net for my friend I realized that we can not SQL Compact Edition with ASP.net. When you try to do that you would get this error page.

SQL Compact Edition error when used with ASP.net
The server encountered an error processing the request. The exception message is 'SQL Server Compact is not intended for ASP.NET development.'. See server logs for more details. The exception stack trace is: 

at System.Data.SqlServerCe.SqlCeRestriction.CheckExplicitWebHosting() at System.Data.SqlServerCe.SqlCeConnection..ctor() at System.Data.SqlServerCe.SqlCeProviderFactory.CreateConnection() at System.Data.EntityClient.EntityConnection.GetStoreConnection(DbProviderFactory factory) at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString) at System.Data.EntityClient.EntityConnection..ctor(String connectionString) at System.Data.Objects.ObjectContext.CreateEntityConnection(String connectionString) at System.Data.Objects.ObjectContext..ctor
I got this when I was hosting WCF DataService in the ASP.net application exposing my Entity Model. The entities were based on a SQL CE database. To see this message, we need to set the behavior of service to allow the exception details to be shown to the user by this setting on the WCF Data Service.
[ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class MyDataService : DataService<App.ModelEntities>
{
    ...
}
Otherwise we see the following message when we run the application:

Although this is more secure message as we are not showing the stack trace to the user but during development this message seems very irritating. In order to avoid this and to get the detailed message set the above specified Service Behavior for the WCF Data Service.

This must be remembered that this limitation seems more like a suggestion by Microsoft so as to discourage the use of SQL Server Compact Edition for public facing systems because of its security limitations. But there might be valid reasons to still use SQL CE for ASP.net applications for demo applications or in-house developed applications. In order to get around that we can use AppDomain slots to set SQLServerCompactEditionUnderWebHosting to true. This results in allowing the use of SQL Server Compact edition by the runtime. We can set that during application startup in Global.asax.
public class Global : System.Web.HttpApplication
{
    void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
    }
}
Now when we run the application we can see the service successfully giving XML data. We have discussed about AppDomain slots here:

http://shujaatsiddiqi.blogspot.com/2011/01/in-this-post-we-will-discuss-named-slot.html

1 comment:

Anonymous said...

Muchas gracias! No encontraba el lugar donde colocar el codigo y aqui esta el detalle.