如何以编程方式设置Entity-Framework Code-First的连接字符串？

Question

我正在编写一些代码，以便在开发机上本地切换 SQLCE 和完整的 SQL（在 AppHarbor 上）。对于 SQL CE，连接字符串全都由系统处理，但是对于 SQL，我必须自己构建连接字符串。下面是我的代码，但是它给出了以下错误：

关键字不受支持：'metadata'

我已经在网上搜寻了几个小时，但所有的解决方案都涉及使用一个 "ContextBuilder" 类，而我找不到这个类（我已经通过 NuGet 包安装了 EF）。以下是当前的代码（在 WebActivator 中启动）：

public static void Start()
{
    // Read the details from AppSettings. Locally, these will be empty.
    var databaseHost = ConfigurationManager.AppSettings["DatabaseHost"];
    var databaseName = ConfigurationManager.AppSettings["DatabaseName"];
    var databaseUsername = ConfigurationManager.AppSettings["DatabaseUsername"];
    var databasePassword = ConfigurationManager.AppSettings["DatabasePassword"];

    // Check whether we have actual SQL Server settings.
    if (!string.IsNullOrWhiteSpace(databaseHost) && !string.IsNullOrWhiteSpace(databaseName))
    {
        // Set up connection string for a real live database :-O
        var connectionString = string.Format("metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;"
            + "provider=System.Data.SqlClient; provider connection string='Data Source={0};"
            + "Initial Catalog={1};User ID={2}; Password={3};MultipleActiveResultSets=True'",
            databaseHost, databaseName, databaseUsername, databasePassword);

        Database.DefaultConnectionFactory = new SqlConnectionFactory(connectionString);
    }
    else
    {
        // Set a custom database initializer for setting up dev database test data.
        Database.SetInitializer<BlogDataContext>(new BlogDataIntializer());

        // Set the connection factory for SQL Compact Edition.
        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
    }
}

- Danny Tuppeny

Answer 1

你应该使用 EntityConnectionStringBuilder 类

string providerName = "System.Data.SqlClient";
string serverName = ".";
string databaseName = "AdventureWorks";

// 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.IntegratedSecurity = 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;

// Set the Metadata location.
entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                        res://*/AdventureWorksModel.ssdl|
                        res://*/AdventureWorksModel.msl";
Console.WriteLine(entityBuilder.ToString());

using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
Console.WriteLine("Just testing the connection.");
conn.Close();
}

- Ghyath Serhal

---

谢谢，我已经有所进展，但是它只能在CREATE DATABASE上运行。正在安装SQL Express以便我可以编写脚本来操作数据库 - 但我不知道Code-First的元数据属性应该设置为什么。 - Danny Tuppeny

运行得很好。我添加的元数据属性必须能够工作，尽管我不知道它是从哪里找到这些元数据文件的 :D - Danny Tuppeny

这些元数据文件是由Entity Framework在第一次连接到数据库时动态生成的。 - Christopher Stevenson

数据库的用户名和密码怎么办？我们在这段代码中放在哪里？ - KADEM Mohammed

您应该使用以下属性：sqlBuilder.UserID = "userId"; sqlBuilder.Password = "password"; - Ghyath Serhal

Answer 2

在 Entity-Framework Code-First 中使用 SqlConnection。你不能使用 EntityConnectionStringBuilder，因为在 Code-First 中没有元数据文件。

- rraszewski

---

这帮助我弄清楚我需要使用实体字符串生成器来进行数据库优先 :) +1 - Piotr Kula

Answer 3

您还可以直接在上下文中设置它。首先，您必须将mdf附加到要使用的SqlServer实例上。虽然不是非常优雅，但对我来说起作用了。

public void DoImportWork()
{
   var ctx = new StatisticsContext(); << your DbContext 

   ctx.Database.Connection.ConnectionString = @"Data Source=localhost\SQLEXP;AttachDbFilename=""C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXP\MSSQL\DATA\StatisticsData.mdf"";Integrated Security=True";

   ctx.Database.Connection.Open();
}

通常情况下，当您向上下文添加第一行时，EF会自动生成所有内容。

- user2882366

Answer 4

这段代码对于使用 Entity Framework 创建 连接字符串 非常有用。

    public string GenerateEFConnectionString(string serverName, string dbName,string ModelName,string userName,string password)
    {   
        // Initialize the connection string builder for the underlying provider.
        SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = dbName;
        //sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;
        sqlBuilder.MultipleActiveResultSets = true;

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        entityBuilder.Provider = "System.Data.SqlClient";
        entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",ModelName); 
        return (entityBuilder.ToString().Replace("\"","""));
    }

它可以从一组参数中调用

    GenerateEFConnectionString("srv","db","mod","vinodsrivastav","nopassword");

生成像这样的连接字符串

metadata=res://*/mod.csdl|res://*/mod.ssdl|res://*/mod.msl;provider=System.Data.SqlClient;provider connection string="Data Source=srv;Initial Catalog=db;User ID=vinodsrivastav;Password=nopassword;MultipleActiveResultSets=True"

- Vinod Srivastav