在此,以支持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的路径
总结:上面的过程是先建立一致的数据库表,然后选择一种数据库生成概念模型,存储模型以及映射关系,然后拷贝并修改存储模型,使之与其他数据库匹配,从而完成对多数据库的支持。