Set the database name when using oData and EF

Question

I have a scenario where there are multiple dbs that have the same schema and clients can pick which database to query. Is there a way to include the database name when doing an oData query from silverlight so it can reuse the same services?

Lets say I have this query (see below) being executed at the client (silverlight/wp7), how do I get this query to run against the database that the user picked when they first launched the app?

private DataServiceCollection _employees;
private void LoadEmployees()
{
DataModelContainer dmc = new DataModelContainer(new Uri("http://localhost:63832/DataService.svc"));
var query = (from e in dmc.Employees
where e.StartDate == BaseDate select e);
_employees = new DataServiceCollection(dmc);
_employees.LoadCompleted += new EventHandler(_employees_LoadCompleted);
_employees.LoadAsync(query);
}

Answer 1

You should be doing that in your configuration files, via connectionstrings element.

eg:

<configuration>
<!-- Other configuration settings -->

<connectionStrings>

  <add name="Sales" 
       providerName="System.Data.SqlClient"
       connectionString= "server=myserver;database=Products;uid=<user name>;pwd=<secure password>" />

  <add name="NorthWind" 
       providerName="System.Data.SqlClient" 
       connectionString="server=.;database=NorthWind;Integrated Security=SSPI" />

</connectionStrings>

To retrieve the connection strings dynamically, based on your query string from the client, make use of the ConfigurationManager class. The following links will help you in this regard:

http://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager.aspx

www.dotnet-guide.com/configurationmanager-class.html

Answer 2

Here is what I came up with: The AddQueryOption will add a value to the query string, in my case the key of the db that I want to run the query against.

var query = (from e in dmc.Employees.AddQueryOption("dbKey", SelectedDB)
where e.StartDate == BaseDate select e);
_employees = new DataServiceCollection(dmc);
_employees.LoadCompleted += new EventHandler(_employees_LoadCompleted);
_employees.LoadAsync(query);
}

In the DataService class I overrride the CreateDataSource method and return a DataModelContainer with the correct connection string for the query
protected override DataModelContainer CreateDataSource() {
DataModelContainer dmc = new DataModelContainer(GetConnectionString());
return dmc;
}
private string GetConnectionString()
{
string dbKey = HttpContext.Current.Request.Params["dbKey"].ToString();
string cnnKey = "DataModelContainer" + dbKey;
return ConfigurationManager.ConnectionStrings[cnnKey].ToString();
}