ado.net快速上手实践篇(一)

来源:网络时间:2011-06-23

  这两天重温经典,对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,改善一下调用形式,丰富一下调用方法,让方法辨识度更高。

发表评论

最新评论(共0条)