开发学院WEB开发ASP.NET ado.net快速上手实践篇(一) 阅读

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

 2010-05-26 17:48:29 来源:WEB开发网   
核心提示:前言:这两天重温经典,对ado.net的东西稍微深入的了解了一下,ado.net快速上手实践篇(一),顺便写点代码练练手,全当是复习笔记吧,其实主要还是调用了IDbOperation接口和方法,未完,一、简单说说ado.net的5大常用对象既然说ado.net,当然不能免俗地要提到5大常用对象

前言:这两天重温经典,对ado.net的东西稍微深入的了解了一下,顺便写点代码练练手,全当是复习笔记吧。
一、简单说说ado.net的5大常用对象

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

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

【注意:下面的示例代码和demo是楼猪本周六和周日两天时间实现的,未经详细测试,可能有重大bug,下载学习使用的童鞋务必注意】

二、数据访问持久化层
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,改善一下调用形式,丰富一下调用方法,让方法辨识度更高。
四、实现伪SqlMapper
1、BaseMapper类


代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace AdoNetDataAccess.Mapper
{
   using AdoNetDataAccess.Core.Contract;

   public abstract class BaseMapper
   {
     public IDbOperation CurrentDbOperation;

   #region query for list

   public abstract IList<T> QueryForList<T>(string sqlStr)
  where T : class, new();

   public abstract IList<T> QueryForList<T>(string sqlStr, Type objType)
where T : class, new();

   public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
    where T : class, new();

   public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
    where T : class, new();


     #endregion

   #region query for dictionary

   public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
       where T : class, new();

   public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
   where T : class, new();

   public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, Type objType)
       where T : class, new();

   public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
           where T : class, new();

   #endregion

   #region dataset datatable

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

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

   #endregion

   #region ExecuteScalar

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

   #endregion

   #region insert

   public abstract int Insert(string sqlStr);

   public abstract int Insert(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

   public abstract bool BatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt);

   #endregion

   #region delete

   public abstract int Delete(string sqlStr);

   public abstract int Delete(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

   #endregion

   #region update

   public abstract int Update(string sqlStr);

   public abstract int Update(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

   #endregion

   }

}

上面代码中的方法您是不是很熟悉呢? 呵呵,使用IBatis.net 的童鞋应该会和楼猪产生更多的共鸣。
2、SqlMapper类

代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace AdoNetDataAccess.Mapper
{
   using AdoNetDataAccess.Core.Contract;
   using AdoNetDataAccess.Core.Obj2Model;

   public class SqlMapper : BaseMapper
   {
     private SqlMapper()
     {

   }

   public SqlMapper(IDbOperation dbOperation)
     {
       this.CurrentDbOperation = dbOperation;
     }

   #region query for list

   public override IList<T> QueryForList<T>(string sqlStr)
     {
       return QueryForList<T>(sqlStr, CommandType.Text, null, typeof(T));
     }

   public override IList<T> QueryForList<T>(string sqlStr, Type objType)
     {
       return QueryForList<T>(sqlStr, CommandType.Text, null, objType);
     }

   public override IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return QueryForList<T>(sqlStr, cmdType, listParams, typeof(T));
     }

   public override IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
     {
       return ModelConverter.QueryForList<T>(sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
     }

   #endregion

   #region query for dictionary

   public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
     {
       return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, null, typeof(T));
     }

   public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
     {
       return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, null, objType);
     }

   public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, Type objType)
     {
       return QueryForDictionary<K, T>(key, sqlStr, cmdType, null, objType);
     }

   public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
     {
       return ModelConverter.QueryForDictionary<K, T>(key, sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
     }

   #endregion

   #region dataset datatable

   public override DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return this.CurrentDbOperation.FillDataTable(sqlStr, cmdType, listParams);
     }

   public override DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return this.CurrentDbOperation.FillDataSet(sqlStr, cmdType, listParams);
     }

   #endregion

   #region ExecuteScalar

   public override object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return this.CurrentDbOperation.ExecuteScalar(sqlStr, cmdType, listParams);
     }

   #endregion

   #region insert

   public override int Insert(string sqlStr)
     {
       object obj = ExecuteScalar(sqlStr, CommandType.Text, null);
       int id = obj == null ? 0 : int.Parse(obj.ToString());
       return id;
     }

   public override int Insert(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       object obj = ExecuteScalar(sqlStr, cmdType, listParams);
       int id = obj == null ? 0 : int.Parse(obj.ToString());
       return id;
     }

   /// <summary>
     /// 批量插入
     /// </summary>
     /// <param name="tableName"></param>
     /// <param name="batchSize"></param>
     /// <param name="copyTimeout"></param>
     /// <param name="dt"></param>
     /// <returns></returns>
     public override bool BatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt)
     {
       return this.CurrentDbOperation.ExecuteBatchInsert(tableName, batchSize, copyTimeout, dt);
     }

   #endregion

   #region delete

   public override int Delete(string sqlStr)
     {
       return CommitSql(sqlStr, CommandType.Text, null);
     }

   public override int Delete(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return CommitSql(sqlStr, cmdType, listParams);
     }

   #endregion

   #region update

   public override int Update(string sqlStr)
     {
       return CommitSql(sqlStr, CommandType.Text, null);
     }

   public override int Update(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return CommitSql(sqlStr, cmdType, listParams);
     }

   #endregion

   #region commit and execute sql

   private int CommitSql(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
     {
       return this.CurrentDbOperation.ExecuteNonQuery(sqlStr, cmdType, listParams);
     }

   #endregion

   #region  dbparameter

   public DbParameter CreateParameter(string paraName, object paramValue)
     {
       return this.CurrentDbOperation.CreateDbPrameter(paraName, paramValue);
     }

   public List<DbParameter> CreateParameterList(string[] paraNames, object[] paramValues)
     {
       List<DbParameter> listParams = new List<DbParameter>();
       try
       {
         if (paraNames.Length != paramValues.Length)
         {
           throw new Exception("Param name and value is not equal.");
         }
         for (int i = 0; i < paraNames.Length; i++)
         {
           DbParameter param = CreateParameter(paraNames[i], paramValues[i]);
           listParams.Add(param);
         }
       }
       catch (Exception ex)
       {
         throw ex;
       }
       return listParams;
     }

   #endregion

   }

}

上面的方法丰富实现了CRUD的常见操作,其实主要还是调用了IDbOperation接口和方法。
未完,待续。

demo下载:demo

http://www.cnblogs.com/jeffwongishandsome/archive/2010/05/23/1742002.html

Tags:ado net 快速

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
更多精彩
    赞助商链接

    热点阅读
      焦点图片
        最新推荐
          精彩阅读