Basically, there are two approaches regarding persistence framework:
1. Code Generation: generate whole DAL based on metadata either from RDBMS or other source
2. O/R Mapping: either use dynamic SQL or Static SQL map objects to RDBMS
There are a lot of hot debates about which approach is better, IMO it all depends, it should be study case by case which approach you will be used to build your persistence framework.
There are some good open source and commeical code generation framework and O/R mapper, such as CodeSmith, NHiberate, LLBGen Pro, Entity Broker etc.
Our Persistence framework, Lattice.DataMapper(http://www.latticesoft.com), which support both Oracle and SQL Servere, is kind of mixing two approachs together, we have a XML/XSLT template based tool to generate stored procedures, data transfer object and xml mapping files as well as a persistence framework to map objects to RDBMS using external XML mapping files. So you do not need to write your CRUD operation for each business object again and again, below is code snippet to create a new employee record in Oracle SCOTT calling stored procedure Insert_EMP in EMP_PKG package which generated by our XML/XSLT code generator:
IDataMapper mapper = DataManager.GetInstance().Create("Mapping.config", "SCOTT");
EmployeeInfo employeeInfo = new EmployeeInfo();
employeeInfo.Name = "Joe";
....
//Creater a new record and return sequence number
int EmployeeID = mapper.Create("addEmployee", employeeInfo);
Here is XML mapping file layout:
<command name="addEmployee" type="storedprocedure" returnType="Identity">
<commandtext>EMP_PKG.Insert_EMP</commandtext>
<parameter name="ENAME" fieldName="Name" direction="Input" />
<parameter name="JOB" fieldName="Title" direction="Input" />
<parameter name="HIREDATE" fieldName="HireDate" direction="Input" />
<parameter name="EMPNO" fieldName="EmployeeID" dbType="Integer" direction="OutPut" />
</command>
Here is XML configuration file layout:
<dataProviders>
<dataProvider name="SqlClient"
connectionType="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
<dataProvider name="OracleClient"
connectionType="System.Data.OracleClient.OracleConnection, System.Data.OracleClient,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</dataProviders>
<dataSources>
<dataSource name="NORTHWIND"
provider="SqlClient"
connectionString="Server=(local);Database=northwind;User ID=sa;Password="
dataMappingDir="/Map/SQLServer/"
/>
<dataSource name="SCOTT"
provider="OracleClient"
connectionString="Data Source=lattice; User ID=SCOTT; Password=TIGER"
dataMappingDir="/Map/Oracle/"
/>
</dataSources>
Li Xin
Get it Done! Simple is Best!
Lattice.DataMapper Persistence Framework for .NET publicschoolportal.com