Home

Microsoft Visual C#: Topics on Filtering Records

   

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:

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

public class Exercise : System.Windows.Forms.Form
{
    Button btnSelect;
    DataGridView dgvVideos;
    Button btnCreateTableAndRecords;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTableAndRecords = new Button();
        btnCreateTableAndRecords.AutoSize = true;
        btnCreateTableAndRecords.Location = new Point(12, 12);
        btnCreateTableAndRecords.Text = "Create Table & Records";
        btnCreateTableAndRecords.Click += new EventHandler(btnDatabaseClick);

        btnSelect = new Button();
        btnSelect.Text = "Select";
        btnSelect.Location = new Point(160, 12);
        btnSelect.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnSelect);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Controls.Add(btnCreateTableAndRecords);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                              AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void CreateTableAndRecords()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE TABLE Videos(" +
                               "[Shelf #] nchar(7) null," +
                               "Title nvarchar(50) not null," +
                               "Director nvarchar(50)," +
                               "[Length] int," +
                               "Rating nchar(10)," +
                               "[Year] int);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO Videos " +
                        "VALUES('DHE-927', 'Two for the Money', 'D.J. Caruso', 123, 'R', 2008)," +
                        "('CGM-683', 'Her Alibi', 'Bruce Beresford', 94, 'PG-13', 1998)," +
                        "('FQT-973', 'Memoirs of a Geisha', 'Rob Marshall', 145, 'PG-13', 2006)," +
                        "('DBT-395', 'Wall Street', 'Oliver Stone', 126, 'R', 2000)," +
                        "(NULL, 'Stealing Harvard', 'Bruce McCulloch', 85, 'PG-13', NULL)," +
                        "('TPH-973', 'A Few Good Men', 'Rob Reiner', 138, NULL, 1992)," +
                        "(NULL, 'The Silence of the Lambs', 'Jonathan Demme', 118, NULL, 1991)," +
                        "('DZV-737', 'The Lady Killers', 'Joel Coen & Ethan Coen', 104, 'R', NULL)," +
                        "(NULL, 'Sneakers', 'Phil Alden Robinson', 126, 'PG-13', 1992)," +
                        "(NULL, 'Annie', 'John Huston', 126, 'G', 1982)," +
                        "(NULL, 'Dave', 'Ivan Reitman', 110, 'PG-13', 1993)," +
                        "('ADR-737', 'Incredibles (The)', 'Brad Bird', 133, 'PG', 2004);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

    void btnDatabaseClick(object sender, EventArgs e)
    {
        CreateTableAndRecords();

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

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

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

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

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

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

This would produce:

Videos

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:

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

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            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. Here is an example:

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

public class Exercise : System.Windows.Forms.Form
{
    Button btnOrder;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnOrder = new Button();
        btnOrder.Text = "Select";
        btnOrder.Location = new Point(12, 12);
        btnOrder.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnOrder);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                           AnchorStyles.Right | AnchorStyles.Bottom;
        ShowVideos();
    }

    void ShowVideos()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE Rating = 'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

    void btnSelectClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE Rating = 'R' " +
                               "ORDER BY Director;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

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

This would produce:

WHERE Condition

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:

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

public class Exercise : System.Windows.Forms.Form
{
    Button btnOrder;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnOrder = new Button();
        btnOrder.Text = "Select";
        btnOrder.Location = new Point(12, 12);
        btnOrder.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnOrder);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                           AnchorStyles.Right | AnchorStyles.Bottom;
        ShowVideos();
    }


    void ShowVideos()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

    void btnSelectClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE  Rating <> N'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

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

Checking for String Inequality

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:

void btnSelectClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE NOT Rating = N'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

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

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

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

Checking for String Not Equal

Notice that 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:

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:

void btnSelectClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand command =
            new SqlCommand("SELECT ALL * FROM Videos " +
                           "WHERE [Length] > 125;",
                           connection);
        connection.Open();
        command.ExecuteNonQuery();

        SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
        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.

Filtering Date/Time Fields

 

Introduction

Transact-SQL provides various data types to support dates, times, and combinations of dates and times. Like a string, the value of a date or time is provided or initialized in single-quotes. Unlike a string, the value of a date or time must follow strict rules inside the single-quotes, otherwise the value would be invalid. When performing comparisons on date/time-based fields, you must keep those rules in mind.

Because a string-based field can contain anything, its comparison allows only equality or inequality. Unlike strings but like numbers, date and time values support all Boolean operators.

Comparing Date/Time Values

Boolean operators used in date/time fields work as follows:

Operation The value of the field
= Exactly matches the indicated date/time value
<> or != Doesn't match the indicated date/time value or is different from the indicated date/time value
< Occurs before the indicated date/time value
<= or !> Occurs before the indicated date/time value or on the same day or time, or doesn't occur after the indicated date/time value
>= or !< Occurs after the indicated date/time value or on the same day or time or doesn't occur before the indicated date/time value
> Occurs after the indicated date/time value

