EntityFramework 多数据库链接,MySql,SqlServer,Oracel等
环境:EntityFramework5.0,MySql5.6,MSSQL2012
EF是强大的ORM工具,真正意义上的多数据库链接指的是不同类型的数据库,以及同种类型的数据库多个库,EF很好的支持这一点,下面简单演示下:
创建一个MVC4.0,Framework4.5的基本项目,然后重点是WebConfig配置:
<?xml version="1.0" encoding="utf-8"?> <!-- For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=152368 --> <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=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <connectionStrings> //无论多少类型,多少个同类型数据库,尽管加吧 <add name="DefaultDB" connectionString="Server=localhost;Uid=sa;Pwd=ovenjackchain;DataBase=CN9295;" providerName="System.Data.SqlClient"/> <add name="DbConMySql" providerName="MySql.Data.MySqlClient" connectionString="Data Source=172.16.205.61;Port=3306;Initial Catalog=WMC;uid=assp;pwd=assp123;" /> </connectionStrings> <appSettings> <add key="webpages:Version" value="2.0.0.0" /> <add key="webpages:Enabled" value="false" /> <add key="PreserveLoginUrl" value="true" /> <add key="ClientValidationEnabled" value="true" /> <add key="UnobtrusiveJavaScriptEnabled" value="true" /> </appSettings> <system.web> <httpRuntime targetFramework="4.5" /> <compilation debug="true" targetFramework="4.5" /> <authentication mode="Forms"> <forms loginUrl="~/Account/Login" timeout="2880" /> </authentication> <pages> <namespaces> <add namespace="System.Web.Helpers" /> <add namespace="System.Web.Mvc" /> <add namespace="System.Web.Mvc.Ajax" /> <add namespace="System.Web.Mvc.Html" /> <add namespace="System.Web.Optimization" /> <add namespace="System.Web.Routing" /> <add namespace="System.Web.WebPages" /> </namespaces> </pages> <profile defaultProvider="DefaultProfileProvider"> <providers> <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" /> </providers> </profile> <membership defaultProvider="DefaultMembershipProvider"> <providers> <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" /> </providers> </membership> <roleManager defaultProvider="DefaultRoleProvider"> <providers> <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" /> </providers> </roleManager> <sessionState mode="InProc" customProvider="DefaultSessionProvider"> <providers> <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" /> </providers> </sessionState> </system.web> <system.webServer> <validation validateIntegratedModeConfiguration="false" /> <handlers> <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" /> <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" /> <remove name="ExtensionlessUrlHandler-Integrated-4.0" /> <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" /> <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" /> <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" /> </handlers> </system.webServer> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" /> <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" /> </dependentAssembly> <dependentAssembly> <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" /> <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.0.0.0" /> </dependentAssembly> <dependentAssembly> <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" /> <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" /> </dependentAssembly> </assemblyBinding> </runtime>
下面这里注释掉,否则会默认根据默认工厂来找 <!--<entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="v11.0" /> </parameters> </defaultConnectionFactory> --><!--<defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF5"> </defaultConnectionFactory>--><!-- </entityFramework>--> <system.data>
如果你还有其他的数据库类型,那么只需要在这里加配置即可 <DbProviderFactories> <remove invariant="MySql.Data.MySqlClient" /> <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.7.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /> </DbProviderFactories> </system.data> </configuration>
以上配置部分,斜体和红字的是我修改过的,其他都是项目自动产生的。
测试:
using System; using System.Collections.Generic; using System.Configuration; using System.Data.Entity; using System.Linq; using System.Web; using System.Web.Mvc; using MySql.Data.Entity; using System.Data.Entity.ModelConfiguration.Conventions; using System.ComponentModel.DataAnnotations; namespace MvcEFMySql.Controllers { //这里是mysql的 public class MyContext : DbContext { public MyContext(string DefaultDb) : base(DefaultDb) { //Database.DefaultConnectionFactory = MySql.Data.MySqlClient.MySqlClientFactory; //Database.Connection.ConnectionString = ; Database.SetInitializer<MyContext>(null); } protected override void OnModelCreating(DbModelBuilder modelBuilder) { //已经存在的数据库,不然会出现负数 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); base.OnModelCreating(modelBuilder); } public DbSet<User> user { get; set; } } //这里是sqlserver的 public class SQLContext : DbContext { public SQLContext(string DefaultDb) : base(DefaultDb) { //Database.DefaultConnectionFactory = MySql.Data.MySqlClient.MySqlClientFactory; //Database.Connection.ConnectionString = ; Database.SetInitializer<MyContext>(null); } protected override void OnModelCreating(DbModelBuilder modelBuilder) { //已经存在的数据库,不然会出现负数 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); base.OnModelCreating(modelBuilder); } public DbSet<DO_Category> category { get; set; } } public class DO_Category { public DO_Category() { } [Key] public Guid Id { get; set; } /// <summary> /// 类目 /// </summary> public string Category { get; set; } /// <summary> /// 图标 /// </summary> public string IconName { get; set; } /// <summary> /// 排序 /// </summary> public int OrderIndex { get; set; } /// <summary> /// 父节点 /// </summary> public Guid FatherId { get; set; } public string CreateUser { get; set; } public DateTime CreateTime { get; set; } public string ModifyUser { get; set; } public DateTime? ModifyTime { get; set; } [Timestamp] public Byte[] RowVersion { get; set; } } public class User { public Guid Id { get; set; } public string UserName { get; set; } } public class HomeController : Controller { // // GET: /Home/ public ActionResult Index() { //Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>()); //操作MySql数据库 var context = new MyContext("DbConMySql"); context.user.Add(new User { Id = Guid.NewGuid(), UserName = "jackchain" }); context.SaveChanges(); var userlist= context.user.ToList(); //获取SQLServer数据库内容 var sqlc = new SQLContext("DefaultDB"); ViewBag.clist = sqlc.category.ToList(); return View(userlist); } } }
ok尽情品味吧。EF6的webconfig稍加不同。重点还是webconfig配置
mysql中文乱码问题,请在连接串最后加:Character Set=utf8;