locked
Multiple Databases RRS feed

  • Question

  • User-221752185 posted

    Hello I have two databases, one with data representing data within the United States. Then another database for all other countries.

    I don't want to create two different WCFS, otherwise the client will have to toggle between two different URLS.  Any examples, ideas of how to dynamically change your database durin a request?

    Wednesday, November 27, 2013 3:57 AM

Answers

  • User-1509636757 posted

    don't want to be passing an extra parameter

    I believe it will be required because at least some identification is required to take decision in code to swap db

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 27, 2013 4:09 AM
  • User-742633084 posted


    Hi triggered,

    As other members mentioned, whatever approach you use, if you want to programmtically let your WCF service or web api service detect which database to use (from client request context), you need to pass some parameter values (no necessarily via operation parameter). It could be a custom HTTP header value , querystring or url suffix or standard operation parameters. Then, the server-side code just lookup the certain value from request and determine which database (the data access component) to use.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 28, 2013 12:56 AM
  • User-488622176 posted

    That's the easiest part. You create a dictionary like Dictionary<String, String> where the first argument is the country code, the second argument the connection string in text. In your WCF service, you fetch the country code and load the correct connection string. You could use a static method to get the connection string, something like:

    using (SqlConnection cn = MyConnectionManager.GetConnection(countryID))
    {
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 28, 2013 6:05 AM

All replies

  • User-1509636757 posted

    what are you using as backend (dbml, edmx, ado)?

    you can pass some identification related to country with each request.. and change the connection string accordingly.. like; we were using linq to sql and we had multiple database.. so we used to pass company id and based on id we change connection string in context oncreated event..

    hope it helps./.

    Wednesday, November 27, 2013 4:03 AM
  • User-221752185 posted

    Yea, i was thinking of handling it through the URL... something like  myservice.com/api/mobile/united-states/  and myservice.com/api/mobile/outside-united-states

    More and more I think about it, WebApi is a better option. But even with web-api, don't want to be passing an extra parameter in every call so I know to swap out database.

    I am using ADO.net. Not EF. 

    Wednesday, November 27, 2013 4:07 AM
  • User-1509636757 posted

    don't want to be passing an extra parameter

    I believe it will be required because at least some identification is required to take decision in code to swap db

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 27, 2013 4:09 AM
  • User-221752185 posted

    Well its not an extra parameter in every option if its in the URL.

    I'm trying to think of a clever way to do it... Like: An HttpModule or if in a WebApi, a Filter. It automatically detects from the request which connection sting should be used... it stores this in context for all back-end classes to use so its only evaluated once per call.  Don't know any contextual properties that I can hold this in... Perhaps the solution is to inherit from context and create my own. 

    Wednesday, November 27, 2013 4:16 AM
  • User-742633084 posted


    Hi triggered,

    As other members mentioned, whatever approach you use, if you want to programmtically let your WCF service or web api service detect which database to use (from client request context), you need to pass some parameter values (no necessarily via operation parameter). It could be a custom HTTP header value , querystring or url suffix or standard operation parameters. Then, the server-side code just lookup the certain value from request and determine which database (the data access component) to use.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 28, 2013 12:56 AM
  • User-221752185 posted

    The use of "some parameter" I feel is a bit stating the obvious and won't answer my question on how to actually switch out the database. My question already puts forward a solution of using a parameter via the URL. The actually problem is... how do I use this variable to swap out my connection string without making a massive mess of the code: eg. same code in various classes, every operation call parameters, etc...

    I was thinking that an Interceptor (Request Received) could read this value from the URL, and populate a database enum in a custom HTTPContext class. Then switching is easy. But now lets consider a more state-less scenario such as the WebApi where HttpContext is not really permitted. Now what? 

    Thursday, November 28, 2013 4:13 AM
  • User-488622176 posted

    That's the easiest part. You create a dictionary like Dictionary<String, String> where the first argument is the country code, the second argument the connection string in text. In your WCF service, you fetch the country code and load the correct connection string. You could use a static method to get the connection string, something like:

    using (SqlConnection cn = MyConnectionManager.GetConnection(countryID))
    {
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 28, 2013 6:05 AM