How to get a gridview to show all table rows when

问题:

The below works correctly and filters my gridview based on the text entered in my textbox.

When no text is entered into my textbox I get no results and cannot understand why.

MY QUESTION

How to get a gridview to show all table rows when no text is entered in the textbox?

MSSQL

@Search nvarchar(50)

SELECT  [table].[column]
FROM    [table]
WHERE   [table].[column] LIKE '%' + @Search + '%' OR COALESCE(@Search,'') = ''

MARKUP

<asp:TextBox ID="txtSearch" RunAt="Server" Text=""/>

<asp:SqlDataSource ID="sqlSearch" RunAt="Server" SelectCommand="spSearch" SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:ControlParameter Type="String" Name="Search" ControlID="txtSearch" PropertyName="Text"/>
  </SelectParameters> 
</asp:SqlDataSource>

RESULTS TO GRIDVIEW

I have tried and tested many methods found on here however all return 0 results when string is empty.

回答1:

Look at CancelSelectOnNullParameter - by default, this prevents the select command from being called when a select parameter's value is null. After setting it to false, the select SP will then still get called even if the text box is empty, e.g.

<asp:SqlDataSource ID="sqlSearch" RunAt="Server" 
    CancelSelectOnNullParameter="false" ...>
</asp:SqlDataSource>

The SqlDataSource seems a bit misleading to me here, because TextBox.Text returns String.Empty when it's empty, not null, therefore I wouldn't expect to have to mess with CancelSelectOnNullParameter for a TextBox, but it seems we have to.

回答2:

Instead of coalesce you may have a if block which checks if the @search parameter is empty or not as shown below..

if @search is not empty 
  SELECT  [table].[column]
  FROM    [table]
  WHERE   [table].[column] LIKE '%' + @Search + '%' OR COALESCE(@Search,'') = ''
else
  SELECT  [table].[column]
  FROM    [table]
  WHERE   [table].[column]