... {
private static OleDbConnection _dbConnection;
[MethodImpl(MethodImplOptions.Synchronized)]
public static void CloseDBConnection()
...{
if( _dbConnection != null ) _dbConnection.Close();
}
[MethodImpl(MethodImplOptions.Synchronized)]
public static OleDbDataReader DoQuery( string sql )
...{
if( sql==null || sql.Length<1 ) return null;
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql,conn);
OleDbDataReader dbData = cmd.ExecuteReader();
_dbConnection = conn;
return dbData;
}
}
这里定义了一个静态的DoQuery方法,传入SQL语句,返回DataReader。这恐怕是整个系统里唯一显式地调用OLEDB的地方了,因为后面我们实现映射到数据表的类的时候,只需要把类成员以及这些成员的值编码成SQL语句,然后把SQL语句传给这个DoQuery方法即可。这里顺便用一下MethodImplAttribute,为的是保证线程安全。
... {
internal abstract string GetCreateTableSql( string tableName );
internal abstract string GetSelectAllSql( string tableName );
internal abstract string GetSelectSql( string tableName );
internal abstract string GetInsertSql( string tableName );
internal abstract string GetDeleteSql( string tableName );
internal abstract string GetUpdateSql( string tableName );
internal abstract string GetMaxAutoIDSql( string tableName );
}
注意(其实大家也已经猜到),一个DObject的实例正好映射数据表中的一条记录,所以DObject派生类里面定义的字段、属性、标签,就会映射到这个数据表里面的字段、及其索引、主键、外键等复杂的关系。他们具体又是怎么映射的呢?也许看过“O/X Mapping的故事”的朋友很容易会想到,不就是反射么。看,metadata多伟大。下面给个GetInsertSql ()的实现作为例子吧,全给出来就太罗嗦了。
... {
PropertyInfo[] plist = this.GetType().GetProperties();
if( plist == null ) return "";
StringBuilder sb = new StringBuilder();
sb.Append( "INSERT INTO " );
sb.Append( tableName );
sb.Append( " (");
foreach( PropertyInfo p in plist )
...{
sb.Append(p.Name+ "," );
}
string str = sb.ToString().TrimEnd(',');
str = str + ") VALUES (";
sb = new StringBuilder();
sb.Append( str );
foreach( PropertyInfo p in plist )
...{
sb.Append( GetProperty( p ) + "," );
}
str = sb.ToString().TrimEnd(',');
str = str + ")";
return str;
}
大家可能会问,为什么非要把表的名字作为参数从外面传入。当然是为了解偶啦,因为真正控制O/R Mapping的工作是在另外一个类DObjectManager里面完成的。使用DObjectManager的时候需要派生一下,派生的时候需要在向DObjectManager的构造函数传入两个参数。一个是你的派生类需要映射到的表的名字tableName,另一个是某条数据记录被查询出来后映射到的对象(即DObject的某个派生类)的类型objectType。其实我一直用的都是.NET Framework 1.1,真希望什么时候能有机会用用2.0,这样实现起范型来就不会这么难看了。
... {
protected Type _objectType;
protected string _tableName;
public DObjectManager( string tableName, Type objectType )
...{
_tableName = tableName;
_objectType = objectType;
}
public abstract bool Create();
public abstract bool Drop();
public abstract int GetMaxID();
public abstract bool Insert( DObject obj );
public abstract bool Delete( DObject obj );
public abstract bool Update( DObject obj );
public abstract DObjectCollection SelectAll();
public abstract DObjectCollection Select( DObject criteria, DOrder order );
}
虽然大家看到上面的代码里面有一大堆的abstract,但这里只是简便起见。我真正实现的时候才懒得写这么多的abstract,能在直接实现的方法我都会直接实现,当然也是为了简便起见,不想把继承树搞得很复杂。下面继续以Insert操作为例给一段代码吧,这样整个机制就可以串起来了。
... {
if( obj == null ) return false;
string sql = obj.GetInsertSql( TableName );
OleDbDataReader dbData = DataAccess.DoQuery( sql );
bool res = dbData != null;
DataAccess.CloseDBConnection();
return res;
}
最后给一段调用的例子吧,里面包含了更加丰富的编程元素,比如Attribute,集合等等,不过基本的原理都跟前面是一样的。其实不管从测试驱动的开发原则,还是从循循善诱的行文思路,我都应该把这段例子放在文章的最前面的。谁叫我这么快地想进入主题呢,还是意识流一点吧,反正这只是关于编程的故事,不是计算机科学的论文。
... {
public Customer()
...{
}
private decimal _customer_id;
[DMainKey,DAutoIncrementing]
[DField("customer_id","IDENTITY(1,1) PRIMARY KEY")]
public decimal customer_id
...{
get...{ return _customer_id; }
set...{ _customer_id = value; }
}
private string _customer_name = "";
[DField("customer_name","varchar(30)")]
public string customer_name
...{
get...{ return _customer_name; }
set...{ _customer_name = value; }
}
private string _customer_address = "";
[DField("customer_address","varchar(256)")]
public string customer_address
...{
get...{ return _customer_address; }
set...{ _customer_address = value; }
}
}
public class CustomerManager : DObjectManager
... {
public CustomerManager() : base( “CustomerTable”, typeof(Customer ) )
...{
}
}
private void QueryCustomerList()
... {
CustomerManager cm = new CustomerManager();
DObjectCollection clist = cm.SelectAll();
foreach(Customer c in clist )
...{
//…
}
}