Home > English > Dynamically changing the connection string for WCF RIA Services Linq2Entities

Dynamically changing the connection string for WCF RIA Services Linq2Entities

One of the business requirements I get a lot is the ability to switch which database they use when they authenticate. I have just recently ported my applications over from Linq2Sql to Linq2Entties and I have noticed just enough difference that I figured I would blog about how to do this. I will also publish a subsequent post that shows how this is accomplished in Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added an ADO.NET Entity Data Model as well a DomainService Class.

Data Access Project

One thing that you will notice, is that I also manually created the DataAccessDomainService.partial.cs. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

Note that I had to add the partial keyword to the main class in order to get my partial class to work. Also the main class derives from LinqToEntitiesDomainService where T is the model we are building our DomainService. In this case it would be DataAccessContext.

Let’s take a look at partial class now:

public partial class DataAccessDomainService
{
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DataAccessContext CreateObjectContext()
    {
        DataAccessContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
        {
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
                c).FirstOrDefault();
            if (userSession != null)
            {
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                {
                    string connString = EntityConnectionStringHelper.Build(
                        db.sl_d_ServerName,
                        db.sl_d_DatabaseName,
                        db.sl_d_UserName,
                        db.sl_d_Password,
                        "DataAccess");
                    ctx = new DataAccessContext(connString);
                }
            }
        }

        return ctx;
    }

};

Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

Note: The string “DataAccess” that I am passing into the EntityConnectionStringHelper.Build method above is actually from the Web.Config connection string. It represents the *.csdl, *.ssdl, *.msl names that are represented in the style that the Entity Framework requires.

In order to help me and get some code reuse, I created a helper class that helps me with buildng my connection string. This is one of the main differences between Linq2Sql and Linq2Entities.

Let’s look at this code.

public class EntityConnectionStringHelper
{
    public static string Build(
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        return EntityConnectionStringHelper.Build(
            providerName,
            serverName,
            databaseName,
            userName,
            password,
            metaData);
    }
    public static string Build(
        string providerName,
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",
            metaData);

        return entityBuilder.ToString();
    }
};

Basically this class has two static methods called Build. One calls into the other passing in the providername. In order to correctly build a Linq2Entities connection string you have to also include the Metadata. This is the main difference between Linq2Sql and Linq2Entities. For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class in conjunction with the EntityConnectionStringBuilder class.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:

<configuration>
  <connectionStrings>

    <add name="DataAccessContext"
      connectionString="metadata=res://*/DataAccess.csdl|res://*/DataAccess.ssdl|
        res://*/DataAccess.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=bunkerhill;
        Initial Catalog=emgov_data;Integrated Security=False;
        User Id=emgovuser;Password=xxxx;
        MultipleActiveResultSets=True&quot;"
        providerName="System.Data.EntityClient" />

  </connectionStrings>
  ...
<configuration>

