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
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.
Hi Mario,
You can access the current filter expression (see the ASPxGridView.FilterExpression) by handling the ASPxGridView.BeforePerformDataSelect event. Please see a similar approach described in the How to get the filter criteria? thread. However, you might need to add an extra parameter to fetch a number of rows from the data source, if no filter is applied. To filter the ASPxGridView programmatically, use its AutoFilterByColumn method.
Thanks,
Alex
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.
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