ASP.NET GridView - SelectCommand with Condition from Session Variable

Question

I am new to ASP.NET

I am trying to combine a query with condition from the session variables.

Basically, I capture certain inputs and build a Session session below. and I know it works by Response.Write.

Session("QueryCondition") = " AND Name like '%Test%' AND CompLeteFlag = 1"

I have a SelectCommand in my GridView1

SelectCommand="SELECT top 100 * FROM [EnhanceTracking] WHERE 1 = 1 order by ID DESC"

What I would like to do is this above query combine with the Session("QueryCondition") to filter the records. IF a classic ASP, then It can be done like this

SelectCommand="SELECT top 100 * FROM [EnhanceTracking] WHERE 1 = 1 " & Session("QueryCondition") & " order by ID DESC"

However, I don't know how to do this in ASP.NET, since it is not allow combining a variable with the SelectCommand.

Please advise what to do. Thanks in adnvance.

You would benefit greatly from spending more time learning about ADO.NET. http://stackoverflow.com/questions/3598699/what-is-ado-net http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx Oh.. as well as sql injection, because what you're carrying over from classic ASP is quite bad: http://stackoverflow.com/questions/601300/what-is-sql-injection — Hardrada, Nov 08 '12 at 01:53
Thank you @Hardrada. I know classic ASP is bad, that is why I am learning ASP.NET. I just want look for another way to do similar to that method in ASP.NET. Basically, I am looking for a correct way to capture user inputs (some textboxes, checkboxes), and put them in a sessions variable and use those as query conditions. So if user navigate to a diffferent page and come back, the same filter still there. Any advices? or examples? — Milacay, Nov 08 '12 at 17:57
I figured it out how to combine that query will the condition string in session variable. For those who try to accomplish this. Don't use DataSourceID in the GridView, instead Bind the data on Page_Load, which has a query combined with conditions in the session variable. — Milacay, Nov 08 '12 at 19:23

score 0 · Answer 1 · answered Dec 12 '12 at 08:06

I did something similar for web application I made. You can utilize the OnSelecting event of asp:SqlDatasource (I would recommend to use this instead of dynamically building the select command in the codebehind) for dynamically adjusting the parameters of the select command.

I am not sure if this is the optimal way of doing this but it works.

My markup looked like this (GridView tag simplified):

<asp:SqlDataSource ID="SelectItemsDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:hubConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:hubConnectionString.ProviderName %>" 

        SelectCommand="SELECT itemEntity.id, itemEntity.name, itemEntity.filename, itemEntity.type, itemEntity.added, userEntity.name FROM item AS itemEntity 
                        LEFT OUTER JOIN USER AS userEntity ON itemEntity.uploader = userEntity.id WHERE itemEntity.type like @type ORDER BY itemEntity.added DESC" 
        OnSelecting="onItemSelecting"> 
        <SelectParameters>
           <asp:Parameter Name="@type" Type="String" />
        </SelectParameters>       
</asp:SqlDataSource>

<asp:GridView ID="ItemGrid" runat="server" DataSourceID="SelectItemsDataSource"/> // Simplified

Notice the OnSelecting attribute.

Then in the codebehind you can simply handle the OnSelecting event:

protected void onItemSelecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    // Type value NOT given and parameter exists - Set wildcard
    if (Request.QueryString["type"] == null && e.Command.Parameters.Contains("@type"))
    {
        e.Command.Parameters["@type"].Value = "%";
        return;
    }
    if (Request.QueryString["type"] != null && e.Command.Parameters.Contains("@type"))
            e.Command.Parameters["@type"].Value = Request.QueryString["type"];   
    }

I believe the Command.Parameters methods are sql injection safe. If not then someone please correct me.

ASP.NET GridView - SelectCommand with Condition from Session Variable

1 Answers1