How to choose from two connection strings?

Question

Good afternoon, I have encountered a problem to choose from two connection strings using C# and Linq to Entities. Currently I have two connection strings which are:

<add name="GameHutDBEntities1" connectionString="metadata=res://*/GameHutModel.csdl|res://*/GameHutModel.ssdl|res://*/GameHutModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=EMMANUEL-PC\SQLEXPRESS;Initial Catalog=GameHutDB;user id=GameHutAdmin; password=123;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
<add name="GameHutDBEntities2" connectionString="metadata=res://*/GameHutModel.csdl|res://*/GameHutModel.ssdl|res://*/GameHutModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=EMMANUEL-PC\SQLEXPRESS;Initial Catalog=GameHutDB;user id=test; password=1234;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

I have two roles which are Admin and Clerk. How can I allow the admin to login with the first connection string and the clerk will login with the second connection string?

enter image description here

http://stackoverflow.com/questions/1257107/entity-framework-to-multiple-databases-same-schema-at-runtime — Oded, Dec 24 '11 at 15:35

score 5 · Accepted Answer · edited May 23 '17 at 11:48

5

Adapted from this answer:

string connString;

if(IsAdmin(user))
    connString = ConfigurationManager.ConnectionStrings["GameHutDBEntities1"];
else
    connString = ConfigurationManager.ConnectionStrings["GameHutDBEntities2"];

using(EntityConnection con = new EntityConnection(connString))
{
  using (Entities context = new Entities(con))
  {
      // Some code here
  }
}

Where IsAdmin is a function taking a user and returning true if he is an admin and false if not.

edited May 23 '17 at 11:48

Community

1
1

answered Dec 24 '11 at 15:38

Oded

489,969
99
883
1,009

I am working with three tier model, where do I need to put this code please? and thanks for answering my question! – user1114676 Dec 24 '11 at 15:41
@user1114676 - This would be in the data access layer, though I would pass the connection string (or a boolean indicating the user is an admin or not) through from the UI as a dependency (you _don't_ want to hard code a dependency on `ConfigurationManager` into your data access layer. – Oded Dec 24 '11 at 15:42
the configurationmanager is not recognized in the data layer, is there a way to use it in the data layer please? – user1114676 Dec 24 '11 at 16:45
@user1114676 - It is part of the `System.Configuration` namespace. You can add the assembly and import the namespace where you need to use it. But, I advise against it - you would be hard coding a dependency to configuration into your data access layer. – Oded Dec 24 '11 at 16:47
@user1114676 - Then you are doing something wrong. Are you sure your config has connection strings with the names you are using? Are you using the correct config? – Oded Dec 27 '11 at 12:29
i put the following code in the login page and it works fine but the problems is that when the user goes to another page, the connection string is not stored anymore. Do you know why please? – user1114676 Dec 27 '11 at 14:38
@user1114676 - Why should a different page know about code that isn't in it? – Oded Dec 27 '11 at 14:39
i need to store a connectionstring for the admin and another for the clerk. when the admin is logged, the admin connectionstring is used but when the admin goes to add a user the connection string is changed to default... – user1114676 Dec 27 '11 at 14:42
@user1114676 - You can store the connection string in a `Session` variable. – Oded Dec 27 '11 at 14:57
the problem is that i need to pass the connString in the datalayer in the connnection class. The session and application variables are not accessed in the data layer and the business layer. If it's possible i think i can solve the problem – user1114676 Dec 27 '11 at 15:03
@user1114676 - If your data layer was written to take a connection string as a parameter, I don't see the problem. You still use the data layer from the upper layers, no? – Oded Dec 27 '11 at 15:05
as i have two connection strings i need to store one of them in the connection class according to the role and keeps using it until the user logs off. – user1114676 Dec 27 '11 at 15:07
@user1114676 - You really need to ask another question about this issue. It is not really part of your original question and comments are not a way to do this. – Oded Dec 27 '11 at 15:08
you are very helpful so can I send you the source code please? – user1114676 Dec 27 '11 at 15:11
@user1114676 - No. That's no how we do things on StackOverflow. – Oded Dec 27 '11 at 15:12

Tomislav Markovski · Answer 2 · 2011-12-24T16:11:39.800

2

var conn = new EntityConnection();
conn.StoreConnection.ConnectionString = ConfigurationManager.ConnectionStrings["GameHutDBEntities2"];

this.Entities = new KurtDBEntities(conn);

edited Dec 24 '11 at 16:11

answered Dec 24 '11 at 15:37

Tomislav Markovski

12,331
7
50
72

I am working with three tier model, where do I need to put this code please? and thanks for answering my question! – – user1114676 Dec 24 '11 at 15:41
1

Whereever you are instantiaing your Entities just pass the connection as a parameter. – Tomislav Markovski Dec 24 '11 at 15:46
Yes, that one. Just pass the connection to the constructor. – Tomislav Markovski Dec 24 '11 at 16:00
See my edit, all code is C#. However, you need to make the logic if user is admin or not. – Tomislav Markovski Dec 24 '11 at 16:12

How to choose from two connection strings?

2 Answers2

Linked