Data Relationships Fundamentals

Introduction

A relational database is a system of two or more lists, such as tables, that share records. The relationship can be explicit or implicit. A data relationship is explicit if a clear relationship has been established between two tables. We already know how to explicitly create a relationship, which is done using primary keys and foreign keys. When an explicit relationship has been created, every time a value is created (or added) to a foreign key in the child table, that value must be found in the primary key of the parent table. Otherwise

USE master;
GO
CREATE DATABASE CollegeParkAutoRepair;
GO
USE CollegeParkAutoRepair;
GO
CREATE SEQUENCE ReceiptNumbers
AS INT
START WITH 100001 
INCREMENT BY 1;
GO

CREATE TABLE RepairOrders
(
	ReceiptNumber 	integer,
	RepairDate    	nvarchar(50),
   	CustomerName  	nvarchar(60),
	PhoneNumber   	nvarchar(32),
   	[Address]     	nvarchar(50),
   	City          	nvarchar(40),
   	[State]       	nvarchar(40),
   	ZIPCode       	nvarchar(20),
   	Make          	nvarchar(30),
   	Model         	nvarchar(32),
   	CarYear       	int,
   	ProblemDescription nvarchar(max),
   	TotalParts    	decimal(5, 2),
   	TotalLabor    	decimal(5, 2),
   	TaxRate       	decimal(5, 2) default 7.75,
   	TaxAmount     	decimal(5, 2),
   	OrderTotal    	decimal(5, 2),
   	Recommendations	nvarchar(max),
   	CONSTRAINT PK_RepairOrders PRIMARY KEY(ReceiptNumber)
);
GO
CREATE TABLE PartsUsed
(
	PartID        int identity(1, 1) not null,
	ReceiptNumber int not null,
   	PartName      nvarchar(50) not null,
   	Price		  decimal(5, 2),
   	CONSTRAINT PK_PartsUsed PRIMARY KEY(PartID)
);
GO
CREATE TABLE JobsPerformed
(
	JobID         int identity(1, 1) not null,
   	ReceiptNumber int not null,
  	JobName       nvarchar(80),
   	Cost          decimal(5, 2),
	CONSTRAINT PK_JobsPerformed PRIMARY KEY(JobID)
);
GO

INSERT INTO RepairOrders(ReceiptNumber, RepairDate, CustomerName, PhoneNumber, [Address], City, [State], ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations) 
VALUES(NEXT VALUE FOR ReceiptNumbers, N'12-05-2019', N'Harrold Duncan', N'202-620-5814', N'9246 Eulaw Drive N.W.', N'Washington', N'DC', N'20018', N'Honda', N'Accord', 2012, N'The customer requested tune up on the car.', 92.50, 70, 7.75, 12.62, 175.52, N'The whole tune up was done.');
GO
INSERT INTO PartsUsed(ReceiptNumber, PartName, Price)
VALUES(100001, N'Air Filter', 24.95),
      (100001, N'Fuel Filter', 50.55),
      (100001, N'Wire Set', 4.35 * 4);
GO
INSERT INTO JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100001, N'Replaced the air filter, the fuel filter, and the spark plugs.', 70);
GO
INSERT INTO RepairOrders(ReceiptNumber, RepairDate, CustomerName, PhoneNumber, [Address], City, [State], ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations) 
VALUES(NEXT VALUE FOR ReceiptNumbers, N'01-10-2019', N'Eugenie Sanders', N'(301) 283-8074', N'3057 Daventry Rd', N'Upper Marlboro', N'MD', N'20772', N'Dodge', N'Sprinter 2500', 2014, N'The customer is complaining of a noise whenever she applies the brakes.', 159.50, 195, 7.75, 27.47, 381.97, N'The whole tune up was done.');
GO
INSERT INTO PartsUsed(ReceiptNumber, PartName, Price)
VALUES(100002, N'Replacement Front Brake Disc', 21.50),
      (100002, N'Replacement Rear Brake Disc', 40.50),
      (100002, N'Front Brake Pad Set', 32.35),
      (100002, N'Rear Brake Pad Set', 65.15);
GO
INSERT INTO JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100002, N'Changed the front and rear brakes.', 85),
      (100002, N'Installed a new brake booster.', 110);
GO

, the value would be rejected.

An implicit relationship is one you make up or figure out. The relationship is not formally established between the tables but in one table (considered a child table), there is a field that represents the records of another table (considered the parent table).

A Common Field for a Relationship

Probably the most important aspect of a relationship between two tables is a field they share. This means that you don't have to formally establish a relationship between two tables. Once they share a column (both columns must have the same data type but they don't have to have the same name), the relationship is implicit. Here is an example that contains three tables that share a common field named ReceiptNumber:

The easiest way to check a relationship between two tables is to match their records. This can be done using the WHERE clause of a SELECT statement. Here are examples:

<!DOCTYPE html>
<html>
<head>
<style>
.accent      { font-weight:   600;   }
.large       { width:         300px; }
.large-x     { width:         400px; }
.medium      { width:         150px; }
.left-column { width:         150px; }
.right       { text-align:    right; }
.underline   { border-bottom: 1px solid black; }
.underline2  { border-bottom: 2px solid black; }
.column      { width:         50%;
               float:         left; }
.row         { clear:         both;
               display:       table;}
