Fundamentals of Records Maintenance

Introduction

Record maintenance consists of changing one or more values of a record, changing a group of records in one step, changing all records of a table, deleting one or more values in a record, deleting a series/group of records, or clearing a table of all its records. These operations can be performed visually or programmatically in Microsoft SQL Server Management Studio or Microsoft Visual Studio, or by writing code either in C# or as a Data Definition Language (DDL) command.

Practical LearningPractical Learning: Introducing Record Maintenance

  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 WaterDistribution1
  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. In the Solution Explorer, right-click WaterDistribution1 -> Add -> New Folder
  7. Type Images and press Enter
  8. Save the following picture to the Images folder:

    Water for a Shining Life

  9. In the Solution Explorer, right-click Content -> Add -> New Item...
  10. In the middle frame of the Add New Item dialog box, click Style Sheet
  11. Change the file Name to WaterDistribution
  12. Click Add
  13. 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;           }
    .lead              { color:            #cfdde0;           }
    
    .col-md-3 h2       { color:            #abcbd9;
                         border-bottom:    1px solid #cfdde0; }
    .col-md-3 p        { color:            #d5d4c2;           }
    .caption           { color:            lightcyan;         }
    .control-label     { font-weight:      200;               }
    .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:            lightgoldenrodyellow;            }
    .water-nav:visited { color:            aliceblue;         }
    .water-nav:active  { color:            #a8c3ce;           }
    .water-nav:hover   { color:            yellow;            }
    
    .col-md-125        { min-height:       1px;
                         padding-right:    15px;
                         padding-left:     15px;
                         width:            12.50%;
                         position:         relative;          }
    @media (min-width: 992px) {
        .col-md-125 { float: left;
                      width: 12.50%;
        }
    }
    .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:            lightblue;
        background-color: cornflowerblue;
    }
  14. In the Solution Explorer, under Views, expand App_Start and double-click BundleConfig.cs to open it
  15. Change the document as follows:
    using System.Web.Optimization;
    
    namespace WaterDistributionCompan1
    {
        public class BundleConfig
        {
            // For more information on bundling, visit https://go.microsoft.com/fwlink/?LinkId=301862
            public static void RegisterBundles(BundleCollection bundles)
            {
                bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                            "~/Scripts/jquery-{version}.js"));
    
                bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                            "~/Scripts/jquery.validate*"));
    
                // Use the development version of Modernizr to develop with and learn from. Then, when you're
                // ready for production, use the build tool at https://modernizr.com to pick only the tests you need.
                bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                            "~/Scripts/modernizr-*"));
    
                bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                          "~/Scripts/bootstrap.js"));
    
                bundles.Add(new StyleBundle("~/Content/css").Include(
                          "~/Content/bootstrap.css",
                          "~/Content/site.css",
                          "~/Content/WaterDistribution.css"));
            }
        }
    }
  16. In the Solution Explorer, expand Views and expand Shared
  17. Double-click _Layout.cshtml and change the document as follows:
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>@ViewBag.Title - 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("Home", "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("Environment", "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("Questions?", "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>
  18. In the Solution Explorer, under Views, expand Home, and double-click Index.cshtml
  19. 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>
  20. To preview the result, on the main menu, click Debug -> Start Without Debugging:

    Right

  21. Close the browser and return to your programming environment
  22. 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 GasDistribution;
        GO
        USE GasDistribution;
        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 GasDistribution and click Open
      5. In the Server Explorer, right-click GasDistribution.mdf and click New Query
  23. To create a database for this lesson, type the following code:
    CREATE TABLE WaterMeters
    (
    	WaterMeterID	INT IDENTITY(1, 1),
    	MeterNumber	    NVARCHAR(10),
    	Make		    NVARCHAR(40),
    	Model		    NVARCHAR(20),
    	MeterSize	    NVARCHAR(20),
    	DateLastUpdate  DATE,
    	CounterValue	INT
    );
    GO
    CREATE TABLE Customers
    (
    	CustomerID	  INT IDENTITY(1, 1),
    	AccountNumber NVARCHAR(20),
    	WaterMeterID  INT,
    	FirstName	  NVARCHAR(20),
    	LastName	  NVARCHAR(20),
    	[Address]	  NVARCHAR(100),
    	City		  NVARCHAR(40),
    	County		  NVARCHAR(40),
    	[State]		  NVARCHAR(5),
    	ZIPCode		  NVARCHAR(10)
    );
    GO
    CREATE TABLE WaterBills
    (
    	WaterBillID		      INT IDENTITY(1, 1),
    	CustomerID	          INT,
    	InvoiceNumber         INT,
    	MeterReadingStartDate Date,
    	MeterReadingEndDate   Date,
    	BillingDays           INT,
    	CounterReadingStart   INT,
    	CounterReadingEnd     INT,
    	TotalHCF              int,
    	TotalGallons          INT,
    	First15HCF            DECIMAL(8, 2),
    	Next10HCF             DECIMAL(8, 2),
    	RemainingHCF          DECIMAL(8, 2),
    	SewerCharges          DECIMAL(8, 2),
    	StormCharges          DECIMAL(8, 2),
    	WaterUsageCharges     DECIMAL(8, 2),
    	TotalCharges          DECIMAL(8, 2),
    	CountyTaxes           DECIMAL(8, 2),
    	StateTaxes            DECIMAL(8, 2),
    	PaymentDueDate        Date,
    	AmountDue             DECIMAL(8, 2),
    	LatePaymentDueDate    Date,
    	LateAmountDue         DECIMAL(8, 2)
    );
    GO
    CREATE TABLE Payments
    (
    	PaymentID     INT IDENTITY(1, 1),
    	ReceiptNumber INT,
    	WaterBillID   INT,
    	PaymentDate   DATE,
    	PaymentAmount DECIMAL(8, 2)
    );
    GO
  24. To execute the code, right-click inside the Query window and click Execute
  25. Close the Query window
  26. When asked whether you want to save, click No
  27. In the Solution Explorer, double-click the very bottom Web.config file
  28. Create a connection string section as follows:
    • Microsoft SQL Server:
      <?xml version="1.0" encoding="utf-8"?>
      <!--
        For more information on how to configure your ASP.NET application, please visit
        https://go.microsoft.com/fwlink/?LinkId=301880
        -->
      <configuration>
        <appSettings>
          <add key="webpages:Version" value="3.0.0.0"/>
          <add key="webpages:Enabled" value="false"/>
          <add key="ClientValidationEnabled" value="true"/>
          <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
        </appSettings>
        <system.web>
          <compilation debug="true" targetFramework="4.6.1"/>
          <httpRuntime targetFramework="4.6.1"/>
          <httpModules>
            <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
          </httpModules>
        </system.web>
        <connectionStrings>
          <add name="csWaterDistribution"
               connectionString="Data Source=(local); Database='GasDistribution'; Integrated Security=True;MultipleActiveResultSets=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
    • Local database:
      <?xml version="1.0" encoding="utf-8"?>
      <!--
        For more information on how to configure your ASP.NET application, please visit
        https://go.microsoft.com/fwlink/?LinkId=301880
        -->
      <configuration>
        <appSettings>
          <add key="webpages:Version" value="3.0.0.0"/>
          <add key="webpages:Enabled" value="false"/>
          <add key="ClientValidationEnabled" value="true"/>
          <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
        </appSettings>
        <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>

Adding a Record

The most fundamental operation performed on an existing table consists of creating a record. As we saw in previous lesson, this is also referred to as inserting a record.

Practical LearningPractical Learning: Creating a Record

  1. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  2. In the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions
  3. Click Add
  4. Type WaterMeters to get WaterMetersController
  5. Press Enter
  6. Implement the second Create() method as follows:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterMetersController : Controller
        {
            . . . No Change
    
            // POST: WaterMeters/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                    {
                        SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO WaterMeters(MeterNumber, Make, " +
                                                                   "                        Model, MeterSize, " +
                                                                   "                        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();
                }
            }
    
            . . . No Change
        }
    }
  7. In the document, right-click one of the Create() methods and click Add View...
  8. In the Add View dialog box, make sure the View Name text box is displaying Create. Click Add
  9. 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>
    }
  10. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  11. In the left frame of the Add Scaffold dialog box, under Common and under MVC, click Controller
  12. In the middle frame, click MVC 5 Controller With Read/Write Actions and click Add
  13. Type Payments to get PaymentsController
  14. Click Add
  15. Implement the Create() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using WaterDistribution1.Models;
    using System.Collections.Generic;
    
    namespace WaterDistribution3.Controllers
    {
        public class PaymentsController : Controller
        {
            . . . No Change
    
            // GET: Payments/Create
            public ActionResult Create()
            {
                Random rndNumber = new Random();
    
                ViewBag.ReceiptNumber = rndNumber.Next(100001, 999999).ToString();
    
                return View();
            }
    
            // POST: Payments/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 cmdPayments = new SqlCommand("INSERT INTO Payments(ReceiptNumber, WaterBillID, " +
                                                                 "                    PaymentDate, PaymentAmount) " +
                                                                 "VALUES(" + collection["ReceiptNumber"] + ", " +
                                                                 collection["WaterBillID"] + ", N'" + collection["PaymentDate"] +
                                                                 "', " + collection["PaymentAmount"] + ");",
                                                                 scWaterDistribution);
    
                        scWaterDistribution.Open();
                        cmdPayments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            . . . No Change
        }
    }
  16. In the class, right-click one of the Create() methods and click Add View...
  17. Make sure the View Name text box is displaying Create. Click Add
  18. Create the form as follows:
    @{
        ViewBag.Title = "Create Bill Payment";
    }
    
    <div class="push-down">
        <h2>Create Bill Payment</h2>
    </div>
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal">
            <div class="form-group">
                <label for="waterBillID" class="control-label col-md-4 caption">Receipt #:</label>
                <div class="col-md-8">
                    @Html.TextBox("ReceiptNumber", ViewBag.ReceiptNumber as string, htmlAttributes: new { @class = "form-control", id = "receiptNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="waterBillID" class="control-label col-md-4 caption">Water Bill #:</label>
                <div class="col-md-8">
                    @Html.TextBox("WaterBillID", null, htmlAttributes: new { @class = "form-control", id = "waterBillID" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="pmtDate" class="control-label col-md-4 caption">Payment Date:</label>
                <div class="col-md-8">
                    @Html.TextBox("PaymentDate", null,
                                  htmlAttributes: new { @class = "form-control", type = "date", id = "pmtDate" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="pmtAmt" class="control-label col-md-4 caption">Payment Amount:</label>
                <div class="col-md-8">
                    @Html.TextBox("PaymentAmount", null, htmlAttributes: new { @class = "form-control", id = "pmtAmt" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-5">
                    @Html.ActionLink("Customers Bills Payments", "Index", null, htmlAttributes: new { @class = "water-nav" })
                </label>
                <div class="col-md-7">
                    <input type="submit" value="Make Bill Payment" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }

Selecting Records

Introduction

The second most routine operation on a database consists of getting all the records of a table. In the SQL, this operation is performed using the SELECT operator. In an ASP.NET MVC application, after getting the records of a table, you can store them in a property attached to a ViewBag object, then access that property as a collection in the view. As an alternative, you can use a model. In this case, you must create an IEnumerable<> type of collection that holds a class whose properties are similar to the columns of the table.

Practical LearningPractical Learning: Selecting Records

  1. In the Solution Explorer, right-click Models -> Add -> Class...
  2. Type Customer as the name of the class
  3. Click Add
  4. Create the class as follows:
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistribution1.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; }
        }
    }
  5. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  6. 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
  7. Click Add
  8. Type Customers to get CustomersController
  9. Click Add
  10. Change the Index() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class CustomersController : Controller
        {
            private 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);
            }
    
            . . .  No Change
        }
    }
  11. In the document, right-click Index() and click Add View...
  12. Make sure the View Name text box displays Index. Click Add
  13. Change the document as follows:
    @model IEnumerable<WaterDistribution1.Models.Customer>
    
    @{
        ViewBag.Title = "Customers Accounts";
    }
    
    <div class="push-down">
        <h2>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 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.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>
  14. In the Solution Explorer, right-click Models -> Add -> Class...
  15. Type WaterBill as the name of the class
  16. Click Add
  17. Create the class as follows:
    using System;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistribution10.Models
    {
        public class WaterBill
        {
            [Display(Name = "Water Bill ID")]
            public int WaterBillID { get; set; }
            [Display(Name = "Customer ID")]
            public int CustomerID { get; set; }
            [Display(Name = "Invoice #")]
            public int InvoiceNumber { 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 = "County Taxes")]
            public decimal CountyTaxes { 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; }
        }
    }
  18. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  19. In the middle frame of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected. Click Add
  20. Type WaterBills to get WaterBillsController
  21. Click Add
  22. Change the document as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterBillsController : Controller
        {
            private 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, CountyTaxes, " +
                                                              "       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.CountyTaxes           = 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);
            }
    
            . . . No Change
        }
    }
  23. In the class, right-click inside the Index() method and click Add View...
  24. Make sure the View Name text box displays Index. Press Enter
  25. Change the webpage as follows:
    @model IEnumerable<WaterDistribution1.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">Customer ID</th>
            <th class="bold text-center">Invoice #</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">County 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.CustomerID)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.InvoiceNumber)</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.CountyTaxes)</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>
  26. In the Solution Explorer, right-click Models -> Add -> Class...
  27. Type Payment as the name of the class
  28. Click Add
  29. Create the class as follows:
    using System;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistribution1.Models
    {
        public class Payment
        {
            [Display(Name = "Payment ID")]
            public int      PaymentID { get; set; }
            [Display(Name = "Receipt #")]
            public int      ReceiptNumber { get; set; }
            [Display(Name = "Water Bill ID")]
            public int      WaterBillID { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Payment Date")]
            public DateTime PaymentDate { get; set; }
            [Display(Name = "Payment Amount")]
            public decimal  PaymentAmount { get; set; }
        }
    }
  30. In the Solution Explorer, right-click Controllers -> Add -> Controllers...
  31. In the middle frame of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected. Click Add
  32. Type Payments to get PaymentsController
  33. Press Enter
  34. Change the class as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class PaymentsController : Controller
        {
            private List<Payment> payments = new List<Payment>();
    
            // GET: Payments
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, WaterBillID, PaymentDate, PaymentAmount " +
                                                            "FROM Payments;",
                                                            scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdPayments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaPayments = new SqlDataAdapter(cmdPayments);
                    DataSet dsPayments = new DataSet("payments");
    
                    sdaPayments.Fill(dsPayments);
    
                    Payment pmt = null;
    
                    for (int i = 0; i < dsPayments.Tables[0].Rows.Count; i++)
                    {
                        pmt = new Payment
                        {
                            PaymentID = int.Parse(dsPayments.Tables[0].Rows[i][0].ToString()),
                            WaterBillID = int.Parse(dsPayments.Tables[0].Rows[i][1].ToString()),
                            PaymentDate = DateTime.Parse(dsPayments.Tables[0].Rows[i][2].ToString()),
                            PaymentAmount = decimal.Parse(dsPayments.Tables[0].Rows[i][3].ToString())
                        };
    
                        payments.Add(pmt);
                    }
                }
    
                return View(payments);
            }
    
            . . . No Change
        }
    }
    
  35. In the document, right-click SELECT and click Add View
  36. Make sure the text box is displaying Index. Click Add
  37. Change the class as follows:
    @model IEnumerable<WaterDistribution1.Models.Payment>
    
    @{
        ViewBag.Title = "Bills Payments";
    }
    
    <div class="push-down">
        <h2>Bills Payments</h2>
    </div>
    
    <hr />
    
    <table class="table table-striped common-font">
        <tr>
            <th class="bold">Payment ID</th>
            <th class="bold">Water Bill ID</th>
            <th class="bold">Payment Date</th>
            <th class="bold">Payment Amount</th>
            <th>@Html.ActionLink("New Bill Payment", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">
                    @Html.DisplayFor(modelItem => item.PaymentID)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.WaterBillID)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.PaymentDate)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.PaymentAmount)
                </td>
                <td>
                    @Html.ActionLink("Update", "Edit", new { id = item.PaymentID }) |
                    @Html.ActionLink("Review", "Details", new { id = item.PaymentID }) |
                    @Html.ActionLink("Remove", "Delete", new { id = item.PaymentID })
                </td>
            </tr>
        }
    </table>
  38. In the Solution Explorer, right-click Models -> Add -> Class...
  39. Type WaterMeter as the name of the class
  40. Click Add
  41. Create the class as follows:
    using System;
    using System.ComponentModel.DataAnnotations;
    
    namespace WaterDistribution1.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 + ")";
                }
            }
        }
    }
  42. In the Solution Explorer, under Controllers, double-click WaterMetersController.cs to access the class
  43. Implement the Index() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterMetersController : Controller
        {
            private List<WaterMeter> waterMeters = new List<WaterMeter>();
    
            // GET: WaterMeters
            public ActionResult Index()
            {
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, 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);
            }
    
            . . . No Change
            
        }
    }
  44. In the class, right-click Index() and click Add View...
  45. Make sure the View Name text box displays Index.
    Click Add
  46. Create the webpage as follows:
    @model IEnumerable<WaterDistribution1.Models.WaterMeter>
    
    @{
        ViewBag.Title = "Water Meters";
    }
    
    <div class="push-down">
        <h2>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>
  47. To execute the application and test it, on the main menu, click Debug and click Start Without Debugging

    Switching a String

  48. Click the New Water Meter link

    Edit - Update XML Elements

  49. Create the following records:
     
    Meter # Make Model Meter Size Date Last Update Counter Value
    392-44-572 Constance Technologies TG-4822 5/8 Inches 03/31/2018 109992
    938-75-869 Standard Trend 266G 1 1/2 Inches 10/22/2017 137926
    799-28-461 Constance Technologies BD-7000 3/4 Inches 05/05/2018 6268
    207-94-835 Constance Technologies TG-6220 5/8 Inches 02/17/2018 96
    592-84-957 Standard Trend 428T 3/4 Inches 12/07/2018 49
    28358958 Igawa International TR6224 3/4 Inches 04/22/2012 1138

    XML Records Maintenance

  50. Close the browser and return to your programming environment

