How do I create connection string programmatically

问题:

How do I create connection string programmatically to MS SQL in Entity Framework 6?

I'm using c# and WPF and I was wondering if someone could show me how or link me to a resource that shows how to set up connection strings programmatically in EF 6. The MSDN article explains that you can http://msdn.microsoft.com/en-us/data/jj680699#moving but it doesn't go into creating actual connection strings.

So here is an EF6 example that works

App.Config

entityFramework codeConfigurationType="WPFwithEF.SqlConfiguration, WPFwithEF"> /entityFramework

context

public class ProductContext : DbContext
{  
    public ProductContext():base("Wpf")
    { }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }
}

Configuration.cs

namespace WPFwithEF
{
public class SqlConfiguration : DbConfiguration
{

    public SqlConfiguration()
    {
        SetProviderServices(SqlProviderServices.ProviderInvariantName,SqlProviderServices.Instance);           
        SetDefaultConnectionFactory(new SqlConnectionFactory());
    }
}
}

but if the context base is "name=Wpf" then this set up does not work is there a way to make that work? And i'm looking for the latest EF6 not the old way to do it.

回答1:

You can use the EntityConnectionStringBuilder as descriped here: How to: Build an EntityConnection Connection String

回答2:

If you are specifically connecting to a MS Sql database, this should work:

private DbConnection CreateConnection(string connectionString)
{
    return new SqlConnection(connectionString);
}

private string CreateConnectionString(string server, string databaseName, string userName, string password)
{
    var builder = new SqlConnectionStringBuilder
    {
        DataSource = server, // server address
        InitialCatalog = databaseName, // database name
        IntegratedSecurity = false, // server auth(false)/win auth(true)
        MultipleActiveResultSets = false, // activate/deactivate MARS
        PersistSecurityInfo = true, // hide login credentials
        UserID = userName, // user name
        Password = password // password
    };
    return builder.ConnectionString;
}

how to use:

public void ConnectoToDbWithEf6()
{
    using(var connection = CreateConnection(CreateConnectionString("server", "db", "you", "password")
    {
        using(var context = new YourContext(connection, true))
        {
            foreach(var someEntity in context.SomeEntitySet)
            {
                Console.WriteLine(someEntity.ToString());
            }
        }
    }

}

see https://msdn.microsoft.com/en-Us/library/system.data.sqlclient.sqlconnectionstringbuilder%28v=vs.100%29.aspx

回答3:

I previously used the DefaultConnection string found in the app.config ( or web.config) as example and just replaced the "connectionstring" on the DbContext, to the one i wanted.

The connectionstring looked something like :

Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-ShoppingList.Web-20150903103641.mdf;Initial Catalog=aspnet-ShoppingList.Web-20150903103641;Integrated Security=True

If i'm remembering correctly, you should replace the connectionstring in :

DbContext.Database.Connection.Connectionstring

PS. You can only use it this way if you are using Code-First. If you are using Model-First or Database-First you should use the EntityConnectionStringBuilder .

回答4:

I investigated this question today. in my opinion the easiest solution is not mentioned above.

Why not use the SqlConnectionStringBuilder class? (using System.Data.SqlClient)

Here a simple example how to use it.

    SqlConnectionStringBuilder sqlb = new  SqlConnectionStringBuilder(getConnectionString(DATABASENAME);

    using (SqlConnection connection = new SqlConnection(sqlb.ConnectionString))
    ...
    )

    // works for EF Core, should also work for EF6 (haven't tried this) 
    private static string getConnectionString(string databaseName)
    {
        return "Data Source=SQLSERVERNAME;Initial Catalog="+databaseName+";Integrated Security=True";
    }

回答5:

You could use the ProductivityTools.ConnectionString nuget package. It has 3 methods:

• Creates connection string to server without database name
• Creates connection string to server with the database name
• Creates connection string for EntityFramework database context

Last method will be right for you and asssuming that your edmx is named Product after invocation

ConnectionStringHelper.ConnectionString.GetSqlEntityFrameworkConnectionString
("serverName", "databaseName", "Product");

Package will return:

metadata=res://*/Product.csdl|res://*/Product.ssdl| res://*/Product.msl; 
provider=System.Data.SqlClient;provider connection string="Data Source=serverName;
Initial Catalog=databaseName;Integrated Security=True"