</style>
<title>College Park Auto-Repair</title>
</head>
<body>
@{
    int receiptNumber = 0;
    double subTotal = 0.00;
    string year = string.Empty;
    string city = string.Empty;
    string make = string.Empty;
    string state = string.Empty;
    string model = string.Empty;
    string address = string.Empty;
    string ZIPCode = string.Empty;
    string taxRate = string.Empty;
    string taxAmount = string.Empty;
    string totalParts = string.Empty;
    string totalLabor = string.Empty;
    string repairDate = string.Empty;
    string phoneNumber = string.Empty;
    string repairTotal = string.Empty;
    string customerName = string.Empty;
    string recommendations = string.Empty;
    string problemDescription = string.Empty;

    Dictionary<string, string> partsUsed = new Dictionary<string, string>();
    Dictionary<string, string> jobsPerformed = new Dictionary<string, string>();

    if (IsPost)
    {
        if (!string.IsNullOrEmpty(Request["txtReceiptNumber"].ToString()))
        {
            receiptNumber = Request["txtReceiptNumber"].AsInt();

            using (System.Data.SqlClient.SqlConnection scCPAR =
                    new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
            {
                System.Data.SqlClient.SqlCommand cmdRepairOrders =
                    new System.Data.SqlClient.SqlCommand("SELECT RepairDate, CustomerName, PhoneNumber, Address, City, State, " +
                                                         "       ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, " +
                                                         "       TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations " +
                                                         "FROM RepairOrders " +
                                                         "WHERE ReceiptNumber = " + receiptNumber, scCPAR);
                scCPAR.Open();

                System.Data.SqlClient.SqlDataReader rdrRepairOrders = cmdRepairOrders.ExecuteReader();

                while (rdrRepairOrders.Read())
                {
                    repairDate = DateTime.Parse(rdrRepairOrders[0].ToString()).ToShortDateString();
                    customerName = Html.Encode(rdrRepairOrders[1].ToString());
                    phoneNumber = rdrRepairOrders[2].ToString();
                    address = rdrRepairOrders[3].ToString();
                    city = rdrRepairOrders[4].ToString();
                    state = rdrRepairOrders[5].ToString();
                    ZIPCode = rdrRepairOrders[6].ToString();
                    make = rdrRepairOrders[7].ToString();
                    model = rdrRepairOrders[8].ToString();
                    year = rdrRepairOrders[9].ToString();
                    problemDescription = rdrRepairOrders[10].ToString();
                    totalParts = rdrRepairOrders[11].ToString();
                    totalLabor = rdrRepairOrders[12].ToString();
                    taxRate = rdrRepairOrders[13].ToString();
                    taxAmount = rdrRepairOrders[14].ToString();
                    repairTotal = rdrRepairOrders[15].ToString();
                    recommendations = rdrRepairOrders[16].ToString();

                    subTotal = double.Parse(totalParts) + double.Parse(totalLabor);
                }
            }

            using (System.Data.SqlClient.SqlConnection scCPAR =
                    new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
            {
                System.Data.SqlClient.SqlCommand cmdPartsUsed =
                    new System.Data.SqlClient.SqlCommand("SELECT PartName, Price " +
                                                         "FROM PartsUsed " +
                                                         "WHERE ReceiptNumber = " + receiptNumber, scCPAR);
                scCPAR.Open();

                System.Data.SqlClient.SqlDataReader rdrPartsUsed = cmdPartsUsed.ExecuteReader();

                while (rdrPartsUsed.Read())
                {
                    partsUsed.Add(rdrPartsUsed[0].ToString(), rdrPartsUsed[1].ToString());
                }
            }

            using (System.Data.SqlClient.SqlConnection scCPAR =
                    new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
            {
                System.Data.SqlClient.SqlCommand cmdJobsPerformed =
                    new System.Data.SqlClient.SqlCommand("SELECT JobName, Cost " +
                                                         "FROM JobsPerformed " +
                                                         "WHERE ReceiptNumber = " + receiptNumber, scCPAR);
                scCPAR.Open();

                System.Data.SqlClient.SqlDataReader rdrJobsPerformed = cmdJobsPerformed.ExecuteReader();

                while (rdrJobsPerformed.Read())
                {
                    jobsPerformed.Add(rdrJobsPerformed[0].ToString(), rdrJobsPerformed[1].ToString());
                }
            }
        }
    }
}

<h1>College Park Auto-Repair</h1>

<form name="frmRepairOrderReview" method="post">
    <table>
        <tr>
            <td class="left-column accent">Receipt #:</td>
            <td><input type="text" name="txtReceiptNumber" value="@receiptNumber" /> 
            <input type="submit" name="btnFind" value="Find" /></td>
        </tr>
        <tr>
            <td class="accent">Repair Date:</td>
            <td><input type="text" id="RepairDate" value=@repairDate /></td>
        </tr>
    </table>
    <p>

        <fieldset>
            <legend class="accent">Customer and Vehicle Information</legend>
            <table>
                <tr>
                    <td class="left-column accent">Customer Name:</td>
                    <td class="underline large">@customerName</td>
                    <td class="accent">Phone #:</td>
                    <td class="underline">@phoneNumber</td>
                </tr>
            </table>
            <table>
                <tr>
                    <td class="left-column accent">Address:</td>
                    <td class="underline">@address</td>
                </tr>
            </table>
            <table>
                <tr>
                    <td class="left-column accent">City:</td>
                    <td class="underline medium">@city</td>
                    <td class="accent">State:</td>
                    <td class="underline">@state</td>
                    <td class="accent">ZIP Code:</td>
                    <td class="underline">@ZIPCode</td>
                </tr>
                <tr>
                    <td class="accent">Make:</td>
                    <td class="underline medium">@make</td>
                    <td class="accent">Model:</td>
                    <td class="underline">@model</td>
                    <td class="accent">Year:</td>
                    <td class="underline">@year</td>
                </tr>
            </table>
            <table>
                <tr>
                    <td class="left-column accent">Problem Description:</td>
                    <td><textarea id="ProblemDescription" cols="50" rows="3">@problemDescription</textarea></td>
                </tr>
            </table>
        </fieldset>

        <div class="row">
            <div class="column">
                <fieldset>
                    <legend class="accent">Parts Used</legend>
                    <table>
                        <tr>
                            <td class="accent large-x underline2">Part Name</td>
                            <td class="accent right">Price</td>
                        </tr>
                        @foreach (KeyValuePair<string, string> partUsed in partsUsed)
                        {
                            <tr>
                                <td class="underline">@partUsed.Key</td>
                                <td class="underline right">@partUsed.Value</td>
                            </tr>
                        }
                    </table>
                </fieldset>
            </div>
            <div class="column">
                <fieldset>
                    <legend class="accent">Jobs Performed</legend>
                    <table>
                        <tr>
                            <td class="underline2 accent large-x">Job Performed</td>
                            <td class="underline2 accent right">Cost</td>
                        </tr>
                        @foreach (KeyValuePair<string, string> jobPerformed in jobsPerformed)
                        {
                            <tr>
                                <td class="underline">@jobPerformed.Key</td>
                                <td class="underline right">@jobPerformed.Value</td>
                            </tr>
                        }
                    </table>
                </fieldset>
            </div>
        </div>

        <div class="row">
            <div class="column">
                <fieldset>
                    <legend class="accent">Repair Summary</legend>
                    <table>
                        <tr>
                            <td class="accent underline">Total Parts:</td>
                            <td class="underline right">@totalParts</td>
                            <td style="width: 80px">&nbsp;</td>
                            <td class="accent underline">Tax Rate:</td>
                            <td class="right underline">@taxRate</td>
                        </tr>
                        <tr>
                            <td class="accent underline">Total Labor:</td>
                            <td class="right underline">@totalLabor</td>
                            <td>&nbsp;</td>
                            <td class="right accent underline">Tax Amount:</td>
                            <td class="right underline">@taxAmount</td>
                        </tr>
                        <tr>
                            <td class="accent underline">Sub-Total:</td>
                            <td class="right underline">@subTotal</td>
                            <td>&nbsp;</td>
                            <td class="accent underline">Repair Total:</td>
                            <td class="right underline">@repairTotal</td>
                        </tr>
                    </table>
                </fieldset>
            </div>
            <div class="column">
                <fieldset>
                    <legend class="accent">Recommendations</legend>
                    <textarea id="ProblemDescription" cols="50" rows="5">@recommendations</textarea>
                </fieldset>
            </div>
        </div>
</form>
</body>
</html>

Data Relationships Fundamentals: A Common Field for a Relationship

Data Relationships Fundamentals: A Common Field for a Relationship

Practical LearningPractical Learning: Introducing Joins

  1. Start Microsoft Visual Studio
  2. To start a new website, on the main menu, click File -> New -> Project...
  3. In the New Project dialog box, click ASP.NET Web application (.NET Framework).
    Change the project Name to WaterDistributionCompany1
  4. Click OK
  5. In the New ASP.NET Web Application dialog box, in the templates list, click the MVC icon and click OK
  6. To create a database
    1. If you want to use Microsoft SQL Server:
      1. Start Microsoft SQL Server Management Studio and log in
      2. In the Object Explorer, right-click the the name of the computer and click New Query
      3. Type:
        USE master;
        GO
        CREATE DATABASE WaterDistribution;
        GO
        USE WaterDistribution;
        GO
      4. To execute, right-click inside the Query window and click Execute
      5. Press Ctrl + A to select everything
    2. If you want to use a local database in Microsoft Visual Studio:
      1. In the Solution Explorer, right-click App_Data -> Add -> SQL Server Database
      2. Type WaterDistribution as the name of the database
      3. Click OK
      4. In the Solution Explorer, under App_Data, right-click WaterDistribution and click Open
      5. In the Server Explorer, right-click WaterDistribution.mdf and click New Query
  7. To create a database for this lesson, type the following code:
    CREATE TABLE WaterMeters
    (
    	WaterMeterID	INT IDENTITY(1, 1),
    	MeterNumber		NVARCHAR(20),
    	Make			NVARCHAR(30),
    	Model			NVARCHAR(20),
    	MeterSize		NVARCHAR(20),
    	DateLastUpdate	DATE,
    	CounterValue	INT,
    	CONSTRAINT PK_WaterMeters PRIMARY KEY(WaterMeterID)
    );
    GO
    CREATE TABLE Customers
    (
    	CustomerID    INT IDENTITY(1, 1),
    	AccountNumber NVARCHAR(20) UNIQUE,
    	WaterMeterID  INT,
    	FirstName     NVARCHAR(20),
    	LastName      NVARCHAR(20),
    	[Address]	  NVARCHAR(100),
    	City          NVARCHAR(40),
    	County        NVARCHAR(40),
    	[State]       NVARCHAR(2),
    	ZIPCode		  NVARCHAR(10),
    	CONSTRAINT FK_WaterMeters FOREIGN KEY(WaterMeterID) REFERENCES WaterMeters(WaterMeterID),
    	CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)
    );
    GO
    CREATE TABLE WaterBills
    (
    	WaterBillID           INT IDENTITY(1, 1),
      InvoiceNumber         INT UNIQUE,
    	CustomerID            INT,
    	MeterReadingStartDate NVARCHAR(40),
    	MeterReadingEndDate   NVARCHAR(40),
    	BillingDays		      INT,
    	CounterReadingStart   DECIMAL,
    	CounterReadingEnd     DECIMAL,
    	TotalHCF              INT,
    	TotalGallons		  INT,
    	First15HCF			  DECIMAL(6, 2),
    	Next10HCF			  DECIMAL(6, 2),
    	RemainingHCF		  DECIMAL(6, 2),
    	SewerCharges		  DECIMAL(6, 2),
    	StormCharges 		  DECIMAL(6, 2),
    	WaterUsageCharges	  DECIMAL(6, 2),
    	TotalCharges		  DECIMAL(6, 2),
    	LocalTaxes			  DECIMAL(6, 2),
    	StateTaxes	          DECIMAL(6, 2),
    	PaymentDueDate        NVARCHAR(40),
    	AmountDue             DECIMAL(6, 2), 
    	LatePaymentDueDate    NVARCHAR(40),
    	LateAmountDue         DECIMAL(6, 2), 
    	CONSTRAINT FK_Customers FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID),
    	CONSTRAINT PK_WaterBills PRIMARY KEY(WaterBillID)
    );
    GO
    CREATE TABLE Payments
    (
    	PaymentID     INT IDENTITY(1, 1),
    	WaterBillID   INT,
    	PaymentDate   DATE,
    	PaymentAmount DECIMAL(8, 2), 
    	CONSTRAINT FK_WaterBills FOREIGN KEY(WaterBillID) REFERENCES WaterBills(WaterBillID),
    	CONSTRAINT PK_Payments PRIMARY KEY(PaymentID)
    );
    GO
  8. To execute the code, right-click inside the Query window and click Execute
  9. Close the Query window
  10. When asked whether you want to save, click No
  11. In the Solution Explorer, double-click the very bottom Web.config file
  12. To create a connection string:
    • If you are using a local database, add a 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=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); Initial Catalog='WaterDistribution'; Integrated Security=True;MultipleActiveResultSets=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
    • If you created you database in Microsoft SQL Server, create a 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=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=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WaterDistribution.mdf;Initial Catalog=WaterDistribution;Integrated Security=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
  13. In the Solution Explorer, right-click WaterDistributionCompany1 -> Add -> New Folder
  14. Type Images and press Enter
  15. Save the following picture to the Images folder:

    Water for a Shining Life

  16. In the Solution Explorer, right-click Content -> Add -> New Item...
  17. In the middle frame of the Add New Item dialog box, click Style Sheet
  18. Change the file Name to WaterDistribution
  19. Click Add
  20. Change the document as follows:
    body {
        background-color: #2b5b8f;
    }
    
    .top-bar           { top:              0px;
                         z-index:          1000;
                         width:            100%;
                         position:         fixed;
                         height:           6.85em;
                         background-color: #203864;           }
    .containment       { margin:           auto;
                         width:            460px;             }
    .navbar-inverse    { background-color: #001132;
                         border-top:       3px solid #cfdde0;
                         border-bottom:    3px solid #cfdde0; }
    .navbar-fixed-top  { top:              6.75em;            }
    .jumbotron         { padding-bottom:   4px;
                         background-color: #153a62;           }
    
    .col-md-3 h2       { color:            #abcbd9;
                         border-bottom:    1px solid #cfdde0; }
    .lead              { color:            #cfdde0;           }
    .col-md-3 p        { color:            #d5d4c2;           }
    .caption           { color:            aliceblue;         }
    .copyright         { color:            #beeeab;           }
    .push-down         { margin-top:       8em;               }
    .push-down h2      { color:            #d5d4c2;
                         font-weight:      600;
                         font-size:        26px;
                         text-align:       center;
                         font-family:      Garamond, Georgia, 'Times New Roman', serif; }
    .push-down h3      { color:            #abcbd9;           }
    .push-down p       { color:            #cfdde0;           }
    .water-nav         { text-decoration:  none;
                         color:            yellow;            }
    .water-nav:link    { color:            orange;            }
    .water-nav:visited { color:            aliceblue;         }
    .water-nav:active  { color:            #a8c3ce;           }
    .water-nav:hover   { color:            yellow;            }
    .common-font       { font-family:      Garamond, Georgia, 'Times New Roman', serif; }
    
    .table-striped > tbody > tr:nth-of-type(even) {
        color: navy;
        background-color: azure;
    }
    
    .table-striped > tbody > tr:nth-of-type(odd) {
        color: aliceblue;
        background-color: cornflowerblue;
    }
  21. In the Solution Explorer, under Views, expand App_Start and double-click BundleConfig.cs to open it
  22. Change the document as follows:
    using System.Web.Optimization;
    
    namespace WaterDistributionCompany1
    {
        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"));
            }
        }
    }
  23. In the Solution Explorer, right-click Models -> Add -> Class...
  24. Type WaterMeter as the name of the class
  25. Click Add
  26. Create the class as follows:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistributionCompany1.Models
    {
        public class WaterMeter
        {
            [Display(Name = "Water Meter ID")]
            public int WaterMeterID { get; set; }
            [Display(Name = "Meter #")]
            public string MeterNumber { get; set; }
            public string Make { get; set; }
            public string Model { get; set; }
    
            [Display(Name = "Meter Size")]
            public string MeterSize { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Date Last Update")]
            public DateTime DateLastUpdate { get; set; }
            [Display(Name = "Counter Value")]
            public int CounterValue { get; set; }
    
            public string Description
            {
                get
                {
                    return Make + " " + Model +
                           " (Model #: " + MeterNumber + ", " +
                           MeterSize + ")";
                }
            }
        }
    }
  27. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  28. In the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions
  29. Click Add
  30. Type WaterMeters to get WaterMetersController
  31. Press Enter
  32. Change the document as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class WaterMetersController : Controller
        {
            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, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters;",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    WaterMeter meter = null;
    
                    for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
                    {
                        meter = new WaterMeter();
                        meter.WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString());
                        meter.MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString();
                        meter.Make = dsWaterMeters.Tables[0].Rows[i][2].ToString();
                        meter.Model = dsWaterMeters.Tables[0].Rows[i][3].ToString();
                        meter.MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString();
                        meter.DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString());
                        meter.CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString());
    
                        waterMeters.Add(meter);
                    }
                }
    
                return View(waterMeters);
            }
    
            // GET: WaterMeters/Details/5
            public ActionResult Details(int id)
            {
                WaterMeter meter = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters " +
                                                               "WHERE WaterMeterID = " + id + ";",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    if (dsWaterMeters.Tables[0].Rows.Count > 0)
                    {
                        meter = new WaterMeter();
                        meter.WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString());
                        meter.MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString();
                        meter.Make = dsWaterMeters.Tables[0].Rows[0][2].ToString();
                        meter.Model = dsWaterMeters.Tables[0].Rows[0][3].ToString();
                        meter.MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString();
                        meter.DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString());
                        meter.CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString());
                    }
                }
    
                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, " +
                                                                   "                        DateLastUpdate, CounterValue) " +
                                                                   "VALUES(N'" + collection["MeterNumber"] + "', N'" +
                                                                   collection["Make"] + "', N'" + collection["Model"] +
                                                                   "', N'" + collection["MeterSize"] + "', N'" +
                                                                   collection["DateLastUpdate"] + "', " +
                                                                   collection["CounterValue"] + ");",
                                                                   scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdWaterMeters.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View("Create");
                }
            }
    
            // GET: WaterMeters/Edit/5
            public ActionResult Edit(int id)
            {
                WaterMeter meter = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters " +
                                                               "WHERE WaterMeterID = " + id + ";",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    if (dsWaterMeters.Tables[0].Rows.Count > 0)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString())
                        };
                    }
                }
    
                ViewBag.DateLastUpdate = meter.DateLastUpdate.ToShortDateString();
    
                return View(meter);
            }
    
            // POST: WaterMeters/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdWaterMeters = new SqlCommand("UPDATE WaterMeters " +
                                                                   "SET MeterNumber    = N'" + collection["MeterNumber"] + "', " +
                                                                   "    Make           = N'" + collection["Make"] + "', " +
                                                                   "    Model          = N'" + collection["Model"] + "', " +
                                                                   "    MeterSize      = N'" + collection["MeterSize"] + "', " +
                                                                   "    DateLastUpdate = N'" + collection["DateLastUpdate"] + "', " +
                                                                   "    CounterValue   = " + collection["CounterValue"] + "   " +
                                                                   "WHERE WaterMeterID = " + id + ";",
                                                                   scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdWaterMeters.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: WaterMeters/Delete/5
            public ActionResult Delete(int id)
            {
                WaterMeter meter = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters " +
                                                               "WHERE WaterMeterID = " + id + ";",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    if (dsWaterMeters.Tables[0].Rows.Count > 0)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString())
                        };
                    }
                }
    
                return View(meter);
            }
    
            // POST: WaterMeters/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdWaterMeters = new SqlCommand("DELETE WaterMeters " +
                                                                   "WHERE WaterMeterID = " + id + ";",
                                                                   scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdWaterMeters.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  33. In the class, right-click Index() and click Add View...
  34. Make sure the View Name text box displays Index.
    Click Add
  35. Change the webpage as follows:
    @model IEnumerable<WaterDistributionCompany1.Models.WaterMeter>
    
    @{
        ViewBag.Title = "Water Meters";
    }
    
    <div class="push-down">
        <h2 class="common-font bold text-center">Water Meters</h2>
    </div>
    
    <hr />
    
    <table class="table table-striped common-font">
        <tr>
            <th class="bold">Water Meter ID</th>
            <th class="bold">Meter #</th>
            <th class="bold">Make</th>
            <th class="bold">Model</th>
            <th class="bold">Meter Size</th>
            <th class="bold">Date Last Update</th>
            <th class="bold">Counter Value</th>
            <th>@Html.ActionLink("New Water Meter", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
        </tr>
    
        @foreach (var item in Model)
        {
        <tr>
            <td class="text-center">@Html.DisplayFor(modelItem => item.WaterMeterID)</td>
            <td>@Html.DisplayFor(modelItem => item.MeterNumber)</td>
            <td>@Html.DisplayFor(modelItem => item.Make)</td>
            <td>@Html.DisplayFor(modelItem => item.Model)</td>
            <td>@Html.DisplayFor(modelItem => item.MeterSize)</td>
            <td>@Html.DisplayFor(modelItem => item.DateLastUpdate)</td>
            <td>@Html.DisplayFor(modelItem => item.CounterValue)</td>
            <td>
                @Html.ActionLink("Update", "Edit", new { id = item.WaterMeterID }) |
                @Html.ActionLink("Review", "Details", new { id = item.WaterMeterID }) |
                @Html.ActionLink("Remove", "Delete", new { id = item.WaterMeterID })
            </td>
        </tr>
        }
    </table>
  36. Click the WaterMetersController.cs tab to access the class
  37. In the class, right-click below Details() and click Add View...
  38. Make sure the View Name text box displays Details. Click Add
  39. Change the document as follows:
    @model WaterDistributionCompany1.Models.WaterMeter
    
    @{
        ViewBag.Title = "Water Meter Details";
    }
    
    <div class="push-down">
        <h2>Water Meter Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
            <dt>Water Meter #</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>
            <dt>@Html.DisplayNameFor(model => model.DateLastUpdate)</dt>
            <dd>@Html.DisplayFor(model => model.DateLastUpdate)</dd>
            <dt>@Html.DisplayNameFor(model => model.CounterValue)</dt>
            <dd>@Html.DisplayFor(model => model.CounterValue)</dd>
        </dl>
    </div>
    
    <p class="text-center">
        @Html.ActionLink("Edit/Update Water Meter Information", "Edit",
                         new { id = Model.WaterMeterID, @class = "water-nav" }) |
        @Html.ActionLink("Water Meters", "Index",
                         null, new { @class = "water-nav" })
    </p>
  40. Click the WaterMetersController.cs tab
  41. In the document, right-click one of the Create() methods and click Add View...
  42. In the Add View dialog box, make sure the View Name text box is displaying Create. Click Add
  43. Create a form as follows:
    @{
        ViewBag.Title = "New Water Meter";
    }
    
    <div class="push-down">
        <h2>New Water Meter</h2>
    </div>
    <hr />
    @using (Html.BeginForm())
    {
        <div class="form-horizontal">
            <div class="form-group">
                <label for="mtrNbr" class="control-label col-md-4 caption">Meter #:</label>
                <div class="col-md-8">
                    @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-4 caption">Make:</label>
                <div class="col-md-8">
                    @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-4 caption">Model:</label>
                <div class="col-md-8">
                    @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-4 caption">Meter Size:</label>
                <div class="col-md-8">
                    @Html.TextBox("MeterSize", null, htmlAttributes: new { @class = "form-control", id = "mtrSize" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="dlu" class="control-label col-md-4 caption">Date Last Update:</label>
                <div class="col-md-8">
                    @Html.TextBox("DateLastUpdate",
                        null, htmlAttributes: new { @class = "form-control", type = "date", id = "dlu" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="cntVal" class="control-label col-md-4 caption">Counter Value:</label>
                <div class="col-md-8">
                    @Html.TextBox("CounterValue", null, htmlAttributes: new { @class = "form-control", id = "cntVal" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-5">
                    @Html.ActionLink("Water Meters", "Index", null,
                htmlAttributes: new { @class = "water-nav" })
                </label>
                <div class="col-md-7">
                    <input type="submit" value="Create Water Meter" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  44. Click the WaterMetersController.cs tab
  45. In the class, right-click one of the Edit() methods and click Add View...
  46. In the Add View dialog box, make sure the View Name text box displays Edit. Click Add
  47. Create a form as follows:
    @model WaterDistributionCompany1.Models.WaterMeter
    
    @{
        ViewBag.Title = "Edit/Update Water Meter";
    
        string strDateLastUpdate = ViewBag.DateLastUpdate;
    }
    
    <div class="push-down">
        <h2>Edit/Update Water Meter</h2>
    </div>
    
    <hr />
    
    @using (Html.BeginForm())
    {
    <div class="form-horizontal common-font containment">
        @Html.HiddenFor(model => model.WaterMeterID)
    
        <div class="form-group">
            @Html.LabelFor(model => model.MeterNumber, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @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-4 caption" })
            <div class="col-md-8">
                @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-4 caption" })
            <div class="col-md-8">
                @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-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.MeterSize, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.DateLastUpdate, htmlAttributes: new { @class = "control-label col-md-4 caption" }) 
            <div class="col-md-3"><label class="control-label caption">@strDateLastUpdate</label></div>
            <div class="col-md-4">
                @Html.EditorFor(model => model.DateLastUpdate, "date", new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.CounterValue, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.CounterValue, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label class="control-label col-md-4 caption">
                @Html.ActionLink("Water Meters", "Index", null, new { @class = "water-nav" })
            </label>
            <div class="col-md-8">
                <input type="submit" value="Update Water Meter Information" class="btn btn-primary" />
            </div>
        </div>
    </div>
    }
  48. Click the WaterMetersController.cs tab to access it
  49. Right-click one of the Delete() methods and click Add View...
  50. Make sure the View Name text box displays Delete. Click Add
  51. Change the document as follows:
    @model WaterDistributionCompany1.Models.WaterMeter
    
    @{
        ViewBag.Title = "Delete Water Meter";
    }
    
    <div class="push-down">
        <h2>Delete Water Meter</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <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>
    
            <dt>@Html.DisplayNameFor(model => model.DateLastUpdate)</dt>
            <dd>@Html.DisplayFor(model => model.DateLastUpdate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.CounterValue)</dt>
            <dd>@Html.DisplayFor(model => model.CounterValue)</dd>
        </dl>
    
        <h3 class="common-font caption">Are you sure you want to remove this water from the database?</h3>
    
        @using (Html.BeginForm())
        {
            <div class="form-actions no-color">
                <input type="submit" value="Delete this Water Meter" class="btn btn-primary" /> |
                @Html.ActionLink("Water Meters", "Index", null, new { @class = "water-nav" })
            </div>
        }
    </div>
  52. In the Solution Explorer, under Views, expand Shared and double-click _Layout.cshtml
  53. Change the _Layout.cshtml 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 - Water for a Shining Life</title>
        @Styles.Render("~/Content/css")
        @Scripts.Render("~/bundles/modernizr")
    </head>
    <body>
        <div class="top-bar">
            <div class="containment"><img src="~/Images/wsl1.png" alt="Water for a Shining Life" width="490" height="92" /></div>
        </div>
    
        <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("Water Distribution Company", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
                </div>
                <div class="navbar-collapse collapse">
                    <ul class="nav navbar-nav">
                        <li>@Html.ActionLink("Emergency Services", "Index", "Home")</li>
                        <li>@Html.ActionLink("Cummunity", "Index", "Home")</li>
                        <li>@Html.ActionLink("Resources", "Index", "Home")</li>
                        <li>@Html.ActionLink("Projects", "Index", "Home")</li>
                        <li>@Html.ActionLink("Customer Service", "Index", "Home")</li>
                        <li>@Html.ActionLink("Employment", "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 class="copyright text-center common-font">&copy; @DateTime.Now.Year - Water for a Shining Life</p>
            </footer>
        </div>
    
        @Scripts.Render("~/bundles/jquery")
        @Scripts.Render("~/bundles/bootstrap")
        @RenderSection("scripts", required: false)
    </body>
    </html>
  54. In the Solution Explorer, under Views, expand Home, and double-click Index.cshtml
  55. Change the document as follows:
    <div class="jumbotron">
        <h2>.</h2>
        <p class="lead">
            Our water utility company provides energy, greatness, and warmth
            for a everyday life, a shining life. We provide solutions to families, businesses,
            and the community.
        </p>
    
        <p class="lead">
            This is the employees portal section of the company. From here,
            employees can register a new water meter, manage a customer account, or
            create a water bill.
        </p>
    </div>
    
    <div class="row">
        <div class="col-md-3">
            <h2>Water Meters</h2>
            <p>
                Our company uses the most accurate, sophisticated, and environment-friendly
                water meters on the market.
            </p>
            <p>@Html.ActionLink("Water Meters", "Index", "WaterMeters", null, new { @class = "btn btn-primary" })</p>
        </div>
        <div class="col-md-3">
            <h2>Customers</h2>
            <p>
                We supply water to individuals, families, small
                businesses, as well as enterprises or government agencies.
            </p>
            <p>@Html.ActionLink("Customers", "Index", "Customers", null, new { @class = "btn btn-primary" })</p>
        </div>
        <div class="col-md-3">
            <h2>Water Bills</h2>
            <p>
                Our water rates are very competitive nationwide. We use precise,
                effective, and strict algorithms when calculating our bills.
            </p>
            <p>@Html.ActionLink("Bills/Invoices", "Index", "WaterBills", null, new { @class = "btn btn-primary" })</p>
        </div>
        <div class="col-md-3">
            <h2>Payments</h2>
            <p>
                Our payment system is the simplest, the fairest, and the fastest. Our custiomer's service
                is highly rated.
            </p>
            <p>@Html.ActionLink("Bills Payments", "Index", "Payments", null, new { @class = "btn btn-primary" })</p>
        </div>
    </div>
  56. To execute, on the main menu, click Debug -> Start Without Debugging:

    Right

  57. Click the Water Meters link

    Text Box

  58. Click the New Water Meter link

    Joins Fundamentals

  59. Create the following records:
     
    Meter # Make Model Meter Size Date Last Update Counter Value
    392-44-572 Constance Technologies TG-4822 5/8 Inches 07/02/2019 109992
    938-75-869 Standard Trend 266G 1 1/2 Inches 07/05/2019 137926
    799-28-461 Constance Technologies BD-7000 3/4 Inches 7/5/2019 6268
    207-94-835 Constance Technologies TG-6220 5/8 Inches 7/2/2019 96
    592-84-957 Standard Trend 428T 3/4 Inches 9/3/2019 49

    Joins Fundamentals

    INSERT WaterMeters(MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue)
    VALUES(N'392-44-572', N'Constance Technologies',  N'TG-4822',    N'5/8 Inches',   N'07/02/2019', 109992),
          (N'938-75-869', N'Standard Trend',          N'266G',       N'1 1/2 Inches', N'07/05/2019', 137926),
          (N'799-28-461', N'Constance Technologies',  N'BD-7000',    N'3/4 Inches',   N'7/5/2019',     6268),
          (N'207-94-835', N'Constance Technologies',  N'TG-6220',    N'5/8 Inches',   N'7/2/2019',       96),
          (N'592-84-957', N'Standard Trend',          N'428T',       N'3/4 Inches',   N'9/3/2019',       49);
    GO
  60. Close the browser and return to your programming environment
  61. Close the Index.cshtml tabs, the _Layout.cshtml, the Delete.cshtml, the Edit.cshtml, the Create.cshtml, the Details.cshtml, the WaterMetersController.cs, the WaterMeter.cs, the BundleConfig.cs, the WaterDistribution.css, and the Web.config tabs
  62. In the Solution Explorer, right-click Models -> Add -> Class...
  63. Type Customer as the name of the class
  64. Click Add
  65. Create the class as follows:
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistributionCompany1.Models
    {
        public class Customer
        {
            [Display(Name = "Customer ID")]
            public int    CustomerID    { get; set; }
            [Display(Name = "Account #")]
            public string AccountNumber { get; set; }
            [Display(Name = "Water Meter")]
            public int    WaterMeterID  { get; set; }
            [Display(Name = "First Name")]
            public string FirstName     { get; set; }
            [Display(Name = "Last Name")]
            public string LastName      { get; set; }
            public string Address       { get; set; }
            public string City          { get; set; }
            public string County        { get; set; }
            public string State         { get; set; }
            [Display(Name = "ZIP Code")]
            public string ZIPCode       { get; set; }
        }
    }
  66. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  67. In the left list of the Add Scaffold dialog box, click MVC and, in the middle list, click MVC 5 Controller With Read/Write Actions
  68. Click Add
  69. Type Customers to get CustomersController
  70. Click Add
  71. Complete the class as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class CustomersController : Controller
        {
            List<Customer> customers = new List<Customer>();
    
            // GET: Customers
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers; ",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    DataTable dtCustomers = dsCustomers.Tables[0];
    
                    for (int i = 0; i < dtCustomers.Rows.Count; i++)
                    {
                        DataRow drCustomer = dtCustomers.Rows[i];
    
                        customers.Add(new Customer()
                        {
                            CustomerID    = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID  = int.Parse(drCustomer[2].ToString()),
                            FirstName     = drCustomer[3].ToString(),
                            LastName      = drCustomer[4].ToString(),
                            Address       = drCustomer[5].ToString(),
                            City          = drCustomer[6].ToString(),
                            County        = drCustomer[7].ToString(),
                            State         = drCustomer[8].ToString(),
                            ZIPCode       = drCustomer[9].ToString()
                        });
                    }
                }
    
                return View(customers);
            }
    
            // GET: Customers/Details/5
            public ActionResult Details(int id)
            {
                Customer client = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers " +
                                                             "WHERE CustomerID = " + id + ";",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    DataTable dtCustomers = dsCustomers.Tables[0];
    
                    if (dtCustomers.Rows.Count > 0)
                    {
                        DataRow drCustomer =dtCustomers.Rows[0];
    
                        client = new Customer
                        {
    
                            CustomerID    = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID  = int.Parse(drCustomer[2].ToString()),
                            FirstName     = drCustomer[3].ToString(),
                            LastName      = drCustomer[4].ToString(),
                            Address       = drCustomer[5].ToString(),
                            City          = drCustomer[6].ToString(),
                            County        = drCustomer[7].ToString(),
                            State         = drCustomer[8].ToString(),
                            ZIPCode       = drCustomer[9].ToString()
                        };
                    }
                }
    
                return View(client);
            }
    
            // GET: Customers/Create
            public ActionResult Create()
            {
                List<WaterMeter> waterMeters = new List<WaterMeter>();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters;",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    WaterMeter meter = null;
    
                    for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
                        };
    
                        waterMeters.Add(meter);
                    }
                }
    
                ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description");
    
                return View();
            }
    
            // POST: Customers/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 cmdCustomers = new SqlCommand("INSERT INTO Customers(AccountNumber, WaterMeterID, " +
                                                                 "       FirstName, LastName, Address, City, " +
                                                                 "       County, State, ZIPCode) " +
                                                                 "VALUES(N'" + collection["AccountNumber"] + "', " +
                                                                 collection["WaterMeterID"] + ", N'" + collection["FirstName"] +
                                                                 "', N'" + collection["LastName"] + "', N'" +
                                                                 collection["Address"] + "', N'"  + collection["City"] + "', N'" + 
                                                                 collection["County"]  + "', N'"  + collection["State"] + "', N'" +
                                                                 collection["ZIPCode"] + "');",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Customers/Edit/5
            public ActionResult Edit(int id)
            {
                Customer client = null;
                WaterMeter meter = null;
                List<WaterMeter> waterMeters = new List<WaterMeter>();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers " +
                                                             "WHERE CustomerID = " + id + ";",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    if (dsCustomers.Tables[0].Rows.Count > 0)
                    {
                        DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
    
                        client = new Customer
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID = int.Parse(drCustomer[2].ToString()),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " +
                                                               "       MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters;",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
                        };
    
                        waterMeters.Add(meter);
                    }
                }
    
                ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description", client.WaterMeterID);
    
                return View(client);
            }
    
            // POST: Customers/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdCustomers = new SqlCommand("UPDATE Customers " +
                                                                 "SET AccountNumber = N'" + collection["AccountNumber"] + "'," + 
                                                                 "    WaterMeterID  = "   + collection["WaterMeterID"]  + "," +
                                                                 "    FirstName     = N'" + collection["FirstName"]     + "'," +
                                                                 "    LastName      = N'" + collection["LastName"]      + "'," +
                                                                 "    Address       = N'" + collection["Address"]       + "'," +
                                                                 "    City          = N'" + collection["City"]          + "'," +
                                                                 "    County        = N'" + collection["County"]        + "'," +
                                                                 "    State         = N'" + collection["State"]         + "'," +
                                                                 "    ZIPCode       = N'" + collection["ZIPCode"]       + "' " +
                                                                 "WHERE CustomerID = " + id + ";",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Customers/Delete/5
            public ActionResult Delete(int id)
            {
                Customer client = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers " +
                                                             "WHERE CustomerID = " + id + ";",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    if (dsCustomers.Tables[0].Rows.Count > 0)
                    {
                        DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
    
                        client = new Customer
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID = int.Parse(drCustomer[2].ToString()),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
                
                return View(client);
            }
    
            // POST: Customers/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Customers " +
                                                                 "WHERE CustomerID = " + id + ";",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
        
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  72. In the class, right-click anything inside the Details() method and click Add View...
  73. Make sure the View Name text box displays Details. Click Add
  74. Change the webpage as follows:
    @model WaterDistributionCompany1.Models.Customer
    
    @{
        ViewBag.Title = "Customer Details";
    }
    
    <div class="push-down">
        <h2>Customer Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
            <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
            <dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
            <dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
            <dt>@Html.DisplayNameFor(model => model.Address)</dt>
            <dd>@Html.DisplayFor(model => model.Address)</dd>
            <dt>@Html.DisplayNameFor(model => model.City)</dt>
            <dd>@Html.DisplayFor(model => model.City)</dd>
            <dt>@Html.DisplayNameFor(model => model.County)</dt>
            <dd>@Html.DisplayFor(model => model.County)</dd>
            <dt>@Html.DisplayNameFor(model => model.State)</dt>
            <dd>@Html.DisplayFor(model => model.State)</dd>
            <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
            <dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
        </dl>
    </div>
    
    <p class="text-center">
            @Html.ActionLink("Edit/Update Customer Information", "Edit",
                             new { id = Model.CustomerID, @class = "water-nav" },
                             new { @class = "water-nav" }) |
            @Html.ActionLink("Customers Records", "Index",
                             null, new { @class = "water-nav" })
    </p>
    
  75. Click the CustomersController.cs tab
  76. In the document, right-click one of the Create() methods and click Add View...
  77. In the Add View dialog box, make the View Name text box displays Create. Click Add
  78. Create the form as follows:
    @{
        ViewBag.Title = "New Customer Account";
    }
    
    <div class="push-down">
        <h2>New Customer Account</h2>
    </div>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal">
            <div class="form-group">
                <label for="mtrNbr" class="control-label col-md-4 caption">Account #:</label>
                <div class="col-md-8">
                    @Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "mtrNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="mtr" class="control-label col-md-4 caption">Water Meter:</label>
                <div class="col-md-8">
                    @Html.DropDownList("WaterMeterID", null, htmlAttributes: new { @class = "form-control", id = "mtr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="fName" class="control-label col-md-4 caption">First Name:</label>
                <div class="col-md-8">
                    @Html.TextBox("FirstName", null, htmlAttributes: new { @class = "form-control", id = "fName" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="lName" class="control-label col-md-4 caption">Last Name:</label>
                <div class="col-md-8">
                    @Html.TextBox("LastName", null, htmlAttributes: new { @class = "form-control", id = "lName" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="adrs" class="control-label col-md-4 caption">Address:</label>
                <div class="col-md-8">
                    @Html.TextBox("Address", null, htmlAttributes: new { @class = "form-control", id = "adrs" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="ct" class="control-label col-md-4 caption">City:</label>
                <div class="col-md-8">
                    @Html.TextBox("City", null, htmlAttributes: new { @class = "form-control", id = "ct" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="county" class="control-label col-md-4 caption">County:</label>
                <div class="col-md-8">
                    @Html.TextBox("County", null, htmlAttributes: new { @class = "form-control", id = "county" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="state" class="control-label col-md-4 caption">State:</label>
                <div class="col-md-8">
                    @Html.TextBox("State", null, htmlAttributes: new { @class = "form-control", id = "state" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="zip" class="control-label col-md-4 caption">ZIP Code:</label>
                <div class="col-md-8">
                    @Html.TextBox("ZIPCode", null, htmlAttributes: new { @class = "form-control", id = "zip" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-5">
                    @Html.ActionLink("Customers", "Index", null,
                htmlAttributes: new { @class = "water-nav" })
                </label>
                <div class="col-md-7">
                    <input type="submit" value="Create Customer Account" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  79. Click the CustomersController.cs tab
  80. Right-click inside one of the Edit() methods and click Add View...
  81. Make sure View Name displays Edit. Click Add
  82. Create a webpage as follows:
    @model WaterDistributionCompany1.Models.Customer
    
    @{
        ViewBag.Title = "Edit/Update Customer Information";
    }
    
    <div class="push-down">
        <h2>Edit/Update Customer Information</h2>
    </div>
    
    <hr />
    
    @using (Html.BeginForm())
    {
    <div class="form-horizontal common-font containment">
        @Html.HiddenFor(model => model.CustomerID)
    
        <div class="form-group">
            @Html.LabelFor(model => model.AccountNumber, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.AccountNumber, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="mtr" class="control-label col-md-4 caption">Water Meter:</label>
            <div class="col-md-8">
                @Html.DropDownList("WaterMeterID", null, htmlAttributes: new { @class = "form-control", id = "mtr" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.County, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.County, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.State, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.State, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.ZIPCode, htmlAttributes: new { @class = "control-label col-md-4 caption" })
            <div class="col-md-8">
                @Html.EditorFor(model => model.ZIPCode, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label class="control-label col-md-4 caption">
                @Html.ActionLink("Customers", "Index", null, new { @class = "water-nav" })
            </label>
            <div class="col-md-8">
                <input type="submit" value="Update Customer Information" class="btn btn-primary" />
            </div>
        </div>
    </div>
    }
  83. Click the CustomersController.cs tab
  84. Right-click inside one of the Delete() methods and click Add View...
  85. Make sure the View Name text box displays Delete. Click Add
  86. Create the webpage as follows:
    @model WaterDistributionCompany1.Models.Customer
    
    @{
        ViewBag.Title = "Remove Customer Account";
    }
    
    <div class="push-down">
        <h2>Cancelling Customer Account</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
            <dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Address)</dt>
            <dd>@Html.DisplayFor(model => model.Address)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.City)</dt>
            <dd>@Html.DisplayFor(model => model.City)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.County)</dt>
            <dd>@Html.DisplayFor(model => model.County)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.State)</dt>
            <dd>@Html.DisplayFor(model => model.State)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
            <dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
        </dl>
    
        <h3 class="common-font caption">Are you sure you want to cancel this customer's account?</h3>
    
        @using (Html.BeginForm())
        {
            <div class="form-actions no-color">
                <input type="submit" value="Delete this Customer's Account" class="btn btn-primary" /> |
                @Html.ActionLink("Customers Accounts", "Index", null, new { @class = "water-nav" })
            </div>
        }
    </div>
  87. Click the CustomersController.cs tab
  88. In the document, right-click Index() and click Add View...
  89. Make sure the View Name text box displays Index. Click Add
  90. Change the document as follows:
    @model IEnumerable<WaterDistributionCompany1.Models.Customer>
    
    @{
        ViewBag.Title = "Customers Accounts";
    }
    
    <div class="push-down">
        <h2 class="common-font bold text-center">Customers Accounts</h2>
    </div>
    
    <hr />
    
    <table class="table table-striped common-font">
        <tr>
            <th class="bold">Customer ID</th>
            <th class="bold">Account #</th>
            <th class="bold">Meter #</th>
            <th class="bold">First Name</th>
            <th class="bold">Last Name</th>
            <th class="bold">Address</th>
            <th class="bold">City</th>
            <th class="bold">County</th>
            <th class="bold">State</th>
            <th class="bold">ZIP Code</th>
            <th>@Html.ActionLink("New Customer Account", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
        </tr>
    
        @foreach (var item in Model)
        {
        <tr>
            <td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
            <td>@Html.DisplayFor(modelItem => item.AccountNumber)</td>
            <td>@Html.DisplayFor(modelItem => item.WaterMeterID)</td>
            <td>@Html.DisplayFor(modelItem => item.FirstName)</td>
            <td>@Html.DisplayFor(modelItem => item.LastName)</td>
            <td>@Html.DisplayFor(modelItem => item.Address)</td>
            <td>@Html.DisplayFor(modelItem => item.City)</td>
            <td>@Html.DisplayFor(modelItem => item.County)</td>
            <td>@Html.DisplayFor(modelItem => item.State)</td>
            <td>@Html.DisplayFor(modelItem => item.ZIPCode)</td>
            <td>
                @Html.ActionLink("Update", "Edit", new { id = item.CustomerID }) |
                @Html.ActionLink("Review", "Details", new { id = item.CustomerID }) |
                @Html.ActionLink("Remove", "Delete", new { id = item.CustomerID })
            </td>
        </tr>
        }
    </table>
  91. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    Joins Fundamentals

  92. In the webpage, click the Customers link
  93. Click the New Customer link

    Joins Fundamentals

  94. Create the following records:
     
    Account # Meter # First Name Last Name Address City County State ZIP Code
    9279-570-8394 799-28-461 Thomas Stones 10252 Broward Ave #D4 Frederick Frederick MD 21703
    4820-375-2842 392-44-572 Akhil Koumari 748 Red Hills Rd Roanoke   VA 24012
    7518-302-6895 207-94-835 Grace Brenner 4299 Peachtree Court Rockville Montgomery MD 20853
    2038-413-9680 938-75-869 Amidou Gomah 2075 Rose Hills Ave Washington   DC 20004
    5938-074-5293 592-84-957 Marie Rath 582G Dunhill Ave Lanham Prince Georges MD 20706

    Joins Fundamentals

    INSERT Customers(AccountNumber, WaterMeterID, FirstName, LastName, [Address], City, County, [State], ZIPCode)
    VALUES(N'9279-570-8394', 3, N'Thomas', N'Stones',  N'10252 Broward Ave #D4', N'Frederick',  N'Frederick',      N'MD', N'21703'),
          (N'4820-375-2842', 2, N'Akhil',  N'Koumari', N'748 Red Hills Rd',      N'Roanoke',    NULL,              N'VA', N'24012'), 
          (N'7518-302-6895', 4, N'Grace',  N'Brenner', N'4299 Peachtree Court',  N'Rockville',  N'Montgomery',     N'MD', N'20853'),
          (N'2038-413-9680', 1, N'Amidou', N'Gomah',   N'2075 Rose Hills Ave',   N'Washington', NULL,              N'DC', N'20004'),
          (N'5938-074-5293', 5, N'Marie',  N'Rath',    N'582G Dunhill Ave',      N'Lanham',     N'Prince Georges', N'MD', N'20706');
    GO
  95. Close the browser and return to your programming environment
  96. Close the Index.cshtml, the Delete.cshtml, the Edit.cshtml, the Details.cshtml, the Create.cshtml, the CustomersController.cs, and the Customer.cs tabs
  97. In the Solution Explorer, right-click Models -> Add -> Class...
  98. Type WaterBill as the name of the class
  99. Click Add
  100. Create the class as follows:
    using System;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistributionCompany1.Models
    {
        public class WaterBill
        {
            [Display(Name = "Water Bill ID")]
            public int WaterBillID { get; set; }
            [Display(Name = "Invoice #")]
            public int InvoiceNumber { get; set; }
            [Display(Name = "Customer ID")]
            public int CustomerID { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Meter Reading Start Date")]
            public DateTime MeterReadingStartDate { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Meter Reading End Date")]
            public DateTime MeterReadingEndDate { get; set; }
            [Display(Name = "Billing Days")]
            public int BillingDays { get; set; }
            [Display(Name = "Counter Reading Start")]
            public int CounterReadingStart { get; set; }
            [Display(Name = "Counter Reading End")]
            public int CounterReadingEnd { get; set; }
            [Display(Name = "Total HCF")]
            public int TotalHCF { get; set; }
            [Display(Name = "Total Gallons")]
            public int TotalGallons { get; set; }
            [Display(Name = "1st 15 HCF")]
            public decimal First15HCF { get; set; }
            [Display(Name = "Next 10 HCF")]
            public decimal Next10HCF { get; set; }
            [Display(Name = "Remaining 10 HCF")]
            public decimal RemainingHCF { get; set; }
            [Display(Name = "Sewer Charges")]
            public decimal SewerCharges { get; set; }
            [Display(Name = "Storm Charges")]
            public decimal StormCharges { get; set; }
            [Display(Name = "Water Usage Charges")]
            public decimal WaterUsageCharges { get; set; }
            [Display(Name = "Total Charges")]
            public decimal TotalCharges { get; set; }
            [Display(Name = "Local Taxes")]
            public decimal LocalTaxes { get; set; }
            [Display(Name = "State Taxes")]
            public decimal StateTaxes { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Pmt Due Date")]
            public DateTime PaymentDueDate { get; set; }
            [Display(Name = "Amt Due")]
            public decimal AmountDue { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Late Pmt Date")]
            public DateTime LatePaymentDueDate { get; set; }
            [Display(Name = "Late Pmt Amt")]
            public decimal LateAmountDue { get; set; }
        }
    }
  101. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  102. In the middle frame of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected. Click Add
  103. Type WaterBills to get WaterBillsController
  104. Click Add
  105. Change the document as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class WaterBillsController : Controller
        {
            List<WaterBill> waterBills = new List<WaterBill>();
    
            // GET: WaterBills
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, InvoiceNumber, " +
                                                              "       MeterReadingStartDate, MeterReadingEndDate, " +
                                                              "       BillingDays, CounterReadingStart, CounterReadingEnd, " +
                                                              "       TotalHCF, TotalGallons, First15HCF, Next10HCF, " +
                                                              "       RemainingHCF, SewerCharges, StormCharges, " +
                                                              "       WaterUsageCharges, TotalCharges, LocalTaxes, " +
                                                              "       StateTaxes, PaymentDueDate, AmountDue, " +
                                                              "       LatePaymentDueDate, LateAmountDue " +
                                                             "FROM WaterBills; ",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterBills.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
                    DataSet dsWaterBills = new DataSet("water-bill");
    
                    sdaWaterBills.Fill(dsWaterBills);
    
                    foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows)
                    {
                        WaterBill bill = new WaterBill();
    
                        bill.WaterBillID = int.Parse(drWaterBill[0].ToString());
                        bill.CustomerID = int.Parse(drWaterBill[1].ToString());
                        bill.InvoiceNumber = int.Parse(drWaterBill[2].ToString());
                        bill.MeterReadingStartDate = DateTime.Parse(drWaterBill[3].ToString());
                        bill.MeterReadingEndDate = DateTime.Parse(drWaterBill[4].ToString());
                        bill.BillingDays = int.Parse(drWaterBill[5].ToString());
                        bill.CounterReadingStart = int.Parse(drWaterBill[6].ToString());
                        bill.CounterReadingEnd = int.Parse(drWaterBill[7].ToString());
                        bill.TotalHCF = int.Parse(drWaterBill[8].ToString());
                        bill.TotalGallons = int.Parse(drWaterBill[9].ToString());
                        bill.First15HCF = decimal.Parse(drWaterBill[10].ToString());
                        bill.Next10HCF = decimal.Parse(drWaterBill[11].ToString());
                        bill.RemainingHCF = decimal.Parse(drWaterBill[12].ToString());
                        bill.SewerCharges = decimal.Parse(drWaterBill[13].ToString());
                        bill.StormCharges = decimal.Parse(drWaterBill[14].ToString());
                        bill.WaterUsageCharges = decimal.Parse(drWaterBill[15].ToString());
                        bill.TotalCharges = decimal.Parse(drWaterBill[16].ToString());
                        bill.LocalTaxes = decimal.Parse(drWaterBill[17].ToString());
                        bill.StateTaxes = decimal.Parse(drWaterBill[18].ToString());
                        bill.PaymentDueDate = DateTime.Parse(drWaterBill[19].ToString());
                        bill.AmountDue = decimal.Parse(drWaterBill[20].ToString());
                        bill.LatePaymentDueDate = DateTime.Parse(drWaterBill[21].ToString());
                        bill.LateAmountDue = decimal.Parse(drWaterBill[22].ToString());
    
                        waterBills.Add(bill);
                    }
                }
    
                return View(waterBills);
            }
    
            // GET: WaterBills/Details/5
            public ActionResult Details(int id)
            {
                WaterBill bill = new WaterBill();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, InvoiceNumber, " +
                                                              "       MeterReadingStartDate, MeterReadingEndDate, " +
                                                              "       BillingDays, CounterReadingStart, CounterReadingEnd, " +
                                                              "       TotalHCF, TotalGallons, First15HCF, Next10HCF, " +
                                                              "       RemainingHCF, SewerCharges, StormCharges, " +
                                                              "       WaterUsageCharges, TotalCharges, LocalTaxes, " +
                                                              "       StateTaxes, PaymentDueDate, AmountDue, " +
                                                              "       LatePaymentDueDate, LateAmountDue " +
                                                              "WHERE WaterBillID = " + id + " " +
                                                             "FROM WaterBills; ",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterBills.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
                    DataSet dsWaterBills = new DataSet("water-bill");
    
                    sdaWaterBills.Fill(dsWaterBills);
    
                    if (dsWaterBills.Tables[0].Rows.Count > 0)
                    {
                        bill.WaterBillID = int.Parse(dsWaterBills.Tables[0].Rows[0].ToString());
                        bill.CustomerID = int.Parse(dsWaterBills.Tables[0].Rows[1].ToString());
                        bill.InvoiceNumber = int.Parse(dsWaterBills.Tables[0].Rows[2].ToString());
                        bill.MeterReadingStartDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[3].ToString());
                        bill.MeterReadingEndDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[4].ToString());
                        bill.BillingDays = int.Parse(dsWaterBills.Tables[0].Rows[5].ToString());
                        bill.CounterReadingStart = int.Parse(dsWaterBills.Tables[0].Rows[6].ToString());
                        bill.CounterReadingEnd = int.Parse(dsWaterBills.Tables[0].Rows[7].ToString());
                        bill.TotalHCF = int.Parse(dsWaterBills.Tables[0].Rows[8].ToString());
                        bill.TotalGallons = int.Parse(dsWaterBills.Tables[0].Rows[9].ToString());
                        bill.First15HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[10].ToString());
                        bill.Next10HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[11].ToString());
                        bill.RemainingHCF = decimal.Parse(dsWaterBills.Tables[0].Rows[12].ToString());
                        bill.SewerCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[13].ToString());
                        bill.StormCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[14].ToString());
                        bill.WaterUsageCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[15].ToString());
                        bill.TotalCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[16].ToString());
                        bill.LocalTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[17].ToString());
                        bill.StateTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[18].ToString());
                        bill.PaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[19].ToString());
                        bill.AmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[20].ToString());
                        bill.LatePaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[21].ToString());
                        bill.LateAmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[22].ToString());
    
                        waterBills.Add(bill);
                    }
                }
    
                return View(bill);
            }
    
            // GET: WaterBills/StartMeterReading
            public ActionResult StartMeterReading()
            {
                return View();
            }
    
            // GET: WaterBills/PrepareMeterReading
            public ActionResult PrepareMeterReading(string AccountNumber, string MeterReadingEndDate, string CounterReadingEnd)
            {
                int numberOfDays = -1;
                Customer client = null;
                WaterMeter meter = null;
                Random rndNumber = new Random();
                bool validAccountNumber = false;
                int previousCounterReading = -1;
                DateTime previousReadingDate = new DateTime();
    
                // Open the table of Customers and check that the account number the user typed is valid
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers " +
                                                             "WHERE AccountNumber = N'" + AccountNumber + "';",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    if (dsCustomers.Tables[0].Rows.Count > 0)
                    {
                        validAccountNumber = true;
                        DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
    
                        client = new Customer
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID = int.Parse(drCustomer[2].ToString()),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
    
                // If the user provided a valid customer account number, ...
                if (validAccountNumber == true)
                {
                    // ... get the water meter used by the customer
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " +
                                                                   "       MeterSize, DateLastUpdate, CounterValue " +
                                                                   "FROM WaterMeters " +
                                                                   "WHERE WaterMeterID = " + client.WaterMeterID + ";",
                                                                   scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdWaterMeters.ExecuteNonQuery();
    
                        SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                        DataSet dsWaterMeters = new DataSet("water-meters");
    
                        sdaWaterMeters.Fill(dsWaterMeters);
    
                        if (dsWaterMeters.Tables[0].Rows.Count > 0)
                        {
                            meter = new WaterMeter
                            {
                                WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()),
                                MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(),
                                Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(),
                                Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(),
                                MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(),
                                DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()),
                                CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString())
                            };
    
                            /* From the table of water meters, we want the counter value of the water meter that 
                             * * is used by the customer. We will use that value as the meter reading start in 
                             * * case this is the first time a water bill is created for the customer. */
                            previousReadingDate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString());
                            previousCounterReading = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString());
                        }
                    }
                }
    
                /* If the table of water bills contains at least one record, open it; 
                 * but most importantly, if there are any records, get the record(s) 
                 * of the account number the user provided in the form. */
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, MeterReadingEndDate, CounterReadingEnd " +
                                                              "FROM WaterBills " +
                                                              "WHERE CustomerID = " + client.CustomerID + "; ",
                                                              scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterBills.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
                    DataSet dsCustomers = new DataSet("water-bill");
    
                    sdaWaterBills.Fill(dsCustomers);
    
                    /* If at one water bill was previously created for the customer, 
                     * get the last CounterReadingEnd and make it the previous counter reading value. */
                    foreach (DataRow drWaterBill in dsCustomers.Tables[0].Rows)
                    {
                        previousReadingDate = DateTime.Parse(drWaterBill[2].ToString());
                        previousCounterReading = int.Parse(drWaterBill[3].ToString());
                    }
                }
    
                TimeSpan tsDays = new TimeSpan();
                DateTime dtMeterReadingStartDate = previousReadingDate;
                DateTime dtMeterReadingEndDate = DateTime.Parse(MeterReadingEndDate);
                tsDays = dtMeterReadingEndDate - dtMeterReadingStartDate;
                numberOfDays = tsDays.Days;
    
                int totalHCF = int.Parse(CounterReadingEnd) - previousCounterReading;
                decimal first15HCF = totalHCF * 3.612M;
                decimal next10HCF = 0, remainingHCF = 0;
    
                if (totalHCF <= 15)
                {
                    first15HCF = totalHCF * 3.612M;
                    next10HCF = 0;
                    remainingHCF = 0;
                }
                else if (totalHCF <= 25M)
                {
                    first15HCF = 15M * 3.612M;
                    next10HCF = (totalHCF - 15M) * 3.918M;
                    remainingHCF = 0;
                }
                else
                {
                    first15HCF = 15M * 3.612M;
                    next10HCF = 10M * 3.918M;
                    remainingHCF = (totalHCF - 25M) * 2.2763M;
                }
    
                decimal waterUsageCharges = first15HCF + next10HCF + remainingHCF;
                decimal sewerCharges = waterUsageCharges * 0.252M;
                decimal stormCharges = waterUsageCharges * 0.0025M;
                decimal totalCharges = waterUsageCharges + sewerCharges + stormCharges;
                decimal LocalTaxes = totalCharges * 0.005M;
                decimal stateTaxes = totalCharges * 0.0152M;
                decimal amountDue = totalCharges + LocalTaxes + stateTaxes;
    
                ViewData["CustomerID"] = client.CustomerID;
                ViewBag.City = client.City;
                ViewBag.State = client.State;
                ViewBag.County = client.County;
                ViewBag.ZIPCode = client.ZIPCode;
                ViewBag.MeterDetails = meter.Description;
                ViewBag.CustomerAddress = client.Address;
                ViewBag.CustomerName = client.FirstName + " " + client.LastName;
                ViewBag.InvoiceNumber = rndNumber.Next(100001, 999999).ToString();
    
                ViewBag.TotalHCF = totalHCF;
                ViewBag.AmountDue = amountDue.ToString("F");
                ViewBag.Next10HCF = next10HCF.ToString("F");
                ViewBag.StateTaxes = stateTaxes.ToString("F");
                ViewBag.First15HCF = first15HCF.ToString("F");
                ViewBag.LocalTaxes = LocalTaxes.ToString("F");
                ViewBag.SewerCharges = sewerCharges.ToString("F");
                ViewBag.StormCharges = stormCharges.ToString("F");
                ViewBag.RemainingHCF = remainingHCF.ToString("F");
                ViewBag.TotalCharges = totalCharges.ToString("F");
                ViewBag.LateAmountDue = (amountDue + 8.95M).ToString("F");
                ViewBag.WaterUsageCharges = waterUsageCharges.ToString("F");
                ViewBag.BillingDays = numberOfDays.ToString();
                ViewBag.CounterReadingStart = previousCounterReading.ToString();
                ViewBag.MeterReadingStartDate = previousReadingDate.ToShortDateString();
                ViewBag.PaymentDueDate = dtMeterReadingEndDate.AddDays(28).ToShortDateString();
                ViewBag.LatePaymentDueDate = dtMeterReadingEndDate.AddDays(45).ToShortDateString();
                ViewBag.TotalGallons = (int.Parse(CounterReadingEnd) - previousCounterReading) * 748; // 748.05
    
                return View();
            }
    
            // GET: WaterBills/Create
            public ActionResult Create()
            {
                return View();
            }
    
            // POST: WaterBills/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    // Save the water bill based on the values from the form
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdWaterBills = new SqlCommand("INSERT INTO WaterBills(CustomerID, InvoiceNumber, MeterReadingStartDate, " +
                                                                  "                       MeterReadingEndDate, BillingDays, CounterReadingStart, " +
                                                                  "                       CounterReadingEnd, TotalHCF, TotalGallons, First15HCF, " +
                                                                  "                       Next10HCF, RemainingHCF, SewerCharges, StormCharges, " +
                                                                  "                       WaterUsageCharges, TotalCharges, LocalTaxes, StateTaxes, " +
                                                                  "                       PaymentDueDate, AmountDue, LatePaymentDueDate, LateAmountDue) " +
                                                                  "VALUES(" + collection["CustomerID"] + ", " + collection["InvoiceNumber"] + ", N'" +
                                                                  collection["MeterReadingStartDate"] + "', N'" + collection["MeterReadingEndDate"] +
                                                                  "', " + collection["BillingDays"] + ", " + collection["CounterReadingStart"] +
                                                                  ", " + collection["CounterReadingEnd"] + ", " + collection["TotalHCF"] + ", " +
                                                                  collection["TotalGallons"] + ", " + collection["First15HCF"] + ", " +
                                                                  collection["Next10HCF"] + ", " + collection["RemainingHCF"] + ", " +
                                                                  collection["SewerCharges"] + ", " + collection["StormCharges"] + ", " +
                                                                  collection["WaterUsageCharges"] + ", " + collection["TotalCharges"] + ", " +
                                                                  collection["LocalTaxes"] + ", " + collection["StateTaxes"] + ", N'" +
                                                                  collection["PaymentDueDate"] + "', " + collection["AmountDue"] + ", N'" +
                                                                  collection["LatePaymentDueDate"] + "', " + collection["LateAmountDue"] + ");",
                                                                  scWaterDistribution);
                        scWaterDistribution.Open();
                        cmdWaterBills.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View("Index");
                }
            }
    
            // GET: WaterBills/Edit/5
            public ActionResult Edit(int id)
            {
                return View();
            }
    
            // POST: WaterBills/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: WaterBills/Delete/5
            public ActionResult Delete(int id)
            {
                return View();
            }
    
            // POST: WaterBills/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
    
    
  106. In the class, right-click inside the Index() method and click Add View...
  107. Make sure the View Name text box displays Index. Press Enter
  108. Change the webpage as follows:
    @model IEnumerable<WaterDistributionCompany1.Models.WaterBill>
    
    @{
        ViewBag.Title = "Water Bills";
    }
    
    <div class="push-down">
        <h2>Water Bills</h2>
    </div>
    
    <hr />
    
    <table class="table table-striped common-font">
        <tr>
            <th class="bold text-center">Water Bill ID</th>
            <th class="bold text-center">Invoice #</th>
            <th class="bold text-center">Customer ID</th>
            <th class="bold text-center">Meter Reading Start Date</th>
            <th class="bold text-center">Meter Reading End Date</th>
            <th class="bold">Billing Days</th>
            <th class="bold">Counter Reading Start</th>
            <th class="bold">Counter Reading End</th>
            <th class="bold">Total HCF</th>
            <th class="bold">Total Gallons</th>
            <th class="bold">First 15 HCF</th>
            <th class="bold text-center">Next 10 HCF</th>
            <th class="bold text-center">Remaining HCF</th>
            <th class="bold text-center">Sewer Charges</th>
            <th class="bold text-center">Storm Charges</th>
            <th class="bold text-center">Water Usage Charges</th>
            <th class="bold text-center">Total Charges</th>
            <th class="bold text-center">Local Taxes</th>
            <th class="bold text-center">State Taxes</th>
            <th class="bold text-center">Payment Due Date</th>
            <th class="bold text-center">Amount Due</th>
            <th class="bold text-center">Late Payment Due Date</th>
            <th class="bold text-center">Late Amount Due</th>
            <th>@Html.ActionLink("New Water Bill", "StartMeterReading", null, htmlAttributes: new { @class = "water-nav" })</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">@Html.DisplayFor(modelItem => item.WaterBillID)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.InvoiceNumber)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.MeterReadingStartDate)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.MeterReadingEndDate)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.BillingDays)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.CounterReadingStart)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.CounterReadingEnd)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.TotalHCF)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.TotalGallons)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.First15HCF)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.Next10HCF)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.RemainingHCF)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.SewerCharges)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.StormCharges)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.WaterUsageCharges)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.TotalCharges)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.LocalTaxes)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.StateTaxes)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.PaymentDueDate)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.AmountDue)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.LatePaymentDueDate)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.LateAmountDue)</td>
                <td>
                    @Html.ActionLink("Update", "Edit", new { id = item.WaterBillID }) |
                    @Html.ActionLink("Review", "Details", new { id = item.WaterBillID }) |
                    @Html.ActionLink("Remove", "Delete", new { id = item.WaterBillID })
                </td>
            </tr>
        }
    </table>
  109. Click the WaterBillsController.cs tab
  110. In the document, right-click Details() and click Add View
  111. Make sure the View Name text box displays Details. Click Add
  112. Change the code as follows:
    @model WaterDistributionCompany1.Models.WaterBill
    
    @{
        ViewBag.Title = "Water Bill Details";
    }
    
    <div class="push-down">
        <h2>Water Bill Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.WaterBillID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterBillID)</dd>
            <dt>@Html.DisplayNameFor(model => model.InvoiceNumber)</dt>
            <dd>@Html.DisplayFor(model => model.InvoiceNumber)</dd>
            <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
            <dt>@Html.DisplayNameFor(model => model.MeterReadingStartDate)</dt>
            <dd>@Html.DisplayFor(model => model.MeterReadingStartDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.MeterReadingEndDate)</dt>
            <dd>@Html.DisplayFor(model => model.MeterReadingEndDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.BillingDays)</dt>
            <dd>@Html.DisplayFor(model => model.BillingDays)</dd>
            <dt>@Html.DisplayNameFor(model => model.CounterReadingStart)</dt>
            <dd>@Html.DisplayFor(model => model.CounterReadingStart)</dd>
            <dt>@Html.DisplayNameFor(model => model.CounterReadingEnd)</dt>
            <dd>@Html.DisplayFor(model => model.CounterReadingEnd)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalHCF)</dt>
            <dd>@Html.DisplayFor(model => model.TotalHCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalGallons)</dt>
            <dd>@Html.DisplayFor(model => model.TotalGallons)</dd>
            <dt>@Html.DisplayNameFor(model => model.First15HCF)</dt>
            <dd>@Html.DisplayFor(model => model.First15HCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.Next10HCF)</dt>
            <dd>@Html.DisplayFor(model => model.Next10HCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.RemainingHCF)</dt>
            <dd>@Html.DisplayFor(model => model.RemainingHCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.SewerCharges)</dt>
            <dd>@Html.DisplayFor(model => model.SewerCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.StormCharges)</dt>
            <dd>@Html.DisplayFor(model => model.StormCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.WaterUsageCharges)</dt>
            <dd>@Html.DisplayFor(model => model.WaterUsageCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalCharges)</dt>
            <dd>@Html.DisplayFor(model => model.TotalCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.LocalTaxes)</dt>
            <dd>@Html.DisplayFor(model => model.LocalTaxes)</dd>
            <dt>@Html.DisplayNameFor(model => model.StateTaxes)</dt>
            <dd>@Html.DisplayFor(model => model.StateTaxes)</dd>
            <dt>@Html.DisplayNameFor(model => model.PaymentDueDate)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentDueDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.AmountDue)</dt>
            <dd>@Html.DisplayFor(model => model.AmountDue)</dd>
            <dt>@Html.DisplayNameFor(model => model.LatePaymentDueDate)</dt>
            <dd>@Html.DisplayFor(model => model.LatePaymentDueDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.LateAmountDue)</dt>
            <dd>@Html.DisplayFor(model => model.LateAmountDue)</dd>
        </dl>
    </div>
    
    <p class="text-center">
        @Html.ActionLink("Edit/Update Water Bill Information", "Edit",
                         new { id = Model.WaterBillID, @class = "water-nav" },
                         new { @class = "water-nav" }) |
        @Html.ActionLink("Water Meters", "Index",
                         null, new { @class = "water-nav" })
    </p>
  113. Click the WaterBillsController.cs tab
  114. In the document, right-click the public ActionResult StartMeterReading() line and click Add View...
  115. Make sure the View Name text box displays StartMeterReading. Click Add
  116. Create a small form as follows:
    @{
        ViewBag.Title = "Meter Reading Start-Up";
    }
    
    <div class="push-down">
        <h2>Meter Reading Start-Up</h2>
    </div>
    
    @using (Html.BeginForm("PrepareMeterReading", "WaterBills", FormMethod.Post))
    {
        <div class="form-horizontal">
            <div class="form-group">
                <label for="acntNbr" class="control-label col-md-4 caption">Customer Account #:</label>
                <div class="col-md-8">
                    @Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "acntNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="mred" class="control-label col-md-4 caption">Meter Reading Date:</label>
                <div class="col-md-8">
                    @Html.TextBox("MeterReadingEndDate", null, 
                                  htmlAttributes: new { @class = "form-control", type = "date", id = "mred" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="cre" class="control-label col-md-4 caption">Current Meter Reading:</label>
                <div class="col-md-8">
                    @Html.TextBox("CounterReadingEnd", null, htmlAttributes: new { @class = "form-control", id = "cre" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-5">
                    @Html.ActionLink("Water Meters", "Index", null,
                htmlAttributes: new { @class = "water-nav" })
                </label>
                <div class="col-md-7">
                    <input type="submit" value="Prepare Meter Reading" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  117. In the Solution Explorer, under Views, right-click WaterBills -> Add -> View...
  118. Type PrepareMeterReading as the name of the view
  119. Press Enter
  120. Create a form as follows:
    @{
        ViewBag.Title = "Prepare Meter Reading";
    }
    
    <div class="push-down">
        <h2>Prepare Meter Reading</h2>
    </div>
    
    <hr />
    
    @using (Html.BeginForm("Create", "WaterBills", FormMethod.Post))
    {
    <div class="form-horizontal">
        <div class="form-group">
            <label for="billNbr" class="control-label col-md-4 caption">Water Bill #:</label>
            <div class="col-md-8">
                @Html.TextBox("InvoiceNumber", null, htmlAttributes: new { @class = "form-control", id = "billNbr" })
            </div>
        </div>
        <div class="form-group">
            <label for="acntNbr" class="control-label col-md-4 caption">Customer Account #:</label>
            <div class="col-md-8">
                @Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "acntNbr", disabled = "disabled" })
                @Html.Hidden("CustomerID", ViewData["CustomerID"] as string)
            </div>
        </div>
    
        <div class="form-group">
            <label for="mred" class="control-label col-md-4 caption">Customer Name:</label>
            <div class="col-md-8">
                @Html.TextBox("CustomerName", @ViewBag.CustomerName as string, new { @class = "form-control", disabled = "disabled" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4 caption">Customer Address:</label>
            <div class="col-md-8">
                @Html.TextBox("CustomerAddress", @ViewBag.CustomerAddress as string, new { @class = "form-control", disabled = "disabled" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4">&nbsp;</label>
            <div class="col-md-125">
                @Html.TextBox("CustomerCity", @ViewBag.City as string, new { @class = "form-control", disabled = "disabled" })
            </div>
            <div class="col-md-125">
                @Html.TextBox("CustomerCounty", @ViewBag.County as string, new { @class = "form-control", disabled = "disabled" })
            </div>
            <div class="col-md-125">
                @Html.TextBox("CustomerState", @ViewBag.State as string, new { @class = "form-control", disabled = "disabled" })
            </div>
            <div class="col-md-125">
                @Html.TextBox("CustomerZIPCode", @ViewBag.ZIPCode as string, new { @class = "form-control", disabled = "disabled" })
            </div>
        </div>
    
        <div class="form-group">
            <label class="control-label col-md-4 caption">Meter Details:</label>
            <div class="col-md-8">
                @Html.TextBox("MeterDetails", ViewBag.MeterDetails as string, htmlAttributes: new { @class = "form-control", disabled = "disabled" })
            </div>
        </div>
        <hr />
        <div class="form-group">
            <label for="mrsd" class="control-label col-md-4 caption">Meter Reading Start Date:</label>
            <div class="col-md-125">
                @Html.TextBox("MeterReadingStartDate", ViewBag.MeterReadingStartDate as string,
                              htmlAttributes: new { @class = "form-control", id = "mrsd" })
            </div>
            <label for="mred" class="control-label col-md-125 caption">Meter Reading End Date:</label>
            <div class="col-md-125">
                @Html.TextBox("MeterReadingEndDate", null,
                              htmlAttributes: new { @class = "form-control", type = "date", id = "mred" })
            </div>
            <label for="days" class="control-label col-md-125 caption">Billing Days:</label>
            <div class="col-md-125">
                @Html.TextBox("BillingDays", ViewBag.BillingDays as string, htmlAttributes: new { @class = "form-control", id = "days" })
            </div>
        </div>
    
        <div class="form-group">
            <label for="crs" class="control-label col-md-4 caption">Counter Reading Start:</label>
            <div class="col-md-125">
                @Html.TextBox("CounterReadingStart", ViewBag.CounterReadingStart as string, htmlAttributes: new { @class = "form-control", id = "crs" })
            </div>
            <label for="cre" class="control-label col-md-125 caption">Current Meter Reading:</label>
            <div class="col-md-125">
                @Html.TextBox("CounterReadingEnd", null, htmlAttributes: new { @class = "form-control", id = "cre" })
            </div>
            <label for="thcf" class="control-label col-md-125 caption">Total HCF:</label>
            <div class="col-md-125">
                @Html.TextBox("TotalHCF", ViewBag.TotalHCF as string, htmlAttributes: new { @class = "form-control", id = "thcf" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label class="control-label col-md-125">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label for="gallons" class="control-label col-md-125 caption">Total Gallons:</label>
            <div class="col-md-125">
                @Html.TextBox("TotalGallons", ViewBag.TotalGallons as string, htmlAttributes: new { @class = "form-control", id = "gallons" })
            </div>
        </div>
    
        <div class="form-group">
            <label for="f15HCF" class="control-label col-md-4 caption">1st 15 HCF at $3.6121:</label>
            <div class="col-md-125">
                @Html.TextBox("First15HCF", ViewBag.First15HCF as string,
                              htmlAttributes: new { @class = "form-control", id = "f15HCF" })
            </div>
            <label for="next10HCF" class="control-label col-md-125 caption">Next 10 HCF at $3.9180:</label>
            <div class="col-md-125">
                @Html.TextBox("Next10HCF", ViewBag.Next10HCF as string,
                              htmlAttributes: new { @class = "form-control", id = "next10HCF" })
            </div>
            <label for="days" class="control-label col-md-125 caption">Remaining HCF at $4.2763:</label>
            <div class="col-md-125">
                @Html.TextBox("RemainingHCF", ViewBag.RemainingHCF as string, htmlAttributes: new { @class = "form-control", id = "days" })
            </div>
        </div>
        <div class="form-group">
            <label for="sewerCharges" class="control-label col-md-4 caption">Sewer Charges:</label>
            <div class="col-md-125">
                @Html.TextBox("SewerCharges", ViewBag.SewerCharges as string,
                              htmlAttributes: new { @class = "form-control", id = "sewerCharges" })
            </div>
            <label for="stormCharges" class="control-label col-md-125 caption">Storm Charges:</label>
            <div class="col-md-125">
                @Html.TextBox("StormCharges", ViewBag.StormCharges as string,
                              htmlAttributes: new { @class = "form-control", id = "stormCharges" })
            </div>
            <label for="wuc" class="control-label col-md-125 caption">Water Usage Charges:</label>
            <div class="col-md-125">
                @Html.TextBox("WaterUsageCharges", ViewBag.WaterUsageCharges as string, htmlAttributes: new { @class = "form-control", id = "wuc" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label class="control-label col-md-125">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label for="totalCharges" class="control-label col-md-125 caption">Total Charges:</label>
            <div class="col-md-125">
                @Html.TextBox("TotalCharges", ViewBag.TotalCharges as string, htmlAttributes: new { @class = "form-control", id = "totalCharges" })
            </div>
        </div>
        <hr />
        <div class="form-group">
            <label class="control-label col-md-4">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label for="LocalTaxes" class="control-label col-md-125 caption">Local Taxes:</label>
            <div class="col-md-125">
                @Html.TextBox("LocalTaxes", ViewBag.LocalTaxes as string,
                              htmlAttributes: new { @class = "form-control", id = "LocalTaxes" })
            </div>
            <label for="stateTaxes" class="control-label col-md-125 caption">State Taxes:</label>
            <div class="col-md-125">
                @Html.TextBox("StateTaxes", null, htmlAttributes: new { @class = "form-control", id = "stateTaxes" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label for="pdd" class="control-label col-md-125 caption">Payment Due Date:</label>
            <div class="col-md-125">
                @Html.TextBox("PaymentDueDate", null,
                              htmlAttributes: new { @class = "form-control", id = "pdd" })
            </div>
            <label for="amtDue" class="control-label col-md-125 caption">Amount Due:</label>
            <div class="col-md-125">
                @Html.TextBox("AmountDue", null,
                              htmlAttributes: new { @class = "form-control", id = "amtDue" })
            </div>
        </div>
        <div class="form-group">
            <label class="control-label col-md-4 caption">&nbsp;</label>
            <div class="col-md-125">&nbsp;</div>
            <label for="lpdd" class="control-label col-md-125 caption">Late Payment Due Date:</label>
            <div class="col-md-125">
                @Html.TextBox("LatePaymentDueDate", null,
                              htmlAttributes: new { @class = "form-control", id = "lpdd" })
            </div>
            <label for="lateAmtDue" class="control-label col-md-125 caption">Late Amount Due:</label>
            <div class="col-md-125">
                @Html.TextBox("LateAmountDue", null, htmlAttributes: new { @class = "form-control", id = "lateAmtDue" })
            </div>
        </div>
    
        <div class="form-group text-center">
            <label class="control-label col-md-5">
                @Html.ActionLink("Water Meters", "Index", null, htmlAttributes: new { @class = "water-nav" })
            </label>
            <div class="col-md-7">
                <input type="submit" value="Save Water Bill" class="btn btn-primary" />
            </div>
        </div>
    </div>
    }
  121. In the Solution Explorer, under Controllers, double-click HomeController.cs
  122. Change the document as follows:
    using System.Web.Mvc;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class HomeController : Controller
        {
            public ActionResult Index()
            {
                return View();
            }
    
            public ActionResult About()
            {
                ViewBag.Message = "We supply water to the whole region in a friendly and responsible manner.";
    
                return View();
            }
    
            public ActionResult Contact()
            {
                ViewBag.Message = "Contact us for all your water concerns or issues.";
    
                return View();
            }
        }
    }
  123. To execute the application, on the main menu, click Debug -> Start Without Debugging
  124. Click the Bills/Invoices button

    Label

  125. Click the New Water Bill link

    Joins Fundamentals

    Joins Fundamentals

  126. Create a few water bills as follows:
     
    Customer Account # Meter Reading Date Current Meter Reading
    7518-302-6895 07/30/2019 114
    4820-375-2842 07/31/2019 109998
    2038-413-9680 07/30/2019 137975
    9279-570-8394 7-Aug-19 6275
    7518-302-6895 7-Nov-19 118
    2038-413-9680 27-Oct-19 137958
    9279-570-8394 3-Nov-19 6295
    4820-375-2842 29-Oct-19 110012
    9279-570-8394 2/2/2019 6312
    7518-302-6895 2/19/2019 126
    5938-074-5293 10/2/2019 52
    4820-375-2842 2/2/2019 110027
    2038-413-9680 1/28/2019 138054
  127. Close the browser and return to your programming environment

Joins Fundamentals

Introduction

A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

  1. Specifying the tables that will be involved in the join
  2. Specifying a column that will create the link in each table
  3. Creating or writing a SQL statement that will produce the records

Using a Shared Field to Join Tables

One of the most important features of a relational database consists of combining records from various tables to get a single list. The SQL provides two main options: Applying a condition on a common field or creating a join.

The primary way to join two or more tables to create a common list that combines their records is to match the records they have in common. Before doing this, the lists must have a field used as the primary key on one table and a foreign key on the other table. The formula to follow is:

SELECT field(s) [, field(s)] FROM table_1, table_2
WHERE condition

Create a SELECT statement to select fields from one or all tables. Then use a WHERE condition to specify how the records will be matched. Here is an example:

CREATE TABLE Departments
(
    DeptCode varchar(7),
    DepartmentName varchar(50)
);
GO
CREATE TABLE Employees
(
    EmployeeNumber varchar(10),
    FirstName varchar(24),
    LastName varchar(24),
    DeptCode varchar(7),
);
GO
INSERT INTO Departments 
VALUES(N'HMNRS', N'Human Resources'),
      (N'ITECH', N'Information Technology'), 
      (N'RESDV', N'Research and Development');
GO
INSERT INTO Employees 
VALUES(N'937-497', N'Frank', N'Salomons', N'ITECH'), 
      (N'284-957', N'Chrissie', N'Siegel', NULL),
      (N'204-805', N'Violet', N'Calhoun', N'HMNRS'), 
      (N'927-394', N'James', N'Waller', N'RESDV'), 
      (N'294-793', N'John', N'Harding', N'HMNRS'), 
      (N'920-794', N'Joan', N'Thomason', NULL), 
      (N'395-004', N'Alexander', N'Lamm', N'HMNRS'), 
      (N'305-804', N'Anna', N'Danielson', NULL);
GO
SELECT EmployeeNumber,
       FirstName,
       LastName,
       DepartmentName
FROM Employees, Departments
WHERE Employees.DeptCode = Departments.DeptCode;
GO

Using a Shared Field to Join Tables

Practical LearningPractical Learning: Using a Shared Field to Join Tables

  1. In the Solution Explorer, under Models, double-click Customer.cs
  2. Inherit a class as follows:
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistributionCompany1.Models
    {
        public class Customer
        {
            [Display(Name = "Customer ID")]
            public int CustomerID       { get; set; }
            [Display(Name = "Account #")]
            public string AccountNumber { get; set; }
            [Display(Name = "Water Meter")]
            public int WaterMeterID { get; set; }
            [Display(Name = "First Name")]
            public string FirstName     { get; set; }
            [Display(Name = "Last Name")]
            public string LastName      { get; set; }
            public string Address       { get; set; }
            public string City          { get; set; }
            public string County        { get; set; }
            public string State         { get; set; }
            [Display(Name = "ZIP Code")]
            public string ZIPCode       { get; set; }
        }
    
        public class Client : Customer
        {
            public string WaterMeter { get; set; }
        }
    }
  3. In the Solution, under Controllers, double-click CustomersController.cs
  4. Change the class as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class CustomersController : Controller
        {
            List<Client> clients = new List<Client>();
            List<Customer> customers = new List<Customer>();
    
            // GET: Customers
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
                                                             "       WaterMeters.MeterNumber + N' - ' + WaterMeters.Make  + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers, WaterMeters " +
                                                             "WHERE Customers.WaterMeterID = WaterMeters.WaterMeterID; ",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    DataTable dtCustomers = dsCustomers.Tables[0];
    
                    for (int i = 0; i < dtCustomers.Rows.Count; i++)
                    {
                        DataRow drCustomer = dtCustomers.Rows[i];
    
                        clients.Add(new Client()
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeter = drCustomer[2].ToString(),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        });
                    }
                }
    
                return View(clients);
            }
    
            . . . No Change
        }
    }
  5. In the document, right-click Index() and click Go To View
  6. Change the webpage as follows:
    @model IEnumerable<WaterDistributionCompany1.Models.Client>
    
    @{
        ViewBag.Title = "Customers Accounts";
    }
    
    <div class="push-down">
        <h2 class="common-font bold text-center">Customers Accounts</h2>
    </div>
    
    <hr />
    
    <table class="table table-striped common-font">
        <tr>
            <th class="bold">Customer ID</th>
            <th class="bold">Account #</th>
            <th class="bold">Water Meter</th>
            <th class="bold">First Name</th>
            <th class="bold">Last Name</th>
            <th class="bold">Address</th>
            <th class="bold">City</th>
            <th class="bold">County</th>
            <th class="bold">State</th>
            <th class="bold">ZIP Code</th>
            <th>@Html.ActionLink("New Customer Account", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
                <td>@Html.DisplayFor(modelItem => item.AccountNumber)</td>
                <td>@Html.DisplayFor(modelItem => item.WaterMeter)</td>
                <td>@Html.DisplayFor(modelItem => item.FirstName)</td>
                <td>@Html.DisplayFor(modelItem => item.LastName)</td>
                <td>@Html.DisplayFor(modelItem => item.Address)</td>
                <td>@Html.DisplayFor(modelItem => item.City)</td>
                <td>@Html.DisplayFor(modelItem => item.County)</td>
                <td>@Html.DisplayFor(modelItem => item.State)</td>
                <td>@Html.DisplayFor(modelItem => item.ZIPCode)</td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.CustomerID }) |
                    @Html.ActionLink("Review", "Details", new { id = item.CustomerID }) |
                    @Html.ActionLink("Delete", "Delete", new { id = item.CustomerID })
                </td>
            </tr>
        }
    </table>
  7. To execute the application, on the main menu, click Debug -> Start Without Debugging:

    Joins Fundamentals

Introduction to Conditional Joins

When creating a join, if you are using a WHERE clause, you can add one or more conditions to select only the necessary records.

Practical LearningPractical Learning: Introducing Conditional Joins

  1. In the Solution Explorer, under Controllers, double-click CustomersController.cs
  2. Change the Details() and the Delete() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class CustomersController : Controller
        {
            List<Client> clients = new List<Client>();
    
            // GET: Customers
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
                                                             "       WaterMeters.MeterNumber + N' - ' + WaterMeters.Make  + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers, WaterMeters " +
                                                             "WHERE Customers.WaterMeterID = WaterMeters.WaterMeterID;",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    DataTable dtCustomers = dsCustomers.Tables[0];
    
                    for (int i = 0; i < dtCustomers.Rows.Count; i++)
                    {
                        DataRow drCustomer = dtCustomers.Rows[i];
    
                        clients.Add(new Client()
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeter = drCustomer[2].ToString(),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        });
                    }
                }
    
                return View(clients);
            }
    
            // GET: Customers/Details/5
            public ActionResult Details(int id)
            {
                Client client = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
                                                             "       WaterMeters.MeterNumber + N' - ' + WaterMeters.Make  + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers, WaterMeters " +
                                                             "WHERE (CustomerID = " + id + ") AND (Customers.WaterMeterID = WaterMeters.WaterMeterID);",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    DataTable dtCustomers = dsCustomers.Tables[0];
    
                    if (dtCustomers.Rows.Count > 0)
                    {
                        DataRow drCustomer = dtCustomers.Rows[0];
    
                        client = new Client
                        {
    
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeter = drCustomer[2].ToString(),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
    
                return View(client);
            }
    
            // GET: Customers/Create
            public ActionResult Create()
            {
                List<WaterMeter> waterMeters = new List<WaterMeter>();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters;",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    WaterMeter meter = null;
    
                    for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
                        };
    
                        waterMeters.Add(meter);
                    }
                }
    
                ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description");
    
                return View();
            }
    
            // POST: Customers/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 cmdCustomers = new SqlCommand("INSERT INTO Customers(AccountNumber, WaterMeterID, " +
                                                                 "                      FirstName, LastName, Address, City, " +
                                                                 "                      County, State, ZIPCode) " +
                                                                 "VALUES(N'" + collection["AccountNumber"] + "', " +
                                                                 collection["WaterMeterID"] + ", N'" + collection["FirstName"] +
                                                                 "', N'" + collection["LastName"] + "', N'" +
                                                                 collection["Address"] + "', N'" + collection["City"] + "', N'" +
                                                                 collection["County"] + "', N'" + collection["State"] + "', N'" +
                                                                 collection["ZIPCode"] + "');",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Customers/Edit/5
            public ActionResult Edit(int id)
            {
                Customer client = null;
                WaterMeter meter = null;
                List<WaterMeter> waterMeters = new List<WaterMeter>();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers " +
                                                             "WHERE CustomerID = " + id + ";",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    if (dsCustomers.Tables[0].Rows.Count > 0)
                    {
                        DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
    
                        client = new Customer
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeterID = int.Parse(drCustomer[2].ToString()),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " +
                                                               "       MeterSize, DateLastUpdate, CounterValue " +
                                                               "FROM WaterMeters;",
                                                               scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterMeters.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
                    DataSet dsWaterMeters = new DataSet("water-meters");
    
                    sdaWaterMeters.Fill(dsWaterMeters);
    
                    for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
                    {
                        meter = new WaterMeter
                        {
                            WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
                            MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
                            Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
                            Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
                            MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
                            DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
                            CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
                        };
    
                        waterMeters.Add(meter);
                    }
                }
    
                ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description", client.WaterMeterID);
    
                return View(client);
            }
    
            // POST: Customers/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdCustomers = new SqlCommand("UPDATE Customers " +
                                                                 "SET AccountNumber = N'" + collection["AccountNumber"] + "'," +
                                                                 "    WaterMeterID  = " + collection["WaterMeterID"] + "," +
                                                                 "    FirstName     = N'" + collection["FirstName"] + "'," +
                                                                 "    LastName      = N'" + collection["LastName"] + "'," +
                                                                 "    Address       = N'" + collection["Address"] + "'," +
                                                                 "    City          = N'" + collection["City"] + "'," +
                                                                 "    County        = N'" + collection["County"] + "'," +
                                                                 "    State         = N'" + collection["State"] + "'," +
                                                                 "    ZIPCode       = N'" + collection["ZIPCode"] + "' " +
                                                                 "WHERE CustomerID = " + id + ";",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Customers/Delete/5
            public ActionResult Delete(int id)
            {
                Client client = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
                                                             "       WaterMeters.MeterNumber + N' - ' + WaterMeters.Make  + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
                                                             "       FirstName, LastName, Address, City, " +
                                                             "       County, State, ZIPCode " +
                                                             "FROM Customers, WaterMeters " +
                                                             "WHERE (CustomerID = " + id + ") AND (Customers.WaterMeterID = WaterMeters.WaterMeterID);",
                                                             scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdCustomers.ExecuteNonQuery();
    
                    SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
                    DataSet dsCustomers = new DataSet("customers");
    
                    sdaCustomers.Fill(dsCustomers);
    
                    if (dsCustomers.Tables[0].Rows.Count > 0)
                    {
                        DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
    
                        client = new Client
                        {
                            CustomerID = int.Parse(drCustomer[0].ToString()),
                            AccountNumber = drCustomer[1].ToString(),
                            WaterMeter = drCustomer[2].ToString(),
                            FirstName = drCustomer[3].ToString(),
                            LastName = drCustomer[4].ToString(),
                            Address = drCustomer[5].ToString(),
                            City = drCustomer[6].ToString(),
                            County = drCustomer[7].ToString(),
                            State = drCustomer[8].ToString(),
                            ZIPCode = drCustomer[9].ToString()
                        };
                    }
                }
    
                return View(client);
            }
    
            // POST: Customers/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Customers " +
                                                                 "WHERE CustomerID = " + id + ";",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdCustomers.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  3. In the document, right-click Details() and click Go To View
  4. Change the document as follows:
    @model WaterDistributionCompany1.Models.Client
    
    @{
        ViewBag.Title = "Customer Details";
    }
    
    <div class="push-down">
        <h2>Customer Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
            <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
            <dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
            <dt>Water Meter</dt>
            <dd>@Html.DisplayFor(model => model.WaterMeter)</dd>
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
            <dt>@Html.DisplayNameFor(model => model.Address)</dt>
            <dd>@Html.DisplayFor(model => model.Address)</dd>
            <dt>@Html.DisplayNameFor(model => model.City)</dt>
            <dd>@Html.DisplayFor(model => model.City)</dd>
            <dt>@Html.DisplayNameFor(model => model.County)</dt>
            <dd>@Html.DisplayFor(model => model.County)</dd>
            <dt>@Html.DisplayNameFor(model => model.State)</dt>
            <dd>@Html.DisplayFor(model => model.State)</dd>
            <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
            <dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
        </dl>
    </div>
    
    <p class="text-center">
        @Html.ActionLink("Edit/Update Customer Information", "Edit",
                         new { id = Model.CustomerID, @class = "water-nav" },
                         new { @class = "water-nav" }) |
        @Html.ActionLink("Customers Records", "Index",
                         null, new { @class = "water-nav" })
    </p>
  5. In the Solution Explorer, under Views and under Customers, double-click Delete.cshtml
  6. Change the webpage as follows:
    @model WaterDistributionCompany1.Models.Client
    
    @{
        ViewBag.Title = "Remove Customer Account";
    }
    
    <div class="push-down">
        <h2>Cancelling Customer Account</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
            <dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
    
            <dt>Water Meter</dt>
            <dd>@Html.DisplayFor(model => model.WaterMeter)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Address)</dt>
            <dd>@Html.DisplayFor(model => model.Address)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.City)</dt>
            <dd>@Html.DisplayFor(model => model.City)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.County)</dt>
            <dd>@Html.DisplayFor(model => model.County)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.State)</dt>
            <dd>@Html.DisplayFor(model => model.State)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
            <dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
        </dl>
    
        <h3 class="common-font caption">Are you sure you want to cancel this customer's account?</h3>
    
        @using (Html.BeginForm())
        {
            <div class="form-actions no-color">
                <input type="submit" value="Delete this Customer's Account" class="btn btn-primary" /> |
                @Html.ActionLink("Customers Accounts", "Index", null, new { @class = "water-nav" })
            </div>
        }
    </div>
  7. In the Solution Explorer, under Models, double-click WaterBill.cs to access the class
  8. Create a new class as follows:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistributionCompany1.Models
    {
        public class WaterBill
        {
            [Display(Name = "Water Bill ID")]
            public int WaterBillID { get; set; }
            [Display(Name = "Invoice #")]
            public int InvoiceNumber { get; set; }
            [Display(Name = "Customer ID")]
            public int CustomerID { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Meter Reading Start Date")]
            public DateTime MeterReadingStartDate { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Meter Reading End Date")]
            public DateTime MeterReadingEndDate { get; set; }
            [Display(Name = "Billing Days")]
            public int BillingDays { get; set; }
            [Display(Name = "Counter Reading Start")]
            public int CounterReadingStart { get; set; }
            [Display(Name = "Counter Reading End")]
            public int CounterReadingEnd { get; set; }
            [Display(Name = "Total HCF")]
            public int TotalHCF { get; set; }
            [Display(Name = "Total Gallons")]
            public int TotalGallons { get; set; }
            [Display(Name = "1st 15 HCF")]
            public decimal First15HCF { get; set; }
            [Display(Name = "Next 10 HCF")]
            public decimal Next10HCF { get; set; }
            [Display(Name = "Remaining 10 HCF")]
            public decimal RemainingHCF { get; set; }
            [Display(Name = "Sewer Charges")]
            public decimal SewerCharges { get; set; }
            [Display(Name = "Storm Charges")]
            public decimal StormCharges { get; set; }
            [Display(Name = "Water Usage Charges")]
            public decimal WaterUsageCharges { get; set; }
            [Display(Name = "Total Charges")]
            public decimal TotalCharges { get; set; }
            [Display(Name = "Local Taxes")]
            public decimal LocalTaxes { get; set; }
            [Display(Name = "State Taxes")]
            public decimal StateTaxes { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Pmt Due Date")]
            public DateTime PaymentDueDate { get; set; }
            [Display(Name = "Amt Due")]
            public decimal AmountDue { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Late Pmt Date")]
            public DateTime LatePaymentDueDate { get; set; }
            [Display(Name = "Late Pmt Amt")]
            public decimal LateAmountDue { get; set; }
        }
    
        public class CustomerInvoice : WaterBill
        {
            public string CustomerAccount { get; set; }
        }
    }
  9. In the document, under Controllers, double-click WaterBillController.cs
  10. Change the Details() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistributionCompany1.Models;
    
    namespace WaterDistributionCompany1.Controllers
    {
        public class WaterBillsController : Controller
        {
            List<WaterBill> waterBills = new List<WaterBill>();
    
            . . . No Change
    
            // GET: WaterBills/Details/5
            public ActionResult Details(int id)
            {
                CustomerInvoice bill = new CustomerInvoice();
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, InvoiceNumber, " +
                                                              "       Customers.AccountNumber + N' - ' + Customers.LastName + N', ' + Customers.FirstName + N' (' + Customers.City + N', ' + Customers.State + N')', " +
                                                              "       MeterReadingStartDate, MeterReadingEndDate, BillingDays, " +
                                                              "       CounterReadingStart, CounterReadingEnd, TotalHCF, TotalGallons, " +
                                                              "       First15HCF, Next10HCF, RemainingHCF, SewerCharges, StormCharges, " +
                                                              "       WaterUsageCharges, TotalCharges, LocalTaxes, StateTaxes, " +
                                                              "       PaymentDueDate, AmountDue, LatePaymentDueDate, LateAmountDue " +
                                                              "FROM WaterBills, Customers " +
                                                              "WHERE(WaterBills.WaterBillID = " + id + ") AND (WaterBills.CustomerID = Customers.CustomerID);",
                                                              scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdWaterBills.ExecuteNonQuery();
    
                    SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
                    DataSet dsWaterBills = new DataSet("water-bills");
    
                    sdaWaterBills.Fill(dsWaterBills);
    
                    if (dsWaterBills.Tables[0].Rows.Count > 0)
                    {
                        bill.WaterBillID           =      int.Parse(dsWaterBills.Tables[0].Rows[0][0].ToString());
                        bill.InvoiceNumber         =      int.Parse(dsWaterBills.Tables[0].Rows[0][1].ToString());
                        bill.CustomerAccount       =                dsWaterBills.Tables[0].Rows[0][2].ToString();
                        bill.MeterReadingStartDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][3].ToString());
                        bill.MeterReadingEndDate   = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][4].ToString());
                        bill.BillingDays           =      int.Parse(dsWaterBills.Tables[0].Rows[0][5].ToString());
                        bill.CounterReadingStart   =      int.Parse(dsWaterBills.Tables[0].Rows[0][6].ToString());
                        bill.CounterReadingEnd     =      int.Parse(dsWaterBills.Tables[0].Rows[0][7].ToString());
                        bill.TotalHCF              =      int.Parse(dsWaterBills.Tables[0].Rows[0][8].ToString());
                        bill.TotalGallons          =      int.Parse(dsWaterBills.Tables[0].Rows[0][9].ToString());
                        bill.First15HCF            =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][10].ToString());
                        bill.Next10HCF             =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][11].ToString());
                        bill.RemainingHCF          =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][12].ToString());
                        bill.SewerCharges          =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][13].ToString());
                        bill.StormCharges          =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][14].ToString());
                        bill.WaterUsageCharges     =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][15].ToString());
                        bill.TotalCharges          =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][16].ToString());
                        bill.LocalTaxes            =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][17].ToString());
                        bill.StateTaxes            =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][18].ToString());
                        bill.PaymentDueDate        = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][19].ToString());
                        bill.AmountDue             =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][20].ToString());
                        bill.LatePaymentDueDate    = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][21].ToString());
                        bill.LateAmountDue         =  decimal.Parse(dsWaterBills.Tables[0].Rows[0][22].ToString());
    
                        waterBills.Add(bill);
                    }
                }
    
                return View(bill);
            }
    
           . . . No Change
        }
    }
    
  11. Right-click any part of the code that was modified and click Go To View
  12. Change the code as follows:
    @model WaterDistributionCompany1.Models.CustomerInvoice
    
    @{
        ViewBag.Title = "Water Bill Details";
    }
    
    <div class="push-down">
        <h2>Water Bill Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.WaterBillID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterBillID)</dd>
            <dt>@Html.DisplayNameFor(model => model.InvoiceNumber)</dt>
            <dd>@Html.DisplayFor(model => model.InvoiceNumber)</dd>
            <dt>Customer</dt>
            <dd>@Html.DisplayFor(model => model.CustomerAccount)</dd>
            <dt>@Html.DisplayNameFor(model => model.MeterReadingStartDate)</dt>
            <dd>@Html.DisplayFor(model => model.MeterReadingStartDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.MeterReadingEndDate)</dt>
            <dd>@Html.DisplayFor(model => model.MeterReadingEndDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.BillingDays)</dt>
            <dd>@Html.DisplayFor(model => model.BillingDays)</dd>
            <dt>@Html.DisplayNameFor(model => model.CounterReadingStart)</dt>
            <dd>@Html.DisplayFor(model => model.CounterReadingStart)</dd>
            <dt>@Html.DisplayNameFor(model => model.CounterReadingEnd)</dt>
            <dd>@Html.DisplayFor(model => model.CounterReadingEnd)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalHCF)</dt>
            <dd>@Html.DisplayFor(model => model.TotalHCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalGallons)</dt>
            <dd>@Html.DisplayFor(model => model.TotalGallons)</dd>
            <dt>@Html.DisplayNameFor(model => model.First15HCF)</dt>
            <dd>@Html.DisplayFor(model => model.First15HCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.Next10HCF)</dt>
            <dd>@Html.DisplayFor(model => model.Next10HCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.RemainingHCF)</dt>
            <dd>@Html.DisplayFor(model => model.RemainingHCF)</dd>
            <dt>@Html.DisplayNameFor(model => model.SewerCharges)</dt>
            <dd>@Html.DisplayFor(model => model.SewerCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.StormCharges)</dt>
            <dd>@Html.DisplayFor(model => model.StormCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.WaterUsageCharges)</dt>
            <dd>@Html.DisplayFor(model => model.WaterUsageCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.TotalCharges)</dt>
            <dd>@Html.DisplayFor(model => model.TotalCharges)</dd>
            <dt>@Html.DisplayNameFor(model => model.LocalTaxes)</dt>
            <dd>@Html.DisplayFor(model => model.LocalTaxes)</dd>
            <dt>@Html.DisplayNameFor(model => model.StateTaxes)</dt>
            <dd>@Html.DisplayFor(model => model.StateTaxes)</dd>
            <dt>@Html.DisplayNameFor(model => model.PaymentDueDate)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentDueDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.AmountDue)</dt>
            <dd>@Html.DisplayFor(model => model.AmountDue)</dd>
            <dt>@Html.DisplayNameFor(model => model.LatePaymentDueDate)</dt>
            <dd>@Html.DisplayFor(model => model.LatePaymentDueDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.LateAmountDue)</dt>
            <dd>@Html.DisplayFor(model => model.LateAmountDue)</dd>
        </dl>
    </div>
    
    <p class="text-center">
        @Html.ActionLink("Edit/Update Water Bill Information", "Edit",
                         new { id = Model.WaterBillID, @class = "water-nav" },
                         new { @class = "water-nav" }) |
        @Html.ActionLink("Water Meters", "Index",
                         null, new { @class = "water-nav" })
    </p>
  13. In the Solution Explorer, under Views and under Shared, double-click _Layout.cshtml
  14. To execute, on the main manu, click Debug -> Start Without Debugging
  15. Click the Bills/Invoices button
  16. Click the Review link of onoe of the records. Here is an example:

    Joins Fundamentals

    Joins Fundamentals

  17. Close the browser and return to your programming environment
  18. Close your programming environment

Previous Copyright © 2007-2019, FunctionX Next