Selecting a Record

As seen when studying data filtering, to select one or a group of records in SQL, you can use the WHERE operator. After finding the record, you can create it as the class you had created to represent a record of a table. You can then return that reject as an object of the class. In the view, use the model to access the object.

Practical LearningPractical Learning: Selecting a Record

  1. In the Solution Explorer, under Controllers, double-click WaterMetersController.cs to access the controller
  2. Implement the Details() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterMetersController : Controller
        {
            . . . No Change
    
            // 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();
            }
    
            . . . No Change
            
        }
    }
  3. In the class, right-click below Details() and click Add View...
  4. Make sure the View Name text box displays Details. Click Add
  5. Change the document as follows:
    @model WaterDistribution1.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>
  6. In the Solution Explorer, under Controllers, double-click CustomersController.cs
  7. Change the Details() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class CustomersController : Controller
        {
            List<Customer> customers = new List<Customer>();
    
            . . . No Change
    
            // 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);
            }
    
            . . . No Change
            
        }
    }
  8. Right-click anything inside the Details() method and click Add View...
  9. Make sure the View Name text box displays Details. Click Add
  10. Change the webpage as follows:
    @model WaterDistribution1.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>
    
  11. In the Solution Explorer, under Controllers, double-click WaterBillsController.cs to access the file
  12. Change the Details() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterBillsController : Controller
        {
            List<WaterBill> waterBills = new List<WaterBill>();
    
            . . . No Change
    
            // 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, CountyTaxes, " +
                                                              "       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.CountyTaxes           = 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());
                    }
                }
    
                return View(bill);
            }
    
            . . .  No Change
        }
    }
  13. In the document, right-click Details() and click Add View
  14. Make sure the View Name text box displays Details. Click Add
  15. Change the code as follows:
    @model WaterDistribution1.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.CustomerID)</dt>
            <dd>@Html.DisplayFor(model => model.CustomerID)</dd>
            <dt>@Html.DisplayNameFor(model => model.InvoiceNumber)</dt>
            <dd>@Html.DisplayFor(model => model.InvoiceNumber)</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.CountyTaxes)</dt>
            <dd>@Html.DisplayFor(model => model.CountyTaxes)</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>
  16. In the Solution Explorer, under Controllers, double-click PaymentsController.cs
  17. Change the Details() method as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class PaymentsController : Controller
        {
            List<Payment> payments = new List<Payment>();
    
            . . . No Change
    
            // GET: Payments/Details/5
            public ActionResult Details(int id)
            {
                Payment pmt = null;
    
                using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
                {
                    SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, WaterBillID, PaymentDate, PaymentAmount " +
                                                            "FROM Payments " +
                                                            "WHERE PaymentID = " + id + ";",
                                                            scWaterDistribution);
    
                    scWaterDistribution.Open();
                    cmdPayments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaPayments = new SqlDataAdapter(cmdPayments);
                    DataSet dsPayments = new DataSet("payments");
    
                    sdaPayments.Fill(dsPayments);
    
                    if(dsPayments.Tables[0].Rows.Count > 0)
                    {
                        pmt = new Payment
                        {
                            PaymentID = int.Parse(dsPayments.Tables[0].Rows[0][0].ToString()),
                            WaterBillID = int.Parse(dsPayments.Tables[0].Rows[0][1].ToString()),
                            PaymentDate = DateTime.Parse(dsPayments.Tables[0].Rows[0][2].ToString()),
                            PaymentAmount = decimal.Parse(dsPayments.Tables[0].Rows[0][3].ToString())
                        };
                    }
                }
    
                return View(pmt);
            }
    
            . . . No Change
        }
    }
  18. In the document, right-click if(dsPayments.Tables[0].Rows.Count > 0) and click Add View...
  19. Make sure the View Name text box has Details. Click Add
  20. Change the webpage as follows:
    @model WaterDistribution1.Models.Payment
    
    @{
        ViewBag.Title = "Bill Payment Details";
    }
    
    <div class="push-down">
        <h2>Bill Payment Details</h2>
    </div>
    
    <hr />
    
    <div class="containment">
        <dl class="dl-horizontal common-font caption">
            <dt>@Html.DisplayNameFor(model => model.PaymentID)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentID)</dd>
            <dt>@Html.DisplayNameFor(model => model.WaterBillID)</dt>
            <dd>@Html.DisplayFor(model => model.WaterBillID)</dd>
            <dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
            <dt>@Html.DisplayNameFor(model => model.PaymentAmount)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentAmount)</dd>
        </dl>
    </div>
    
    <p class="text-center">
        @Html.ActionLink("Edit/Update Bill Payment Information", "Edit",
                         new { id = Model.PaymentID, @class = "water-nav" }) |
        @Html.ActionLink("Bills Payments", "Index",
                         null, new { @class = "water-nav" })
    </p>
  21. In the Solution Explorer, under Controllers, double-click WaterBillsController.cs
  22. Change the document as follows (you will create two accessory methods (to process a water bill) and implement the second Create() method to save a water bill):
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterBillsController : Controller
        {
            private List<WaterBill> waterBills = new List<WaterBill>();
    
            . . . No Change
    
            // GET: WaterBills/Details/5
            public ActionResult Details(int id)
            {
                . . . No Change
    
                return View();
            }
    
            // 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 countyTaxes = totalCharges * 0.005M;
                decimal stateTaxes = totalCharges * 0.0152M;
                decimal amountDue = totalCharges + countyTaxes + 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.Next10HCF = next10HCF.ToString("F");
                ViewBag.StateTaxes = stateTaxes.ToString("F");
                ViewBag.First15HCF = first15HCF.ToString("F");
                ViewBag.AmountDue = amountDue.ToString("F");
                ViewBag.CountyTaxes = countyTaxes.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, CountyTaxes, 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["CountyTaxes"] + ", " + collection["StateTaxes"] + ", N'" +
                                                                  collection["PaymentDueDate"] + "', " + collection["AmountDue"] + ", N'" +
                                                                  collection["LatePaymentDueDate"] + "', " + collection["LateAmountDue"] + ");",
                                                                  scWaterDistribution);
                        scWaterDistribution.Open();
                        cmdWaterBills.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View("Index");
                }
            }
    
            . . . No Change
            
        }
    }
  23. In the document, right-click the public ActionResult StartMeterReading() line and click Add View...
  24. Make sure the View Name text box displays StartMeterReading. Click Add
  25. 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>
    }
  26. In the Solution Explorer, under Views, right-click WaterBills -> Add -> View...
  27. Type PrepareMeterReading as the name of the view
  28. Press Enter
  29. 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="countyTaxes" class="control-label col-md-125 caption">County Taxes:</label>
            <div class="col-md-125">
                @Html.TextBox("CountyTaxes", ViewBag.CountyTaxes as string,
                              htmlAttributes: new { @class = "form-control", id = "countyTaxes" })
            </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>
    }