Here is an example:

SELECT StudentNumber, FirstName, LastName, DateOfBirth,
       Gender, City, SingleParentHome
FROM   Registration.Students
WHERE  DateOfBirth >= '1995-01-01';
GO

Negating Date/Time Comparisons

When it comes to comparisons, date and time values follow the same approach as numbers, but date and time values add some logical rules. To negate a date/time comparison, you can precede it with the NOT operator. If you negate an equality comparison, the expression would mean that the date or time doesn't occur on the indicated value, but it doesn't indicate whether the date/time occurs before or after that date. Therefore, in most cases, to negate a date/time comparison, you should use the opposite of the operator. Because date/time values support all Boolean operators, the opposites of those operators apply in the same logic we reviewed for numeric values:

Operation Opposite
= <> or !=
<> or != =
< >= or !<
<= or !> >
> <= or !>
>= or !< <
 

Other Topics on Filtering Records

 

Creating a Table

You can use all or some records from an existing table to create a new table that contains those records. To do this, you use the following formula:

SELECT Columns INTO NewTableName FROM ExistingTable [WHERE Condition]

To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:

void CopyVideos()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection2';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("SELECT * INTO SavedMovies FROM Videos;",
                               connection);
        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("The Videos table (along with its records) has been backed up.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

Instead of using all columns, you can specify only the desired columns after the SELECT keyword. Here is an example:

SELECT Title, Rating INTO ByRatings FROM Videos;
GO

Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example:

SELECT * INTO AllMovies FROM Videos;
GO

In the same way, you can select what columns and what records to put into the new table. To specify the records, you can use a WHERE condition. Here is an example:

void CreateWideScreenList()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection2';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("SELECT CopyrightYear, Title, WideScreen " +
                               "INTO WideScreenList FROM Videos " +
                               "WHERE WideScreen = 1;",
                               connection);
        connection.Open();
        command.ExecuteNonQuery();

        MessageBox.Show("A table of wide screen videos hse been created.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

Functions and Data Selection

 

Using a Built-In Function

To refine your data analysis, you can use functions, whether functions you create yourself or the Transact-SQL built-in functions. As mentioned previously, the first candidates of functions you should try to use are the built-in functions, some of which we reviewed in Lesson 23.

To use a built-in function, in the placeholder of the column, type the name of the function, followed by its parentheses. If the function takes some parameters, remember to follow the rules of calling a parameterized function. Here is an example that uses some date-based built-in functions to display the ages of the students:

SELECT FirstName,
       LastName,
       Sex,
       DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
       City,
       State
FROM Students

This would produce:

Students

You can also include a function in any of the operators we have reviewed so far. Here is an example:

SELECT FirstName, LastName, Gender, DateOfBirth, SPHome
FROM   Students
WHERE  (DateOfBirth BETWEEN CONVERT(DATETIME, N'1995-01-01', 102) AND
			    CONVERT(DATETIME, N'1999-12-31', 102))

This would produce:

Function

If you are working on a Windows application, you can assign the condition to the Filter property of the binding source.

Using a User-Defined Function

If none of the built-in functions satisfies your needs, you can create your own and use it during data analysis. Obviously, you should first create the function. Here is an example of two functions created in the ROSH database:

/* =============================================
   Author:      FunctionX
   Create date: Friday 6 April, 2007
   Description:	This function is used 
                to get the full name of a student
   =============================================*/
CREATE FUNCTION GetFullName
(
	@FName varchar(20),
	@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
	RETURN @LName + N', ' + @FName;
};
GO
/* =============================================
   Author:	FunctionX
   Create date: Saturday 7 April, 2007
   Description:	This function is used 
                to display Yes or No
   ============================================= */
CREATE FUNCTION ShowYesOrNo
(
    @SPHomeStatus bit
)
RETURNS varchar(3)
AS
BEGIN
    DECLARE @Result varchar(3);

    IF @SPHomeStatus = 0
       SET @Result = N'No';
    ELSE
       SET @Result = N'Yes';

    RETURN @Result;
};
GO

Once a function is ready, in the placeholder of your SQL statement, type dbo., followed by the name of the function, its parentheses, and its paremeter(s), if any, inside of the parentheses. Here is an example:

SELECT StudentID,
       dbo.GetFullName(FirstName, LastName) AS [Student's Name],
       Gender,
       dbo.ShowYesOrNo(SPHome) AS [Live's in a Single Parent Home?],
       ParentsNames AS [Parents' Names]
FROM Students;
GO

This would produce:

Function

 

Home Copyright © 2010-2011 C#Key