首先需要在网上安装目标数据库的provider安装包和bundle安装包,如sqlite需要下载Sqlite-netFx40-setup-bundle-x86-2010-1.0.106.0.exe、Sqlite-1.0.66.0-setup.exe,下载后安装,让IDE支持目标数据库。
比如我们现在有一个MsSql数据库,还需要支持Sqlite,则先将MSSql数据库中的表和数据导入到sqlite数据库中,在项目中添加EF模型,根据MsSql数据库,生成.edmx文件,这时会自动在App.config文件中添加数据库连接。我们希望可以通过代码进行连接,将这个连接信息删除掉,编写代码测试MsSql的EF模型是否正常工作:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
namespace DAL
{
public class EfUtils
{
public static readonly EfUtils Instance = new EfUtils();
public static string GetEntityConnectionString(string edmxFullName, string server, string dadaBase, string usr, string pswd, string appName)
{
EntityConnectionStringBuilder entityConnectionStringBuilder = new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Metadata = "res://*/" + edmxFullName + ".csdl|res://*/" + edmxFullName + ".ssdl|res://*/" + edmxFullName + ".msl";
entityConnectionStringBuilder.Provider = "System.Data.SqlClient";
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = server;
sqlConnectionStringBuilder.InitialCatalog = dadaBase;
sqlConnectionStringBuilder.UserID = usr;
sqlConnectionStringBuilder.Password = pswd;
entityConnectionStringBuilder.ProviderConnectionString = sqlConnectionStringBuilder.ToString();
return entityConnectionStringBuilder.ConnectionString;
}
private string GetEntityConnectionString_Sqlite(string edmxFullName)
{
EntityConnectionStringBuilder entityConnectionStringBuilder = new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Metadata = "ModelName.Sqlite.csdl|ModelName.Sqlite.ssdl|ModelName.Sqlite.msl";
entityConnectionStringBuilder.Provider = "System.Data.SQLite";
SQLiteConnectionStringBuilder sqlConnectionStringBuilder = new SQLiteConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = @"SqliteDB.db3";
sqlConnectionStringBuilder.Version = 3;
entityConnectionStringBuilder.ProviderConnectionString = sqlConnectionStringBuilder.ToString();
return entityConnectionStringBuilder.ConnectionString;
//return sqlConnectionStringBuilder.ToString();
}
private string GetConnString()
{
return GetEntityConnectionString("ModelName", "dbserver", "DBName", "sa", "password", "");
//return GetEntityConnectionString_Sqlite("ModelName");
}
public void ExecuteWithTransaction(Action fun)
{
var conStr = GetConnString();
using (EntityConnection conn = new EntityConnection(conStr))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
using (var ef = new ModelEntities(conn))
{
fun(ef);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
}
}
}
}
注意:
抽取文件的时候,查看edmx文件中的内容,有<!--SSDL content-->、<!--CSDL content-->、<!--C-S mapping content-->内容,分别将其下方的节点及其子节点拷贝出来,放在相应后缀的xml文件中,去掉拷贝出来的xml的根节点即可。
在连接字符串中,需要注意的是Metadata属性设置中指定了ssdl、csdl、msl文件的方式,默认是用res://*/后加文件名进行设置的,告诉程序去程序集的资源中取配置信息,而在Sqlite配置中,则去掉了res前缀,直接到exe程序的所在目录中查找相应资源文件。