Data Selection and Combo Boxes

When users are performing data entry, one way you can assist them is to provide combo boxes in a form. To make this possible, in the first Create() or the first Edit() methods of a controller, you can apply a property to a ViewBag object. The name of this property should (must) be the same name as the column that holds the values from the table that supplies the values. The value of this property should be from a class named SelectList. The SelectList class is defined in the System.Web.Mvc namespace. It is equipped with various constructors. One of them uses the following syntax:

public SelectList(IEnumerable items, string dataValueField, string dataTextField);

One of the constructors of the SelectList class takes one argument. In all cases, the lone or the first argument of the constructor is the list of records. For the syntax we chose, the second argument is the name of the column that represents the records of the other table. This argument is passed as a string. Normally, this argument is the same as the property applied to the ViewBag object. The third argument of the syntax we chose is a string that represents what will display in the combo box. You can chose any column of the other table. An alternative is to create a string that holds a combination of the columns of the other table.

Practical LearningPractical Learning: Creating Combo Boxes

  1. In the Solution Explorer, under Controllers, double-click CustomersController.cs to access the class
  2. Change the Create() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using WaterDistribution1.Models;
    using System.Collections.Generic;
    
    namespace WaterDistribution1.Controllers
    {
        public class CustomersController : Controller
        {
            private List<Customer> customers = new List<Customer>();
    
            . . . No Change
    
            // 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();
                }
            }
    
            . . . No Change
        }
    }
  3. Right-click one of the Create() methods and click Add View...
  4. In the Add View dialog box, make the View Name text box displays Create. Click Add
  5. 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>
    }
  6. To execute, on the main menu, click Debug -> Start Without Debugging

  7. 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 George MD 20706
    28864153060 392-44-572 Janice Edson 10304 Rolando Drv Anderson PA 17262

    XML Records Maintenance

  8. Click the Home link
  9. Click the Bills/Invoices button

    XML Records Maintenance

  10. Click the New Water Bill link:

    Edit - Update XML Elements

  11. To start a record, in the text boxes, enter the following values:
    Customer Account #:    7518-302-6895
    Meter Reading Date:    7/30/2018
    Current Meter Reading: 114

    Edit - Update XML Elements

  12. Click Prepare Meter Reading
  13. Review the water bill

    Edit - Update XML Elements

  14. Click the Save Water Bill button
  15. Click the New Water Bill link
  16. Create other records with the following values:

    Account # Meter Reading End Date Current Meter Reading Payment Due Date Late Payment Due Date
    4820-375-2842 07/31/2018 109998 08/28/2018 09/14/2018
    2038-413-9680 7/30/2018 137975 8/27/2018 9/13/2018
    9279-570-8394 08/07/2018 6275 08/04/2018 08/20/2018
    7518-302-6895 11/07/2018 118 12/01/2018 12/15/2018
    2038-413-9680 10/27/2018 138012 11/24/2018 12/10/2018

    XML Records Maintenance

  17. Click the Home link and click the Bills Payments button
  18. Click the New Bill Payment link

    XML Records Maintenance

  19. Click the New Bill Payment link
  20. Create a few records with the following values:

    Edit - Update XML Elements

    Bill ID Payment Date Payment Amount
    2 08/24/2018 27.74
    3 09/12/2018 198.36
    1 08/27/2018 84.39
    4 09/01/2018 32.36
    6 12/05/2018 163.40
    5 12/10/2018 27.44

    XML Records Maintenance

  21. Close the browser and return to your programming environment

