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:
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:
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:
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;
}
}
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;
}
}
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.
|
|||||||||||||||||||||||
