Microsoft Visual C# & ADO.NET: Introduction to Filtering Records

	Introduction to Filtering Records

Introduction to Conditions

WHERE is the Condition?

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 must be an existing column of a table. It is followed by the assignment operator. The Value is the value that would set the condition. If the value is a character or a string, you must include it in single-quotes. If it is a number, you can type its numeric value.

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 Gender = N'Female';

After the WHERE condition, enter the ORDER BY expression. Here is an example:

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

Hiding a Column

In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result. For example, imagine you create a statement that produces a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Sex column in the statement. In this case, you can hide that column in the result.

To hide a column from a SELECT statement, if you are working from the table in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Diagram or in the Criteria sections, select the column. Then, in the Criteria section, under the Output column, uncheck the box corresponding to the field you want to hide.

If you are writing a SQL statement, omit the column in the SELECT statement but involve it in the WHERE condition. Here is an example:

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

Notice that the SELECT statement does not have the Gender column and the resulting query does not show the Gender column.

Logical Comparisons

Introduction to Operators

We know how to 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.

WHEN a Field's Value Meets a Criterion

You can use a WHEN conditional statement to refine data selection. Consider the following table:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreateDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateDatabase = new Button();
        btnCreateDatabase.Text = "Create Database";
        btnCreateDatabase.Location = new Point(12, 12);
        btnCreateDatabase.Width = 100;
        btnCreateDatabase.Click += new EventHandler(btnCreateTableClick);

        Text = "Video Collection";
        Controls.Add(btnCreateDatabase);

        StartPosition = FormStartPosition.CenterScreen;
    }

    void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideoStore =
                   new SqlConnection("Data Source='EXPRESSION';" +
                                    "Integrated Security=SSPI;"))
        {
            SqlCommand cmdVideoStore =
                                    new SqlCommand("IF EXISTS (SELECT name " +
                                                   "FROM sys.databases " +
                                                   "WHERE name = N'VideoStore') " +
                                                   "DROP DATABASE VideoStore " +
                                                   "CREATE DATABASE VideoStore;",
                                                   cntVideoStore);

            cntVideoStore.Open();
            cmdVideoStore.ExecuteNonQuery();
            MessageBox.Show("A database named VideoStore has been created.",
                       "Video Store");
        }

        using (SqlConnection cntVideoStore =
                   new SqlConnection("Data Source='EXPRESSION';" +
                                    "Database='VideoStore';" +
                                   "Integrated Security=yes;"))
        {
            SqlCommand cmdVideoStore =
                   new SqlCommand("CREATE TABLE Employees" +
                                  "(" +
                                  "    FirstName nvarchar(20), " +
                                  "    MI nchar(2), " +
                                  "    LastName nvarchar(20), " +
                                  "    HourlySalary smallmoney, " +
                                  "    Status tinyint" +
                                  ");",
                          cntVideoStore);
            cntVideoStore.Open();
            cmdVideoStore.ExecuteNonQuery();

            MessageBox.Show("A table named Employees has been created.",
                           "Video Store",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        using (SqlConnection cntVideoStore =
            new SqlConnection("Data Source='EXPRESSION';" +
                              "Database='VideoStore';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdVideoStore =
                    new SqlCommand("INSERT INTO Employees " +
                                   "VALUES(N'Christopher', N'L', N'Larsen', 14.50, 3), " +
                                   "      (N'Henry', N'C', N'Jonathan', 12.85, 1), " +
                                   "      (N'Lance', N'A', N'Seagal', 16.95, 1); " +
                                   "INSERT INTO Employees(FirstName, LastName, Status) " +
                                   "VALUES(N'Roberta', N'Ortez', 1); " +
                                   "INSERT INTO Employees(FirstName, LastName, HourlySalary) " +
                                   "VALUES(N'Paul', N'Swanson', 10.90); " +
                                   "INSERT INTO Employees " +
                                   "VALUES(N'Kristofer', N'L', N'Michaels', 12.85, 2); " +
                                   "INSERT INTO Employees " +
                                   "VALUES(N'Ivan', N'N', N'Sanders', 15.00, 2), " +
                                   "      (N'David', N'P', N'Monahan', 13.05, 1);",
                                   cntVideoStore);
            cntVideoStore.Open();
            cmdVideoStore.ExecuteNonQuery();

            MessageBox.Show("A few records have been created.",
                            "Video Store",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Imagine you want to select the Status column:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideoStore =
                        new SqlConnection("Data Source='EXPRESSION';" +
                                          "Database='VideoStore';" +
                                          "Integrated Security=yes;"))
    {
            SqlCommand cmdVideoStore =
                                    new SqlCommand("SELECT * FROM Employees;",
                                                   cntVideoStore);
            cntVideoStore.Open();
            cmdVideoStore.ExecuteNonQuery();

            SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdVideoStore);
            BindingSource bsEmployees = new BindingSource();

            DataSet dsEmployees = new DataSet("EmployeesSet");
            sdaEmployees.Fill(dsEmployees);

            bsEmployees.DataSource = dsEmployees.Tables[0];
            dgvEmployees.DataSource = bsEmployees;;
    }
}

Employees

If a column has values that are difficult to identify, you can use a CASE conditional statement to customize the result(s). Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideoStore =
                        new SqlConnection("Data Source='EXPRESSION';" +
                                          "Database='VideoStore';" +
                                          "Integrated Security=yes;"))
    {
            SqlCommand cmdVideoStore =
                                    new SqlCommand("SELECT FirstName, MI, LastName, HourlySalary, Status = " +
                "CASE Status " +
                "    WHEN 1 THEN N'Full-Time' " +
                "    WHEN 2 THEN N'Part-Time' " +
                "END " +
                "FROM Employees;",
                                                   cntVideoStore);
            cntVideoStore.Open();
            cmdVideoStore.ExecuteNonQuery();

            SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdVideoStore);
            BindingSource bsEmployees = new BindingSource();

            DataSet dsEmployees = new DataSet("EmployeesSet");
            sdaEmployees.Fill(dsEmployees);

            bsEmployees.DataSource = dsEmployees.Tables[0];
            dgvEmployees.DataSource = bsEmployees;
    }
}