Updating the Records

Updating all Records of a Table with a Value

Consider a table created as follows:

CREATE SCHEMA Academics;
GO
CREATE TABLE Academics.Courses
(
    CourseCode	   NCHAR(8) NOT NULL,
    CourseName	   NVARCHAR(100),
    Credits	   TINYINT DEFAULT 1,
    Prerequisites  NVARCHAR(100),
    Classification NVARCHAR(28),
);
GO
INSERT Academics.Courses(CourseCode, CourseName)
VALUES('ENGL-101', 'Introduction to English Writing'),
	  ('STAT-138', 'Introduction to Statistics'),
	  ('BIOL-226', 'Cell Biology'),
	  ('CMSC-121', 'Introduction to Computer Science I'),
	  ('CHEM-210', 'Organic Chemistry I'),
	  ('RSCH-100', 'Academic Research and College Work'),
	  ('ANTH-222', 'Anthropological Theory'),
	  ('CHEM-210-L', 'Organic Chemistry I - Lab'),
	  ('ECON-100', 'Introductory Macroeconomics'),
	  ('CMSC-320', 'Algorithms and Data Structures'),
	  ('ANTH-402', 'Contemporary Ethnographies and Observations'),
	  ('BIOL-226-L', 'Cell Biology - Lab'),
	  ('CMSC-221', 'Introduction to Computer Science II');
