Use together ADO.NET Data Services with SQL Compact

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:

  1. Create data file of SQL Comapct. You can use integrated tool in Visual Studio or use SQL Management Studio;
  2. 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;
  3. Create web-service (ADO.NET Data Services ;));
  4. 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;
  5. 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.

How I can define custom instance logic of data model in ADO.NET Data Services?

When we work with ADO.NET Data Services we reference data model via generic-parameter. Thus, instance of data context class it is created by infrastructure of ADO.NET Data Services. In this case ADO.NET Data Services use constructor without parameters.

Frequently such behavior quite enough – we create data model, in configuration file we register connection string and it works. However, it is possible to see scenarios when it necessary to meddle in this process.

Causes my be very different:

  • We need to store connection string outside of configuration file;
  • We need to create not DataContext class, but his class, which has been inherited from this;
  • Data Context class not contains constructor without parameters (yes, yes, and such too happens);
  • etc
  • etc

In this cases ADO.NET Data Services platform offer us very nice way to solve this problem. Everything, that it is necessary to make is to override method CreateDataSource() of base class.

 

public class WebDataService1 : DataService<DataClasses1DataContext>
{
    public static void InitializeService(IDataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    }
 
     protected override DataClasses1DataContext CreateDataSource()
    {
        return base.CreateDataSource();
    }
}


As you can see, this method returns a data source of that type, which is specified in generic-parameter when we define service class. This method called when it is necessary to get data model. Now we can define custom logic, in which we are needing. For example, we can redefine connection string:



public class WebDataService1 : DataService<DataClasses1DataContext>
{
    public static void InitializeService(IDataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    }
 
    protected override DataClasses1DataContext CreateDataSource()
    {
        return new DataClasses1DataContext("some connection string");
    }
}

ADO.NET Data Services и LINQ to SQL: continuation

Recently I wrote about what actions are necessary, that the LINQ-to-SQL data model could work together with ADO.NET Data Services. I wish to add this information with one important fact.

Actually, there is an convention on which ADO.NET Data Services tries to define independently key fields in entity. This convention consists in the following – if the entity is not marked with DataServiceKey attribute, following actions are carried out:

  1. Among properties of entity property ID is searching. If it exists, it will be primary key;
  2. If property ID is not found, the propery “EntityTypeID” is searching. In this case “EntityType” means name of your entity. For example, if your entity is named as “Customer”, attempt to find out “CustomerID” will be made. If such property is found, it will be primary key;
  3. If these properties are not found, the error will be generated.

Many thanks to Phani Raj, for the information on the given behavior.

Updating data into ADO.NET Data Services which based on LINQ to SQL

We continue to investigate possibilites of use LINQ to SQL as model of data for ADO.NET Data Services. Today we will talk about possibility of change data.

I will remind, that ADO.NET Data Services platform gives us possibility not only to select data through web service, but also to update them using methods POST/PUT/MERGE/DELETE. Within data model it is necessary to implement IQueryable interface (for each entity set) for data selecting, and IUpdatable (for datacontext class) for updating.

Let’s go back to LINQ to SQL. If we will try to create LINQ to SQL data model, give access to them via ADO.NET Data Services and try to update data we will find out, that web service generates an error. At superficial studing of a cause of error it is possible to guess, that the problem consists that LINQ to SQL data model do not implement IUpdatable. The reasons of such outcome are obvious: IUpdatable interface is component of ADO.NET Data Services. LINQ to SQL has been released much earlier, than ADO.NET Data Services. Thus it is possible to make a conclusion – implement yet not ready interface LINQ to SQL team could not.

Nevertheless. this restriction really serious and can essentially affect the final solution. Of course, it is possible to refuse to use LINQ to SQL, but it not our method :) How to solve this problem? It is very simple – implement IUpdatable interface manually :)

However, when we see IUpdatable interface it is possible to become despondent, because there it is necessary to implement 12 method with opaque logic.

partial class NewsDataContext : IUpdatable
{
    #region IUpdatable Members
 
    public void AddReferenceToCollection(object targetResource, string propertyName, object resourceToBeAdded)
    {
        throw new System.NotImplementedException();
    }
 
    public void ClearChanges()
    {
        throw new System.NotImplementedException();
    }
 
    public object CreateResource(string containerName, string fullTypeName)
    {
        throw new System.NotImplementedException();
    }
 
    public void DeleteResource(object targetResource)
    {
        throw new System.NotImplementedException();
    }
 
    public object GetResource(System.Linq.IQueryable query, string fullTypeName)
    {
        throw new System.NotImplementedException();
    }
 
    public object GetValue(object targetResource, string propertyName)
    {
        throw new System.NotImplementedException();
    }
 
    public void RemoveReferenceFromCollection(object targetResource, string propertyName, object resourceToBeRemoved)
    {
        throw new System.NotImplementedException();
    }
 
    public object ResetResource(object resource)
    {
        throw new System.NotImplementedException();
    }
 
