Filtering Data

Introduction to Operators

In the previous lesson, we learned that we could analyze data using the Table window. We also learned how to analyze data by creating and executing a SQL statement in a query window. Instead of selecting all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records.

When analyzing data or if you are creating a query using the Table window, you can type an expression that uses one or more logical operators we reviewed. Here is an example of an expression

> '12/31/1993'

This means that the dates that occur after 1993 would be selected.

1. Start a new Windows Application named AltairRealtors7
2. In the Solution Explorer, right-click Form1.cs and click Rename
3. Type AltairRealtors.cs and press Enter
4. From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
5. Name it cmnProperties
6. Create the menu items as follows:
    

   Text	(Name)	Image
   Filter by &Selection	mnuFilterBySelection	filtsel.ico
   Filter &Excluding Selection	mnuFilterExclSel	filtexcl.ico
   Separator
   Sort &Ascending	mnuSortAscending	ascending.ico
   Sort &Descending	mnuSortDescending	descending.ico
   Separator
   &Remove Filter/Sort	mnuRemoveFilterSort	rmvfiltsrt.ico

7. On the main menu, click Data -> Add New Data Source...
8. On the first page of the wizard, make sure Database is selected and click Next
9. In the second page of the wizard, click New Connection...
10. In the Server Name combo box, select the server or type (local)
11. In the Select or Enter a Database Name combo box, select AltairRealtors1
12. Click Test Connection and click OK twice
13. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
14. Click Next
15. Change the connection string to cstAltairRealtors and click Next
16. Change the name of the data set to dsAltairRealtors
17. Expand the Tables node and expand the Properties node
18. Click the check boxes of: PropertyNumber, City, State, ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, Stories, YearBuilt, and MarketValue
     
     
19. Click Finish
20. From the Data Source window, drag the Properties object and drop it on the form
21. Under the form, click the controls and use the Properties window to change their names as follows:
     

    Object	New Name
    propertiesBindingSource	bsProperties
    propertiesTableAdapter	tadProperties
    propertiesBindingNavigator	bnProperties

22. On the form, click the data grid view control
23. In the Properties window, change its name to dgvProperties
24. Set its ContextMenuStrip to cmnProperties
25. Under the Properties window, click Edit Columns and configure the columns as follows:
     

    Column	HeaderText	Width
    PropertyNumber	Prop #	50
    City		90
    State		40
    ZIPCode	ZIP Code	60
    PropertyType	Property Type	90
    Condition		80
    Bedrooms	Beds	40
    Bathrooms	Baths	40
    Stories		45
    YearBuilt	Year	40
    MarketValue	Market Value	80

26. Complete the design of the form as follows:
    
    
27. On the form, click the data grid view
28. In the Events section of the Properties window, double-click MouseDown and implement the event as follows:
     

    altairusing System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; namespace AltairRealtors7 { public partial class AltairRealtors : Form { DataGridViewColumn colSelected; public AltairRealtors() { InitializeComponent(); } private void propertiesBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); this.bsProperties.EndEdit(); this.tadProperties.Update(this.dsAltairRealtors.Properties); } private void AltairRealtors_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the // 'dsAltairRealtors.Properties' table. You can move, // or remove it, as needed. this.tadProperties.Fill(this.dsAltairRealtors.Properties); colSelected = new DataGridViewColumn(); } private void dgvProperties_MouseDown(object sender, MouseEventArgs e) { // Identity the point where the mouse landed DataGridView.HitTestInfo hti = dgvProperties.HitTest(e.X, e.Y); // Create a cell reference based on the coordinates of the mouse DataGridViewCell celSelected = dgvProperties.Rows[hti.RowIndex].Cells[hti.ColumnIndex]; // Just in case the user right-clicked, select that cell dgvProperties.CurrentCell = celSelected; // Identify the selected column and initialize our variable with it colSelected = dgvProperties.Columns[hti.ColumnIndex]; } } }

29. Return to the form and, under it, click the cmnProperties context menu
30. On the form, under ContextMenuStrip, double-click Sort Ascending, and implement the event as follows:
     

    private void mnuSortAscending_Click(object sender, EventArgs e) { // Sort the records using the data grid view dgvProperties.Sort(colSelected, ListSortDirection.Ascending); }

31. Return to the form and double-click Sort Descending
32. Implement the event as follows:
     

    private void mnuSortDescending_Click(object sender, EventArgs e) { // Sort the records using the data grid view dgvProperties.Sort(colSelected, ListSortDirection.Descending); }

33. Execute the application
34. Right-click a value in a column and sort the records
35. Close the form and return to your programming environment

If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion(the plural is criteria). The expression is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of a table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

To apply a WHERE condition, if you are working from a table in the Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Criteria section and under the Filter column, click the box that corresponds to the field on which the condition will be applied, and type the value of the expression (only the value). Here is an example:

If you are writing writing the SQL statement to specify the condition, after FROM table, enter WHERE followed by the whole Expression. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       Sex, ParentsNames
FROM Students
WHERE Sex='Female';

If you are working in a table window and specify the expression in the Criteria section, the WHERE condition would be created in the SQL section and, to make it easier to read, it would be included in parentheses:

SELECT DateOfBirth, LastName, FirstName, 
       Sex, State, ParentsNames
FROM Students
WHERE State='MD'
ORDER BY LastName;

SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM Students
WHERE Sex='Female';
GO

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM Students
WHERE Sex = 'Female';

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT Gender = 'Female';
GO

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT (Sex = 'Female');

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM   Students
WHERE  State IS NOT NULL;