Application Setup

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 LearningPractical Learning: Introducing the Application

  1. Start Microsoft Visual Studio
  2. In the Visual Studio 2022 dialog box, click Create a New Project
  3. In the Create a New Project dialog box, in the Languages combo box, select C#
  4. In the list of projects templates, click Windows Forms App
  5. Click Next
  6. Change the Project Name to StellarWaterPoint2
  7. Click Next
  8. In the Framework combo box, select the highest version: .NET 9.0 (Standard Term Suppot)
  9. Click Create

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 LearningPractical Learning: Preparing a Database

  1. To create a database:
    • If you want to create the database in Microsoft Visual Studio as a local database:
      1. On the main menu of Microsoft Visual Studio, click View and click Server Explorer
      2. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
      3. In the Server Name combo box, select your server or type (local)
      4. Set the database name as StellarWaterPoint1
      5. Click OK
      6. In the Server Explorer, right-click the StellarWaterPoint connection and click New Query
    • If you want to create the database in Microsoft SQL Server:
      1. Start SQL Server Management Studio
      2. In the Object Explorer, right-click the name of the server and click New Query
      3. In the empty document, type the following code: 
        USE master;
GO
IF DB_ID (N'StellarWaterPoint1') IS NOT NULL
	DROP DATABASE StellarWaterPoint1;
GO
CREATE DATABASE StellarWaterPoint1;
GO
USE StellarWaterPoint1;
GO
      4. On the Standard toolbar, click the Execute button
      5. Delete everything in the Query Editor
  2. In the Query Editor, type the following code to create the tables: 
    CREATE TABLE WaterMeters
(
	WaterMeterId int identity(1, 1),
	MeterNumber  nvarchar(10) not null,
	Make         nvarchar(25) null,
	Model        nvarchar(15) not null,
	MeterSize    nvarchar(15),
	CONSTRAINT   PK_WaterMeters PRIMARY KEY(WaterMeterId)
);
GO

CREATE TABLE Customers
(
	CustomerId    int identity(1, 1),
	AccountNumber nvarchar(15) not null,
	MeterNumber   nvarchar(10),
	FirstName     nvarchar(18),
	LastName      nvarchar(18) not null,
	[Address]     nvarchar(150),
	City          nvarchar(25),
	County        nvarchar(35),
	[State]       nvarchar(35) not null,
	ZIPCode       nvarchar(12),
	CONSTRAINT    PK_Customers PRIMARY KEY(CustomerId)
);
GO

CREATE TABLE WaterBills
(
	WaterBillId           int identity(1, 1),
	AccountNumber         nvarchar(15) not null,
	InvoiceNumber         int          not null,
	MeterReadingStartDate nvarchar(50) not null,
	MeterReadingEndDate   nvarchar(50) not null,
	BillingDays           int          not null,
	CounterReadingStart   nvarchar(15),
	CounterReadingEnd     nvarchar(15),
	Consumption           nvarchar(15),
	TotalGallons          nvarchar(15),
	FirstTierConsumption  nvarchar(15),
	SecondTierConsumption nvarchar(15),
	LastTierConsumption   nvarchar(15),
	WaterCharges          nvarchar(15),
	SewerCharges          nvarchar(15),
	EnvironmentCharges    nvarchar(15),
	TotalCharges          nvarchar(15),
	LocalTaxes            nvarchar(15),
	StateTaxes            nvarchar(15),
	PaymentDueDate        nvarchar(50),
	AmountDue             nvarchar(15),
	LatePaymentDueDate    nvarchar(50),
	LateAmountDue         nvarchar(15),
	CONSTRAINT            PK_WaterBills PRIMARY KEY(WaterBillId)
);
GO
  3. Right-click inside the document and click Execute

Supporting ADO.NET

To perform the database operations of our application, we will use ADO.NET.

Practical LearningPractical Learning: Supporting ADO.NET

  1. In the Solution Explorer, right-click the name of the project and click Manage NuGet Packages...
  2. In the NuGet tab, click Browse
  3. In the combo box, type Microsoft.Data.SqlClient
  4. In the list, click System.Data.SqlClient
  5. In the right list, click Install
  6. In the Preview Changes dialog box, click Apply
  7. In the License Acceptance dialog box, click I Accept

Water Meters

Setting Up a Water Meter

Before consuming water, a customer must have a water meter installed at his/her residence or in the business location. To represent such a water meter, we had created a table for water meters. We would have created forms to let the user create records.

Practical LearningPractical Learning: Setting Up a Water Meter

  1. In the Solution Explorer, right-click StellarWaterPoint2 -> New -> Folder
  2. TypeWaterMeters and press Enter
  3. To create a form, in the Solution Explorer, right-click WaterMeters -> Add -> Form (Windows Forms)...
  4. Set the name to Create
  5. Click Add
  6. Design the form as follows:

    Stellar Water Point - New Water Meter

    Control (Name) Text Other Properties
    Label Label   &Meter #:  
    MaskedTextBox Masked Text Box mtbMeterNumber   Masked: 000-00-000
    Label Label   M&ake:  
    TextBox Text Box txtMake  
    Label Label   M&odel:  
    TextBox Text Box txtModel   Modifiers: Public
    Label Label   Me&ter Size:  
    TextBox Text Box txtMeterSize  
    Button Button btnSaveWateMeter S&ave Water Meter  

Practical LearningPractical Learning: Presenting Water Meters

  1. To create a form, in the Solution Explorer, right-click WaterMeters -> Add -> Form (Windows Forms)...
  2. Set the name to WaterMeters
  3. Click Add
  4. Design the form as follows:

    Water Meters

    Control Text Name Other Properties
    ListView List View   lvwWaterMeters FullRowSelect:True
    GridLines: True
    View: Details
    Button Button Close btnClose

    List View Columns

    (Name) Text TextAlign Width
    colWaterMeterId Id   40
    colMeterNumber Meter # 120
    colMake Make   225
    colModel Model   105
    colMeterSize Meter Size   105

Customers

Customers are entities that use a product. For a water distribution company, customers use water provided by the company. We had already created a table for customers, and we added some sample records. To show a list of customers, we will create a form equiped with a list view to display a list of customers.

