Dynamic connection string for EF First code
My current connection string, which is in Web.config, is this:
<add name="PersonDBContext"
connectionString="Server=111.111.1.11;
Database=MyProgram;
User Id=admin;
Password=12345;
Integrated Security=False"
providerName="System.Data.SqlClient" />
I want the same program connection string to be dynamic, this is my attempt:
EntityConnectionStringBuilder csb = new EntityConnectionStringBuilder();
csb.ProviderConnectionString = "Data Source=111.111.1.11;Initial Catalog=MyProgram;User Id=admin;Password=12345;Integrated Security=False";
csb.Provider = "System.Data.SqlClient";
String entityConnStr = csb.ToString();
return entityConnStr;
And this is what I get:
Keyword not supported: vendor.
Can you tell me what I am doing wrong? And do I need metadata for the First Code line of code? Thank.
EDIT: I figured that I either shouldn't use the EntityConnectionStringBuilder or I should give the metadata for the EntityConnectionStringBuilder class. Can you tell me one way how to do this?
source to share
What I needed to do in order to have a dynamic connection string so that users could enter or select the server they wanted to connect to was as follows:
In the Model.Context.cs file that EF creates, I changed the constructor to:
public partial class Entities : DbContext
{
public Entities()
: base(BuildConnectionString)
{
}
...
}
And then I wrote the EntitiesEx.cs extension class
partial class Entities : DbContext
{
private static string BuildConnectionString
{
get
{
// Specify the provider name, server and database.
string providerName = "System.Data.SqlClient";
string serverName = DatabaseController.Server;
string databaseName = <DatabaseName>;
// 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.UserID = <user>;
sqlBuilder.Password = <password>;
sqlBuilder.IntegratedSecurity = false;
sqlBuilder.PersistSecurityInfo = true;
sqlBuilder.MultipleActiveResultSets = true;
// 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;
//assembly full name
Type t = typeof(Entities);
string assemblyFullName = t.Assembly.FullName.ToString();
// Set the Metadata location.
entityBuilder.Metadata = string.Format("res://{0}/", //Models.Model.csdl|Models.Model.ssdl|Models.Model.msl",
assemblyFullName);
try
{
//Test de conexion
using (EntityConnection conn = new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
conn.Close();
}
}
catch (Exception ex)
{
throw new Exception("Connection error" + ex.Message);
}
return entityBuilder.ToString();
}
}
Like con, every time you create your model from the database you will have to change your constructor in the Entities class (Model.Context.cs)
source to share
Dynamic connection string for code First use a simple sql connection string or not EntityConnectionStringBuilder. So you can achieve it like this.
public static string DynamicConnectionString(SqlConnectionStringBuilder builder)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "ServerName";
builder.InitialCatalog = "DatabaseName";
builder.UserID = "UserId";
builder.Password = "Password";
builder.MultipleActiveResultSets = true;
builder.PersistSecurityInfo = true;
return builder.ConnectionString.ToString();
}
source to share