Use together ADO.NET Data Services with SQL Compact

Friday, March 27, 2009

Yes, you were not mistaken, having read title. Now we will look how it is possible to work with data from SQL Compact using ADO.NET Data Services.

Important remark. Actually, SQL Compact not absolutely correctly to use together with ADO.NET Data Services. SQL Compact initially is not focused on a mode of the multiuser work and it is intended for other purposes – for use as local storage. Using SQL Compact as storage for ADO.NET Data Services can negatively affect productivity of your web service.

Nevertheless, it is not excluded, that in your case it will be necessary use SQL Compact. For this case let’s look as it is possible to construct web-service, which use SQL Compact as main storage. That it to make it is necessary to execute soma simple steps:

Create data file of SQL Comapct. You can use integrated tool in Visual Studio or use SQL Management Studio;
Understand which data access technology we like to use. As a variant, it is possible to use ADO.NET Entity Framework or LINQ to SQL. If we use last we generate data model using SQLMetal utility, we add obvious defining of primary keys and if it is necessary, we add IUpdatable implementation;
Create web-service (ADO.NET Data Services ;));
We see an error that is impossible to instantiate data model class. The matter is that LINQ to SQL model for SQL Compact has no constructor by default (without parameters).Therefore, we will override CreateDataSource method (in web-service class) in which we will obviously create new data model;
Let’s start web service and we will see an error that use SQL Compact in this case very much not well. Let’s agree with this statement. We realise all consequences. Let's adjust environment for use SQL Compact. For this purpose during any initial moment of time (for example, at the moment of Application_Start) it is necessary to configure the execution environment in appropriate way.
```
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
```
It is the most convenient to make it in file Global.asax in which we can just create the handler of the given event.
```
public class Global : System.Web.HttpApplication
```
```
{
```
```
        protected void Application_Start(object sender, EventArgs e)
```
```
        {
```
```
            AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
```
```
        } 
```
```
//...
```

Perfectly, now our service can work over SQL Compact and even to change these data.

No Comments