Practical LearningPractical Learning: Introducing Customers

  1. To create a form, on the main menu, click Project -> Add Form (Windows Forms)...
  2. Set the name to Customers
  3. Click Add
  4. Design the form as follows:

    Stellar Water Point - Customers

    Control Text Name Other Properties
    ListView List View   lvwCustomers FullRowSelect:True
    GridLines: True
    View: Details
    Button Button Close btnClose

    List View Columns

    (Name) Text TextAlign Width
    colCustomerId Id   40
    colAccountNumber Account # Center 175
    colMeterNumber Meter #   120
    colFirstName First Name   105
    colLastName Last Name   105
    colAddress Address   250
    colCity City   120
    colCounty County   150
    colState State    
    colZIPCode ZIP Code   100

New Water Bill

The main subject of a business-customer relationship is a money transaction. Customers receive bills and have to pay. In our database, we created a table for water bills. Now we need a tool, a form, to help an employee create a water bill.

Practical LearningPractical Learning: Introducing Water Bills

  1. To create a form, in the Solution Explorer, right-click StellarWaterPoint1 -> Add -> Form (Windows Forms)...
  2. Set the name to WaterBillNew
  3. Click Add
  4. Design the form as follows:

    Stellar Water Point - New Water Bill

    Control Text Name Other Properties
    Label Label &Invoice #:    
    TextBox Text Box   txtInvoiceNumber  
    GroupBox Group Box Customer Information    
    Label Label &Account #:    
    TextBox Text Box   txtAccountNumber  
    Label Label C&ustomer Name:    
    TextBox Text Box   txtCustomerName  
    Label Label Address:    
    TextBox Text Box   txtAddress  
    TextBox Text Box   txtCity  
    TextBox Text Box   txtCounty  
    TextBox Text Box   txtState  
    TextBox Text Box   txtZIPCode  
    Label Label _________________________________________________    
    Label Label Meter Details:    
    TextBox Text Box   txtMeterDetails  
    GroupBox Group Box Meter Reading    
    Label Label Meter &Reading Start Date:    
    Date Time Picker Text Box   dtpMeterReadingStartDate  
    Label Label Meter Reading &End Date:    
    Date Time Picker Text Box   dtpMeterReadingEndDate  
    Label Label Coun&ter Reading Start:    
    TextBox Text Box   txtCounterReadingStart  
    Label Label Counter Read&ing End:    
    TextBox Text Box   txtCounterReadingStart  
    GroupBox Group Box Meter Result    
    Label Label &Billing Days:    
    TextBox Text Box   txtBillingDays  
    Label Label Consu&mption:    
    TextBox Text Box   txtConsumption  
    Label Label Total &Gallons:    
    TextBox Text Box   txtTotalGallons  
    Label Label &First Tier Consumption:    
    TextBox Text Box   txtFirstTierConsumption  
    Label Label Sec&ond Tier:    
    TextBox Text Box   txtSecondTierConsumption  
    Label Label &Last Tier:    
    TextBox Text Box   txtLastTierConsumption  
    GroupBox Group Box Consumption Charges    
    Label Label &Water Charges:    
    TextBox Text Box   txtWaterCharges  
    Label Label &Sewer Charges:    
    TextBox Text Box   txtSewerCharges  
    Label Label &Environment Charges:    
    TextBox Text Box   txtEnvironmentCharges  
    Label Label &Total Charges:    
    TextBox Text Box   txtTotalCharges  
    GroupBox Group Box Taxes    
    Label Label Local &Taxes:    
    TextBox Text Box   txtLocalTaxes  
    Label Label &State Taxes:    
    TextBox Text Box   txtStateTaxes  
    GroupBox Group Box Water Bill Payment    
    Label Label Payment Due Date:    
    Date Time Picker Text Box   dtpPaymentDueDate  
    Label Label &Amount Due:    
    TextBox Text Box   txtAmountDue  
    Label Label Late Payment Due Date:    
    Date Time Picker Text Box   dtpLatePaymentDueDate  
    Label Label &Late Amount Due:    
    TextBox Text Box   txtLateAmountDue  
    Button Button Save Water Bill btnSaveWaterBill  
    Button Button Close btnClose  

    Form Properties

    Form Property Value
    FormBorderStyle FixedDialog
    Text Stellar Water Point - New Water Bill
    StartPosition CenterScreen
    MaximizeBox False
  5. Double-click an unoccupied area of the form to create its Load event
  6. Return to the form and click the Account # text box
  7. In the Properties window, click the Events button Events
  8. In the Events section of the Properties window, double-click Leave
  9. Return to the form and double-click the Meter Reading End Date date time picker to generate its Value Changed event
  10. Return to the form and click the Counter Reading End text box
  11. In the Events section of the Properties window, double-click Leave
  12. Return to the form and double-click the Save Water Bill button to generate its Click event
  13. Return to the form and double-click the Close button to generate its Click event
  14. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;

namespace StellarWaterPoint1
{
    public partial class WaterBillNew : Form
    {
        public WaterBillNew()
        {
            InitializeComponent();
        }

        private void InitializeWaterBill()
        {
            Random rndNumber = new Random();

            txtInvoiceNumber.Text = rndNumber.Next(100000, 999999).ToString();
            txtAccountNumber.Text = string.Empty;
            txtCustomerName.Text = string.Empty;
            txtAddress.Text = string.Empty;
            txtCity.Text = string.Empty;
            txtCounty.Text = string.Empty;
            txtState.Text = string.Empty;
            txtZIPCode.Text = string.Empty;
            txtMeterDetails.Text = string.Empty;
            dtpMeterReadingStartDate.Value = DateTime.Now;
            dtpMeterReadingEndDate.Value = DateTime.Now;
            txtBillingDays.Text = string.Empty;
            txtCounterReadingStart.Text = string.Empty;
            txtCounterReadingEnd.Text = string.Empty;
            txtConsumption.Text = string.Empty;
            txtTotalGallons.Text = string.Empty;
            txtFirstTierConsumption.Text = string.Empty;
            txtSecondTierConsumption.Text = string.Empty;
            txtLastTierConsumption.Text = string.Empty;
            txtWaterCharges.Text = string.Empty;
            txtSewerCharges.Text = string.Empty;
            txtEnvironmentCharges.Text = string.Empty;
            txtTotalCharges.Text = string.Empty;
            txtLocalTaxes.Text = string.Empty;
            txtStateTaxes.Text = string.Empty;
            dtpPaymentDueDate.Value = DateTime.Now;
            txtAmountDue.Text = string.Empty;
            dtpLatePaymentDueDate.Value = DateTime.Now;
            txtLateAmountDue.Text = string.Empty;
        }

        private void WaterBillNew_Load(object sender, EventArgs e)
        {
            InitializeWaterBill();
        }

