在ASP中使用gridview过滤器和存储过程..NET和c#

本文关键字：存储过程 NET 过滤器 gridview ASP | 更新日期: 2023-09-27 18:13:28

我有一个由存储过程填充的gridview。在这个数据集上，用户应该能够通过从2个下拉列表中选择LabID和/或SiteName来过滤它。实现这一目标的最佳方式是什么?

    <asp:DropDownList ID="ddlLabIDs" runat="server" AutoPostBack="True" 
        DataSourceID="SqlDataSource2" DataTextField="LabID" 
        DataValueField="LabID" AppendDataBoundItems="True">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
        SelectCommand="exec usp_LabIDs_Select;"></asp:SqlDataSource>
    <asp:DropDownList ID="ddlSiteNames" runat="server" 
        DataSourceID="SqlDataSource3" DataTextField="SiteName" 
        DataValueField="SiteName" AppendDataBoundItems="True" 
        AutoPostBack="True">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
        SelectCommand="exec usp_SiteNames_Select;"></asp:SqlDataSource>
    <asp:GridView ID="AllDataFlat" runat="server" AllowPaging="True" PageSize="20"
        AllowSorting="True" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="LabID" HeaderText="LabID" SortExpression="LabID" />
            <asp:BoundField DataField="SiteName" HeaderText="SiteName" 
                SortExpression="SiteName" />
            <asp:BoundField DataField="StartDateTime" HeaderText="StartDateTime" 
                SortExpression="StartDateTime" />
            <asp:BoundField DataField="FilterNumber" HeaderText="FilterNumber" 
                SortExpression="FilterNumber" />
            <asp:BoundField DataField="AmtWaterFiltered" HeaderText="AmtWaterFiltered" 
                ReadOnly="True" SortExpression="AmtWaterFiltered" />
            <asp:BoundField DataField="WaterTemp" HeaderText="WaterTemp" ReadOnly="True" 
                SortExpression="WaterTemp" />
            <asp:BoundField DataField="pH" HeaderText="pH" SortExpression="pH" />                
        </Columns>           
    </asp:GridView>        
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
        SelectCommand="exec usp_AllDataFlat_Select;">
    </asp:SqlDataSource>

谢谢。

在ASP中使用gridview过滤器和存储过程..NET和c#

您可以这样尝试.....

注意:这只是一个例子，如何过滤网格视图与选定值在两个下拉列表…

<%@ page autoeventwireup="true" codefile="FilterWithMultipleDropDownLists.aspx.cs"
        inherits="GridView_FilterWithMultipleDropDownLists" language="C#" masterpagefile="~/MasterPages/Default.master"
        title="GridView: Filter With Multiple DropDownLists" %>
<asp:content id="Content1" runat="Server" contentplaceholderid="ContentPlaceHolder1">
        <div>
                Select Supplier:
                <asp:dropdownlist id="ddlSuppliers" runat="server" appenddatabounditems="True" autopostback="True"
                        datasourceid="sdsSuppliers" datatextfield="CompanyName" datavaluefield="SupplierID">
                        <asp:listitem text="All Suppliers" value="-1">
                        </asp:listitem>
                </asp:dropdownlist>
                <asp:sqldatasource id="sdsSuppliers" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                        selectcommand="SELECT [SupplierID], [CompanyName] FROM [Suppliers] ORDER BY [CompanyName]">
                </asp:sqldatasource>
        </div>
        <div style="margin-top: 12px;">
                Select Category:
                <asp:dropdownlist id="ddlCategories" runat="server" appenddatabounditems="True" autopostback="True"
                        datasourceid="sdsCategories" datatextfield="CategoryName" datavaluefield="CategoryID">
                        <asp:listitem text="All Categories" value="-1">
                        </asp:listitem>
                </asp:dropdownlist>
                <asp:sqldatasource id="sdsCategories" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                        selectcommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER By [CategoryName]">
                </asp:sqldatasource>
        </div>
        <asp:gridview id="gvProducts" runat="server" autogeneratecolumns="False" datakeynames="ProductID"
                datasourceid="sdsProducts" style="margin-top: 24px;">
                <columns>
                        <asp:boundfield datafield="ProductID" headertext="ProductID" insertvisible="False"
                                readonly="True" sortexpression="ProductID" />
                        <asp:boundfield datafield="ProductName" headertext="ProductName" sortexpression="ProductName" />
                        <asp:boundfield datafield="CompanyName" headertext="Supplier" sortexpression="CompanyName" />
                        <asp:boundfield datafield="CategoryName" headertext="Category" sortexpression="CategoryName" />
                </columns>
        </asp:gridview>
        <asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                selectcommand="SELECT [P].[ProductID], [P].[ProductName], [P].[SupplierID], [P].[CategoryID], [S].[CompanyName], [C].[CategoryName] FROM [Products] AS [P] INNER JOIN [Suppliers] AS [S] ON [S].[SupplierID] = [P].[SupplierID] INNER JOIN [Categories] AS [C] ON [C].[CategoryID] = [P].[CategoryID] WHERE ([P].[SupplierID] = CASE WHEN @SupplierID = -1 THEN [P].[SupplierID] ELSE @SupplierID END AND [P].[CategoryID] = CASE WHEN @CategoryID = -1 THEN [P].[CategoryID] ELSE @CategoryID END) ORDER BY [P].[ProductName]">
                <selectparameters>
                        <asp:controlparameter controlid="ddlSuppliers" name="SupplierID" propertyname="SelectedValue" type="Int32" />
                        <asp:controlparameter controlid="ddlCategories" name="CategoryID" propertyname="SelectedValue" type="Int32" />
                </selectparameters>
        </asp:sqldatasource>
</asp:content>

最有效的方法是将下拉控件中选择的值传递给存储过程usp_AllDataFlat_Select。在此存储过程中，您应该能够仅选择满足筛选器值的行。

如果您无法访问存储过程(无法修改它们)，那么在我看来，实现这一点的最佳方法是将对存储过程的调用包装在一个对象中，该对象从数据库中检索所有行并在内存中过滤它们(不是很有效)。然后，您可以使用objectdatasource而不是SqlDataSource来填充gridview。这个objectdatasorce可以调用包装存储过程的对象的方法。

你可以在这里找到一个例子。本例使用查询而不是存储过程，但将查询替换为对存储过程的调用应该是微不足道的。

祝你好运，

相关文章：