(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…

Advertisements
  1. GEB
    July 19, 2010 at 11:20 am

    Matt, great post. I do have one recommendation. When overriding CreateObjectContext(), you should raise an exception rather than returning a null context. It turns out that WCF RIA Services will exhibit some unwanted behaviors if null is returned from this method.

    • July 19, 2010 at 11:44 am

      Gary,

      Great suggestion. I will look into that and make an update to the post.

      Matt

  2. Marco
    September 27, 2010 at 11:01 am

    Hi Matt , thank you for your post !
    I have the following scenario : my users authenticate with a web form and I store the username and password in the silverlight project, how can i access this info from the RIA services to build the connection string ? I have to requery the database or there is a way to get the information I already have in the silverlight context ?

    • November 10, 2010 at 11:06 pm

      Marco,

      Probably the easiest way to do this is use the Authentication Service provided with RIA Services. You basically derive from the AuthenticationBase class and provide the type for T. I use the class User. Then all you have to do is override some methods to get everything to work:

      bool ValidateUser(…)
      User GetAuthenticatedUser(…)

      If that doesn’t help, let me know and I can do a post that walks you through how I do this.

      Regards,

      Matt

  3. TRH
    November 10, 2010 at 2:40 am

    Matt,

    Thank’s a lot for the example. One small change might be to make clear that the MetaData value of “DataAccess” is actually the name of the .EDMX file. Took me a few minutes to figure out that it wasn’t some sort of mode being passed to the entityBuilder.

    • November 10, 2010 at 11:12 pm

      TRH,

      Good point, I will update the post to make it clearer.

      Regards,

      Matt

  4. Scott
    December 4, 2010 at 11:56 pm

    Very nice blog post. I have a questions that is driving me nuts. I see you are buidling your connection string with data found within the user context. However, what if you need some data from the client in order to figure out which database the DomainService needs to access? In other words, how can I create the connection string when only the UI knows which DB needs to be accessed?

    I’ve searched high and low for this, and I have found no way to truely create a dynamic connection string where the user selects which DB to connect to.

    Thought?

  5. Rich
    March 24, 2011 at 4:15 pm

    Matt, thanks much for this walk-through. Where other sites failed to mention the need to override the CreateObjectContext() you spelled it out very clearly.

    • March 25, 2011 at 6:54 pm

      Rich,

      You are very welcome. I am just glad that you found some value in it.

      Matt

  6. George P.
    May 4, 2011 at 4:35 pm

    Very good post, but since i am new to silverlight i have many questions. First the scanario.
    I have many databases so as you can quess the connection string is different for each. The user enters the silverlight application and gives user name, password and a code corresponding to the database he wants to connect. Now i want to use this info with your solution above but how can i pass this info to the web project? My problem is similar to Marco’s above but i cannot figure it out how to do it. Can you please provide me with some more info or guidelines?

    • May 11, 2011 at 12:22 am

      George,

      I am not quite sure I follow your scenario. But I can tell you that you can create a simple WCF service to pass in the credentials that you need. I have used this solution in both standard Web Services and WCF RIA Services. I believe that you should be able to do the same.

      Let me know if that helps and if I can help more,

      Matt

  7. Mark Miller
    September 29, 2011 at 6:16 pm

    Matt,

    I enjoyed you post on switching contexts dynamically. I have a WCF RIA Services project that needs to flip between 3 different environments and this code works great…with one small hiccup. I load a number of static datasources from lookup tables in the app.xaml and since at that point I haven’t authenticated the user, the connection string from the web config is used.

    I’m using Forms Authentication using the ASP.NET setup with some additional fields added to the aspnet_users table, and the formsAuthentication service that comes with the RIA Services template. I’d hoped to be able to load these static resource in code, after authenticating the user, but my code doesn’t work. If this should work, hopefully you can tell me what I’m doing wrong… of if my whole approach is incorrect.

    Here’s a code snippet of how I’m trying to load these resources …
    ….
    public class AppResources
    {
    DeviceDomainContext _ctx = new DeviceDomainContext();

    public void LoadResources()
    {

    System.Windows.Application.Current.Resources.Clear();
    ResourceDictionary rdContext = new ResourceDictionary();

    rdContext.Add(“deviceDomainContext”, _ctx);

    ResourceDictionary rd = new ResourceDictionary();

    ComboBoxDataSource ds = new ComboBoxDataSource();
    ds.DomainContext = _ctx;
    ds.OperationName = “GetDeviceModelsList”;
    ds.Name = “deviceModelsListDataSource”;

    rd.Add(ds.Name, ds);

    }
    }

    Mark

    • September 30, 2011 at 1:26 am

      Hi Mark,

      It is hard for me to tell with just the snippet you provided. What is exactly happening? Are you getting an error? Are you resources getting created with every request or do you have them cached?

      Sorry I can’t help you further,

      Matt

  8. Mark
    September 30, 2011 at 5:56 am

    Matt,

    The code runs without errors, but does not create the resources like the corresponding xaml

    I would only call this code once to create the resources after the users logs in, or if they switch environments, I would have them login again to reset the resources pointing to the new environment.

    I guess I was assuming that this code would add them as a cached resource the same as the xaml code.

    Hope this clarifies the issue.

    Mark

  9. November 10, 2011 at 7:52 pm

    Hello and thank you for the information!

    I’m currently working on a project that requires me to dynamically change the data base/context and I’d like to apply some of what you’re teaching us to my project.

    Do you have a working demo that you can share? I always seem to learn a little more when I have a working demo to dissect. It also comes in handy when I have a problem. I can simply watch a working version go through the process and determine where my code is failing.

    Anyway, please let me know and thanks!

    ~ Dave

  10. November 10, 2011 at 8:35 pm

    Hello again,

    Well, perhaps I’m over thinking this… I often do.

    What we basically have are two databases. The .Net Authentication database and the “users” database.

    We have 1 Authentication database and it keeps the records for all registered users AND which “users” database they use.

    Here’s the ‘gotcha’; the “users” database is replicated as many times as needed. Our current plans are to have 22 separate databases. These databases will store and separate each company’s data. The “users” databases are 100% identical. There is no difference between.

    They are currently hosted on the same server (domain). Naturally, they are in separate SQL Server 2008 R2 instances…

    So, I’m wondering, would I only need to change part of the connection string? Could I simply ‘update’ the entity connect string with a new database name and password and be done? Could it be that simple or am I missing something.

    Please let me know and thanks!

    ~ Dave

    • November 11, 2011 at 1:33 am

      Dave,

      Yes, you can do exactly that! As long as you create a connection string that is in the correct format, you could simply swap database names and passwords. I don’t believe there is any way to modify an existing connection string so you will still have to do what I have described in my post.

      Hope this helps,

      Matt

  11. November 11, 2011 at 4:34 am

    One thing that’s been troubling us Silverlight 4 developers is the speed in which our tools are evolving. It’s awesome to see them being developed so fast but it’s also a challenge to keep up with them. The documentation that supports the changes often isn’t keeping up…

    Your original post is dated May 20, 2010 and I have to wonder what advancements have been made in our dev toolbox that might affect the code that you demo’d.

    Entity Framework is now at 4.1 with other versions in beta. Do you know if anything has changed that might make this topic easier to code?

    Thanks!

    ~ Dave

    • November 11, 2011 at 11:25 am

      Dave,

      I don’t believe that the connection string API has or will change since it would break all existing code with Entity Framework. I am quite sure that this will work for you without any other changes.

      Regards,

      Matt

  12. November 11, 2011 at 7:55 pm

    Thanks for the replies.

    I’m guessing you don’t have a demo project to go along with this post since you didn’t post a link… but, just to be sure, please let us know if a demo is available.

    Thanks!

    ~ Dave

  13. November 12, 2011 at 1:19 pm

    Dave,

    I will see what I can do.

    Thanks again for reading my blog,

    Matt

  14. November 12, 2011 at 7:46 pm

    Matt,

    Thanks for looking into a demo for us.

    I have another question; is this solution one that requires me to reset the connection string each time I make a database call or call a query in a domain service class?

    Or does this solution reset the connection string for all database calls for the “session”, meaning, until the code resets it some how?

    Please let me know and thanks,

    ~ Dave

  15. November 13, 2011 at 12:15 am

    Hello Matt,

    Well, I’m not getting it. For some reason I keep getting my metadata placed in the wrong place and it’s replicated 3 times.

    Here’s what I get back; metadata=res://*/res://*/bcRMS.App.csdl|res://*/bcRMS.App.ssdl|res://*/bcRMS.App.msl.csdl|res://*/res://*/bcRMS.App.csdl|res://*/bcRMS.App.ssdl|res://*/bcRMS.App.msl.ssdl|res://*/res://*/bcRMS.App.csdl|res://*/bcRMS.App.ssdl|res://*/bcRMS.App.msl.msl;provider=System.Data.SqlClient;provider connection string=”Data Source=myHost.com;Initial Catalog=myDB;Integrated Security=False;User ID=myUser;Password=myPW”

    Have you seen this before? Any ideas as to what I might be doing wrong?

    Please let me know and thanks.

    ~ Dave

  16. November 13, 2011 at 2:27 am

    Hello once again,

    Well, I found the problem. Turns out I was passing in the full metadata string… then I changed it to pass in a smaller string and noticed how that affected it. Once I realized the error of my ways I changed it to match the string.format command.

    Naturally you can imagine the look on my face when the light bulb finally went on… The look was of both shock and happiness… along with the words… “no way! Are you $#@&%$# kidding me?”

    Ok, so in order to help the next person “avoid” my mistake I’ll post the metadata string I ended up using;
    string metadata = “bcRMS.App”; <<—Yes, this is the one! Just use your own.
    //string metadata = "bcRMS.App|bcRMS.App|bcRMS.App"; <<–Wrong.
    //string metadata = "res://*/bcRMS.App.csdl|res://*/bcRMS.App.ssdl|res://*/bcRMS.App.msl"; <<– Totally wrong!

    So, now that it's working without errors I'll try to implement it in my application.

    Thanks again for your help!

    ~ Dave

  17. November 13, 2011 at 7:38 pm

    Hello,

    I have one more question; I’m getting a new connection string back but it’s not in the same order as the original or the example you show in your post. Your example shows the metadata value being placed within the “connectionString” tag however when my new string comes back the metadata is listed first and not within the connectionString.

    Here are some examples;

    <<>>

    <<>>
    metadata=res://*/bcRMS.App.csdl|res://*/bcRMS.App.ssdl|
    res://*/bcRMS.App.msl;provider=System.Data.SqlClient;
    provider connection string=”Data Source=tcp:xxxx.com;
    Initial Catalog=xxxx;Integrated Security=False;
    Persist Security Info=True;
    User ID=xxxx;Password=xxxx;
    MultipleActiveResultSets=True;
    Application Name=EntityFramework”

    Do you think this will be a problem? I’m not getting any errors but I’m also not getting to the data as I expect.

    Please let me know what you think and thanks!

    ~ Dave

  18. November 13, 2011 at 7:43 pm

    Hello,

    My previous post was automatically edited for safety reasons because of my use of the greater than / less than charactors.

    Here’s the text that was in that block;

    Your example

    Sorry, about that. I’ll be more careful with my choice of charactors.

    ~ Dave

  19. November 13, 2011 at 7:44 pm

    Third times a charm?
    add name=”DataAccessContext”
    connectionString=”metadata=res://*/DataAccess.csdl|res://*/DataAccess.ssdl|
    res://*/DataAccess.msl;provider=System.Data.SqlClient;
    provider connection string="Data Source=bunkerhill;
    Initial Catalog=emgov_data;Integrated Security=False;
    User Id=emgovuser;Password=xxxx;
    MultipleActiveResultSets=True"”
    providerName=”System.Data.EntityClient”

  20. November 13, 2011 at 9:25 pm

    Hello Matt,

    I’m sorry for posting so many comments and THANK YOU for your help.

    I’ll make this my last post – promise;

    I found a way to test the new connectionString that is being created. It’s a simple code snippent that opens a connection with the new connectionstring. If it opens you’ll see a entry in your output tab. If not, it will thow an error. Note that I also changed the original “Console.WriteLine” to “Debug.WriteLine.

    Anyway here’s the test:
    using (EntityConnection conn = new EntityConnection(entityBuilder.ToString()))
    {
    conn.Open();
    Debug.WriteLine(“”);
    Debug.WriteLine(“Just testing the connection.”);
    Debug.WriteLine(“”);
    conn.Close();
    }

    I just added this code snippet to the bottom of the EntityConnectionStringHelp class (before the return statement) and ran the app. Now I know that my new connection string works.

    One last note; the connectionString that comes back from this code is not in the same order as the connectionStrings found in our Web.Config file – but that seems to be ok and it passes this test.

    Once again, thank you for sharing and teaching us about connectionStrings!

    Take care,

    ~ Dave

  21. shanpingping
    February 6, 2012 at 7:38 am

    Hi,
    Thank you for sharing!
    Could you send your demo to me? Email: shanpingping2005@163.com

  22. Ankur
    February 14, 2012 at 7:47 am

    Hi,
    Nice article!
    Could you send your source code to me? Email: ankurbhargava2000@gmail.com

    • February 15, 2012 at 1:07 pm

      I will try and get a demo put together soon.

  23. Chris
    April 20, 2012 at 9:24 am

    Hi,
    Thank you for sharing this article!
    Could you send an demo to me? Email: cwa_40@hotmail.com

    • May 2, 2012 at 11:05 am

      Hi Chris,

      I will try and get something together to share on my blog.

      Matt

      • Chris
        June 13, 2012 at 6:05 pm

        Hi Matt,
        This wuld be great.
        So I can look forward and finish up this Silverlight project an go online.
        Thankful regarding from Switzerland
        Chris

  24. November 18, 2012 at 5:23 pm

    Hello, I want to subscribe for this weblog to get latest updates, so
    where can i do it please help out.

    • November 18, 2012 at 6:21 pm

      Hi Katherina,

      You should be able to subscribe to my blog without any issues. If you click on the RSS feed, you should be subscribe.

      Thanks,

      Matt

  25. Cliff Strass
    April 2, 2013 at 5:53 pm

    Great article Matt. I have implemented this and all works great. One question .. Is there a way to change the connection string per query? That is I want to create the connection string from a UI drop-down. With this in place the end-user can select which db instance to query against.
    Any help would be appreciated, thanks Cliff.

    • April 16, 2013 at 1:49 pm

      Hi Cliff,

      The short answer is yes. Once I authenticate a user, then I present them with a dropdown of business entities that they can perform their work on behalf. This is a generic solution and it should work for most scenarios where you have two-step authentication where you first authenticate is a separate database that holds the connections to all other databases. This way you can extend and add as many other databases connections you want in the future without recompile or redeploying.

      Hope that helps,

      Matt

      • Cliff Strass
        April 22, 2013 at 3:24 pm

        To solve this I basically added the client ID or database ID to the WCF header before making the RIA services call to the server. At the server end I ‘pick-up’ the database ID and lookup the connection string and pass it along to the domain context when instantiating it.
        Works like a charm. Here is a thread from Kyle McClellan (from the RIA services team) explaining in more detail (with code) how this is done.
        http://social.msdn.microsoft.com/Forums/id-ID/silverlightwcf/thread/a74b563e-1df5-456f-b628-a068557f0bb3

        Hope it helps anyone else.
        Thanks, Cliff.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: