Microsoft SQL Server - Example Application: Lambda Square Apartments

	Example Graphical Database Application: Lambda Square Apartments

Application Fundamentals

Introduction

Lambda Square is a company that owns an apartment buildiing and rents its apartments to customers who must pay rent every month.

We are going to create a Microsoft SQL Server database to manage the rent operations of the business. We will also create a graphical application that employees can use to interact with the database.

In this exercise, we are working in a computer network. Microsoft SQL Server (2012) is installed in a Microsoft Wiindows Server 2008 where the computer (the server) is named EXPRESSION. The application is developed in a client computer (named CENTRAL) that runs Microsoft Windows 7 Enterprise.

Practical Learning: Creating the Database

Start Microsoft SQL Server and login

Start a new Query window and type the following code:

USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'LamdaSquare1'
)
DROP DATABASE LamdaSquare1;
GO

CREATE DATABASE LamdaSquare1;
GO

USE LamdaSquare1;
GO

CREATE SCHEMA Presentation;
GO
CREATE SCHEMA Rentals;
GO
CREATE SCHEMA Personnel;
GO

To execute the code to create the database, right-click inside the Query window and click Execute
Start Microsoft Visual Studio
To start a new project, on the main menu, click File -> New Project...
Select your language. We will use Visual C#
Make sure Windows Forms Application and set the name to LambdaSquare1
Click OK
In the Solution Explorer, right-click Form1.cs and click Rename
Type Central.cs and press Enter
Return to Microsoft SQL Server

The Company's Employees

Employees are clerks who process all types of transactions. They create the records for apartments. They register new tenants. They process payments for tenants.

With as little information as possible, we will create a table for the employees.

Practical Learning Practical Learning: Creating Employees

Replace the contents of the Query window with the following:

CREATE TABLE Personnel.Employees
(
	EmployeeNumber nvarchar(10) not null unique,
	FirstName nvarchar(24),
	LastName nvarchar(24),
	EmployeeName AS LastName + N', ' + FirstName,
	Title nvarchar(50),
	Notes nvarchar(max),
	Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO

Execute
Return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form
Make sure Windows Form is selected and set the name to Employee
Click Add

Design the form as follows:

Lambda Square - Employee

Control	(Name)	Text	Other Properties
Label		Employee #
TextBox	txtEmployeeNumber
Label		First Name:
TextBox	txtFirstName
Label		Last Name:
TextBox	txtLastName
Label		Title:
TextBox	txtTitle
Label		Notes:
TextBox	txtNotes		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

On the main menu, click Project -> Add Windows Form...
Set the Name to Employees
Click Add

Design the form as follows:

Control

(Name)

Text

Anchor

ListView

lvwEmployees

Top, Bottom, Left, Right

Columns

(Name)	Text	TextAlign	Width
colEmployeeNumber	Empl #
colFirstName	First Name		70
colLastName	Last Name		70
colEmployeeName	Employee Name		120
COLTitle	Title		145

Button

btnNewEmployee

New Employee...

Bottom, Right

Button

btnClose

Bottom, Right

Double-click an unoccupied area of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Employees : Form
    {
        public Employees()
        {
            InitializeComponent();
        }

        private void ShowEmployees()
        {
            lvwEmployees.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                            new SqlCommand("SELECT * FROM Personnel.Employees; ",
                                           cntLambdaSquare);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("Employees");

                cntLambdaSquare.Open();

                sdaEmployees.SelectCommand = cmdEmployees;
                sdaEmployees.Fill(dsEmployees);

                foreach (DataRow drEmployee in dsEmployees.Tables[0].Rows)
                {
                    ListViewItem lviEmployee = 
                    	new ListViewItem(drEmployee["EmployeeNumber"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["FirstName"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["LastName"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["EmployeeName"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["Title"].ToString());
                    lvwEmployees.Items.Add(lviEmployee);
                }
            }
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            ShowEmployees();
        }    
    }
}

Return to the form and click the list view
On the Properties window, click the Events button
In the Events section of the Properties window, double-click DoubleClick

Implement the event as follows:

private void lvwEmployees_DoubleClick(object sender, EventArgs e)
{
    // These values will help us compare the current and the new values
    string strTitle = "";
    string strNotes = "";
    string strLastName = "";
    string strFirstName = "";
    // Get a reference to the Employees dialog box
    Employee empl = new Employee();
    // Get the employee number based on the record double-clicked
    ListViewItem lviSelected = lvwEmployees.SelectedItems[0];

    // Open a connection to the database
    using (SqlConnection cntLambdaSquare =
                   new SqlConnection("Data Source='EXPRESSION';" +
                                     "Database=LambdaSquare1;" +
                                     "Integrated Security=SSPI;"))
    {
        // Select the records from the Employees table
        SqlCommand cmdEmployees = 
        	new SqlCommand("SELECT ALL * FROM Personnel.Employees " +
                               "WHERE EmployeeNumber = '" + lviSelected.Text + "';",
                               cntLambdaSquare);
        SqlDataAdapter sdaEmployees = new SqlDataAdapter();
        DataSet dsEmployees = new DataSet("Employees");

        cntLambdaSquare.Open();

        sdaEmployees.SelectCommand = cmdEmployees;
        sdaEmployees.Fill(dsEmployees);

        foreach (DataRow drEmployee in dsEmployees.Tables[0].Rows)
        {
            // Initialize each of our variables with the 
            // corresponding value from the Employees table
            strFirstName = drEmployee["FirstName"].ToString();
            strLastName = drEmployee["LastName"].ToString();
            strTitle = drEmployee["Title"].ToString();
            strNotes = drEmployee["Notes"].ToString();

            // Pass the values from the Employees table to the Employees dialog box
            empl.txtEmployeeNumber.Text = lviSelected.Text;
            empl.txtEmployeeNumber.Enabled = false;
            empl.txtFirstName.Text = drEmployee["FirstName"].ToString();
            empl.txtLastName.Text = drEmployee["LastName"].ToString();
            empl.txtTitle.Text = drEmployee["Title"].ToString();
            empl.txtNotes.Text = drEmployee["Notes"].ToString();
        }

        // Change the caption of the dialog box to indicate a possible update of records
        empl.Text = "Lambda Square - Update Employee Record";
        // Change the caption of the OK button
        empl.btnOK.Text = "Update";
    }

    // Display the dialog box
    if (empl.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        // Find out if a value was changed
        if ((strFirstName != empl.txtFirstName.Text) ||
            (strLastName != empl.txtLastName.Text) ||
            (strTitle != empl.txtTitle.Text) ||
            (strNotes != empl.txtNotes.Text))
        {
            // A value was changed. You must update the record.
            using (SqlConnection cntLambdaSquare =
                   new SqlConnection("Data Source='EXPRESSION';" +
                                     "Database=LambdaSquare1;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdLambdaSquare =
                    new SqlCommand("UPDATE Personnel.Employees " +
                                   "SET FirstName = '" + empl.txtFirstName.Text + "'," +
                                   "    LastName = '" + empl.txtLastName.Text + "'," +
                                   "    Title = '" + empl.txtTitle.Text + "'," +
                                   "    Notes = '" + empl.txtNotes.Text + "' " +
                                   "WHERE EmployeeNumber = '" + empl.txtEmployeeNumber.Text + "';",
                                   cntLambdaSquare);

                cntLambdaSquare.Open();
                cmdLambdaSquare.ExecuteNonQuery();

                MessageBox.Show("The employee's record has been updated.",
                                "Lambda Square - Employees",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            ShowEmployees();
        }
    }
}

Return to the form and double-click the New Employee button

Implement the event as follows:

private void btnNewEmployee_Click(object sender, EventArgs e)
{
    Employee empl = new Employee();

    if (empl.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(empl.txtEmployeeNumber.Text))
                {
                    MessageBox.Show("You must specify the employee number.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(empl.txtLastName.Text))
                {
                    MessageBox.Show("You must enter the employee's last name.",
                        "Lambda Square - Apartments",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='EXPRESSION';" +
                                         "Database=LambdaSquare1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdLambdaSquare = new SqlCommand("INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, Notes) " +
                                         "VALUES('" + empl.txtEmployeeNumber.Text + "', '" + empl.txtFirstName.Text + "', '" +
                                         empl.txtLastName.Text + "', '" + empl.txtTitle.Text + "', '" + empl.txtNotes.Text + "');",
                                         cntLambdaSquare);

                    cntLambdaSquare.Open();
                    cmdLambdaSquare.ExecuteNonQuery();

                    MessageBox.Show("A new employee has been hired.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowEmployees();
}

Return to the form and double-click the Close button

Implement the event as follows:

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

Return to Microsoft SQL Server

Introduction to Tenants

The Marital Status of a Tenant

Typically, a tenant is a person who rents an apartment. This person could live alone or with others. To assist us with information about a tenant, we will specify the marital status of a tenant. The possible options or values are single, married, divorced, etc.

Practical Learning Practical Learning: Creating The Marital Status

In the Query window of Microsoft SQL Server, replace the code with:

CREATE TABLE Rentals.MaritalsStatus
(
	MaritalStatus nvarchar(25) not null,
	StatusDescription nvarchar(max),
	Constraint PK_MaritalsStatus Primary Key(MaritalStatus)
);
GO

Execute and return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form...
Set the Name to MaritalsStatus
Click Add
Design the form approximately as follows:

Design the form as follows:

Control	(Name)	Text	Other Properties
Label		Marital Status
TextBox	txtMaritalStatus
Label		Description
TextBox	txtDescription		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

Return to Microsoft SQL Server

The Tenants

A tenant is a customer who rents an apartment. A tenant is a person with some characteristics. For our database, we will consider:

The Tenant Code: This is the equivalent of an account of a customer in any business. This code must be unique for each tenant
The First Name
The Last Name
The Tenant Name: This is combination of the first and last name expressed as Last Name, First Name
The Marital Status: To give employees the ability to add or delete a marital status, this information will be stored in a different table and linked to this field
The Number of Children
The Phone Number
The Email Address

Practical Learning Practical Learning: Creating Tenants

In Microsoft SQL Server, replace the code in the Query window with:

CREATE TABLE Rentals.Tenants
(
	TenantCode nvarchar(10) not null unique,
	FirstName nvarchar(24),
	LastName nvarchar(24),
	TenantName AS LastName + N', ' + FirstName,
	MaritalStatus nvarchar(25),
	NumberOfChildren tinyint,
	PhoneNumber nvarchar(20),
	EmailAddress nvarchar(40),
	Notes nvarchar(max),
	Constraint PK_Tenants Primary Key(TenantCode),
	Constraint FK_MaritalsStatus Foreign Key(MaritalStatus)
		References Rentals.MaritalsStatus(MaritalStatus)
);
GO

Execute
Return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form...
Set the Name to Tenant
Click Add

Design the form as follows:

Tenant

Control	(Name)	Text	Other Properties
Label		Tenant Code:
TextBox	txtTenantCode
Label		First Name:
TextBox	txtFirstName
Label		Last Name:
TextBox	txtLastName
Label		Marital Status
ComboBox	cbxMaritalStatus
Button	btnMaritalStatus	New...
Label		Phone Number:
TextBox	txtPhoneNumber
Label		Number of Children:
TextBox	txtNumberOfChildren
Label		Email Address:
TextBox	txtEmailAddress
Label		Notes:
TextBox	txtNotes		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

Double-click an unoccupied area of the dialog box

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Tenant : Form
    {
        public Tenant()
        {
            InitializeComponent();
        }

        private void GetMaritalStatus()
        {
            cbxMaritalStatus.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdLambdaSquare = 
                	new SqlCommand("SELECT * FROM Rentals.MaritalsStatus;",
                            cntLambdaSquare);
                cntLambdaSquare.Open();
                SqlDataReader rdr = cmdLambdaSquare.ExecuteReader();

                while (rdr.Read())
                    cbxMaritalStatus.Items.Add(rdr[0].ToString());

                rdr.Close();
            }
        }

        private void Tenant_Load(object sender, EventArgs e)
        {
            GetMaritalStatus();
        }
    }
}

Return to the form and double-click the New button

Implement the event as follows:

private void btnMaritalStatus_Click(object sender, EventArgs e)
{
    MaritalsStatus ms = new MaritalsStatus();

    if (ms.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(ms.txtMaritalStatus.Text))
                {
                    MessageBox.Show("You must enter a new marital status.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                string strMaritalStatus = ms.txtMaritalStatus.Text;

                if (cbxMaritalStatus.Items.Contains(strMaritalStatus))
                    MessageBox.Show(strMaritalStatus + " is already in the list.",
                                "Lambda Square - Apartments",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                else
                {
                    using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='EXPRESSION';" +
                                         "Database=LambdaSquare1;" +
                                         "Integrated Security=SSPI;"))
                    {
                        SqlCommand cmdLambdaSquare = new SqlCommand("INSERT INTO Rentals.MaritalsStatus(MaritalStatus, StatusDescription) " +
                                             "VALUES('" + ms.txtMaritalStatus.Text + "', '" + ms.txtDescription.Text + "');",
                                             cntLambdaSquare);

                        cntLambdaSquare.Open();
                        cmdLambdaSquare.ExecuteNonQuery();
                    }

                    GetMaritalStatus();
                }
    }
}

On the main menu, click Project -> Add Windows Form...
Set the Name to Tenants
Click Add

Design the form as follows:

Control

(Name)

Text

Anchor

ListView

lvwEmployees

Top, Bottom, Left, Right

Columns

(Name)	Text	TextAlign	Width
colTenantCode	Tenant #
colFirstName	First Name		70
colLastName	Last Name		70
colTenantName	Tenant Name		90
colMaritalStatus	Status
colNumberOfChildren	Children	Right	50
colPhoneNumber	Phone #	Center	100
colEmailAddress	Email Address		150

Button

btnNewEmployee

New Tenant...

Bottom, Right

Button

btnClose

Bottom, Right

Double-click an unoccupied area of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Tenants : Form
    {
        public Tenants()
        {
            InitializeComponent();
        }

        private void ShowTenants()
        {
            lvwTenants.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                            new SqlCommand("SELECT * FROM Rentals.Tenants;",
                                           cntLambdaSquare);
                SqlDataAdapter sdaTenants = new SqlDataAdapter();
                DataSet dsTenants = new DataSet("Employees");

                cntLambdaSquare.Open();

                sdaTenants.SelectCommand = cmdEmployees;
                sdaTenants.Fill(dsTenants);

                foreach (DataRow drEmployee in dsTenants.Tables[0].Rows)
                {
                    ListViewItem lviTenant =
                        new ListViewItem(drEmployee["TenantCode"].ToString());
                    lviTenant.SubItems.Add(drEmployee["FirstName"].ToString());
                    lviTenant.SubItems.Add(drEmployee["LastName"].ToString());
                    lviTenant.SubItems.Add(drEmployee["TenantName"].ToString());
                    lviTenant.SubItems.Add(drEmployee["MaritalStatus"].ToString());
                    lviTenant.SubItems.Add(drEmployee["NumberOfChildren"].ToString());
                    lviTenant.SubItems.Add(drEmployee["PhoneNumber"].ToString());
                    lviTenant.SubItems.Add(drEmployee["EmailAddress"].ToString());
                    lvwTenants.Items.Add(lviTenant);
                }
            }
        }

        private void Tenants_Load(object sender, EventArgs e)
        {
            ShowTenants();
        }
  
    }
}

Return to the form and double-click the New Tenant button

Implement the event as follows:

private void btnNewTenant_Click(object sender, EventArgs e)
{
    Tenant tnt = new Tenant();

    if (tnt.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(tnt.txtTenantCode.Text))
                {
                    MessageBox.Show("You must specify the account number of the tenant.",
                                    "Lambda Square - Tenants",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(tnt.txtLastName.Text))
                {
                    MessageBox.Show("You must enter the tenant's last name.",
                        "Lambda Square - Tenants",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='EXPRESSION';" +
                                         "Database=LambdaSquare1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdLambdaSquare =
                        new SqlCommand("INSERT INTO Rentals.Tenants(" +
                                       "TenantCode,	FirstName,	LastName, " +
                                       "MaritalStatus, 	NumberOfChildren, " +
                                       "PhoneNumber, EmailAddress, Notes) " +
                                       "VALUES('" + tnt.txtTenantCode.Text + "', '" + 
                                       tnt.txtFirstName.Text + "', '" + tnt.txtLastName.Text + "', '" +
                                       tnt.cbxMaritalStatus.Text + "', " + 
                                       int.Parse(tnt.txtNumberOfChildren.Text ) + ", '" + 
                                       tnt.txtPhoneNumber.Text + "', '" + tnt.txtEmailAddress.Text + "', '" + 
                                       tnt.txtNotes.Text + "');",
                                         cntLambdaSquare);

                    cntLambdaSquare.Open();
                    cmdLambdaSquare.ExecuteNonQuery();

                    MessageBox.Show("A new tenant has been registered.",
                                    "Lambda Square - Tenants",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowTenants();
}

Return to the form and double-click the Close button

Implement the event as follows:

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

Return to Microsoft SQL Server

The Apartment Units

The Occupancy Status

An apartment is our example is a house unit occupied by a tenant. To start, an apartment is available or not. A tenant can be assigned to only an apartment that is currently empty. On the other hand, an apartment can be not available for some reason. For example, an apartment may need repair or some appliance(s) is(are) missing.

Practical Learning Practical Learning: Creating the Occupancy Status

In Microsoft SQL Server, replace te text in the Query window with:

CREATE TABLE Rentals.OccupanciesStatus
(
	OccupancyStatus nvarchar(25) not null,
	StatusDescription nvarchar(max),
	Constraint PK_OccupanciesStatus Primary Key(OccupancyStatus)
);
GO

Execute and return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form...
Set the Name to OccupanciesStatus
Click Add

Design the form as follows:

Control	(Name)	Text	Other Properties
Label		Occupancy Status
TextBox	txtOccupancyStatus
Label		Description
TextBox	txtDescription		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

Return to Microsoft SQL Server

The Apartments

The apartments are the products that customers rent. Each apartment has basic characteristics such as:

The Number of Bedrooms: Some of our apartments have 1 bedroom. Some others have 2 or 3 bedrooms. The number of bedrooms will be represented as an integer
The Number of Bathrooms: Some apartments have 1 bathroom, which is the case for most one-bedroom apartments. A two-bedroom apartment can have one or two bathrooms. This depends on how the apartment was built
The Monthly Price: Obviously, tenants must pay rent every month. The price of an apartment varies on different factors such as the size of the apartment, the number of bedrooms, the number of bathrooms, etc. In the real world, the prices are set by categories. In our example, we will use different (types of) prices, just to make it fun
The Security Deposit: Our company requires that new tenants pay some type of deposit when they start occupying an apartment. The amount may depend on the number of bedrooms or the architecture of the apartment (in most cases, many apartments have the same layout). For our example, we will apply different amounts of security deposit
The Occupancy Status: We will use a field that specifies whether the apartment is available or not. Options may also include the fact that the apartment needs repair or some appliance(s) is(are) missing. Also, normally, when an apartment is ready to be rented, it gets the status of "Available". When the apartment is assigned to a tenant, its status should change to "Occupied" or "Rented" or something like that. We will not apply that functionality automatically because it requires (VBA) code. For our example, we will trust that an employee will change the status of an apartment when necessary. Because there can be different types of status, we will store this option in a different table

Practical Learning Practical Learning: Creating the Apartments

In the Query window of Microsoft SQL Server, replace the text with:

CREATE TABLE Presentation.Units
(
	UnitNumber nvarchar(10) not null unique,
	Bedrooms int,
	Bathrooms real,
	MonthlyRate money,
	SecurityDeposit money,
	OccupancyStatus nvarchar(25),
	Constraint PK_Units Primary Key(UnitNumber),
	Notes nvarchar(max),
	Constraint FK_OccupanciesStatus Foreign Key(OccupancyStatus)
		References Rentals.OccupanciesStatus(OccupancyStatus)
);
GO

Return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form...
Set the Name to Apartment
Click Add

Design the form as follows:

Lambda Square - Apartment

Control	(Name)	Text	Other Properties
Label		Unit #:
TextBox	txtUnitNumber
Label		Bedrooms:
TextBox	txtBedrooms		TextAlight: Right
Label		Bathrooms:
TextBox	txtBathrooms		TextAlight: Right
Label		Monthly Rate:
TextBox	txtMonthlyRate		TextAlight: Right
Label		Security Deposit:
TextBox	txtSecurityDeposit		TextAlight: Right
Label		Status:
ComboBox	cbxOccupanyStatusStatus
Button	btnOccupancyStatus	New...
Label		Notes:
TextBox	txtNotes		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

Double-click an unoccupied area of the dialog box

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Apartment : Form
    {
        public Apartment()
        {
            InitializeComponent();
        }

        private void GetOccupancyStatus()
        {
            cbxOccupancyStatus.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdLambdaSquare =
                    new SqlCommand("SELECT * FROM Rentals.OccupanciesStatus;",
                            cntLambdaSquare);
                cntLambdaSquare.Open();
                SqlDataReader rdr = cmdLambdaSquare.ExecuteReader();

                while (rdr.Read())
                    cbxOccupancyStatus.Items.Add(rdr[0].ToString());

                rdr.Close();
            }
        }

        private void Apartment_Load(object sender, EventArgs e)
        {
            GetOccupancyStatus();
        }
    }
}

Return to the form and double-click the New button

Implement the event as follows:

private void btnOccupancyStatus_Click(object sender, EventArgs e)
{
    OccupanciesStatus os = new OccupanciesStatus();

    if (os.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(os.txtOccupancyStatus.Text))
                {
                    MessageBox.Show("You must enter the occupancy status of the apartment.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                string strOccupancyStatus = os.txtOccupancyStatus.Text;

                if (cbxOccupancyStatus.Items.Contains(strOccupancyStatus))
                    MessageBox.Show(strOccupancyStatus + " is already in the list.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                else
                {
                    using (SqlConnection cntLambdaSquare =
                           new SqlConnection("Data Source='EXPRESSION';" +
                                                 "Database=LambdaSquare1;" +
                                                 "Integrated Security=SSPI;"))
                    {
                        SqlCommand cmdLambdaSquare =
                            new SqlCommand("INSERT INTO Rentals.OccupanciesStatus(OccupancyStatus, StatusDescription) " +
                                               "VALUES('" + os.txtOccupancyStatus.Text + "', '" + os.txtDescription.Text + "');",
                                               cntLambdaSquare);

                        cntLambdaSquare.Open();
                        cmdLambdaSquare.ExecuteNonQuery();
                    }

                    GetOccupancyStatus();
                }
     }  
}

On the main menu, click Project -> Add Windows Form...
Set the name to Apartments
Click Add

Design the form as follows:

Control

(Name)

Text

Anchor

ListView

lvwApartments

Top, Bottom, Left, Right

Columns

(Name)	Text	TextAlign	Width
colUnitNumber	Unit #		45
colBedrooms	Beds	Right	40
colBathrooms	Baths	Right	40
colMonthlyRate	Rate	Right	65
colSecurityDeposit	Deposit	Right	65
colOccupancyStatus	Status	Center	80

Button

btnNewApartment

New Apartment...

Bottom, Right

Button

btnClose

Bottom, Right

Double-click an unoccupied area of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Apartments : Form
    {
        public Apartments()
        {
            InitializeComponent();
        }

        private void ShowApartments()
        {
            lvwApartments.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdApartments =
                            new SqlCommand("SELECT * FROM Presentation.Units;",
                                           cntLambdaSquare);
                SqlDataAdapter sdaApartments = new SqlDataAdapter();
                DataSet dsApartments = new DataSet("Apartments");

                cntLambdaSquare.Open();

                sdaApartments.SelectCommand = cmdApartments;
                sdaApartments.Fill(dsApartments);

                foreach (DataRow drApartment in dsApartments.Tables[0].Rows)
                {
                    ListViewItem lviApartment =
                        new ListViewItem(drApartment["Unitmnumber"].ToString());
                    lviApartment.SubItems.Add(drApartment["Bedrooms"].ToString());
                    lviApartment.SubItems.Add(drApartment["Bathrooms"].ToString());
                    lviApartment.SubItems.Add(drApartment["MonthlyRate"].ToString());
                    lviApartment.SubItems.Add(drApartment["SecurityDeposit"].ToString());
                    lviApartment.SubItems.Add(drApartment["OccupancyStatus"].ToString());
                    lviApartment.SubItems.Add(drApartment["Notes"].ToString());

                    lvwApartments.Items.Add(lviApartment);
                }
            }
        }

        private void Apartments_Load(object sender, EventArgs e)
        {
            ShowApartments();
        }
    }
}

Return to the form and double-click the New Apartment button

Implement the event as follows:

private void btnNewApartment_Click(object sender, EventArgs e)
{
    Apartment apt = new Apartment();

    if (apt.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(apt.txtUnitNumber.Text))
                {
                    MessageBox.Show("You must specify the unit number of the apartment.",
                                    "Lambda Square - Apartments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='EXPRESSION';" +
                                         "Database=LambdaSquare1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdLambdaSquare =
                        new SqlCommand("INSERT INTO Presentation.Units(" +
                                       "UnitNumber,	Bedrooms, Bathrooms, MonthlyRate, " +
                                       "SecurityDeposit, OccupancyStatus, Notes) " +
                                       "VALUES('" + apt.txtUnitNumber.Text + "', " +
                                       int.Parse(apt.txtBedrooms.Text) + ", " + float.Parse(apt.txtBathrooms.Text) + ", " +
                                       double.Parse(apt.txtMonthlyRate.Text) + ", " + double.Parse(apt.txtSecurityDeposit.Text) + ", '" +
                                       apt.cbxOccupancyStatus.Text + "', '" + apt.txtNotes.Text + "');",
                                         cntLambdaSquare);

                    cntLambdaSquare.Open();
                    cmdLambdaSquare.ExecuteNonQuery();

                    MessageBox.Show("A new apartment has been built.",
                                    "Lambda Square - Tenants",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowApartments(); 
}

Return to the form and double-click the Close button

Implement the event as follows:

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

Return to Microsoft SQL Server

aaa

Business Operations

The Registrations

To get possession of an apartment, a customer must come to the office, fill out a form, check what apartments are available, and wait. An employee would process the application, check the available apartments, check the customer's needs, and assign an apartment if everything is alright. This process can be referred to as registration.

To process a registration, we will need:

The Registration Number: This is a unique number that identifies each registration
The Registration Date: This is the date the record was created
The Employee Number: This represents the employee who created the record
The Tenant Code: This is the account number of the customer whose registration was processed
The Apartment Number: This information identifies the apartment that is allocated to the customer
The Rent Start Date: This information lets us know the first day the tenant starts occupying the apartment. The payments typically start at the end of the month of that date

Practical Learning Practical Learning: Registering the Tenants

In Microsoft SQL Server, replace the text in the Query window with the following:

CREATE TABLE Rentals.Registrations
(
	RegistrationID int identity(1001, 1) not null,
	RegistrationDate Date,
	EmployeeNumber nvarchar(10), -- Processed By
	TenantCode nvarchar(10), -- Processed For
	UnitNumber nvarchar(10) not null,
	RentStartDate date,
	Notes nvarchar(max),
	Constraint PK_Registrations Primary Key(RegistrationID),
	Constraint FK_Registrants Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
	Constraint FK_Tenants Foreign Key(TenantCode) References Rentals.Tenants(TenantCode),
	Constraint FK_Units Foreign Key(UnitNumber) References Presentation.Units(UnitNumber)
);
GO

To execute the code, on the toolbar, click the Execute button
Return to Microsoft Visual Studio
On the main menu, click Project -> Add Windows Form...
Set the Name to Registration
Click Add

Design the form as follows:

Lambda Square - Registration

Control	(Name)	Text	Other Properties
Label		Registation Date:
DateTimePicker	dtpRegistrationDate
Label		Registered By
Label		Employee #:
TextBox	txtEmployeeNumber
TextBox	txtEmployeeName
Label		Registered For
Label		Tenant Code:
TextBox	txtTenantCode
TextBox	txtTenantName
Label		Unit Number:
TextBox	txtUnitNumber
Label		Rent Start Date:
DateTimePicker	dtpRentStartDate
Label		Notes:
TextBox	txtNotes		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

On the form, click the txtEmployeeNumber text box
In the Properties window, click Events and double Leave

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Registration : Form
    {
        public Registration()
        {
            InitializeComponent();
        }

        private void txtEmployeeNumber_Leave(object sender, EventArgs e)
        {
            if( string.IsNullOrEmpty(txtEmployeeNumber.Text) )
                return;

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                   new SqlCommand("SELECT * FROM Personnel.Employees " +
                          "WHERE EmployeeNumber = '" + txtEmployeeNumber.Text + "';",
                                           cntLambdaSquare);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("Employees");

                cntLambdaSquare.Open();

                sdaEmployees.SelectCommand = cmdEmployees;
                sdaEmployees.Fill(dsEmployees);

                foreach (DataRow drEmployee in dsEmployees.Tables[0].Rows)
                    txtEmployeeName.Text = (drEmployee["EmployeeName"].ToString());
            }
        }
    }
}

Return to the form and click the txtTenantCode text box
In the Events section of the Properties window, double-click Leave

Implement the event as follows:

private void txtTenantCode_Leave(object sender, EventArgs e)
{
    int children = 0;
    string strTenantName = "";
    string strMaritalStatus = "";

    if (string.IsNullOrEmpty(txtTenantCode.Text))
        return;

    using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand cmdTenants =
                 new SqlCommand("SELECT * FROM Rentals.Tenants " +
                                "WHERE TenantCode = '" + txtTenantCode.Text + "';",
                                           cntLambdaSquare);
        SqlDataAdapter sdaTenantCode = new SqlDataAdapter();
        DataSet dsTenantCode = new DataSet("Tenants");

        cntLambdaSquare.Open();

        sdaTenantCode.SelectCommand = cmdTenants;
        sdaTenantCode.Fill(dsTenantCode);

        foreach (DataRow drTenant in dsTenantCode.Tables[0].Rows)
        {
                    strTenantName = drTenant["TenantName"].ToString();
                    strMaritalStatus = drTenant["MaritalStatus"].ToString();
                    children = int.Parse(drTenant["NumberOfChildren"].ToString());
        }

        if (string.IsNullOrEmpty(strTenantName))
        {
            txtTenantCode.Text = "";
            return;
        }
        else
        {
            if (children == 1)
                txtTenantName.Text = strTenantName + "; " + strMaritalStatus + " with 1 child.";
            else
                txtTenantName.Text = strTenantName + "; " + strMaritalStatus + " with " + children.ToString() + " children.";
        }
    }
}

Return to the form and click the txtUnitNumber text box
In the Events section of the Properties window, double-Leave

Implement the event as follows:

private void txtUnitNumber_Leave(object sender, EventArgs e)
{
    int bedrooms = 0;
    float bathrooms = 0.00F;
    double monthlyRate = 0.00D;

    if (string.IsNullOrEmpty(txtTenantCode.Text))
        return;

    using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdUnits =
                         new SqlCommand("SELECT * FROM Presentation.Units " +
                                  "WHERE UnitNumber = '" + txtUnitNumber.Text + "';",
                                           cntLambdaSquare);
                SqlDataAdapter sdaUnits = new SqlDataAdapter();
                DataSet dsUnits = new DataSet("Units");

                cntLambdaSquare.Open();

                sdaUnits.SelectCommand = cmdUnits;
                sdaUnits.Fill(dsUnits);

                foreach (DataRow drUnit in dsUnits.Tables[0].Rows)
                {
                    bedrooms = int.Parse(drUnit["Bedrooms"].ToString());
                    bathrooms = float.Parse(drUnit["Bathrooms"].ToString());
                    monthlyRate = double.Parse(drUnit["MonthlyRate"].ToString());
                }

                if (string.IsNullOrEmpty(txtUnitNumber.Text))
                {
                    txtUnitNumber.Text = "";
                    return;
                }
                else
                {
                    txtUnitDetails.Text = bedrooms  + ", " +
                        bathrooms + " bathrooms; Monthly Rate: " +
                        monthlyRate.ToString("F");
                }
    }
}

On the main menu, click Project -> Add Windows Form...
Set the name to Registrations
Click Add

Design the form as follows:

Control

(Name)

Text

Anchor

ListView

lvwRegistrations

Top, Bottom, Left, Right

Columns

(Name)	Text	TextAlign	Width
colUnitNumber	Unit #		45
colBedrooms	Beds	Right	40
colBathrooms	Baths	Right	40
colMonthlyRate	Rate	Right	65
colSecurityDeposit	Deposit	Right	65
colOccupancyStatus	Status	Center	80

Button

btnNewRegistration

New Registration ...

Bottom, Right

Button

btnClose

Bottom, Right

Double-click an unoccupied area of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Registrations : Form
    {
        public Registrations()
        {
            InitializeComponent();
        }

        private void ShowRegistrations()
        {
            lvwRegistrations.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdApartments =
                            new SqlCommand("SELECT * FROM Rentals.Registrations;",
                                           cntLambdaSquare);
                SqlDataAdapter sdaRegistrations = new SqlDataAdapter();
                DataSet dsRegistrations = new DataSet("Registrations");

                cntLambdaSquare.Open();

                sdaRegistrations.SelectCommand = cmdApartments;
                sdaRegistrations.Fill(dsRegistrations);

                foreach (DataRow drRegistration in dsRegistrations.Tables[0].Rows)
                {
                    ListViewItem lviRegistration =
                        new ListViewItem(drRegistration["RegistrationID"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["RegistrationDate"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["EmployeeNumber"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["TenantCode"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["UnitNumber"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["RentStartDate"].ToString());

                    lvwRegistrations.Items.Add(lviRegistration);
                }
            }
        }

        private void Registrations_Load(object sender, EventArgs e)
        {
            ShowRegistrations();
        }
    }
}

Return to the form and double-click the New Registration button

Implement the event as follows:

private void btnNewRegistration_Click(object sender, EventArgs e)
{
    Registration reg = new Registration();

    if (reg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='EXPRESSION';" +
                                         "Database=LambdaSquare1;" +
                                         "Integrated Security=SSPI;"))
        {
            SqlCommand cmdLambdaSquare =
                new SqlCommand("INSERT INTO Rentals.Registrations(" +
                               "RegistrationDate, EmployeeNumber, TenantCode, UnitNumber, " +
                               "RentStartDate, Notes) " +
                               "VALUES('" + reg.dtpRegistrationDate.Value  + "', '" +
                               reg.txtEmployeeNumber.Text + "', '" + reg.txtTenantCode.Text  + "', '" +
                               reg.txtUnitNumber.Text  + "', '" + reg.dtpRentStartDate.Value  + "', '" +
                               reg.txtNotes.Text + "');",
                                 cntLambdaSquare);

            cntLambdaSquare.Open();
            cmdLambdaSquare.ExecuteNonQuery();

            MessageBox.Show("A new registration has been made.",
                            "Lambda Square - Tenants",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    ShowRegistrations(); 
}

Return to Microsoft SQL Server

The Payments

At the end of each month, every tenant must pay rent. To process a payment, we will need:

The Payment Number: This is a uniqque number that identifies a payment
The Payment Date: This is the date the payment is/was made
The Employee Number: This information identifies the employee who processed or received the payment
The Registration Number: In some applications, you may want to identify a payment by the aparment number or the tenant. Experience shows that a payment may be made by a person who is not living in the apartment (a parent, a friend, a spouse, an employer, an acquaintance, etc). A payment can also be made by two people, each paying part of the rent. There are many other disparate possibilities like that. Therefore, we will use the registration for which the payment is made. After all, a registration number holds the apartment number and the tenant who is occupying that apartment
The Amount Paid: As you may know already, there are various types of payments (such as security deposits, regular monthly payments, etc) and various amounts of payments (remember that apartments have different prices)

Practical Learning Practical Learning: Getting the Payments

Change the code in the Query window with the following:

CREATE TABLE Rentals.Payments
(
	ReceiptNumber int identity(100001, 1) not null,
	PaymentDate date,
	EmployeeNumber nvarchar(10), -- Processed By
	RegistrationID int,
	AmountPaid money,
	Notes nvarchar(max),
	Constraint PK_Payments Primary Key(ReceiptNumber),
	Constraint FK_PaymentReceiver Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
	Constraint FK_Registrations Foreign Key(RegistrationID) References Rentals.Registrations(RegistrationID)
);
GO

To execute the code, on the toolbar, click the Execute button
Close Microsoft SQL Server
When asked whether you want to save, click No
On the main menu, click Project -> Add Windows Form...
Set the Name to Payment
Click Add

Design the form as follows:

Lambda Square - Payment

Control	(Name)	Text	Other Properties
Label		Payment Date:
DateTimePicker	dtpPaymentDate
Label		Payment Receied/Processed By
Label		Employee #:
TextBox	txtEmployeeNumber
TextBox	txtEmployeeName
Label		Payment For
Label		Registration #:
TextBox	txtRegistrationID
Label		Amount Paid:
TextBox	txtAmountPaid		TextAlight: Right
TextBox	txtRegistrationDetails		Multine: True Scrollbars: Vertical
ComboBox	cbxOccupanyStatusStatus
Label		Notes:
TextBox	txtNotes		Multine: True Scrollbars: Vertical
Form			AcceptButton: btnOK CancelButton: btnCancel

On the form, click the txtEmployeeNumber text box
In the Properties window, click Events and double Leave

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Payment : Form
    {
        public Payment()
        {
            InitializeComponent();
        }

        private void txtEmployeeNumber_Leave(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtEmployeeNumber.Text))
                return;

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                   new SqlCommand("SELECT * FROM Personnel.Employees " +
                          "WHERE EmployeeNumber = '" + txtEmployeeNumber.Text + "';",
                                           cntLambdaSquare);

                cntLambdaSquare.Open();
                SqlDataReader drEmployee = cmdEmployees.ExecuteReader();

                while (drEmployee.Read())
                    txtEmployeeName.Text = (drEmployee["EmployeeName"].ToString());

                drEmployee.Close();
            }
        }
    }
}

Return to the form and click the txtRegistrationID text box
In the Events section of the Properties window, double-click Leave

Implement the event as follows:

private void txtRegistrationID_Leave(object sender, EventArgs e)
{
    string strUnitNumber = "";
    string strTenantCode = "";
    string strEmployeeNumber = "";
    string strRegistration = "";

    int children = 0;
    string strTenantName = "";
    string strMaritalStatus = "";

    if (string.IsNullOrEmpty(txtRegistrationID.Text))
        return;

    using (SqlConnection cntRegistrations =
                        new SqlConnection("Data Source='EXPRESSION';" +
                                          "Database='LambdaSquare1';" +
                                          "Integrated Security=yes;"))
    {
                SqlCommand cmdRegistrations =
                         new SqlCommand("SELECT * FROM Rentals.Registrations " +
                                        "WHERE RegistrationID = '" + txtRegistrationID.Text + "';",
                                                   cntRegistrations);
                SqlDataAdapter sdaRegistrations = new SqlDataAdapter();
                DataSet dsRegistrations = new DataSet("Registrations");

                cntRegistrations.Open();

                sdaRegistrations.SelectCommand = cmdRegistrations;
                sdaRegistrations.Fill(dsRegistrations);

                foreach (DataRow drTenant in dsRegistrations.Tables[0].Rows)
                {
                    strEmployeeNumber = drTenant["EmployeeNumber"].ToString();
                    strTenantCode = drTenant["TenantCode"].ToString();
                    strUnitNumber = drTenant["UnitNumber"].ToString();
                }

                if (string.IsNullOrEmpty(strEmployeeNumber))
                {
                    txtRegistrationID.Text = "";
                    return;
                }
            }

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdTenants =
                         new SqlCommand("SELECT * FROM Rentals.Tenants " +
                                        "WHERE TenantCode = '" + strTenantCode + "';",
                                           cntLambdaSquare);
                SqlDataAdapter sdaTenantCode = new SqlDataAdapter();
                DataSet dsTenantCode = new DataSet("Tenants");

                cntLambdaSquare.Open();

                sdaTenantCode.SelectCommand = cmdTenants;
                sdaTenantCode.Fill(dsTenantCode);

                foreach (DataRow drTenant in dsTenantCode.Tables[0].Rows)
                {
                    strTenantName = drTenant["FirstName"].ToString() + " " + 
                        drTenant["LastName"].ToString();
                    strMaritalStatus = drTenant["MaritalStatus"].ToString();
                    children = int.Parse(drTenant["NumberOfChildren"].ToString());
                }

                if (!string.IsNullOrEmpty(strTenantName))
                {
                    if (children == 1)
                        strRegistration = "Tenant: " + strTenantName + ", " +
                            strMaritalStatus + " with 1 child." + System.Environment.NewLine;
                    else
                        strRegistration = "Tenant: " + strTenantName + ", " + 
                            strMaritalStatus + " with " + children.ToString() + 
                            " children." + System.Environment.NewLine;
                }
            }
 
            using (SqlConnection cntApartments =
                        new SqlConnection("Data Source='EXPRESSION';" +
                                          "Database='LambdaSquare1';" +
                                          "Integrated Security=yes;"))
            {
                SqlCommand cmdApartments =
                         new SqlCommand("SELECT * FROM Presentation.Units " +
                                        "WHERE UnitNumber = '" + strUnitNumber + "';",
                                                   cntApartments);
                SqlDataAdapter sdaApartments = new SqlDataAdapter();
                DataSet dsApartments = new DataSet("Apartments");

                cntApartments.Open();

                sdaApartments.SelectCommand = cmdApartments;
                sdaApartments.Fill(dsApartments);

                foreach (DataRow drApartment in dsApartments.Tables[0].Rows)
                    strRegistration += "Unit Occupied: " + drApartment["UnitNumber"].ToString() + 
                        System.Environment.NewLine + "Monthly Rate: " + 
                        double.Parse(drApartment["MonthlyRate"].ToString()).ToString("F");
            }

            txtRegistrationDetails.Text = strRegistration;
}

On the main menu, click Project -> Add Windows Form...
Set the name to Payments
Click Add

Design the form as follows:

Control

(Name)

Text

Anchor

ListView

lvwRegistrations

Top, Bottom, Left, Right

Columns

(Name)	Text	TextAlign	Width
colRegistrationID	Reg ID		50
colRegistrationDate	Reg Date	Center
colEmployeeNumber	Clerk
colTenantCode	Tenant	Center
colUnitNumber	Unit #	Center	50
colRentStartDate	Start Date Center

Button

btnNewRegistration

New Registration ...

Bottom, Right

Button

btnClose

Bottom, Right

Double-click an unoccupied area of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LambdaSquare1
{
    public partial class Payments : Form
    {
        public Payments()
        {
            InitializeComponent();
        }

        private void ShowPayments()
        {
            
            lvwPayments.Items.Clear();

            using (SqlConnection cntLambdaSquare =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='LambdaSquare1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdPayments =
                            new SqlCommand("SELECT * FROM Rentals.Payments;",
                                           cntLambdaSquare);
                SqlDataAdapter sdaPayments = new SqlDataAdapter();
                DataSet dsPayments = new DataSet("Payments");

                cntLambdaSquare.Open();

                sdaPayments.SelectCommand = cmdPayments;
                sdaPayments.Fill(dsPayments);

                foreach (DataRow drPayment in dsPayments.Tables[0].Rows)
                {
                    ListViewItem lviPayment =
                        new ListViewItem(drPayment["ReceiptNumber"].ToString());
                    lviPayment.SubItems.Add(DateTime.Parse(drPayment["PaymentDate"].ToString()).ToShortDateString());
                    lviPayment.SubItems.Add(drPayment["EmployeeNumber"].ToString());
                    lviPayment.SubItems.Add(drPayment["RegistrationID"].ToString());
                    lviPayment.SubItems.Add(drPayment["AmountPaid"].ToString());

                    lvwPayments.Items.Add(lviPayment);
                }
            }
        }

        private void Payments_Load(object sender, EventArgs e)
        {
            ShowPayments();
        }
    }
}

Return to the form and double-click the New Payment button

Implement the event as follows:

private void btnNewPayment_Click(object sender, EventArgs e)
{
    Payment pmt = new Payment();

    if (pmt.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                using (SqlConnection cntLambdaSquare =
                               new SqlConnection("Data Source='EXPRESSION';" +
                                                 "Database=LambdaSquare1;" +
                                                 "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdLambdaSquare =
                        new SqlCommand("INSERT INTO Rentals.Payments(" +
                                       "PaymentDate, EmployeeNumber, " +
                                       "RegistrationID, AmountPaid, Notes) " +
                                       "VALUES('" + pmt.dtpPaymentDate.Value + "', '" +
                                       pmt.txtEmployeeNumber.Text + "', " + int.Parse(pmt.txtRegistrationID.Text) + ", " +
                                       double.Parse(pmt.txtAmountPaid.Text) + ", '" + pmt.txtNotes.Text + "');",
                                         cntLambdaSquare);

                    cntLambdaSquare.Open();
                    cmdLambdaSquare.ExecuteNonQuery();

                    MessageBox.Show("A new Payment has been made.",
                                    "Lambda Square - Tenants",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowPayments(); 
}

Display the Central Form

Design it as follows:

Control	(Name)	Text
Button	btnTenants	Tenants...
Button	btnApartments	Apartments...
Button	btnRegistrations	Registrations...
Button	btnPayments	Payments...
Button	btnEmployees	Employees...
Button	btnClose	Close

Double-click the Tenants button

Implement its event as follows:

private void btnTenants_Click(object sender, EventArgs e)
{
    Tenants clients = new Tenants();
    clients.Show();
}

Return to the form and double-click the Apartments button

Implement its event as follows:

private void btnApartments_Click(object sender, EventArgs e)
{
    Apartments apts = new Apartments();
    apts.Show();
}

Return to the form and double-click the Registrations button

Implement its event as follows:

private void btnRegistrations_Click(object sender, EventArgs e)
        {
            Registrations regs = new Registrations();
            regs.Show();
        }

Return to the form and double-click the Payments button

Implement its event as follows:

private void btnPayments_Click(object sender, EventArgs e)
        {
            Payments pmts = new Payments();
            pmts.Show();
        }

Return to the form and double-click the Employees button

Implement its event as follows:

private void btnEmployees_Click(object sender, EventArgs e)
        {
            Employees empls = new Employees();
            empls.Show();
        }

Return to the form and double-click the Close button

Implement its event as follows:

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

Testing the Application

A summary of customer payments is a document that shows a list of payments the customer has made. It also shows information such as the date a payment was made.

Practical Learning Practical Learning: Creating the Summary

Execute the application
Click the Employees form

Click the New Employee button and continuously create the following employees records:

Empl #	First Name	Last Name	Title
62797	Leonard	Goulet	Owner - General Manager
27495	Justine	Sandt	Rent Manager
94008	Martin	Schroeder	Rent Associate
30529	Mark	Reason	Maintenance Technician
92748	Nancy	Longhorn	Rent Associate
40995	Jane	Proctor	Human Resources Manager
92749	Horace	Taylor	Maintenance Technician
72088	Thomas	Wilkens	Bookeeper
28404	Raymond	Wilkinson
38047	Marc	Knights	Rent Associate

Close the Employees form
Double-click the Tenants form
Click the New Tenant button

Click the New button and create the following marital stastus records:

Marital Status	Status Description
Divorced	At one time, the tenant was married but has legally divorced.
Married	The tenant is currently (legally) married, whether living with the spouse or not.
Separated	The tenant and the official spouse are not living together and are not expected to share the apartment
Single	The tenant has never been married.
Unspecified
Widow	At one time, the tenant was married but the spouse passed away.

Create the following tenant records:

Tenant Code	First Name	Last Name	Marital Status	Children	Phone #	Email Address
928411	Ann	Sanders	Married	1	(240) 524 -2831	annsanders@emailcity.com
279475	Mahty	Shaoul	Married	2	202-729-1574	mshaoulman@gmail.com
920074	Frank	Ulm	Single	0	(301) 882-0704	fulm112244@yaho.com
804085	Elise	Provoski	Separated	1	(443) 974-9631	eprevalence@yahoo.com
920948	Grace	Curryan	Married	1	(240) 927-0993	gcarrier@gmail.com
603848	Tracy	Warrens	Divorced	2	202-793-6924	twarrior12@hotmail.coom
824922	Paul	Yamo	Married	3	(410-792-7045	pyamo@hr.umd.edu
300409	Nancy	Shermann	Single	1	(703) 338-2973	nsherre@emailcity.com
248506	Michael	Tiernan	Single	0	301-274-9285	resdev.globelan.net
208081	Phillippe	Anderson	Single	0	202-729-1574	philanders@gmail.com

Close the Tenants form
Double-click the Apartments button
Click the New Apartment button

Click the New button and continuously create the following records:

Occupancy Status	Status Description
Available	The apartment is ready for rent. It has passed internal and external inspections.
Occupied	The apartment is currently used by another tenant and it is not available
Not Ready	For any reason, the apartment cannot be occupied at this time. It could be due to need repair or a failed inspection.
Needs Repair

Create the following apartments records:

Unit #	Beds	Baths	Monthly Price	Security Deposit	Status
101	2	2	1150	450	Available
102	1	1	950	350	Needs Repair
103	1	1	925	350	Available
104	3	2	1350	500	Available
105	2	1	1150	400	Available
106	3	2	1350	500	Available
107	3	2	1285	500	Not Ready
108	1	1	885	300	Available
109	2	2	1150	450	Available
110	1	1	895	300	Available
111	2	2	1145	450	Available
112	2	1	1085	400	Available
201	2	1	1185	400	Available
202	1	1	895	300	Available
203	1	1	925	350	Available
204	3	2	1250	500	Available
205	2	1	1100	400	Available
206	3	2	1300	500	Available
207	3	2	1350	500	Available
208	1	1	920	350	Available
209	2	2	1150	450	Available
210	1	1	895	300	Available
211	2	2	1175	450	Available
212	2	1	1075	400	Available
301	2	2	1175	450	Available
302	1	1	950	350	Available
303	1	1	925	350	Available
304	3	2	1250	500	Available
305	2	1	1100	400	Needs Repair
306	3	2	1300	500	Available
307	3	2	1350	500	Available
308	1	1	920	350	Available
309	2	2	1150	450	Available
310	1	1	935	350	Available
311	2	2	1175	450	Available
312	2	1	1075	400	Available

Close the Apartments form
Double-click the Registrations form

Click the New Registration button and create the following records:

Registration Date	Empl #	Tenant Code	Unit #	Rent Start Date
Tuesday, June 12, 2012	38047	928411	109	01-Jul-12
Friday, June 15, 2012	92748	279475	104	01-Aug-12
Friday, June 22, 2012	27495	920074	103	01-Jul-12
Friday, June 22, 2012	94008	804085	305	01-Aug-12
Monday, July 23, 2012	94008	920948	105	01-Sep-12
Wednesday, July 25, 2012	27495	603848	106	01-Aug-12
Wednesday, August 01, 2012	38047	824922	204	01-Oct-12
Friday, August 10, 2012	27495	300409	108	01-Sep-12
Wednesday, September 12, 2012	92749	248506	209	01-Nov-12
Friday, October 05, 2012	38047	208081	202	01-Nov-12

Close the Registrations form
Double-click the Payments form

Click the New Payment button and create the following records:

Payment Date	Empl #	Reg ID	Amount	Notes
Friday, June 22, 2012	38047	1001	450	This is the payment for the security deposit.
Monday, June 25, 2012	92749	1002	500	Payment for security deposit
Monday, July 02, 2012	27495	1003	350	Security Deposit
Wednesday, July 25, 2012	38047	1001	1150
Thursday, July 26, 2012	94008	1003	925
Wednesday, August 01, 2012	27495	1006	500	Security Deposit
Wednesday, August 08, 2012	27495	1008	300	Security Deposit
Wednesday, August 08, 2012	27495	1007	500	Security Deposit
Monday, August 13, 2012	27495	1004	400	Security Deposit
Monday, August 27, 2012	27495	1004	1100
Tuesday, August 28, 2012	92749	1002	1350
Tuesday, August 28, 2012	38047	1001	1150
Thursday, August 30, 2012	94008	1003	925
Thursday, August 30, 2012	94008	1006	1350
Monday, September 17, 2012	27495	1009	450	Security Deposit
Tuesday, September 18, 2012	92749	1005	400	Security Deposit
Tuesday, September 25, 2012	92749	1004	1100
Tuesday, September 25, 2012	92749	1008	885
Tuesday, September 25, 2012	92749	1006	1350
Thursday, September 27, 2012	92749	1001	1150
Friday, September 28, 2012	27495	1002	1350
Friday, September 28, 2012	27495	1005	1150
Monday, October 01, 2012	38047	1003	925
Monday, October 08, 2012	27495	1010	300	Security Deposit
Wednesday, October 24, 2012	92749	1004	1100
Wednesday, October 24, 2012	92749	1005	1150
Thursday, October 25, 2012	27495	1006	1350
Thursday, October 25, 2012	27495	1007	1250
Friday, October 26, 2012	92749	1002	1350
Monday, October 29, 2012	62797	1001	1150
Monday, October 29, 2012	62797	1008	885
Tuesday, October 30, 2012	27495	1003	925
Monday, November 26, 2012	92749	1008	885
Monday, November 26, 2012	38047	1002	1350
Tuesday, November 27, 2012	38047	1006	1350
Wednesday, November 28, 2012	62797	1001	1150
Wednesday, November 28, 2012	94008	1004	1100
Wednesday, November 28, 2012	92749	1005	1150
Wednesday, November 28, 2012	92749	1007	1250
Friday, November 30, 2012	94008	1009	1150
Friday, November 30, 2012	38047	1003	925
Friday, November 30, 2012	92748	1010	895
Sunday, December 02, 2012	92749	1002	1350
Tuesday, December 25, 2012	38047	1006	1350
Tuesday, December 25, 2012	38047	1007	1250
Wednesday, December 26, 2012	62797	1002	1350
Wednesday, December 26, 2012	94008	1001	1150
Thursday, December 27, 2012	92748	1009	1150
Friday, December 28, 2012	38047	1005	1150
Friday, December 28, 2012	94008	1010	895
Friday, December 28, 2012	92749	1004	1100
Friday, December 28, 2012	94008	1003	925
Monday, December 31, 2012	92749	1008	885

Close the Registrations form

Download