“GridView”+“ObjectDataSource”+“SQL Server存储过程” 实现数据源存储过程分页功能

解决的问题：

       最近在开发一个B/S架构的小型企业管理系统，随着用户将越来越多的数据录入服务器的数据库中，GridView自带的分页功能的弊端越来越突出。GridView的“用户界面分页机制”使用起来非常简便，但是它有一个致命的缺陷：它的分页是“假分页”，比如数据表中有10000条数据，而GridView的PageSize设为10（即每页最多显示10条数据），为了显示这10条数据，需要将数据表中所有的10000条数据都加载到服务器内存中，假设有多个客户端进行此操作，服务器的内存将会被疯狂占用，从而导致服务器系能恶化，客户体验也会降低。

       为了解决这一问题，需要采用“数据源分页机制”。

解决方法：

       如上所述，采用“数据源分页机制”，即“GridView”+“ObjectDataSource”+“SQL Server存储过程”。该方法的核心是利用ObjectDataSource控件将表示层（GridView）与数据层（SQL Server）隔离开来，并创建一个中间层（业务对象）用于连接表示层和数据层，从而形成所谓的“三层应用程序架构”。

具体实现：

    （1）数据层SQL Server

 a.  创建一个存储过程"ProductPagingSearch"用于读取数据表中的数据，@StartRowIndex为读取数据的起始行，@MaximumRows为每次读取的最大行数，其余参数用于数据库查询，可根据需求自行设定。代码如下：

ALTER PROCEDURE [dbo].[ProductPagingSearch] 
	(
		@StartRowIndex int, 
		@MaximumRows int,
		@SearchWord nvarchar(max),
		@SearchType nvarchar(10)
        --@SearchType 为查询类别
	)
AS

IF (@SearchType='1')
BEGIN
with 编号表 as 
(
  select 产品编号,ROW_NUMBER() OVER (order by 产品编号) as 序号
  from View_ProductContent
  where 产品名称 like '%'+@SearchWord+'%' or 产品编号 like '%'+@SearchWord+'%' or 产品类别 like '%'+@SearchWord+'%' or 产品品牌 like '%'+@SearchWord+'%' or 产品型号 like '%'+@SearchWord+'%'
)
select    
dbo.ProductContent.产品编号, dbo.ProductContent.产品名称, dbo.ProductContent.产品型号, dbo.ProductContent.单位, dbo.ProductContent.分类号, 
dbo.ProductContent.成本, dbo.ProductContent.售价, dbo.ProductContent.批发价, dbo.ProductContent.供应商, dbo.ProductContent.保修期, dbo.ProductContent.产品图片,dbo.ProductContent.备注, dbo.ProductClassification.品牌 AS 产品品牌, dbo.ProductClassification.类别 AS 产品类别, dbo.ProductClassification.类别2,cast('false' as bit) as 选择
from 
编号表	join ProductContent on  编号表.产品编号=ProductContent.产品编号 join ProductClassification ON dbo.ProductClassification.分类号 = dbo.ProductContent.分类号
where 序号 between (@StartRowIndex+1) and (@StartRowIndex+@MaximumRows)
END

注意这里使用ROW_NUMBER函数额外生成了一个字段”序号“，该字段用于确定要读取的行数：

where 序号 between (@StartRowIndex+1) and (@StartRowIndex+@MaximumRows)

      

       （2）中间层ObjectDataSource

a. 首先我们在App_Code文件夹中创建以个业务对象类DataSourcePaging.cs，

用于定义ObjectDataSource所要使用的方法，这些方法可用于与数据库进行交互。

GetProductPaging() 方法用于调用数据库中的存储过程"ProductPagingSearch"并获取相应的数据，

GetProductCount() 方法用于获取数据表的总行数。代码如下：

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///ProductPaging 的摘要说明
/// </summary>
public class DataSourcePaging
{
    private string CONN_STRING = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

	public DataSourcePaging()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}

    public SqlDataReader GetProductPaging(int startRowIndex, int maximumRows, string searchWord, string searchType)
    {
        SqlConnection con = new SqlConnection(CONN_STRING);

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        //设置运行SQL Server 中名为 ProductPagingSearch 的存储过程
        cmd.CommandText = "ProductPagingSearch";
        cmd.CommandType = CommandType.StoredProcedure;

        //定义存储过程的参数
        cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex);
        cmd.Parameters.AddWithValue("@MaximumRows", maximumRows);
        cmd.Parameters.AddWithValue("@SearchWord", searchWord);
        cmd.Parameters.AddWithValue("@SearchType", searchType);

        con.Open();

        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public int GetProductCount(int startRowIndex, int maximumRows, string searchWord, string searchType)
    {
        int nRows = 0;
        string sql = null;
        if (searchType == "1")
        {
            sql = "select Count(*) from View_ProductContent where "
             + "产品名称 like '%" + searchWord + "%'" + " or "
             + "产品编号 like '%" + searchWord + "%'" + " or "
             + "产品类别 like '%" + searchWord + "%'" + " or "
             + "产品品牌 like '%" + searchWord + "%'" + " or "
             + "产品型号 like '%" + searchWord + "%'";
        }
        else if (searchType == "2")
        {
            sql = "select Count(*) from View_ProductContent where "
                 + "产品品牌 ='" + searchWord + "'";
        }
        else if (searchType == "3")
        {
            sql = "select Count(*) from View_ProductContent where "
                  + "分类号 ='" + searchWord + "'";
        }
        SqlConnection con = new SqlConnection(CONN_STRING);

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = sql;

        using (con)
        {
            con.Open();
            object obj = cmd.ExecuteScalar();
            if (obj== null)
            {
                nRows = 0;
            }
            else
            {
                nRows = (int)obj;
            }
        }

        return nRows;
    }
}

 

b.    然后我们创建一个ObjectDataSource1，并为其设置如下属性：

    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" 
        SelectMethod="GetProductPaging"  OldValuesParameterFormatString="{0}"
        TypeName="DataSourcePaging" SelectCountMethod="GetProductCount">
        <SelectParameters>
            <asp:Parameter DefaultValue="0" Name="startRowIndex" Type="Int32" />
            <asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" />
            <asp:Parameter DefaultValue="null" Name="searchWord" Type="String" />
            <asp:Parameter DefaultValue="1" Name="searchType" Type="String" />
        </SelectParameters>
    </asp:ObjectDataSource>

在这里我只需要使用Select方法，所以只定义的SelectParameters，这些可根据需要自行增减；

（3）表示层GridView

我们创建一个GridView1，并将ObjectDataSource1绑定为GridView1的DataSoure，GridView1的关键属性如下： 

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
  AutoGenerateColumns="False" DataSourceID="ObjectDataSource1">
<PagerSettings FirstPageText="第一页" LastPageText="最后一页" 
  Mode="NextPreviousFirstLast" NextPageText="下一页" PreviousPageText="上一页" />

注意，此处GridView并不需要添加PagingIndexChanging事件。

通过上述操作后，我们便构建了由“GridView”+“ObjectDataSource”+“SQL Server存储过程”组成的“三层应用架构”。

总结：

最后，将上述架构总结如下：

（1）通过在数据库中建立存储过程"ProductPagingSearch"以获取数据；

（2）通过建立业务对象类"DataSourcePaging.cs"调用存储过程"ProductPagingSearch"；

（3）通过ObjectDataSource1使用业务对象类"DataSourcePaging.cs"；

（4）通过GridView1绑定ObjectDataSource1以呈现数据给用户。

相比“用户界面分页机制”，采用上述“数据源分页机制”后，大大减少了服务器端的负载，

提高了数据库的访问效率，用户体验也随之改善。