Ticket Q238753
Visible to All Users

GridView loaded from Sqlserver Store Procedure with filter parameters

created 15 years ago

I'm evaluating the ASPxGridView. What I need to do is the following:
I have a Sqlserver store procedure which I'll use to populate the GridView. This SP take some 3 parameters which will be used in the WHERE clause (eg. field like '%' + @sParam + '%').
This SP will be used to create a DataTable Here is the function:
        protected DataTable GetData()
        {
            //CONNECTION STRING
            string sConnStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            //STORE PROCEDURE
            string sStrSP = "dbo.GetTable";
            //FILTER READING
            // THESE SHOULD BE FUNCTION PARAMETERS READ FROM THE FILTER TEXT FIELDS
            string sId = "";
            string sName = "";
            string sDescr = "";
            //SQLSERVER PARAMETER CONFIGURATION
            System.Data.SqlClient.SqlParameter[] sqlparams =
            {
            new System.Data.SqlClient.SqlParameter("@sId", sId),
            new System.Data.SqlClient.SqlParameter("@sName", sName),
            new System.Data.SqlClient.SqlParameter("@sDescr", sDescr),
            };
            //EXECUTION
            // DAL is a project I use to access the database
            System.Data.SqlClient.SqlDataReader rs = DAL.SqlHelper.ExecuteReader(sConnStr, System.Data.CommandType.StoredProcedure, sStrSP, sqlparams);
            //LOAD
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Id", typeof(string)));
            dt.Columns.Add(new DataColumn("Name", typeof(string)));
            dt.Columns.Add(new DataColumn("Descr", typeof(string)));
            if (rs.HasRows)
            {
                while (rs.Read())
                {
                    DataRow dr = dt.NewRow();
                    dr["Id"] = rs.GetString(0);
                    dr["Name"] = rs.GetString(1);
                    dr["Descr"] = rs.GetString(2);
                    dt.Rows.Add(dr);
                }
                rs.Close();
            }
            return dt;
        }
My ASPxGridView lool like this
        <dxwgv:ASPxGridView ID="gridView" ClientInstanceName="ClientGridView" runat="server"
            AutoGenerateColumns="False"
            Settings-ShowFilterRow="True"
            Settings-ShowFilterRowMenu="true"
            Width="100%"
            DataSource="<%# GetInfoList() %>"
            OnHtmlRowCreated="gridView_HtmlRowCreated"
            OnHtmlRowPrepared="gridView_HtmlRowPrepared">
            <Columns>
                <dxwgv:GridViewDataColumn FieldName="Id" Width="50px"></dxwgv:GridViewDataColumn>
                <dxwgv:GridViewDataColumn FieldName="Name" Width="150px"></dxwgv:GridViewDataColumn>
                <dxwgv:GridViewDataColumn FieldName="Descr"></dxwgv:GridViewDataColumn>
            </Columns>
            <Settings ShowVerticalScrollBar="true" UseFixedTableLayout="true" />
            <SettingsPager Mode="ShowAllRecords"></SettingsPager>
            <ClientSideEvents EndCallback="function (s, e) { ResizeGridHeight(); }" Init="function (s, e) { ResizeGridHeight(); }" />
        </dxwgv:ASPxGridView>
What I need is the following:
If all of the filter textboxes are empty I would like to load data with only the first 100 records (select top 100 …)
If some filter textbox has data, then I would like to requery the database (through my SP) using each non empty filter textbox as a parameter to the WHERE clause.
Is it possible to be done? If so, how?
Thanks for your time.
Mário Nunes

Show previous comments (1)

    Thank you Alex.
    I appreciate your quick answer. However I still have (a lot of) doubts.
    I'm a ASP.NET newby. I came from the ASP world and I still don't understand all of these new methods.
    Can you send me an example (c#) on how can I read the textboxes filters and send the corresponding values to my datasource?
    I attatched a screenshot of what I would like to do.
    I apologise for the inconvenience, but I'm not totaly, but almost lost.
    I apppreciate any kind of help you can give me.
    thanks.

    DevExpress Support Team 15 years ago

      Hello Mario,
      I'm sorry but I can't create this example for you. This doesn't mean that I don't want to, but the question you've asked can't be correctly implemented. Let me explain why:
      The ASPxGridView works in two modes: grid mode and Server-side mode. The main difference is that in the server mode the database engine filters and sorts all the data. The grid displays just a result. In the grid mode, all the data is fetched to the web server, where the grid filters and sorts it by itself.
      There are two events which might be useful: ASPxGridView.BeforePerformDataSelect, ASPxGridView.ProcessColumnAutoFilter. The first event fires when the grid requires data. The second one fires when the data should be filtered. You can obtain the text value from filter textboxes in the second event handler. The main difficulty is that the grid obtains the data first, and then filters it. You see that you can't change the datasource according to the entered values, because there isn't any public place to get the text value.
      I'm sorry, but according to the How to get the filter criteria? thread, the better solution is to use separate ASPxTextBox controls outside of the grid.
      I think that the LINQ Server Mode will be more suitable for you.
      Thanks,
      Vest

        Hi Alex.
        It was as I expected. I'll have to re-think my app strategy.
        Thank you for your time and help.
        Best Regards

        Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

        Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.