这两天重温经典，对ado.net的东西稍微深入的了解了一下，顺便写点代码练练手，全当是复习笔记吧。

一、简单说说ado.net的5大常用对象

既然说ado.net，当然不能免俗地要提到5大常用对象。本文不会对ado.net的5大对象和它们的关系进行过多阐释，不过我们应该对下面这张图的结构有个了解：

关于上图图示中的5大对象，经常做以数据为驱动的mis系统的童鞋应该不会陌生。本文一笔带过。下面我们一步一步实现以ado.net为核心的数据访问程序。

二、数据访问持久化层

1、IDbOperation接口

代码

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

namespace AdoNetDataaccess.Core.Contract

{

public interface IDbOperation

{

DbCommand CreateDbCommd(DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DbParameter CreateDbPRameter(string paramName, object paramValue);

DbDataReader ExecuteReader(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

int ExecuteNonQuery(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

/// <summary>

/// 批量插入

/// </summary>

/// <param name="tableName">表名称</param>

/// <param name="dt">组装好的要批量导入的datatable</param>

/// <returns></returns>

bool ExecuteBatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt);

void OpenConnection();

void CloseConnection();

}

}

上面的接口包括增删改查，批量插入以及数据库连接对象的连接和关闭等常用操作，您可以根据命名和参数轻松理解函数的含义。根据楼猪的开发经验，对于平时的数据库操作，上述方法差不多够用了。当然您也可以按照自己需要，重写组织添加其他函数。

2、针对一种数据源的数据操作实现

底层的数据操作接口定义好后，就要针对一种数据源，具体实现上述的数据操作。这里楼猪选择了Sql Server。我们也可以实现其他数据源的数据访问操作，按照配置，利用抽象工厂动态反射选择是哪一种数据源的实现。这里按下不表，有心的童鞋自己可以动手一试。下面是具体的实现：

代码

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

using System.Transactions;

namespace AdoNetDataAccess.Core.Implement

{

using AdoNetDataAccess.Core.Contract;

public class SqlServer : IDbOperation, IDisposable

{

private int cmdTimeOut = 60;

private DbConnection sqlConn = null;

private DbCommand cmd = null;

private SqlServer()

{

}

public SqlServer(string sqlConStr)

{

sqlConn = new SqlConnection(sqlConStr);

cmdTimeOut = sqlConn.ConnectionTimeout;

}

public SqlServer(string sqlConStr, int timeOut)

{

sqlConn = new SqlConnection(sqlConStr);

if (timeOut < 0)

{

timeOut = sqlConn.ConnectionTimeout;

}

cmdTimeOut = timeOut;

}

#region contract method

public DbCommand CreateDbCommd(DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

DbCommand cmd = new SqlCommand();

cmd.Connection = sqlConn;

cmd.CommandText = sqlStr;

cmd.CommandType = cmdType;

if (transaction != null)

{

cmd.Transaction = transaction;

}

if (listParams != null && listParams.Count > 0)

{

cmd.Parameters.AddRange(listParams.ToArray());

}

cmd.CommandTimeout = cmdTimeOut;

OpenConnection();

return cmd;

}

public DbParameter CreateDbPrameter(string paramName, object paramValue)

{

SqlParameter sp = new SqlParameter(paramName, paramValue);

return sp;

}

public DbDataReader ExecuteReader(string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

DbDataReader rdr = null;

try

{

OpenConnection();

cmd = CreateDbCommd(sqlConn, null, sqlStr, cmdType, listParams);

rdr = cmd.ExecuteReader();

}

catch (Exception ex)

{

throw ex;

}

return rdr;

}

public DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

OpenConnection();

DbTransaction trans = sqlConn.BeginTransaction();

DbCommand cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);

SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);

DataTable dt = new DataTable();

try

{

sqlDataAdpter.Fill(dt);

trans.Commit();

}

catch (Exception e)

{

trans.Rollback();

throw new Exception("执行数据库操作失败, sql: " + sqlStr, e);

}

finally

{

sqlDataAdpter.Dispose();

cmd.Dispose();

trans.Dispose();

CloseConnection();

}

return dt;

}

public DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

OpenConnection();

DbTransaction trans = sqlConn.BeginTransaction();

DbCommand cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);

SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);

DataSet ds = new DataSet();

try

{

sqlDataAdpter.Fill(ds);

trans.Commit();

}

catch (Exception e)

{

trans.Rollback();

throw new Exception("执行数据库操作失败, sql: " + sqlStr, e);

}

finally

{

sqlDataAdpter.Dispose();

cmd.Dispose();

trans.Dispose();

CloseConnection();

}

return ds;

}

public object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

object result = null;

OpenConnection();

DbTransaction trans = sqlConn.BeginTransaction();

try

{

cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);

result = cmd.ExecuteScalar();

trans.Commit();

}

catch (Exception e)

{

trans.Rollback();

throw new Exception("执行数据库操作失败, sql: " + sqlStr, e);

}

finally

{

trans.Dispose();

CloseConnection();

}

return result;

}

public int ExecuteNonQuery(string sqlStr, CommandType cmdType, List<DbParameter> listParams)

{

int result = -1;

OpenConnection();

DbTransaction trans = sqlConn.BeginTransaction();

try

{

cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);

result = cmd.ExecuteNonQuery();

trans.Commit();

}

catch (Exception e)

{

trans.Rollback();

throw new Exception("执行数据库操作失败, sql: " + sqlStr, e);

}

finally

{

trans.Dispose();

CloseConnection();

}

return result;

}

/// <summary>

/// 批量插入

/// </summary>

/// <param name="tableName"></param>

/// <param name="batchSize"></param>

/// <param name="copyTimeout"></param>

/// <param name="dt"></param>

