C# Display full data table instead of only search input

Question

I am using VS2005 C# and SQL Server 2005.

Currently I am able to display data using datasource from the sql statement SELECT * from table.

Now I have implemented a search function on my table which will display search results as of user's input.

However, I am not able to set the default display of the datatable to list all data by default or when the search textbox is empty.

I am following this guide: http://www.asp.net/data-access/tutorials/displaying-data-with-the-objectdatasource-cs and I am basically stuck at the final part where he puts in an if-else statement for his datatable listng, which I have no idea where to change mine :(

Below are the codes and screenshots:

RPList.aspx.cs:

<%@ Page Language="C#" MasterPageFile="~/MainPage.master" AutoEventWireup="true" CodeFile="RPList.aspx.cs" Inherits="SimpleDisplay" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
    Role: <asp:TextBox ID="RPbyRoleTB" runat="server"></asp:TextBox>
    <asp:Button ID="RPbyRoleBtn" runat="server" Text="Show Roles & Processes" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:<connection> %>" SelectCommand="SELECT * FROM [RolesProcess] WHERE ([Role] = @Role)">
        <SelectParameters>
            <asp:ControlParameter ControlID="RPbyRoleTB" Name="Role" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

<script language="javascript" type="text/javascript">
// <!CDATA[

// ]]>
</script>

    <asp:GridView ID="GridView1" runat="server" AllowSorting="True" DataSourceID="SqlDataSource1">
    </asp:GridView>
</asp:Content>

What do I need to do so that when my page loads or when the search box is empty, it will show the full datatable by default?

---

As suggested by Politia, I have tried changing my query to SELECT [columns] FROM RolesProcess WHERE (Role = @Role) OR (LEN(@Role) = 0), however, as what I've commented, it doesn't seem to work as smoothly as i thought. Below are the screenshots.

---

Behind code for page after changing sql query:

<%@ Page Language="C#" MasterPageFile="~/MainPage.master" AutoEventWireup="true" CodeFile="RPList.aspx.cs" Inherits="SimpleDisplay" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SODConnectionString %>" SelectCommand="SELECT [columns] FROM RolesProcess WHERE (Role = @Role) OR (LEN(@Role) = 0) OR (@Role IS NULL)">
        <SelectParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="Role" PropertyName="Text" Type="String" DefaultValue="" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:TextBox ID="TextBox1"  runat="server" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" Text="Button" />
<script language="javascript" type="text/javascript">
// <!CDATA[

// ]]>
</script>

    <asp:GridView ID="GridView1" runat="server" AllowSorting="True" DataSourceID="SqlDataSource1">
    </asp:GridView>
</asp:Content>

The queries all works during configuration. Now when I load my page, the tables doesn't appear, and even if I search for 'blank' values, the table does not show any data as well.

Image of page when it loads, and even if I clicked on the button, nothing shows out.

Answer 1

That's why i mostly recommend using ObjectDataSource and your own custom adaptor which can contain this logic and is more easy to test.

Nontheless, with this method, you do have to reflect your whishes in your query. You can either set the default value of the parameter to % like:

<asp:ControlParameter ControlID="RPbyRoleTB" Name="Role" PropertyName="Text" DefaultValue="%" Type="String" />

Or update your query to reflect empty strings and/or null values.

SELECT *
FROM table
WHERE Role = @role
OR LEN(@role) = 0