Records Maintenance
Records Maintenance
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 Learning: Introducing Record Maintenance
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;
}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"));
}
}
}<!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">© @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><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> 
USE master; GO CREATE DATABASE GasDistribution; GO USE GasDistribution; GO
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
<?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><?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 Learning: Creating a Record
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 } }
@{
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>
}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 } }
@{
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 Learning: Selecting Records
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; }
}
}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 } }
@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>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; }
}
}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 } }
@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>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; }
}
}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
}
}
@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>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 + ")";
}
}
}
}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 } }
@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>
| 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 |
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 Learning: Selecting a Record
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
}
}@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>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
}
}@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>
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
}
}@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>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
}
}@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>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
}
}@{
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>
}@{
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"> </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"> </label>
<div class="col-md-125"> </div>
<label class="control-label col-md-125"> </label>
<div class="col-md-125"> </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"> </label>
<div class="col-md-125"> </div>
<label class="control-label col-md-125"> </label>
<div class="col-md-125"> </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"> </label>
<div class="col-md-125"> </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"> </label>
<div class="col-md-125"> </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"> </label>
<div class="col-md-125"> </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 Learning: Creating Combo Boxes
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
}
}@{
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>
}
| 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 |
Customer Account #: 7518-302-6895 Meter Reading Date: 7/30/2018 Current Meter Reading: 114
| 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 |
| 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 |
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
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:
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'
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
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 Learning: Updating a Record
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
}
}@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>
}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
}
}@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')
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
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 Learning: Deleting a Record
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();
}
}
}
}
@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>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();
}
}
}
}@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>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:
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'
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
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')
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
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 Learning: Ending the Lesson
|
|
||
| Previous | Copyright © 2001-2021, FunctionX | Next |
|
|
||