Daniel Casserly Daniel Casserly - 1 month ago 11
C# Question

linq to entities changing database connection string in code

I am using the devart component dotconnect for postgresql. I have created the site using linq to entities, however, I would like each user to have a seperate database. This means that I need to change the connection string for each person that has logged in. I understand the main part of how to generate a new connection string etc, however, when i pass that as a paramater to the object context object it comes back with the error

"user id keyword not supported, "

if i create a class that generates an entity connection the error message changes to:

"Unable to load the specified metadata resource."

Cannot work out what I have done wrong in these instances.

Answer

ok so, as usual, when i posted this question, about 3 minutes later i found the problem. The entity connection string, for general purposes should have a cool little

res://*/

this makes the metadata work. This solves the problem of metadata resource and this works. So to help others who may, like me, have spent development time doing this, i created a class, with a method like so.

 public static string getConnString(string database)
    {
        string connectionstring = "User Id=USER ID HERE;Password=PASSWORD HERE;Host=server;Database="+database+";Persist Security Info=True;Schema=public";

        EntityConnectionStringBuilder newconnstring = new EntityConnectionStringBuilder();
        newconnstring.Metadata = @"res://*/"; 
        newconnstring.Provider = "Devart.Data.PostgreSql";
        newconnstring.ProviderConnectionString = connectionstring;


        return newconnstring.ToString();

    }

then create a constructor like so

   dataEntities data = new dataEntities(databaseConnection.getConnString(INSERTDBNAMEHERE);

Then we can reference that in the same way as a usual linq statement. Simples!!