.NET数据访问集锦（二）编写自己的SqlHelper

时间：2010-09-28 来源：Ferry

　　这里主要介绍一下使用DataReader读取数据，包括分页读取。DataReader只读向前，DataAdapter的Fill方法是一次性把查询到的所有数据取出来填充到DataTable或DataSet，下面的方法是使用DataReader只把当前页所需要的数据取出来添加到DataTable，效率要比使用DataAdapter的Fill方法高得多。

一、创建一个类库项目，命名为DAO，并创建一个SqlHelper.cs类

using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class SqlHelper
    {
        //数据库连接字符串，在实际应用中将其写在配置文件中，不要如此硬编码
        private static string ConnString = "server=.;database=test;uid=sa;pwd=123456";

        public SqlHelper()
        {
        }

        public static void FillDataTable(string query, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                int cols = dr.VisibleFieldCount;
                for (int i = 0; i < cols; i++)
                {
                    dt.Columns.Add(new DataColumn(dr.GetName(i), dr.GetFieldType(i)));
                }
                while (dr.Read())
                {
                    DataRow row = dt.NewRow();
                    for (int i = 0; i < cols; i++)
                    {
                        row[i] = dr[i];
                    }
                    dt.Rows.Add(row);
                }
                dr.Close();
            }
        }
        /// <summary>
        /// 分页读取数据
        /// </summary>
        /// <param name="query">select查询语句</param>
        /// <param name="pageSize">每页显示数据数</param>
        /// <param name="currentPageIndex">当前页码</param>
        /// <param name="dt">存储数据的DataTable实例</param>
        public static void FillDataTable(string query,int pageSize,int currentPageIndex, DataTable dt)
        {
            //读取数据的开始索引
            long startIndex = (currentPageIndex - 1) * pageSize;
            //读取数据的结束索引
            long endIndex = currentPageIndex * pageSize-1;
            //DataReader读取的当前数据行的索引
            long readToIndex = -1;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                //数据源中的列数
                int cols = dr.VisibleFieldCount;
                //构造DataTable结构
                for (int i = 0; i < cols; i++)
                {
                    dt.Columns.Add(new DataColumn(dr.GetName(i), dr.GetFieldType(i)));
                }
                //读取数据，将数据一行一行添加到DataTable
                while (dr.Read())
                {
                    readToIndex++;
                    //当DataReader指针在开始索引和结束索引闭区间时才读取数据构造DataRow
                    //并添加到DataTable
                    if (readToIndex >= startIndex && readToIndex <= endIndex)
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < cols; i++)
                        {
                            row[i] = dr[i];
                        }
                        dt.Rows.Add(row);
                    }
                }
                dr.Close();
            }

        }
    }
}

　　二、测试，创建一个网站项目，添加到DAO项目的引用，设计如下界面

　　GridView保持默认设置，不要设置自动分页，下面是5个Button和一个TextBox

　　后台代码：

using System;
using System.Web.UI.WebControls;
using System.Data;
using DAO;

public partial class _Default : System.Web.UI.Page
{
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (ViewState["CurrentPageIndex"] == null)
            {
                ViewState["CurrentPageIndex"] = 1;
            }
            BindData();
        }
    }
    private void BindData()
    {
        //SqlHelper.FillDataTableByDataAdapter("select * from BigData", dt);
        SqlHelper.FillDataTable("select * from BigData", 10, int.Parse(ViewState["CurrentPageIndex"].ToString()), dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        DateTime dt1 = DateTime.Now;
        GridView1.PageIndex = e.NewPageIndex;
        BindData();
        DateTime dt2 = DateTime.Now;
        TimeSpan ts = dt2 - dt1;

        Response.Write(ts.Milliseconds.ToString());
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        ViewState["CurrentPageIndex"] = 1;
        BindData();
    }
    protected void btnPrev_Click(object sender, EventArgs e)
    {
        ViewState["CurrentPageIndex"] = int.Parse(ViewState["CurrentPageIndex"].ToString()) - 1;
        BindData();
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        ViewState["CurrentPageIndex"] = int.Parse(ViewState["CurrentPageIndex"].ToString()) + 1;
        BindData();
    }
    protected void btnGo_Click(object sender, EventArgs e)
    {
        ViewState["CurrentPageIndex"] = txtPage.Text.Trim();
        BindData();
    }
}

　　创建一个两列的表，插入一百万条数据测试

declare @num int
set @num=1
while(@num<1000001)
begin
insert BigData values(@num)
set @num = @num+1
end

　　运行后界面：

　　翻页响应很快，比直接使用DataAdapter的Fill方法填充DataTable，然后使用GridView自动翻页效率高的多。

数据翻页功能的实现还有其它一些方法，比如TOP、row_number()over（SQL Server 2005/2008）、存储过程等，有兴趣可以测试一下。

本例提供的方法仅供参考。