Employees

Remember that WHEN can use an ELSE section if no CASE matches the values you provided. Here is an example of using it:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideoStore =
                        new SqlConnection("Data Source='EXPRESSION';" +
                                          "Database='VideoStore';" +
                                          "Integrated Security=yes;"))
    {
        SqlCommand cmdVideoStore =
		 new SqlCommand("SELECT FirstName, MI, LastName, HourlySalary, Status = " +
                		"CASE Status " +
                		"    WHEN 1 THEN N'Full-Time' " +
                		"    WHEN 2 THEN N'Part-Time' " +
                		"    ELSE N'Contractor/Seasonal' " +
                		"END " +
                		"FROM Employees;",
                                cntVideoStore);
        cntVideoStore.Open();
        cmdVideoStore.ExecuteNonQuery();

        SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdVideoStore);
        BindingSource bsEmployees = new BindingSource();

        DataSet dsEmployees = new DataSet("EmployeesSet");
        sdaEmployees.Fill(dsEmployees);

        bsEmployees.DataSource = dsEmployees.Tables[0];
        dgvEmployees.DataSource = bsEmployees;
    }
}

Employees

Conditional Statements

Introduction to Logical Comparisons

For your databases, you can create expressions that represent a combination of values, variables, and operators. To support expressions, Transact-SQL provides operators other than some of those you might be familiar with, from C#.

Transact-SQL supports the same logical operators as C# with small variations. The operators are:

Operator Name	C#	Transact-SQL
Equality	==	=
Less Than	<	<
Less Than Or Equal To	<=	<= or !>
Greater Than	>	>
Greater Than Or Equal To	>=	>= or !<
Not Equal	!=	<> or !=

Introduction to Conditional Statements

A conditional statement is an expression you formulate to evaluate it. Most of the time, the statement is written so that, when evaluated, it can produce a result of true or false, then, depending on the outcome, you can take action. A condition is usually written as simple as possible to make it clear to you and the SQL interpreter. Although the interpreter never gets confused, if you create a difficult statement, you may receive an unpredictable result.

In the next few sections, we will review the keywords and formulas that Transact-SQL provides to help you formulate clear expressions. Expressions usually start with a keyword, followed by the expression itself. After the expression, you can tell the interpreter what to do. The statement may appear as follows:

Keyword Expression
	Statement

With the above formula, we will always let you know what keyword you can use, why, and when.

BEGIN...END

After the expression, you can write the statement in one line. This is the statement that would be executed if/when the Expression of our formula is satisfied. In most cases, you will need more than one line of code to specify the Statement. As it happens, the interpreter considers whatever comes after the Statement as a unit but only the line immediately after the Expression. To indicate that your Statement covers more than one line, start it with the BEGIN keyword. Then you must use the END keyword to indicate where the Statement ends. In this case, the formula of a conditional statement would appear as follows:

Keyword Expression
BEGIN
	Statement Line 1
	Statement Line 2
	
	Statement Line n
END

You can still use the BEGIN...END combination even if your Statement covers only one line:

Keyword Expression
BEGIN
	Statement
END

Using the BEGIN...END combination makes your code easier to read because it clearly indicates the start and end of the Statement.

IF a Condition is True

Probably the primary comparison you can perform on a statement is to find out whether it is true. This operation is performed using an IF statement in Transact-SQL. Its basic formula is:

IF Condition
	Statement

When creating an IF statement, first make sure you provide a Condition expression that can be evaluated to produce true or false. To create this Condition, you can use variables and the logical comparison operator reviewed above.

When the interpreter executes this statement, it first examines the Condition to evaluate it to a true result. If the Condition produces true, then the interpreter executes the Statement. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise1';" +
                  "Integrated Security=yes;"))
        {
            SqlCommand command =
             new SqlCommand("DECLARE @DateHired As DateTime, " +
                    "@CurrentDate As DateTime " +
                    "SET @DateHired = N'1996/10/04' " +
                    "SET @CurrentDate = N'2007/04/11' " +
                    "IF @DateHired < @CurrentDate " +
            	    "	SELECT N'You have the experience required for a new promotion in this job'",
                    connection);
            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                MessageBox.Show(rdr[0].ToString(),
                        "Database Application",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
            }

            rdr.Close();
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

IF...ELSE

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives. Consider the following code:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise1';" +
                  "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("DECLARE @DateHired As DateTime," +
                               " @CurrentDate As DateTime;" +
                               "SET @DateHired = N'1996/10/04'" +
            		       "SET @CurrentDate = N'2007/04/16';" +
           		       "IF @DateHired > @CurrentDate" +
    			       "	SELECT N'You have the experience required for a new promotion';",
                               connection);

        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
        {
            MessageBox.Show(rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        rdr.Close();
    }
}

In case the expression to examine produces a false result, there is nothing to do. Sometimes this will happen.

CASE...WHEN...THEN

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
    WHEN Value1 THEN Result
    WHEN Value2 THEN Result

    WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise1';" +
                  "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("DECLARE @CharGender Char(1)," +
                               "@Gender Varchar(20);" +
                               "SET @CharGender = N'F';" +
                               "SET @Gender = " +
                               "CASE @CharGender" +
                               "    WHEN N'm' THEN N'Male'" +
                               "    WHEN N'M' THEN N'Male'" +
                               "    WHEN N'f' THEN N'Female'" +
                               "    WHEN N'F' THEN N'Female'" +
                               "END;" +
                               "SELECT N'Student Gender: ' + @Gender;",
                               connection);

        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
        {
            MessageBox.Show(rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        rdr.Close();
    }
}

Here is the result of executing it:

