Fundamentals of Record Selections

Introduction

Data entry is probably the primary user-based operation performed on a database. This is the C in the CRUD acronym. Here are two examples that add a few records to two tables:

CREATE DATABASE RedOakHighSchool;
GO
USE RedOakHighSchool;
GO
CREATE SCHEMA Academics;
GO
CREATE TABLE Academics.Genders
(
	GenderCode	NCHAR(1) NOT NULL,
	Gender		NVARCHAR(24) NOT NULL,
	CONSTRAINT PK_Genders PRIMARY KEY(GenderCode)
);
GO
CREATE TABLE Students
(
	StudentNumber NVARCHAR(10) NOT NULL,
	FirstName	  NVARCHAR(20),
	LastName	  NVARCHAR(20) NOT NULL,
	GenderCode	  NCHAR(1) NOT NULL,
	[Address]	  NVARCHAR(100),
	City		  NVARCHAR(40),
	[State]		  NCHAR(2),
	CONSTRAINT FK_Genders FOREIGN KEY(GenderCode) REFERENCES Academics.Genders(GenderCode),
	CONSTRAINT PK_Students PRIMARY KEY(StudentNumber)
);
GO
INSERT INTO Academics.Genders(GenderCode, Gender)
VALUES(N'M', N'Male'), (N'F', N'Female'), (N'U', N'Unknown');
GO
INSERT INTO Students(StudentNumber, FirstName, LastName, GenderCode, [Address],	City, [State])
VALUES(N'9280534052', N'Oliver',  N'Stone',     N'M', N'10384 Montana Rd',    N'Mill Creek',   N'PA'),
	  (N'5386268480', N'Leslie',  N'Owens',     N'F', N'8644 Cagey str',      N'Wellsville',   N'NY'),
	  (N'2795007493', N'Gregory', N'Simeone',   N'M', N'927 N. Cranston Ave', N'Moorefield',   N'WV'),
	  (N'8374831186', N'Jeanne',  N'Schneider', N'F', N'2938 Lewson Crt',     N'Beaver Falls', N'PA');
GO

The second most routine operation consists of reading records. This is the R in the CRUD approach. In query languages such as the structured query language (SQL) or the LINQ, reading records is referred to as selecting records. You can select all of the records of a table or you can specify some particular records of your interest.

ApplicationPractical Learning: Introducing Data Selection

  1. Start 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 WaterDistributionBusiness1
  4. Click OK
  5. In the New ASP.NET Web Application dialog box, click the MVC icon
  6. Click OK
  7. In the Solution Explorer, right-click App_Data -> Add -> New Item...
  8. In the left frame of the Add New Item dialog box, click Data. In the middle frame, click SQL Server Database. Change the database Name to WaterDistribution
  9. Press Enter
  10. In the Solution Explorer, right-click Content -> Add -> Style Sheet
  11. Type WaterDistribution as the name of the file
  12. Click Add
  13. 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; }
  14. In the Solution Explorer, expand App_Start and double-click BundleConfig.cs
  15. 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"));
            }
        }
    }
  16. In the Solution Explorer, double-click the very bottom Wep.config file to open it
  17. To create a connection string section type the following code:
    • Microsoft SQL Server:
      <?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>
        <system.web>
          <compilation debug="true" targetFramework="4.6.1"/>
          <httpRuntime targetFramework="4.6.1"/>
          <httpModules>
            <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
          </httpModules>
        </system.web>
        <connectionStrings>
          <add name="csWaterDistribution"
               connectionString="Data Source=(local); Database='WaterDistribution'; Integrated Security=True;MultipleActiveResultSets=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
    • Local database:
      <?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>
  18. To prepare the connection to the database, on the main menu, click Debug and click Start Without Debugging
  19. In the Server Explorer, right-click WaterDistribution.mdf and click New Query (if you receive an error, then first expand it to establish the connection)
  20. 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
  21. To execute the code and create the tables, right-click inside the Query window and click Execute
  22. Close the Query window
  23. When asked whether you want to save, click No
  24. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  25. In the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions

    Add Scaffold

  26. Click Add
  27. Type WaterMeters to get WaterMetersController
  28. Click Add
  29. Change the second Create() method as follows:
  30. using System.Web.Mvc;
    using System.Data.SqlClient;
    
    namespace WaterDistributionBusiness1.Controllers
    {
        public class WaterMetersController : Controller
        {
            . . . No Change
    
            // 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();
                }
            }
    
            . . . No Change
        }
    }
  31. Right-click anywhere inside one of the Create() methods and click Add View...
  32. In the Add View dialog box, make sure the View Name text box displays Create. Click Add
  33. Design the form 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>
    }
  34. To execute, on the main menu, click Debug -> Start Without Debugging
  35. Create the following two records:

    Meter # Make Model Meter Size
    293-740 Breston S-93749 3/4 Inches
    820-418 Vashty Worldwide DD-3840 3/4 Inches
  36. Close the browser and return to your programming environment

