C# asp.net中gridview上的大量数据

Very large amount of data on gridview in asp.net

提问by Naty Bizz

I have a large amount of data (a sql query with 20000 records) and filling my datagrid with that amount of data takes like 10 minutes, this is my gridview definition:

我有大量数据（一个包含 20000 条记录的 sql 查询），用这些数据填充我的数据网格需要 10 分钟，这是我的 gridview 定义：

<asp:GridView ID="g" runat="server" Height="113px" Width="817px" 
BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" 
CellPadding="3" GridLines="Vertical" AllowPaging="True" Font-Size="Small" 
     PageSize="30">
    <AlternatingRowStyle BackColor="#DCDCDC" />
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#0000A9" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#000065" />
    <PagerStyle cssClass="gridpager" HorizontalAlign="Left" />  

</asp:GridView>

As you can see I have enabled to true the AllowPaging property.

如您所见，我已启用 AllowPaging 属性。

This is how I bind the data:

这就是我绑定数据的方式：

DataSet dts = new DataSet();
OracleDataAdapter oad = new OracleDataAdapter(query, co.conn);

cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();

oad.Fill(dts);


g.DataSource = dts.Tables[0];
g.DataBind();

How can I improve the performance?

如何提高性能？

When I fill the dataset (oad.Fill(dts);) takes 10 minutes to complete. Is this because I set the 20000 records at once? Is there a way to show only the first 30 records and recall the data when the user paginates the gridview? Is there another way to improve the performance?

当我填充数据集时 (oad.Fill(dts);) 需要 10 分钟才能完成。这是因为我一次设置了20000条记录吗？有没有办法只显示前 30 条记录并在用户对 gridview 进行分页时调用数据？还有其他方法可以提高性能吗？

采纳答案by Jupaol

If my understanding is correct, you want to add server paging

如果我的理解是正确的，你想添加服务器分页

When you simply add AllowPaging="True"to the grid, by default, the GridViewhas no idea how to paging your data from the server, the paging is being executed in-memory after the whole results have been fetched from the database and this happens every time the GridViewis bind

当您简单地添加AllowPaging="True"到网格时，默认情况下，GridView不知道如何从服务器分页数据，在从数据库中获取整个结果后，分页正在内存中执行，并且每次GridView绑定时都会发生这种情况

I think you want to add server paging (paging in the server and only sending to the client a small bunch of records), in order to do that, you could take advantage of the several ASP.Net data source controls.

我认为您想添加服务器分页（在服务器中分页并且只向客户端发送一小部分记录），为此，您可以利用几个 ASP.Net 数据源控件。

Since you are doing the connection to your database manually, then you need to manually add paging code in your queries and map that code to the control

由于您手动连接到数据库，因此您需要在查询中手动添加分页代码并将该代码映射到控件

I think the only data source controls that can help you (since you are using Oracle as the database) are

我认为唯一可以帮助您的数据源控件（因为您使用 Oracle 作为数据库）是

• SqlDataSource. Sadly it does not support server paging out-of-the-box, you would need to tweak it
• ObjectDataSource. It can be easily integrated with the GridViewcontrol to provide paging, however you would need to manually add code to your queries or store procedures to paginate your data in the server
• EntityDatasource. It's used to connect with your database when using EntityFramework

• Sql数据源。遗憾的是，它不支持开箱即用的服务器分页，您需要对其进行调整
• 对象数据源。它可以很容易地与GridView控件集成以提供分页，但是您需要手动将代码添加到查询或存储过程中以对服务器中的数据进行分页
• 实体数据源。它用于在使用 EntityFramework 时连接您的数据库

If you would be using EF or NHibernate for example, it would be easier, the code would look like:

例如，如果您将使用 EF 或 NHibernate，它会更容易，代码如下所示：

g.DataSource = myContext.MyTable.Skip(pageIndex * pageSize).Take(pageSize);
g.DataBind();

Example using the ObjectDatasource

使用示例 ObjectDatasource

ASPX

ASPX

    <asp:ObjectDataSource runat="server" ID="ods" TypeName="MyObject" EnablePaging="True"
        SelectMethod="FindPaging"
        MaximumRowsParameterName="pageSize"
        SortParameterName="sortColumn"
        StartRowIndexParameterName="startIndex"
        SelectCountMethod="FindPagingCount" onselected="ods_Selected"
    >
        <SelectParameters>
            <asp:Parameter Name="sortColumn" Type="String" />
            <asp:Parameter Name="startIndex" Type="Int32" />
            <asp:Parameter Name="pageSize" Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

            <asp:GridView ID="grid" runat="server" AllowPaging="True" AllowSorting="True" PageSize="10"
                DataSourceID="ods" AutoGenerateColumns="true">
            </asp:GridView>

Data Component

数据组件

[System.ComponentModel.DataObject]
public class MyObject
{
    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public IEnumerable<employee> FindPaging(int startIndex, int pageSize, string sortColumn)
    {
        // place here your code to access your database and use the parameters to paginate your results in the server
    }

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public int FindPagingCount(int startIndex, int pageSize, string sortColumn)
    {
        var c = new DataClassesDataContext();

        return c.employees.Count();
    }
}

回答by Jonas

The easy solution would be to grab the top 30 rows, then when you grab the next set you grab the next 30 by doing top 30 where id > @lastid.

简单的解决方案是抓取前 30 行，然后当您抓取下一组时，通过执行 id > @lastid 的前 30 行来抓取接下来的 30 行。

That way you only ever request 30 rows and dont have to bother with getting the whole recordset from the database.

这样，您只需请求 30 行，而不必费心从数据库中获取整个记录集。

回答by Servy

Paging simply means that it will only display 'pagesize' items at a time, but it will still need to get all of the data from the database before it can display those few items because that's what you're query is telling it to do.

分页只是意味着它一次只会显示“pagesize”项，但它仍然需要从数据库中获取所有数据才能显示这几项，因为这就是您要查询的内容。

What you need to do is modify the query so that it is only fetching 'pagesize' items, and then you need to add an event handler to the OnPageIndexChangingevent so that you can re-query the database for the next 'pagesize' items. (Or preivous, or whatever, based on the paging options you have set. The event args will contain the page index the user asked for.)

您需要做的是修改查询，使其仅获取“pagesize”项，然后您需要向事件添加一个事件处理程序，OnPageIndexChanging以便您可以重新查询数据库以获取下一个“pagesize”项。（或以前的，或其他，基于您设置的分页选项。事件参数将包含用户要求的页面索引。）

If you do some web searching for "gridview paging" or similar you can find code examples, but they will vary quite a bit based on the database you're using, how you're fetching it, and so on.

如果您在网络上搜索“gridview paging”或类似内容，您可以找到代码示例，但根据您使用的数据库、获取它的方式等，它们会有所不同。

Paging for oracle :

oracle 的分页：

https://stackoverflow.com/a/241643/351383

https://stackoverflow.com/a/241643/351383