Fundamentals of Columns Selections

SELECTing a Column

Remember that the primary formula to make (a) selection(s) on a table is:

SELECT what-field(s) FROM what-object;

To select a column, you can replace the what-field(s) placeholder of our formula with the name of the desired column. For example, to get a list of last names of students of a table, you would execute the following statement:

SELECT LastName FROM Students;
GO

If the table was created in a particular schema, make sure to qualify the name of the table with its schema. Here is an example:

SELECT LastName FROM Registration.Students;
GO

You can also qualify a column by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:

SELECT Students.LastName FROM Registration.Students;

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

Practical LearningPractical Learning: Introducing Records Selections

  1. Start Microsoft Visual Studio
  2. On the main menu, click File -> New -> Project...
  3. In the New Project dialog box, click ASP.NET Application (.NET Framework).
    Change the project Name to TrafficTicketsManagement1
  4. Click OK
  5. In the New ASP.NET Web Application dialog box, click the MVC icon and click OK
  6. In the Solution Explorer, expand Views and expand Shared
  7. Double-click _Layout.cshtml to open it
  8. Change the document as follows:
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - Traffic Tickets Management</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    </head>
    <body>
        <div class="navbar navbar-inverse navbar-fixed-top">
            <div class="container">
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                    </button>
                    @Html.ActionLink("Traffic Tickets Management", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
                </div>
                <div class="navbar-collapse collapse">
                    <ul class="nav navbar-nav">
                        <li>@Html.ActionLink("Emergency/911", "Index", "Home")</li>
                        <li>@Html.ActionLink("Government", "Index", "Home")</li>
                        <li>@Html.ActionLink("Public Parks", "Index", "Home")</li>
                        <li>@Html.ActionLink("Employment/Careers", "Index", "Home")</li>
                        <li>@Html.ActionLink("About", "About", "Home")</li>
                        <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
                    </ul>
                </div>
            </div>
        </div>
        <div class="container body-content">
            @RenderBody()
            <hr />
            <footer>
                <p style="text-align: center">&copy; @DateTime.Now.Year - Traffic Tickets Management</p>
            </footer>
        </div>
    
        @Scripts.Render("~/bundles/jquery")
        @Scripts.Render("~/bundles/bootstrap")
        @RenderSection("scripts", required: false)
    </body>
    </html>
  9. In the Solution Explorer, below Views and Home, double-click Index.cshtml:
  10. Change the document as follows:
    @{
        ViewBag.Title = "Welcome";
    }
    
    <div class="row">
        <div class="col-md-4">
            <h2>Constituents</h2>
            <p>Here, you will find out about available services and opportunities. These include 
               employment assistance and financial services.</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>County Board</h2>
            <p>Get to know the county executives. Read their biographies. Don't hesitate to reach 
            out to them. And make suggestions!</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>Police/Safety</h2>
            <p>Our constituents safety is our top priority. Inform us about your concerns in energy 
            (water, electricity, transportation, communication, etc).</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
        </div>
    </div>
    
    <div class="jumbotron">
        <h1>Solomon County</h1>
        <p class="lead">Our county is here to serve you, assist you, and lead you. We have gathered
            various types of community, social, personal, and financial resources.</p>
    </div>
  11. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    Fundamentals of Data Selection

  12. To create a database
    • Microsoft SQL Server
      1. Start Microsoft SQL Server Management Studio
      2. Log in/Connect to the server
      3. In the Object Explorer, right-click the name of the computer and click New Query
      4. In the empty document, type the following code:
        USE master;
        GO
        CREATE DATABASE TrafficTicketSystem1;
        GO
        USE TrafficTicketSystem1;
        GO
      5. To execute, on the toolbar, click the Execute button Execute
      6. Press Ctrl + A to select everything and press Delete
      7. Return to Microsoft Visual Studio and, in the Solution Explorer, double-click the bottom web.config file
      8. In the web.config file, add a connection string node 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=301880
          -->
        <configuration>
          . . .
          <connectionStrings>
            <add name="csTrafficTicketsManagement"
                 connectionString="Data Source=(local);Database='TrafficTicketSystem1';Integrated Security=True"
                 providerName="System.Data.SqlClient" />
          </connectionStrings>
          . . .
    • Local Database
      1. In Microsoft Visual Studio, in the Solution Explorer, right-click App_Data -> Add -> New Item...
      2. In the left frame of the Add New Item dialog box, under Visual C#, click Data and, in the middle list, click SQL Server Database
      3. Change the Name to TrafficTicketSystem1
      4. Click Add
      5. In the Solution Explorer, double-click the bottom web.config file
      6. In the web.config file, add a connection string node 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=301880
          -->
        <configuration>
          . . .
          <connectionStrings>
            <add name="csTrafficTicketsManagement"
                 connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\TrafficTicketSystem1.mdf;Initial Catalog=TrafficTicketSystem1;Integrated Security=True"
                 providerName="System.Data.SqlClient" />
          </connectionStrings>
          . . .
      7. In the Solution Explorer, under App_Data, right-click TrafficTicketSystem1.mdf -> Open
      8. In the Server Explorer, right-click TrafficTicketSystem1.mdf and click New Query
  13. In both cases, in the empty Query window, type the following code:
    CREATE SCHEMA CitationDivision;
    GO
    CREATE TABLE CitationDivision.ViolationsCategoryies
    (
        ViolationCategory NVARCHAR(50)
    );
    GO
    CREATE TABLE CitationDivision.CamerasTickets
    (
        TicketNumber      INT IDENTITY(100001, 1),
        CameraNumber      NCHAR(10)     NOT NULL,
        VehicleTagNumber  NVARCHAR(20)  NOT NULL,
        ViolationDate	    DATE      NOT NULL,
        ViolationTime		TIME      NOT NULL,
        ViolationCategory NVARCHAR(50),
        ViolationLocation NVARCHAR(50)  NOT NULL,
        MediaTransferDate DATETIME,
        PhotoAvailable    BIT,
        VideoAvailable    BIT,
        PaymentDueDate    DATE,
        AmountDue		    SMALLINT
    );
    GO
    INSERT INTO CitationDivision.ViolationsCategoryies VALUES(N'Speed'), (N'Red Light'), (N'Stop Sign'), (N'Reckless Driving'), (N'Illegal U-Turn'), (N'Illegal Right-Turn'), (N'Holding Ellectro-Device While Derving'), (N'Leaving Scene After Accident');
    GO
    
    INSERT INTO CitationDivision.CamerasTickets(CameraNumber, VehicleTagNumber, ViolationDate, ViolationTime, ViolationCategory, ViolationLocation, MediaTransferDate, PhotoAvailable, VideoAvailable, PaymentDueDate, AmountDue)
    VALUES(N'QGD-309586', N'JHK WRT',  N'08-31-2018',		 N'14:47:15',	 N'Speed',				N'Holland Str',						N'10-04-2018 10:05:25',		 1, 0, N'11-06-2018',  85),
          (N'BKR-880248', N'8DT9403',  N'06-01-2017',		 N'01:16:44 AM', N'Red Light',			N'Woodson Rd and Cissera Ave',		N'06/22/17 09:42:55 AM',	 0, 1, N'08-05-2017',  75),
          (N'LGU-602049', N'BPT-3095', N'05/27/2018',		 N'06:18:05 AM', N'Stop Sign',			N'Sommerset Rd and Clarenton Blvd',	N'13-June-2018 20:12:44',	 1, 1, N'08/02/2018',  60),
          (N'ANR-928374', N'M-280468', N'07-15-2018',		 N'13:47:27',	 N'Red Light',			N'Samson Ave and Jameson Str',		N'16-Jul-2018 11:32:38 AM',  1, 1, N'09-16-2018', 125),
          (N'QGD-309586', N'850684',   N'21-September-2018', N'02:18:13 AM', N'Speed',				N'Holland Str',						N'10-04-2018 10:08:12',		 1, 1, N'08-Nov-18',   75),
          (N'PTH-927947', N'M-280468', N'07-15-2018',		 N'13:52:29', 	 N'Speed',				N'Samson Ave and Eastern Str',		N'05-August-18 14:17:09',	 1, 0, N'09-16-2018',  75),
          (N'BKR-880248', N'GHL-9283', N'06-01-2017',		 N'00:48:17 AM', N'Illegal Right-Turn', N'Woodson Rd and Cissera Ave',		N'06/22/17 10:17:42 AM',	 0, 1, N'08-05-2017',  60),
          (N'BKR-880248', N'928497',   N'06-01-2017',		 N'02:24:39 AM', N'Illegal Right-Turn', N'Woodson Rd and Cissera Ave',		N'06/22/17 10:20:07',		 1, 1, N'08-05-2017',  60),
          (N'LDG-174048', N'KKL614',   N'04/14/2018',		 N'16:27:01',	 N'Speed',              N'Rte 728 Sumnerville',				N'04-17-2018 18:15:22',		 1, 0, N'04-28-2018',  75),
          (N'QGD-309586', N'KGD 2095', N'21-SEP-2018',		 N'05:35:19 AM', N'Speed',				N'Holland Str',						N'10-04-2018 10:22:25',		 1, 0, N'11-06-2018',  85),
          (N'WJN-294075', N'M-280468', N'10-31-2018',		 N'10:14:25',	 N'Reckless Driving',	N'Manassa Blvd',					N'11-Nov-2018 22:46:07',	 0, 1, N'09-16-2018',  40),
          (N'PTH-927947', N'KSD4LKE',  N'07-15-2018',		 N'13:58:06',	 N'Speed',				N'Samson Ave and Eastern Str',		N'05-August-18 04:24:25 PM', 0, 1, N'09-16-2018',  75);
    GO
  14. To execute, on the toolbar of the SQL Editor window, click the the Execute button
  15. Close the Query window
  16. When asked whether you want to save, click No
  17. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  18. In the Add Scaffold dialog box, click MVC 5 Controller - Empty and click Add
  19. Type TicketsProcessing to get TicketsProcessingController
  20. Click Add
  21. Change the document as follows:
    using System.Web.Mvc;
    
    namespace TrafficTicketsManagement1.Controllers
    {
        public class TicketsProcessingController : Controller
        {
            // GET: TicketsProcessing
            public ActionResult Index()
            {
                return View();
            }
    
            // GET: TicketsProcessing/TicketsSummary
            public ActionResult TicketsSummary()
            {
                return View();
            }
        }
    }
  22. In the document, right-click TicketsSummary() and click Add View...
  23. In the dialog box, make sure the text box displays TicketsSummary and click Add

Selecting Some Columns

As opposed to selecting all fields, you can select one particular column or a few columns. To do this, you can replace the what-field(s) factor in our formula with the names of the desired columns, separating them with commas column. 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;

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

SELECT Students.FirstName,
       Students.LastName,
       Students.DateofBirth,
       Students.Gender
FROM Students;

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.DateOfBirth,
       Gender
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.DateOfBirth, std.Gender
FROM Students std;

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. If you are working in the Table window, the name of a column would display as the column header in the Results section. 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 class="codered">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 the 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.DateOfBirth AS [Date of Birth],
       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 in the results section of the Table window. To specify the caption of the column, use the alias features we reviewed in the previous section. Here is an example:

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 + N' ' + ZIPCode AS [Home Address], 
       EmrgName + N' ' + EmrgPhone AS [Emergency Contact]
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 bew 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# language. 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 statement 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 Web Controls

Data Selection and Labels

In the previous lesson, we saw how to display values using a SQL data reader. Text-based controls are the prime candidate for showing the value of a column of a table. A label can be used to display static text that the user cannot change.

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

@using (Html.BeginForm())
{
    using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection("Data Source=(local); Database='WaterDistribution'; Integrated Security=True))
    {
        string strTrafficSystem = "SELECT TicketNumber, CameraNumber, ViolationCategory, VehicleTagNumber, " +
                                  "       ServiceVehicle, DateCitationMailed, ViolationPeriod, ViolationLocation, " +
                                  "       PaymentDueDate, AmountDue " +
                                  "FROM CitationDivision.CamerasTickets;";

        System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);

        scTrafficSystem.Open();

        System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();

        <table border="6">
            <tr>
                <td style="text-align: center; font-weight: 600">Ticket #</td>
                <td><b>Camera #</b></td>
                <td><b>Violation</b></td>
                <td style="text-align: center; font-weight: 600">Tag Number</td>
                <td style="text-align: center; font-weight: 600">Service Vehicle?</td>
                <td style="text-align: center; font-weight: 600"><b>Date Citation Mailed</td>
                <td><b>Violation Date/Time</b></td>
                <td><b>Location</b></td>
                <td style="text-align: center; font-weight: 600">Pmt Due Date</td>
                <td style="text-align: right; font-weight: 600">Amount Due</td>
            </tr>
            @while (sdrTrafficSystem.Read())
            {
                <tr>
                    <td>@Html.Label(@sdrTrafficSystem[0].ToString())</td>
                    <td>@Html.Label(@sdrTrafficSystem[1].ToString())</td>
                    <td>@Html.Label(@sdrTrafficSystem[2].ToString())</td>
                    <td style="text-align: center">@Html.Label(@sdrTrafficSystem[3].ToString())</td>
                    <td style="text-align: center">@Html.Label(@sdrTrafficSystem[4].ToString())</td>
                    <td style="text-align: center">@Html.Label(DateTime.Parse(@sdrTrafficSystem[5].ToString()).ToShortDateString())</td>
                    <td>@Html.Label(@sdrTrafficSystem[6].ToString())</td>
                    <td>@Html.Label(@sdrTrafficSystem[7].ToString())</td>
                    <td style="text-align: center">@Html.Label(DateTime.Parse(@sdrTrafficSystem[8].ToString()).ToShortDateString())</td>
                    <td style="text-align: right">@Html.Label(@sdrTrafficSystem[9].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.

Practical LearningPractical Learning: Displaying Records in Check Boxes

  1. Change the TicketsSummary.cshtml document as follows:
    @{
        ViewBag.Title = "Tickets Summary";
    }
    
    <h2 class="text-center">Tickets Summary</h2>
    
    @using (Html.BeginForm())
    {
        using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
        {
            string strTrafficSystem = "SELECT TicketNumber, CameraNumber, VehicleTagNumber, ViolationDate, ViolationTime, ViolationCategory, " +
                                      "       ViolationLocation, MediaTransferDate, PhotoAvailable, VideoAvailable, " +
                                      "       PaymentDueDate, AmountDue " +
                                      "FROM CitationDivision.CamerasTickets;";
    
            System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
    
            scTrafficSystem.Open();
    
            System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
    
            bool photoIsAvailable = false;
            bool videoIsAvailable = false;
    
        <table border="6">
            <tr style="background-color: #4f0505; color: antiquewhite;">
                <td style="text-align: center; font-weight: 600">Ticket #</td>
                <td><b>Camera #</b></td>
                <td style="text-align: center; font-weight: 600">Tag Number</td>
                <td><b>Violation Date</b></td>
                <td style="text-align: center; font-weight: 600">Violation Time</td>
                <td style="text-align: center; font-weight: 600">Type of Violation</td>
                <td><b>Location</b></td>
                <td><b>Media Transfer Date/Time</b></td>
                <td><b>Photo Available</b></td>
                <td><b>Video Available</b></td>
                <td style="text-align: center; font-weight: 600">Pmt Due Date</td>
                <td style="text-align: center; font-weight: 600">Amount Due</td>
            </tr>
            @while (sdrTrafficSystem.Read())
            {
                <tr style="background-color: ivory; color:  black;">
                    <td>@sdrTrafficSystem[0].ToString()</td>
                    <td>@sdrTrafficSystem[1].ToString()</td>
                    <td>@sdrTrafficSystem[2].ToString()</td>
                    <td style="text-align: center">@DateTime.Parse(sdrTrafficSystem[3].ToString()).ToShortDateString()</td>
                    <td style="text-align: center">@sdrTrafficSystem[4].ToString()</td>
                    <td>@sdrTrafficSystem[5].ToString()</td>
                    <td style="text-align: center">@sdrTrafficSystem[6].ToString()</td>
                    <td style="text-align: center">@sdrTrafficSystem[7].ToString()</td>
                    @if (@sdrTrafficSystem[8].ToString().Equals("True"))
                    {
                        photoIsAvailable = true;
                    }
                    else
                    {
                        photoIsAvailable = false;
                    }
                    <td style="text-align: center">@Html.CheckBox("PhotoAvailable", @photoIsAvailable)</td>
                    @if (@sdrTrafficSystem[9].ToString().Equals("True"))
                    {
                        videoIsAvailable = true;
                    }
                    else
                    {
                        videoIsAvailable = false;
                    }
                    <td style="text-align: center">@Html.CheckBox("VideoAvailable", videoIsAvailable)</td>
                    <td style="text-align: center">@DateTime.Parse(@sdrTrafficSystem[10].ToString()).ToShortDateString()</td>
                    <td style="text-align: center">@sdrTrafficSystem[11].ToString()</td>
                </tr>
            }
        </table>
        }
    }
  2. To execute, on the main menu, click Debug -> Start Without Debugging:

  3. Close the browser and return to your programming environment

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.

Practical LearningPractical Learning: Using a Combo Box for Data Selection

  1. In the Solution Explorer, under Controllers, double-click TicketsProcessingController.cs to access it
  2. Change the document as follows:
    using System;
    using System.Web.Mvc;
    using System.Configuration;
    using System.Data.SqlClient;
    
    namespace CountyPolice2.Controllers
    {
        public class TicketsProcessingController : Controller
        {
            // GET: TicketsProcessing
            public ActionResult Index()
            {
                return View();
            }
    
            // GET: TicketsProcessing/TicketsSummary
            public ActionResult TicketsSummary()
            {
                return View();
            }
    
            // GET: TicketsProcessing/IssueTicket
            public ActionResult IssueTicket()
            {
                return View();
            }
    
            // GET: TicketsProcessing/SaveTicket
            public ActionResult SaveTicket(string CameraNumber, string VehicleTagNumber, string ViolationDate,
                                           string ViolationTime, string ViolationCategory, string ViolationLocation,
                                           string MediaTransferDate, string PhotoAvailable, string VideoAvailable,
                                           string PaymentDueDate, string AmountDue)
            {
                if (!string.IsNullOrEmpty(CameraNumber))
                {
                    int videoAvailable = 0;
                    int photoAvailable = 0;
    
                    if (bool.Parse(PhotoAvailable) == true)
                        photoAvailable = 1;
    
                    if (bool.Parse(VideoAvailable) == true)
                        videoAvailable = 1;
    
                    using (SqlConnection scTrafficSystem = new SqlConnection(ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
                    {
                        string strNewTrafficTicket = "INSERT INTO CitationDivision.CamerasTickets(CameraNumber, VehicleTagNumber, ViolationDate, " +
                                                     "ViolationTime, ViolationCategory, ViolationLocation, MediaTransferDate, PhotoAvailable, " +
                                                     "VideoAvailable, PaymentDueDate, AmountDue) " +
                                                     "VALUES(N'" + CameraNumber + "', N'" + VehicleTagNumber + "', N'" + 
                                                             DateTime.Parse(ViolationDate).ToShortDateString() + "', N'" + ViolationTime + 
                                                             "', N'" + ViolationCategory + "', N'" + ViolationLocation + "', N'" + 
                                                             MediaTransferDate + "', " + photoAvailable + ", " + videoAvailable + ", N'" +
                                                             DateTime.Parse(PaymentDueDate).ToShortDateString() + "', " + double.Parse(AmountDue) + ");";
    
                        SqlCommand cmdTrafficTicket = new SqlCommand(strNewTrafficTicket, scTrafficSystem);
    
                        scTrafficSystem.Open();
    
                        cmdTrafficTicket.ExecuteNonQuery();
                    }
                }
    
                return RedirectToAction("IssueTicket");
            }
        }
    }
  3. In the document, right-click IssueTicket() and click Add View...
  4. In the dialog box, make sure the text box displays IssueTicket and click Add
  5. Create a form as follows:
    @{
        ViewBag.Title = "Traffic Ticket Processing";
    }
    
    <h2 class="text-center">Traffic Ticket Processing</h2>
    
    <hr />
    
    @{
        List<SelectListItem> ViolationCategory = new List<SelectListItem>();
    
        using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
        {
            string strViolationsCategoryies = "SELECT ViolationCategory " +
                                              "FROM CitationDivision.ViolationsCategoryies;";
    
            System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strViolationsCategoryies, scTrafficSystem);
    
            scTrafficSystem.Open();
    
            System.Data.SqlClient.SqlDataReader sdrViolationsCategoryies = cmdTrafficSystem.ExecuteReader();
    
            while (sdrViolationsCategoryies.Read())
            {
                ViolationCategory.Add(new SelectListItem { Value = @sdrViolationsCategoryies[0].ToString(), Text = @sdrViolationsCategoryies[0].ToString() });
            }
        }
    }
    
    @using (Html.BeginForm("SaveTicket", "TicketsProcessing", FormMethod.Post))
    {
        <div style="width: 550px; margin: auto;">
            <table>
                <tr>
                    <td style="width: 150px; font-weight: 600;">Camera #:</td>
                    <td>@Html.TextBox("CameraNumber")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Vehicle Tag #:</td>
                    <td>@Html.TextBox("VehicleTagNumber")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Violation Date:</td>
                    <td>@Html.TextBox("ViolationDate")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Violation Time</td>
                    <td>@Html.TextBox("ViolationTime")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Violation Type:</td>
                    <td>@Html.DropDownList("ViolationCategory", ViolationCategory)</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Violation Location:</td>
                    <td>@Html.TextBox("ViolationLocation")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Date/Time Media Was Transfered:</td>
                    <td>@Html.TextBox("MediaTransferDate")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Photo is Available:</td>
                    <td>@Html.CheckBox("PhotoAvailable")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Video is Available:</td>
                    <td>@Html.CheckBox("VideoAvailable")</td>
                <tr>
                <tr>
                    <td style="font-weight: 600;">Payment Due Date:</td>
                    <td>@Html.TextBox("PaymentDueDate")</td>
                </tr>
                <tr>
                    <td style="font-weight: 600;">Amount Due:</td>
                    <td>@Html.TextBox("AmountDue")</td>
                </tr>
            </table>
        </div>
    
        <hr />
    
        <p class="text-center"><input type="submit" name="btnIssueTicket" value="Issue Ticket" style="width: 300px" /></p>
    }
  6. To execute, on the main menu, click Debug -> Start Without Debugging:

    Using a Combo Box for Data Selection

  7. Enter the following values in the text boxes and make selections on the other controls:
    Camera #:             PNR-188075
    Vehicle Tag #:        CHW8BPG
    Violation Date:       04-16-2018
    Violation Time:       13:46:26
    Violation Category:   Red Light
    Location:             Coaster Alley Drv and Filister Rd
    Media Transfer Date:  04-27-2018 19:48:09
    Photo Availablee:     Unchecked
    Video Available:      Checked
    Payment Due Date:     06-05-2018
    Amount Due:           125
  8. Click the Issue Ticket button
  9. Close the browser and return to your programming environment

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