新项目中与Team的成员讨论了一下关于数据访问层的整理和重构,基于对原有的访问层的一些不满意:主要是每个方法都要自己手写SQL语句这一状况,我提出建议是使用O/R Mapping工具,现在这类工具有如过江之鲫,但比较好用也就是那几个:nHibernate,iBatis.Net,nObject。但这一提议并没有收到效果,Team里的成员有些没有接触过这些框架,且害怕学习曲线过长,无法在项目的预期时间内完成任务,只得作罢。
后来与他们聊了聊,大家的想法是:如果能一个操作器,只要简单地将我们定义的实体类塞进去,而访问层就能完成,那就是最理想的情况——即,DAL层都不愿一个个写方法了。是人就想要偷偷懒,这也是人类进步的动力嘛:)况且程序员本来就是最懒的一群人,宁愿多用些脑细胞,也不愿多写一行代码,对此深表理解~ 说白了,要做到DAL简单的几行代码搞定,那也就是自己开发O/R Mapping工具,不是多牛,至少是初级的,能更高抽象一些重复代码工作的框架。
自然,这个光荣而艰巨的任务落在了本人的头上。怎么下手呢?首先跳入脑海的就是“反射”,无非就是,将数据库中的表一字不差的按列生成各个实体,列和实体中的字段名称相同,用反射来生成一些SQL句子,通过反射获取的字段属性PropertyInfo.GetValue()方法来做参数的绑定(SqlParameter,防注入).,最后将这些方法提取出来,结合Ado.Net方法,给DAL层提供调用。
有了思路,就可以动手了。
假设有一张这样的表:TB_NEWS,其中id列既为主键又是自增列(自增列在生成通用代码,需要注意,必须进行处理)
那么,定义一个实体类dtoNews与其对应:
namespace IndieFacade
{
public class dtoNews
{
public dtoNews() { }
private int m_id;
private string m_author;
private DateTime m_pubtime;
private string m_contents;
private int m_reads;
private string m_tags;
private string m_title;
private string m_summary;
/// <summary>
/// ID
/// </summary>
public int id
{
get { return m_id; }
set { m_id = value; }
}
/// <summary>
/// 标题
/// </summary>
public string Title
{
get { return m_title; }
set { m_title = value; }
}
/// <summary>
/// Summary
/// </summary>
public string Summary
{
get { return m_summary; }
set { m_summary = value; }
}
/// <summary>
/// Author
/// </summary>
public string Author
{
get { return m_author; }
set { m_author = value; }
}
/// <summary>
/// Tags
/// </summary>
public string Tags
{
get { return m_tags; }
set { m_tags = value; }
}
/// <summary>
/// Reads
/// </summary>
public int Reads
{
get { return m_reads; }
set { m_reads = value; }
}
/// <summary>
/// Contents
/// </summary>
public string Contents
{
get { return m_contents; }
set { m_contents = value; }
}
/// <summary>
/// 发布时间
/// </summary>
public DateTime PubTime
{
get { return m_pubtime; }
set { m_pubtime = value; }
}
}
}
然后,我写了这样一个DbCommon类,用于处理一些通用的方法,当然,只是试验了一个最简单的插入方法:
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace IndieFacade
{
/// <summary>
/// DB访问代理
/// </summary>
public class DbCommon
{
// ----------------------------------- 生成SQL串方法 --------------------------------- //
/// <summary>
/// 根据反射,将一个对象做为一条记录插入到表中
/// </summary>
/// <param name="_connString"> 数据库连接串 </param>
/// <param name="tableName"> 表名 </param>
/// <param name="dtoObject"> 实体类,与表中列名一致的实体 </param>
/// <param name="isIdentity"> 是否有自增长列 </param>
/// <param name="identityColNum"> 自增长列号 </param>
/// <param name="errMsg"> 出错信息,参数传递 </param>
/// <returns> 是否成功 </returns>
#region static bool InsertDtoToDb(string _connString,string tableName, object dtoObject, bool isIdentity, int identityColNum,ref string errMsg)
public static bool InsertDtoToDb( string _connString, string tableName, object dtoObject, bool isIdentity, int identityColNum, ref string errMsg)
{
// 通过反射,生成INSERT操作所需要的串
string _sqlcmd = DbCommon.GenerateInsertSQLText(tableName, dtoObject,isIdentity,identityColNum);
// 通过反射,生成一个SqlParameter数组
SqlParameter[] pars = DbCommon.GetParameters(dtoObject, isIdentity, identityColNum);
// 数据连接
SqlConnection _conn = new SqlConnection(_connString);
try
{
// 传入SQL语句,及绑定参数列表
int res = DbCommon.ExcuteNoQuery(_conn, _sqlcmd, pars);
if (res > 0 )
return true ;
else
return false ;
}
catch (Exception err)
{
errMsg = err.Message;
return false ;
}
finally
{
_conn.Close();
}
}
#endregion
/// <summary>
/// 生成INSERT操作所需要的串
/// </summary>
/// <param name="tableName"> 要插入的表名 </param>
/// <param name="dtoObject"> dto对象 </param>
/// <param name="isIdentity"> 是否有自增长列 </param>
/// <param name="identityColNum"> 自增长列号 </param>
/// <returns> 串 </returns>
#region static string GenerateInsertSQLText(string tableName,object dtoObject,, bool isIdentity, int identityColNum)
private static string GenerateInsertSQLText( string tableName, object dtoObject, bool isIdentity, int identityColNum)
{
string fieldstr = string .Empty; // 字段的串
string bindstr = string .Empty; // 绑定变量的串
string sqlstr = string .Empty; // 最终SQL串
// 反射DTO对象的各字段,必须把字段和DB中字段同名
System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
// 列号
int i = 0 ;
foreach (System.Reflection.PropertyInfo pi in pps)
{
if ( ! isIdentity || identityColNum != i)
{
fieldstr += pi.Name + " , " ;
bindstr += " @ " + pi.Name + " , " ;
}
i ++ ;
}
fieldstr = fieldstr.TrimEnd( new char [] { ' , ' });
bindstr = bindstr.TrimEnd( new char [] { ' , ' });
sqlstr = string .Format( " INSERT INTO {0} ({1}) VALUES({2}) " , tableName, fieldstr, bindstr);
return sqlstr;
}
#endregion
/// <summary>
/// 获取dto对象各字段对应到表中的绑定参数数组
/// </summary>
/// <param name="dtoObject"> dto对象 </param>
/// <param name="isIdentity"> 是否有自增长列 </param>
/// <param name="identityColNum"> 自增长列号 </param>
/// <returns> 绑定参数数组 </returns>
#region static SqlParameter[] GetParameters(object dtoObject, bool isIdentity, int identityColNum)
private static SqlParameter[] GetParameters( object dtoObject, bool isIdentity, int identityColNum)
{
// 反射DTO对象的各字段,必须把字段和DB中字段同名
System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
// 确定成员数量,生成动态数组
SqlParameter[] pars = (SqlParameter[])
Array.CreateInstance( typeof (SqlParameter), isIdentity ? pps.Length - 1 : pps.Length);
int i = 0 ;
foreach (System.Reflection.PropertyInfo pi in pps)
{
if ( ! isIdentity || identityColNum != i)
{
pars[i] = new SqlParameter();
pars[i].ParameterName = string .Format( " @{0} " , pi.Name);
pars[i].Value = pi.GetValue(dtoObject, null );
}
i ++ ;
}
return pars;
}
#endregion
// ----------------------------------- 工具方法 --------------------------------------- //
/// <summary>
/// SQLDATAREADER
/// </summary>
#region static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext)
public static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
try
{
conn.Open();
return cmd.ExecuteReader();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// SQLDATAREADER参数版本
/// </summary>
#region static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)
public static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
if (sqlparams.Length > 0 )
{
for ( int i = 0 ; i < sqlparams.Length; i ++ )
cmd.Parameters.Add(sqlparams[i]);
}
try
{
conn.Open();
return cmd.ExecuteReader();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// SQLSCALAR
/// </summary>
#region static object ExcuteScalar(SqlConnection conn, string cmdtext)
public static object ExcuteScalar(SqlConnection conn, string cmdtext)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// SQLSCALAR参数版本
/// </summary>
#region static object ExcuteScalar(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)
public static object ExcuteScalar(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
if (sqlparams.Length > 0 )
{
for ( int i = 0 ; i < sqlparams.Length; i ++ )
cmd.Parameters.Add(sqlparams[i]);
}
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// ExcuteNoQuery
/// </summary>
/// <returns> 影响行数 </returns>
#region static int ExcuteNoQuery(SqlConnection conn, string cmdtext)
public static int ExcuteNoQuery(SqlConnection conn, string cmdtext)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// ExcuteNoQuery参数版本
/// </summary>
/// <returns> 影响行数 </returns>
#region static int ExcuteNoQuery(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
public static int ExcuteNoQuery(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
{
SqlCommand cmd = new SqlCommand(cmdtext, conn);
if (sqlparams.Length > 0 )
{
for ( int i = 0 ; i < sqlparams.Length; i ++ )
cmd.Parameters.Add(sqlparams[i]);
}
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception err)
{
conn.Close();
throw err;
}
}
#endregion
/// <summary>
/// 获取指定表的总记录数
/// </summary>
/// <param name="connstr"></param>
/// <param name="TableName"></param>
#region static int GetRecordCount(string connstr, string TableName)
public static int GetRecordCount( string connstr, string TableName)
{
// 取表的记录数
string SQLTEXT = @" SELECT COUNT(*) FROM " + TableName;
SqlConnection conn = new SqlConnection(connstr);
try
{
int count = ( int )DbCommon.ExcuteScalar(conn, SQLTEXT);
return count;
}
catch
{
return 0 ;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
#endregion
/// <summary>
/// 获取指定表的总记录数(带条件)
/// </summary>
/// <param name="connstr"></param>
/// <param name="TableName"></param>
#region static int GetRecordCount(string connstr, string TableName,string Where)
public static int GetRecordCount( string connstr, string TableName, string Where)
{
// 取表的记录数
string SQLTEXT = @" SELECT COUNT(*) FROM " + TableName + " WHERE " + Where;
SqlConnection conn = new SqlConnection(connstr);
try
{
int count = ( int )DbCommon.ExcuteScalar(conn, SQLTEXT);
return count;
}
catch
{
return 0 ;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
#endregion
/// <summary>
/// 返回指定SQL语句查询的记录条数
/// </summary>
/// <param name="pagesize"></param>
/// <param name="bid"></param>
/// <param name="SQLTEXT"></param>
/// <param name="sqlparams"></param>
#region static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT)
public static int GetPageCountBySQL( string connstr, int pagesize, string SQLTEXT)
{
SqlConnection conn = new SqlConnection(connstr);
try
{
int pagecount = 0 ;
int count = ( int )DbCommon.ExcuteScalar(conn, SQLTEXT);
pagecount = count / pagesize;
// 余数
int yushu = count % pagesize;
if (yushu > 0 )
pagecount += 1 ;
return pagecount;
}
catch (Exception err)
{
return 0 ;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
#endregion
/// <summary>
/// 返回指定SQL语句查询的记录条数
/// </summary>
/// <param name="pagesize"></param>
/// <param name="bid"></param>
/// <param name="SQLTEXT"></param>
/// <param name="sqlparams"></param>
#region static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT,params SqlParameter[] sqlparams)
public static int GetPageCountBySQL( string connstr, int pagesize, string SQLTEXT, params SqlParameter[] sqlparams)
{
SqlConnection conn = new SqlConnection(connstr);
try
{
int pagecount = 0 ;
int count = ( int )DbCommon.ExcuteScalar(conn, SQLTEXT, sqlparams);
pagecount = count / pagesize;
// 余数
int yushu = count % pagesize;
if (yushu > 0 )
pagecount += 1 ;
return pagecount;
}
catch (Exception err)
{
return 0 ;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
#endregion
}
}
再写一个针对dtoNews实体与TB_NEWS表进行操作的DAL类:ManagerNews,调用DBCommon类中的方法进行这样的操作,将数据实体转换为一条数据记录:
using System.Collections.Generic;
using System.Text;
namespace IndieFacade
{
public class ManagerNews :IDisposable
{
private string _connString; // 连接串
private string _message; // 错误消息
private string _tableName; // 表名
#region 对外接口:错误消息
/// <summary>
/// 错误消息
/// </summary>
public string Message
{
get { return this ._message; }
}
#endregion
#region 构造函数
/// <summary>
/// 使用默认配置,进行数据库连接
/// </summary>
public ManagerNews()
{
_connString = ConfigCache.ConnStr;
_tableName = " TB_NEWS " ;
_message = string .Empty;
}
/// <summary>
/// 指定数据库连接串
/// </summary>
public ManagerNews( string connString)
{
_connString = connString;
_tableName = " TB_NEWS " ;
_message = string .Empty;
}
#endregion
// ----------------------------查询方法----------------------------- //
/// <summary>
/// 添加一个新闻
/// </summary>
/// <param name="blog"> 日志分类 </param>
/// <returns> 成功否 </returns>
#region bool AddNews(dtoNews news)
public bool AddNews(dtoNews news)
{
return DbCommon.InsertDtoToDb( this ._connString, this ._tableName, news, true , 0 , ref this ._message);
}
#endregion
// -----------------------实现Idisposable接口----------------- //
// Implement IDisposable.
// Do not make this method virtual.
// A derived class should not be able to override this method.
public void Dispose()
{
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize( this );
}
}
}
最后到客户端(例如WEB上的.aspx页面)就变得非常的轻松了:
{
dtoNews news = new dtoNews();
news.id = 1 ;
news.PubTime = DateTime.Now;
news.Reads = 1 ;
news.Summary = "" ;
news.Tags = "" ;
news.Title = "" ;
news.Contents = "" ;
news.Author = "" ;
using (ManagerNews mn = new ManagerNews())
{
bool ss = mn.AddNews(news);
}
}
这个框架还很稚嫩,还只是解决了一两个如何偷懒的问题,至少在本人可怜的512内存机器上,性能堪忧,以后再慢慢改进吧。