        private void txtAccountNumber_Leave(object sender, EventArgs e)
        {
            string meterNumber = string.Empty;

            using (SqlConnection scStellarWaterPoint =
                new SqlConnection("Data Source=(local);" +
                                  "Database=StellarWaterPoint;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomers = new SqlCommand("IdentifyClient", scStellarWaterPoint);

                cmdCustomers.CommandType = CommandType.StoredProcedure;

                SqlParameter spCustomer = new SqlParameter();
                spCustomer.ParameterName = "@AcntNbr";
                spCustomer.DbType = DbType.String;
                spCustomer.Value = txtAccountNumber.Text;
                spCustomer.Direction = ParameterDirection.Input;
                cmdCustomers.Parameters.Add(spCustomer);

                scStellarWaterPoint.Open();
                cmdCustomers.ExecuteNonQuery();

                SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);

                DataSet dsCustomers = new DataSet("CustomersSet");

                sdaCustomers.Fill(dsCustomers);

                if (dsCustomers.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drClient in dsCustomers.Tables[0].Rows)
                    {
                        meterNumber = drClient[1].ToString()!;
                        txtCustomerName.Text = string.Format("{0} {1}", drClient[2].ToString(), drClient[3].ToString());
                        txtAddress.Text = drClient[4].ToString();
                        txtCity.Text = drClient[5].ToString();
                        txtCounty.Text = drClient[6].ToString();
                        txtState.Text = drClient[7].ToString();
                        txtZIPCode.Text = drClient[8].ToString();
                    }
                }
                else
                {
                    txtCustomerName.Text = string.Empty;
                    txtAddress.Text = string.Empty;
                    txtCity.Text = string.Empty;
                    txtCounty.Text = string.Empty;
                    txtState.Text = string.Empty;
                    txtZIPCode.Text = string.Empty;
                    txtMeterDetails.Text = string.Empty;
                }
            }

            if (!string.IsNullOrEmpty(meterNumber))
            {
                using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("GetWaterMonitor", scStellarWaterPoint);

                    cmdWaterMeters.CommandType = CommandType.StoredProcedure;

                    SqlParameter spWaterMeter = new SqlParameter();
                    spWaterMeter.ParameterName = "@MtrNbr";
                    spWaterMeter.DbType = DbType.String;
                    spWaterMeter.Value = meterNumber;
                    spWaterMeter.Direction = ParameterDirection.Input;
                    cmdWaterMeters.Parameters.Add(spWaterMeter);

                    scStellarWaterPoint.Open();
                    cmdWaterMeters.ExecuteNonQuery();

                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);

                    DataSet dsWaterMeters = new DataSet("WaterMetersSet");

                    sdaWaterMeters.Fill(dsWaterMeters);