CASE

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of values that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise1';" +
                  "Integrated Security=yes;"))
    {
            SqlCommand command =
		 new SqlCommand("DECLARE @CharGender Char(1)," +
				"@Gender Varchar(20);" +
            			"SET @CharGender = N'g';" +
            			"SET @Gender = " +
            			"CASE @CharGender" +
            			"    WHEN N'm' THEN N'Male'" +
            			"    WHEN N'M' THEN N'Male'" +
            			"    WHEN N'f' THEN N'Female'" +
            			"    WHEN N'F' THEN N'Female'" +
            			"ELSE N'Unknown'" +
            			"END;" +
            			"SELECT N'Student Gender: ' + @Gender;",
            			connection);

        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
        {
            MessageBox.Show(rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        rdr.Close();
    }
}

This would produce:

CASE...WHEN...THEN...ELSE

If you do not produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise1';" +
                  "Integrated Security=yes;"))
    {
        SqlCommand command =
	 new SqlCommand("DECLARE @CharGender Char(1)," +
        	 	"@Gender Varchar(20);" +
             		"SET @CharGender = N'Q';" +
             		"SET @Gender = " +
             		"CASE @CharGender" +
             		"    WHEN N'm' THEN N'Male'" +
             		"    WHEN N'M' THEN N'Male'" +
             		"    WHEN N'f' THEN N'Female'" +
             		"    WHEN N'F' THEN N'Female'" +
             		"ELSE N'Unknown'" +
             		"END;" +
             		"SELECT N'Student Gender: ' + @Gender;",
             		connection);

        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
        {
            MessageBox.Show(rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        rdr.Close();
    }
}

This would produce:

CASE...WHEN...THEN...ELSE

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.

WHILE

As done in C#, to examine a condition and evaluate it before taking action in SQL, you can use the WHILE operator. The basic formula of this statement is:

WHILE Expression 
    Statement

When implementing this statement, first provide an Expression after the WHILE keyword. The Expression must produce a true or a false result. If the Expression is true, then the interpreter executes the Statement. After executing the Statement, the Expression is checked again. AS LONG AS the Expression is true, it will keep executing the Statement. When or once the Expression becomes false, it stops executing the Statement. This scenario can be illustrated as follows:

WHILE

Here is an example:

DECLARE @Number As int

WHILE @Number < 5
	SELECT @Number AS Number
GO

To effectively execute a while condition, you should make sure you provide a mechanism for the interpreter to get a referenced value for the condition, variable, or expression being checked. This is sometimes in the form of a variable being initialized although it could be some other expression. Such a while condition could be illustrated as follows:

WHILE

This time, the statement would be implemented as follows:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @Number As int;" +
			"SET @Number = 1;" +
			"WHILE @Number < 5" +
			"    BEGIN" +
			"        SELECT @Number AS Number;" +
			"        SET @Number = @Number + 1;" +
			"    END;",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
	MessageBox.Show(rdr[0].ToString());

    rdr.Close();
    connection.Close();
}

Boolean Constants

Introduction

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result; in other words, something is not half true or half false or "Don't Know": either it is true or it is false).

The TRUE and FALSE Constants

Like C#, in SQL, a Boolean variable can hold a TRUE value. The value is also considered as 1. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

The NULL Constant

After you have declared a variable, the SQL interpreter reserves a space in the computer memory for it but does not put anything in that memory space. At that time, that area of memory does not hold a significant value. Also at that time, the variable is considered null.

Here is note to be careful about: when a variable is said to hold a null value, it does not mean its value is 0. It does not even mean that the variable's memory space is empty. It actually means that we cannot clearly determine the current value that the variable is holding.

To support the null value, Transact-SQL provides a constant named NULL. The NULL constant is mostly used for comparison purposes. For example, you can use an IF statement to check the nullity of a variable.

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example:

// Square Calculation
private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command =
 	 new SqlCommand("DECLARE @Side As Decimal(10,3)," +
			" 	 @Perimeter As Decimal(10,3)," +
			" 	 @Area As Decimal(10,3);" +
			"SET @Perimeter = @Side * 4;" +
			"SET @Area = @Side * @Side;" +
			"IF @Side IS NULL" +
			"    PRINT 'A null value is not welcome'" +
			"ELSE IF @Side > 0" +
			"    BEGIN" +
			"        SELECT @Side AS Side;" +
			" 	 SELECT @Perimeter AS Perimeter ;" +
			"        SELECT @Area AS Area;" +
			"    END;" +
			"ELSE" +
			"    PRINT 'You must provide a positive value';",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
}