SELECTing a Column

In the SQL, the primary formula to make (a) selection(s) on a table is:

SELECT column-name(s) FROM what-object;

To select a column, you can replace the column-name(s) placeholder of our formula with the name of the desired column. For example, to get a list of names from a table named Students that contains a column named FirstName, you would execute the following statement:

SELECT FirstName FROM Students;
GO

You can write such a SELECT statement in a Query window and then execute it. You can pass the statement as the command of a SQL connection. This can be done as follows:

@{
    ViewBag.Title = "Exercise";
}

<h2>Red Oak High School - Students</h2>
@{
    using (System.Data.SqlClient.SqlConnection scROSH = new System.Data.SqlClient.SqlConnection("Integrated Security='SSPI'; Initial Catalog='RedOakHighSchool';Data Source='(local)';"))
    {
        System.Data.SqlClient.SqlCommand cmdStudents = new System.Data.SqlClient.SqlCommand("SELECT FirstName FROM Students;", scROSH);

        scROSH.Open();
        cmdStudents.ExecuteNonQuery();

        System.Data.SqlClient.SqlDataAdapter sdaStudents = new System.Data.SqlClient.SqlDataAdapter(cmdStudents);
        System.Data.DataSet dsStudents = new System.Data.DataSet("StudentsSet");

        sdaStudents.Fill(dsStudents);

        <ul>
            @foreach (System.Data.DataRow drCustomer in dsStudents.Tables[0].Rows)
            {
                foreach (System.Data.DataColumn col in dsStudents.Tables[0].Columns)
                {
                    <li>@drCustomer["FirstName"]</li>
                }
        }

        </ul>
    }
}

Qualifying a Column

Qualifying an object consists of indicating its parent. When selecting a column, to indicate the table to which it belongs, precede the name of the column with the name of its table and a period. Here is an example:

SELECT Students.LastName FROM Students;

When you execute the statement, only the column that contains the last names would be selected.

Qualifying a Table

To qualify a table, that is, to indicate the schema to which it belongs, precede the name of the table wtih the name of the schema to which it belongs, and a period. Here is an example:

SELECT Gender FROM Academics.Genders;
GO

Selecting Some Columns

To select more than one column, replace the column-name(s) placeholder of our formula with the names of the desired columns, separating them with commas. The formula to follow is:

SELECT column_1, column_2, . . ., column_n FROM what-object;

For example, to get a list that includes the first name, the last name, the gender, the email address, and the home phone of records from a table called Students, you would create the SQL statement as follows:

SELECT FirstName, LastName, Gender, City, State FROM Students;

You can qualify each column by preceding it with the name of the table followed by the period operator. Here is an example:

@{
    ViewBag.Title = "Exercise";
}

<h2>Red Oak High School - Students</h2>
@{
    using (System.Data.SqlClient.SqlConnection scROSH = new System.Data.SqlClient.SqlConnection("Integrated Security='SSPI'; Initial Catalog='RedOakHighSchool';Data Source='(local)';"))
    {
        System.Data.SqlClient.SqlCommand cmdStudents =
            new System.Data.SqlClient.SqlCommand("SELECT Students.FirstName, " +
                                                 "       Students.LastName, " +
                                                 "       Students.City " +
                                                 "FROM Students;", scROSH);

        scROSH.Open();
        cmdStudents.ExecuteNonQuery();

        System.Data.SqlClient.SqlDataAdapter sdaStudents = new System.Data.SqlClient.SqlDataAdapter(cmdStudents);
        System.Data.DataSet dsStudents = new System.Data.DataSet("StudentsSet");

        sdaStudents.Fill(dsStudents);
        
        <table border="4">
            <tr>
                <td><b>First Name</b></td>
                <td><b>Last Name</b></td>
                <td><b>City</b></td>
            </tr>
        @foreach (System.Data.DataRow drCustomer in dsStudents.Tables[0].Rows)
        {
            <tr>
                <td>@drCustomer["FirstName"]</td>
                <td>@drCustomer["LastName"]</td>
                <td>@drCustomer["City"]</td>
            </tr>    
        }
    </table>
    }
}

