WinForms ADO.NET: Stellar Water Point
WinForms ADO.NET: Stellar Water Point
Project Start Up
Introduction
A Windows Forms application is a graphical program that uses one or more forms with Windows controls that make an application very user-friendly. One of the options offered by a Windows Forms application is to support a database where values or records can be created and managed.
In this exercise, we will create a Microsoft SQL Server database used in a Windows Forms application. To create and manage records, we will use ADO.NET abd the C# language.
Practical Learning: Introducing the Application
A Database for an Application
As mentioned above, our water distribution application will use a database. You can create the database using Microsoft SQL Server or in the Visual Studio application as a local database. When setting up the database, we will create tables for water meters, customers, and water bills.
Practical Learning: Preparing a Database
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'StellarWaterPoint26'
)
DROP DATABASE StellarWaterPoint26;
GO
CREATE DATABASE StellarWaterPoint26;
GO
-- Select the newly created database
USE StellarWaterPoint26;
GO
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------
CREATE SCHEMA Management;
GO
-- Tables
CREATE TABLE Management.WaterMeters
(
WaterMeterId INT IDENTITY(1, 1),
MeterNumber NVARCHAR(15) NOT NULL,
Make NVARCHAR(25) NULL,
Model NVARCHAR(20) NOT NULL,
MeterSize NVARCHAR(15) NULL,
CONSTRAINT PK_WaterMeters PRIMARY KEY(WaterMeterId)
);
GO
CREATE TABLE Management.AccountsTypes
(
AccountTypeId INT IDENTITY(1, 1),
TypeCode NVARCHAR(5) NOT NULL,
AccountType NVARCHAR(200) NULL,
CONSTRAINT PK_AccountsTypes PRIMARY KEY(AccountTypeId)
);
GO
CREATE TABLE Management.Customers
(
CustomerId INT IDENTITY(1, 1),
AccountNumber NVARCHAR(15),
AccountName NVARCHAR(150),
MeterNumber NVARCHAR(15),
AccountType NVARCHAR(5),
[Address] NVARCHAR(150),
City NVARCHAR(25),
County NVARCHAR(35),
[State] NVARCHAR(35),
ZIPCode NVARCHAR(12),
CONSTRAINT PK_Customers PRIMARY KEY(CustomerId)
);
GO
CREATE TABLE Management.WaterBills
(
WaterBillId INT IDENTITY(1, 1),
BillNumber INT NOT NULL,
AccountNumber NVARCHAR(15) NOT NULL,
MeterReadingStartDate DATE,
MeterReadingEndDate DATE,
BillingDays INT,
CounterReadingStart INT,
CounterReadingEnd INT,
TotalHCF INT,
TotalGallons INT,
FirstTierConsumption DECIMAL(6, 2),
SecondTierConsumption DECIMAL(6, 2),
LastTierConsumption DECIMAL(6, 2),
WaterCharges DECIMAL(6, 2),
SewerCharges DECIMAL(6, 2),
EnvironmentCharges DECIMAL(6, 2),
ServiceCharges DECIMAL(6, 2),
TotalCharges DECIMAL(6, 2),
LocalTaxes DECIMAL(6, 2),
StateTaxes DECIMAL(6, 2),
PaymentDueDate DATE,
AmountDue DECIMAL(6, 2),
LatePaymentDueDate DATE,
LateAmountDue DECIMAL(6, 2),
CONSTRAINT PK_WaterBills PRIMARY KEY(WaterBillId)
);
GO
-- =================================================================================
-- Views
CREATE VIEW Management.GetAccountsTypes
AS
SELECT TypeCode + N' - ' + AccountType AS AccountType
FROM Management.AccountsTypes
GO
CREATE VIEW Management.GetCustomersAccounts
AS
SELECT clients.CustomerId AS ClientId,
clients.AccountNumber AS AccountNbr,
clients.AccountName AS ClientName,
clients.MeterNumber AS MtrNbr,
acnTypes.TypeCode + N' - ' + acnTypes.AccountType AS AccountType,
clients.[Address] AS Address,
clients.City AS City,
clients.County AS County,
clients.[State] AS State,
clients.ZIPCode AS ZipCode
FROM Management.Customers clients
LEFT OUTER JOIN Management.AccountsTypes acnTypes
ON clients.AccountType = acnTypes.TypeCode;
GO
CREATE VIEW Management.GetWaterBills
AS
SELECT WaterBillId,
BillNumber,
clients.AccountNumber + N' - ' + clients.AccountName + N' (' + clients.AccountType + N'), Mtr #: ' + clients.MeterNumber AS AccountSummary,
MeterReadingStartDate,
MeterReadingEndDate,
BillingDays,
CounterReadingStart,
CounterReadingEnd,
TotalHCF,
TotalGallons,
PaymentDueDate,
AmountDue
FROM Management.WaterBills bills
INNER JOIN Management.Customers clients
ON bills.AccountNumber = clients.AccountNumber;
GO
-- =================================================================================
-- Stored Procedures
CREATE PROCEDURE Management.CreateCustomerAccount
@acntNbr nvarchar(15),
@acntName nvarchar(150),
@mtrNbr nvarchar(15),
@acntType nvarchar(5),
@adrs nvarchar(150),
@city nvarchar(25),
@county nvarchar(35),
@state nvarchar(35),
@zip nvarchar(12)
AS
BEGIN
INSERT INTO Management.Customers(AccountNumber, AccountName, MeterNumber, AccountType, [Address], City, County, [State], ZIPCode)
VALUES( @acntNbr, @acntName, @mtrNbr, @acntType, @adrs, @city, @county, @state, @zip);
END
GO
CREATE PROCEDURE Management.SelectCustomers
@acntNbr nvarchar(15)
AS
BEGIN
SELECT clients.CustomerId AS CustomerId,
clients.AccountNumber AS AccountNumber,
clients.AccountName AS ClientName,
clients.MeterNumber AS MeterNumber,
acnTypes.TypeCode + N' - ' + acnTypes.AccountType AS AccountType,
clients.[Address] AS Address,
clients.City AS City,
clients.County AS County,
clients.[State] AS State,
clients.ZIPCode AS ZipCode
FROM Management.Customers clients
LEFT OUTER JOIN Management.AccountsTypes acnTypes
ON clients.AccountType = acnTypes.TypeCode
WHERE clients.AccountNumber = @acntNbr;
END
GO
CREATE PROCEDURE Management.UpdateCustomerAccount
@acntNbr nvarchar(15),
@mtrNbr nvarchar(15),
@acntName nvarchar(150),
@acntType nvarchar(5),
@adrs nvarchar(150),
@city nvarchar(25),
@county nvarchar(35),
@state nvarchar(35),
@zip nvarchar(12)
AS
BEGIN
UPDATE Management.Customers SET MeterNumber = @mtrNbr WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET AccountName = @acntName WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET AccountType = @acntType WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET [Address] = @adrs WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET City = @city WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET County = @county WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET [State] = @state WHERE AccountNumber = @acntNbr;
UPDATE Management.Customers SET ZIPCode = @zip WHERE AccountNumber = @acntNbr;
END
GO
INSERT INTO Management.AccountsTypes(TypeCode, AccountType)
VALUES(N'OTH', N'Other'),
(N'BUS', N'General Business'),
(N'RES', N'Residential Household'),
(N'SGO', N'Social/Government/Non-Profit Organization'),
(N'UUO', N'Unidentified or Unclassified Type of Organization'),
(N'WAT', N'Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc');
GOThe Main Form of the Application
.
Practical Learning: Preparing the Main Form of the Application
Water Meters
Introduction
Our application will use forms to create water meter records, to view a record of a water meter, to edit or to delete the record of a water meter.
Practical Learning: Introducing Water Meters
Displaying Water Meters
To let the user see a list of the water meters in the database, we will use a form equipped with a list view.
Practical Learning: Displaying Water Meters
| (Name) | Text | Width | TextAlign |
| colWaterMeterId | Id | 40 | |
| colMeterNumber | Meter # | 150 | Center |
| colMake | Make | 300 | |
| colModel | Model | 150 | |
| colMeterSize | Meter Size | 150 |
| Control | (Name) | Other Properties | |
| ListView |  |
lvwWaterMeters | FullRowSelect: True GridLines: True View: Details |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterId, " +
" MeterNumber, " +
" Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
lvwWaterMeters.Items.Clear();
foreach(DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
ListViewItem lviWaterMeter = new(@drWaterMeter[0].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[1].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[2].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[3].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[4].ToString());
lvwWaterMeters.Items.Add(lviWaterMeter);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
}
}
| Control | (Name) | Text | Font | |
| Button |  |
btnWaterMeters | &Water Meters... | Times New Roman, 24pt, style=Bold |
namespace StellarWaterPoint3
{
public partial class WaterDistribution : Form
{
public WaterDistribution()
{
InitializeComponent();
}
private void btnWaterMeters_Click(object sender, EventArgs e)
{
WaterMeters.Central central = new WaterMeters.Central();
central.ShowDialog();
}
}
}A Water Meter Record
.
Practical Learning: Creating a Water Meter Record
| Control | (Name) | Text | Other Properties | |
| Label |  |
&Meter #: | ||
| MaskedTextBox |  |
mtbMeterNumber | Masked: 000-000-000 | |
| Label | |
M&ake: | ||
| TextBox |  |
txtMake | Modifiers: Public | |
| Label | |
M&odel: | ||
| TextBox | |
txtModel | Modifiers: Public | |
| Label | |
Me&ter Size: | ||
| TextBox | |
txtMeterSize | Modifiers: Public | |
| Button | |
btnOK | &OK | DialogResult: OK |
| Button | |
btnCancel | &Cancel | DialogResult: Cancel |
FormBorderStyle: FixedDialog Text: Stellar Water Point - Water Meter Setup StartPosition: CenterScreen AcceptButton: btnOK CancelButton: btnCancel
| Control | (Name) | Other Properties | |
| ListView | |
No Change | No Change |
| Button | |
btnNewWaterMeter | Text: &New Water Meter... |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterId, " +
" MeterNumber, " +
" Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
lvwWaterMeters.Items.Clear();
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
ListViewItem lviWaterMeter = new(@drWaterMeter[0].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[1].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[2].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[3].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[4].ToString());
lvwWaterMeters.Items.Add(lviWaterMeter);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
Create create = new();
if(create.ShowDialog() == DialogResult.OK)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterMeters(MeterNumber, Make, Model, MeterSize) " +
"VALUES(N'" + create.mtbMeterNumber.Text + "', N'" +
create.txtMake.Text + "', N'" +
create.txtModel.Text + "', N'" +
create.txtMeterSize.Text + "');",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
}
ShowWaterMeters();
}
}
}
| Meter # | Make | Model | Meter Size |
| 392-494-572 | Constance Technologies | TG-4822 | 5/8 Inches |
| 938-705-869 | Stan Wood | 66-G | 1 Inch |
| 588-279-663 | Estellano | NCF-226 | 3/4 Inches |
Water Meter Details
.
Practical Learning: Creating a Water Meter Record
| Control | (Name) | Text | Enabled | Modifiers | Other Properties | |
| Label | |
&Meter #: | ||||
| MaskedTextBox | |
mtbMeterNumber | False | Public | Masked: 000-000-000 | |
| Button | |
btnFindWaterMeter | &Find Water Meter | |||
| Label | |
Make: | ||||
| TextBox | |
txtMake | False | Public | ||
| Label | |
Model: | ||||
| TextBox | |
txtModel | False | Public | ||
| Label | |
Meter Size: | ||||
| TextBox | |
txtMeterSize | False | Public | ||
| Button | |
btnClose | &Close | |||
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Details : Form
{
public Details()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMake.Text = drWaterMeter[0].ToString();
txtModel.Text = drWaterMeter[1].ToString();
txtMeterSize.Text = drWaterMeter[2].ToString();
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
using System.Data;
using System.Xml;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
. . .
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
if (lvwWaterMeters.SelectedItems.Count > 0)
{
Details details = new();
details.mtbMeterNumber.Text = lvwWaterMeters.SelectedItems[0].SubItems[1].Text;
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + details.mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
details.txtMake.Text = drWaterMeter[0].ToString();
details.txtModel.Text = drWaterMeter[1].ToString();
details.txtMeterSize.Text = drWaterMeter[2].ToString();
}
details.ShowDialog();
}
}
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
No Change | No Change |
| Button | |
No Change | No Change |
| Button | |
btnViewWaterMeter | &View Water Meter... |
using System.Data;
using System.Xml;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
. . .
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
. . .
}
private void btnViewWaterMeter_Click(object sender, EventArgs e)
{
Details view = new();
view.ShowDialog();
ShowWaterMeters();
}
}
}
Updating a Water Meter Details
One of the routine operations performed on a database is to change the details of a record. To support this operation for a water meter, we will create a form that can be used to update the information of a water meter.
Practical Learning: Updating a Water Meter
| Control | (Name) | Text | Enabled | Other Properties | ||
| Label | |
&Meter #: | ||||
| MaskedTextBox | |
mtbMeterNumber | False | Masked: 000-000-000 | ||
| Button | |
btnFindWaterMeter | &Find Water Meter | |||
| Label | |
Make: | ||||
| TextBox | |
txtMake | False | |||
| Label | |
Model: | ||||
| TextBox | |
txtModel | False | |||
| Label | |
Meter Size: | ||||
| TextBox | |
txtMeterSize | False | |||
| Button | |
btnFindWateMeter | &Find Wate Meter | |||
| Button | |
btnClose | &Close | |||
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMake.Text = drWaterMeter[0].ToString();
txtModel.Text = drWaterMeter[1].ToString();
txtMeterSize.Text = drWaterMeter[2].ToString();
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
. . .
}
private void btnUpdateWaterMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("UPDATE Management.WaterMeters SET Make = N'" + txtMake.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';" +
"UPDATE Management.WaterMeters SET Model = N'" + txtModel.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';" +
"UPDATE Management.WaterMeters SET MeterSize = N'" + txtMeterSize.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMake.Text = drWaterMeter[0].ToString();
txtModel.Text = drWaterMeter[1].ToString();
txtMeterSize.Text = drWaterMeter[2].ToString();
}
}
}
private void btnUpdateWaterMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("UPDATE Management.WaterMeters SET Make = N'" + txtMake.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';" +
"UPDATE Management.WaterMeters SET Model = N'" + txtModel.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';" +
"UPDATE Management.WaterMeters SET MeterSize = N'" + txtMeterSize.Text + "' WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
Close();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
No Change | No Change |
| Button | |
No Change | No Change |
| Button | |
No Change | No Change |
| Button | |
btnEditWaterMeter | &Edit Water Meter... Anchor: Bottom, Right |
using System.Xml;
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
. . .
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
. . .
}
private void btnViewWaterMeter_Click(object sender, EventArgs e)
{
. . .
}
private void btnEditWaterMeter_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterMeters();
}
}
}
| Make: | Stanford Trend | Model: | 266G |
| Meter Size: | 1 1/2 Inches |
Removing a Water Meter from the Database
If a record is not necessary anymore on a database, the user may want to remove it. To assist the user with this operation, we will create a form withe necessary buttons.
Practical Learning: Deleting a Water Meter Record
| Control | (Name) | Text | Enabled | Other Properties | |
| Label | |
&Meter #: | |||
| MaskedTextBox | |
mtbMeterNumber | False | Masked: 000-000-000 | |
| Label | |
Make: | |||
| TextBox | |
txtMake | False | ||
| Label | |
Model: | |||
| TextBox | |
txtModel | False | ||
| Label | |
Meter Size: | |||
| TextBox | |
txtMeterSize | False | ||
| Button | |
btnClose | &Close | ||
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMake.Text = drWaterMeter[0].ToString();
txtModel.Text = drWaterMeter[1].ToString();
txtMeterSize.Text = drWaterMeter[2].ToString();
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
. . .
}
private void btnDeleteWaterMeter_Click(object sender, EventArgs e)
{
if(MessageBox.Show("Are you sure you want to delete the water meter with meter number " + mtbMeterNumber.Text + "?",
"Stellar Water Point",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("DELETE FROM Management.WaterMeters WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
}
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMake.Text = drWaterMeter[0].ToString();
txtModel.Text = drWaterMeter[1].ToString();
txtMeterSize.Text = drWaterMeter[2].ToString();
}
}
}
private void btnDeleteWaterMeter_Click(object sender, EventArgs e)
{
if(MessageBox.Show("Are you sure you want to delete the water meter with meter number " + mtbMeterNumber.Text + "?",
"Stellar Water Point",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("DELETE FROM Management.WaterMeters WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
}
Close();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Text | Anchor | |
| ListView | |
lvwWaterMeters | Top, Bottom, Left, Right | |
| Button | |
btnNewWaterMeter | &New Water Meter... | Bottom, Right |
| Button | |
btnViewWaterMeter | &View Water Meter... | Bottom, Right |
| Button | |
btnEditWaterMeter | &Edit Water Meter... | Bottom, Right |
| Button | |
btnDeleteWateMeter | &Delete Water Meter... | Bottom, Right |
| Button | |
btnClose | &Close | Bottom, Right |
using System.Data;
using System.Xml;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterMeters()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
. . .
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
. . .
}
private void btnViewWaterMeter_Click(object sender, EventArgs e)
{
Details view = new();
view.ShowDialog();
ShowWaterMeters();
}
private void btnEditWaterMeter_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterMeters();
}
private void btnDeleteWateMeter_Click(object sender, EventArgs e)
{
Delete delete = new();
delete.ShowDialog();
ShowWaterMeters();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterMeters(MeterNumber, Make, Model, MeterSize)" +
"VALUES(N'392-494-572', N'Constance Technologies', N'TG-4822', N'5/8 Inches'), " +
" (N'938-725-869', N'Stanford Trend', N'266G', N'1 1/2 Inches')," +
" (N'588-279-663', N'Estellano', N'NCF-226', N'4 Inches'), " +
" (N'186-962-805', N'Lansome', N'2800', N'1 1/2 Inches')," +
" (N'379-386-979', N'Planetra', N'P-2020', N'4 Inches'), " +
" (N'580-742-825', N'Kensa Sons', N'KS2000A', N'1 3/4 Inches')," +
" (N'849-351-444', N'Raynes Energica', N'a1088', N'2 Inches'), " +
" (N'208-428-308', N'Constance Technologies', N'808D', N'3/4 Inches'), " +
" (N'738-588-249', N'Warrington', N'W4242', N'5/8 Inches'), " +
" (N'496-813-794', N'Estellano', N'NCF-226', N'3/4 Inches'), " +
" (N'862-715-006', N'Warrington', N'W-4040', N'1/2 Inch'), " +
" (N'649-358-184', N'Raynes Energica', N'b1700', N'1 1/2 Inches')," +
" (N'928-317-924', N'Raynes Energica', N'GN1000', N'2 Inch'), " +
" (N'595-753-147', N'Raynes Energica', N'CRC-1000', N'1 Inch'), " +
" (N'799-528-461', N'Kensa Sons', N'K-584-L', N'3/4 Inches'), " +
" (N'386-468-057', N'Estellano', N'NCF-226', N'3/4 Inches'), " +
" (N'938-275-294', N'Constance Technologies', N'TT-8822', N'4 Inches'), " +
" (N'288-427-585', N'Planetra', N'P-2020', N'1/2 Inch'), " +
" (N'394-835-297', N'Raynes Energica', N'i2022', N'3/4 Inches'), " +
" (N'847-252-246', N'Raynes Energica', N'2000-MS', N'1 1/2 Inches')," +
" (N'349-725-848', N'Planetra', N'P-8000', N'4 Inches'), " +
" (N'713-942-058', N'Raynes Energica', N'3366-MS', N'3/4 Inches'), " +
" (N'747-581-379', N'Warrington', N'W4242', N'5/8 Inches'), " +
" (N'582-755-263', N'Kensa Sons', N'KS2000A', N'1 Inch'), " +
" (N'827-260-758', N'Raynes Energica', N'a1088', N'1-1/4 Inch'), " +
" (N'837-806-836', N'Lansome', N'7400', N'5/8 Inches'), " +
" (N'207-964-835', N'Constance Technologies', N'TG-6220', N'5/8 Inches'), " +
" (N'296-837-495', N'Raynes Energica', N'QG505', N'4 Inches'), " +
" (N'468-359-486', N'Raynes Energica', N'KLP-8822', N'1-1/4 Inch'), " +
" (N'931-486-003', N'Planetra', N'P-2020', N'1/2 Inch'), " +
" (N'483-770-648', N'Raynes Energica', N'WW-4404', N'1 Inch'), " +
" (N'592-824-957', N'Kensa Sons', N'D-497-H', N'3/4 Inches'), " +
" (N'293-835-704', N'Raynes Energica', N'GL-1000', N'1/2 Inch'), " +
" (N'739-777-749', N'Warrington', N'W2200W', N'3/4 Inches'), " +
" (N'374-886-284', N'Raynes Energica', N'i2022', N'3/4 Inches'), " +
" (N'186-959-757', N'Kensa Sons', N'M-686-G', N'1 1/2 Inches')," +
" (N'594-827-359', N'Planetra', N'P-8000', N'1 Inch'), " +
" (N'394-739-242', N'Raynes Energica', N'9393-TT', N'5/8 Inches'), " +
" (N'529-283-752', N'Constance Technologies', N'404T', N'3/4 Inches'), " +
" (N'295-770-695', N'Warrington', N'W-2286', N'1-1/4 Inch'), " +
" (N'739-749-737', N'Kensa Sons', N'KS2000A', N'1 Inch'), " +
" (N'947-528-317', N'Raynes Energica', N'GDL-5000', N'1 Inch'), " +
" (N'630-207-055', N'Lansome', N'2800', N'3/4 Inches'), " +
" (N'827-508-248', N'Standard Trend', N'428T', N'3/4 Inches'), " +
" (N'293-924-869', N'Raynes Energica', N'CRC-2020', N'1/2 Inch'), " +
" (N'928-247-580', N'Raynes Energica', N'GOL2000', N'0.34 Inch'), " +
" (N'682-537-380', N'Planetra', N'P-2020', N'1-1/4 Inch'), " +
" (N'470-628-850', N'Estellano', N'WRT-482', N'3/4 Inches'), " +
" (N'649-373-505', N'Constance Technologies', N'BD-7000', N'5/8 Inches');",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
ShowWaterMeters();
}
private void ShowWaterMeters()
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterId, " +
" MeterNumber, " +
" Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
lvwWaterMeters.Items.Clear();
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
ListViewItem lviWaterMeter = new(@drWaterMeter[0].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[1].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[2].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[3].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[4].ToString());
lvwWaterMeters.Items.Add(lviWaterMeter);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
Create create = new();
if (create.ShowDialog() == DialogResult.OK)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterMeters(MeterNumber, Make, Model, MeterSize) " +
"VALUES(N'" + create.mtbMeterNumber.Text + "', N'" +
create.txtMake.Text + "', N'" +
create.txtModel.Text + "', N'" +
create.txtMeterSize.Text + "');",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
}
ShowWaterMeters();
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
if (lvwWaterMeters.SelectedItems.Count > 0)
{
Details details = new();
details.mtbMeterNumber.Text = lvwWaterMeters.SelectedItems[0].SubItems[1].Text;
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + details.mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
details.txtMake.Text = drWaterMeter[0].ToString();
details.txtModel.Text = drWaterMeter[1].ToString();
details.txtMeterSize.Text = drWaterMeter[2].ToString();
}
details.ShowDialog();
}
}
}
private void btnViewWaterMeter_Click(object sender, EventArgs e)
{
Details view = new();
view.ShowDialog();
ShowWaterMeters();
}
private void btnEditWaterMeter_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterMeters();
}
private void btnDeleteWateMeter_Click(object sender, EventArgs e)
{
Delete delete = new();
delete.ShowDialog();
ShowWaterMeters();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.WaterMeters
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
/* using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterMeters(MeterNumber, Make, Model, MeterSize)" +
"VALUES(N'392-494-572', N'Constance Technologies', N'TG-4822', N'5/8 Inches'), " +
" (N'938-725-869', N'Stanford Trend', N'266G', N'1 1/2 Inches')," +
" (N'588-279-663', N'Estellano', N'NCF-226', N'4 Inches'), " +
" (N'186-962-805', N'Lansome', N'2800', N'1 1/2 Inches')," +
" (N'379-386-979', N'Planetra', N'P-2020', N'4 Inches'), " +
" (N'580-742-825', N'Kensa Sons', N'KS2000A', N'1 3/4 Inches')," +
" (N'849-351-444', N'Raynes Energica', N'a1088', N'2 Inches'), " +
" (N'208-428-308', N'Constance Technologies', N'808D', N'3/4 Inches'), " +
" (N'738-588-249', N'Warrington', N'W4242', N'5/8 Inches'), " +
" (N'496-813-794', N'Estellano', N'NCF-226', N'3/4 Inches'), " +
" (N'862-715-006', N'Warrington', N'W-4040', N'1/2 Inch'), " +
" (N'649-358-184', N'Raynes Energica', N'b1700', N'1 1/2 Inches')," +
" (N'928-317-924', N'Raynes Energica', N'GN1000', N'2 Inch'), " +
" (N'595-753-147', N'Raynes Energica', N'CRC-1000', N'1 Inch'), " +
" (N'799-528-461', N'Kensa Sons', N'K-584-L', N'3/4 Inches'), " +
" (N'386-468-057', N'Estellano', N'NCF-226', N'3/4 Inches'), " +
" (N'938-275-294', N'Constance Technologies', N'TT-8822', N'4 Inches'), " +
" (N'288-427-585', N'Planetra', N'P-2020', N'1/2 Inch'), " +
" (N'394-835-297', N'Raynes Energica', N'i2022', N'3/4 Inches'), " +
" (N'847-252-246', N'Raynes Energica', N'2000-MS', N'1 1/2 Inches')," +
" (N'349-725-848', N'Planetra', N'P-8000', N'4 Inches'), " +
" (N'713-942-058', N'Raynes Energica', N'3366-MS', N'3/4 Inches'), " +
" (N'747-581-379', N'Warrington', N'W4242', N'5/8 Inches'), " +
" (N'582-755-263', N'Kensa Sons', N'KS2000A', N'1 Inch'), " +
" (N'827-260-758', N'Raynes Energica', N'a1088', N'1-1/4 Inch'), " +
" (N'837-806-836', N'Lansome', N'7400', N'5/8 Inches'), " +
" (N'207-964-835', N'Constance Technologies', N'TG-6220', N'5/8 Inches'), " +
" (N'296-837-495', N'Raynes Energica', N'QG505', N'4 Inches'), " +
" (N'468-359-486', N'Raynes Energica', N'KLP-8822', N'1-1/4 Inch'), " +
" (N'931-486-003', N'Planetra', N'P-2020', N'1/2 Inch'), " +
" (N'483-770-648', N'Raynes Energica', N'WW-4404', N'1 Inch'), " +
" (N'592-824-957', N'Kensa Sons', N'D-497-H', N'3/4 Inches'), " +
" (N'293-835-704', N'Raynes Energica', N'GL-1000', N'1/2 Inch'), " +
" (N'739-777-749', N'Warrington', N'W2200W', N'3/4 Inches'), " +
" (N'374-886-284', N'Raynes Energica', N'i2022', N'3/4 Inches'), " +
" (N'186-959-757', N'Kensa Sons', N'M-686-G', N'1 1/2 Inches')," +
" (N'594-827-359', N'Planetra', N'P-8000', N'1 Inch'), " +
" (N'394-739-242', N'Raynes Energica', N'9393-TT', N'5/8 Inches'), " +
" (N'529-283-752', N'Constance Technologies', N'404T', N'3/4 Inches'), " +
" (N'295-770-695', N'Warrington', N'W-2286', N'1-1/4 Inch'), " +
" (N'739-749-737', N'Kensa Sons', N'KS2000A', N'1 Inch'), " +
" (N'947-528-317', N'Raynes Energica', N'GDL-5000', N'1 Inch'), " +
" (N'630-207-055', N'Lansome', N'2800', N'3/4 Inches'), " +
" (N'827-508-248', N'Standard Trend', N'428T', N'3/4 Inches'), " +
" (N'293-924-869', N'Raynes Energica', N'CRC-2020', N'1/2 Inch'), " +
" (N'928-247-580', N'Raynes Energica', N'GOL2000', N'0.34 Inch'), " +
" (N'682-537-380', N'Planetra', N'P-2020', N'1-1/4 Inch'), " +
" (N'470-628-850', N'Estellano', N'WRT-482', N'3/4 Inches'), " +
" (N'649-373-505', N'Constance Technologies', N'BD-7000', N'5/8 Inches');",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
ShowWaterMeters(); */
}
private void ShowWaterMeters()
{
// Make a connection to the database stored in the designated server
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
/* Create a database command that selects all items from the WaterMeters table.
* Indicate that the command will use the above connection. */
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterId, " +
" MeterNumber, " +
" Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters; ",
scWaterDistribution);
// Create a data set that will hold a collection of the records from the WaterMeters table.
DataSet dsWaterMeters = new("WaterMetersSet");
// Open the database connection
scWaterDistribution.Open();
// Create a data adapter and pass the above command to it
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
// Get the records from the above data adapter and store those records in the data set that was created.
sdaWaterMeters.Fill(dsWaterMeters);
// Before displaying the records in the list view, in case there are some records in it, remove them
lvwWaterMeters.Items.Clear();
/* Check each record from the WaterMeters table.
* Remember that the records were stored in the data set. */
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
// Get each record and display it in a subsequent row of the list view
ListViewItem lviWaterMeter = new(@drWaterMeter[0].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[1].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[2].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[3].ToString());
lviWaterMeter.SubItems.Add(drWaterMeter[4].ToString());
lvwWaterMeters.Items.Add(lviWaterMeter);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterMeters();
}
private void btnNewWaterMeter_Click(object sender, EventArgs e)
{
/* The form that is used to create a water meter record is named Create.
* Therefore, to prepare to start a water meter record, declare a variable of that form. */
Create create = new();
/* Display the Create form as a dialog box.
* Find out if the user pressed Enter or clicked
* the Save Water Meter button to close the Create dialog box.*/
if (create.ShowDialog() == DialogResult.OK)
{
// Establish a connection to the database on the server
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
/* Create a command that can add a record to the WaterMeters table.
* Get the values from the WaterMeters.Create dialog box. */
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterMeters(MeterNumber, Make, Model, MeterSize) " +
"VALUES(N'" + create.mtbMeterNumber.Text + "', N'" +
create.txtMake.Text + "', N'" +
create.txtModel.Text + "', N'" +
create.txtMeterSize.Text + "');",
scWaterDistribution);
// Open the database connection
scWaterDistribution.Open();
// Execute the command to create the new record.
cmdWaterMeters.ExecuteNonQuery();
}
}
/* When the user closes the WaterMeters.Create dialog box,
* (re)display the list of water meters on the list view.*/
ShowWaterMeters();
}
private void lvwWaterMeters_DoubleClick(object sender, EventArgs e)
{
// Prepare a data set that will hold a collection of the records from the WaterMeters table.
DataSet dsWaterMeters = new("WaterMetersSet");
// When the user double-clicks the list view, find out if the user double-clicked a certain record
if (lvwWaterMeters.SelectedItems.Count > 0)
{
// If the user double-clicked a record, create a reference to the Details form
Details details = new();
/* Get the meter number of the record that the user double-clicked,
* pass that meter number to the Meter Number text box of the Details form. */
details.mtbMeterNumber.Text = lvwWaterMeters.SelectedItems[0].SubItems[1].Text;
// Establish a connection to the database that is in the server
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
/* Create a command that will select the values of the Make, the Model,
* and the Meter Size fields of the WaterMeters table.
* Add a WHERE condition to the command to select only the water meter
* whose meter number is the same as the one the user double-clicked on the list view. */
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + details.mtbMeterNumber.Text + "';",
scWaterDistribution);
// Open the connection that was established.
scWaterDistribution.Open();
// Create a data adapter and pass the above command to it.
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
// Get the record from the data adapter and store it in the data set that was prepared.
sdaWaterMeters.Fill(dsWaterMeters);
/* Get the values of the Make, the Model, and the Meter Size of the record that was selected. */
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
// Prepare to display each value in its corresponding text box on the Details form
details.txtMake.Text = drWaterMeter[0].ToString();
details.txtModel.Text = drWaterMeter[1].ToString();
details.txtMeterSize.Text = drWaterMeter[2].ToString();
}
// Display the Details form with the values that were prepared.
details.ShowDialog();
}
}
}
private void btnViewWaterMeter_Click(object sender, EventArgs e)
{
Details view = new();
view.ShowDialog();
ShowWaterMeters();
}
private void btnEditWaterMeter_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterMeters();
}
private void btnDeleteWateMeter_Click(object sender, EventArgs e)
{
Delete delete = new();
delete.ShowDialog();
ShowWaterMeters();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}Customers
Introduction
.
Practical Learning: Introducing Customers
Displaying Customers
.
Practical Learning: Displaying Customers
| (Name) | Text | TextAlign | Width |
| colCustomerId | Id | 40 | |
| colAccountNumber | Account # | Center | 150 |
| colAccountName | Account Name | 200 | |
| colMeterNumber | Meter # | Center | 100 |
| colAccountType | Account Type | 200 | |
| colAddress | Address | 250 | |
| colCity | City | 125 | |
| colCounty | County | 125 | |
| colState | State | Center | |
| colZIPCode | ZIP-Code | Center | 125 |
| Control | (Name) | Other Properties | |
| ListView | |
lvwCustomers | FullRowSelect: True GridLines: True View: Details |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.Customers
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowCustomers()
{
DataSet dsCustomers = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT clients.CustomerId, " +
" clients.AccountNumber, " +
" clients.AccountName, " +
" clients.MeterNumber, " +
" acnTypes.TypeCode + N' - ' + acnTypes.AccountType AS AccountType, " +
" clients.[Address], " +
" clients.City, " +
" clients.County, " +
" clients.[State], " +
" clients.ZIPCode " +
"FROM Management.Customers clients " +
"INNER JOIN Management.AccountsTypes acnTypes " +
"ON clients.AccountType = acnTypes.TypeCode; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
sdaCustomers.Fill(dsCustomers);
lvwCustomers.Items.Clear();
foreach (DataRow drCustomer in dsCustomers.Tables[0].Rows!)
{
ListViewItem lviCustomer = new(drCustomer["CustomerId"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountNumber"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountName"].ToString());
lviCustomer.SubItems.Add(drCustomer["MeterNumber"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountType"].ToString());
lviCustomer.SubItems.Add(drCustomer["Address"].ToString());
lviCustomer.SubItems.Add(drCustomer["City"].ToString());
lviCustomer.SubItems.Add(drCustomer["County"].ToString());
lviCustomer.SubItems.Add(drCustomer["State"].ToString());
lviCustomer.SubItems.Add(drCustomer["ZIPCode"].ToString());
lvwCustomers.Items.Add(lviCustomer);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowCustomers();
}
}
}
| Control | (Name) | Text | Font | |
| Button | |
btnCustomers | C&ustomers... | Times New Roman, 24pt, style=Bold |
namespace StellarWaterPoint30
{
public partial class WaterDistribution : Form
{
public WaterDistribution()
{
InitializeComponent();
}
private void btnCustomers_Click(object sender, EventArgs e)
{
Customers.Central clients = new();
clients.ShowDialog();
}
private void btnWaterMeters_Click(object o, EventArgs e)
{
WaterMeters.Central central = new WaterMeters.Central();
central.ShowDialog();
}
}
}A New Customer Account
.
Practical Learning: Creating a Customer Account
| Control | (Name) | Text | Other Properties | |
| Label | |
&Account #: | ||
| MaskedTextBox | |
mtbAccountNumber | Masked: 0000-000-0000 | |
| Label | |
&Account Name: | ||
| TextBox | |
txtAccountName | ||
| Label | |
&Meter #: | ||
| MaskedTextBox | |
mtbMeterNumber | Masked: 000-000-000 | |
| Button | |
btnFindWaterMeter | &Find Water Meter | |
| Label | |
Meter &Details: | ||
| TextBox | |
txtMeterDetails | Enabled: False | |
| Label | |
&Account Type: | ||
| ComboBox |  |
cbxAccountsTypes | ||
| Label | |
&Address: | ||
| TextBox | |
txtAddress | ||
| Label | |
C&ity: | ||
| TextBox | |
txtCity | ||
| Label | |
C&ounty: | ||
| TextBox | |
txtCounty | ||
| Label | |
&State: | ||
| TextBox | |
txtState | ||
| Label | |
&ZIP-Code: | ||
| MaskedTextBox | |
mtbZIPCode | Masked: Zip-Code | |
| Button | |
btnSaveCustomerAccount | S&ave Customer Account | DialogResult: OK |
| Button | |
btnClose | &Close | DialogResult: Cancel |
FormBorderStyle: FixedDialog Text: Stellar Water Point - Create Customer Account StartPosition: CenterScreen AcceptButton: btnCreateCustomerAccount CancelButton: btnCancel
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void Create_Load(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdAccountsTypes = new SqlCommand("SELECT AccountType " +
"FROM Management.GetAccountsTypes;",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaAccountsTypes = new SqlDataAdapter(cmdAccountsTypes);
DataSet dsAccountsTypes = new("AccountsTypesSet");
sdaAccountsTypes.Fill(dsAccountsTypes);
foreach (DataRow drWaterMeter in dsAccountsTypes.Tables[0].Rows!)
{
cbxAccountsTypes.Items.Add(drWaterMeter[0].ToString()!);
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void Create_Load(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdAccountsTypes = new SqlCommand("SELECT AccountType " +
"FROM Management.GetAccountsTypes;",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaAccountsTypes = new SqlDataAdapter(cmdAccountsTypes);
DataSet dsAccountsTypes = new("AccountsTypesSet");
sdaAccountsTypes.Fill(dsAccountsTypes);
foreach (DataRow drWaterMeter in dsAccountsTypes.Tables[0].Rows!)
{
cbxAccountsTypes.Items.Add(drWaterMeter[0].ToString()!);
}
}
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwCustomers | FullRowSelect: True GridLines: True View: Details |
| Button | |
btnNewCustomerAccount | &New Customer Account... |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint30.Customers
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowCustomers()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowCustomers();
}
private void btnCreateCustomerAccount_Click(object sender, EventArgs e)
{
Create create = new();
if (create.ShowDialog() == DialogResult.OK)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint30';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.CreateCustomerAccount", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntName";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtAccountName.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@mtrNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 10;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbMeterNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntType";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 5;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.cbxAccountsTypes.Text[..3];
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@adrs";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtAddress.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@city";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 25;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtCity.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@county";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtCounty.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@state";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtState.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@zip";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 12;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbZIPCode.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
scCustomers.ExecuteNonQuery();
}
}
}
ShowCustomers();
}
}
}
Account #: 9279-570-8394 Account Name: Thomas Stones Meter #: 799-528-461 Account Type: RES - Residential Household Address: 10252 Broward Ave #D4 City: Frederick County: Frederick State: MD ZIP-Code: 21703-4422
| Account # | Account Name | Meter # | Account Type | Address | City | County | State | ZIP-Code |
| 4086-938-4783 | Hernola Dough | 594-827-359 | UUO - Unidentified or Unclassified Type of Organization | 10 10 Hexagonal Drv | Winston | Yoke | Penn | 11402-4411 |
| 7080-583-5947 | Sunny Yard | 827-508-248 | WAT - Water Intensive Business(Laudromat, Hair Salon, Restaurant, etc | 663 Sherry Wood East Street | Shimpstown | Franklin | PA | 17236-2626 |
Customer Account Details
.
Practical Learning: Showing Customer Account
| Control | (Name) | Text | Other Properties | |
| Label | |
&Account #: | ||
| MaskedTextBox | |
mtbAccountNumber | Masked: 0000-000-0000 | |
| Label | |
&Account Name: | ||
| TextBox | |
txtAccountName | Enabled: False | |
| Label | |
Meter &Details: | ||
| TextBox | |
txtMeterDetails | Enabled: False | |
| Label | |
&Account Type: | ||
| TextBox | |
txtAccountsTypes | Enabled: False | |
| Label | |
&Address: | ||
| TextBox | |
txtAddress | Enabled: False | |
| Label | |
C&ity: | ||
| TextBox | |
txtCity | Enabled: False | |
| Label | |
C&ounty: | ||
| TextBox | |
txtCounty | Enabled: False | |
| Label | |
&State: | ||
| TextBox | |
txtState | Enabled: False | |
| Label | |
&ZIP-Code: | ||
| TextBox | |
txtZIPCode | Enabled: False | |
| Button | |
btnClose | &Close | |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Details : Form
{
public Details()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[1].ToString();
strMeterNumber = drCustomerAccount[2].ToString();
txtAccountType.Text = drCustomerAccount[3].ToString();
txtAddress.Text = drCustomerAccount[4].ToString();
txtCity.Text = drCustomerAccount[5].ToString();
txtCounty.Text = drCustomerAccount[6].ToString();
txtState.Text = drCustomerAccount[7].ToString();
txtZIPCode.Text = drCustomerAccount[8].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwWaterMeters | No Change |
| Button | |
No Change | No Change |
| Button | |
btnCustomerAccountDetails | Customer Account &Details... Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowCustomers()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowCustomers();
}
private void btnCreateCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void btnCustomerAccountDetails_Click(object sender, EventArgs e)
{
Details details = new();
details.ShowDialog();
ShowCustomers();
}
}
}
Editing a Customer Account
.
Practical Learning: Editing a Customer Account
| Control | (Name) | Text | Other Properties | |
| Label | |
&Account #: | ||
| MaskedTextBox | |
mtbAccountNumber | Masked: 0000-000-0000 | |
| Button | |
btnFindCustomerAccount | &Find Customer Account | |
| Label | |
&Account Name: | ||
| TextBox | |
txtAccountName | ||
| Label | |
&Meter #: | ||
| MaskedTextBox | |
mtbMeterNumber | Masked: 000-000-000 | |
| Button | |
btnFindWaterMeter | Find &Water Meter | |
| Label | |
Meter &Details: | ||
| TextBox | |
txtMeterDetails | Enabled: False | |
| Label | |
&Account Type: | ||
| ComboBox | |
cbxAccountsTypes | ||
| Label | |
&Address: | ||
| TextBox | |
txtAddress | ||
| Label | |
C&ity: | ||
| TextBox | |
txtCity | ||
| Label | |
C&ounty: | ||
| TextBox | |
txtCounty | ||
| Label | |
&State: | ||
| TextBox | |
txtState | ||
| Label | |
&ZIP-Code: | ||
| MaskedTextBox | |
mtbZIPCode | Masked: Zip-Code | |
| Button | |
btnUpdateCustomerAccount | &Update Customer Account | DialogResult: OK |
| Button | |
btnClose | &Close | DialogResult: Cancel |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void Editor_Load(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdAccountsTypes = new SqlCommand("SELECT AccountType " +
"FROM Management.GetAccountsTypes;",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaAccountsTypes = new SqlDataAdapter(cmdAccountsTypes);
DataSet dsAccountsTypes = new("AccountsTypesSet");
sdaAccountsTypes.Fill(dsAccountsTypes);
foreach (DataRow drWaterMeter in dsAccountsTypes.Tables[0].Rows!)
{
cbxAccountsTypes.Items.Add(drWaterMeter[0].ToString()!);
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void Editor_Load(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT AccountName, " +
" MeterNumber, " +
" AccountType, " +
" [Address], " +
" City, " +
" County, " +
" [State], " +
" ZIPCode " +
"FROM Management.Customers " +
"WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[0].ToString();
mtbMeterNumber.Text = drCustomerAccount[1].ToString();
string strAccountCode = drCustomerAccount[2].ToString()!;
foreach (string item in cbxAccountsTypes.Items)
{
if (item[..3] == strAccountCode)
{
cbxAccountsTypes.Text = item;
}
}
txtAddress.Text = drCustomerAccount[3].ToString();
txtCity.Text = drCustomerAccount[4].ToString();
txtCounty.Text = drCustomerAccount[5].ToString();
txtState.Text = drCustomerAccount[6].ToString();
mtbZIPCode.Text = drCustomerAccount[7].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void Editor_Load(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void Editor_Load(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
. . .
}
private void btnUpdateCustomerAccount_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.UpdateCustomerAccount", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@mtrNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbMeterNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntName";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtAccountName.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntType";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 5;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = cbxAccountsTypes.Text[..3];
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@adrs";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtAddress.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@city";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 25;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtCity.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@county";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtCounty.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@state";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtState.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@zip";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 12;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbZIPCode.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
scCustomers.ExecuteNonQuery();
}
}
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void Editor_Load(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdAccountsTypes = new SqlCommand("SELECT AccountType " +
"FROM Management.GetAccountsTypes;",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaAccountsTypes = new SqlDataAdapter(cmdAccountsTypes);
DataSet dsAccountsTypes = new("AccountsTypesSet");
sdaAccountsTypes.Fill(dsAccountsTypes);
foreach (DataRow drWaterMeter in dsAccountsTypes.Tables[0].Rows!)
{
cbxAccountsTypes.Items.Add(drWaterMeter[0].ToString()!);
}
}
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT AccountName, " +
" MeterNumber, " +
" AccountType, " +
" [Address], " +
" City, " +
" County, " +
" [State], " +
" ZIPCode " +
"FROM Management.Customers " +
"WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[0].ToString();
mtbMeterNumber.Text = drCustomerAccount[1].ToString();
string strAccountCode = drCustomerAccount[2].ToString()!;
foreach (string item in cbxAccountsTypes.Items)
{
if (item[..3] == strAccountCode)
{
cbxAccountsTypes.Text = item;
}
}
txtAddress.Text = drCustomerAccount[3].ToString();
txtCity.Text = drCustomerAccount[4].ToString();
txtCounty.Text = drCustomerAccount[5].ToString();
txtState.Text = drCustomerAccount[6].ToString();
mtbZIPCode.Text = drCustomerAccount[7].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnFindWateMeter_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + mtbMeterNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnUpdateCustomerAccount_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.UpdateCustomerAccount", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@mtrNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbMeterNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntName";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtAccountName.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntType";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 5;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = cbxAccountsTypes.Text[..3];
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@adrs";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtAddress.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@city";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 25;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtCity.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@county";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtCounty.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@state";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = txtState.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@zip";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 12;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbZIPCode.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
scCustomers.ExecuteNonQuery();
}
}
Close();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwCustomers | No Change |
| Button | |
btnNewCustomerAccount | No Change |
| Button | |
btnCustomerAccountDetails | No Change |
| Button | |
btnUpdateCustomerAccount | &Update Customer Account... Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowCustomers()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowCustomers();
}
private void btnCreateCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void btnCustomerAccountDetails_Click(object sender, EventArgs e)
{
Details details = new();
details.ShowDialog();
ShowCustomers();
}
private void btnEditCustomerAccount_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowCustomers();
}
}
}
Account Name: Bernotte Doughnuts Meter #: 580-742-825 and click Find Water Meter Account Type: BUS - General Business Address: 10103 Hexagon Drive City: Winterstown County: York State: PA ZIP-Code: 17402-8828
Deleting a Customer Account from the Database
.
Practical Learning: Deleting a Customer Account
| Control | (Name) | Text | Other Properties | |
| Label | |
&Account #: | ||
| MaskedTextBox | |
mtbAccountNumber | Masked: 0000-000-0000 | |
| Button | |
btnFindCustomerAccount | &Find Customer Account | |
| Label | |
&Account Name: | ||
| TextBox | |
txtAccountName | Enabled: False | |
| Label | |
Meter &Details: | ||
| TextBox | |
txtMeterDetails | Enabled: False | |
| Label | |
&Account Type: | ||
| TextBox | |
txtAccountsTypes | Enabled: False | |
| Label | |
&Address: | ||
| TextBox | |
txtAddress | Enabled: False | |
| Label | |
C&ity: | ||
| TextBox | |
txtCity | Enabled: False | |
| Label | |
C&ounty: | ||
| TextBox | |
txtCounty | Enabled: False | |
| Label | |
&State: | ||
| TextBox | |
txtState | Enabled: False | |
| Label | |
&ZIP-Code: | ||
| TextBox | |
txtZIPCode | Enabled: False | |
| Button | |
btnDeleteCustomerAccount | &Delete Customer Account | |
| Button | |
btnClose | &Close | |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[1].ToString();
strMeterNumber = drCustomerAccount[2].ToString();
txtAccountType.Text = drCustomerAccount[3].ToString();
txtAddress.Text = drCustomerAccount[4].ToString();
txtCity.Text = drCustomerAccount[5].ToString();
txtCounty.Text = drCustomerAccount[6].ToString();
txtState.Text = drCustomerAccount[7].ToString();
txtZIPCode.Text = drCustomerAccount[8].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void btnDeleteCustomerAccount_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete the account of this customer (Account Number: " + mtbAccountNumber.Text + ")?",
"Stellar Water Point",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Management.Customers WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
}
}
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.Customers
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[1].ToString();
strMeterNumber = drCustomerAccount[2].ToString();
txtAccountType.Text = drCustomerAccount[3].ToString();
txtAddress.Text = drCustomerAccount[4].ToString();
txtCity.Text = drCustomerAccount[5].ToString();
txtCounty.Text = drCustomerAccount[6].ToString();
txtState.Text = drCustomerAccount[7].ToString();
txtZIPCode.Text = drCustomerAccount[8].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnDeleteCustomerAccount_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete the account of this customer (Account Number: " + mtbAccountNumber.Text + ")?",
"Stellar Water Point",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Management.Customers WHERE AccountNumber = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
}
}
Close();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwCustomers | FullRowSelect: True GridLines: True View: Details Anchor: Top, Bottom, Left, Right |
| Button | |
btnCreateCustomerAccount | Create Customer &Account.... Anchor: Bottom, Right |
| Button | |
btnCustomerAccountDetails | Customer Account &Details... Anchor: Bottom, Right |
| Button | |
btnEditCustomerAccount | &Edit Customer Account... Anchor: Bottom, Right |
| Button | |
btnDeleteCustomerAccount | &Delete Customer Account... Anchor: Bottom, Right |
| Button | |
btnClose | &Close Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.WaterMeters;
namespace StellarWaterPoint31.Customers
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowCustomers()
{
DataSet dsCustomers = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT clients.CustomerId, " +
" clients.AccountNumber, " +
" clients.AccountName, " +
" clients.MeterNumber, " +
" acnTypes.TypeCode + N' - ' + acnTypes.AccountType AS AccountType, " +
" clients.[Address], " +
" clients.City, " +
" clients.County, " +
" clients.[State], " +
" clients.ZIPCode " +
"FROM Management.Customers clients " +
"INNER JOIN Management.AccountsTypes acnTypes " +
"ON clients.AccountType = acnTypes.TypeCode; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
sdaCustomers.Fill(dsCustomers);
lvwCustomers.Items.Clear();
foreach (DataRow drCustomer in dsCustomers.Tables[0].Rows!)
{
ListViewItem lviCustomer = new(drCustomer["CustomerId"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountNumber"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountName"].ToString());
lviCustomer.SubItems.Add(drCustomer["MeterNumber"].ToString());
lviCustomer.SubItems.Add(drCustomer["AccountType"].ToString());
lviCustomer.SubItems.Add(drCustomer["Address"].ToString());
lviCustomer.SubItems.Add(drCustomer["City"].ToString());
lviCustomer.SubItems.Add(drCustomer["County"].ToString());
lviCustomer.SubItems.Add(drCustomer["State"].ToString());
lviCustomer.SubItems.Add(drCustomer["ZIPCode"].ToString());
lvwCustomers.Items.Add(lviCustomer);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowCustomers();
}
private void btnCreateCustomerAccount_Click(object sender, EventArgs e)
{
Create create = new();
if (create.ShowDialog() == DialogResult.OK)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.CreateCustomerAccount", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntName";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtAccountName.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@mtrNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbMeterNumber.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntType";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 5;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.cbxAccountsTypes.Text[..3];
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@adrs";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 150;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtAddress.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@city";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 25;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtCity.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@county";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtCounty.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@state";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 35;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.txtState.Text;
scCustomers.Parameters.Add(spCustomers);
spCustomers = new SqlParameter();
spCustomers.ParameterName = "@zip";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 12;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = create.mtbZIPCode.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
scCustomers.ExecuteNonQuery();
}
}
}
ShowCustomers();
}
private void btnCustomerAccountDetails_Click(object sender, EventArgs e)
{
Details details = new();
details.ShowDialog();
ShowCustomers();
}
private void btnEditCustomerAccount_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowCustomers();
}
private void btnDeleteCustomerAccount_Click(object sender, EventArgs e)
{
Delete delete = new();
delete.ShowDialog();
ShowCustomers();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
USE StellarWaterPoint26; GO EXECUTE Management.CreateCustomerAccount N'9279-570-8394', N'Thomas Stones', N'799-528-461', N'RES', N'10252 Broward Ave #D4', N'Frederick', N'Frederick', N'MD', N'21703-4422'; EXECUTE Management.CreateCustomerAccount N'4086-938-4783', N'Bernotte Doughnuts', N'580-742-825', N'BUS', N'10103 Hexagon Drv', N'Winterstown', N'York', N'PA', N'17402-8818'; EXECUTE Management.CreateCustomerAccount N'2068-258-9486', N'Yollanda Training', N'186-962-805', N'UUO', N'4819 East Munk Street', N'Whitehall', N'Fulton', N'PA', N'17340-1188'; EXECUTE Management.CreateCustomerAccount NULL, N'First Methodist Congregation', NULL, NULL, N'7702 Charles Road', NULL, NULL, NULL, NULL; EXECUTE Management.CreateCustomerAccount N'6986-829-3741', N'Eyes Wide', N'208-428-308', N'BUS', N'12087 Avencia Court #4D1', N'Silver Spring', N'Montgomery', N'MD', N'20910-2288'; EXECUTE Management.CreateCustomerAccount N'4293-802-8506', N'Kelly Davids', N'496-813-794', N'RES', N'938 East Panchot Str', N'Greenwood', N'Sussex', N'DE', N'19950-4242'; EXECUTE Management.CreateCustomerAccount N'9947-374-2648', N'Marianne Harrington', N'862-715-006', N'RES', N'708 Correta Drv', N'Arlington', NULL, N'VA', N'22222-6060'; EXECUTE Management.CreateCustomerAccount N'7928-131-4850', NULL, NULL, N'SGO', NULL, N'Washington', NULL, NULL, NULL; EXECUTE Management.CreateCustomerAccount N'1386-949-2058', N'Watson Country Buffet', N'296-837-495', N'WAT', N'4862 Wellington Street', N'Hammonton', N'Atlantic ', N'NJ', N'08037-2828'; EXECUTE Management.CreateCustomerAccount N'7943-686-9786', N'Angel Bulzaides', N'394-835-297', N'RES', N'10227 Old Harbor Drv', N'Elkview', N'Kanawha', N'WV', N'25071-5858'; EXECUTE Management.CreateCustomerAccount N'4820-375-2842', N'Sun Communal', N'392-494-572', N'SGO', N'748 Red Hills Rd', N'Roanoke', NULL, N'VA', N'24012-4824'; EXECUTE Management.CreateCustomerAccount N'9618-579-2577', N'Gerald Place', N'847-252-246', N'UUO', N'3666 Hanchor Drv', N'Granville', N'Licking', N'OH', N'43023-2777'; EXECUTE Management.CreateCustomerAccount NULL, N'Astral Sequence', NULL, N'BUS', N'12715 Eastern Gateway', N'Catonsville', N'Baltimore County', NULL, NULL; EXECUTE Management.CreateCustomerAccount N'6003-386-3955', N'Mandiakandara Marmoudi', N'374-886-284', N'OTH', N'539 Avalon Court', N'Greenwood', N'Sussex', N'DE', N'19950-5550'; EXECUTE Management.CreateCustomerAccount N'5294-859-7513', N'Jeannette Schiller', N'713-942-058', N'RES', N'10110 Winslow Ave', N'Mercerville', N'Mercer', N'NJ', N'08619-7472'; EXECUTE Management.CreateCustomerAccount N'9249-379-6848', N'Country West Eatery', N'588-279-663', N'BUS', N'8280 Sligo North Way', N'Albright', N'Preston', N'WV', N'26519-6620'; EXECUTE Management.CreateCustomerAccount N'5252-757-9595', NULL, N'379-386-979', NULL, N'4992 Preston Street', NULL, NULL, N'OH', NULL; EXECUTE Management.CreateCustomerAccount N'7080-583-5947', N'Sunny Yard', N'827-508-248', N'WAT', N'663 Sherry Wood East Street', N'Shimpstown', N'Franklin', N'PA', N'17236-2626'; EXECUTE Management.CreateCustomerAccount N'8027-304-6829', N'Anthony Clarcksons', N'837-806-836', N'RES', N'904 Augusta Drive', N'Blackbird', N'New Castle', N'DE', N'19734-8822'; EXECUTE Management.CreateCustomerAccount N'6699-396-2905', N'Spencer Reuter', N'649-373-505', N'RES', N'2850 Burnsweak Avenue', N'Silver Spring', N'Montgomery', N'MD', N'20910-4044'; EXECUTE Management.CreateCustomerAccount N'1827-395-0203', N'Sathyavanthara Khooni', N'470-628-850', N'WAT', N'10331 Chryswell Road', N'Washington', NULL, N'DC', N'20008-2426'; EXECUTE Management.CreateCustomerAccount NULL, N'Eastern Cage', NULL, NULL, NULL, N'Hammonton', NULL, N'NJ', NULL; EXECUTE Management.CreateCustomerAccount N'3947-957-4958', N'Patsil Industries', N'747-581-379', N'BUS', N'10348 Larrens Drive', N'Baltimore', N'Baltimore', N'MD', N'21215-2222'; EXECUTE Management.CreateCustomerAccount N'2836-485-9699', N'Red Oak High School', N'379-386-979', N'SGO', N'442 Donham Road', N'Silver Spring', N'Montgomery', N'MD', N'20910-8822'; EXECUTE Management.CreateCustomerAccount N'5938-074-5293', N'Park and Roll', N'592-824-957', N'SGO', N'582G Dunhill Avenue', N'Lanham', N'Prince Georges', N'MD', N'20706-8284'; EXECUTE Management.CreateCustomerAccount N'3028-502-9418', N'Spencer Kershaw', N'186-959-757', N'RES', N'338C Grayson Street', N'Gatchellville', N'York', N'PA', N'17352-6464'; EXECUTE Management.CreateCustomerAccount NULL, NULL, N'928-317-924', N'BUS', NULL, N'Stafford', NULL, NULL, NULL; EXECUTE Management.CreateCustomerAccount N'2974-972-8139', N'Paul Arnette', N'295-770-695', N'OTH', N'8127 Bledsoe Str', N'Hyattsville', N'Prince Georges', N'MD', N'20783-5858'; EXECUTE Management.CreateCustomerAccount N'2758-493-7249', N'Hervey Smile', N'293-924-869', N'WAT', N'12973 Sonaa Street #E42', N'Silver Spring', N'Montgomery', N'MD', N'20910-4488'; EXECUTE Management.CreateCustomerAccount N'9337-947-3664', NULL, N'649-358-184', N'SGO', NULL, N'Bellefontaine', NULL, N'OH', NULL; EXECUTE Management.CreateCustomerAccount N'7518-302-6895', N'Grace Brenner', N'207-964-835', N'BUS', N'4299 Peachtree Court', N'Rockville', N'Montgomery', N'MD', N'20853-1888'; EXECUTE Management.CreateCustomerAccount NULL, N'Jeffrey Maney', NULL, N'RES', NULL, NULL, NULL, NULL, NULL; EXECUTE Management.CreateCustomerAccount N'7028-405-9381', N'Valley Services', N'306-842-497', N'WAT', N'613 Meadowhill Road', N'Alonzaville', N'Shenandoah', N'VA', N'22664-8080'; EXECUTE Management.CreateCustomerAccount N'5293-957-3395', N'Wellway Community Center', N'386-468-057', N'RES', N'10484 Greenway Avenue', N'Mt Storm', N'Grant', N'WV', N'26739-7700'; EXECUTE Management.CreateCustomerAccount N'2038-413-9680', N'Eastern Friandise', N'938-725-869', N'BUS', N'2075 Rose Hills Avenue', N'Washington', NULL, N'DC', N'20004-2626'; EXECUTE Management.CreateCustomerAccount NULL, N'Amidou Gomah', NULL, N'RES', N'14118 Yellow Burrough Blvd', N'Philadelphia', NULL, N'PA', NULL; EXECUTE Management.CreateCustomerAccount N'3792-853-6885', N'Department of Public Affairs', N'595-753-147', NULL, NULL, N'Upper Marlboro', N'Prince George County', NULL, NULL; EXECUTE Management.CreateCustomerAccount N'8282-777-8282', N'Garland Hotel', N'938-275-294', N'BUS', N'4222 Extell Ave', N'Cambridge', NULL, N'MD', N'21613-2288'; EXECUTE Management.CreateCustomerAccount NULL, N'Single Connection', N'288-427-585', N'BUS', NULL, N'Mansfield', NULL, N'OH', N'44903-3030'; EXECUTE Management.CreateCustomerAccount N'2499-636-4444', N'Bryanna Spencer', NULL, N'RES', N'6282 Sheppherd Str', NULL, NULL, N'NJ', N'08759'; EXECUTE Management.CreateCustomerAccount N'2842-585-7260', N'District Community Reserves', N'349-725-848', N'SGO', N'3280 Hopewell Street, NE', N'Washington', NULL, NULL, NULL; EXECUTE Management.CreateCustomerAccount N'9282-794-7937', N'Yashua Yáñés', NULL, N'RES', N'10214 Monroe Ave', N'Easton', NULL, N'MD', NULL; EXECUTE Management.CreateCustomerAccount NULL, N'Miguel Altieri', NULL, N'RES', N'10941 Patriot Blvd', N'Crenshaw', N'Jefferson', N'PA', N'15824-6628'; EXECUTE Management.CreateCustomerAccount N'2847-597-2829', N'Jameson', N'379-386-979', N'WAT', N'7373 Gold Town Rd', NULL, NULL, N'WV', N'25305-5550'; EXECUTE Management.CreateCustomerAccount N'6381-748-2222', N'Up Eyes', NULL, N'BUS', N'4149 Deerfield Str', NULL, NULL, NULL, N'26601-9090'; EXECUTE Management.CreateCustomerAccount NULL, N'Annette Wald', NULL, N'RES', N'11441 Eastern Friendshi Rd', N'Alexandria', NULL, N'VA', NULL; EXECUTE Management.CreateCustomerAccount N'8384-708-2941', N'Department of Environment Affairs', NULL, N'SGO', NULL, NULL, NULL, N'OH', NULL; EXECUTE Management.CreateCustomerAccount N'3728-138-2947', N'Marie Rath', NULL, NULL, N'8802 Atlantic Ave', NULL, NULL, N'PA', N'18101-7711'; EXECUTE Management.CreateCustomerAccount N'1793-857-9413', N'Body Care', NULL, N'WAT', NULL, N'Ocean City', NULL, N'NJ', NULL; EXECUTE Management.CreateCustomerAccount N'6028-695-2068', N'Ronald Glassman', N'468-359-486', N'BUS', NULL, NULL, NULL, NULL, N'22206-5520'; EXECUTE Management.CreateCustomerAccount NULL, N'Belsher Laundromate', NULL, N'WAT', N'8812 Lawrence Ave', N'Wilmington', NULL, NULL, N'19806-1317'; EXECUTE Management.CreateCustomerAccount N'9616-283-7249', NULL, NULL, N'SGO', N'13006 Blueberry Ave', N'Takoma Park', NULL, N'MD', NULL; EXECUTE Management.CreateCustomerAccount N'2829-516-8353', N'Richard Eghert', NULL, N'RES', N'662 Placido Road', N'Charleston', NULL, NULL, NULL;
Water Bills
Introduction
.
Practical Learning: Introducing Water Bills
namespace StellarWaterPoint31.Models
{
internal class WaterBill
{
internal (double a, double b, double c) CalculateTiers(string acnt, double total)
{
(double tier1, double tier2, double tier3) results = (0.00, 0.00, 0.00);
if (acnt == "RES")
{
results.tier1 = total * 41.50 / 10000.00;
results.tier2 = total * 32.50 / 10000.00;
results.tier3 = total * 26.00 / 10000.00;
}
else if (acnt == "SGO")
{
results.tier1 = total * 46.00 / 10000.00;
results.tier2 = total * 50.00 / 10000.00;
results.tier3 = total * 4.00 / 10000.00;
}
else if (acnt == "BUS")
{
results.tier1 = total * 45.00 / 10000.00;
results.tier2 = total * 30.00 / 10000.00;
results.tier3 = total * 25.00 / 10000.00;
}
else if (acnt == "UUO")
{
results.tier1 = total * 25.00 / 10000.00;
results.tier2 = total * 35.00 / 10000.00;
results.tier3 = total * 40.00 / 10000.00;
}
else if (acnt == "WAT")
{
results.tier1 = total * 50.00 / 10000.00;
results.tier2 = total * 40.00 / 10000.00;
results.tier3 = total * 10.00 / 10000.00;
}
else
{
results.tier1 = total * (48.00 / 10000.00);
results.tier2 = total * (32.00 / 10000.00);
results.tier3 = total * (20.00 / 10000.00);
}
return results;
}
internal double CalculateSewerCharges(string acnt, double total)
{
double result;
if (acnt == "RES")
{
result = total * 6.826941 / 100.00;
}
else if (acnt == "SGO")
{
result = total * 4.162522 / 100.00;
}
else if (acnt == "BUS")
{
result = total * 8.315136 / 100.00;
}
else if (acnt == "UUO")
{
result = total * 10.626147 / 100.00;
}
else if (acnt == "WAT")
{
result = total * 12.025135 / 100.00;
}
else // if (acnt == "OTH")
{
result = total * 9.202615 / 100.00;
}
return result;
}
internal double CalculateEnvironmentCharges(string acnt, double total)
{
double result;
switch (acnt)
{
case "RES":
result = total * 0.022724;
break;
case "SGO":
result = total * 0.118242;
break;
case "BUS":
result = total * 0.161369;
break;
case "UUO":
result = total * 0.082477;
break;
case "WAT":
result = total * 0.413574;
break;
default:
result = total * 0.221842;
break;
}
return result;
}
internal double CalculateServiceCharges(string acnt, double total)
{
switch (acnt)
{
case "RES":
return total * 0.145748;
case "SGO":
return total * 0.102246;
case "BUS":
return total * 0.242627;
case "UUO":
return total * 0.186692;
case "WAT":
return total * 0.412628;
default:
return total * 0.210248;
}
}
internal double CalculateLocalTaxes(string acnt, double total) => acnt switch
{
"RES" => total * 0.031574,
"SGO" => total * 0.035026,
"BUS" => total * 0.122517,
"UUO" => total * 0.105737,
"WAT" => total * 0.153248,
_ => total * 0.125148
};
internal double CalculateStateTaxes(string acnt, double total) => acnt switch
{
"RES" => total * 0.01724,
"SGO" => total * 0.008779,
"BUS" => total * 0.042448,
"UUO" => total * 0.067958,
"WAT" => total * 0.081622,
_ => total * 0.013746
};
internal DateTime SetPaymentDueDate(string acnt, DateTime date)
{
TimeSpan tsPaymentDueDate = new TimeSpan(1, 0, 0, 0);
if (acnt == "RES")
{
tsPaymentDueDate = new TimeSpan(15, 0, 0, 0);
}
else if (acnt == "SGO")
{
tsPaymentDueDate = new TimeSpan(20, 0, 0, 0);
}
else if (acnt == "BUS")
{
tsPaymentDueDate = new TimeSpan(30, 0, 0, 0);
}
else if (acnt == "UUO")
{
tsPaymentDueDate = new TimeSpan(25, 0, 0, 0);
}
else if (acnt == "WAT")
{
tsPaymentDueDate = new TimeSpan(40, 0, 0, 0);
}
else
{
tsPaymentDueDate = new TimeSpan(35, 0, 0, 0);
}
return date + tsPaymentDueDate;
}
internal DateTime SetLatePaymentDueDate(string acnt, DateTime date)
{
switch (acnt)
{
case "RES":
return date + new TimeSpan(30, 0, 0, 0);
case "SGO":
return date + new TimeSpan(40, 0, 0, 0);
case "BUS":
return date + new TimeSpan(50, 0, 0, 0);
case "UUO":
return date + new TimeSpan(60, 0, 0, 0);
case "WAT":
return date + new TimeSpan(65, 0, 0, 0);
default:
return date + new TimeSpan(45, 0, 0, 0);
}
}
internal double CalculateLateAmountDue(string acnt, double amt) => acnt switch
{
"RES" => amt + 8.95,
"SGO" => amt + (amt / 4.575),
"BUS" => amt + (amt / 12.315),
"UUO" => amt + (amt / 7.425),
"WAT" => amt + (amt / 15.225),
_ => amt + (amt / 6.735)
};
}
}Showing Water Bills
.
Practical Learning: Showing Water Bills
| (Name) | Text | TextAlign | Width |
| colWaterBillId | Id | 40 | |
| colBillNumber | Bill # | Center | 80 |
| colAccountSummary | Account Summary | Center | 550 |
| colStartDate | Start Date | Center | 150 |
| colEndDate | End Date | Center | 150 |
| colBillingDays | Days | Center | |
| colCounterStart | Counter Start | Right | 125 |
| colCounterEnd | Counter End | Right | 125 |
| colTotalHCF | Total HCF | Right | 100 |
| colGallons | Gallons | Right | 95 |
| colPaymentDueDate | Pmt Due Date | Center | 125 |
| colAmountDue | Amt Due | Right | 90 |
| Control | (Name) | Other Properties | |
| ListView | |
lvwWaterBills | FullRowSelect: True GridLines: True View: Details Anchor: Top, Bottom, Left, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterBills()
{
DataSet dsWaterBills = new("WaterBillsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillId, " +
" BillNumber, " +
" AccountSummary, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" PaymentDueDate, " +
" AmountDue " +
"FROM Management.GetWaterBills; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
lvwWaterBills.Items.Clear();
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
ListViewItem lviWaterBill = new(drWaterBill["WaterBillId"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["AccountNumber"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["BillNumber"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["AccountSummary"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["MeterReadingStartDate"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["MeterReadingEndDate"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["BillingDays"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["CounterReadingStart"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["CounterReadingEnd"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["TotalHCF"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["TotalGallons"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["PaymentDueDate"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["AmountDue"].ToString());
lvwWaterBills.Items.Add(lviWaterBill);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterBills();
}
}
}
| Control | (Name) | Text | Font | |
| Button | |
btnWaterBills | C&Water Bills... | Times New Roman, 24pt, style=Bold |
| Button | |
btnClose | &Close | Times New Roman, 24pt, style=Bold |
namespace StellarWaterPoint31
{
public partial class WaterDistribution : Form
{
public WaterDistribution()
{
InitializeComponent();
}
private void btnWaterBills_Click(object o, EventArgs e)
{
WaterBills.Central central = new();
central.Show();
}
private void btnCustomers_Click(object o, EventArgs e)
{
Customers.Central clients = new();
clients.Show();
}
private void btnWaterMeters_Click(object o, EventArgs e)
{
WaterMeters.Central central = new WaterMeters.Central();
central.Show();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}A New Water Bill
.
Practical Learning: Processing a Water Bill
| Control | Text | Name | Other Properties | |
| Label | |
&Water Bill #: | ||
| TextBox | |
txtBillNumber | ||
| GroupBox |  |
Customer Information | ||
| Label | |
&Account #: | ||
| MaskedTextBox | |
mtbAccountNumber | Mask: 0000-000-0000 | |
| Button | |
Find Customer &Account | btnFindCustomerAccount | |
| Label | |
Account Name: | ||
| TextBox | |
txtAccountName | ||
| Label | |
Account Type: | ||
| TextBox | |
txtAccountType | ||
| Label | |
Address: | ||
| TextBox | |
txtAddress | ||
| TextBox | |
txtCity | ||
| TextBox | |
txtCounty | ||
| TextBox | |
txtState | ||
| TextBox | |
txtZIPCode | ||
| Label | |
_________________________________________________ | ||
| Label | |
Meter Details: | ||
| TextBox | |
txtMeterDetails | ||
| GroupBox | |
Meter Reading | ||
| Label | |
Meter &Reading Start Date: | ||
| Date Time Picker |  |
dtpMeterReadingStartDate | ||
| Label | |
Meter Reading &End Date: | ||
| Date Time Picker | |
dtpMeterReadingEndDate | ||
| Label | |
Coun&ter Reading Start: | ||
| TextBox | |
txtCounterReadingStart | ||
| Label | |
Counter Readi&ng End: | ||
| TextBox | |
txtCounterReadingEnd | ||
| Button | |
&Evaluate Water Bill | btnEvaluateWaterBill | Times New Roman, 24pt, style=Bold |
| GroupBox | |
Meter Result | ||
| Label | |
Billing Days: | ||
| TextBox | |
txtBillingDays | ||
| Label | |
Total HCF: | ||
| TextBox | |
txtTotalHCF | ||
| Label | |
Total Gallons: | ||
| TextBox | |
txtTotalGallons | ||
| Label | |
First Tier Consumption: | ||
| TextBox | |
txtFirstTierConsumption | ||
| Label | |
Second Tier: | ||
| TextBox | |
txtSecondTierConsumption | ||
| Label | |
Last Tier: | ||
| TextBox | |
txtLastTierConsumption | ||
| GroupBox | |
Consumption Charges | ||
| Label | |
Water Charges: | ||
| TextBox | |
txtWaterCharges | ||
| Label | |
Sewer Charges: | ||
| TextBox | |
txtSewerCharges | ||
| Label | |
Environment Charges: | ||
| TextBox | |
txtEnvironmentCharges | ||
| Label | |
Service Charges: | ||
| TextBox | |
txtServiceCharges | ||
| Label | |
Total Charges: | ||
| TextBox | |
txtTotalCharges | ||
| GroupBox | |
Taxes | ||
| Label | |
Local Taxes: | ||
| TextBox | |
txtLocalTaxes | ||
| Label | |
State Taxes: | ||
| TextBox | |
txtStateTaxes | ||
| GroupBox | |
Water Bill Payment | ||
| Label | |
Payment Due Date: | ||
| Date Time Picker | |
dtpPaymentDueDate | ||
| Label | |
Amount Due: | ||
| TextBox | |
txtAmountDue | ||
| Label | |
Late Payment Due Date: | ||
| Date Time Picker | |
dtpLatePaymentDueDate | ||
| Label | |
&Late Amount Due: | ||
| TextBox | |
txtLateAmountDue | ||
| Button | |
Save Water Bill | btnSaveWaterBill | |
| Button | |
&Close | btnClose | |
Form Properties
| Form Property | Value |
| FormBorderStyle | FixedDialog |
| Text | Stellar Water Point - Water Bill Processing |
| StartPosition | CenterScreen |
| MaximizeBox | False |
| MinimizeBox | False |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.SelectCustomers", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(scCustomers);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.Models;
namespace StellarWaterPoint31.WaterBills
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.SelectCustomers", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(scCustomers);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
private void btnEvaluateWaterBill_Click(object sender, EventArgs e)
{
double counterStart = 0, counterEnd = 0;
try
{
counterStart = double.Parse(txtCounterReadingStart.Text);
}
catch (FormatException feCRStart)
{
MessageBox.Show("You must enter a valid value in the Counter Reading Start text box. " +
"The error produced is: " + feCRStart.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
try
{
counterEnd = double.Parse(txtCounterReadingEnd.Text);
}
catch (FormatException feCREnd)
{
MessageBox.Show("You must enter a valid value in the Counter Reading End text box. " +
"The error produced is: " + feCREnd.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
WaterBill bill = new();
double consumption = counterEnd - counterStart;
double gallons = consumption * 748.05;
string strAccountType = txtAccountType.Text[..3];
(double first, double second, double last) tiers = bill.CalculateTiers(strAccountType, gallons);
double waterCharges = tiers.first + tiers.second + tiers.last;
double sewerCharges = bill.CalculateSewerCharges(strAccountType, waterCharges);
double envCharges = bill.CalculateEnvironmentCharges(strAccountType, waterCharges);
double srvCharges = bill.CalculateServiceCharges(strAccountType, waterCharges);
double totalCharges = waterCharges + sewerCharges + envCharges + srvCharges;
double localTaxes = bill.CalculateLocalTaxes(strAccountType, waterCharges);
double stateTaxes = bill.CalculateStateTaxes(strAccountType, waterCharges);
double amtDue = totalCharges + localTaxes + stateTaxes;
txtTotalHCF.Text = consumption.ToString();
txtTotalGallons.Text = ((int)(Math.Ceiling(gallons))).ToString();
txtFirstTierConsumption.Text = tiers.first.ToString("F");
txtSecondTierConsumption.Text = tiers.second.ToString("F");
txtLastTierConsumption.Text = tiers.last.ToString("F");
txtWaterCharges.Text = waterCharges.ToString("F");
txtSewerCharges.Text = sewerCharges.ToString("F");
txtEnvironmentCharges.Text = envCharges.ToString("F");
txtServiceCharges.Text = srvCharges.ToString("F");
txtTotalCharges.Text = totalCharges.ToString("F");
txtLocalTaxes.Text = localTaxes.ToString("F");
txtStateTaxes.Text = stateTaxes.ToString("F");
dtpPaymentDueDate.Value = bill.SetPaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtAmountDue.Text = amtDue.ToString("F");
dtpLatePaymentDueDate.Value = bill.SetLatePaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtLateAmountDue.Text = bill.CalculateLateAmountDue(strAccountType, amtDue).ToString("F");
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.Models;
namespace StellarWaterPoint31.WaterBills
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
private void btnEvaluateWaterBill_Click(object sender, EventArgs e)
{
. . .
}
private void btnSaveWaterBill_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterBills(BillNumber, " +
" AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue) " +
"VALUES(" + txtBillNumber.Text + ", N'" +
mtbAccountNumber.Text + "', N'" +
dtpMeterReadingStartDate.Value.ToShortDateString() + "', N'" +
dtpMeterReadingEndDate.Value.ToShortDateString() + "', " +
txtBillingDays.Text + ", " +
txtCounterReadingStart.Text + ", " +
txtCounterReadingEnd.Text + ", " +
txtTotalHCF.Text + ", " +
txtTotalGallons.Text + ", " +
txtFirstTierConsumption.Text + ", " +
txtSecondTierConsumption.Text + ", " +
txtLastTierConsumption.Text + ", " +
txtWaterCharges.Text + ", " +
txtSewerCharges.Text + ", " +
txtEnvironmentCharges.Text + ", " +
txtServiceCharges.Text + ", " +
txtTotalCharges.Text + ", " +
txtLocalTaxes.Text + ", " +
txtStateTaxes.Text + ", N'" +
dtpPaymentDueDate.Value.ToShortDateString() + "', " +
txtAmountDue.Text + ", N'" +
dtpLatePaymentDueDate.Value.ToShortDateString() + "', " +
txtLateAmountDue.Text + ");",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
Close();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.Models;
namespace StellarWaterPoint31.WaterBills
{
public partial class Create : Form
{
public Create()
{
InitializeComponent();
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.SelectCustomers", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(scCustomers);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
private void btnEvaluateWaterBill_Click(object sender, EventArgs e)
{
double counterStart = 0, counterEnd = 0;
try
{
counterStart = double.Parse(txtCounterReadingStart.Text);
}
catch (FormatException feCRStart)
{
MessageBox.Show("You must enter a valid value in the Counter Reading Start text box. " +
"The error produced is: " + feCRStart.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
try
{
counterEnd = double.Parse(txtCounterReadingEnd.Text);
}
catch (FormatException feCREnd)
{
MessageBox.Show("You must enter a valid value in the Counter Reading End text box. " +
"The error produced is: " + feCREnd.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
WaterBill bill = new();
double consumption = counterEnd - counterStart;
double gallons = consumption * 748.05;
string strAccountType = txtAccountType.Text[..3];
(double first, double second, double last) tiers = bill.CalculateTiers(strAccountType, gallons);
double waterCharges = tiers.first + tiers.second + tiers.last;
double sewerCharges = bill.CalculateSewerCharges(strAccountType, waterCharges);
double envCharges = bill.CalculateEnvironmentCharges(strAccountType, waterCharges);
double srvCharges = bill.CalculateServiceCharges(strAccountType, waterCharges);
double totalCharges = waterCharges + sewerCharges + envCharges + srvCharges;
double localTaxes = bill.CalculateLocalTaxes(strAccountType, waterCharges);
double stateTaxes = bill.CalculateStateTaxes(strAccountType, waterCharges);
double amtDue = totalCharges + localTaxes + stateTaxes;
txtTotalHCF.Text = consumption.ToString();
txtTotalGallons.Text = ((int)(Math.Ceiling(gallons))).ToString();
txtFirstTierConsumption.Text = tiers.first.ToString("F");
txtSecondTierConsumption.Text = tiers.second.ToString("F");
txtLastTierConsumption.Text = tiers.last.ToString("F");
txtWaterCharges.Text = waterCharges.ToString("F");
txtSewerCharges.Text = sewerCharges.ToString("F");
txtEnvironmentCharges.Text = envCharges.ToString("F");
txtServiceCharges.Text = srvCharges.ToString("F");
txtTotalCharges.Text = totalCharges.ToString("F");
txtLocalTaxes.Text = localTaxes.ToString("F");
txtStateTaxes.Text = stateTaxes.ToString("F");
dtpPaymentDueDate.Value = bill.SetPaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtAmountDue.Text = amtDue.ToString("F");
dtpLatePaymentDueDate.Value = bill.SetLatePaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtLateAmountDue.Text = bill.CalculateLateAmountDue(strAccountType, amtDue).ToString("F");
}
private void btnSaveWaterBill_Click(object sender, EventArgs e)
{
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO Management.WaterBills(BillNumber, " +
" AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue) " +
"VALUES(" + txtBillNumber.Text + ", N'" +
mtbAccountNumber.Text + "', N'" +
dtpMeterReadingStartDate.Value.ToShortDateString() + "', N'" +
dtpMeterReadingEndDate.Value.ToShortDateString() + "', " +
txtBillingDays.Text + ", " +
txtCounterReadingStart.Text + ", " +
txtCounterReadingEnd.Text + ", " +
txtTotalHCF.Text + ", " +
txtTotalGallons.Text + ", " +
txtFirstTierConsumption.Text + ", " +
txtSecondTierConsumption.Text + ", " +
txtLastTierConsumption.Text + ", " +
txtWaterCharges.Text + ", " +
txtSewerCharges.Text + ", " +
txtEnvironmentCharges.Text + ", " +
txtServiceCharges.Text + ", " +
txtTotalCharges.Text + ", " +
txtLocalTaxes.Text + ", " +
txtStateTaxes.Text + ", N'" +
dtpPaymentDueDate.Value.ToShortDateString() + "', " +
txtAmountDue.Text + ", N'" +
dtpLatePaymentDueDate.Value.ToShortDateString() + "', " +
txtLateAmountDue.Text + ");",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
}
Close();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwWaterBills | No Change |
| Button | |
btnProcessWaterBill | &Process Water Bill... |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterBills()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterBills();
}
private void btnProcessWaterBill_Click(object sender, EventArgs e)
{
Create create = new();
create.ShowDialog();
ShowWaterBills();
}
}
}
| Water Bill # | Account # | Reading Start Date | Reading End Date | Counter Reading Start | Counter Reading End |
| 451474 | 2068-258-9486 | 01/11/2010 | 04/12/2010 | 103943 | 103956 |
| 923633 | 5293-957-3395 | 01/17/2010 | 08/18/2010 | 256945 | 256972 |
| 917829 | 9279-570-8394 | 02/15/2010 | 05/14/2010 | 5205 | 5222 |
| 202666 | 6986-829-3741 | 03/08/2010 | 06/06/2010 | 5679 | 5690 |
Water Bill Details
.
Practical Learning: Showing a Water Bill
| Control | Text | Name | Other Properties | ||
| Label | |
&Water Bill #: | |||
| TextBox | |
txtBillNumber | |||
| Button | |
&Find Water Bill | btnFindWaterBill | ||
| GroupBox | |
Customer Information | |||
| Label | |
&Account #: | |||
| TextBox | |
txtAccountNumber | Enabled: False | ||
| Label | |
Account Name: | |||
| TextBox | |
txtAccountName | Enabled: False | ||
| Label | |
Account Type: | |||
| TextBox | |
txtAccountType | |||
| Label | |
Address: | Enabled: False | ||
| TextBox | |
txtAddress | Enabled: False | ||
| TextBox | |
txtCity | |||
| TextBox | |
txtCounty | Enabled: False | ||
| TextBox | |
txtState | Enabled: False | ||
| TextBox | |
txtZIPCode | Enabled: False | ||
| Label | |
_________________________________________________ | |||
| Label | |
Meter Details: | |||
| TextBox | |
txtMeterDetails | Enabled: False | ||
| GroupBox | |
Meter Reading | |||
| Label | |
Meter &Reading Start Date: | |||
| Text Box | |
txtMeterReadingStartDate | Enabled: False | ||
| Label | |
Meter Reading &End Date: | |||
| Text Box | |
txtMeterReadingEndDate | Enabled: False | ||
| Label | |
Coun&ter Reading Start: | |||
| TextBox | |
txtCounterReadingStart | Enabled: False | ||
| Label | |
Counter Readi&ng End: | |||
| TextBox | |
txtCounterReadingEnd | Enabled: False | ||
| GroupBox | |
Meter Result | |||
| Label | |
Billing Days: | |||
| TextBox | |
txtBillingDays | Enabled: False | ||
| Label | |
Total HCF: | |||
| TextBox | |
txtTotalHCF | Enabled: False | ||
| Label | |
Total Gallons: | |||
| TextBox | |
txtTotalGallons | Enabled: False | ||
| Label | |
First Tier Consumption: | |||
| TextBox | |
txtFirstTierConsumption | Enabled: False | ||
| Label | |
Second Tier: | |||
| TextBox | |
txtSecondTierConsumption | Enabled: False | ||
| Label | |
Last Tier: | |||
| TextBox | |
txtLastTierConsumption | Enabled: False | ||
| GroupBox | |
Consumption Charges | |||
| Label | |
Water Charges: | |||
| TextBox | |
txtWaterCharges | Enabled: False | ||
| Label | |
Sewer Charges: | |||
| TextBox | |
txtSewerCharges | Enabled: False | ||
| Label | |
Environment Charges: | |||
| TextBox | |
txtEnvironmentCharges | Enabled: False | ||
| Label | |
Service Charges: | |||
| TextBox | |
txtServiceCharges | Enabled: False | ||
| Label | |
Total Charges: | |||
| TextBox | |
txtTotalCharges | Enabled: False | ||
| GroupBox | |
Taxes | |||
| Label | |
Local Taxes: | |||
| TextBox | |
txtLocalTaxes | Enabled: False | ||
| Label | |
State Taxes: | |||
| TextBox | |
txtStateTaxes | Enabled: False | ||
| GroupBox | |
Water Bill Payment | |||
| Label | |
Payment Due Date: | |||
| Date Time Picker | |
dtpPaymentDueDate | Enabled: False | ||
| Label | |
Amount Due: | |||
| TextBox | |
txtAmountDue | Enabled: False | ||
| Label | |
Late Payment Due Date: | |||
| Text Box | |
txtLatePaymentDueDate | Enabled: False | ||
| Label | |
&Late Amount Due: | |||
| TextBox | |
txtLateAmountDue | |||
| Button | |
&Close | btnClose | ||
Form Properties
| Form Property | Value |
| FormBorderStyle | FixedDialog |
| Text | Stellar Water Point - Water Bill Processing |
| StartPosition | CenterScreen |
| MaximizeBox | False |
| MinimizeBox | False |
using Microsoft.Data.SqlClient;
using System.Data;
namespace StellarWaterPoint31.WaterBills
{
public partial class Details : Form
{
public Details()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsWaterBills = new("WaterBillsSet");
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue " +
"FROM Management.WaterBills " +
"WHERE BillNumber = " + txtBillNumber.Text + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
txtAccountNumber.Text = drWaterBill[0].ToString();
txtMeterReadingStartDate.Text = drWaterBill[1].ToString();
txtMeterReadingEndDate.Text = drWaterBill[2].ToString();
txtBillingDays.Text = drWaterBill[3].ToString();
txtCounterReadingStart.Text = drWaterBill[4].ToString();
txtCounterReadingEnd.Text = drWaterBill[5].ToString();
txtTotalHCF.Text = drWaterBill[6].ToString();
txtTotalGallons.Text = drWaterBill[7].ToString();
txtFirstTierConsumption.Text = drWaterBill[8].ToString();
txtSecondTierConsumption.Text = drWaterBill[9].ToString();
txtLastTierConsumption.Text = drWaterBill[10].ToString();
txtWaterCharges.Text = drWaterBill[11].ToString();
txtSewerCharges.Text = drWaterBill[12].ToString();
txtEnvironmentCharges.Text = drWaterBill[13].ToString();
txtServiceCharges.Text = drWaterBill[14].ToString();
txtTotalCharges.Text = drWaterBill[15].ToString();
txtLocalTaxes.Text = drWaterBill[16].ToString();
txtStateTaxes.Text = drWaterBill[17].ToString();
txtPaymentDueDate.Text = drWaterBill[18].ToString();
txtAmountDue.Text = drWaterBill[19].ToString();
txtLatePaymentDueDate.Text = drWaterBill[20].ToString();
txtLateAmountDue.Text = drWaterBill[21].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNbr = N'" + txtAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Other Properties | |
| ListView | |
lvwWaterBills | No Change |
| Button | |
No Change | No Change |
| Button | |
btnWaterBillDetails | Water Bill &Details... Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterBills()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterBills();
}
private void btnProcessWaterBill_Click(object sender, EventArgs e)
{
Create create = new();
create.ShowDialog();
ShowWaterBills();
}
private void btnViewWaterBill_Click(object sender, EventArgs e)
{
Details details = new();
details.Show();
}
}
}
Water Bill Edition
.
Practical Learning: Creating a Water Bill Editor
| Control | (Name) | Text | Additional Properties | |
| Button |  |
btnFindWaterBill | &Find | |
| Button | |
btnUpdateWaterBill | &Update Water Bill | |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsWaterBills = new("WaterBillsSet");
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue " +
"FROM Management.WaterBills " +
"WHERE BillNumber = " + txtBillNumber.Text + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
mtbAccountNumber.Text = drWaterBill[0].ToString();
dtpMeterReadingStartDate.Text = drWaterBill[1].ToString();
dtpMeterReadingEndDate.Text = drWaterBill[2].ToString();
txtBillingDays.Text = drWaterBill[3].ToString();
txtCounterReadingStart.Text = drWaterBill[4].ToString();
txtCounterReadingEnd.Text = drWaterBill[5].ToString();
txtTotalHCF.Text = drWaterBill[6].ToString();
txtTotalGallons.Text = drWaterBill[7].ToString();
txtFirstTierConsumption.Text = drWaterBill[8].ToString();
txtSecondTierConsumption.Text = drWaterBill[9].ToString();
txtLastTierConsumption.Text = drWaterBill[10].ToString();
txtWaterCharges.Text = drWaterBill[11].ToString();
txtSewerCharges.Text = drWaterBill[12].ToString();
txtEnvironmentCharges.Text = drWaterBill[13].ToString();
txtServiceCharges.Text = drWaterBill[14].ToString();
txtTotalCharges.Text = drWaterBill[15].ToString();
txtLocalTaxes.Text = drWaterBill[16].ToString();
txtStateTaxes.Text = drWaterBill[17].ToString();
dtpPaymentDueDate.Text = drWaterBill[18].ToString();
txtAmountDue.Text = drWaterBill[19].ToString();
dtpLatePaymentDueDate.Text = drWaterBill[20].ToString();
txtLateAmountDue.Text = drWaterBill[21].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNbr = N'" + mtbAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.SelectCustomers", scWaterDistribution))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(scCustomers);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.Models;
namespace StellarWaterPoint31.WaterBills
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
. . .
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
. . .
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
private void btnEvaluateWaterBill_Click(object sender, EventArgs e)
{
double counterStart = 0, counterEnd = 0;
try
{
counterStart = double.Parse(txtCounterReadingStart.Text);
}
catch (FormatException feCRStart)
{
MessageBox.Show("You must enter a valid value in the Counter Reading Start text box. " +
"The error produced is: " + feCRStart.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
try
{
counterEnd = double.Parse(txtCounterReadingEnd.Text);
}
catch (FormatException feCREnd)
{
MessageBox.Show("You must enter a valid value in the Counter Reading End text box. " +
"The error produced is: " + feCREnd.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
WaterBill bill = new();
double consumption = counterEnd - counterStart;
double gallons = consumption * 748.05;
string strAccountType = txtAccountType.Text[..3];
(double first, double second, double last) tiers = bill.CalculateTiers(strAccountType, gallons);
double waterCharges = tiers.first + tiers.second + tiers.last;
double sewerCharges = bill.CalculateSewerCharges(strAccountType, waterCharges);
double envCharges = bill.CalculateEnvironmentCharges(strAccountType, waterCharges);
double srvCharges = bill.CalculateServiceCharges(strAccountType, waterCharges);
double totalCharges = waterCharges + sewerCharges + envCharges + srvCharges;
double localTaxes = bill.CalculateLocalTaxes(strAccountType, waterCharges);
double stateTaxes = bill.CalculateStateTaxes(strAccountType, waterCharges);
double amtDue = totalCharges + localTaxes + stateTaxes;
txtTotalHCF.Text = consumption.ToString();
txtTotalGallons.Text = ((int)(Math.Ceiling(gallons))).ToString();
txtFirstTierConsumption.Text = tiers.first.ToString("F");
txtSecondTierConsumption.Text = tiers.second.ToString("F");
txtLastTierConsumption.Text = tiers.last.ToString("F");
txtWaterCharges.Text = waterCharges.ToString("F");
txtSewerCharges.Text = sewerCharges.ToString("F");
txtEnvironmentCharges.Text = envCharges.ToString("F");
txtServiceCharges.Text = srvCharges.ToString("F");
txtTotalCharges.Text = totalCharges.ToString("F");
txtLocalTaxes.Text = localTaxes.ToString("F");
txtStateTaxes.Text = stateTaxes.ToString("F");
dtpPaymentDueDate.Value = bill.SetPaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtAmountDue.Text = amtDue.ToString("F");
dtpLatePaymentDueDate.Value = bill.SetLatePaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtLateAmountDue.Text = bill.CalculateLateAmountDue(strAccountType, amtDue).ToString("F");
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
using StellarWaterPoint31.Models;
namespace StellarWaterPoint31.WaterBills
{
public partial class Editor : Form
{
public Editor()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsWaterBills = new("WaterBillsSet");
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue " +
"FROM Management.WaterBills " +
"WHERE BillNumber = " + txtBillNumber.Text + ";",
scStellarWaterPoint);
scStellarWaterPoint.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
mtbAccountNumber.Text = drWaterBill[0].ToString();
dtpMeterReadingStartDate.Text = drWaterBill[1].ToString();
dtpMeterReadingEndDate.Text = drWaterBill[2].ToString();
txtBillingDays.Text = drWaterBill[3].ToString();
txtCounterReadingStart.Text = drWaterBill[4].ToString();
txtCounterReadingEnd.Text = drWaterBill[5].ToString();
txtTotalHCF.Text = drWaterBill[6].ToString();
txtTotalGallons.Text = drWaterBill[7].ToString();
txtFirstTierConsumption.Text = drWaterBill[8].ToString();
txtSecondTierConsumption.Text = drWaterBill[9].ToString();
txtLastTierConsumption.Text = drWaterBill[10].ToString();
txtWaterCharges.Text = drWaterBill[11].ToString();
txtSewerCharges.Text = drWaterBill[12].ToString();
txtEnvironmentCharges.Text = drWaterBill[13].ToString();
txtServiceCharges.Text = drWaterBill[14].ToString();
txtTotalCharges.Text = drWaterBill[15].ToString();
txtLocalTaxes.Text = drWaterBill[16].ToString();
txtStateTaxes.Text = drWaterBill[17].ToString();
dtpPaymentDueDate.Text = drWaterBill[18].ToString();
txtAmountDue.Text = drWaterBill[19].ToString();
dtpLatePaymentDueDate.Text = drWaterBill[20].ToString();
txtLateAmountDue.Text = drWaterBill[21].ToString();
}
}
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNbr = N'" + mtbAccountNumber.Text + "';",
scStellarWaterPoint);
scStellarWaterPoint.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scStellarWaterPoint);
scStellarWaterPoint.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnFindCustomerAccount_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsCustomersAccounts = new("CustomersSet");
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
using (SqlCommand scCustomers = new SqlCommand("Management.SelectCustomers", scStellarWaterPoint))
{
scCustomers.CommandType = CommandType.StoredProcedure;
SqlParameter spCustomers = new SqlParameter();
spCustomers.ParameterName = "@acntNbr";
spCustomers.SqlDbType = SqlDbType.NVarChar;
spCustomers.Size = 15;
spCustomers.Direction = ParameterDirection.Input;
spCustomers.Value = mtbAccountNumber.Text;
scCustomers.Parameters.Add(spCustomers);
scStellarWaterPoint.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(scCustomers);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
}
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scStellarWaterPoint);
scStellarWaterPoint.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
{
TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;
txtBillingDays.Text = (tsDays.Days + 1).ToString();
}
private void btnEvaluateWaterBill_Click(object sender, EventArgs e)
{
double counterStart = 0, counterEnd = 0;
try
{
counterStart = double.Parse(txtCounterReadingStart.Text);
}
catch (FormatException feCRStart)
{
MessageBox.Show("You must enter a valid value in the Counter Reading Start text box. " +
"The error produced is: " + feCRStart.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
try
{
counterEnd = double.Parse(txtCounterReadingEnd.Text);
}
catch (FormatException feCREnd)
{
MessageBox.Show("You must enter a valid value in the Counter Reading End text box. " +
"The error produced is: " + feCREnd.Message,
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
WaterBill bill = new();
double consumption = counterEnd - counterStart;
double gallons = consumption * 748.05;
string strAccountType = txtAccountType.Text[..3];
(double first, double second, double last) tiers = bill.CalculateTiers(strAccountType, gallons);
double waterCharges = tiers.first + tiers.second + tiers.last;
double sewerCharges = bill.CalculateSewerCharges(strAccountType, waterCharges);
double envCharges = bill.CalculateEnvironmentCharges(strAccountType, waterCharges);
double srvCharges = bill.CalculateServiceCharges(strAccountType, waterCharges);
double totalCharges = waterCharges + sewerCharges + envCharges + srvCharges;
double localTaxes = bill.CalculateLocalTaxes(strAccountType, waterCharges);
double stateTaxes = bill.CalculateStateTaxes(strAccountType, waterCharges);
double amtDue = totalCharges + localTaxes + stateTaxes;
txtTotalHCF.Text = consumption.ToString();
txtTotalGallons.Text = ((int)(Math.Ceiling(gallons))).ToString();
txtFirstTierConsumption.Text = tiers.first.ToString("F");
txtSecondTierConsumption.Text = tiers.second.ToString("F");
txtLastTierConsumption.Text = tiers.last.ToString("F");
txtWaterCharges.Text = waterCharges.ToString("F");
txtSewerCharges.Text = sewerCharges.ToString("F");
txtEnvironmentCharges.Text = envCharges.ToString("F");
txtServiceCharges.Text = srvCharges.ToString("F");
txtTotalCharges.Text = totalCharges.ToString("F");
txtLocalTaxes.Text = localTaxes.ToString("F");
txtStateTaxes.Text = stateTaxes.ToString("F");
dtpPaymentDueDate.Value = bill.SetPaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtAmountDue.Text = amtDue.ToString("F");
dtpLatePaymentDueDate.Value = bill.SetLatePaymentDueDate(strAccountType, dtpMeterReadingEndDate.Value);
txtLateAmountDue.Text = bill.CalculateLateAmountDue(strAccountType, amtDue).ToString("F");
}
private void btnUpdateWaterBill_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtBillNumber.Text))
{
MessageBox.Show("You must enter a bill number and click Find Water Bill. Otherwise, there is no water bill to update.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (string.IsNullOrEmpty(mtbAccountNumber.Text))
{
MessageBox.Show("You must enter the account number of the customer whose bill is being updated. " +
"Otherwise, the record cannot be saved.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (string.IsNullOrEmpty(txtAccountName.Text))
{
MessageBox.Show("You must enter a valid account number of a customer whose bill is being updated, " +
"and then click Find Customer Account.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("UPDATE Management.WaterBills SET AccountNumber = N'" + mtbAccountNumber.Text + "' WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET MeterReadingStartDate = N'" + dtpMeterReadingStartDate.Value.ToShortDateString() + "' WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET MeterReadingEndDate = N'" + dtpMeterReadingEndDate.Value.ToShortDateString() + "' WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET BillingDays = " + txtBillingDays.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET CounterReadingStart = " + txtCounterReadingStart.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET CounterReadingEnd = " + txtCounterReadingEnd.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET TotalHCF = " + txtTotalHCF.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET TotalGallons = " + txtTotalGallons.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET FirstTierConsumption = " + txtFirstTierConsumption.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET SecondTierConsumption = " + txtSecondTierConsumption.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET LastTierConsumption = " + txtLastTierConsumption.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET WaterCharges = " + txtWaterCharges.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET SewerCharges = " + txtSewerCharges.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET EnvironmentCharges = " + txtEnvironmentCharges.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET ServiceCharges = " + txtServiceCharges.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET TotalCharges = " + txtTotalCharges.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET LocalTaxes = " + txtLocalTaxes.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET StateTaxes = " + txtStateTaxes.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET PaymentDueDate = N'" + dtpPaymentDueDate.Value.ToShortDateString() + "' WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET AmountDue = " + txtAmountDue.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET LateAmountDue = " + txtLateAmountDue.Text + " WHERE BillNumber = " + txtBillNumber.Text + ";" +
"UPDATE Management.WaterBills SET LatePaymentDueDate = N'" + dtpLatePaymentDueDate.Value.ToShortDateString() + "' WHERE BillNumber = " + txtBillNumber.Text + ";",
scStellarWaterPoint);
scStellarWaterPoint.Open();
cmdWaterBills.ExecuteNonQuery();
MessageBox.Show("The customer's water bill has been updated.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Text | Other Properties | |
| ListView | |
lvwWaterBills | No Change | |
| Button | |
btnNewWaterBill | No Change | |
| Button | |
btnViewWaterBill | No Change | |
| Button | |
btnEditWaterBill | &Edit Water Bill... | Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterBills()
{
. . .
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterBills();
}
private void btnProcessWaterBill_Click(object sender, EventArgs e)
{
Create create = new();
create.ShowDialog();
ShowWaterBills();
}
private void btnViewWaterBill_Click(object sender, EventArgs e)
{
Details details = new();
details.Show();
}
private void btnEditWaterBill_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterBills();
}
}
}
Account #: 9249-379-6848 and click Find Customer Account Meter Reading Start Date: 1/19/2010 Meter Reading End Date: 4/17/2010 Counter Reading Start: 256953 Counter Reading End: 256966
.
Practical Learning: Editing/Updating a Record
| Control | (Name) | Text | |
| Button | |
btnDeleteWaterBill | &Delete Water Bill |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsWaterBills = new("WaterBillsSet");
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue " +
"FROM Management.WaterBills " +
"WHERE BillNumber = " + txtBillNumber.Text + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
txtAccountNumber.Text = drWaterBill[0].ToString();
txtMeterReadingStartDate.Text = drWaterBill[1].ToString();
txtMeterReadingEndDate.Text = drWaterBill[2].ToString();
txtBillingDays.Text = drWaterBill[3].ToString();
txtCounterReadingStart.Text = drWaterBill[4].ToString();
txtCounterReadingEnd.Text = drWaterBill[5].ToString();
txtTotalHCF.Text = drWaterBill[6].ToString();
txtTotalGallons.Text = drWaterBill[7].ToString();
txtFirstTierConsumption.Text = drWaterBill[8].ToString();
txtSecondTierConsumption.Text = drWaterBill[9].ToString();
txtLastTierConsumption.Text = drWaterBill[10].ToString();
txtWaterCharges.Text = drWaterBill[11].ToString();
txtSewerCharges.Text = drWaterBill[12].ToString();
txtEnvironmentCharges.Text = drWaterBill[13].ToString();
txtServiceCharges.Text = drWaterBill[14].ToString();
txtTotalCharges.Text = drWaterBill[15].ToString();
txtLocalTaxes.Text = drWaterBill[16].ToString();
txtStateTaxes.Text = drWaterBill[17].ToString();
txtPaymentDueDate.Text = drWaterBill[18].ToString();
txtAmountDue.Text = drWaterBill[19].ToString();
txtLatePaymentDueDate.Text = drWaterBill[20].ToString();
txtLateAmountDue.Text = drWaterBill[21].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNbr = N'" + txtAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
}
}using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Delete : Form
{
public Delete()
{
InitializeComponent();
}
private void btnFindWaterBill_Click(object sender, EventArgs e)
{
string? strMeterNumber = string.Empty;
DataSet dsWaterBills = new("WaterBillsSet");
DataSet dsCustomersAccounts = new("CustomersAccountsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT AccountNumber, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" FirstTierConsumption, " +
" SecondTierConsumption, " +
" LastTierConsumption, " +
" WaterCharges, " +
" SewerCharges, " +
" EnvironmentCharges, " +
" ServiceCharges, " +
" TotalCharges, " +
" LocalTaxes, " +
" StateTaxes, " +
" PaymentDueDate, " +
" AmountDue, " +
" LatePaymentDueDate, " +
" LateAmountDue " +
"FROM Management.WaterBills " +
"WHERE BillNumber = " + txtBillNumber.Text + ";",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
txtAccountNumber.Text = drWaterBill[0].ToString();
txtMeterReadingStartDate.Text = drWaterBill[1].ToString();
txtMeterReadingEndDate.Text = drWaterBill[2].ToString();
txtBillingDays.Text = drWaterBill[3].ToString();
txtCounterReadingStart.Text = drWaterBill[4].ToString();
txtCounterReadingEnd.Text = drWaterBill[5].ToString();
txtTotalHCF.Text = drWaterBill[6].ToString();
txtTotalGallons.Text = drWaterBill[7].ToString();
txtFirstTierConsumption.Text = drWaterBill[8].ToString();
txtSecondTierConsumption.Text = drWaterBill[9].ToString();
txtLastTierConsumption.Text = drWaterBill[10].ToString();
txtWaterCharges.Text = drWaterBill[11].ToString();
txtSewerCharges.Text = drWaterBill[12].ToString();
txtEnvironmentCharges.Text = drWaterBill[13].ToString();
txtServiceCharges.Text = drWaterBill[14].ToString();
txtTotalCharges.Text = drWaterBill[15].ToString();
txtLocalTaxes.Text = drWaterBill[16].ToString();
txtStateTaxes.Text = drWaterBill[17].ToString();
txtPaymentDueDate.Text = drWaterBill[18].ToString();
txtAmountDue.Text = drWaterBill[19].ToString();
txtLatePaymentDueDate.Text = drWaterBill[20].ToString();
txtLateAmountDue.Text = drWaterBill[21].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdCustomersAccounts = new SqlCommand("SELECT ALL * " +
"FROM Management.GetCustomersAccounts " +
"WHERE AccountNbr = N'" + txtAccountNumber.Text + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaCustomersAccounts = new SqlDataAdapter(cmdCustomersAccounts);
sdaCustomersAccounts.Fill(dsCustomersAccounts);
foreach (DataRow drCustomerAccount in dsCustomersAccounts.Tables[0].Rows!)
{
txtAccountName.Text = drCustomerAccount[2].ToString();
strMeterNumber = drCustomerAccount[3].ToString();
txtAccountType.Text = drCustomerAccount[4].ToString();
txtAddress.Text = drCustomerAccount[5].ToString();
txtCity.Text = drCustomerAccount[6].ToString();
txtCounty.Text = drCustomerAccount[7].ToString();
txtState.Text = drCustomerAccount[8].ToString();
txtZIPCode.Text = drCustomerAccount[9].ToString();
}
}
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT Make, " +
" Model, " +
" MeterSize " +
"FROM Management.WaterMeters " +
"WHERE MeterNumber = N'" + strMeterNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new("WaterMetersSet");
sdaWaterMeters.Fill(dsWaterMeters);
foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows!)
{
txtMeterDetails.Text = drWaterMeter[0].ToString() + " " +
drWaterMeter[1].ToString() +
" (Meter Size: " + drWaterMeter[2].ToString() + ")";
}
}
}
private void btnDeleteWaterBill_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtBillNumber.Text))
{
MessageBox.Show("You must enter a valid bill number and then click Find Water Bill. " +
"Otherwise, there is no water bill to delete.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("Are you sure you want to delete this water bill (you cannot undo the action)?",
"Stellar Water Point",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
using (SqlConnection scStellarWaterPoint = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("DELETE Management.WaterBills WHERE BillNumber = " + txtBillNumber.Text + ";",
scStellarWaterPoint);
scStellarWaterPoint.Open();
cmdWaterBills.ExecuteNonQuery();
MessageBox.Show("The water bill has been deleted.",
"Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
| Control | (Name) | Text | Other Properties | |
| ListView | |
lvwWaterBills | Anchor: Bottom, Top, Bottom, Left, Right | |
| Button | |
btnProcessWaterBill | &Process Water Bill... | Anchor: Bottom, Right |
| Button | |
btnViewWaterBill | &View Water Bill... | Anchor: Bottom, Right |
| Button | |
btnEditWaterBill | &Edit Water Bill... | Anchor: Bottom, Right |
| Button | |
btnDeleteWaterBill | &Delete Water Bill... | Anchor: Bottom, Right |
| Button | |
btnClose | &Close | Anchor: Bottom, Right |
using System.Data;
using Microsoft.Data.SqlClient;
namespace StellarWaterPoint31.WaterBills
{
public partial class Central : Form
{
public Central()
{
InitializeComponent();
}
private void ShowWaterBills()
{
DataSet dsWaterBills = new("WaterBillsSet");
using (SqlConnection scWaterDistribution = new SqlConnection("Data Source=(local);" +
"Initial Catalog='StellarWaterPoint26';" +
"TrustServerCertificate=True;" +
"Integrated Security=True;"))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillId, " +
" BillNumber, " +
" AccountSummary, " +
" MeterReadingStartDate, " +
" MeterReadingEndDate, " +
" BillingDays, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" CounterReadingStart, " +
" CounterReadingEnd, " +
" TotalHCF, " +
" TotalGallons, " +
" PaymentDueDate, " +
" AmountDue " +
"FROM Management.GetWaterBills " +
"ORDER BY WaterBillId; ",
scWaterDistribution);
scWaterDistribution.Open();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
sdaWaterBills.Fill(dsWaterBills);
lvwWaterBills.Items.Clear();
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows!)
{
ListViewItem lviWaterBill = new(drWaterBill["WaterBillId"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["BillNumber"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["AccountSummary"].ToString());
lviWaterBill.SubItems.Add(DateTime.Parse(drWaterBill["MeterReadingStartDate"].ToString()!).ToShortDateString());
lviWaterBill.SubItems.Add(DateTime.Parse(drWaterBill["MeterReadingEndDate"].ToString()!).ToShortDateString());
lviWaterBill.SubItems.Add(drWaterBill["BillingDays"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["CounterReadingStart"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["CounterReadingEnd"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["TotalHCF"].ToString());
lviWaterBill.SubItems.Add(drWaterBill["TotalGallons"].ToString());
lviWaterBill.SubItems.Add(DateTime.Parse(drWaterBill["PaymentDueDate"].ToString()!).ToShortDateString());
lviWaterBill.SubItems.Add(drWaterBill["AmountDue"].ToString());
lvwWaterBills.Items.Add(lviWaterBill);
}
}
}
private void Central_Load(object sender, EventArgs e)
{
ShowWaterBills();
}
private void btnProcessWaterBill_Click(object sender, EventArgs e)
{
Create create = new();
create.ShowDialog();
ShowWaterBills();
}
private void btnViewWaterBill_Click(object sender, EventArgs e)
{
Details details = new();
details.Show();
}
private void btnEditWaterBill_Click(object sender, EventArgs e)
{
Editor editor = new();
editor.ShowDialog();
ShowWaterBills();
}
private void btnDeleteWaterBill_Click(object sender, EventArgs e)
{
Delete delete = new();
delete.ShowDialog();
ShowWaterBills();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
|
|
|||
| Home | Copyright © 2003-2025, FunctionX | Saturday 01 April 2023 | |
|
|
|||