公司现在的项目数据库访问采用主动域对象持久化技术，即在持久化对象中封装数据库操作，而业务逻辑层只管调用持久化对象的接口，不管数据库操作细节。例如：Save一个Employee对象，罗基层客户端只需调用Employee持久化对象的Save()方法就行了。
但是这样做会是数据层出现大量冗余代码，特别实在查询的时候。如果想通过ID获取一个Employee那么就会有一个GetEmployeeByID()方法，如果又按照Name查找或者想获取所有Employees呢，又会有GetEmployeeByName()和GetAllEmployees()方法。其实他们除了Sql语句不一样，其他都一样。
// Get Employee By ID
private DataSet GetEmployeeByID( string sEmployeeID )
{
string sSql = "SELECT * FROM EMPLOYEE WHERE EMPLOYEEID='"+ sEmployeeID +"'";
DataSet ds = new DataSet();
// Sql Connection
SqlConnection conn = null;
try
{
// create connection
conn = new SqlConnection( GetConnectionString() );
// create command
SqlCommand cmd = new SqlCommand( sSql, conn );
// create adapter
SqlDataAdapter adapter = new SqlDataAdapter();
// set command text for adapter
adapter.SelectCommand = cmd;
// open connection
conn.Open();
// fill data set
adapter.Fill( ds );
}
catch(Exception ex)
{
// message
System.Windows.Forms.MessageBox.Show( ex.ToString(), "Exception Information" );
// rethrow
throw ex;
}
finally
{
// close connection
if(conn != null)
{
conn.Close();
}
}
return ds;
}
// Get All Employees
private DataSet GetAllEmployees()
{
string sSql = "SELECT * FROM EMPLOYEE";
DataSet ds = new DataSet();
// Sql Connection
SqlConnection conn = null;
try
{
// create connection
conn = new SqlConnection( GetConnectionString() );
// create command
SqlCommand cmd = new SqlCommand( sSql, conn );
// create adapter
SqlDataAdapter adapter = new SqlDataAdapter();
// set command text for adapter
adapter.SelectCommand = cmd;
// open connection
conn.Open();
// fill data set
adapter.Fill( ds );
}
catch(Exception ex)
{
// message
System.Windows.Forms.MessageBox.Show( ex.ToString(), "Exception Information" );
// rethrow
throw ex;
}
finally
{
// close connection
if(conn != null)
{
conn.Close();
}
}
return ds;
}
那么有人说了，编写一个共性一点儿的方法GetEmployee()方法，将查询条件作为参数传进去，不就得了。这样是可以解决部分问题，但是又暴露出了另一个问题，那就是逻辑层组织SQL查询条件，使得逻辑层开始知道数据库操作的细节了，加强了逻辑层和数据层的耦合。如果数据库发生了修改，不但要修改数据层的代码，还要修改逻辑层的代码，即没有将数据库的修改对系统造成的冲击压制在数据层，而影响到了逻辑层。
现在我们的问题明确了，提出我们的要求：
1、减少数据层的冗余代码。
2、为逻辑层和数据层解耦。
研究上面的代码，你会发现执行数据库操作有两个公共步骤，一是组织SQL，二是执行SQL。
首先我们将执行SQL的部分封装到SqlExecutor类中，由他来负责SQL语句的执行和返回结果。客户端调用时只是发出执行SQL的请求，不管如何使用ADO.Net操作数据库。它有如下方法：
// 执行Insert/Update/Delete语句，返回执行结果
public static bool ExecuteUpdate( string sSql )
// 执行Select语句，返回DataSet
public static DataSet ExecuteQuery( string sSql )
// 执行Scalar
public static string ExecuteScalarQuery( string sSql )
这里应该还考虑存储过程和事务处理，不过作为demo，不再多说。
其次，就是将SQL的组织进行封装，最理想的是自动产生SQL，这也是NHibernate所能够完成的。也曾想直接使用NHibernate不就得了。但是，这里有三个问题：第一，我们的项目开发周期非常短，进度急，使用NHibernate的风险实在太大；第二，性能问题，构架中间曾势必带来性能上的损失；第三，灵活性，项目中总会出现查询条件非常复杂的SQL语句，例如子查询嵌套，恐到时候为了搞出一个SQL，上下求索而不能。因此，我们想引入NHibernate的思想，并对其进行简化，半自动化产生SQL语句。
让我们来看看平时都是怎样组织SQL语句的：在逻辑层将数据通过参数传入到数据层持久化对象的特定方法中，然后hard code将参数组织到对应的字段上。以保存Employee为例子：
public class Employee
{
public string sEmployeeID;
public string sFirstName;
public string sLastName;
public Employee()
{
sEmployeeID = "0";
sFirstName = "";
sLastName = "";
}
}
public bool SaveEmployee( Employee emp )
{
string sSql = "INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME) VALUES ('"+ emp.sFirstName +"', '+ emp.sLastName +')";
// execute sql
SqlExecutor.ExecuteUpdate( sSql );
}
我希望的效果是在逻辑层初始化Employee持久化对象，并对其属性进行赋值。再调用Employee的ExecuteUpdate()方法，客户端负责制定所要执行的SQL语句，该语句会在Employee中半自动产生。因此，逻辑层客户端调用代码如下：
// BizLogicObject
private void SaveEmployee()
{
// new Employee Object
Employee employee = new Employee();
// set value
employee.sEmployeeID = this.textBox1.Text;
employee.sFirstName = this.textBox2.Text;
employee.sLastName = this.textBox3.Text;
// save Employee
bool bResult = employee.ExecuteUpdate( Employee.UpdateSqlTextSelector.SaveEmployee );
MessageBox.Show("Saving Result: "+ bResult, "Message");
}
// Employee持久化对象
using System;
using System.Collections;
using System.Data;
namespace ObjectPersistent2
{
public class Employee
{
#region Public Fields
// employee id
public string sEmployeeID;
// first name
public string sFirstName;
// last name
public string sLastName;
#endregion
#region Sql Text Selector: enum
public enum UpdateSqlTextSelector
{
SaveEmployee,
DeleteEmployeeByID
}
public enum QerySqlTextSelector
{
SelectEmployeeByID,
SelectAllEmployees,
SelectEmployeeByFirstName,
}
#endregion
#region SQLs
#region Query SQLs
// Sql Text for select employee by its id
private string sSelectEmployeeByID = "SELECT EmployeeID, FirstName, LastName FROM Employees WHERE (EmployeeID = @sEmployeeID)";
// sql text for select all employee
private string sSelectAllEmployees = "SELECT EmployeeID, FirstName, LastName FROM Employees";
// sql text for select employee by first name
private string sSelectEmployeeByFirstName = "SELECT EmployeeID, FirstName, LastName FROM Employees WHERE (FirstName = '@sFirstName')";
#endregion
#region Update SQLs
// Sql Text for save employee
private string sSaveEmployee = "INSERT INTO Employees (FirstName, LastName) VALUES ('@sFirstName', '@sLastName')";
// Sql Text for delete employee by its id
private string sDeleteEmployeeByID = "DELETE FROM Employees WHERE EmployeeID = @sEmployeeID";
#endregion
#endregion
#region Constructors
public Employee()
{
this.sEmployeeID = "0";
this.sFirstName = "";
this.sLastName = "";
}
#endregion
#region Private Methods
/// <summary>
/// Functions: select and return corresponding SQL by enum SqlTextSelector
/// Author: Neo. Liu (2005-11-15)
/// </summary>
/// <param name="selector">enum: SqlTextSelector</param>
/// <returns>string: corresponding Sql</returns>
private string SelectSqlText( Employee.QerySqlTextSelector QuerySqlSelector )
{
string sSql = "";
switch( QuerySqlSelector )
{
case Employee.QerySqlTextSelector.SelectEmployeeByID:
sSql = sSelectEmployeeByID;
break;
case Employee.QerySqlTextSelector.SelectEmployeeByFirstName:
sSql = sSelectEmployeeByFirstName;
break;
case Employee.QerySqlTextSelector.SelectAllEmployees:
sSql = sSelectAllEmployees;
break;
default:
throw new Exception( "Invalid Sql Text Selector." );
}
return sSql;
}
private string SelectSqlText( Employee.UpdateSqlTextSelector UpdateSqlSelector )
{
string sSql = "";
switch( UpdateSqlSelector )
{
case Employee.UpdateSqlTextSelector.SaveEmployee:
sSql = sSaveEmployee;
break;
case Employee.UpdateSqlTextSelector.DeleteEmployeeByID:
sSql = sDeleteEmployeeByID;
break;
default:
throw new Exception( "Invalid Sql Text Selector." );
}
return sSql;
}
#endregion
#region Public Methods
public bool ExecuteUpdate( Employee.UpdateSqlTextSelector UpdateSqlSelector )
{
try
{
// get sql
string sSql = SqlTextBuilder.BuildSqlText( this.SelectSqlText( UpdateSqlSelector ) , this );
// execute sql
return SqlExecutor.ExecuteUpdate( sSql );
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return false;
}
#region Query Methods
public DataSet ExecuteQuery(Employee.QerySqlTextSelector QuerySqlSelector )
{
DataSet ds = null;
try
{
// get sql
string sSql = SqlTextBuilder.BuildSqlText( this.SelectSqlText(QuerySqlSelector ), this );
// execute sql
ds = SqlExecutor.ExecuteQuery( sSql );
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return ds;
}
#endregion
#endregion
}
}
// Sql Text Builder负责替换Sql语句
using System;
using System.Collections;
using System.Text.RegularExpressions;
using System.Reflection;
namespace ObjectPersistent2
{
/// <summary>
/// SqlTextBuilder 的摘要说明。
/// </summary>
public class SqlTextBuilder
{
#region Public static Methods
/// <summary>
/// Functions: build and return SQL
/// Author: Neo. Liu (2005-11-15)
/// </summary>
/// <param name="sSql">string: rough sql text</param>
/// <param name="PObj">Persistent Object</param>
/// <returns>string: builded sql text</returns>
public static string BuildSqlText( string sSql, object PersistentObject )
{
// sql text
string sTempSql = sSql;
//
// parse sql text, get @paramter holder
//
MatchCollection matches = GetMatchCollection( sSql );
string sPlaceHolder = "";
string sFieldName = "";
string sFieldValue = "";
// loop
foreach( Match match in matches )
{
// place hoder, such as: @N_EmployeeID
sPlaceHolder = match.Value;
// table column name, such as N_EmployeeID
sFieldName = sPlaceHolder.Replace(@"@", "" );
// get field value
System.Type ObjType = PersistentObject.GetType();
FieldInfo FieldObj = ObjType.GetField(sFieldName );
sFieldValue = FieldObj.GetValue( PersistentObject ).ToString();
// replace
sSql = sSql.Replace( sPlaceHolder, sFieldValue );
}
return sSql;
}
#endregion
#region Private static Methods
/// <summary>
/// Functions: Get match collection from SQL
/// Author: Neo. Liu (2005-11-15)
/// </summary>
/// <param name="sSql">string: target sql</param>
/// <returns>MatchCollection</returns>
private static MatchCollection GetMatchCollection( string sSql )
{
Regex RegExp = new Regex( @"@\w+" );
return RegExp.Matches( sSql );
}
#endregion
}
}
初步设想，请大家多提意见。