In ADO.NET we deals with DataTable and DataSet as the representations of different table of our actual database. So we can write complex queries to fetch information from different table by joining them ,adding subqueries to fetch particular information etc… . We can say it behaves same as the view of SQLServer.
Here is a tip about DataTable which describes how to sort & filter out any DataTable. It will help you for sure to avoid DataBase server round-trip, thus improving the performance. Also by this way you can add more customization to each row.
We can filter any DataTable by writing query constrain as we do with our DataBase Tables. Here is How. Way 1:
Lets say our intend DataTable is dtQuestionList on which we want to apply some filter.
string Expression = "IS_AUTO_EVAL=0"; string SortingOrder = "QUESTION_ORDER ASC"; DataRow[] drFilteredQuestions = dtQuestionList.Select(Expression, sort);
Now drFilteredQuestions array will contain all the rows that will Auto evaluation type & it will be sorted by Sorting order constraint i.e it will be sorted by QUESTION_ORDER in ascending way.
Both the Constrain & SortingOrder are optional. You can also add more constrain by writing more complex condition in the Expression String.
DataRow[] drFilteredQuestions = dtQuestionList.Select(Expression); // Return filtered constrain With default sorting order. DataRow[] drFilteredQuestions = dtQuestionList.Select(Sort); // Return with sorted order. DataRow[] drFilteredQuestions = dtQuestionList.Select(); // Return all rows.
The problem here is that it is not returning any DataTable object as required. So to Convert it to DataTable again we can go in this way.
DataTable dtFilteredQuestion = dtQuestionList.Clone();
foreach (DataRow drQuestion in drFilteredQuestion)
{
dtFilteredQuestion.ImportRow(drQuestion);
}
You can also make changes to each DataRow of the Array and after that add it to the DataTable.
Way 2: There is also another way of sorting & filtering out the DataTable. This way we create one DataView applying the filter Constrain. Let’s See How. Create one DataView applying the filter , sorting order & row view state. And again convert that DataView to DataTable.
While creating the DataView provide the DataTable to be filtered as first argument. The constraints as the second argument (blank string if no constrain to be applied). Add the sorting preference as third argument (blank string if to go ahed with default sorting). The last option is about DataViewRowState .
DataView dvFilteredQuestion = new DataView(dtQuestionList, Expression, sort, DataViewRowState.CurrentRows); DataTable dtNewFilteredQuestion = dvFilteredQuestion.ToTable(); // Convert To DataTable ListView lvQuestion = new ListView(); lvQuestion.DataSource = dvFilteredQuestion; lvQuestion.DataBind();
DataView objects are bindable to any data container.