You don't have to qualify all columns, you can qualify some and not qualify some others. Here is an example:

SELECT Students.FirstName,
       LastName,
       Students.City
FROM Students;

You can use an alias name for a table by preceding each column with a letter or a word and a period operator, and then entering the name of the table followed by that letter or word. Here is an example:

SELECT std.FirstName, std.LastName, std.City, std.State
FROM Students std;

SELECTing ALL Columns of a Table

To select all columns of a table, in place of the column or the list of columns of a SELECT statement, use the asterisk. For example, to select all records from a table named Students, you can type:

SELECT * FROM Students;

Alternatively, you can precede the asterisk with the ALL keyword. Here is an example:

SELECT ALL * FROM RealEstate.Properties;

Data Selection with ASP.NET MVC

Introduction

To provide a better environment for your users to view the records of your database, you can create friendly webpages that display only selected records. You have many options. You can select the records in a method of a controller class and send those records to a webpage. To prepare the records, you should put them in a collection that implements the IEnumerable<> interface.

Practical LearningPractical Learning: Specifying the Width of a Control

  1. In the Solution Explorer, right-click Models -> Add -> Class...
  2. Type WaterMeter as the name of the file
  3. Click Add
  4. 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; }
        }
    }
  5. Click the WaterMetersController.cs tab to access it
  6. Change the document as follows:
    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;", 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);
            }
    
            . . . No Change
    
            // 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();
                }
            }
    
            . . . No Change
        }
    }

Accessing Data as a Model

As we saw in previous lessons, the most professional way to access data in a view is through a Model object made available by a class derived from Controller. To make this possible, in your controller class, you should create a collection of an IEnumerable<> type. In a view associated with an action method of the controller, declare an @model statement that uses an IEnumerable<> object. Specify the parameter type as a class that represents a record of the table. In the body of the view, access the Model object as it holds a collection of the records of the table.

Practical LearningPractical Learning: Accessing Data as a Model

  1. In the WaterMetersController class, right-click inside the Index() method and click Add View...
  2. In the Add View dialog box, make sure the View Name text box displays Index and press Enter
  3. 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>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @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>
            </tr>
        }
    </table>
    
    <p class="text-center common-font">@Html.ActionLink("New Water Meter", "Create")</p>
  4. To see the result, on the main menu of Microsoft Visual Studio, click Debug -> Start Without Debugging
  5. To execute, on the main menu, click Debug -> Start Without Debugging
  6. Click the New Water Meter button and add the following records:

    Meter # Make Model Meter Size
    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
  7. Close the browser and return to your programming environment

Topics on Selecting Records

Selecting Into a Table

Consider the following Employees table:

CREATE TABLE Employees
(
	EmployeeNumber int unique not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Department nvarchar(50) null,
	EmploymentStatus nvarchar(30),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
      (40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
      (27462, N'Peter',   N'Swanson', N'Corporate', N'Full Time', 22.84),
      (52835, N'Edward',  N'Johansen', N'Information Technology', N'Consultant', 15.50),
      (93075, N'Sharon',  N'Edson', N'Accounting', N'Full Time', 28.74),
      (82718, N'Yanuh',   N'Hadhah', N'Corporate', N'Full Time', 14.86),
      (29174, N'Hamin',   N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO

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

SELECT columns INTO new-table-name FROM existing-table

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

SELECT * INTO CompanyRecipients FROM Employees;
GO

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

SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;
GO

Distinct Field Selection

If you specify a column to select from a table, every record of that column would be selected. This can cause the same value to repeat over and over. Sometimes you want to show each value only once. To get such a result, you can use the DISTINCT keyword before the name of the column in the SELECT statement.

To perform a distinct selection, in most cases, you would get a better result if you select only one column. Still, you can use as many columns as you want.

Using an Alias Name for a Column

If you create a SELECT statement, when you select a column, you must specify its name. The string that displays in the column header is called a caption. As an alternative, if you want, you can display the caption of your choice. This is done by creating an alias for a column.

To create the alias of a column, in your SELECT statement, on the right side of the column name, put an empty space followed by the desired name of the column header. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT CameraNumber Device
FROM CitationDivision.CamerasTickets;
GO

If you want the column header to appear with more than one word, provide the words as a string in single-quotes or between square brackets: [ and ] . Here are examples:

SELECT TicketNumber 'Ticket #',
	   CameraNumber Device,
	   ViolationCategory [Type of Violation],
	   PaymentDueDate [Payment Due Date]
FROM CitationDivision.CamerasTickets;
GO

As an alternative, instead of the empty space between the column name of its alias, use the AS keyword. Here are examples:

SELECT TicketNumber 'Ticket #',
	   CameraNumber Device,
	   VehicleTagNumber AS [Tag Nbr],
	   ViolationCategory [Type of Violation],
	   PaymentDueDate [Payment Due Date],
	   AmountDue AS 'Pmt Amt'
FROM CitationDivision.CamerasTickets;
GO

Of course, you can qualify each column. Here are examples of preceding the names of columns by their parent table:

SELECT Students.FirstName AS [First Name],
       Students.LastName AS [Last Name],
       Students.City AS [City],
Students.State
FROM   Students;

A Combination or Expression of Columns

When formulating a SELECT statement, you can combine some column names in an expression. You can use any of the available operators.

To create an expression in a SELECT statement, type the expression where a column would be used. Here is an example:

SELECT FirstName + N' ' + LastName
FROM   Students;

This type of expression would appear without a caption. To specify the caption of the column, use the alias features we reviewed in the previous section.

The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply a weekly hours value to an hourly salary to get a weekly salary. Here is an example:

SELECT WeeklyHours * HourlySalary
FROM Payroll

Of course, you can add as many expressions as you want in the SELECT operation of a table. You can also create an alias for an expression to give it the desired name. To do this, on the right side of the expression, add an alias as we saw already. Here are examples:

SELECT FirstName + N' ' + LastName AS [Full Name], 
       Address + N', ' + City + N' ' + State AS [Home Address]
FROM   Students

The Assignment Operator

If you just create a regular expression using arithmetic operators, the new column would not have a name. The SQL allows you to specify a different name for any column during data selection or a name for an expression. This is done using the assignment operator "=".

To change the name of a column during data selection, in your SQL statement, on the right side of SELECT, type the desired name, followed by the assignment operator, followed by the actual name of the column. If the name you want to use is in one word, simply use it. If the name is in more than one word, include it either in single-quotes or between [ and ]. Here are examples:

SELECT TicketNumber 'Ticket #',
	   CameraNumber Device,
	   VehicleTagNumber AS [Tag Nbr],
	   Period = ViolationPeriod,
	   ViolationCategory [Type of Violation],
	   'Camera Position' = ViolationLocation,
	   PaymentDueDate [Payment Due Date],
	   AmountDue AS 'Pmt Amt'
FROM CitationDivision.CamerasTickets;
GO

WHEN a Column's Value Meets a Criterion

Consider the following table:

CREATE DATABASE DepartmentStore;
GO
USE DepartmentStore;
GO
CREATE TABLE Employees
(
    FirstName	 nvarchar(20),
    MI		     nchar(2), 
    LastName	 nvarchar(20), 
    HourlySalary smallmoney, 
    [Status]	 tinyint
);
GO
INSERT INTO Employees 
VALUES(N'Christopher', N'L', N'Larsen', 14.50, 3), 
      (N'Henry', N'C', N'Jonathan', 12.85, 1), 
      (N'Lance', N'A', N'Seagal', 16.95, 1);
GO
INSERT INTO Employees(FirstName, LastName, Status) 
VALUES(N'Roberta', N'Ortez', 1); 
GO
INSERT INTO Employees(FirstName, LastName, HourlySalary) 
VALUES(N'Paul', N'Swanson', 10.90); 
GO
INSERT INTO Employees VALUES(N'Kristofer', N'L', N'Michaels', 12.85, 2); 
GO
INSERT INTO Employees VALUES(N'Ivan', N'N', N'Sanders', 15.00, 2), 
							(N'David', N'P', N'Monahan', 13.05, 1);
GO

When writing your SELECT statement, the value of a column may not be clear to the user. Consider the Employment column in the following query:

<!DOCTYPE html>
<html>
<head>
<title>Department Store - Employees</title>
</head>
<body>
<div align="center">
    <h1>Department Store - Employees</h1>
    
    @{
        using(System.Data.SqlClient.SqlConnection scDepartmentStores =
                new System.Data.SqlClient.SqlConnection("Integrated Security='True';Initial Catalog='DepartmentStore';Data Source=(Local)"))
        {
            string strEmployees = "SELECT FirstName, MI, LastName, HourlySalary, [Status] " +
                                  "FROM Employees;";

            System.Data.SqlClient.SqlCommand cmdEmployees = new System.Data.SqlClient.SqlCommand(strEmployees, scDepartmentStores);

            scDepartmentStores.Open();

            cmdEmployees.ExecuteNonQuery();

            System.Data.SqlClient.SqlDataAdapter sdaEmployees = new System.Data.SqlClient.SqlDataAdapter(cmdEmployees);


            System.Data.DataSet dsEmployees = new System.Data.DataSet("EmployeesSet");
            sdaEmployees.Fill(dsEmployees);

            <table border="6">
                <tr style="font-weight: 600; background-color: navy; color: lightcyan">
                    <td>First Name</td>
                    <td style="text-align: center">MI</td>
                    <td>Last Name</td>
                    <td style="text-align: right">Hourly Salary</td>
                    <td style="text-align: center">Employment Status</td>
                </tr>
            @for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
            {
                System.Data.DataRow drProperty = dsEmployees.Tables[0].Rows[i];
                if (i % 2 == 0)
                {
                    <tr style="background-color: aliceblue; color: navy;">
                        <td>@drProperty[0]</td>
                        <td style="text-align: center">@drProperty[1]</td>
                        <td>@drProperty[2]</td>
                        <td style="text-align: right">@drProperty[3]</td>
                        <td style="text-align: center;">@drProperty[4]</td>
                    </tr>
                }
                else
                {
                    <tr style="background-color: dodgerblue; color: yellow;">
                        <td>@drProperty[0]</td>
                        <td style="text-align: center">@drProperty[1]</td>
                        <td>@drProperty[2]</td>
                        <td style="text-align: right">@drProperty[3]</td>
                        <td style="text-align: center;">@drProperty[4]</td>
                    </tr>
                }
            }
            </table>
        }
    }
</div>
</body>
</html>

This would produce:

Sets

If the value of a column is not very clear, to let you provide some type of explanation to the user, Transact-SQL provides a conditional statement that uses a combination of CASE and WHEN keywords. It roughly resembles the switch statement of the C-based and Visual Basic languages. The formula to follow is:

CASE proposition
    WHEN expression_1 THEN result_1
    WHEN expression_2 THEN result_2
    . . .
    WHEN expression_n THEN result_n
END

You can first apply an expression to the CASE keywork to consider a value. The expression can also be the name of a column of a table. The CASE statement ends with an END keyword. The section between the CASE line and the END keyword is the body of the CASE statement.

In the body of the CASE statement, create a number of statements that start with a WHEN expression followed by a THEN expression. Each WHEN expression considers a possible value of the CASE expression. If the value is found, deal with it in a THEN expression. In the same way, create as many WHEN expression_n THEN result_n expression as you need.

Here is an example of creating and using a CASE statement:

<!DOCTYPE html>
<html>
<head>
<title>Department Store - Employees</title>
</head>
<body>
<div align="center">
    <h1>Department Store - Employees</h1>
    
    @{
        using(System.Data.SqlClient.SqlConnection scDepartmentStores =
                new System.Data.SqlClient.SqlConnection("Integrated Security='True';Initial Catalog='DepartmentStore';Data Source=(Local)"))
        {
            string strEmployees = "SELECT FirstName, MI, LastName, " +
                                  "       HourlySalary, " +
                                  "[Status] = " +
                                  "CASE Status " +
                                  "    WHEN 1 THEN N'Full-Time' " +
                                  "    WHEN 2 THEN N'Part-Time' " +
                                  "END " +
                                  "FROM Employees;";

            System.Data.SqlClient.SqlCommand cmdEmployees = new System.Data.SqlClient.SqlCommand(strEmployees, scDepartmentStores);

            scDepartmentStores.Open();

            cmdEmployees.ExecuteNonQuery();

            System.Data.SqlClient.SqlDataAdapter sdaEmployees = new System.Data.SqlClient.SqlDataAdapter(cmdEmployees);

            System.Data.DataSet dsEmployees = new System.Data.DataSet("EmployeesSet");
            sdaEmployees.Fill(dsEmployees);

            <table border="6">
                <tr style="font-weight: 600; background-color: navy; color: lightcyan">
                    <td>First Name</td>
                    <td style="text-align: center">MI</td>
                    <td>Last Name</td>
                    <td style="text-align: right">Hourly Salary</td>
                    <td style="text-align: center">Employment Status</td>
                </tr>
            @for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
            {
                System.Data.DataRow drProperty = dsEmployees.Tables[0].Rows[i];
                if (i % 2 == 0)
                {
                    <tr style="background-color: aliceblue; color: navy;">
                        <td>@drProperty[0]</td>
                        <td style="text-align: center">@drProperty[1]</td>
                        <td>@drProperty[2]</td>
                        <td style="text-align: right">@drProperty[3]</td>
                        <td style="text-align: center;">@drProperty[4]</td>
                    </tr>
                }
                else
                {
                    <tr style="background-color: dodgerblue; color: yellow;">
                        <td>@drProperty[0]</td>
                        <td style="text-align: center">@drProperty[1]</td>
                        <td>@drProperty[2]</td>
                        <td style="text-align: right">@drProperty[3]</td>
                        <td style="text-align: center;">@drProperty[4]</td>
                    </tr>
                }
            }
            </table>
        }
    }
</div>
</body>
</html>

This would produce:

Employees

If none of the WHEN expression_n expressions matches a CASE value, you can create a last statement using the ELSE keyword. The formula to follow would become:

CASE proposition
    WHEN expression_1 THEN result_1
    WHEN expression_2 THEN result_2
    . . .
    WHEN expression_n THEN result_n
    ELSE else_result
END

Here is an example of using it:

SELECT FirstName, MI, LastName, 
       HourlySalary, 
       [Status] = 
	    CASE Status
		WHEN 1 THEN N'Full-Time' 
		WHEN 2 THEN N'Part-Time' 
		ELSE N'Contractor/Seasonal' 
	    END 
FROM Employees;

This would produce:

Employees

Data Selection and ASP.NET MVC Web Controls

Data Selection and Labels

To display a value of a SELECT statement in a label, call the Html.Label() method. Pass the first argument from a value of an indexer of a data reader. Here are examples:

@{
    ViewBag.Title = "Gas Meters";
}

<h2>Gas Meters</h2>

@using (Html.BeginForm())
{
    using (System.Data.SqlClient.SqlConnection scGasDistribution = new System.Data.SqlClient.SqlConnection("Data Source=(local); Database='GasDistribution1'; Integrated Security=True"))
    {
        System.Data.SqlClient.SqlCommand cmdGasMeters = 
            new System.Data.SqlClient.SqlCommand("SELECT MeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue FROM GasMeters",
            scGasDistribution);

        scGasDistribution.Open();

        System.Data.SqlClient.SqlDataReader sdrGasMeters = cmdGasMeters.ExecuteReader();

    <table border="6">
        <tr>
            <td style="text-align: center; font-weight: 600">Meter ID</td>
            <td><b>Meter #</b></td>
            <td><b>Make</b></td>
            <td><b>Model</b></td>
            <td><b>MeterSize</b></td>
            <td><b>DateLastUpdate</b></td>
            <td><b>CounterValue</b></td>
        </tr>
        @while (sdrGasMeters.Read())
        {
            <tr>
                <td>@Html.Label(@sdrGasMeters[0].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[1].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[2].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[3].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[4].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[5].ToString())</td>
                <td>@Html.Label(@sdrGasMeters[6].ToString())</td>
            </tr>
        }
    </table>
    }
}

Text Boxes

After getting a value from a SELECT statement, to display that value in a text box, call the Html.TextBox() method and pass the second argument as an indexed value of a data reader.

Check Boxes

A check box is used to display a true or false value. If you are using a data reader to get the value of a column that has Boolean values, parse that value before applying it to the check box.

Combo Boxes and List Boxes

A combo box, also called a drop-down-list, is a list-based control that can show one, some, or all of the values of a column of a table. To put those values in the control, you can use a data reader to get each value and add it to the combo box.

If the column has repeating values and you use the same technique, the combo box also would have repeating values. In most cases, this would not be professional. To make sure the combo box displays unique values, you have various alternatives. You can use a loop to check whether the combo box has a certain value already and decide whether to add or ignore the value. An alternative is to apply the DISTINCT keyword to the SELECT statement.

Radio Buttons

Radio buttons work as a mutually-exclusive group. The user can select one item at a time. As you may know already, to create a radio button, you can call the Html.RadioButton() method and pass at least two arguments. One issue is that, when creating a group of radio buttons, all of the radio buttons in the same group must use the same name, which is the first argument of their method. This means that all radio buttons in the group must be passed the same first argument. The second argument should be different for each radio button and it would hold the value of the control.

Practical LearningPractical Learning: Ending the Lesson


Previous Copyright © 2001-2019, FunctionX Next