ADO.NET Data Services for Multiple Databases?

This all started with an innocent question by Bob Archer on Twitter. Bob wondered whether he could use ADO.NET Data Services in an application that was being touted as “Software as a Service” (SaaS). His concern was the apparent hard wiring of the Data Source in the DataService definition. This design might assume that you had to connect to a single database for all requests.

In his case, the Entity Framework metadata was the same but the connection string might choose a different physical server and database name. I thought that it shouldn’t be too difficult. My first attempt to help him was to suggest interceptors for every entity type and manually change the database string based on some data in the request. This felt hacky so I asked Pablo Castro (at Microsoft) for some advice on the problem. He of course had the solution.

The solution was to override the protected CreateDataSource method on the Service. This method is called during each request to create the Data Source. This happens in the pipeline after the request information has been parsed so you can use request parameters to decide what Data Source to create.  You still need to return a Data Source that is the same type as the generic parameter but this means you can determine how to create the object. For example:

public class ProductService : DataService<ProductEntities>
{
  // ...

  protected override ProductEntities CreateDataSource()
  {
    HttpRequest req = HttpContext.Current.Request;

    if (req.IsAuthenticated)
    {
      return new ProductEntities("Authenticated DB Connection String");
    }
    else
    {
      return new ProductEntities("Anonymous DB Connection String");
    }
  }

}

This sample shows that you would need to return a ProductEntities data source, but you can determine how the creation of the data source happens. In the case of how ADO.NET Data Services works, this is called on each and every request separately and not cached so you can safely create a data source based on your current request as shown above.  Thanks Pablo, you made me look good again!