Performing a query on a dataset object
-
Hi everyone, I have an ASP.NET DataSet question. Is it possible to perform an SQL like query on a DataSet object? Basically, I want to filter a DataSet object. Of course, the query does not have to be a SQL like. Here is what I am trying to do. I have a report generation tool. So basically there would be one set of data and you can choose what kind of report you want to see. The filtering has to be done based on user choice. Any suggestions and help would be greatly appreciated. Sincerely, Pankaj Without struggle, there is no progress
-
Hi everyone, I have an ASP.NET DataSet question. Is it possible to perform an SQL like query on a DataSet object? Basically, I want to filter a DataSet object. Of course, the query does not have to be a SQL like. Here is what I am trying to do. I have a report generation tool. So basically there would be one set of data and you can choose what kind of report you want to see. The filtering has to be done based on user choice. Any suggestions and help would be greatly appreciated. Sincerely, Pankaj Without struggle, there is no progress
if you are binding to a control, pass the dataset to a dataview and you can filter by values in a colomn:
c# DataView dv = new DataView; dv = MyDataSet.Tables[0].DefaultView; dv.Filter = "MyColomName = 'stringMatchExpresion'"; // Bind();Hope this helps (hope i got the code close enough!!) www.fuxup.com[^] -
Hi everyone, I have an ASP.NET DataSet question. Is it possible to perform an SQL like query on a DataSet object? Basically, I want to filter a DataSet object. Of course, the query does not have to be a SQL like. Here is what I am trying to do. I have a report generation tool. So basically there would be one set of data and you can choose what kind of report you want to see. The filtering has to be done based on user choice. Any suggestions and help would be greatly appreciated. Sincerely, Pankaj Without struggle, there is no progress
The most simple way would be to use a DataView object to filter the records found within the DataSet. The DataView may be bound to most data bindable objects including DataGrids, DropDownLists, Reports, etc. The DataView also allows for sorting and filtering. Filtering is enabled using the RowFilter property of the DataView. The row filter acts much like the where clause in a Sql statement. Assuming your DataSet object is named myDataSet and it contains a single Table named "Contacts". C#:
DataView myDataView = (DataView)myDataSet.Tables["Contacts"].DefaultView;
// or DataView myDataView = (DataView)myDataSet.Tables[0].DefaultView;
String myFilter = "City = 'Chicago'";
myDataView.RowFilter = myFilter;
myDataGrid.DataSource = myDataView;
myDataGrid.DataBind();Dim myDataView as DataView = CType(myDataSet.Tables("Contacts").DefaultView, DataView)
' or Dim myDataView as DataView = CType(myDataSet.Tables(0).DefaultView, DataView)
Dim myFilter as String = "City = 'Chicago'"
myDataView.RowFilter = myFilter
myDataGrid.DataSource = myDataView
myDataGrid.DataBind()Using the DataView object does not modify the source DataSet, so subsequent filtering of the DataSet can be performed. To insure that I can retrieve the filtered recordset across postbacks, I usually create a ViewState variable to hold the row filter value and use the DataView object as the standard binding object for data when filtering is needed. C#:
In the Page.OnLoad event...
If !(Page.IsPostBack)
{
ViewState["myFilter"] = "";
}
In the filtering event...
ViewState["myFilter"] = myNewFilterString;
In the data binding event...
String myFilter = (String)ViewState["myFilter"];
myDataView.RowFilter = myFilter;In the Page.OnLoad event...
If Not Page.IsPostBack Then
ViewState("myFilter") = ""
End If
In the filtering event...
ViewState("myFilter") = myNewFilterString;
In the data binding event...
Dim myFilter as String = CType(ViewState("myFilter"),String)
myDataView.RowFilter = myFilter