GO
SELECT * FROM Academics.Courses;
GO

Records Maintenance

Imagine that, at one time, on a particular table, all records need to receive a new value in one particular column or in certain columns. The DDL command to update a record is UPDATE. The basic formula to use is:

UPDATE table-name SET conditional-expression

You must specify the name of the table as the table-name of our formula. The SET statement allows you to specify a conditional-expression. This expression can be formulated using the types of conditions we have used in the past, in which case the conditional-expression can be stated as column-name operator expression.

To update the records, from our UPDATE formula, you can simply compare the intended column to the desired value. Here is an example:

UPDATE Academics.Courses SET Credits = 3;
GO

This would produce:

Updating all Records

Remember that the LIKE operator can be used where the equality operator is used. This means that the above statement can also be written as follows:

UPDATE Academics.Courses LIKE Credits = 3;
GO

As always, to make your code easier to read, you can write it on various lines:

UPDATE table-name
SET conditional-expression

Updating Various Columns

You can update various columns with one UPDATE...SET operation. To do this, after the SET operator, enter each combination of column-name = expression and separate them with commas. The formula to follow is:

UPDATE table-name SET conditional-expression_1, conditional-expression_2, conditional-expression_n

To make your code easy to read, you can distribe it to different lines:

UPDATE table-name
SET conditional-expression_1,
    conditional-expression_2,
    conditional-expression_n

