c# - 从代码隐藏创建 SqlDataSource 过滤器参数

Question

我正在尝试通过隐藏代码从 SalDataSource 填充 gridview。我想使用 3 个文本框过滤结果:ScheduerName、Username 和 Summary。我的愿望是使 aspx 页面尽可能简单，并从代码后面创建过滤器参数。仅当我只有一个参数(示例:SchedulerName)时，此代码才有效。我做了一些研究，发现其中一个问题是 ConvertEmptyStringToNull 设置为 false，因此我尝试将所有参数设置为 true 但没有成功。

protected void Page_Load(object sender, EventArgs e)
{
    string connectionString = "Data Source=WIN-KMH6OBJU9JD\\SQL2008;Initial Catalog=TimeRecorder;User ID=username;Password=password";
    SqlDataSource1.ConnectionString = connectionString;
    SqlDataSource1.SelectCommand = "SELECT ScheduleName, Summary,RecursiveTaskID, Priority_RefID, Username, DATEDIFF(DD,lmdf,GETDATE()) AS lmdf FROM vw_General_Tasks";
    SqlDataSource1.FilterExpression = "  (ScheduleName LIKE '%{0}%' or '{0}' ='-1') AND (Username LIKE '%{1}%' or '{1}' ='-1') AND (Summary LIKE '%{2}%' or '{2}' ='-1')";

    SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("ScheduleName","txtScheduler"));
    SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("Username", "txtUsername"));
    SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("Summary", "txtSummary"));

    SqlDataSource1.FilterParameters[0].ConvertEmptyStringToNull = true;
    SqlDataSource1.FilterParameters[1].ConvertEmptyStringToNull = true;
    SqlDataSource1.FilterParameters[2].ConvertEmptyStringToNull = true;    
}

WebForm2.aspx

<form id="form1" runat="server">

        <p>
            Summary:
            <asp:TextBox ID="txtSummary" runat="server" Width="96px" AutoPostBack="true"></asp:TextBox>
        </p>
        <p>
            Scheduler:
            <asp:TextBox ID="txtScheduler" runat="server" Width="96px"></asp:TextBox>
        </p>
        <p>
            Username:
            <asp:TextBox ID="txtUsername" runat="server" Width="96px"></asp:TextBox>
        </p>
        <p>
            <asp:Button runat="server" Text="Filter" ID="btnFilter"></asp:Button>
        </p>


        <asp:SqlDataSource 
            ID="SqlDataSource1" 
            runat="server">
        </asp:SqlDataSource>

        <p>
            <asp:GridView
                ID="GridView1"
                runat="server"
                DataSourceID="SqlDataSource1"
                AutoGenerateColumns="False">
                <Columns>
                    <asp:BoundField DataField="RecursiveTaskID" HeaderText="RecursiveTaskID" SortExpression="RecursiveTaskID" />

                    <asp:BoundField DataField="Summary" HeaderText="Summary" SortExpression="Summary" />

                    <asp:BoundField DataField="ScheduleName" HeaderText="Scheduler" ReadOnly="True" SortExpression="ScheduleName" />

                    <asp:BoundField DataField="Username" HeaderText="Username" HtmlEncode="False" SortExpression="Username" />

                    <asp:BoundField DataField="lmdf" HeaderText="Last Modified" HtmlEncode="False" SortExpression="lmdf" />

                    <asp:BoundField DataField="Priority_RefID" HeaderText="Prio" HtmlEncode="False" SortExpression="Priority_RefID" />
                </Columns>
            </asp:GridView>
        </p>

    </form>

Answer 1

我使用的是不同的表，但你明白了:

protected void Page_Load(object sender, EventArgs e)
{
    TextBox1.Text = "A";
    TextBox2.Text = "M";

    SqlDataSource1.FilterExpression = "ContactName like '{0}%' and City like '{1}%'";

    SqlDataSource1.FilterParameters.Add(new ControlParameter("ContactName", "TextBox1", "Text"));
    SqlDataSource1.FilterParameters.Add(new ControlParameter("City", "TextBox2", "Text"));
}

看来您的代码中缺少的是 ControlParameter.PropertyName。在 TextBox 的情况下，它将是 Text。

结果:

因此，在您的情况下，您必须这样做:

SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("ScheduleName","txtScheduler", "Text"));
SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("Username", "txtUsername", "Text"));
SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("Summary", "txtSummary", "Text"));