Details on Data Selection
|
|
Details on Data Selection |
|
Sorting the Records |
|
Sorting the Records in the Table Window |
The lists of records we get with a SELECT statement are presented in the order they have in the table. SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order. After selecting a series of columns, you may want to list the records following an alphabetical order from one specific field. To get an alphabetical or an incremental order of records, you must let the database know what field would be used as reference.
To specify the order, if you are using a table window in Microsoft SQL Server Management Studio or in Microsoft Visual Studio:
If you select Ascending or Sort Ascending, the list of records would be re-arranged based on the type of the selected column:
If you select Descending or Sort Descending, the list of records would be re-arranged based on the type of the selected column:
After selecting the desired Sort Type, you can execute the SQL statement.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace AltairRealtors6
{
public partial class AltairRealtors : Form
{
public AltairRealtors()
{
InitializeComponent();
}
private void AltairRealtors_Load(object sender, EventArgs e)
{
using (SqlConnection cnnAltairRealtors1 =
new SqlConnection("Data Source=(local);" +
"Integrated Security='SSPI';"))
{
string strDatabase = "IF EXISTS (" +
"SELECT * " +
" FROM sys.databases " +
" WHERE name = N'AltairRealtors1' " +
")" +
"DROP DATABASE AltairRealtors1;" +
"CREATE DATABASE AltairRealtors1;";
SqlCommand cmdAltairRealtors1 = new SqlCommand(strDatabase,
cnnAltairRealtors1);
cnnAltairRealtors1.Open();
cmdAltairRealtors1.ExecuteNonQuery();
MessageBox.Show("The AltairRealtors1 database has been created");
}
using (SqlConnection cnnAltairRealtors1 =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
string strAltairRealtors1 =
"CREATE TABLE Properties" +
"(" +
"PropertyID int identity(1,1) NOT NULL," +
"PropertyNumber char(6)," +
"Address varchar(100)," +
"City varchar(50)," +
"State char(2)," +
"ZIPCode varchar(12)," +
"PropertyType varchar(40)," +
"Condition varchar(32)," +
"Bedrooms smallint," +
"Bathrooms float," +
"FinishedBasement bit," +
"IndoorGarage bit," +
"Stories smallint," +
"YearBuilt smallint," +
"MarketValue money" +
");";
SqlCommand cmdAltairRealtors1 =
new SqlCommand(strAltairRealtors1,
cnnAltairRealtors1);
cnnAltairRealtors1.Open();
cmdAltairRealtors1.ExecuteNonQuery();
MessageBox.Show("The Properties table has been created");
}
using (SqlConnection cnnAltairRealtors1 =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
string strAltairRealtors1 =
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('524880', '1640 Lombardo Ave', 'Silver Spring', 'MD'," +
"'20904', 'Single Family', 'Good', 4, 2.5, 3, 1, 3, 1995, 495880.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('688364', '10315 North Hacht Rd', 'College Park', 'MD'," +
"'20747', 'Single Family', 'Excellent', 4, 3.5, 3," +
"1, 2, 2000, 620724.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, FinishedBasement," +
"Stories, MarketValue)" +
"VALUES('611464', '6366 Lolita Drive', 'Laurel', 'MD'," +
"'20707', 'Single Family', 'Good', 1, 2, 422625.00);" +
"INSERT INTO Properties(Address, City, PropertyType," +
"Bedrooms, MarketValue)" +
"VALUES('9002 Palasko Hwy', 'Tysons Corner'," +
"'Condominium', 2, 422895.00);" +
"INSERT INTO Properties(PropertyNumber, State," +
"ZIPCode, Bedrooms, YearBuilt, MarketValue)" +
"VALUES('420115', 'DC'," +
"'20011', 2, 1982, 312555);" +
"INSERT INTO Properties(PropertyNumber, City, ZIPCode," +
"PropertyType, Bedrooms, YearBuilt, MarketValue)" +
"VALUES('917203', 'Alexandria', '22024'," +
"'Single Family', 3, 1965, 345660.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"PropertyType, Condition, Bedrooms, Bathrooms, MarketValue)" +
"VALUES('200417', '4140 Holisto Crt', 'Germantown', 'MD'," +
"'Condominium', 'Excellent', 2, 1, 215495.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('927474', '9522 Lockwood Rd', 'Chevy Chase', 'MD'," +
"'20852', 'Townhouse', 'Bad Shape', 3, 2.5, 3, 0, 3," +
"1992, 415665.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('207850', '14250 Parkdoll Rd', 'Rockville', 'MD'," +
"'20854', 'Townhouse', 'Good', 3, 2.5, 2, 1, 2," +
"1988, 325995.00);" +
"INSERT INTO Properties(City, PropertyType, Bedrooms," +
"YearBuilt, MarketValue)" +
"VALUES('Washington', 'Townhouse', 4, 1975, 366775.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"YearBuilt, MarketValue)" +
"VALUES('288540', '10340 Helmes Street #408', 'Silver Spring', 'MD'," +
"'20906', 'Condominium', 'Good', 1, 1, 2000, 242775.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('247472', '1008 Coppen Street', 'Silver Spring', 'MD'," +
"'20906', 'Single Family', 'Excellent'," +
"3, 3, 3, 1, 3, 1996, 625450.00);" +
"INSERT INTO Properties(City, ZIPCode, PropertyType, " +
"Stories, YearBuilt, MarketValue)" +
"VALUES('Chevy Chase', '20956', 'Single Family', " +
"3, 2001, 525450.00);" +
"INSERT INTO Properties(Address, City, State," +
"PropertyType, Condition, Bedrooms, MarketValue)" +
"VALUES('686 Herod Ave #D04', 'Takoma Park', 'MD'," +
"'Condominium', 'Excellent', 2, 360885.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('297446', '14005 Sniders Blvd', 'Laurel', 'MD'," +
"'20707', 'Townhouse', 'Needs Repair'," +
"4, 1.5, 3, 1, 2, 2002, 412885.00);" +
"INSERT INTO Properties(City, ZIPCode, Condition, Bedrooms," +
"Stories, YearBuilt)" +
"VALUES('Silver Spring', '20905', 'Good'," +
"4, 2, 1965);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('924792', '680 Prushia Rd', 'Washington', 'DC'," +
"'20008', 'Single Family', 'Good'," +
"5, 3.5, 3, 0, 3, 2000, 555885.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('294796', '14688 Parrison Street', 'College Park', 'MD'," +
"'20742', 'Single Family', 'Excellent'," +
"5, 2.5, 2, 1, 2, 1995, 485995.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"YearBuilt, MarketValue)" +
"VALUES('811155', '10340 Helmes Street #1012', 'Silver Spring'," +
"'MD', '20906', 'Condominium', 'Good'," +
"1, 1, 2000, 252775.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('447597', '4201 Vilamar Ave', 'Hyattsville', 'MD'," +
"'20782', 'Townhouse', 'Excellent'," +
"3, 2, 2, 1, 3, 1992, 365880.00);" +
"INSERT INTO Properties(Address, ZIPCode, Bathrooms)" +
"VALUES('1622 Rombard Str', 20904, 2.5);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('297415', '980 Phorwick Street', 'Washington', 'DC'," +
"'20004', 'Single Family', 'Good'," +
"4, 3.5, 3, 3, 1, 2004, 735475.00);" +
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('475974', '9015 Marvin Crow Ave', 'Gaithersburg', 'MD'," +
"'20872', 'Single Family', 'Needs Repair'," +
"4, 2.5, 3, 1, 1, 1965, 615775.00);";
SqlCommand cmdAltairRealtors1 =
new SqlCommand(strAltairRealtors1,
cnnAltairRealtors1);
cnnAltairRealtors1.Open();
cmdAltairRealtors1.ExecuteNonQuery();
MessageBox.Show("Some records have been created in the Properties table.");
}
}
}
}
|
| 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 |
 |