                    foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows)
                    {
                        txtMeterDetails.Text = drWaterMeter[0].ToString() + " - " +
                                               drWaterMeter[1].ToString() + " " +
                                               drWaterMeter[2].ToString() + " (Size: " +
                                               drWaterMeter[3].ToString() + ")";
                    }
                }
            }
        }

        private void dtpMeterReadingEndDate_ValueChanged(object sender, EventArgs e)
        {
            TimeSpan tsDays = dtpMeterReadingEndDate.Value - dtpMeterReadingStartDate.Value;

            txtBillingDays.Text = tsDays.Days.ToString();
        }

        private void txtCounterReadingEnd_Leave(object sender, EventArgs e)
        {
            double counterStart = 0, counterEnd = 0;

            try
            {
                counterStart = double.Parse(txtCounterReadingStart.Text);
            }
            catch (FormatException feCRStart)
            {
                MessageBox.Show("There was a problem with the value of the " +
                                "Counter Reading Start. The error produced is: " + feCRStart.Message,
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            try
            {
                counterEnd = double.Parse(txtCounterReadingEnd.Text);
            }
            catch (FormatException feCREnd)
            {
                MessageBox.Show("There was a problem with the value of the " +
                                "Counter Reading End. The error produced is: " + feCREnd.Message,
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            double consumption = counterEnd - counterStart;
            double gallons = consumption * 748.05;
            double firstTier = gallons * (48.00 / 10000.00);
            double secondTier = gallons * (32.00 / 10000.00);
            double lastTier = gallons * (20.00 / 10000.00);
            double waterCharges = firstTier + secondTier + lastTier;
            double sewerCharges = waterCharges * 28.65 / 100;
            double envCharges = waterCharges * 0.22184;
            double totalCharges = waterCharges + sewerCharges + envCharges;
            double localTaxes = totalCharges * 0.06148;
            double stateTaxes = totalCharges * 0.01374;
            double amtDue = totalCharges + localTaxes + stateTaxes;

            TimeSpan tsPaymentDueDate = new TimeSpan(15, 0, 0, 0);

            txtConsumption.Text = consumption.ToString();
            txtTotalGallons.Text = gallons.ToString("F");
            txtFirstTierConsumption.Text = firstTier.ToString("F");
            txtSecondTierConsumption.Text = secondTier.ToString("F");
            txtLastTierConsumption.Text = lastTier.ToString("F");
            txtWaterCharges.Text = waterCharges.ToString("F");
            txtSewerCharges.Text = sewerCharges.ToString("F");
            txtEnvironmentCharges.Text = envCharges.ToString("F");
            txtTotalCharges.Text = totalCharges.ToString("F");
            txtLocalTaxes.Text = localTaxes.ToString("F");
            txtStateTaxes.Text = stateTaxes.ToString("F");
            dtpPaymentDueDate.Value = dtpMeterReadingEndDate.Value + tsPaymentDueDate;
            txtAmountDue.Text = amtDue.ToString("F");
            dtpLatePaymentDueDate.Value = dtpMeterReadingEndDate.Value + new TimeSpan(30, 0, 0, 0);
            txtLateAmountDue.Text = (amtDue + 8.95).ToString("F");
        }

        private void btnSaveWaterBill_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtInvoiceNumber.Text))
            {
                MessageBox.Show("You must enter an invoice number. Otherwise, the record cannot be saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (string.IsNullOrEmpty(txtAccountNumber.Text))
            {
                MessageBox.Show("You must enter the account number of the customer whose bill is being prepare. " +
                                "Otherwise, the record cannot be saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (string.IsNullOrEmpty(txtCounterReadingStart.Text))
            {
                MessageBox.Show("You must type the starting value of the water meter for this customer bill.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (string.IsNullOrEmpty(txtCounterReadingEnd.Text))
            {
                MessageBox.Show("You must type the ending value of the water meter for this bill.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
            {
                SqlCommand cmdWaterBills = new SqlCommand("INSERT INTO WaterBills(AccountNumber,         " +
                                                          "                       InvoiceNumber,         " +
                                                          "                       MeterReadingStartDate, " +
                                                          "                       MeterReadingEndDate,   " +
                                                          "                       BillingDays,           " +
                                                          "                       CounterReadingStart,   " +
                                                          "                       CounterReadingEnd,     " +
                                                          "                       Consumption,           " +
                                                          "                       TotalGallons,          " +
                                                          "                       FirstTierConsumption,  " +
                                                          "                       SecondTierConsumption, " +
                                                          "                       LastTierConsumption,   " +
                                                          "                       WaterCharges,          " +
                                                          "                       SewerCharges,          " +
                                                          "                       EnvironmentCharges,    " +
                                                          "                       TotalCharges,          " +
                                                          "                       LocalTaxes,            " +
                                                          "                       StateTaxes,            " +
                                                          "                       PaymentDueDate,        " +
                                                          "                       AmountDue,             " +
                                                          "                       LatePaymentDueDate,    " +
                                                          "                       LateAmountDue) " +
                                                          "VALUES(N'" + txtAccountNumber.Text + "',   " +
                                                                        txtInvoiceNumber.Text + ",  N'" +
                                                                        dtpMeterReadingStartDate.Value.ToShortDateString() + "', N'" +
                                                                        dtpMeterReadingEndDate.Value.ToShortDateString() + "',   " +
                                                                        txtBillingDays.Text + " , N'" +
                                                                        txtCounterReadingStart.Text + "', N'" +
                                                                        txtCounterReadingEnd.Text + "', N'" +
                                                                        txtConsumption.Text + "', N'" +
                                                                        txtTotalGallons.Text + "', N'" +
                                                                        txtFirstTierConsumption.Text + "', N'" +
                                                                        txtSecondTierConsumption.Text + "', N'" +
                                                                        txtLastTierConsumption.Text + "', N'" +
                                                                        txtWaterCharges.Text + "', N'" +
                                                                        txtSewerCharges.Text + "', N'" +
                                                                        txtEnvironmentCharges.Text + "', N'" +
                                                                        txtTotalCharges.Text + "', N'" +
                                                                        txtLocalTaxes.Text + "', N'" +
                                                                        txtStateTaxes.Text + "', N'" +
                                                                        dtpPaymentDueDate.Value.ToShortDateString() + "', N'" +
                                                                        txtAmountDue.Text + "', N'" +
                                                                        dtpLatePaymentDueDate.Value.ToShortDateString() + "', N'" +
                                                                        txtLateAmountDue.Text + "')",
                                                          scStellarWaterPoint);

                scStellarWaterPoint.Open();
                cmdWaterBills.ExecuteNonQuery();

                MessageBox.Show("The customer's water bill has been saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);

                InitializeWaterBill();
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}
  15. Return to the form and, in the Properties window, click the Properties button

Water Bill Edition

Records of a database have to be managed. While the primary operation of managing a record is to locate it, the next operation is to change it, or edit or update it. We will provide a form that can allow a user to take care of that.

Practical LearningPractical Learning: Creating a Water Bill Editor

  1. In the Solution Explorer, right-click the name of the project -> Add -> Form (Windows Forms...)
  2. Set the name of the form to WaterBillEditor
  3. Click Add
  4. Using the Properties window, change the size of the new form to match the size of the New Water Bill form
  5. Select everything in the New Water Bill form and copy it
  6. Paste it in Water Bill Editor form
  7. Change the design of the Water Bill Editor form as follows (you will add only the controls that are not found in the New Water Bill form):

    Stellar Water Point - Water Bill Editor

    Control (Name) Text Additional Properties
    Button Button btnFindWaterBill &Find  
    Button Button btnUpdateWaterBill &Update Water Bill  
  8. Double-click Find button on the form to generate its Click event
  9. Return to the form and click the Counter Reading End text box
  10. In the Properties window, click the Events button Events
  11. In the Events section of the Properties window, double-click the Leave field
  12. Return to the form and double-click the Update Water Bill button to launch its Click event
  13. Return to the form and double-click the Close button
  14. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;

namespace StellarWaterPoint1
{
    public partial class WaterBillEditor : Form
    {
        public WaterBillEditor()
        {
            InitializeComponent();
        }

        private void btnFindWaterBill_Click(object sender, EventArgs e)
        {
            if(string.IsNullOrEmpty(txtInvoiceNumber.Text))
            {
                MessageBox.Show("Please type an invoice number so the system can attempt to find it.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            string meterNumber   = string.Empty;

            using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
            {
                SqlCommand cmdWaterBills = new SqlCommand("LocateBill", scStellarWaterPoint);

                cmdWaterBills.CommandType = CommandType.StoredProcedure;

                SqlParameter spWaterBill = new SqlParameter();
                spWaterBill.ParameterName = "@invNbr";
                spWaterBill.DbType = DbType.String;
                spWaterBill.Value = txtInvoiceNumber.Text;
                spWaterBill.Direction = ParameterDirection.Input;
                cmdWaterBills.Parameters.Add(spWaterBill);

                scStellarWaterPoint.Open();
                cmdWaterBills.ExecuteNonQuery();

                SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterBills);

                DataSet dsWaterBills = new DataSet("WaterBillsSet");

                sdaWaterMeters.Fill(dsWaterBills);

                if (dsWaterBills.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drBill in dsWaterBills.Tables[0].Rows)
                    {
                        txtAccountNumber.Text = drBill[1].ToString()!;
                        dtpMeterReadingStartDate.Value = DateTime.Parse(drBill[2].ToString()!);
                        dtpMeterReadingEndDate.Value = DateTime.Parse(drBill[3].ToString()!);
                        txtBillingDays.Text = drBill[4].ToString();
                        txtCounterReadingStart.Text = drBill[5].ToString();
                        txtCounterReadingEnd.Text = drBill[6].ToString();
                        txtConsumption.Text = drBill[7].ToString();
                        txtTotalGallons.Text = drBill[8].ToString();
                        txtFirstTierConsumption.Text = drBill[9].ToString()!;
                        txtSecondTierConsumption.Text = drBill[10].ToString()!;
                        txtLastTierConsumption.Text = drBill[11].ToString()!;
                        txtWaterCharges.Text = drBill[12].ToString()!;
                        txtSewerCharges.Text = drBill[13].ToString()!;
                        txtEnvironmentCharges.Text = drBill[14].ToString()!;
                        txtTotalCharges.Text = drBill[15].ToString()!;
                        txtLocalTaxes.Text = drBill[16].ToString()!;
                        txtStateTaxes.Text = drBill[17].ToString()!;
                        dtpPaymentDueDate.Value = DateTime.Parse(drBill[18].ToString()!);
                        txtAmountDue.Text = drBill[19].ToString()!;
                        dtpLatePaymentDueDate.Value = DateTime.Parse(drBill[20].ToString()!);
                        txtLateAmountDue.Text = drBill[21].ToString()!;
                    }
                }
                else
                {
                    MessageBox.Show("There is no water bill with that number.",
                                    "Stellar Water Point",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                    txtAccountNumber.Text = string.Empty;
                    txtCustomerName.Text = string.Empty;
                    txtAddress.Text = string.Empty;
                    txtCity.Text = string.Empty;
                    txtCounty.Text = string.Empty;
                    txtState.Text = string.Empty;
                    txtZIPCode.Text = string.Empty;
                    txtMeterDetails.Text = string.Empty;

                    dtpMeterReadingStartDate.Value = DateTime.Now;
                    dtpMeterReadingEndDate.Value = DateTime.Now;
                    txtBillingDays.Text = string.Empty;
                    txtCounterReadingStart.Text = string.Empty;
                    txtCounterReadingEnd.Text = string.Empty;
                    txtConsumption.Text = string.Empty;
                    txtTotalGallons.Text = string.Empty;
                    txtFirstTierConsumption.Text = string.Empty;
                    txtSecondTierConsumption.Text = string.Empty;
                    txtLastTierConsumption.Text = string.Empty;
                    txtWaterCharges.Text = string.Empty;
                    txtSewerCharges.Text = string.Empty;
                    txtEnvironmentCharges.Text = string.Empty;
                    txtTotalCharges.Text = string.Empty;
                    txtLocalTaxes.Text = string.Empty;
                    txtStateTaxes.Text = string.Empty;
                    dtpPaymentDueDate.Value = DateTime.Now;
                    txtAmountDue.Text = string.Empty;
                    dtpLatePaymentDueDate.Value = DateTime.Now;
                    txtLateAmountDue.Text = string.Empty;

                    return;
                }
            }

            using (SqlConnection scStellarWaterPoint =
                new SqlConnection("Data Source=(local);" +
                                  "Database=StellarWaterPoint;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomers = new SqlCommand("IdentifyClient", scStellarWaterPoint);

                cmdCustomers.CommandType = CommandType.StoredProcedure;

                SqlParameter spCustomer = new SqlParameter();
                spCustomer.ParameterName = "@AcntNbr";
                spCustomer.DbType = DbType.String;
                spCustomer.Value = txtAccountNumber.Text;
                spCustomer.Direction = ParameterDirection.Input;
                cmdCustomers.Parameters.Add(spCustomer);

                scStellarWaterPoint.Open();
                cmdCustomers.ExecuteNonQuery();

                SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);

                DataSet dsCustomers = new DataSet("CustomersSet");

                sdaCustomers.Fill(dsCustomers);

                if (dsCustomers.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drClient in dsCustomers.Tables[0].Rows)
                    {
                        meterNumber = drClient[1].ToString()!;
                        txtCustomerName.Text = string.Format("{0} {1}", drClient[2].ToString(), drClient[3].ToString());
                        txtAddress.Text = drClient[4].ToString();
                        txtCity.Text = drClient[5].ToString();
                        txtCounty.Text = drClient[6].ToString();
                        txtState.Text = drClient[7].ToString();
                        txtZIPCode.Text = drClient[8].ToString();
                    }
                }
            }

            if (!string.IsNullOrEmpty(meterNumber))
            {
                using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("GetWaterMonitor", scStellarWaterPoint);

                    cmdWaterMeters.CommandType = CommandType.StoredProcedure;

                    SqlParameter spWaterMeter = new SqlParameter();
                    spWaterMeter.ParameterName = "@MtrNbr";
                    spWaterMeter.DbType = DbType.String;
                    spWaterMeter.Value = meterNumber;
                    spWaterMeter.Direction = ParameterDirection.Input;
                    cmdWaterMeters.Parameters.Add(spWaterMeter);

                    scStellarWaterPoint.Open();
                    cmdWaterMeters.ExecuteNonQuery();

                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);

                    DataSet dsWaterMeters = new DataSet("WaterMetersSet");

                    sdaWaterMeters.Fill(dsWaterMeters);

                    foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows)
                    {
                        txtMeterDetails.Text = drWaterMeter[0].ToString() + " - " +
                                               drWaterMeter[1].ToString() + " " +
                                               drWaterMeter[2].ToString() + " (Size: " +
                                               drWaterMeter[3].ToString() + ")";
                    }
                }
            }
        }

        private void txtCounterReadingEnd_Leave(object sender, EventArgs e)
        {
            double counterStart = 0, counterEnd = 0;

            try
            {
                counterStart = double.Parse(txtCounterReadingStart.Text);
            }
            catch (FormatException feCRStart)
            {
                MessageBox.Show("There was a problem with the value of the " +
                                "Counter Reading Start. The error produced is: " + feCRStart.Message,
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            try
            {
                counterEnd = double.Parse(txtCounterReadingEnd.Text);
            }
            catch (FormatException feCREnd)
            {
                MessageBox.Show("There was a problem with the value of the " +
                                "Counter Reading End. The error produced is: " + feCREnd.Message,
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            double consumption = counterEnd - counterStart;
            double gallons = consumption * 748.05;
            double firstTier = gallons * (48.00 / 10000.00);
            double secondTier = gallons * (32.00 / 10000.00);
            double lastTier = gallons * (20.00 / 10000.00);
            double waterCharges = firstTier + secondTier + lastTier;
            double sewerCharges = waterCharges * 28.65 / 100;
            double envCharges = waterCharges * 0.22184;
            double totalCharges = waterCharges + sewerCharges + envCharges;
            double localTaxes = totalCharges * 0.06148;
            double stateTaxes = totalCharges * 0.01374;
            double amtDue = totalCharges + localTaxes + stateTaxes;

            txtConsumption.Text           = consumption.ToString();
            txtTotalGallons.Text          = gallons.ToString("F");
            txtFirstTierConsumption.Text  = firstTier.ToString("F");
            txtSecondTierConsumption.Text = secondTier.ToString("F");
            txtLastTierConsumption.Text   = lastTier.ToString("F");
            txtWaterCharges.Text          = waterCharges.ToString("F");
            txtSewerCharges.Text          = sewerCharges.ToString("F");
            txtEnvironmentCharges.Text    = envCharges.ToString("F");
            txtTotalCharges.Text          = totalCharges.ToString("F");
            txtLocalTaxes.Text            = localTaxes.ToString("F");
            txtStateTaxes.Text            = stateTaxes.ToString("F");
            dtpPaymentDueDate.Value       = dtpMeterReadingEndDate.Value + new TimeSpan(15, 0, 0, 0);
            txtAmountDue.Text             = amtDue.ToString("F");
            dtpLatePaymentDueDate.Value   = dtpMeterReadingEndDate.Value + new TimeSpan(30, 0, 0, 0);
            txtLateAmountDue.Text         = (amtDue + 8.95).ToString("F");
        }

        private void btnUpdateWaterBill_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtInvoiceNumber.Text))
            {
                MessageBox.Show("You must enter an invoice number. Otherwise, the record cannot be saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (string.IsNullOrEmpty(txtAccountNumber.Text))
            {
                MessageBox.Show("You must enter the account number of the customer whose bill is being prepared. " +
                                "Otherwise, the record cannot be saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
            {
                SqlCommand cmdWaterBills = new SqlCommand("UPDATE WaterBills SET AccountNumber         = N'" + txtAccountNumber.Text         + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET BillingDays           =   " + txtBillingDays.Text           + "  WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET CounterReadingStart   = N'" + txtCounterReadingStart.Text   + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET CounterReadingEnd     = N'" + txtCounterReadingEnd.Text     + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET Consumption           = N'" + txtConsumption.Text           + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET TotalGallons          = N'" + txtTotalGallons.Text          + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET FirstTierConsumption  = N'" + txtFirstTierConsumption.Text  + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET SecondTierConsumption = N'" + txtSecondTierConsumption.Text + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET LastTierConsumption   = N'" + txtLastTierConsumption.Text   + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET WaterCharges          = N'" + txtWaterCharges.Text          + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET SewerCharges          = N'" + txtSewerCharges.Text          + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET EnvironmentCharges    = N'" + txtEnvironmentCharges.Text    + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET TotalCharges          = N'" + txtTotalCharges.Text          + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET LocalTaxes            = N'" + txtLocalTaxes.Text            + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET StateTaxes            = N'" + txtStateTaxes.Text            + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET AmountDue             = N'" + txtAmountDue.Text             + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET LateAmountDue         = N'" + txtLateAmountDue.Text         + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET MeterReadingStartDate = N'" + dtpMeterReadingStartDate.Value.ToShortDateString() + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET MeterReadingEndDate   = N'" + dtpMeterReadingEndDate.Value.ToShortDateString()   + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET PaymentDueDate        = N'" + dtpPaymentDueDate.Value.ToShortDateString()        + "' WHERE InvoiceNumber = " + txtInvoiceNumber.Text + ";" +
                                                          "UPDATE WaterBills SET LatePaymentDueDate    = N'" + dtpLatePaymentDueDate.Value.ToShortDateString()    + "' WHERE InvoiceNumber = " + txtInvoiceNumber.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();
        }
    }
}

Water Bill Deletion

Sometimes, a table may have a useless record, in which case it must be deleted. We will create a form that can let an employee locate and delete a record.

Practical LearningPractical Learning: Editing/Updating a Record

  1. To create a form, on the main menu of Microsoft Visual Studio, click Project and click Add Form (Windows Forms...)
  2. Change the file Name to WaterBillDelete
    3. Click Add Resize the form to have the same size as the Water Bill Editor form
  3. Select and copy everything in the Water Bill Editor form
  4. Paste it in the new Water Bill Delete form
  5. Change the design of the form as follows (you will change only one button):

    Stellar Water Point - Water Bill Deletion

    Control (Name) Text
    Button Button btnDeleteWaterBill &Delete Water Bill
  6. Double-click an unoccupied area of the form to generate its Load event
  7. Return to the form and double-click the Find button
  8. Return to the form and double-click the &Select Picture... button
  9. Return to the form and double-click the Up&date Auto-Part button
  10. Return to the form and double-click the Close button
  11. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;

namespace StellarWaterPoint1
{
    public partial class WaterBillDelete : Form
    {
        public WaterBillDelete()
        {
            InitializeComponent();
        }

        private void btnFindWaterBill_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtInvoiceNumber.Text))
            {
                MessageBox.Show("Please type an invoice number so the system can attempt to find it.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            string meterNumber = string.Empty;

            using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
            {
                SqlCommand cmdWaterBills = new SqlCommand("LocateBill", scStellarWaterPoint);

                cmdWaterBills.CommandType = CommandType.StoredProcedure;

                SqlParameter spWaterBill = new SqlParameter();
                spWaterBill.ParameterName = "@invNbr";
                spWaterBill.DbType = DbType.String;
                spWaterBill.Value = txtInvoiceNumber.Text;
                spWaterBill.Direction = ParameterDirection.Input;
                cmdWaterBills.Parameters.Add(spWaterBill);

                scStellarWaterPoint.Open();
                cmdWaterBills.ExecuteNonQuery();

                SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterBills);

                DataSet dsWaterBills = new DataSet("WaterBillsSet");

                sdaWaterMeters.Fill(dsWaterBills);

                if (dsWaterBills.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drBill in dsWaterBills.Tables[0].Rows)
                    {
                        txtAccountNumber.Text = drBill[1].ToString()!;
                        dtpMeterReadingStartDate.Value = DateTime.Parse(drBill[2].ToString()!);
                        dtpMeterReadingEndDate.Value = DateTime.Parse(drBill[3].ToString()!);
                        txtBillingDays.Text = drBill[4].ToString();
                        txtCounterReadingStart.Text = drBill[5].ToString();
                        txtCounterReadingEnd.Text = drBill[6].ToString();
                        txtConsumption.Text = drBill[7].ToString();
                        txtTotalGallons.Text = drBill[8].ToString();
                        txtFirstTierConsumption.Text = drBill[9].ToString()!;
                        txtSecondTierConsumption.Text = drBill[10].ToString()!;
                        txtLastTierConsumption.Text = drBill[11].ToString()!;
                        txtWaterCharges.Text = drBill[12].ToString()!;
                        txtSewerCharges.Text = drBill[13].ToString()!;
                        txtEnvironmentCharges.Text = drBill[14].ToString()!;
                        txtTotalCharges.Text = drBill[15].ToString()!;
                        txtLocalTaxes.Text = drBill[16].ToString()!;
                        txtStateTaxes.Text = drBill[17].ToString()!;
                        dtpPaymentDueDate.Value = DateTime.Parse(drBill[18].ToString()!);
                        txtAmountDue.Text = drBill[19].ToString()!;
                        dtpLatePaymentDueDate.Value = DateTime.Parse(drBill[20].ToString()!);
                        txtLateAmountDue.Text = drBill[21].ToString()!;
                    }
                }
                else
                {
                    MessageBox.Show("There is no water bill with that number.",
                                    "Stellar Water Point",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                    txtAccountNumber.Text = string.Empty;
                    txtCustomerName.Text = string.Empty;
                    txtAddress.Text = string.Empty;
                    txtCity.Text = string.Empty;
                    txtCounty.Text = string.Empty;
                    txtState.Text = string.Empty;
                    txtZIPCode.Text = string.Empty;
                    txtMeterDetails.Text = string.Empty;

                    dtpMeterReadingStartDate.Value = DateTime.Now;
                    dtpMeterReadingEndDate.Value = DateTime.Now;
                    txtBillingDays.Text = string.Empty;
                    txtCounterReadingStart.Text = string.Empty;
                    txtCounterReadingEnd.Text = string.Empty;
                    txtConsumption.Text = string.Empty;
                    txtTotalGallons.Text = string.Empty;
                    txtFirstTierConsumption.Text = string.Empty;
                    txtSecondTierConsumption.Text = string.Empty;
                    txtLastTierConsumption.Text = string.Empty;
                    txtWaterCharges.Text = string.Empty;
                    txtSewerCharges.Text = string.Empty;
                    txtEnvironmentCharges.Text = string.Empty;
                    txtTotalCharges.Text = string.Empty;
                    txtLocalTaxes.Text = string.Empty;
                    txtStateTaxes.Text = string.Empty;
                    dtpPaymentDueDate.Value = DateTime.Now;
                    txtAmountDue.Text = string.Empty;
                    dtpLatePaymentDueDate.Value = DateTime.Now;
                    txtLateAmountDue.Text = string.Empty;

                    return;
                }
            }

            using (SqlConnection scStellarWaterPoint =
                new SqlConnection("Data Source=(local);" +
                                  "Database=StellarWaterPoint;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomers = new SqlCommand("IdentifyClient", scStellarWaterPoint);

                cmdCustomers.CommandType = CommandType.StoredProcedure;

                SqlParameter spCustomer = new SqlParameter();
                spCustomer.ParameterName = "@AcntNbr";
                spCustomer.DbType = DbType.String;
                spCustomer.Value = txtAccountNumber.Text;
                spCustomer.Direction = ParameterDirection.Input;
                cmdCustomers.Parameters.Add(spCustomer);

                scStellarWaterPoint.Open();
                cmdCustomers.ExecuteNonQuery();

                SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);

                DataSet dsCustomers = new DataSet("CustomersSet");

                sdaCustomers.Fill(dsCustomers);

                if (dsCustomers.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drClient in dsCustomers.Tables[0].Rows)
                    {
                        meterNumber = drClient[1].ToString()!;
                        txtCustomerName.Text = string.Format("{0} {1}", drClient[2].ToString(), drClient[3].ToString());
                        txtAddress.Text = drClient[4].ToString();
                        txtCity.Text = drClient[5].ToString();
                        txtCounty.Text = drClient[6].ToString();
                        txtState.Text = drClient[7].ToString();
                        txtZIPCode.Text = drClient[8].ToString();
                    }
                }
            }

            if (!string.IsNullOrEmpty(meterNumber))
            {
                using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdWaterMeters = new SqlCommand("GetWaterMonitor", scStellarWaterPoint);

                    cmdWaterMeters.CommandType = CommandType.StoredProcedure;

                    SqlParameter spWaterMeter = new SqlParameter();
                    spWaterMeter.ParameterName = "@MtrNbr";
                    spWaterMeter.DbType = DbType.String;
                    spWaterMeter.Value = meterNumber;
                    spWaterMeter.Direction = ParameterDirection.Input;
                    cmdWaterMeters.Parameters.Add(spWaterMeter);

                    scStellarWaterPoint.Open();
                    cmdWaterMeters.ExecuteNonQuery();

                    SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);

                    DataSet dsWaterMeters = new DataSet("WaterMetersSet");

                    sdaWaterMeters.Fill(dsWaterMeters);

                    foreach (DataRow drWaterMeter in dsWaterMeters.Tables[0].Rows)
                    {
                        txtMeterDetails.Text = drWaterMeter[0].ToString() + " - " +
                                               drWaterMeter[1].ToString() + " " +
                                               drWaterMeter[2].ToString() + " (Size: " +
                                               drWaterMeter[3].ToString() + ")";
                    }
                }
            }
        }

        private void btnDeleteWaterBill_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtInvoiceNumber.Text))
            {
                MessageBox.Show("You must enter an invoice number. Otherwise, the record cannot be saved.",
                                "Stellar Water Point", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            using (SqlConnection scStellarWaterPoint =
                            new SqlConnection("Data Source=(local);" +
                                              "Database=StellarWaterPoint;" +
                                              "Integrated Security=SSPI;"))
            {
                SqlCommand cmdWaterBills = new SqlCommand("DELETE WaterBills WHERE InvoiceNumber = " + txtInvoiceNumber.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();
        }
    }
}

A Starting Form

An application should have a point that leads to the other objects. Fortunately, when you create a Windows Forms application, it creates a default starting form. We will simply change the design of that form to suit our needs.

Practical LearningPractical Learning: Finalizing the Application

  1. In the Solution Explorer, right-click Form1.cs and click Rename
  2. Type StellarWaterPoint (to have StellarWaterPoint.cs) and press Enter three times to accept the name and display the form
  3. Design the form as follows:

    Stellar Water Point - Central Form

    Control (Name) Text
    Button Button btnCreateWaterBill Create Water Bill...
    Button Button btnUpdateWaterBill Update Water Bill...
    Button Button btnDeleteWaterBill Delete Water Bill...
    Button Button btnCustomers Customers Accounts...
    Button Button btnWaterMeters Water Meters...
    Button Button btnClose Close
  4. On the form, double-click the Create Water Bill button to generate its Click event
  5. Return to the form and double-click the Update Water Bill button to generate its Click event
  6. Return to the form and double-click the Delete Water Bill button to generate its Click event
  7. Return to the form and double-click the Customers Account button to generate its Click event
  8. Return to the form and double-click the Water Meters button to generate its Click event
  9. Return to the form and double-click the Close button
  10. Change the document as follows: 
    namespace StellarWaterPoint1
{
    public partial class StellarWaterPoint : Form
    {
        public StellarWaterPoint()
        {
            InitializeComponent();
        }

        private void btnCreateWaterBill_Click(object sender, EventArgs e)
        {
            WaterBillNew wbn = new WaterBillNew();

            wbn.ShowDialog(this);
        }

        private void btnUpdateWaterBill_Click(object sender, EventArgs e)
        {
            WaterBillEditor wbe = new();

            wbe.ShowDialog(this);
        }

        private void btnDeleteWaterBill_Click(object sender, EventArgs e)
        {
            WaterBillDelete wbd = new WaterBillDelete();
            wbd.ShowDialog(this);
        }

        private void btnCustomers_Click(object sender, EventArgs e)
        {
            Customers clients = new Customers();
            
            clients.ShowDialog();
        }

        private void btnWaterMeters_Click(object sender, EventArgs e)
        {
            WaterMeters wms = new WaterMeters();

            wms.ShowDialog();
        }

        private void btnSaveWaterBill_Click(object sender, EventArgs e)
        {
            
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Executing and Testing the Application

After creating and application, you can execute it. You can then test it with sample values.

Practical LearningPractical Learning: Executing and Testing the Application

  1. To execute the application, on the main menu, click Debug -> Start Without Debugging:

  2. Click the Water Meters button

    Stellar Water Point - Water Meters

  3. Close the Water Meterrs form
  4. Click the New Make button
  5. Click the Customers button:

    Stellar Water Point - Customers

  6. Close the Customers form
  7. Click the Create Water Bill button:

    Stellar Water Point - New Water Bill

  8. Enter the following values: 
    Invoice #:	           468550   
Account #:	              7518-302-6895
Meter Reading Start Date: 10/5/2022
Meter Reading End Date:   1/9/2023
Counter Reading Start:    96
Counter Reading End:      114
  9. Click Save Water Bill
  10. Click other water bills with the following values:
    Invoice # Account # Meter Reading Start Date Meter Reading End Date Counter Reading Start Counter Reading End
    682416 4820-375-2842 10/18/2022 1/20/2023 109992 109998
    306646 2038-413-9680 10/24/2022 1/27/2023 137926 137975
    614081 9279-570-8394 11/14/2022 2/15/2023 6268 6275
    468206 7518-302-6895 1/9/2023 4/8/2023 114 118
    242974 2038-413-9680 1/27/2023 4/27/2023 137975 138012
    656117 4820-375-2842 1/20/2023 4/24/2023 109998 110052
    764183 9279-570-8394 2/15/2023 5/13/2023 6275 6295
    252018 7518-302-6895 4/8/2023 7/11/2023 118 126
    503888 4820-375-2842 4/24/2023 7/18/2023 110052 110102
    548358 2038-413-9680 4/27/2023 7/28/2023 138012 138054
    834278 5938-074-5293 5/5/2023 8/5/2023 49 52
    567507 9279-570-8394 5/13/2023 8/12/2023 6295 6312
  11. Close the forms and return to your programming environment
  12. Close your programming environment
CREATE PROCEDURE LocateBill @InvNbr nvarchar(15)
AS
    BEGIN
        SELECT InvoiceNumber,
               AccountNumber,
               MeterReadingStartDate,
               MeterReadingEndDate,
               BillingDays,
               CounterReadingStart,
               CounterReadingEnd,
               Consumption,
               TotalGallons,
               FirstTierConsumption,
               SecondTierConsumption,
               LastTierConsumption,
               WaterCharges,
               SewerCharges,
               EnvironmentCharges,
               TotalCharges,
               LocalTaxes,
               StateTaxes,
               PaymentDueDate,
               AmountDue,
               LatePaymentDueDate,
               LateAmountDue
        FROM   WaterBills
        WHERE  InvoiceNumber = @InvNbr
    END;
GO

CREATE PROCEDURE GetWaterMonitor @MtrNbr nvarchar(15)
AS
    BEGIN
        SELECT MeterNumber,
               Make,
               Model,
               MeterSize
        FROM   WaterMeters
        WHERE  MeterNumber = @MtrNbr
    END;
GO

CREATE PROCEDURE IdentifyClient @AcntNbr nvarchar(15)
AS
    BEGIN
        SELECT AccountNumber,
               MeterNumber,
               FirstName,
               LastName,
               [Address],
               City,
               County,
               [State],
               ZIPCode
        FROM Customers
        WHERE AccountNumber = @AcntNbr
    END;
GO
-------------------------------------------------------------------------------
INSERT INTO WaterMeters(MeterNumber, Make, Model, MeterSize)
VALUES(N'392-44-572', N'Constance Technologies', N'TG-4822', N'5/8 Inches'),
	  (N'938-75-869', N'Stanford Trend',         N'266G',    N'1 1/2 Inches'),
	  (N'799-28-461', N'Constance Technologies', N'BD-7000', N'3/4 Inches'),
	  (N'207-94-835', N'Constance Technologies', N'TG-6220', N'5/8 Inches'),
	  (N'592-84-957', N'Standard Trend',         N'428T',    N'3/4 Inches'),
	  (N'374-06-284', N'Raynes Energica',        N'i2022',   N'3/4 Inches');
GO

INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
VALUES(N'9279-570-8394', N'799-28-461', N'Thomas', N'Stones',  N'10252 Broward Ave #D4', N'Frederick', N'Frederick', N'MD', N'21703');
GO

INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, [State], ZIPCode)
VALUES(N'4820-375-2842', N'392-44-572', N'Akhil', N'Koumari', N'748 Red Hills Rd', N'Roanoke', N'VA', N'24012');
GO

INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
VALUES(N'7518-302-6895', N'207-94-835', N'Grace', N'Brenner', N'4299 Peachtree Court', N'Rockville', N'Montgomery', N'MD', N'20853');
GO

INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, [State], ZIPCode)
VALUES(N'2038-413-9680', N'938-75-869', N'Amidou', N'Gomah', N'2075 Rose Hills Ave', N'Washington', N'DC', N'20004');
GO

INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
VALUES(N'5938-074-5293', N'592-84-957', N'Marie', N'Rath', N'582G Dunhill Ave', N'Lanham', N'Prince Georges', N'MD', N'20706');
GO
  • Close the Query window
  • When asked whether you want to save, click Don't Save
    		•
    Home Copyright © 2003-2023, FunctionX Saturday 01 April 2023