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 LearningPractical Learning: Introducing Sorting Records

  1. Staat Microsoft Visual Studio
  2. On the main menu, click File -> New -> Project...
  3. In the New Project dialog box, make sure ASP.NET Web Application (.NET Framework) is selected in the middle list.
    Change the Name of the project to AltairRealtors1
  4. Click OK
  5. In the New ASP.NET Web Application dialog box, click the Empty icon
  6. Click OK
  7. If you want to use Microsoft SQL Server to create a database
    1. Start Microsoft SQL Server and login/connect to the database server
    2. In the Object Explorer, right-click the computer name and click New Query
    3. Type the following code:
      USE master;
      GO
      CREATE DATABASE AltairRealtors1;
      GO
      USE AltairRealtors1;
      GO
    4. To execute, right-click inside the Code Editor and click Execute
    5. Click inside the Code Editor, press Ctrl + A to select everything, and press Delete
  8. If you are using a local database
    1. In the Solution Explorer of Microsoft Visual Studio, right-click AltairRealtors1 -> Add -> Add ASP.NET Folder -> App_Data
    2. In the Solution Explorer, right-click App_Data -> Add -> SQL Server Database
    3. Type AltairRealtors1 as the name of the database
    4. Click OK
    5. In the Solution Explorer, under App_Data, right-click AltairRealtors1.mdf and click Open
    6. In the Server Explorer, right-click AltairRealtors1.mdf and click New Query
  9. In both cases, type the following code:
    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
  10. To execute the code and create the tables, right-click inside the Query window and click Execute
  11. Close the Query window
  12. If asked whether you want to save, click No
  13. In the Solution Explorer, double-click Wep.config to open it
  14. To specify a connecction string
    • If you created your database in Microsoft SQL Server, create a connection string section as follows:
      <?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=\&quot;Web\&quot; /optionInfer+"/>
          </compilers>
        </system.codedom>
        <connectionStrings>
          <add name="csAltairRealtors" connectionString="Data Source=(local);Initial Catalog=AltairRealtors1;Integrated Security=True" providerName="System.Data.SqlClient" />
        </connectionStrings>
      </configuration>
    • If you created a local database, create a connection string as follows:
      <?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=\&quot;Web\&quot; /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>
  15. To start a web page, in the Solution Explorer, right-click AltairRealtors1 -> Add -> New Item...
  16. In the left frame, under Visual C#, expand the Web node and click Razor
  17. In the middle list, click Web Page (Razor v3)
  18. Change the name to Index
  19. Click Add
  20. Change the code as follows:
    <!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>
  21. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    Introducing Interfaces

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 LearningPractical Learning: Comparing for a Lesser Value

  1. Return to your programming environment and change the code as follows:
    . . . 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>
  2. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    Comparison for a Lesser Value

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 LearningPractical Learning: Comparing for a Lesser String Value

  1. Return to your programming environment and change the code as follows:
    . . . 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>
  2. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    The Comparison for Lesser Values and String-Based Columns

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 LearningPractical Learning: Comparing for a Greater Value

  1. Return to your programming environment and change the code as follows:
    . . . 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>
  2. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    The Comparison for Greater Value

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 LearningPractical Learning: Comparing for Greater Boolean Value

  1. Return to your programmint environment and change the document as follows:
<!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 Comparison for Greater Value

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:

WHERE Condition

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:

Negating a Condition

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:

Where a Column IS NULL

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:

Where a Column IS NULL

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

Employees

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)):

Employees

If you didn't close the browser, return to it and refresh it; otherwise, return to Microsoft Visual Studio and execute the code again:

Where a Column IS NULL

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

Disposing of an Object

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:

Boolean Operations on Numeric Fields

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:

Not Showing a Column

If necessary, you can arrange the records based on any column of your choice.

ApplicationPractical Learning: Filtering Records

  1. On the main menu of Microsoft Visual Studio, click File -> New -> Project...
  2. In the New Project dialog box, make sure ASP.NET Web Application (.NET Framework) is selected in the middle list.
    Change the Name of the project to WaterDistributionBusiness3
  3. Click OK
  4. In the New ASP.NET Web Application dialog box, click the MVC icon
  5. Click OK
  6. In the Solution Explorer, right-click App_Data -> Add -> SQL Server Database
  7. Type WaterDistribution as the name of the database
  8. Press Enter
  9. In the Solution Explorer, under App_Data, right-click WaterDistribution.mdf and click Open
  10. In the Server Explorer, right-click WaterDistribution.mdf and click New Query
  11. Type the following code:
    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
  12. To execute the code and create the tables, right-click inside the Query window and click Execute
  13. Close the Query window
  14. When asked whether you want to save, click No
  15. In the Solution Explorer, right-click Content -> Add -> Style Sheet
  16. Type WaterDistribution as the name of the file
  17. Click Add
  18. Change the document as follows:
    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; }
  19. In the Solution Explorer, expand App_Start and double-click BundleConfig.cs
  20. Change the document as follows:
    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"));
            }
        }
    }
  21. In the Solution Explorer, double-click the very bottom Wep.config file to open it
  22. To create a connection string section, type the following code:
    <?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>
  23. To prepare the connection to the database, on the main menu, click Debug and click Start Without Debugging
  24. To execute the code and create the tables, right-click inside the Query window and click Execute
  25. Close the Query window
  26. When asked whether you want to save, click No
  27. In the Solution Explorer, right-click Models -> Add -> Class...
  28. Type WaterMeter as the name of the file
  29. Click Add
  30. Change the class as follows:
    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; }
        }
    }
  31. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  32. In the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions

    Add Scaffold

  33. Click Add
  34. Type WaterMeters to get WaterMetersController
  35. Click Add
  36. Change the document as follows:
  37. 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();
                }
            }
        }
    }
  38. Right-click Details() and click Add View...
  39. In the Add View dialog box, make sure the View Name text box displays Create. Click Add
  40. Design the form as follows:
    @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>
  41. Click the WaterMetersController.cs tab to access it
  42. Right-click anywhere inside one of the Create() methods and click Add View...
  43. In the Add View dialog box, make sure the View Name text box displays Create. Click Add
  44. Change the document as follows:
    @{
        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>
    }
  45. Click the WaterMetersController.cs tab to access it
  46. Right-click inside one of the Edit() methods and click Add View...
  47. In the Add View dialog box, make sure the View Name text box displays Create. Click Add
  48. Change the document as follows:
    @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>
    }
  49. Click the WaterMetersController.cs tab to access it
  50. Right-click inside one of the Delete() methods and click Add View...
  51. In the Add View dialog box, make sure the View Name text box displays Create. Click Add
  52. Change the document as follows:
    @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>
  53. Click the WaterMetersController.cs tab to access it
  54. Right-click inside the Index() method and click Add View...
  55. In the Add View dialog box, make sure the View Name text box displays Index and press Enter
  56. Change the document as follows:
    @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>
  57. To execute, on the main menu, click Debug -> Start Without Debugging
  58. Create the following records:

    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
  59. Close the browser and return to your programming environment
  60. Close your programming environment

Previous Copyright © 2001-2019, FunctionX Next