Here is an example:

UPDATE Academics.Courses
SET Classification = 'Undergraduate',
    Prerequisites = 'RSCH-100'

Updating Records

Conditionally Updating One or Some Records

Instead of updating all records, you can set a condition by which some records would be selected. To do this, add a WHERE operator to the UPDATE statement. The formula to follow is:

UPDATE table-name
SET column-name = expression
WHERE condition(s)

The condition is set the same way we saw for data analysis. Here is an exaple:

UPDATE Academics.Courses SET Credits = 5
WHERE CourseCode = 'ANTH-402';
GO

Sets

If you want to update the values of various columns with one conditions, in the SET section, create the list of the desired columns and assign the desired value to each. Then set the condition before the end of the statement. The formula to follow is:

UPDATE table-name
SET conditional-expression_1,
    conditional-expression_2,
    conditional-expression_n
WHERE condition(s)

Practical LearningPractical Learning: Updating a Record

  1. In the Solution Explorer, under Controllers, double-click WaterMetersController.cs to access its class
  2. Implement both Edit() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class WaterMetersController : Controller
        {
            List<WaterMeter> waterMeters = new List<WaterMeter>();
    
            . . . No Change
    
            // 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();
                }
            }
    
            . . . No Change
        }
    }
  3. In the class, right-click one of the Edit() methods and click Add View...
  4. In the Add View dialog box, make sure the View Name text box displays Edit. Click Add
  5. Create a form as follows:
    @model WaterDistribution1.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>
    }
  6. In the Solution Explorer, under Controllers, double-click CustomersController.cs to access it
  7. Implement both Edit() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.Controllers
    {
        public class CustomersController : Controller
        {
            List<Customer> customers = new List<Customer>();
    
            . . . No Change
    
            // 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();
                }
            }
    
            . . . No Change
        }
    }
  8. Right-click inside one of the Edit() methods and click Add View...
  9. Make sure View Name displays Edit. Click Add
  10. Create a webpage as follows:
    @model WaterDistribution1.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>
    }

Conditionally Updating Some Columns

You can update the records of more than one column if they respond to a certain condition. To do this, add the conditional conjunction, disjunctions, or any combination in the SET operation. If you just a conjunction, all columns that apply the conditions would be changed. If you want to apply the same change to different columns, use a disjunction. Here is an example:

UPDATE Academics.Courses SET Credits = 2
WHERE (CourseCode = 'BIOL-226-L') OR (CourseCode = 'CHEM-210-L')

Video Collection

Conditionally Updating Many Records with Different Conditions

You can use a combination of conditions to update various columns. To do this, in the SET section, add the desired combinations of column-name = expression expressions. In the WHERE clause, add the desired conditions. In most cases, you will use disjunctions. Here is an example:

UPDATE Academics.Courses
SET Prerequisites = 'MATH-107, ANTH-110',
    Credits       = 4
WHERE (CourseCode = 'CMSC-221') OR
      (CourseCode = 'BIOL-226') OR
      (CourseCode = 'CMSC-320') OR
      (CourseCode = 'CHEM-210');
GO

Accessing the Records Maintenance

Deleting Records

Deleting all Records

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure.

In the SQL Server Management Studio, to visually delete all records from a table, open it in design view, right-click the button in the top-left corner at the intersection of the column and row headers, and click Delete. As an alternative, first select all of them, and press Delete. In both cases, you would receive a warning. If you still want to delete the records, click Yes. If you change your mind, click No. Be careful when doing this because once the records have been deleted, you cannot get them back.

In SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE table-name;

When this statement executes, all records from the table-name would be removed from the table. Here is an example:

DELETE Academics.Courses;
GO

Removing the First n Records

Instead of removing all records, to delete only the first n records of a table, use the following formula:

DELETE TOP (Number) table-name;

In the parentheses, enter the desired number of records. When the statement executes, the first n records of the table would be deleted. Here is an example:

DELETE TOP (2) Employees;
GO

This statement asks the database engine to delete the first two records of the Employees table.

Removing a Specific Record

If you find out that a record is not necessary, not anymore, you can remove it from a table.

In the SQL Server Management Studio, to visually remove a record from a table, open the table in Table view. Right-click the row header (the gray box on the left side) of the record and click Delete. You can also first select the record and press Delete. You would receive a warning to confirm your intention.

To programmatically delete a record:

In SQL, to delete a record, use the DELETE FROM statement associated with the WHERE operator. The formula to follow is:

DELETE table-name
WHERE condition

As an option, you can include the FROM keyword before the name of the table:

DELETE FROM table-name
WHERE condition

The table-name is used to identify a table whose record(s) would be removed.

The condition allows you to identify a record or a group of records that carries a criterion. Make sure you are precise in your criteria so you would not delete the wrong record(s). Here is an example used to remove a particular record from the table:

DELETE FROM Collection.Videos
WHERE Title = N'The Lady Killers';
GO

Practical LearningPractical Learning: Deleting a Record

  1. In the Solution Explorer, under Controllers, double-click WaterMetersController.cs to access the class
  2. Implement both Delete() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.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();
                }
            }
        }
    }
    
  3. Right-click one of the Delete() methods and click Add View...
  4. Make sure the View Name text box displays Delete. Click Add
  5. Change the document as follows:
    @model WaterDistribution1.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>
  6. In the Solution Explorer, under Controllers, double-click CustomersController.cs to access it
  7. Implement the Delete() methods as follows:
    using System;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using WaterDistribution1.Models;
    
    namespace WaterDistribution1.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();
                }
            }
        }
    }
  8. Right-click inside one of the Delete() methods and click Add View...
  9. Make sure the View Name text box displays Delete. Click Add
  10. Create the webpage as follows:
    @model WaterDistribution1.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>
  11. To save the files, on the main menu, click File and click Save All