    public object ResolveResource(object resource)
    {
        throw new System.NotImplementedException();
    }
 
    public void SaveChanges()
    {
        throw new System.NotImplementedException();
    }
 
    public void SetReference(object targetResource, string propertyName, object propertyValue)
    {
        throw new System.NotImplementedException();
    }
 
    public void SetValue(object targetResource, string propertyName, object propertyValue)
    {
        throw new System.NotImplementedException();
    }
 
    #endregion
}




I will not consider details of implementing of each method. These methods will seem to you simple when you look at them in details. That you could use now already possibility of data updating through web service (based on LINQ to SQL model) I offer you one of variants of implementation of the given interface. You can download it (and use in your projects) by using link at the end of this post.



Important remark. As you see, the interface is implemented in a partial-class. It is essential, because if you implement it in autogenerated file “*.designer.cs” you can lose this code at any data model change.



IUpdatable implementation:


DataContext.Updatable.cs

ADO.NET Data Services and LINQ-to-SQL

As a rule, ADO.NET Data Services works over ADO.NET Entity Framework data model. However, work with real projects (and customers:)) sometimes specifies to us other conditions. Frequently there is no necessity to use Entity Framework. And in this case we start to think of use LINQ-to-SQL as data model.

In earlier versions of ADO.NET Data Services it is has been simple – create data model, create web-service and it works! When ADO.NET Data Services released, the situation has been changed – at construction of similar service based on LINQ-to-SQL and accessing to this service we see an error message.

The reasons of this problem consist in strategy of definition of the fields containing primary keys. In early versions in the absence of obvious marks of this fields, infrastructure of ADO.NET Data Services itself tried to define them. However, in release this strategy has been changed and now it is necessary to define them obvious.

To set this fields very simply. You need mark entity using DataServiceKey attribute, in constructor of which define primary keys. The information about primary keys very important for service, because when you access to single entity you should define primary key value in URL.

In what a problem in LINQ-to-SQL? Problem in that when LINQ-to-SQL generates entities, he not marks him with this attribute. Certainly, it is very annoying lack. But you can correct it very simply. As you remember, that all entities in LINQ-to-SQL data model are partial-classes. You can generate second part of partial class for each entity and mark it with DataServiceKey attribute.

[DataServiceKey("NewsId")] 
partial class New 
{
}


Now ours service, which based on LINQ-to-SQL model works. Great :)

Debugging ADO.NET Data Services

Interest to ADO.NET Data Services platform obviously is growing up among developers of web-services. It also confirmed when I receive e-mail letters with questions about different problems. As a rule these questions are reduced to serivce error after some actions, when serivce say about general error and not takes details. In this case services say that he stored information about error in logs.

image

Whether familiarly to you the given message? I think yes, if you had time to try ADO.NET Data Services in practice.

Really, ADO.NET Data Service not write any messages to log. If you need this behavior you should do it manually. What to do if you need to understand native problem of error?

It’s very simple! You need to go to static method InitializeService,which initiate web-service. In this method passed parameter contains object which implements IDataServiceConfiguration.Last has property UseVerboseErrors. To get more information about error you should set this property to True.

public static void InitializeService(IDataServiceConfiguration config)
{
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    config.UseVerboseErrors = true;
}


Now we call web-service again and we see error details.



image



Here we can already see some details and even Stack Trace! Often this information is enough. But in some cases this information has not enough. After simple operations in a reflector it is possible to understand, that all ADO.NET Data Services infrastructure is constructed over Windows Communcation Foundation, and is concrete WCF REST API. As you know, in the security goals WCF by default hides exception details. Though, we can override this behavior and explicit specify to WCF that he should show this information. In our case it more simply use ServiceBehavior attribute.



[ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class WebDataService1 : DataService<MvcMiniCmsEntities>
{
    //...


Now WCF infrastructure will be include exception details in response and you can understand why error is appears.

Microsoft Techdays Developers 2009 (Global)

On April, 1st, 2009 there will pass world event Microsoft Techdays Developers 2009.

Microsoft Techdays Developers 2009 will pass in online, i.e. it’s virtual. In a current of 24 hours workd experts will tell to us about interesting technology.

Event will be broken into five tracks:

  • Windows Development and Frameworks
  • Windows Mobile Development
  • Office and SharePoint Development
  • Developer Tools, Languages & Practices
  • Web Development & User Experience

Registration consists of filling little form and receive acknowledge by e-mail.

That is interesting, conference carrying out is broken into three big time zones: America, Europe/Middle East/Africa, Asia Pacific. To specify your time zone it will be possible at registration.

Also, registered on a site you receive 50% the discount at delivery of certified examination (or free second attempt). Alse tere is a possibility to participate in draw MSDN Premium Subscription.

Do not pass!

 

Registration: https://www.msfttechdays.com/Modules/Registration/Submit.aspx

Agenda: https://www.msfttechdays.com/public/agenda.aspx

Session list: https://www.msfttechdays.com/public/sessionlist.aspx