如何用SqlDataReader构造一个高效率的查询数据集

1 ,这里我就不谈SqlDataReader和DateSet的区别了，主要说下我们怎么能够构造一个能够高效率的DataTable,当然也是可以构造一 个ArrayList来添加实体类。SqlDataReader中我们能够获取执行SQL后的字段名称（GetName方法）和获取字段类型 （GetFieldType方法），这样我们就能够构造一个DataTable.
 for (int i = 0; i < intColLength; i++)
    {
        //构造sql的table
        dtb.Columns.Add(dtrValue.GetName(i), GetColType(i));
    }
 SqlDataReader是一个单向的游标，不能够从某个起点往下读数据，这点我觉得微软是做的有点不够，这样如果查询结果多，而要获取的是数据集的最后的数据要消耗掉一点资源。
 2，如何让SqlDataReader再提高点，那你就只能指定是从那一行纵向读数据了，也就是在你页面中需要显示的数据才读取，其他数据单向下移动读取游标就可以，下面就是一个这样的实例代码

#region "私有变量"
        /// <summary>
        /// 表示一个到数据库的打开的连接
        /// </summary>
        private System.Data.SqlClient.SqlConnection Con = new SqlConnection();
        /// <summary>
        /// 表示执行对象是SQl还是存储过程
        /// </summary>
        private System.Data.SqlClient.SqlCommand Cmd = new SqlCommand();
        /// <summary>
        /// 表示用于填充 System.Data.DataSet 和更新数据库的一组数据命令和到数据库的连接
        /// </summary>
        private System.Data.SqlClient.SqlDataAdapter Dtapt = new SqlDataAdapter();
        /// <summary>
        /// 表示要在数据库中生成的事务
        /// </summary>
        private System.Data.SqlClient.SqlTransaction sqlTran;
        /// <summary>
        /// 提供从数据源读取数据行的只进流的方法
        /// </summary>
        private SqlDataReader dtrValue = null;
        #endregion

        #region"数据库连接处理"

        /// <summary>
        /// 获得webconfig中的 默认 Sql连接字符串
        /// </summary>
        private string strConSql
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["SQLConntionStr"].ToString();
            }

        }
        #endregion

        #region "事务处理"

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (Con.State == ConnectionState.Closed)
            {
                //打开连接
                OpenCn();
                //开始事务
                if (sqlTran == null)
                {
                    sqlTran = Con.BeginTransaction();
                }
                Cmd.Transaction = sqlTran;
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransection()
        {
            sqlTran.Commit();
            sqlTran.Dispose();
            sqlTran = null;
            CloseCn();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransection()
        {
            sqlTran.Rollback();
            sqlTran.Dispose();
            sqlTran = null;
            CloseCn();
        }

        #endregion

#region"返回分页表数据Datatable [Read] 方式获取数据,数据量建议在查询结果在10000条记录内"
        /// <summary>
        /// 用于分页控件，返回需要显示页的数据和记录条数
        /// </summary>
        /// <param name="p_strSql">SQL语句</param>
        /// <param name="p_CmdParms">SQL参数和其对应值</param>
        /// <param name="p_intStart">开始记录</param>
        /// <param name="p_intPageSize">每页显示记录条数</param>
        /// <param name="out_intCount">返回记录条数</param>
        /// <returns>查询数据集</returns>
        protected DataTable ExecuteReadTable(string p_strSql, SqlParameter[] p_CmdParms, int p_intStart, int p_intPageSize, ref  int out_intCount)
        {
            return ExecuteReadTable(CommandType.Text, p_strSql, p_CmdParms, p_intStart, p_intPageSize, ref out_intCount);
        }

        /// <summary>
        /// 1. 根据存储过程和参数值得到DataTable 值
        /// 2. 根据SQL的得到DataTable 值
        /// </summary>
        /// <param name="p_objCmdType">是存储过程还是SQL</param>
        /// <param name="p_intStart">开始记录</param>
        /// <param name="p_intPageSize">每页显示条数</param>
        /// <param name="p_strSql">可是是SQL 也可以是存储过程</param>
        /// <param name="p_CmdParms">SqlParameter参数列表</param>
        /// <param name="out_intCount">返回总记录数</param>
        /// <returns>返回DataTable</returns>
        protected DataTable ExecuteReadTable(CommandType p_objCmdType, string p_strSql, SqlParameter[] p_CmdParms, int p_intStart, int p_intPageSize, ref  int out_intCount)
        {
            DataTable dtb = new DataTable();
            DateTime dtStart = DateTime.Now;

            dtrValue = ExecuteReader(p_objCmdType, p_strSql, p_CmdParms);
            if (dtrValue == null)
            {
                CloseCn();
                return dtb;
            }
            int intColLength = dtrValue.FieldCount;

            for (int i = 0; i < intColLength; i++)
            {
                //构造sql的table
                dtb.Columns.Add(dtrValue.GetName(i), GetColType(i));
            }
            DataRow dr;

            int k = 0;
            if (dtrValue.HasRows)
            {
                //读取数据行值
                while (dtrValue.Read())
                {
                    //读取分页间数据
                    if (p_intStart <= k && k < p_intStart + p_intPageSize)
                    {
                        dr = dtb.NewRow();
                        //读取每列值
                        for (int j = 0; j < intColLength; j++)
                        {
                            //读取每列的值
                            dr[dtrValue.GetName(j)] = GetValue(j, GetFieldType(j).ToString());
                        }
                        dtb.Rows.Add(dr);
                    }
                    k++;
                }
                //删除了当前页所有数据则读上一页数据
                if (k <= p_intStart)
                {
                    while (k <= p_intStart)
                    {
                        p_intStart = p_intStart - p_intPageSize;
                    }
                    k = 0;
                    dtrValue = ExecuteReader(p_objCmdType, p_strSql, p_CmdParms);
                    if (dtrValue.HasRows)
                    {
                        while (dtrValue.Read())
                        {
                            //读取分页间数据
                            if (p_intStart <= k && k < p_intStart + p_intPageSize)
                            {
                                dr = dtb.NewRow();
                                //读取每列值
                                for (int j = 0; j < intColLength; j++)
                                {
                                    //读取每列的值
                                    dr[dtrValue.GetName(j)] = GetValue(j, GetFieldType(j).ToString());
                                }
                                dtb.Rows.Add(dr);
                            }
                            k++;
                        }
                    }
                }
            }
            CloseCn();
            SEHR.BLL.Function.AddSQLLog.WriteLog(p_strSql, dtStart.TimeOfDay.ToString(), DateTime.Now.TimeOfDay.ToString(), Convert.ToString(DateTime.Now - dtStart));
            if (out_intCount == 0)
            {
                out_intCount = k;//获得总行数并且返回到页面
            }
            return dtb;
        }

        #endregion

#region "ExecuteReader 执行SQL语句"
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="p_objCmdType">命令类型 1CommandType.Text SQL语句 2CommandType.StoredProcedure存储过程</param>
        /// <param name="p_strSQL">命令类型 1 SQL语句 2存储过程名称</param>
        /// <param name="p_CmdParms">SqlParameter</param>
        /// <returns>SqlDataReader</returns>
        private SqlDataReader ExecuteReader(CommandType p_objCmdType, string p_strSQL, SqlParameter[] p_CmdParms)
        {
            SqlDataReader dtrRet = null;
            try
            {
                //打开连接
                OpenCn();
                //命令行连接
                Cmd.Connection = Con;
                Cmd.CommandText = p_strSQL;
                //是SQL语句还是存储过程
                Cmd.CommandType = p_objCmdType;

                //循环CmdParms值
                if (p_CmdParms != null)
                {
                    foreach (SqlParameter objParm in p_CmdParms)
                    {
                        Cmd.Parameters.Add(objParm);
                    }
                }
                dtrRet = Cmd.ExecuteReader();
                Cmd.Parameters.Clear();
                return dtrRet;
            }
            catch (Exception e)
            {
                string strErr = string.Empty; //p_CmdParms 参数值
                if (p_CmdParms != null)
                {
                    foreach (SqlParameter objParm in p_CmdParms)
                    {
                        strErr += objParm.ParameterName + " ='" + objParm.Value + "' ";
                    }
                }
                if (Con.State != ConnectionState.Closed && sqlTran == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
                // 写错误日志
                SEHR.BLL.Function.AddMsgLog.AddError("SqlBase", e.Message + ""n"r SQL : " + p_strSQL + ""n"r 参数 : " + strErr, e.StackTrace);
                return null;
            }
            finally
            {
                //如果连接打开并且没有事务和SqlDataReader事件 则关闭连接
                if (Con.State != ConnectionState.Closed && sqlTran == null && dtrRet == null)
                {
                    Cmd.Parameters.Clear();
                    CloseCn();
                }
            }
        }
        #endregion