|
If you create a table that includes an identity column and
that column is the first (or the most left) column of the table, when the user
performs data entry, the records are arranged in the incremental order of the
numbers of that identity column. Here is an example:
If you want, you can allow the user to arrange the list of
records based on another column. This other column would be used as reference.
Rearrange the list of records is referred to as sorting and there are two
options: ascending and descending.
When a list is sorted in ascending order:
- If the list is made or numeric values, the lowest value would become the
first, followed by the second to lowest value, and the highest value would
become the last. This is the case for the EmployeeID column of the above
table
- If the list is made of strings (words), the alphabet would be used. The
string whose first letter is the highest in the alphabet from a, b, c, etc
would be the first. In this case, the ascending order is the same as the
alphabetical order. For example, in a list made Paul, Walter, Anne, and
John, in that order, when sorted in ascending order, the list would become
Anne, John, Paul, Walter. If various strings in the list start with the same
letter, as is the case for the above FirstName column, the strings with the
same starting letter would be grouped first. Then among the strings with the
same starting letter, the second letter would be considered and the same
algorithm would be applied
- If the list is made of dates, the earliest date would become the first and
the most recent date would become the last
- If the list is a combination of numbers and strings, the numbers would be
arranged in incremental order firs, followed by the list of strings in
alphabetical order
- If the list contains empty values, the empty values would be the first,
the other values would be arranged in order depending on their type
All of the techniques used to sort records on a table can also be applied to a query that displays in Datasheet View. To create more advanced queries, the SQL allows you to sort a field on a query and
use this arrangement as part of the query.
To sort a column in ascending order, you can include the ORDER BY clause in your statement. The
formula used is:
SELECT What FROM TableName ORDER BY ColumnName;
The What and the TableName factors are
the same as described earlier. The ColumnName placeholder allows
you to specify the column used as reference. When this operation is
performed, the values under the ColumnName column would be sorted
in ascending order. The value or the other columns would be used to
correspond to those of this column. Consider the following example:
Notice that the records under the LastName column are
sorted in alphabetical order, and the other values in the other columns
simply follow this order.
If you use the * operator to include all fields, you can order the list based on any of the table's
column.
By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the
ASC keyword specified after the column used as basis. Here is an
example:
If you create a database application that contains a table
and you want the users to be able to sort values of that table, probably the
fastest means of supporting this operation is through the DataGrid control. Its
columns are already configured to be sorted in ascending or descending order.
If you are working in a Windows Forms Application and using
a DataView object, to sort records, the DataView class is equipped with the Sort
property that is of type String. To specify the sort order to apply to
the records, assign a formatted string to this property.
Instead of sorting a list in ascending order, you can
rearrange its values in reverse order. This is referred to as descending order.
The algorithms used are the same as for ascending, except that they are applied in
reverse.
If you want to sort records in descending order, use the
DESC keyword after the name of the column, in place of ASC.
The DESC keyword produces the opposite result to the ASC effect.
Here is an example:
Once again, if you are working in a Windows Forms
Application and using a DataView object, to sort records, assign an
appropriately built string to the DataView.Sort property.
|
Practical
Learning: Sorting Records
|
|
- Design the form as follows:
 |
| Control |
Name |
Text/CaptionText |
Other Properties |
| Label |
|
Sort: |
|
| ComboBox |
cboColumns |
|
DropDownStyle: DropDownList |
| Label |
|
in |
|
| ComboBox |
cboSortOrder |
|
DropDownStyle: DropDownList |
| Label |
|
order |
|
| DataGrid |
|
Students Records |
Auto Format: Colorful 2
Anchor: Top, Bottom, Left, Right
DataSource: dvwStudents |
| Button |
btnClose |
Close |
|
|
- Access the Items property of the right combo box and create two strings as
Ascending and Descending
- In the Windows Forms section of the Toolbox, click ContextMenu and click
the form
- Create the menu items as follows:
| Text |
(Name) |
| Filter By Selection |
mnuFiltBySel |
| Filter Excluding Selection |
mnuFiltExclSel |
| - |
|
| Sort Ascending |
mnuSortAsc |
| Sort Descending |
mnuSortDesc |
| - |
|
| Remove Filter/Sort |
mnuRemFiltSort |
- On the form, click the DataGrid control and, in the Properties window, set
its ContextMenu property to contextMenu1
- Double-click an occupied area of the form to access its Load event
- In the class, declare a DataColumn variable named colSelected
public class Form1 : System.Windows.Forms.Form
{
private DataColumn colSelected;
|
- Implement the Load event of the form as follows:
private void Form1_Load(object sender, System.EventArgs e)
{
DataTable tblStudents = this.dsROSH1.Tables["Students"];
DataColumnCollection colStudents = tblStudents.Columns;
for(int i = 0; i < colStudents.Count; i++)
this.cboColumns.Items.Add(colStudents[i].ColumnName);
this.cboColumns.SelectedIndex = 0;
this.cboSortOrder.SelectedIndex = 0;
this.sqlDataAdapter1.Fill(this.dsROSH1);
colSelected = new DataColumn();
}
|
- Return to the form and click the DataGrid control. In the Events section
of the Properties window, generate the MouseDown event and implement it as
follows:
private void dataGrid1_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
{
DataGrid.HitTestInfo hti = this.dataGrid1.HitTest(e.X, e.Y);
DataGridCell curCell = new DataGridCell();
curCell.RowNumber = hti.Row;
curCell.ColumnNumber = hti.Column;
this.dataGrid1.CurrentCell = curCell;
colSelected = this.dsROSH1.Tables["Students"].Columns[hti.Column];
}
|
- Return to the form. Double-click the combo box on the right side of the
Sort label and implement its SelectedIndexChanged event as follows:
private void cboColumns_SelectedIndexChanged(object sender, System.EventArgs e)
{
string strAscDesc = "ASC";
if( this.cboSortOrder.Text == "Descending" )
strAscDesc = "DESC";
this.dvwStudents.Sort = this.cboColumns.Text + " " + strAscDesc;
}
|
- Return to the form and double-click the combo box on the right side of the
in label
- As done for the first combo box, implement the SelectedIndexChanged event
as follows:
private void cboSortOrder_SelectedIndexChanged(object sender, System.EventArgs e)
{
string strAscDesc = "ASC";
if( this.cboColumns.SelectedIndex == -1 )
return;
if( this.cboSortOrder.Text == "Descending" )
strAscDesc = "DESC";
this.dvwStudents.Sort = this.cboColumns.Text + " " + strAscDesc;
}
|
- Return to the form and click contextMenu1
- In the menu, double-click Sort Ascending and implement its event as
follows:
private void mnuSortAsc_Click(object sender, System.EventArgs e)
{
this.dvwStudents.Sort = colSelected.ColumnName + " ASC";
}
|
- Return to the form. In the menu, double-click Sort Descending and
implement its Click event as follows:
private void mnuSortDesc_Click(object sender, System.EventArgs e)
{
this.dvwStudents.Sort = colSelected.ColumnName + " DESC";
}
|
- Return to the form. In the menu, double-click Remove Filter/Sort and
implement its Click event as follows:
private void mnuRemFiltSort_Click(object sender, System.EventArgs e)
{
this.dvwStudents.RowFilter = 0;
this.dvwStudents.Sort = "StudentID ASC";
}
|
- Return to the form and double-click the Close button
- Implement its Click event as follows:
private void btnClose_Click(object sender, System.EventArgs e)
{
Close();
}
|
- Execute the application
- Test the top combo boxes and test the context menu
|
|