To avoid having a NULL value, you can either initialize the variable or you can assign it a value. Here is an example:

NULL

// Square Calculation
private void btnCalculate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @Side As Decimal(10,3)," +
	 	        " 	 @Perimeter As Decimal(10,3)," +
		        " 	 @Area As Decimal(10,3);" +
			"SET @Side = 48.126;" +
			"SET @Perimeter = @Side * 4;" +
			"SET @Area = @Side * @Side;" +
			"IF @Side IS NULL" +
			"    PRINT 'A null value is not welcome'" +
			"ELSE IF @Side > 0" +
			"    BEGIN" +
			" 	SELECT @Side, @Perimeter, @Area;" +
			"    END;" +
			"ELSE" +
			"    PRINT 'You must provide a positive value';",
			connection);

    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	txtSide.Text = rdr[0].ToString();
	txtPerimeter.Text = rdr[1].ToString();
	txtArea.Text = rdr[2].ToString();
    }

    rdr.Close();
    connection.Close();
}

This would produce:

The NOT Operator

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.

Checking the Nullity of a Field

Depending on how it was created, a field can have actual or null values. To check whether a field is holding a null value, use the following formula for the WHERE statement:

WHERE ColumnName IS NULL

In this case, only the records that are NULL on the ColumnName will be considered in the result.

Negating Some Records

We saw that we can use the NOT operator to negate the validity of a Boolean expression. Consider the following statement:

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

When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate the WHERE condition. To do this, type NOT before the expression. Here is an example:

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

To make this condition easier to read, you should include the positive expression in parentheses. This would be done as follows:

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

This clearly indicates that it is the expression in the parentheses that is being negated. In the same way, you can use the IS NOT NULL to find the records that are not null. For example, you can create a list of only records that do not have a null value on a certain column. Here is an example:

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

When this statement is executed, the table would display only the records that include a state for each student.

Filtering String-Based Fields

Introduction

As you should know already, the values of a certain column can be null or using a value of type char, varchar, or varchar(max), or their variants (nchar, nvarchar, or nvarchar(max)).

