>问题
我正在尝试使用文本框txtSearch
来过滤SqlDataSource
的结果。
我的查询:
SELECT
[id], [username], [name]
FROM
[dbo].[users]
WHERE
@filter IS NULL OR
LEN(@filter) = 0 OR
[name] LIKE ('%'+@filter+'%')
ASPX 标记:
<div>
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" CausesValidation="False" OnClick="btnSearch_Click" />
</div>
<asp:SqlDataSource ID="SQLDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
SelectCommand="SELECT [id], [username], [name] FROM [dbo].[users] WHERE @filter IS NULL OR LEN(@filter) = 0 OR [name] LIKE ('%'+@filter+'%')">
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch" DefaultValue=""
Name="filter" Type="String" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
C# 代码隐藏:
protected void btnSearch_Click(object sender, EventArgs e)
{
GridView1.DataBind();
}
表 [dbo].[users]
:
id name username
---- ---- --------
0 Jack jhenry
1 Jim jcallaway
2 Phillip pmcarthur
我只是得到一个空网格,然后我尝试将默认值设置为 " "
(单个空格)并将@filter IS NULL
修改为 @filter = ''
.这给了我整个网格,但当我在文本框中输入一个值并点击Search
时,没有任何反应。如何毫不费力地将过滤器连接到SqlDataSource
?
脏修复:
搜索空间" "
而不是NULL
有效,但我已经设置了ConvertEmptyStringToNull = True
SELECT
[id], [username], [name]
FROM
[dbo].[users]
WHERE
@filter = ' ' OR
LEN(@filter) = 0 OR
[name] LIKE ('%'+@filter+'%')
我宁愿用IS NULL
来清洁它,可能吗?
你能Type="String"
添加到控制参数并尝试使用以下查询吗?
SELECT [id], [username], [name]
FROM [dbo].[users]
WHERE LTRIM(RTRIM(@filter)) = ''
OR [name] LIKE '%'+ LTRIM(RTRIM(@filter)) +'%'
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch"
DefaultValue=""
Name="filter"
Type="String"
PropertyName="Text" />
</SelectParameters>
更新:
您需要重新绑定数据源而不是网格。
protected void btnSearch_Click(object sender, EventArgs e)
{
SQLDataSource1.DataBind();
}
存储过程:
如果需要验证逻辑,最好使用存储过程。
例如:
CREATE PROCEDURE GetUsers
@Filter NVARCHAR(100) = NULL
AS
BEGIN
DECLARE @SearchFilter BIT
SET @SearchFilter = 1
IF (@Filter IS NULL OR RTRIM(LTRIM(@Filter)) = N'')
SET @SearchFilter = 0
SET @Filter = ISNULL(@Filter, '')
SET @Filter = '%' + RTRIM(LTRIM(@Filter)) + '%'
SELECT [id], [username], [name]
FROM [dbo].[users] WITH (NOLOCK)
WHERE @SearchFilter = 0
OR [name] LIKE @Filter
END