Question
I'm working on a project that relies on the ADO.NET Entity Data Model. I have 4 identical database schemas, one for each department in my company (Marketing, Finanace, Administrative, and HR). I currently know which which department each of my users resides in. I would like to use the department to determine which database the user can connect to.
In C# code, how do I set the connection string at runtime? Currently, I have
string connectionString = GetUsersConnectionString();
using (MyEntities entities = new MyEntities())
{
MyDataEntity myDataEntity = new MyDataEntity();
// Save to the database
entities.MyDataEntity.Add(myDataEntity);
entities.SaveChanges();
}
What am I missing here? How do I set the connection string of MyEntities to connectionString?
Popular Answer
Here's a good way approach.
public static class ConnectionManager
{
public static string GetSqlConnectionString()
{
var serverName = @"" + ConfigurationManager.AppSettings["ServerName"];
var databaseName = ConfigurationManager.AppSettings["DatabaseName"];
var username = ConfigurationManager.AppSettings["Username"];
var password = ConfigurationManager.AppSettings["Password"];
SqlConnectionStringBuilder providerCs = new SqlConnectionStringBuilder();
providerCs.DataSource = serverName;
providerCs.InitialCatalog = databaseName;
//providerCs.IntegratedSecurity = true;
//providerCs.UserInstance = true;
providerCs.UserID = username;
providerCs.Password = password;
var csBuilder = new EntityConnectionStringBuilder();
csBuilder.Provider = "System.Data.SqlClient";
csBuilder.ProviderConnectionString = providerCs.ToString();
csBuilder.Metadata = string.Format("res://{0}/yourDataBase.csdl|res://{0}/yourDataBase.ssdl|res://{0}/yourDataBase.msl",
typeof(yourDataBaseEntities).Assembly.FullName);
return csBuilder.ToString();
}
}
Usage:
In your app.config place this code
<appSettings>
<add key="ServerName" value="UNKNOWN01-PC\sampleServer"/>
<add key="DatabaseName" value="samplDatabase"/>
<add key="Username" value="sampleUser"/>
<add key="Password" value="sampPass"/>
</appSettings>
Then,
string connectionString = GetSqlConnectionString();
using (MyEntities entities = new MyEntities(connectionString))
{
MyDataEntity myDataEntity = new MyDataEntity();
// Save to the database
entities.MyDataEntity.Add(myDataEntity);
entities.SaveChanges();
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