/// <returns></returns>

public bool ExecuteBatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt)

{

bool flag = false;

try

{

using (TransactionScope scope = new TransactionScope())

{

OpenConnection();

using (SqlBulkCopy sbc = new SqlBulkCopy(sqlConn as SqlConnection))

{

//服务器上目标表的名称

sbc.DestinationTableName = tableName;

sbc.BatchSize = batchSize;

sbc.BulkCopyTimeout = copyTimeout;

for (int i = 0; i < dt.Columns.Count; i++)

{

//列映射定义数据源中的列和目标表中的列之间的关系

sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

}

sbc.WriteToServer(dt);

flag = true;

scope.Complete();//有效的事务

}

}

}

catch (Exception ex)

{

throw ex;

}

return flag;

}

public void OpenConnection()

{

if (sqlConn.State == ConnectionState.Broken || sqlConn.State == ConnectionState.Closed)

sqlConn.Open();

}

public void CloseConnection()

{

sqlConn.Close();

}

#endregion

#region dispose method

/// <summary>

/// dispose接口方法

/// </summary>

public void Dispose()

{

}

#endregion

}

}

到这里，我们实现了SqlServer类里的方法，对Ms SqlServer数据库我们就已经可以进行简单的基础的CRUD操作了。

三、简单直观的对象实体转换

在第二步中，我们已经实现了简单的数据CRUD操作。根据楼猪使用ORM的经验和习惯，我们也应该对一些查询结果进行转换，因为以类的组织方式比直接呈现ado.net对象更容易让人接受，效率高低反在其次。下面利用常见的反射原理，简单实现一个对象实体转换器ModelConverter类：

代码

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Reflection;

using System.Threading;

namespace AdoNetDataAccess.Core.Obj2Model

{

using AdoNetDataAccess.Core.Contract;

public sealed class ModelConverter

{

private static readonly object objSync = new object();

#region query for list

/// <summary>

/// 查询数据表项并转换为对应实体

/// </summary>

/// <typeparam name="T"></typeparam>

/// <param name="objType"></param>

/// <param name="rdr"></param>

/// <returns></returns>

public static IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType, IDbOperation dbOperation)

where T : class, new()

{

IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);

IList<T> listModels = new List<T>();

try

{

Monitor.Enter(objSync);

Hashtable ht = CreateHashColumnName(rdr);

while (rdr.Read())

{

Object obj = Activator.CreateInstance(objType);

PropertyInfo[] properties = objType.GetProperties();

foreach (PropertyInfo propInfo in properties)

{

string columnName = propInfo.Name.ToUpper();

if (ht.ContainsKey(columnName) == false)

{

continue;

}

int index = rdr.GetOrdinal(propInfo.Name);

object columnValue = rdr.GetValue(index);

if (columnValue != System.DBNull.Value)

{

SetValue(propInfo, obj, columnValue);

}

}

T model = default(T);

model = obj as T;

listModels.Add(model);

}

}

finally

{

rdr.Close();

rdr.Dispose();

Monitor.Exit(objSync);

}

return listModels;

}

#endregion

#region query for dictionary

/// <summary>

/// 查询数据表项并转换为对应实体

/// </summary>

/// <typeparam name="K"></typeparam>

/// <typeparam name="T"></typeparam>

/// <param name="key">字典对应key列名</param>

/// <param name="objType"></param>

/// <param name="rdr"></param>

/// <returns></returns>

public static IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType, IDbOperation dbOperation)

where T : class, new()

{

IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);

IDictionary<K, T> dictModels = new Dictionary<K, T>();

try

{

Monitor.Enter(objSync);

Hashtable ht = CreateHashColumnName(rdr);

while (rdr.Read())

{

Object obj = Activator.CreateInstance(objType);

PropertyInfo[] properties = objType.GetProperties();

object dictKey = null;

foreach (PropertyInfo propInfo in properties)

{

string columnName = propInfo.Name.ToUpper();

if (ht.ContainsKey(columnName) == false)

{

continue;

}

int index = rdr.GetOrdinal(propInfo.Name);

object columnValue = rdr.GetValue(index);

if (columnValue != System.DBNull.Value)

{

SetValue(propInfo, obj, columnValue);

if (string.Compare(columnName, key.ToUpper()) == 0)

{

dictKey = columnValue;

}

}

}

T model = default(T);

model = obj as T;

K objKey = (K)dictKey;

dictModels.Add(objKey, model);

}

}

finally

{

rdr.Close();

rdr.Dispose();

Monitor.Exit(objSync);

}

return dictModels;

}

#endregion

#region internal util

private static Hashtable CreateHashColumnName(IDataReader rdr)

{

int len = rdr.FieldCount;

Hashtable ht = new Hashtable(len);

for (int i = 0; i < len; i++)

{

string columnName = rdr.GetName(i).ToUpper(); //不区分大小写

string columnRealName = rdr.GetName(i);

if (ht.ContainsKey(columnName) == false)

{

ht.Add(columnName, columnRealName);

}

}

return ht;

}

private static void SetValue(PropertyInfo propInfo, Object obj, object objValue)

{

try

{

propInfo.SetValue(obj, objValue, null);

}

catch

{

object realValue = null;

try

{

realValue = Convert.ChangeType(objValue, propInfo.PropertyType);

propInfo.SetValue(obj, realValue, null);

}

catch (Exception ex)

{

string err = ex.Message;

//throw ex; //在数据库数据有不符合规范的情况下应该及时抛出异常

}

}

}

#endregion

}

}

到这里，简单的数据访问持久化层就实现了。下面模仿楼猪使用的IBatis.net，写个伪SqlMapper，改善一下调用形式，丰富一下调用方法，让方法辨识度更高。