Introduction to Filtering Records
Introduction to Filtering Records
Introduction to Conditions
WHERE is the Condition?
When SELECTing records from a table, you may want to get only records that respond to a certain condition. This is referred to as filtering records.
To assist you in filtering records, (both) the SQL (and Transact-SQL) provide(s)s an operator named WHERE.
Practical Learning: Introducing Sorting Records
USE master; GO CREATE DATABASE AltairRealtors1; GO USE AltairRealtors1; GO
CREATE SCHEMA RealEstate;
GO
CREATE SCHEMA Management;
GO
CREATE TABLE Management.PropertiesTypes
(
PropertyType nvarchar(20),
[Description] nvarchar(max),
constraint pk_properties_types primary key(PropertyType)
);
GO
CREATE TABLE Management.PropertiesConditions
(
Condition nvarchar(20),
[Description] nvarchar(max),
constraint pk_properties_conditions primary key(Condition)
);
GO
CREATE TABLE Management.SalesStatus
(
SaleStatus nvarchar(20),
[Description] nvarchar(max),
constraint pk_sales_status primary key(SaleStatus)
);
GO
CREATE TABLE RealEstate.Properties
(
PropertyNumber int,
PropertyType nvarchar(20),
[Address] nvarchar(120),
City nvarchar(40),
Neighborhood nvarchar(40),
County nvarchar(50),
[State] nchar(2),
Bedrooms tinyint,
Bathrooms decimal,
FinishedBasement bit,
IndoorGarage bit,
Stories tinyint,
Condition nvarchar(20),
YearBuilt smallint,
SaleStatus nvarchar(20),
MarketValue integer,
constraint fk_properties_types foreign key(PropertyType) references Management.PropertiesTypes(PropertyType),
constraint fk_properties_conditions foreign key(Condition) references Management.PropertiesConditions(Condition),
constraint fk_sales_status foreign key(SaleStatus) references Management.SalesStatus(SaleStatus),
constraint pk_properties primary key(PropertyNumber)
);
GO
INSERT INTO Management.PropertiesTypes
VALUES(N'Unknown', N'The type of property cannot be specified or is not clear.'),
(N'Condominium', N'A condominium, also called condo, is a unit built in a small, medium, or large building. It ressembles an apartment. It may have one, two, or more bedrooms.'),
(N'Townhouse', N'A townhouse, sometimes called a town house or town home, is a relatively small house attached to at least another house.'),
(N'Single Family', N'A single family is a stand-alone house. It may have one, two or three levels, also called stories.');
GO
INSERT INTO Management.PropertiesConditions
VALUES(N'Unknown', N'The condition of the property cannot be determined or is not clear.'),
(N'Good', N'A property is good if it is good enough to be sold. It may be less than perfect but it is wholly acceptable.'),
(N'Excellent', N'An excellent property is one that has everything perfect or almost. There are no major repairs to be made.'),
(N'Bad Shape', N'A property is in bad shape if it requires a mojor or many repairs.'),
(N'Under Inspection', N'The property is currently under review or community inspection. It is not ready for sale but no major work needs to be done.'),
(N'Needs Repair', N'This type of condition indicates that one or more repairs are necessairy. The property in this condition is not ready for sale.');
GO
INSERT Management.SalesStatus
VALUES(N'Unknown', N'The sale status of the property is not known.'),
(N'Ready For Sale', N'The property is currently available for sale.'),
(N'Sold', N'The property has been sold.');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], Condition, SaleStatus)
VALUES(927415, N'4140 Holisto Crt', N'Germantown', N'Montgomery', N'MD', N'Under Inspection', N'Ready For Sale');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, SaleStatus, [Address], City, Neighborhood, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, Stories, YearBuilt, MarketValue)
VALUES(395860, N'Ready For Sale', N'1508 Quaddra Str #808', N'York', N'Valley View', N'PA', N'Condominium', N'Good', 2, 2.5, 0, 8, 1996, 415665);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue, SaleStatus)
VALUES(593804, N'10384 East Side Rd', N'Alexandria', N'Potomac Yard', N'Alexandria', N'VA', N'Single Family', N'Excellent', 4, 3.5, 0, 0, 2, 1998, 655505, N'Sold'),
(803184, N'284 Bolston Ave', N'Baltimore', N'Hamden', N'Baltimore', N'MD', N'Single Family', N'Needs Repair', 4, 2.5, 0, 1, 3, 2010, 165885, N'Ready For Sale');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], SaleStatus, City, Neighborhood, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(393585, N'7707 Schwartzer Rd', N'Ready For Sale', N'Rockville', N'Court House', N'MD', N'Single Family', N'Excellent', 5, 3.5, 1, 1, 3, 1999, 844725);
GO
INSERT INTO RealEstate.Properties(SaleStatus, PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, Bedrooms, Bathrooms, Stories, YearBuilt, MarketValue)
VALUES(N'Ready For Sale', 150281, N'9205 Arrow Consfield Str', N'Bowie', N'Bowie State', N'Prince George', N'MD', N'Single Family', N'Excellent', 5, 3.5, 0, 2014, 782575);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue, SaleStatus)
VALUES(867703, N'10282 Harrison Drv #406', N'Charleston', N'Hillsdale', N'WV', N'Condominium', N'Under Inspection', 1, 1, 0, 1, 6, 2016, 675225, N'Sold');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, City, Neighborhood, County, PropertyType, Condition, Bedrooms, FinishedBasement, Stories, YearBuilt)
VALUES(372804, N'Columbia', N'Town Center', N'Howard', N'Townhouse', N'Good', 4, 1, N'3', 2008);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(684462, N'7715 Sharron Str', N'College Park', N'University Blvd', N'Prince George', N'MD', N'Single Family', N'Excellent', 4, 3.5, 1, 0, 3, 1998, 498675);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue, SaleStatus)
VALUES(179375, N'927 Willard Rd', N'Silver Spring', N'Brigs Chaney', N'MD', N'Townhouse', N'Under Inspection', 5, 3.5, 1, 1, 3, 2011, 782500, N'Ready For Sale');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(270394, N'South Ventura Ave', N'Baltimore', N'Garwyn Oaks', N'Baltimore', N'MD', N'Townhouse', N'Good', 3, 2.5, 1, 0, 3, 1990, 244955);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, SaleStatus, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(486263, N'8226 Athena Drv NW #404', N'Washington', N'16th Str NW', N'Washington', N'DC', N'Condominium', N'Needs Repair', N'Sold', 2, 2, 0, 0, 4, 2000, 318950);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, City, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, Stories, YearBuilt, MarketValue, SaleStatus)
VALUES(925703, N'Bethesda', N'Single Family', N'Unknown', 5, 4.5, 1, 3, 2010, 1180500, N'Sold');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, City, Neighborhood, [State], PropertyType, Condition, Bedrooms, IndoorGarage, MarketValue, SaleStatus)
VALUES(206417, N'Baltimore', N'Towson', N'MD', N'Condominium', N'Unknown', 2, 1, 215495, N'Ready For Sale');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt)
VALUES(507150, N'14250 Parkdoll Rd', N'Rockville', N'Twinbrook', N'Montgomery', N'MD', N'Good', 3, 2.5, 1, 1, 2, 1988);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], PropertyType, Bedrooms, Bathrooms, FinishedBasement, MarketValue, SaleStatus)
VALUES(240875, N'842 Hempton Street', N'Charleston', N'Kanawha', N'WV', N'Townhouse', 3, 2.5, 0, 348500, N'Sold');
GO
INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Neighborhood, County, [State], PropertyType, Condition, SaleStatus, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(747472, N'418 Woodmont Ave', N'Hanover', N'McSherrystown', N'York', N'PA', N'Single Family', N'Under Inspection', N'Ready For Sale', 4, 2.50, 1, 0, 2, 1980, 4246605),
(304050, N'1008 Coppen Street', N'Silver Spring', N'Aspen Hill', N'Montgomery', N'MD', N'Single Family', N'Excellent', N'Sold', 3, 3, 1, 1, 3, 1996, 685755),
(831714, N'927 Montgomery Ave', N'Alexandria', N'Huntington', 'Alexandria', N'VA', N'Townhouse', N'Good', N'Ready For Sale', 4, 2.5, 1, 0, 3, 1992, 4859205);
GO
INSERT INTO RealEstate.Properties(PropertyNumber, City, Neighborhood, County, [State], Bedrooms, Bathrooms)
VALUES(269384, N'Gettysburg', N'Round Top', N'Adams', N'PA', 0, 0);
GO<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.6.2"/>
<httpRuntime targetFramework="4.6.2"/>
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701"/>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/>
</compilers>
</system.codedom>
<connectionStrings>
<add name="csAltairRealtors" connectionString="Data Source=(local);Initial Catalog=AltairRealtors1;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration><?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.6.2"/>
<httpRuntime targetFramework="4.6.2"/>
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701"/>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/>
</compilers>
</system.codedom>
<connectionStrings>
<add name="csAltairRealtors" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\AltairRealtors1.mdf;Initial Catalog=AltairRealtors1;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration><!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
<style type="text/css">
.maroon { color: maroon; }
.back-maroon { background-color: maroon; }
.right-aligned { text-align: right; }
.centered { text-align: center; }
.tbl-hdr { font-weight: 600;
color: antiquewhite; }
.back-light { background-color: antiquewhite;
color: black; }
.listing { margin: auto;
width: 1425px; }
</style>
</head>
<body>
<h1 class="centered maroon">Altair Realtors - Properties Listing</h1>
<div class="listing">
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
string strConnectionStringName = null;
// If you are using a local database, keep the following line:
strConnectionStringName = "csAltairRealtors";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> SaleStatus = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT DISTINCT SaleStatus " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
SaleStatus.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT PropertyNumber, PropertyType, City, Neighborhood, County, " +
" [State], Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, " +
" Stories, YearBuilt, Condition, SaleStatus, MarketValue " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr class="tbl-hdr back-maroon">
<td class="centered">Property #</td>
<td>Property Type</td>
<td>City</td>
<td>Neighborhood</td>
<td>County</td>
<td class="centered">State</td>
<td class="centered">Beds</td>
<td class="centered">Baths</td>
<td class="centered">Finished Basement?</td>
<td class="centered">Indoor Garage?</td>
<td class="centered">Stories</td>
<td class="centered">Year Built</td>
<td>Condition</td>
<td>Sale Status</td>
<td class="right-aligned">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr class="back-light">
<td class="centered">@sdrProperties[0]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[1].ToString(), @PropertyType, new { @class = "back-light" })</td>
<td>@sdrProperties[2]</td>
<td>@sdrProperties[3]</td>
<td>@sdrProperties[4]</td>
<td>@sdrProperties[5]</td>
<td class="centered">@sdrProperties[6]</td>
<td class="centered">@sdrProperties[7]</td>
@if (sdrProperties[8].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td class="centered">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[9].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td class="centered">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td class="centered">@sdrProperties[10]</td>
<td class="centered">@sdrProperties[11]</td>
<td>@Html.DropDownList("Condition", @sdrProperties[12].ToString(), @Condition, new { @class = "back-light" })</td>
<td>@Html.DropDownList("SaleStatus", @sdrProperties[13].ToString(), @SaleStatus, new { @class = "back-light" })</td>
<td class="centered">@sdrProperties[14]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>
The Expression to Test
The starting formula to create a conditional selection in the SQL is:
SELECT column(s)-name(s) FROM object-name WHERE expression;
When creating a filtering condition, the expression is formulated as a logical condition. The expression is called a criterion (the plural is criteria). The expression is formulated as follows:
column-name operator value
The column-name must be an existing column of the table indicated in the FROM section. The column name is followed by a logical operator. The value depends on the type of, and is applied to, the column based on the operator. If the expression is well written, the SELECT statement will produce only the records that respond to the condition.
The Comparison for Equality
The SQL supports the same comparison operators as C#, with a few (minor) exceptions. For example, to find records that are equal to a certain value, from our above WHERE formula, the expression for equality comparison can be formulated as follows:
column-name = value
The NULL Constant
As mentioned in previous lessons, a column is null if it doesn't have a value. To support this possibility for data analysis, the SQL provides a constant named NULL.
SQL Logical Operators: Comparison for a Lesser Value
Introduction
To let you compare the values of a column for a lesser value, the SQL supports the same Less Than operator (<) as C#. The comparison is easily intuitive for numbers. The code in the following example produces a list of houses that cost less than $500,000.
Practical Learning: Comparing for a Lesser Value
. . . No Change
<div class="listing">
@{
. . . No Change
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT PropertyNumber, PropertyType, City, Neighborhood, County, " +
" [State], Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, " +
" Stories, YearBuilt, Condition, SaleStatus, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE MarketValue < 500000;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
. . . No Change
}
</div>
</body>
</html>
The Comparison for Lesser Values and Boolean Columns
Remember that a Boolean column has only two values: 0 and 1. If you apply the < operator to the 0 value of such a colum, because there is no such a value, you would not get any record. If you compare the column with < 1, because the other value responds to that condition, you would get the records that responds to = 0.
The Comparison for Lesser Values and String-Based Columns
You can use the < operator to compare the values of a string-based column. In this case, the SQL interpreter would apply the rules of the language on the user's computer to compare strings. The comparison is case-sensitive. Consider the following WHERE statement:
SELECT PropertyNumber, City, County, Neighborhood,
[State], PropertyType, Condition,
Bedrooms, Bathrooms, FinishedBasement,
IndoorGarage, Stories, YearBuilt, MarketValue
FROM RealEstate.Properties
WHERE City < N'Charleston';
In this case, the SQL interpreter would first compare the first letter of the City column to C (in uppercase). All records whose City value start with D (and d) to Z (and z) would be excluded. All records whose City value start with A (or a) or B (or b) would be included. If a record starts with C in uppercase, its second letter would be compared to h, which is the second letter of Charleston in our example. The SQL interpreter would continue the comparisions to decide what records to include and which ones to exclude.
Practical Learning: Comparing for a Lesser String Value
. . . No Change
<div class="listing">
@{
. . . No Change
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT PropertyNumber, PropertyType, City, Neighborhood, County, " +
" [State], Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, " +
" Stories, YearBuilt, Condition, SaleStatus, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE MarketValue < 500000;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
. . . No Change
}
</div>
</body>
</html>
Comparing for a Lesser or Equal Value
The SQL supports comparison for a Less Than or Equal value using the same <= operator as C#. Once again, this operator follows the same logic as done in elementary algebra. In addition to the rules of the < operator, the <= adds the rules of the equality comparison.
You can apply the <= operator to a Boolean column. If you apply the "column-name <= 0", you would get the same records as "column-name = 0". On the other hand, "column-name <= 1" is not the same as "column-name = 1" because the "<= 1" includes the records that have 0. Normally, you should refrain from applying the <= 1 comparison to a column.
The <= operator can be applied to a string-based column. The result would include the records that have the indicated value plus the strings that are alphabetically lower than those of the indicated value.
As an alternative to <=, Transact-SQL provides the !l> operator to perform the same operation.
The Comparison for Greater Value
Introduction
To get records where the numeric value of a certain column is greater than a value of your choice, apply the > operator. Here is an example:
SELECT ALL *
FROM RealEstate.Properties
WHERE MarketValue > 500000;
Practical Learning: Comparing for a Greater Value
. . . No Change
<div class="listing">
@{
. . . No Change
using (System.Data.SqlClient.SqlConnection scRealEstate = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[strConnectionStringName].ConnectionString))
{
string strProperties = "SELECT PropertyNumber, PropertyType, City, Neighborhood, County, " +
" [State], Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, " +
" Stories, YearBuilt, Condition, SaleStatus, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE MarketValue > 500000;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scRealEstate);
scRealEstate.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
. . . No Change
}
</div>
</body>
</html>
The Comparison for Greater Boolean Value
You can apply the > operator on a Boolean-Based column. If you apply the > 0 to a column, because the only other value is greater than 1, you would get the same records as if you had applied = 0.
Practical Learning: Comparing for Greater Boolean Value
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE IndoorGarage > 0;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="background-color: #5d0505; color: #f1e7bc; text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for(int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty[0]</td>
<td>@drProperty[1]</td>
<td>@drProperty[2]</td>
<td>@drProperty[3]</td>
<td style="text-align: center">@drProperty[4]</td>
<td>@Html.DropDownList("PropertyType", @drProperty[5].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @drProperty[6].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty[7]</td>
<td style="text-align: center">@drProperty[8]</td>
@if (drProperty[9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty[10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center; background-color: #5d0505; color: #f1e7bc">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty[11]</td>
<td style="text-align: right">@drProperty[12]</td>
<td style="text-align: right;">@drProperty[13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
The > operator can be applied to a text-based column. It follows the same logic as the < operator except that it operats on the opposed direction with regards to the alphabet.
The Comparison for Greater or Equal Value
To let you find the records where the values of a certain column are greater than or equal to a certain value, the SQL provides the >= value. It can be applied to integers and strings. It follows the same approach as the <= operator except that it operators on the indicated values and those that are numerically or alphabetically greater than the indicated value.
The >= operator can be applied to column that use a BIT data type. If you apply the >= 1 expression to a column, you would get records that have the values = 1. You should not apply the >= 0 expression to a column because the would include the records that are True on that column, which would deceive the purpose.
As an alternative to the >= operator of SQL, Transact-SQL provides the !< operator to perform the same operation.
SQL Logical Operators
Negating a Condition
To let you perform more logical operations for data analysis, the SQL provides some operators in addition to the classic Boolean ones.
To let you get the opposite of an operation, the SQL provides the NOT operator. You can apply it to any of the logical operations we have seen so far. To apply the NOT operator, type it just after b>WHERE. Here is an example:
SELECT * FROM RealEstate.Properties
WHERE NOT PropertyType = N'Single Family';This code works just fine, but to make it easier to ready, you should treat the NOT operator like a function (or method). This means that the expression that comes after this operator should be included in parentheses. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
" WHERE NOT(PropertyType = N'Single Family');";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td style="background-color: #5d0505; color: #f1e7bc;">Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for(int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty[0]</td>
<td>@drProperty[1]</td>
<td>@drProperty[2]</td>
<td>@drProperty[3]</td>
<td style="text-align: center">@drProperty[4]</td>
<td style="background-color: #5d0505; color: #f1e7bc;">@Html.DropDownList("PropertyType", @drProperty[5].ToString(), @PropertyType, new { style = "background-color: #5d0505; color: yellow;" })</td>
<td>@Html.DropDownList("Condition", @drProperty[6].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty[7]</td>
<td style="text-align: center">@drProperty[8]</td>
@if (drProperty[9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty[10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty[11]</td>
<td style="text-align: right">@drProperty[12]</td>
<td style="text-align: right;">@drProperty[13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
Of course, you can arrange the result by adding an ORDER BY expression as the last line of the whole WHERE clause. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE NOT (PropertyType = N'Single Family') " +
"ORDER BY City";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td style="background-color: darkgoldenrod; color: yellow">City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td style="background-color: #5d0505; color: #f1e7bc;">Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for(int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty[0]</td>
<td style="background-color: darkgoldenrod; color: yellow">@drProperty[1]</td>
<td>@drProperty[2]</td>
<td>@drProperty[3]</td>
<td style="text-align: center">@drProperty[4]</td>
<td style="background-color: #5d0505; color: #f1e7bc;">@Html.DropDownList("PropertyType", @drProperty[5].ToString(), @PropertyType, new { style = "background-color: #5d0505; color: yellow;" })</td>
<td>@Html.DropDownList("Condition", @drProperty[6].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty[7]</td>
<td style="text-align: center">@drProperty[8]</td>
@if (drProperty[9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty[10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty[11]</td>
<td style="text-align: right">@drProperty[12]</td>
<td style="text-align: right;">@drProperty[13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
Where a Column IS NULL
Consider the following query:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for (int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty[0]</td>
<td>@drProperty[1]</td>
<td>@drProperty[2]</td>
<td>@drProperty[3]</td>
<td style="text-align: center">@drProperty[4]</td>
<td>@Html.DropDownList("PropertyType",
@drProperty[5].ToString(),
@PropertyType,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition",
@drProperty[6].ToString(),
@Condition,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty[7]</td>
<td style="text-align: center">@drProperty[8]</td>
@if (drProperty[9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty[10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty[11]</td>
<td style="text-align: right">@drProperty[12]</td>
<td style="text-align: right;">@drProperty[13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
Notice that ther are some cells are empty. Notice particularly that the County column has three empty cells. During the original data entry, the empty placeholder didn't receive a value. As a matter of fact, they are not empty in the strict sense. In the database sense, those records are null.
To let you find out whether a column has null values, the SQL provides an operator named IS. This opeerator is used to compare a column to the NULL constant that we introduced earlier. This means that the operator is used as an IS NULL expression.
To apply the IS NULL expression, type it after the WHERE column-name expression. This means that the IS NULL expression qualifies the column.
The IS NULL operation can be applied to a column of any type. It produces the records that don't have a value on a certain column. Here is an example applied to a string-based column:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE County IS NULL;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td style="background-color: #5d0505; color: #f1e7bc;">County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for (int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][0]</td>
<td>@dsProperties.Tables[0].Rows[i][1]</td>
<td style="background-color: #5d0505; color: #f1e7bc;">@dsProperties.Tables[0].Rows[i][2]</td>
<td>@dsProperties.Tables[0].Rows[i][3]</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][4]</td>
<td>
@Html.DropDownList("PropertyType",
@dsProperties.Tables[0].Rows[i][5].ToString(),
@PropertyType,
new { style = "background-color: antiquewhite; color: BLACK;" })</td>
<td>@Html.DropDownList("Condition",
@dsProperties.Tables[0].Rows[i][6].ToString(),
@Condition,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][7]</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][8]</td>
@if (dsProperties.Tables[0].Rows[i][9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (dsProperties.Tables[0].Rows[i][10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@dsProperties.Tables[0].Rows[i][11]</td>
<td style="text-align: right">@dsProperties.Tables[0].Rows[i][12]</td>
<td style="text-align: right;">@dsProperties.Tables[0].Rows[i][13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
When this expression is applied to a string-based column, the result includes only the columns that were NULL during data entry. This means that if a column had received a value but then the value was deleted, the column is not NULL and it would not be included in the result.
Return to Microsoft SQL Server Management Studio. In the Object Explorer, under AltairRealtors, expand Tables. Right-click RealEstate.Properties and click Edit Top 200 Rows
Notice that all empty cells are marked NULL. As mentioned already, those records didn't receive values during data entry.
In the County column, click every Alexandria and press Delete (there are 2 Alexandria values to delete) (Alexandria is an independent city in VA, USA, and doesn't belong to, or is not managed by, a county). In the same County column, click Washington and press Delete (Washington, DC doesn't have counties (unlike New York City)):
If you didn't close the browser, return to it and refresh it; otherwise, return to Microsoft Visual Studio and execute the code again:
Notice that you still get only 5 records. Remove the condition in the SQL statement:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td style="background-color: #5d0505; color: #f1e7bc;">County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for (int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][0]</td>
<td>@dsProperties.Tables[0].Rows[i][1]</td>
<td style="background-color: #5d0505; color: #f1e7bc;">@dsProperties.Tables[0].Rows[i][2]</td>
<td>@dsProperties.Tables[0].Rows[i][3]</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][4]</td>
<td>@Html.DropDownList("PropertyType",
@dsProperties.Tables[0].Rows[i][5].ToString(),
@PropertyType,
new { style = "background-color: antiquewhite; color: BLACK;" })</td>
<td>@Html.DropDownList("Condition",
@dsProperties.Tables[0].Rows[i][6].ToString(),
@Condition,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][7]</td>
<td style="text-align: center">@dsProperties.Tables[0].Rows[i][8]</td>
@if (dsProperties.Tables[0].Rows[i][9].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (dsProperties.Tables[0].Rows[i][10].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@dsProperties.Tables[0].Rows[i][11]</td>
<td style="text-align: right">@dsProperties.Tables[0].Rows[i][12]</td>
<td style="text-align: right;">@dsProperties.Tables[0].Rows[i][13]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce
Notice that there are now more empty records under the County column.
Where a Column IS Not NULL
Negating an IS NULL consists of find the records that are not null. You have various options. You can apply the NOT operator immediately after the WHERE keyword. Here is an example:
SELECT ALL *
FROM RealEstate.Properties
WHERE not PropertyType is null;As an alternative, the SQL provides the IS NOT NULL expression written after the WHERE keyword. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE PropertyType IS NOT NULL;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td style="background-color: #5d0505; color: yellow;">Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for (int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty["PropertyNumber"]</td>
<td>@drProperty["City"]</td>
<td>@drProperty["County"]</td>
<td>@drProperty["Neighborhood"]</td>
<td style="text-align: center">@drProperty["State"]</td>
<td style="background-color: #5d0505; color: #f1e7bc;">
@Html.DropDownList("PropertyType",
@drProperty["PropertyType"].ToString(),
@PropertyType,
new { style = "background-color: #5d0505; color: yellow;" })</td>
<td>@Html.DropDownList("Condition",
@drProperty["Condition"].ToString(),
@Condition,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty["Bedrooms"]</td>
<td style="text-align: center">@drProperty["Bathrooms"]</td>
@if (drProperty["FinishedBasement"].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty["IndoorGarage"].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty["Stories"]</td>
<td style="text-align: right">@drProperty["YearBuilt"]</td>
<td style="text-align: right;">@drProperty["MarketValue"]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
By the way, to make your condition easier to read, you should include the column-name IS NULL or the column-name IS NOT NULL expression in parentheses. Here is an example:
SELECT * FROM RealEstate.Properties
WHERE (PropertyType IS NULL);Of course, if necessary, you can arrange the records by adding an ORDER BY expression at the end of the SELECT statement.
Not Showing a Column
When formulating a SELECT statement, you can apply a condition to a column without including that column in the result. To hide a column in the results of a query, omit the column in the SELECT statement. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing - Single Family Houses</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing - Single Family Houses</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, County, Neighborhood, [State], Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue " +
"FROM RealEstate.Properties " +
"WHERE PropertyType = N'single family';;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
cmdProperties.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaProperties = new System.Data.SqlClient.SqlDataAdapter(cmdProperties);
System.Data.DataSet dsProperties = new System.Data.DataSet("PropertiesSet");
sdaProperties.Fill(dsProperties);
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>County</td>
<td>Neighborhod</td>
<td style="text-align: center">State</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@for (int i = 0; i < dsProperties.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsProperties.Tables[0].Rows[i];
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@drProperty["PropertyNumber"]</td>
<td>@drProperty["City"]</td>
<td>@drProperty["County"]</td>
<td>@drProperty["Neighborhood"]</td>
<td style="text-align: center">@drProperty["State"]</td>
<td>@Html.DropDownList("Condition",
@drProperty["Condition"].ToString(),
@Condition,
new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@drProperty["Bedrooms"]</td>
<td style="text-align: center">@drProperty["Bathrooms"]</td>
@if (drProperty["FinishedBasement"].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (drProperty["IndoorGarage"].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center;">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@drProperty["Stories"]</td>
<td style="text-align: right">@drProperty["YearBuilt"]</td>
<td style="text-align: right;">@drProperty["MarketValue"]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
If necessary, you can arrange the records based on any column of your choice.
Practical Learning: Filtering Records
CREATE TABLE WaterMeters ( WaterMeterID INT IDENTITY(1, 1), MeterNumber NVARCHAR(20) NOT NULL, Make NVARCHAR(30) NOT NULL, Model NVARCHAR(20) NOT NULL, MeterSize NVARCHAR(20) NOT NULL, CONSTRAINT PK_WaterMeters PRIMARY KEY(WaterMeterID) ); GO
body {
background-color: #FFF;
}
.bold { font-weight: 600; }
.blue { color: #286090; }
.top-padding { padding-top: 0.50em; }
.common-font { font-family: Georgia, Garamond, 'Times New Roman', serif; }using System.Web.Optimization;
namespace WaterDistributionBusiness1
{
public class BundleConfig
{
// For more information on bundling, visit https://go.microsoft.com/fwlink/?LinkId=301862
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
"~/Scripts/jquery-{version}.js"));
bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
"~/Scripts/jquery.validate*"));
// Use the development version of Modernizr to develop with and learn from. Then, when you're
// ready for production, use the build tool at https://modernizr.com to pick only the tests you need.
bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
"~/Scripts/modernizr-*"));
bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
"~/Scripts/bootstrap.js"));
bundles.Add(new StyleBundle("~/Content/css").Include(
"~/Content/bootstrap.css",
"~/Content/site.css",
"~/Content/WaterDistribution.css"));
}
}
}<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
</appSettings>
<connectionStrings>
<add name="csWaterDistribution" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WaterDistribution.mdf;Initial Catalog=WaterDistribution;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
. . . No Change
</configuration>namespace WaterDistributionBusiness1.Models
{
public class WaterMeter
{
public int WaterMeterID { get; set; }
public string MeterNumber { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public string MeterSize { get; set; }
}
}
using System.Net;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using WaterDistributionBusiness1.Models;
namespace WaterDistributionBusiness1.Controllers
{
public class WaterMetersController : Controller
{
private List<WaterMeter> WaterMeters = new List<WaterMeter>();
// GET: WaterMeters
public ActionResult Index()
{
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize " +
"FROM WaterMeters " +
"ORDER BY Make;", scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
WaterMeter meter = null;
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows)
{
meter = new WaterMeter()
{
WaterMeterID = int.Parse(drWaterMeter["WaterMeterID"].ToString()),
MeterNumber = drWaterMeter["MeterNumber"].ToString(),
Make = drWaterMeter["Make"].ToString(),
Model = drWaterMeter["Model"].ToString(),
MeterSize = drWaterMeter["MeterSize"].ToString()
};
WaterMeters.Add(meter);
}
}
return View(WaterMeters);
}
// GET: WaterMeters/Details/5
public ActionResult Details(int id)
{
WaterMeter meter = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize " +
"FROM WaterMeters " +
"WHERE WaterMeterID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
if (dsWaterMeters.Tables[0].Rows.Count > 0)
{
meter = new WaterMeter()
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0]["WaterMeterID"].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[0]["MeterNumber"].ToString(),
Make = dsWaterMeters.Tables[0].Rows[0]["Make"].ToString(),
Model = dsWaterMeters.Tables[0].Rows[0]["Model"].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[0]["MeterSize"].ToString()
};
}
}
if (meter == null)
{
return HttpNotFound();
}
return View(meter);
}
// GET: WaterMeters/Create
public ActionResult Create()
{
return View();
}
// POST: WaterMeters/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO WaterMeters(MeterNumber, Make, Model, MeterSize) " +
"VALUES(N'" + collection["MeterNumber"] + "', N'" + collection["Make"] + "', N'" + collection["Model"] + "', N'" +
collection["MeterSize"] + "');",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: WaterMeters/Edit/5
public ActionResult Edit(int id)
{
WaterMeter meter = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize " +
"FROM WaterMeters " +
"WHERE WaterMeterID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
if (dsWaterMeters.Tables[0].Rows.Count > 0)
{
meter = new WaterMeter()
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0]["WaterMeterID"].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[0]["MeterNumber"].ToString(),
Make = dsWaterMeters.Tables[0].Rows[0]["Make"].ToString(),
Model = dsWaterMeters.Tables[0].Rows[0]["Model"].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[0]["MeterSize"].ToString()
};
}
}
if (meter == null)
{
return HttpNotFound();
}
return View(meter);
}
// POST: WaterMeters/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: WaterMeters/Delete/5
public ActionResult Delete(int id)
{
WaterMeter meter = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize " +
"FROM WaterMeters " +
"WHERE WaterMeterID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
if (dsWaterMeters.Tables[0].Rows.Count > 0)
{
meter = new WaterMeter()
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0]["WaterMeterID"].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[0]["MeterNumber"].ToString(),
Make = dsWaterMeters.Tables[0].Rows[0]["Make"].ToString(),
Model = dsWaterMeters.Tables[0].Rows[0]["Model"].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[0]["MeterSize"].ToString()
};
}
}
if (meter == null)
{
return HttpNotFound();
}
return View(meter);
}
// POST: WaterMeters/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}
@model WaterDistributionBusiness1.Models.WaterMeter
@{
ViewBag.Title = "Water Meter Details";
}
<h2 class="bold blue common-font text-center">Water Meter Details</h2>
<hr />
<div>
<dl class="dl-horizontal common-font">
<dt>
@Html.DisplayNameFor(model => model.WaterMeterID)
</dt>
<dd>
@Html.DisplayFor(model => model.WaterMeterID)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MeterNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.MeterNumber)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Make)
</dt>
<dd>
@Html.DisplayFor(model => model.Make)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Model)
</dt>
<dd>
@Html.DisplayFor(model => model.Model)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MeterSize)
</dt>
<dd>
@Html.DisplayFor(model => model.MeterSize)
</dd>
</dl>
</div>
<p class="common-font">
@Html.ActionLink("Edit/Update this Record", "Edit", new { id = Model.WaterMeterID }) ::
@Html.ActionLink("WaterMeters", "Index")
</p>@{
ViewBag.Title = "New Water Meter";
}
<h2 class="bold common-font blue">New Water Meter</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
<label for="mtrNbr" class="control-label col-md-2">Meter #:</label>
<div class="col-md-10">
@Html.TextBox("MeterNumber", null, htmlAttributes: new { @class = "form-control", id = "mtrNbr" })
</div>
</div>
<div class="form-group">
<label for="make" class="control-label col-md-2">Make:</label>
<div class="col-md-10">
@Html.TextBox("Make", null, htmlAttributes: new { @class = "form-control", id = "make" })
</div>
</div>
<div class="form-group">
<label for="model" class="control-label col-md-2">Model:</label>
<div class="col-md-10">
@Html.TextBox("Model", null, htmlAttributes: new { @class = "form-control", id = "model" })
</div>
</div>
<div class="form-group">
<label for="mtrSize" class="control-label col-md-2">Meter Size:</label>
<div class="col-md-10">
@Html.TextBox("MeterSize", null, htmlAttributes: new { @class = "form-control", id = "mtrSize" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Water Meters", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Create Water Meter" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionBusiness1.Models.WaterMeter
@{
ViewBag.Title = "Edit/Update Water Meter";
}
<h2 class=" bold blue common-font">Edit/Update Water Meter</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
@Html.Hidden("WaterMeterID")
<div class="form-group">
@Html.LabelFor(model => model.MeterNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.MeterNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Make, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Make, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Model, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Model, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MeterSize, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.MeterSize, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Water Meters", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Update Water Meter" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionBusiness1.Models.WaterMeter
@{
ViewBag.Title = "Delete Water Meter";
}
<h2 class="bold common-font blue">Delete Water Meter</h2>
<hr />
<h3 class="common-font blue">Are you sure you want to delete this water meter?</h3>
<div>
<dl class="dl-horizontal common-font">
<dt>
@Html.DisplayNameFor(model => model.WaterMeterID)
</dt>
<dd>
@Html.DisplayFor(model => model.WaterMeterID)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MeterNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.MeterNumber)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Make)
</dt>
<dd>
@Html.DisplayFor(model => model.Make)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Model)
</dt>
<dd>
@Html.DisplayFor(model => model.Model)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MeterSize)
</dt>
<dd>
@Html.DisplayFor(model => model.MeterSize)
</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
<input type="submit" value="Delete Water Meter" class="btn btn-primary" /> |
@Html.ActionLink("Water Meters", "Index")
</div>
}
</div>@model IEnumerable<WaterDistributionBusiness1.Models.WaterMeter>
@{
ViewBag.Title = "Water Meters";
}
<h2 class="bold blue common-font text-center">Water Meters</h2>
<hr />
<table class="table table-striped common-font">
<tr>
<th>Water Meter ID</th>
<th>Make</th>
<th>Model</th>
<th>Meter Size</th>
<th>@Html.ActionLink("New Water Meter", "Create")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">
@Html.DisplayFor(modelItem => item.WaterMeterID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Make)
</td>
<td>
@Html.DisplayFor(modelItem => item.Model)
</td>
<td>
@Html.DisplayFor(modelItem => item.MeterSize)
</td>
<td>
@Html.ActionLink("Edit/Update", "Edit", new { id = item.WaterMeterID }) |
@Html.ActionLink("Meter Details", "Details", new { id = item.WaterMeterID }) |
@Html.ActionLink("Delete Water Meter", "Delete", new { id = item.WaterMeterID })
</td>
</tr>
}
</table>| Meter # | Make | Model | Meter Size |
| 293-740 | Breston | S-93749 | 3/4 Inches |
| 820-418 | Vashty Worldwide | DD-3840 | 3/4 Inches |
| 627-425 | Breston | T-39478 | 5/8 Inches |
| 304-861 | Vashty Worldwide | DD-3840 | 3/4 Inches |
| 925-935 | Igawa International | DTT 8802 | 1 Inch |
|
|
||
| Previous | Copyright © 2001-2021, FunctionX | Next |
|
|
||