经常在网上看到对ORM的讨论沸沸扬扬,我也来凑个热闹,谈谈我写的一个ORM。最近在做一项工作,把我们经常用到的一些业务逻辑抽象出来,写成一个个的Application Block,使之可以运用到不同的Application中,比如Data Access,Messaging,Auditing,Data binding等等。现在先做一个Data access application block。由于时间仓促,没有进行什么优化和较多的测试,大家不必深究我所提供的Code,我只希望为大家的ORM提供另一种想法。
这个application block应达到的目的:
-
封装所有的Data Access操作。
-
适合主流的DBMS:SQL Server(2000和2005),Oracle(9i和10g),DB2。
-
尽量简化Developer的操作和提供最大的灵活性,在Data Retrieval方面,只要指定SQL或者Stored Procedure和相应的参数;在Data Update方面,既可以直接调用SQL和Stored Procedure,还可以把包含多个相互关联Data Table的Dataset通过一次调用实现数据的更新。此外,可以自由地选择使用SQL还是Stored procedure;可以使用Commander builder生成Command或者使用基于Mapped stored procedure生成的Command进行数据更新。
-
实现泛型编程,使使用该AppBlock的代码能够适合所有的数据库。
-
实现Transaction。
-
提供可配置性,包括不同数据库的配置,不同Data Mapping的配置等等。
下面是该AppBlock使用到的Entity:
-
Database:Abstract Class,封装了绝大部分和具体数据库无关的Data Access操作逻辑。通过两个Mapping:IDbParameterNameMapping和IStoredProcedureNameMapping,实现Dataset和Db的一个映射。比如Dataset中Data table name和Stored procedure name的Mapping,Data table中Field和Stored procedure中参数名的Mapping。这两个Mapping是可以配置的,你只需要实现提供的Interface编写适合你的Mapping provider就可以了。
-
SqlDatabase:封装基于SQL Server 的操作。ADO.NET 2.0在1.0的基础上作了很大的改善,主要的增加的大量的基类,为我们进行泛型编程,编写和具体Db无关的代码变得异常容易。所以我们把大多数Data Access的操作可以封装在Abstract Database类中,SqlDatabase中的内容实际上是很少的。
-
OracleDatabase:封装基于Oracle的操作。
-
IDbParameterNameMapping和IStoredProcedureNameMapping:我想大家都是这样的感受,实现ORM的本质就是实现内存中的数据(主要是Dataset)和数据库的一个映射。在Dataset和数据库中的Table相互Mapping方面,我觉得没有必要采用特殊的Mapping,直接和简单易行的就是Table和Dataset中的Data Table完全匹配(table name 和field name完全匹配)。所以重要的是实现Dataset和Stored procedure的Mapping:Table Name如何与进行Insert,Update,Delete的Stored procedure name匹配,不同Version(original & current)的Field如何与Stored procedure的Parameter name 匹配。而这样一个匹配应该是可配置的,因为每个Application在数据库设计时的命名都有各自的要求,所以我在这里采用的Provider的设计模式。用户可以实现这两个Interface编写适合自己的Mapping provider,通过我提供的Configuration block很容易地完成配置。同时,我写了一个默认的,简单的Mapping:SimpleDbParameterNameMapping和SimpleStoredProcedureNameMapping。
??????有一点需要补充的是,要实现上面的Mapping,对Stored Procedure的命名有较高的要求,手工编写的方式已经不能适合我们的要求,所以我们需要一个生成Stored procedure的Generator,这个Generator也使用这两个可配置的Mapping接口。
为了使大家清楚地看出这个Application block所有的操作,我把所有的操作封装在一个IDatabase的interface中,不过需要注意的是,我采用的是基于Abstract class的编程,而不是基于Interface的编程,相信大家对这两种方式讨论得已经碰倒的太多了,孰优孰劣我就不想对说了。这个IDatabase 接口,只是展示所有Operation之用,并没有在我的代码中用到。
namespace?Artech.ApplicationBlock.DataAccess { ????public?interface?IDatabase ????{ ????????Fill?a?System.Data.DataSet?with?retrieved?data.#region?Fill?a?System.Data.DataSet?with?retrieved?data. ????????void?FillDataSet(DataSet?dataInfo,?string?commandText,?IDictionary<,1)">object>?parameters);
?????????tableName,?CommandType?commandType,1)">?parameters);
????????#endregion
????????Save?the?changed?data?which?is?stored?in?a?dataset?into?database.?Save?the?changed?data?which?is?stored?in?a?dataset?into?database.?UpdateData(DataSet?dataInfo);
?????????UpdateData(DataTable?table);
?????????UpdateData(DataTable?table,1)">?insertCommandText,1)">?updateCommandText,1)">?deleteCommandText, ???????????Dictionary?insertParameters,?Dictionary?updateParameters,1)">?deleteParameters);
????????????????????????????Dictionary?deleteParameters);
???????? ????????
????????Execute?a?command?and?return?the?affect?row?count.?Execute?a?command?and?return?the?affect?row?count.int?ExecuteNonQuery(CommandType?commandType,1)">?inputParameters,1)">?outputParameters);
?????????inputParameters);
?????????ExecuteNonQuery(??inputParameters); ????????
????????Execute?a?command?and?return?the?data?in?the?form?of?data?reader.?Execute?a?command?and?return?the?data?in?the?form?of?data?reader. ????????DbDataReader?ExecuteReader(CommandType?commandType,1)">?outputParameters);
????????DbDataReader?ExecuteReader(CommandType?commandType,1)">?inputParameters);
????????DbDataReader?ExecuteReader(?outputParameters);
????????DbDataReader?ExecuteReader(
????????Execute?a?command?and?return?a?scalar?value.?Execute?a?command?and?return?a?scalar?value.
?????????ExecuteScalar(CommandType?commandType,1)">?ExecuteScalar(
????????Transaction?based?operation?Transaction?based?operation?BeginTransaction(); ?????????Commit(); ?????????RollBack(); ???????? ????} }
这个列表和大部分ORM没有什么太大的区别,大家已经司空见惯,实现起来也不会有什么太大的困难。对于大部分操作,我不会做详细的介绍。接下来我们来简要地看看这样一个AppBlock是如何实现的。 1. Data Mapping
我们首先来看看Data Mapping:实现Dataset中Table name和Stored Procedure Name的Mapping,以及Dataset 中的Field 和Stored procedure的参Parameter?name的Mapping。
IDbParameterNameMapping
using?System; ?System.Collections.Generic; ?System.Text;
?Artech.ApplicationBlock.DataMapping { ????/**////?<summary> ?????IStoredProcedureNameMapping?defines?the?mapping?between?the?data?table?name?and?the?name?of?stored?procedures?to?perform?insertion,?modification?and?deletion?operation. ????</summary> ?????IStoredProcedureNameMapping ????{ ????????/**/ ?????????Get?the?name?of?stored?procedure?to?perform?seletion?operation. ????????</summary><param?name="tableName">The?name?of?the?database?table.</param> ????????<returns>The?name?of?stored?procedure?to?perform?seletion?operation</returns> ?????????GetSelectStoredProcedureName(?tableName);
????????/**/?Get?the?name?of?stored?procedure?to?perform?insert?operation. ????????The?name?of?stored?procedure?to?perform?insertion?operation?GetInsertStoredProcedureName(?tableName);
????????/**/?Get?the?name?of?stored?procedure?to?perform?modification?operation. ????????The?name?of?stored?procedure?to?perform?modification?operation?GetModifyStoredProcedureName(?tableName);
????????/**/?Get?the?name?of?stored?procedure?to?perform?deletion?operation. ????????The?name?of?stored?procedure?to?perform?deletion?operation?GetDeleteStoredProcedureName(?tableName); ????}
IDbParameterNameMapping
?System.Text; ?System.Data; ?Artech.ApplicationBlock.DataMapping { ????/**/?IDbParameterNameMapping?define?the?defult?mapping?between?the?source?column?name?and?the?parameter?name?of?the?corresponding?stored?procedure. ?????IDbParameterNameMapping ????{ ????????/**/?Get?the?source?column?name?based?on?the?parameter?name?of?the?related?stored?procedure. ????????<param?name="patameterName">The?parameter?name?of?the?corresponding?stored?procedure.The?source?column?name?corresponding?to?the?parameter?name.?GetSourceCoulmnName(?patameterName);
????????/**/?Get?the?source?parameter?name?based?on?the?source?column?name. ????????<param?name="columnName"><param?name="rowVersion">The?data?row?version?of?the?source?solumn?conressponding?to?the?parameter.?GetParameterName(?columnName,?DataRowVersion?rowVersion); ????}
这两个Mapping主要用在通过Dataset跟新数据库的场景,利用IDbParameterNameMapping,我们通过Dataset中各个Table name获得对它进行Insert,Update,Delete操作的Stored procedure的name。利用IDbParameterNameMapping,我们可以为Stored procedure的Parameter指定对应的Source field.??
注:GetParameterName方法实际上是不需要的,我把使用在另一个AppBlock中。
接下来我们来写两个实现了上面连个Interface的默认的mapping:SimpleStoredProcedureNameMapping和SimpleDbParameterNameMapping。他实际上实现了这样的Mapping:比如Table name为T_ABC_DEF(我经常用的命名方式:以T开头代表Table,名称大写并一下划线连接),那么对应的Stored procedure name分别为:sp_abc_def_s(Select),sp_abc_def_i(Insert),sp_abc_def_u(Update),sp_abc_def_d(delete)。如果Field name为ABC_123,那么对于Original version的Parameter name为o_abc_123(o代表Original),Current version的Parameter name为p_abc_123(p代表一般意义的Parameter)。
?Artech.ApplicationBlock.DataMapping class?SimpleStoredProcedureNameMapping:IStoredProcedureNameMapping ????{ ????????IStoredProcedureNameMapping?Members?IStoredProcedureNameMapping?Members ????????/**/?Get?the?name?of?stored?procedure?to?perform?selection?operation. ????????The?name?of?stored?procedure?to?perform?selection?operation?tableName) ????????{ ????????????//T_ABC_DEF=>sp_abc_def_s ????????????return.Format("sp_{0}_s2-).ToLower()); ????????}
????????/**/?tableName) ????????T_ABC_DEF=>sp_abc_def_isp_{0}_i
????????/**/?tableName) ????????T_ABC_DEF=>sp_abc_def_usp_{0}_u ????????/**/?tableName) ????????T_ABC_DEF=>sp_abc_def_dsp_{0}_d
????????
SimpleDbParameterNameMapping
?System.Data;
?Artech.ApplicationBlock.DataMapping { ????/**/?SimpleDbParameterNameMapping?defines?a?simple?mapping?method?between?the?corresponding?parameter?name?and?source?culumn?name?of?the?table. ?????SimpleDbParameterNameMapping:IDbParameterNameMapping ????{ ????????IDbParameterNameMapping?Members?IDbParameterNameMapping?Members
????????/**/?patameterName) ????????@p_abc_def|o_abc_def=>ABC_DEF?patameterName.Substring(3).ToUpper(); ????????}
????????/**/????????ABC_DEF?=>@p_abc_def|o_abc_def?prefix?=@p; ????????????if?(rowVersion?==?DataRowVersion.Original) ????????????{ ????????????????prefix?@o; ????????????}
????????????{0}_{1} ????????}
2. Configuration
对于一个能够被不同Application使用的Application Block,可配置性是最基本的要求,为此,我为整个AppBlock编写了单独的Configuration。在很多情况下我们在一个Application中会使用到多个具有相同或不同类型的数据库,所以这个Configuration System也许我们配置若干数据库。因而我充分利于了ConfigurationElementCollection,ConfigurationElementCollection中的每个Element对应一个具体的数据库。相关的配置信息包括:
-
一个唯一标该识数据库的DatabaseName(配置名称为name)
-
一个带面Data access provider类型的DbProvider(配置名称为dbProvider)
-
每个Db对应的ConnectionString(配置名称为connectionString)
-
代表是否是Default Db的IsDefaultDatabase(配置名称为isDefault)
-
两个Mapping :DbParameterNameMapping和StoredProcedureNameMapping(配置名称为dbParameterNameMapping和storedProcedureNameMapping,默认值为我丁一的默认Mapping?type)
-
默认的Command Type:DedaultCommandType(配置名称为dedaultCommandType,默认为使用stored procedure)
-
使用CommandBuilder生成的Command还是使用Mapped stored procedure生成的Command进行Update:UsingCommandBuilderToUpdate(配置名称为usingCommandBuilderToUpdate,默认为使用Mapped stored procedure)。
ConfigurationElementCollection
?System.Configuration;
?Artech.ApplicationBlock.Configuration ?DataAccessConfigurationCollection:?ConfigurationElementCollection ????{ ????????protectedoverride?ConfigurationElement?CreateNewElement() ????????new?DataAccessConfigurationElement(); ????????}?GetElementKey(ConfigurationElement?element) ????????{ ????????????DataAccessConfigurationElement?configurationElement??element?as?DataAccessConfigurationElement; ?????????????(configurationElement?null) ????????????{ ????????????????throw?Exception(It?is?fail?to?parse?the?data?access?configuration); ????????????}?configurationElement.DatabaseName; ????????}
DataAccessConfigurationElement
?System.Configuration; ?Artech.ApplicationBlock.Configuration ?DataAccessConfigurationElement:ConfigurationElement ????{ ????????/**/?The?database?namw?which?uniquely?identifies?the?current?database. ????????????????[ConfigurationProperty(nametrue)] ?????????DatabaseName ????????get ????????????this[]?set ?????????????value; ????????????} ????????}
????????/**/?A?string?indicating?the?concrete?database?provider. ????????dbProviderSystem.Data.SqlClient?DbProvider ???????? ???????????? ????????????
???????? ????????/**/?A?string?indicating?the?connection?string?to?connect?to?the?concrete?database. ????????connectionString?ConnectionString ???????? ???????????? ????????????
????????/**/?A?flag?which?determines?if?this?is?the?default?database. ????????isDefaultfalsebool?IsDefaultDatabase ???????? ?????????????()]; ????????????} ???????????? ????????/**/?A?bool?flag?indicating?if??the?default?operation?are?all?executed?using?command?builder?or?stored?procedure. ????????usingCommandBuilderToUpdate?UsingCommandBuilderToUpdate ???????? ???????????? ????????????
????????/**/?A?CommandType?enumeration?indicating?if?the?default?command?type. ????????dedaultCommandType?CommandType.StoredProcedure)] ?????????CommandType?DedaultCommandType ???????? ?????????????(CommandType) ???????????? ????????????
????????/**/?A?string?indicating?the?type?of?the?class?to?perform?mapping?between?parameter?and?the?source?column?name. ????????dbParameterNameMappingArtech.ApplicationBlock.DataMapping.SimpleDbParameterNameMapping,Artech.ApplicationBlock.DataMapping?DbParameterNameMapping ???????? ???????????? ????????????
????????/**/?A?string?indicating?the?type?of?the?class?to?perform?mapping?between??the?data?table?name?and?the?name?of?stored?procedures?to?perform?insertion,?modification?and?deletion?operation. ????????storedProcedureNameMappingArtech.ApplicationBlock.DataMapping.SimpleStoredProcedureNameMapping,1)">?StoredProcedureNameMapping ???????? ???????????? ????????????
DataAccessConfigurationSection
?Artech.ApplicationBlock.Configuration { ???? ?????DataAccessConfigurationSection:?ConfigurationSection ????{ ????????[ConfigurationProperty(""?DataAccessConfigurationCollection?Databases ???????? ????????????{ ????????????????DataAccessConfigurationCollection; ????????????} ????????????
????}
我们可以把我们定义的Configure通过下面的方式运用到configuration文件中。
<?xml?version="1.0"?> <configuration ????configSections ????????sectionGroup?="Artech.ApplicationBlock"?type="Artech.ApplicationBlock.Configuration.ApplicationBlockConfigurationSectionGroup,?Artech.ApplicationBlock.Configuration" ????????????section?="Artech.ApplicationBlock.DataAccess"="Artech.ApplicationBlock.Configuration.DataAccessConfigurationSection,Artech.ApplicationBlock.Configuration"/> ??????="Artech.ApplicationBlock.Messaging"="Artech.ApplicationBlock.Configuration.MessagingConfigurationSection,1)"></sectionGroupArtech.ApplicationBlock.ApplicationBlock.DataAccessadd?="sqlDatabase"??connectionString="Data?Source=JIANGJINNANSQLEXPRESS;Initial?Catalog=iFramework;Integrated?Security=True"></add?????? ????Artech.ApplicationBlock.DataAccessArtech.ApplicationBlock????
注:我把我开发的所有AppBlock相关的Configuration放在一个自定义的叫做ApplicationBlockConfigurationSectionGroup的ConfigurationSectionGroup中(<sectionGroup name="Artech.ApplicationBlock" type="Artech.ApplicationBlock.Configuration.ApplicationBlockConfigurationSectionGroup,Artech.ApplicationBlock.Configuration">),我推荐大家使用这种方式,一来可以使结构更加清晰,二来在你需要的时候,扩展该ConfigurationSectionGroup加入一些公用的配置。
3. Database Factory
为了实现泛型化的编程,使我的代码和具体的数据库无关,我采用了Factory的模式。我定义了两个重载的方法CreateDatabase,调用没有参数的方法创建一个默认的Database(isDefault=”true”),调用有参数的方法通过传入配置的Database的name创建我们需要的Database。具体的逻辑就是通过ConfigurationManager提取相应的Configuration 信息,通过DbProvider创建对应类型的Database (比如”System.Data.SqlClient”对应SQL Server)。把provider的信息传入System.Data.Common.DbProviderFactories.GetFactory方法创建一个System.Data.Common。DbProviderFactory赋值给创建的Database的DatabaseProviderFactory属性(DbProviderFactory是一个很有价值的Type,为我们创建一个泛型的ADO.NET对象提供了方便)。同时把其他的配置信息赋值给创建的Database相应的属性。提取两个Mapping Type的信息,通过Reflection的机制创建相应的类型,并同时把其他的配置信息赋值给创建的Database相应的属性。
?System.Data.Common; ?System.Reflection; ?Artech.ApplicationBlock.Configuration; ?Artech.ApplicationBlock.Common; ?Artech.ApplicationBlock.DataMapping; ?Artech.ApplicationBlock.Common.Exceptions;
?Artech.ApplicationBlock.DataAccess static?DatabaseFactory ?????ThrowAConfigurationException() ?????????CustomConfigurationException(CommonConstant.DataAccess.Messages.ConfigurationError); ????????}?DatabaseFactory() ????????{
????????}
????????/**/?Create?the?default?database. ????????Default?database.?Database?CreateDatabase() ?????????ApplicationBlockConfiguration.GetDataAccessConfigurationElement(); ????????????Database?database?;
?????????????(configurationElement.DbProvider??CommonConstant.DataAccess.ProviderInvariantName_SqlServer) ????????????{ ????????????????database??SqlDatabase(); ????????????}else ????????????{ ???????????????? ????????????}
????????????database.ConnectionString??configurationElement.ConnectionString; ????????????database.DatabaseProviderFactory??DbProviderFactories.GetFactory(configurationElement.DbProvider); ????????????database.DefaultCommandType??configurationElement.DedaultCommandType;
????????????Type?dbParameterNameMappingType??Type.GetType(configurationElement.DbParameterNameMapping); ????????????database.DbParameterNameMapping??(IDbParameterNameMapping)dbParameterNameMappingType.GetConstructor(?Type[0]).Invoke(]);
????????????Type?storedProcedureNameMappingType??Type.GetType(configurationElement.StoredProcedureNameMapping); ????????????database.StoredProcedureNameMapping??(IStoredProcedureNameMapping)storedProcedureNameMappingType.GetConstructor(]); ????????????database.UseCommandBuilder??configurationElement.UsingCommandBuilderToUpdate;
?????????????database; ????????} ????????/**/?Create?a?particular?database?by?specifying?the?configurable?database?name. ????????<param?name="databaseName">the?name?of?database?specified?in?configuration.The?database?corresponding?to?the?database?name.?Database?CreateDatabase(?databaseName) ?????????ApplicationBlockConfiguration.GetDataAccessConfigurationElement(databaseName); ????????????Database?database??CommonConstant.DataAccess.ProviderInvariantName_SqlServer) ???????????? ????????????{
????????????} ????????????database.ConnectionString? ??????? ????}
注:上面的代码是不完整的,仅仅做到SQL Server的支持,由于基于Configuration的逻辑被我封装在另一个Configuration AppBlock中,所以代码中包含有对此的调用,相信不会影响大家的理解。
[原创]我的ORM: 开发自己的Data Access Application Block - Part II (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|