Entity framework多数据库切换支持
在此,以支持SQL2008和ORACLE为例:
1、设计数据库各个表,请注意两个数据库各个表生成.net对应实体的数据类型必须保证会一致,因为各个数据库共用的是一个实体,这就要求对各个数据库的字段类型通过EF生成实体的数据类型要非常的了解;
2、 建立好数据库后,选择其中一种类型数据库(此处选择ORACLE数据库),创建ADO.NET实体数据类型(此处命名为TestModel),并保存;
3、至此,我们有了Web.config的数据库连接相关配置、TestModel.edmx和TestModel.Designer.cs两个文件;
4、现在,我们通过程序支持SQL2008数据库,首先,需要手动创建存储模型文件,在项目中新建名为TestModel.SqlServer.ssdl 的xml文件。然后右击TestModel.edmx选择XML(文本)编辑器,将<!-- SSDL content -->之后介于 <edmx:StorageModels> … </edmx:StorageModels>之间的内容拷贝至新建的TestModel.SqlServer.ssdl文件中,这样TestModel.SqlServer.ssdl的内容应该如下:
<?xml version= "1.0" encoding= "utf-8" ?> <Schema Namespace= "OracleModel.Store" Alias= "Self" Provider= "Oracle.DataAccess.Client" ProviderManifestToken= "11.2" xmlns:store= "http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns= "http://schemas.microsoft.com/ado/2009/02/edm/ssdl" > <EntityContainer Name= "OracleModelStoreContainer" > <EntitySet Name= "DEPARTMENTINFO" EntityType= "OracleModel.Store.DEPARTMENTINFO" store:Type= "Tables" Schema= "LDSA" /> <EntitySet Name= "USERINFO" EntityType= "OracleModel.Store.USERINFO" store:Type= "Tables" Schema= "LDSA" /> <AssociationSet Name= "部门外键" Association= "OracleModel.Store.部门外键" > <End Role= "DEPARTMENTINFO" EntitySet= "DEPARTMENTINFO" /> <End Role= "USERINFO" EntitySet= "USERINFO" /> </AssociationSet> </EntityContainer> <EntityType Name= "DEPARTMENTINFO" > <Key> <PropertyRef Name= "ID" /> </Key> <Property Name= "ID" Type= "number" Nullable= "false" Precision= "10" /> <Property Name= "NAME" Type= "nvarchar2" Nullable= "false" MaxLength= "20" /> </EntityType> <EntityType Name= "USERINFO" > <Key> <PropertyRef Name= "ID" /> </Key> <Property Name= "ID" Type= "number" Nullable= "false" Precision= "10" /> <Property Name= "NAME" Type= "nvarchar2" Nullable= "false" MaxLength= "20" /> <Property Name= "DEPARTID" Type= "number" Nullable= "false" Precision= "10" /> <Property Name= "CREATEDATE" Type= "date" /> </EntityType> <Association Name= "部门外键" > <End Role= "DEPARTMENTINFO" Type= "OracleModel.Store.DEPARTMENTINFO" Multiplicity= "1" > <OnDelete Action= "Cascade" /> </End> <End Role= "USERINFO" Type= "OracleModel.Store.USERINFO" Multiplicity= "*" /> <ReferentialConstraint> <Principal Role= "DEPARTMENTINFO" > <PropertyRef Name= "ID" /> </Principal> <Dependent Role= "USERINFO" > <PropertyRef Name= "DEPARTID" /> </Dependent> </ReferentialConstraint> </Association> </Schema> |
5、接着修改 TestModel.SqlServer.ssdl 存储模型文件,使之能与匹配Sql Server 2008数据库表。首先我们需要将 Provider 和 ProviderManifestToken修改为:
Provider= "System.Data.SqlClient" ProviderManifestToken= "2008" |
<?xml version= "1.0" encoding= "utf-8" ?> <Schema Namespace= "OracleModel.Store" Alias= "Self" Provider= "System.Data.SqlClient" ProviderManifestToken= "2008" xmlns:store= "http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns= "http://schemas.microsoft.com/ado/2009/02/edm/ssdl" > <EntityContainer Name= "OracleModelStoreContainer" > <EntitySet Name= "DEPARTMENTINFO" EntityType= "OracleModel.Store.DEPARTMENTINFO" store:Type= "Tables" Schema= "dbo" /> <EntitySet Name= "USERINFO" EntityType= "OracleModel.Store.USERINFO" store:Type= "Tables" Schema= "dbo" /> <AssociationSet Name= "部门外键" Association= "OracleModel.Store.部门外键" > <End Role= "DEPARTMENTINFO" EntitySet= "DEPARTMENTINFO" /> <End Role= "USERINFO" EntitySet= "USERINFO" /> </AssociationSet> </EntityContainer> <EntityType Name= "DEPARTMENTINFO" > <Key> <PropertyRef Name= "ID" /> </Key> <Property Name= "ID" Type= "int" Nullable= "false" StoreGeneratedPattern= "Identity" /> <Property Name= "NAME" Type= "nvarchar" Nullable= "false" MaxLength= "20" /> </EntityType> <EntityType Name= "USERINFO" > <Key> <PropertyRef Name= "ID" /> </Key> <Property Name= "ID" Type= "int" Nullable= "false" StoreGeneratedPattern= "Identity" /> <Property Name= "NAME" Type= "nvarchar" Nullable= "false" MaxLength= "20" /> <Property Name= "DEPARTID" Type= "int" Nullable= "false" /> <Property Name= "CREATEDATE" Type= "date" /> </EntityType> <Association Name= "部门外键" > <End Role= "DEPARTMENTINFO" Type= "OracleModel.Store.DEPARTMENTINFO" Multiplicity= "1" > <OnDelete Action= "Cascade" /> </End> <End Role= "USERINFO" Type= "OracleModel.Store.USERINFO" Multiplicity= "*" /> <ReferentialConstraint> <Principal Role= "DEPARTMENTINFO" > <PropertyRef Name= "ID" /> </Principal> <Dependent Role= "USERINFO" > <PropertyRef Name= "DEPARTID" /> </Dependent> </ReferentialConstraint> </Association> </Schema> |
6、最后Web.config增加sql2008数据库连接如下:
oracle:(创建ADO.NET实体数据类型自动生成)
<!--<add name= "DBEntities" connectionString= "metadata=res://*/Models.TestModel.csdl|res://*/Models.TestModel.ssdl|res://*/Models.TestModel.msl;provider=Oracle.DataAccess.Client;provider connection string=" DATA SOURCE=//192.168.16.155/testdb;PASSWORD=123456;PERSIST SECURITY INFO=True;USER ID=LDSA "" providerName= "System.Data.EntityClient" />--> sql2008: <add name= "DBEntities" connectionString= "metadata=res://*/Models.TestModel.csdl|~/Models/TestModel.SqlServer.ssdl|res://*/Models.TestModel.msl;provider=System.Data.SqlClient;provider connection string=" Data Source=ZHANGDEYI-PC\ADESQL;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456; "" providerName= "System.Data.EntityClient" /> |
提示: ~/Models/TestModel.SqlServer.ssdl 存放TestModel.SqlServer.ssdl的路径
总结:上面的过程是先建立一致的数据库表,然后选择一种数据库生成概念模型,存储模型以及映射关系,然后拷贝并修改存储模型,使之与其他数据库匹配,从而完成对多数据库的支持。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