||||||||||||||||||||||||||||||||
|
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace AltairRealtors6
{
public partial class AltairRealtors : Form
{
public AltairRealtors()
{
InitializeComponent();
}
private void AltairRealtors_Load(object sender, EventArgs e)
{
using (SqlConnection cnnProperties =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
string strSelect = "SELECT * FROM Properties;";
SqlCommand cmdProperties = new SqlCommand(strSelect,
cnnProperties);
SqlDataAdapter sdaProperties =
new SqlDataAdapter(cmdProperties);
BindingSource bsProperties = new BindingSource();
DataSet dsProperties = new DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
cnnProperties.Open();
bsProperties.DataSource = dsProperties.Tables[0];
dgvProperties.DataSource = bsProperties;
foreach (DataColumn col in dsProperties.Tables[0].Columns)
clbColumns.Items.Add(col.ColumnName);
}
rdoShowAllFields.Checked = true;
}
}
}
|
private void btnExecute_Click(object sender, EventArgs e)
{
using (SqlConnection cnnProperties =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
string strColumns = "";
foreach (string str in clbColumns.CheckedItems)
strColumns = strColumns + ", " + str;
string strResult = "";
if (rdoShowAllFields.Checked == true)
strResult = "SELECT * FROM Properties";
else
strResult = "SELECT " +
strColumns.Substring(1) +
" FROM Properties";
SqlCommand cmdProperties =
new SqlCommand(strResult, cnnProperties);
SqlDataAdapter sdaProperties =
new SqlDataAdapter(cmdProperties);
BindingSource bsProperties = new BindingSource();
DataSet dsProperties = new DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
cnnProperties.Open();
bsProperties.DataSource = dsProperties.Tables[0];
dgvProperties.DataSource = bsProperties;
}
}
|
private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
{
if (clbColumns.CheckedItems.Count < 1)
rdoShowAllFields.Checked = true;
else
rdoShowSomeFields.Checked = true;
}
|
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
|
In SQL, to specify the sorting order, use the ORDER BY expression. The formula to follow is:
SELECT What FROM WhatObject ORDER BY WhatField;
The column used as the basis must be recognized as part of the selected columns. For example, to get a list of students in alphabetical order based on the LastName column, you can use the following statement:
Practical
Learning: Introducing Data SortingSELECT FirstName,
LastName,
DateOfBirth,
Sex
FROM Students
ORDER BY LastName;
This would produce:
In the same way, you can get the list of girls followed by the list of boys by ordering the list in alphabetical order based on the Sex column. The statement to get this result can be written as follows:
SELECT FirstName, LastName, Gender, EmailAddress FROM Students ORDER BY Gender
As another example, to list all students arranged in alphabetical order by their last name, you can change the statement as follows:
SELECT * FROM Students ORDER BY LastName
By default, records are ordered in Ascending order. Nevertheless, the Ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in Ascending order including the first and last names, you would use a statement as follows:
SELECT * FROM Students ORDER BY LastName ASC
On the other hand, if you want to sort records in reverse order, you can use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:
SELECT FirstName,
LastName,
Gender,
ParentsNames,
SPHome
FROM Students
ORDER BY LastName DESC;
This would produce:
|
|
 |
