.net SQLite EF6 在运行时以编程方式设置连接字符串

SQLite EF6 programmatically set connection string at runtime

提问by WebDucer

I try to migrate form EF 3.5 to 6 (with SQLite as database). We can not set the connection string in the app config file (this works without problems with ef6). We have to set connection string programmatically at runtime (after user has selected the SQLite file).

我尝试从 EF 3.5 迁移到 6（使用 SQLite 作为数据库）。我们无法在应用程序配置文件中设置连接字符串（这对 ef6 没有问题）。我们必须在运行时以编程方式设置连接字符串（在用户选择 SQLite 文件之后）。

Here is our app.config

这是我们的 app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="testContext" connectionString="data source=Data\testdb.sqlite;Foreign Keys=True"
      providerName="System.Data.SQLite" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="System.Data.SQLite" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    </DbProviderFactories>
  </system.data>
</configuration>

Here ist the DbContext

这是 DbContext

public class FirmwareContext : DbContext
{
    public FirmwareContext()
        : this(DEFAULT_CONNECTION)
    {

    }

    public FirmwareContext(string connextionNameOrString)
        : base(connextionNameOrString)
    {

    }
}

If I connect with the connecation name from app.config all works without problems. If I try to pass the connection string with the second constructor this fails

如果我使用 app.config 中的连接名称连接，则一切正常。如果我尝试使用第二个构造函数传递连接字符串，则会失败

Here is small example

这是小例子

SQLiteConnectionStringBuilder builder =
    factory.CreateConnectionStringBuilder() as SQLiteConnectionStringBuilder;
builder.DataSource = dataSource; // Path to file name of the user
builder.ForeignKeys = true;

var context = new FirmwareContext(builder.ToString());

var context = new FirmwareContext(builder.ToString());
var test = context.Firmware.FirstOrDefault();

I got the following exception ("Schlüsselwort wird nicht unterstützt: 'foreign keys'.") => The key 'foreign key' is nott supported. If I remove the foreign key set, I got the following exception ("The provider did not return a ProviderManifestToken string.") and some inner exceptions.

我收到以下异常（“Schlüsselwort wird nicht unterstützt: 'foreign keys'。”）=> 不支持键 'foreign key'。如果删除外键集，则会出现以下异常（“提供程序未返回 ProviderManifestToken 字符串。”）和一些内部异常。

It seems that the bas(connectionString) build the MSSQL connection string and for SQLite.

bas(connectionString) 似乎为 SQLite 构建了 MSSQL 连接字符串。

How can I make my second constructor campatible with sqlite?

如何使我的第二个构造函数与 sqlite 兼容？

回答by kjbartel

I was having the same problem. I found a workaround by using a different constructor from the base DbContext class:

我遇到了同样的问题。我通过使用与基本 DbContext 类不同的构造函数找到了一种解决方法：

public DbContext(DbConnection existingConnection, bool contextOwnsConnection);

Using this override you can pass an SQLiteConnection instead which you set the connection string on. So for example you can add a new constructor to your FirmwareContext.

使用此覆盖，您可以传递 SQLiteConnection 而不是您设置连接字符串。例如，您可以向 FirmwareContext 添加一个新的构造函数。

public FirmwareContext(string connectionString)
    : base(new SQLiteConnection() { ConnectionString = connectionString }, true)
{
}

Or even

甚至

public FirmwareContext(string filename)
    : base(new SQLiteConnection() { ConnectionString =
            new SQLiteConnectionStringBuilder()
                { DataSource = filename, ForeignKeys = true }
            .ConnectionString }, true)
{
}

回答by kjbartel

After looking at this further it seems the problem is that there isn't a IDbConnectionFactory defined for SQLite. So another approach would be to define your own factory implementation.

进一步查看后，似乎问题在于没有为 SQLite 定义 IDbConnectionFactory。所以另一种方法是定义你自己的工厂实现。

public class SQLiteConnectionFactory : IDbConnectionFactory
{
    public DbConnection CreateConnection(string connectionString)
    {
        return new SQLiteConnection(connectionString);
    }
}

Then replace the defaultConnectionFactoryentry in your 'app.config' with something like:

然后将defaultConnectionFactory“app.config”中的条目替换为以下内容：

<defaultConnectionFactory type="MyDAL.SQLiteConnectionFactory, MyDAL" />

回答by Ansary Ans21

Normally the connection string in config would look like this

通常配置中的连接字符串看起来像这样

<add name="MuthootOneClientEntities" connectionString="metadata=res://*/LocalData.MuthootClientContext.csdl|res://*/LocalData.MuthootClientContext.ssdl|res://*/LocalData.MuthootClientContext.msl;provider=System.Data.SQLite;provider connection string=&quot;data source=D:\LocalRepo\trunk\dev\Muthoot.MuthootOne.API\src\Muthoot.MuthootOne.API.Services\LocalData\FD1CBA65-1B68-449F-8E6D-A652137466D4.db&quot;" providerName="System.Data.EntityClient" />

change the above to following.(remove &quot ; to "") and mention it directly in entity context connection string with your own database location.

将上面的内容更改为以下内容。（删除“;”到“”）并在实体上下文连接字符串中使用您自己的数据库位置直接提及它。

public partial class MuthootClientEntities : DbContext
{
    public MuthootClientEntities()
        : base(@"metadata=res://*/LocalData.MuthootClientContext.csdl|res://*/LocalData.MuthootClientContext.ssdl|res://*/LocalData.MuthootClientContext.msl;provider=System.Data.SQLite;provider connection string=""data source=" + System.Environment.CurrentDirectory + @"\LocalData\FD1CBA65-1B68-449F-8E6D-A652137466D4.db""")        
    {
        var test = System.Environment.CurrentDirectory;
    }