Conditionally Removing the First n Records

Consider the following table:

CREATE TABLE Employees
(
	EmployeeNumber int,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(283007, N'Megan',    N'Wright',    8.50),
      (480295, N'Horace',   N'Taylor',   20.25),
      (804805, N'Marc',     N'Knights',  10.85),
      (294117, N'Bryan',    N'Tenant',   30.25),
      (837479, N'Paul',     N'Motto',    18.02),
      (280304, N'Joyce',    N'Holliday', 11.66),
      (924802, N'Peter',    N'Mukoko',    8.68),
      (725381, N'Marianne', N'Brooks',   22.64);
GO

By default, the DELETE expression acts on all records of a table. As an alternative, you can ask the database engine to consider only the first n records of a table. The formula to do this is:

DELETE TOP (number) FROM table-name
WHERE condition(s)

In the parentheses after TOP, enter the desired number of records. When the statement executes, the WHERE condition would be applied on only the first number of records. Any record that falls in that condition would be deleted. Here is an example:

DELETE TOP (4) FROM Employees
WHERE HourlySalary < 12.50;
GO

This code asks the database engine to delete any record in the first four records of the Employees table if the hourly salary of the employee in less than 12.50.

Conditionally Removing the First Percentage of Records

If you don't want to specify a fixed number of records, you can use a percentage instead. The formula to follow is:

DELETE TOP (number) PERCENT FROM table-name
WHERE condition(s)

In the parentheses, enter a number between 0.00 and 100.00 included. The number of records to consider is based on the total number of the records using a percentage. Here is an example:

DELETE TOP (40) PERCENT FROM Employees
WHERE HourlySalary < 12.50;
GO

This code deletes any record whose salary is less than 12.50 but the record must be among the first 40% of the records.

Outputting the Deleted Results

When some record(s) has(have) been deleted, the operation is performed behind the scenes and you don't see the result. In Microsoft SQL Server Management Studio, if you want to see a list of the records that were deleted, you can use the OUTPUT operator to display the result. In Microsoft SQL Server Management, to show the list of the records from a table that was completely emptied, you can use the following formula:

DELETE FROM table-name
OUTPUT DELETED.Columns

The OUTPUT INSERTED expression follows the description we have seen for the record update. Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
GO

To show the list of the records that were deleted based on a condition, use the following formula:

DELETE FROM table-name
OUTPUT DELETED.Columns
WHERE Condition(s)

 Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
WHERE YearReleased IS NULL;
GO

Data Relationships and Records Maintenance

Introduction

After a primary and a foreign keys have been created to link two tables, you may be wondering what would happen if a record in the parent table changes. Of course, you can edit or delete records but there are rules to follow.

Updating Records

After creating a foreign key that relates to a primary key in a parent table, remember that the value of the foreign key points to the record in the parent table. To make sure that the flow of data is smooth, you are not allowed to edit the value of the primary key. If you try changing the record on the primary table, you would receive an error.

Deleting Records

After creating a relationship between a primary key and a foreign key, every foreign key value (in the child table) must be able to find its original value in the primary key of the parent table. If you were to delete a record in the parent table, the record(s) in the child table would become orphan(s). For this reason, you are not allowed to delete a record in the parent table if there is a record in the child table that is related to the record you are trying to delete. If you try to delete a record involved in a relationship, you would receive an error. If you need to delete a record in the parent table, you must first delete its related record(s) in the child table(s).

Editing or Updating Records

Introduction

Consider a table created as follows:

CREATE SCHEMA Academics;
GO
CREATE TABLE Academics.Courses
(
    CourseCode	   NCHAR(8) NOT NULL,
    CourseName	   NVARCHAR(100),
    Credits	   TINYINT DEFAULT 1,
    Prerequisites  NVARCHAR(100),
    Classification NVARCHAR(28),
);
GO
INSERT Academics.Courses(CourseCode, CourseName)
VALUES('ENGL-101', 'Introduction to English Writing'),
	  ('STAT-138', 'Introduction to Statistics'),
	  ('BIOL-226', 'Cell Biology'),
	  ('CMSC-121', 'Introduction to Computer Science I'),
	  ('CHEM-210', 'Organic Chemistry I'),
	  ('RSCH-100', 'Academic Research and College Work'),
	  ('ANTH-222', 'Anthropological Theory'),
	  ('CHEM-210-L', 'Organic Chemistry I - Lab'),
	  ('ECON-100', 'Introductory Macroeconomics'),
	  ('CMSC-320', 'Algorithms and Data Structures'),
	  ('ANTH-402', 'Contemporary Ethnographies and Observations'),
	  ('BIOL-226-L', 'Cell Biology - Lab'),
	  ('CMSC-221', 'Introduction to Computer Science II');
GO
SELECT * FROM Academics.Courses;
GO

Record maintenance consists of changing one or more values of a record, changing a group of records in one step, changing all records of a table, deleting one or more values in a record, deleting a series/group of records, or clearing a table of all its records. These operations can be performed visually or programmatically in Microsoft SQL Server Management Studio or Microsoft Visual Studio, or by writing code either in C# or as a Data Definition Language (DDL) command.

Updating the Records of a Table with a Value

Imagine that, at one time, on a particular table, all records need to receive a new value in one particular column or in certain columns. The DDL command to update a record is UPDATE. The basic formula to use is:

UPDATE table-name SET conditional-expression

You must specify the name of the table as the table-name of our formula. The SET statement allows you to specify a conditional-expression. This expression can be formulated using the types of conditions we have used in the past, in which case the conditional-expression can be stated as column-name operator expression.

To update the records, from our UPDATE formula, you can simply compare the intended column to the desired value. Here is an example:

UPDATE Academics.Courses SET Credits = 3;
GO

This would produce:

Updating all Records

Remember that the LIKE operator can be used where the equality operator is used. This means that the above statement can also be written as follows:

UPDATE Academics.Courses LIKE Credits = 3;
GO

As always, to make your code easier to read, you can write it on various lines:

UPDATE table-name
SET conditional-expression

Updating Various Columns

You can update various columns with one UPDATE...SET operation. To do this, after the SET operator, enter each combination of column-name = expression and separate them with commas. The formula to follow is:

UPDATE table-name SET conditional-expression_1, conditional-expression_2, conditional-expression_n

To make your code easy to read, you can distribe it to different lines:

UPDATE table-name
SET conditional-expression_1,
    conditional-expression_2,
    conditional-expression_n

Here is an example:

UPDATE Academics.Courses
SET Classification = 'Undergraduate',
    Prerequisites = 'RSCH-100'

Updating Records

Conditionally Updating One or Some Records Using a Condition

Instead of updating all records, you can set a condition by which some records would be selected. To do this, add a WHERE operator to the UPDATE statement. The formula to follow is:

UPDATE table-name
SET column-name = expression
WHERE condition(s)

The condition is set the same way we saw for data analysis. Here is an exaple:

UPDATE Academics.Courses SET Credits = 5
WHERE CourseCode = 'ANTH-402';
GO

Sets

Conditionally Updating Some Columns

You can update the records of more than one column if they respond to a certain condition. To do this, add the conditional conjunction, disjunctions, or any combination in the SET operation. If you just a conjunction, all columns that apply the conditions would be changed. If you want to apply the same change to different columns, use a disjunction. Here is an example:

UPDATE Academics.Courses SET Credits = 2
WHERE (CourseCode = 'BIOL-226-L') OR (CourseCode = 'CHEM-210-L')

Video Collection

Conditionally Updating Many Records with Different Conditions

You can use a combination of conditions to update various columns. To do this, in the SET section, add the desired combinations of column-name = expression expressions. In the WHERE clause, add the desired conditions. In most cases, you will use disjunctions. Here is an example:

UPDATE Academics.Courses
SET Prerequisites = 'MATH-107, ANTH-110',
    Credits       = 4
WHERE (CourseCode = 'CMSC-221') OR
      (CourseCode = 'BIOL-226') OR
      (CourseCode = 'CMSC-320') OR
      (CourseCode = 'CHEM-210');
GO

Accessing the Records Maintenance

Deleting Records

Deleting all Records

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure.

In the SQL Server Management Studio, to visually delete all records from a table, open it in design view, right-click the button in the top-left corner at the intersection of the column and row headers, and click Delete. As an alternative, first select all of them, and press Delete. In both cases, you would receive a warning. If you still want to delete the records, click Yes. If you change your mind, click No. Be careful when doing this because once the records have been deleted, you cannot get them back.

In SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE table-name;

When this statement executes, all records from the table-name would be removed from the table. Here is an example:

DELETE Academics.Courses;
GO

Removing the First n Records

Instead of removing all records, to delete only the first n of a table, use the following formula:

DELETE TOP (Number) table-name;

In the parentheses, enter the desired number of records. When the statement executes, the first n records of the table would be deleted. Here is an example:

DELETE TOP (2) Employees;
GO

This statement asks the database engine to delete the first two records of the Employees table.

Removing a Specific Record

If you find out that a record is not necessary, not anymore, you can remove it from a table.

TIn the SQL Server Management Studio, to visually remove a record from a table, open the table in Table view. Right-click the row header (the gray box on the left side) of the record and click Delete. You can also first select the record and press Delete. You would receive a warning to confirm your intention.

To programmatically delete a record:

In SQL, to delete a record, use the DELETE FROM statement associated with the WHERE operator. The formula to follow is:

DELETE FROM table-name
WHERE condition

The table-name is used to identify a table whose record(s) would be removed.

The condition allows you to identify a record or a group of records that carries a criterion. Make sure you are precise in your criteria so you would not delete the wrong record(s). Here is an example used to remove a particular record from the table:

DELETE FROM Collection.Videos
WHERE Title = N'The Lady Killers';
GO

Conditionally Removing the First n Records

Consider the following table:

CREATE TABLE Employees
(
	EmployeeNumber int,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(283007, N'Megan',    N'Wright',    8.50),
      (480295, N'Horace',   N'Taylor',   20.25),
      (804805, N'Marc',     N'Knights',  10.85),
      (294117, N'Bryan',    N'Tenant',   30.25),
      (837479, N'Paul',     N'Motto',    18.02),
      (280304, N'Joyce',    N'Holliday', 11.66),
      (924802, N'Peter',    N'Mukoko',    8.68),
      (725381, N'Marianne', N'Brooks',   22.64);
GO

By default, the DELETE expression acts on all records of a table. As an alternative, you can ask the database engine to consider only the first n records of a table. The formula to do this is:

DELETE TOP (number) FROM table-name
WHERE condition(s)

In the parentheses after TOP, enter the desired number of records. When the statement executes, the WHERE condition would be applied on only the first number of records. Any record that falls in that condition would be deleted. Here is an example:

DELETE TOP (4) FROM Employees
WHERE HourlySalary < 12.50;
GO

This code asks the database engine to delete any record in the first four records of the Employees table if the hourly salary of the employee in less than 12.50.

Conditionally Removing the First Percentage of Records

If you don't want to specify a fixed number of records, you can use a percentage instead. The formula to follow is:

DELETE TOP (number) PERCENT FROM table-name
WHERE condition(s)

In the parentheses, enter a number between 0.00 and 100.00 included. The number of records to consider is based on the total number of the records using a percentage. Here is an example:

DELETE TOP (40) PERCENT FROM Employees
WHERE HourlySalary < 12.50;
GO

This code deletes any record whose salary is less than 12.50 but the record must be among the first 40% of the records.

Outputting the Deleted Results

When some record(s) has(have) been deleted, the operation is performed behind the scenes and you don't see the result. In Microsoft SQL Server Management Studio, if you want to see a list of the records that were deleted, you can use the OUTPUT operator to display the result. In Microsoft SQL Server Management, to show the list of the records from a table that was completely emptied, you can use the following formula:

DELETE FROM table-name
OUTPUT DELETED.Columns

The OUTPUT INSERTED expression follows the description we have seen for the record update. Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
GO

To show the list of the records that were deleted based on a condition, use the following formula:

DELETE FROM table-name
OUTPUT DELETED.Columns
WHERE Condition(s)

 Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
WHERE YearReleased IS NULL;
GO

Practical LearningPractical Learning: Ending the Lesson


Previous Copyright © 2001-2019, FunctionX Next