||||||||||||||||||||
|
private void AltairRealtors_Load(object sender, EventArgs e)
{
// Open a connection to the database
using (SqlConnection cnnProperties =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
// This statement creates a list of all properties
string strSelect = "SELECT * FROM Properties;";
// This data set will hold the tables of the database
DataSet dsProperties = new DataSet("PropertiesSet");
// Create a command to perform on the connection
SqlCommand cmdProperties = new SqlCommand(strSelect,
cnnProperties);
// Create a data adapter that will populate the data set
SqlDataAdapter sdaProperties =
new SqlDataAdapter(cmdProperties);
// Create a binding source
BindingSource bsProperties = new BindingSource();
// Fill the data set with the values
sdaProperties.Fill(dsProperties);
// Open the connection
cnnProperties.Open();
// Get the table from the data set
DataTable tblProperties = dsProperties.Tables[0];
// Assign that table to the binding source
bsProperties.DataSource = tblProperties;
// Apply that binding source to the data grid view
dgvProperties.DataSource = bsProperties;
// Get a list of the columns of the table and
// put them the names of those columns in the checked list box
foreach (DataColumn col in dsProperties.Tables[0].Columns)
{
clbColumns.Items.Add(col.ColumnName);
cbxColumns.Items.Add(col.ColumnName);
}
rdoShowAllFields.Checked = true;
cbxColumns.SelectedIndex = 0;
cbxAscendingDescending.SelectedIndex = 0;
}
}
|
private void btnExecute_Click(object sender, EventArgs e)
{
// Open a connection to the database
using (SqlConnection cnnProperties =
new SqlConnection("Data Source=(local);" +
"Database='AltairRealtors1';" +
"Integrated Security='SSPI';"))
{
// Get the list of columns checked in the list box
// and put it in the Arrange combo box
string strColumns = "";
foreach (string str in clbColumns.CheckedItems)
strColumns = strColumns + ", " + str;
// Find out what radio button is selected and use it
// to know what column(s) will be used
string strResult = "";
if (rdoShowAllFields.Checked == true)
strResult = "SELECT * FROM Properties";
else
strResult = "SELECT " +
strColumns.Substring(1) +
" FROM Properties";
// Find out what sort order is selected and apply it
if (cbxAscendingDescending.Text == "Ascending Order")
strResult = strResult + " ORDER BY " + cbxColumns.Text + " ASC;";
else
strResult = strResult + " ORDER BY " + cbxColumns.Text + " DESC;";
// Create a command to execute on the database
SqlCommand cmdProperties =
new SqlCommand(strResult, cnnProperties);
// Create a data adapter that will populate the data set
SqlDataAdapter sdaProperties =
new SqlDataAdapter(cmdProperties);
// Create a binding source
BindingSource bsProperties = new BindingSource();
// Create a data set
DataSet dsProperties = new DataSet("PropertiesSet");
// Fill the data set with the records in the data adapter
sdaProperties.Fill(dsProperties);
// Open the connection
cnnProperties.Open();
// Assign the Properties table to the binding source
bsProperties.DataSource = dsProperties.Tables[0];
// Assign the binding source to the data grid view
dgvProperties.DataSource = bsProperties;
}
}
|
private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
{
// This list will hold the names of checked columns in the list box
List<string> lstColumns = new List<string>();
// Assist the user with checking the radio buttons
// based on the contents of the list box
if (clbColumns.CheckedItems.Count < 1)
rdoShowAllFields.Checked = true;
else
rdoShowSomeFields.Checked = true;
// If the second radio button is selected, get the columns
// checked in the list box and add them to the Arrange combo box
if (rdoShowSomeFields.Checked == true)
{
cbxColumns.Items.Clear();
foreach (object objItem in clbColumns.CheckedItems)
cbxColumns.Items.Add(objItem);
cbxColumns.SelectedIndex = 0;
}
}
|
|
Sorting the Records in the Data Grid View |
If you use a data grid view in your application, you can sort records without writing a single line of code. To sort the records based on a particular column, click the column header. After clicking for the first time, the column is sorted alphabetically, incrementally, or chronologically and an up-pointing arrow button would appear on the column header. Here is an example on the City column:
To sort records in reverse order based on a particular column, you can click the column again. Or, you must first click the column header to sort in order, then click the same column header again to reverse. When the records are sorted in reverse, a down-pointing arrow button would appear on the column header. Here is an example on the ZIPCode column:
|
|
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace AltairRealtors6c
{
public partial class AltairRealtors : Form
{
DataGridViewColumn colSelected;
public AltairRealtors()
{
InitializeComponent();
}
. . . No Change
|
private void AltairRealtors_Load(object sender, EventArgs e)
{
colSelected = new DataGridViewColumn();
. . . No Change
}
|
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];
}
|
private void mnuSortAscending_Click(object sender, EventArgs e)
{
// Since we know what column is going to be used to sort the record,
// synchronize it with the Arrange combo box
cbxColumns.Text = colSelected.Name;
// Since the user clicked Ascending, synchronize with the in combo box
cbxAscendingDescending.SelectedIndex = 0;
// Sort the records using the data grid view
dgvProperties.Sort(colSelected, ListSortDirection.Ascending);
}
|
private void mnuSortDescending_Click(object sender, EventArgs e)
{
// Since we know what column is going to be used to sort the record,
// synchronize it with the Arrange combo box
cbxColumns.Text = colSelected.Name;
// Since the user clicked Ascending, synchronize with the in combo box
cbxAscendingDescending.SelectedIndex = 1;
// Sort the records using the data grid view
dgvProperties.Sort(colSelected, ListSortDirection.Descending);
}
|
|
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 in Lesson 21. Here is an example of an expression
> '12/31/1993'
This means that the dates that occur after 1993 would be selected.
|
|
| 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 |
| Object | New Name |
| propertiesBindingSource | bsProperties |
| propertiesTableAdapter | tadProperties |
| propertiesBindingNavigator | bnProperties |
| 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 |
altairusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
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];
}
}
}
|
private void mnuSortAscending_Click(object sender, EventArgs e)
{
// Sort the records using the data grid view
dgvProperties.Sort(colSelected, ListSortDirection.Ascending);
}
|
private void mnuSortDescending_Click(object sender, EventArgs e)
{
// Sort the records using the data grid view
dgvProperties.Sort(colSelected, ListSortDirection.Descending);
}
|
|
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 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:
When creating a condition, you can sort it if you want. If you are working in a table window, in the Criteria section, under the Sort Type column, click the box corresponding to the field that will be used as the basis. In the Filter column, click the box that corresponds to the column that will hold the condition and enter the expression. Here is an example:
If you are writing the SQL statement, after the WHERE condition, enter the ORDER BY expression. Here is an example:
SELECT DateOfBirth, LastName, FirstName,
Sex, State, ParentsNames
FROM Students
WHERE State='MD'
ORDER BY LastName;
This would produce:
|
|
private void mnuFilterBySelection_Click(object sender, EventArgs e)
{
string strResult = dgvProperties.CurrentCell.Value.ToString();
if (strResult == "")
{
if( (colSelected.ValueType == Type.GetType("System.Int16")) ||
(colSelected.ValueType == Type.GetType("System.Double")))
bsProperties.Filter = "IsNull(" +
dgvProperties.Columns[colSelected.Index].DataPropertyName +
", '0') = 0";
else
bsProperties.Filter = "IsNull(" +
dgvProperties.Columns[colSelected.Index].DataPropertyName +
", 'Null Column') = 'Null Column'";
}
else
bsProperties.Filter =
dgvProperties.Columns[colSelected.Index].DataPropertyName +
" = '" + strResult + "'";
}
|
 |
 |
 |
|
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
This would produce:
Notice that the SELECT statement doesn't have the Gender column and the resulting query doesn't show the Gender column.
|
Negating Some Records |
In Lesson 21, we saw that you could 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 don't 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.
|
|
private void mnuFilterExclSel_Click(object sender, EventArgs e)
{
string strResult = dgvProperties.CurrentCell.Value.ToString();
if (strResult == "")
{
if( (colSelected.ValueType == Type.GetType("System.Int16")) ||
(colSelected.ValueType == Type.GetType("System.Double")))
bsProperties.Filter = "IsNull(" +
dgvProperties.Columns[colSelected.Index].DataPropertyName +
", '0') <> 0";
else
bsProperties.Filter = "IsNull(" +
dgvProperties.Columns[colSelected.Index].DataPropertyName +
", 'Null Column') <> 'Null Column'";
}
else
bsProperties.Filter =
dgvProperties.Columns[colSelected.Index].DataPropertyName +
" <> '" + strResult + "'";
}
|
private void mnuRemoveFilterSort_Click(object sender, EventArgs e)
{
bsProperties.Filter = null;
bsProperties.Sort = null;
}
|
 |
 |
| Previous | Copyright © 2008-2009 Yevol.com | Next |