ado.net快速上手实践篇（一）

 

文章来源：IT工程信息网  http://www.systhinker.com/?viewnews-11675

前言：这两天重温经典，对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  

 

文章来源：IT工程信息网  http://www.systhinker.com/?viewnews-11675