The primary Boolean operation you can perform on a field consists of checking its nullity. As mentiond already, this operation can be performed by using IS NULL in its expression. Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
        new SqlConnection("Data Source='EXPRESSION';" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                           "WHERE  [Shelf #] IS NULL;",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

Videos

As mentioned already, to make the condition easier to read, you should include the expression in parentheses. This would be done as follows:

SELECT ALL * FROM Videos
WHERE ([Shelf #] IS NULL);
GO

When the statement executes, the table would display only the records that don't have a value for the state. On the other hand, to get the records that are not null, you would use IS NOT NULL. Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
        new SqlConnection("Data Source='EXPRESSION';" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                           "WHERE  [Shelf #] IS NOT NULL;",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

Videos

Checking for String Equality

Another common operation performed on a field consists of finding out whether it holds a specific value. This is done using the equality "=" operator. Therefore, to find out whether a field holds a certain value, compare it with that value. You must include the value in single-quotes. Here is an example:

SELECT ALL * FROM Videos
WHERE  Rating = N'R';
GO

In a WHERE statement, you can also use the ORDER BY expression to sort a list of records based on a column of your choice. Consider this:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
            new SqlConnection("Data Source='EXPRESSION';" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                                           "WHERE  Rating = N'R';",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

WHERE Condition

Here is an example or sorting the records after filtering:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos = new SqlConnection("Data Source='EXPRESSION';" +
                      "Database='VideoCollection1';" +
                      "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                                           "WHERE  Rating = 'R' " +
                                           "ORDER BY Director;",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

WHERE Condition

Checking for String Inequality

To check whether a field doesn't hold a certain value, you can use the <> operator. Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos = new SqlConnection("Data Source='EXPRESSION';" +
                      "Database='VideoCollection1';" +
                      "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                                           "WHERE Rating <> N'R';",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

Checking for String Inequality

Remember (from Lesson 08) that, besides <>, Transact-SQL also supports the != operator used to perform a comparison for inequality. Therefore, the above statement can also be written as:

SELECT ALL * FROM Videos
WHERE  Rating != N'R';
GO

As an alternative, instead of <> or !=, use the equality operator but precede the expression with NOT. Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos = new SqlConnection("Data Source='EXPRESSION';" +
                      "Database='VideoCollection1';" +
                      "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                                           "WHERE NOT Rating = N'R';",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

The result is the same as if only the comparison for equality was used. Of course, you can precede the <> operation with NOT. Here is an example (the SQL code ROSH database is available):

SELECT StudentNumber, FirstName, LastName, Gender, ParentsNames
FROM Registration.Students
WHERE NOT (Gender <> 'Male');
GO

In this case, the result would include not the records that are not equal to the value, which would be equivalent to using = and not NOT.

Filtering Numeric Fields

Introduction

As done for strings, if a field holds both numeric and null values, to find out whether a field holds a null value, apply the IS NULL expression to its condition. Here is an example:

SELECT [Shelf #], Title, [Year]
FROM Videos
WHERE [Year] IS NULL;
GO

Unlike strings, number-based fields use all Boolean operators supported both by ISO SQL anbd Transact-SQL. They are:

Operation	Used to find out whether
=	A field holds a certain numeric value
<> or !=	A field doesn't hold a certain numeric value or a field has a value different from a certain numeric value
<	A field's value is lower than a certain numeric value
<= or !>	A field's value is lower than or is equal to a certain numeric value or a field's value is not greater than a certain numeric value
>	A field's value is greater than a certain numeric value
>= or !<	A field's value is greater than or is equal to a certain numeric value or a field's value is greater than or is equal to a certain numeric value

Here is an example:

private void btnShowRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos = new SqlConnection("Data Source='EXPRESSION';" +
                      "Database='VideoCollection1';" +
                      "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                            new SqlCommand("SELECT ALL * FROM Collection.Videos " +
                                           "WHERE [Length] > 125;",
                                           cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
        BindingSource bsVideos = new BindingSource();

        DataSet dsVideos = new DataSet("VideosSet");
        sdaVideos.Fill(dsVideos);

        bsVideos.DataSource = dsVideos.Tables[0];
        dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

Boolean Operations on Numeric Fields

The Negativity or Opposite of a Numeric Comparison

There are various ways you can find the negation of a number-based comparison. As seen previously, to negate a comparison, you can precede the expression with the NOT operator. Otherwise, by definition, each Boolean operator has an opposite. They are:

Operation		Opposite
Primary	Also	Primary	Also
=		<>	!=
<>	!=	=
<	!<	>=
<=		>	!>
>	!>	<=
>=		<	!<

Based on this, to find the negativity of a comparision, you can use the opposite operator.

Filtering Boolean Fields

As you may know already, a Boolean field is one whose type is BIT. A Boolean field can hold only one of two values: 0 or 1 (representing False or True, No or Yes, On or Off).

As seen for the other types, a Boolean field can hold a null value if it didn't receive an actual value during data entry. This means that you can compare its value to IS NULL. Here is an example:

SELECT StudentNumber, FirstName, LastName,
       Gender, City, SingleParentHome
FROM   Registration.Students
WHERE  SingleParentHome IS NULL;
GO

In the same way, you can use IS NOT NULL, exactly as done for the other data type, to negate IS NULL.

In Microsoft SQL Server, a Boolean or bit value is treated as a small integer. That is, it behaves as if it can use a value from 0 up. In this case, 0 means that the value of the field is false and 1 means the value of the field is true. When filtering Boolean records, you can use the 0 or the 1 value applied to a column with the equality operator. If you use any other number, or although you can also use any integer, you may get get only empty records.