Home

Microsoft SQL Server Graphical Database Example : The Ceil Inn Hotel
   

Introduction

Ceil Inn is a (fictional) hotel. The company provides various types rooms (bedrooms, suites, and conference rooms). Rooms have different types of bed (queen, double, or king). Depending on the type of room and the bed(s), a certain rate is charged to occupy or use a room.

We are asked to create a database application that would help the employees of Ceil Inn to manage their business.

Practical Learning: Starting the Application
  1. Start Microsoft Visual Studio
  2. To start a new project, on the main menu, click File -> New -> Project...
  3. Make sure Windows Forms Application is selected.
    Set the Name to CeilInn1
  4. Click OK
  5. CIn the Solution Explorer, right-click Form1.cs and click Rename
  6. Type CeilInn.cs and press Enter
  7. Double-click the body of the form to generate its Load event
  8. 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreateDatabase()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";


            using (SqlConnection cntCeilInn =
        new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("IF EXISTS (" +
                                   "SELECT name " +
                                   "FROM sys.databases " +
                                   "WHERE name = N'CeilInn1')" +
                                   "DROP DATABASE CeilInn1; " +
                                   "CREATE DATABASE CeilInn1;", cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE SCHEMA HumanResources;", cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE SCHEMA Hotel;", cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE SCHEMA Room;", cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

           MessageBox.Show("The CeilInn1 database has been created.",
                                "Ceil Inn - Hotel",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreateDatabase();
        }
    }
}
  9. Execute the application
  10. When the message box displays, click OK

The Employees of the Company

Employees are people who will manage all aspects of the database. In most databases, they are identified with as much information as possible. To keep our implication simple, we will need the name and the title of each employee. To uniquely identity an employee, each one will have an employee number. This number will be specified but the person who is creating the record for a new hire.

Practical Learning: Creating the Employees
  1. Change the document 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreateEmployees()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE TABLE HumanResources.Employees" +
                                   "(" +
                                   "    EmployeeNumber nvarchar(10) NOT NULL," +
                                   "	FirstName nvarchar(24)," +
                                   "	LastName nvarchar(24) not null," +
                             "	EmployeeName AS (LastName + N', ' + FirstName)," +
                                   "	Title nvarchar(50)," +
                                   "	Notes nvarchar(max)," +
                        "    Constraint PK_Employees Primary Key (EmployeeNumber)" +
                                   ");",
                                   cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

      MessageBox.Show("The Employees table has been added to te CeilInn1 database.",
                                "Ceil Inn - Hotel",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreateEmployees();
        }
    }
}
  2. Execute the application
  3. When the message box displays, click OK
  4. To start a new form, on the main menu, Project -> Add Windows Form...
  5. Set the Name to NewEmployee
  6. Click Add
  7. Design the form as follows:
     
    Ceil Inn - New Employee
    Control (Name) Text Other Properties
    Label Label   Employee #  
    TextBox TextBox txtEmployeeNumber    
    Label Label   First Name:  
    TextBox TextBox txtFirstName    
    Label Label   Last Name:  
    TextBox TextBox txtLastName    
    Label Label   Title:  
    TextBox TextBox txtTitle    
    Label Label   Notes:  
    TextBox TextBox txtNotes   Multine: True
    Scrollbars: Vertical
    Form       AcceptButton: btnOK
    CancelButton: btnCancel
  8. On the main menu, click Project -> Add Windows Form...
  9. Set the Name to Employees
  10. Click Add
  11. Design the form as follows:
     
    Ceil Inn - Employees
    Control (Name) Text Anchor
    DataGridView ListView dgvEmployees   Top, Bottom, Left, Right
    Button Button btnNewEmployee New Employee... Bottom, Right
    Button Button btnClose Close Bottom, Right
  12. Double-click an unoccupied area of the form
  13. 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 CeilInn1
{
    public partial class Employees : Form
    {
        public Employees()
        {
            InitializeComponent();
        }

        private void ShowEmployees()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source='" + strServerName + "';" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                        new SqlCommand(("SELECT EmployeeNumber," +
                                        "	FirstName, LastName," +
                                        "	EmployeeName, Title " +
                                        "FROM HumanResources.Employees;",
                                        cntCeilInn);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("Employees");

                cntCeilInn.Open();

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

                dgvEmployees.DataSource = dsEmployees.Tables[0];
            }
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            ShowEmployees();
        }
    }
}
  14. Return to the form and double-click the New Employee button
  15. Implement the event as follows: 
    private void btnNewEmployee_Click(object sender, EventArgs e)
{
    string strServerName = "EXPRESSION";
    NewEmployee empl = new NewEmployee();

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

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

                using (SqlConnection cntLambdaSquare =
                       new SqlConnection("Data Source='" + strServerName + "';" +
                                         "Database=CeilInn1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdLambdaSquare =
                        new SqlCommand("INSERT INTO HumanResources.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.",
                                    "Ceil Inn Hotel - Employees",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowEmployees();
}
  16. Return to the form and double-click the Close button
  17. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

The Customers of the Hotel

The customers are the people who rent the rooms that the hotel offers. Like employees, customers have to be identified. For our database, we will just need each customer's name, the telephone number, and information about a person to contact in case of emergency.

When a customer comes to rent a room, an account will be created for him or her. If the same customer comes to rent a room another time, a new accuont will be created. This means that, for accounting purposes, we will need a unique account number for each renting session, even for a repeating customer.

Practical Learning: Creating the Customers
  1. Access the CeilInn.cs file and change its document 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreateCustomers()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE TABLE Hotel.Customers " +
                                   "( " +
                                   "	AccountNumber nvarchar(12) not null," +
                                   "	FirstName nvarchar(24)," +
                                   "	LastName nvarchar(24) not null," +
                                   "	CustomerName  AS (LastName + N', ' + FirstName)," +
                                   "	PhoneNumber nvarchar(20)," +
                                   "	EmergencyName nvarchar(50)," +
                                   "	EmergencyPhone nvarchar(20)," +
                                   "	Notes nvarchar(max)," +
                                   "    Constraint PK_Customers Primary Key (AccountNumber)" +
                                   ");",
                                   cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

           MessageBox.Show("The Customers table has been added to te CeilInn1 database.",
                           "Ceil Inn - Customers",
                           MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreateCustomers();
        }
    }
}
  2. Execute the application
  3. When the message box displays, click OK
  4. To create a new form, on the main menu, Project -> Add Windows Form...
  5. Set the Name to NewCustomer
  6. Click Add
  7. Design the form as follows:
     
    Ceil Inn - New Customer
    Control (Name) Text Other Properties
    Label Label   Account #:  
    TextBox TextBox txtAccountNumber    
    Label Label   First Name:  
    TextBox TextBox txtFirstName    
    Label Label   Last Name:  
    TextBox TextBox txtLastName    
    Label Label   Emrg Name:  
    TextBox TextBox txtEmergencyName    
    Label Label   Emrg Phone:  
    TextBox TextBox txtEmergencyPhone    
    Label Label   Notes:  
    TextBox TextBox txtNotes   Multine: True
    Scrollbars: Vertical
    Form       AcceptButton: btnOK
    CancelButton: btnCancel
  8. On the main menu, click Project -> Add Windows Form...
  9. Set the Name to Customers
  10. Click Add
  11. Design the form as follows:
     
    Ceil Inn - Customers
    Control (Name) Text Anchor
    DataGridView Data Grid View dgvCustomers   Top, Bottom, Left, Right
    Button Button btnNewCustomer New Customer... Bottom, Right
    Button Button btnClose Close Bottom, Right
  12. Double-click an unoccupied area of the form
  13. 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 CeilInn1
{
    public partial class Customers : Form
    {
        public Customers()
        {
            InitializeComponent();
        }
        private void ShowCustomers()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source='" + strServerName + "';" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdCustomers =
                            new SqlCommand("SELECT AccountNumber, FirstName," +
                                           "       LastName, CustomerName," +
                                           "       PhoneNumber, EmergencyName," +
                                           "       EmergencyPhone " +
                                           "FROM Hotel.Customers; ",
                                           cntCeilInn);
                SqlDataAdapter sdaCustomers = new SqlDataAdapter();
                DataSet dsCustomers = new DataSet("Employees");

                cntCeilInn.Open();

                sdaCustomers.SelectCommand = cmdCustomers;
                sdaCustomers.Fill(dsCustomers);

                dgvCustomers.DataSource = dsCustomers.Tables[0];
            }
        }

        private void Customers_Load(object sender, EventArgs e)
        {
            ShowCustomers();
        }
    }
}
  14. Return to the form and double-click the New Customer button
  15. Implement the event as follows: 
    private void btnNewCustomer_Click(object sender, EventArgs e)
{
    string strServerName = "EXPRESSION";
    NewCustomer client = new NewCustomer();

    if (client.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(client.txtAccountNumber.Text))
                {
                    MessageBox.Show("You must specify an account number of the new customer.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(client.txtLastName.Text))
                {
                    MessageBox.Show("You must enter the customer's last name.",
                        "Ceil Inn Hotel - Customers",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(client.txtEmergencyPhone.Text))
                {
                    MessageBox.Show("You must specify an emergency phone number for the customer.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntCeilInn =
                       new SqlConnection("Data Source='" + strServerName + "';" +
                                         "Database=CeilInn1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCeilInn =
                        new SqlCommand("INSERT INTO Hotel.Customers( " +
                                       "AccountNumber, FirstName, LastName, " +
                                       "PhoneNumber, EmergencyName," +
                                       "EmergencyPhone, Notes) " +
                                       "VALUES('" + client.txtAccountNumber.Text + "', '" +
                                         client.txtFirstName.Text + "', '" + client.txtLastName.Text +
                                         "', '" + client.txtPhoneNumber.Text + "', '" +
                                         client.txtEmergencyName.Text + "', '" + client.txtEmergencyPhone.Text
                                         + "', '" + client.txtNotes.Text + "');",
                                         cntCeilInn);

                    cntCeilInn.Open();
                    cmdCeilInn.ExecuteNonQuery();

                    MessageBox.Show("A new customer has been registered.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowCustomers();
}
  16. Return to the form and double-click the Close button
  17. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

The Types of Rooms

 The Ceil Inn hotel offers different categories of rooms. A regular bedroom is equipped with one or two beds, one table, one sofa or recliner, two chairs, a closet, and an iron. Besides being a place to sleep, the room has a telephone (analogue line), a kitchen area that has a stove, a refrigerator, a dish washer, and cabinets. The cabinets contain spoons, forks, knives, and plates.

The Types of Beds

Most of the business of Ceil Inn hotel is based on rooms the customers rent, and most customer rent a room to spend the night. To serve such customers, bedroom have different types of bed. The most common bed has a queen size that can serve one or two people. Another category of room uses a king size, which is larger and wider than the queen. Some customers wants a single room but with different beds. Such rooms have a double bed. Of course, conference rooms do not have a bed.

The Occupancy Status of a Room

The hoteil has different rooms that are rented or freed some time to time. When a room is not rented, it has the status of available. When a room has been rented, its occupancy status must be changed to occupied. To take care of this, we will create a table for rooms status.

This is the idea behind a relational database. A relational database is an application that contains two or more tables so that information in one table is made available to another table or other tables that need(s) it. The information is entered once in one particular table. If the same information is needed in another table, it is simply identified one way or another. This reduces, and can eliminate, the likelihood of mistakes that result from duplicate data.

Hotel Rooms

Rooms are probably the most important objects of a hotel. A room is primarily characterized by its category as a bedroom or a conference room. If it's a bedroom, then other pieces of information are necessary. We have already seen what they are: bed type and status.

Each room must have a unique room number. The rate applied when renting a room should be specified. After all, en employee should not guess the price of a room when a customer is about to rent it.

Practical Learning: Creating the Rooms 
  1. Access the CeilInn.cs file and change the document 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreateHotelRooms()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE TABLE Hotel.Rooms" +
                                  "(" +
                                  "    RoomNumber nvarchar(10) not null," +
                                  "    RoomType nvarchar(20) default N'Bedroom'," +
                                  "    BedType nvarchar(40) default N'Queen'," +
                                  "    Rate money default 85.95," +
                               "    RoomStatus nvarchar(25) default N'Available'," +
                               "    Constraint PK_Rooms Primary Key (RoomNumber)" +
                                  ");",
                                   cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

           MessageBox.Show("A table for the hotel rooms has been created.",
                           "Ceil Inn - Hotel Rooms",
                           MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreateHotelRooms();
        }
    }
}
  2. Execute the application
  3. When the message box displays, click OK
  4. On the main menu, Project -> Add Windows Form...
  5. Set the Name to NewHotelRoom
  6. Click Add
  7. Design the form as follows:
     
    Ceil Inn - New Room
    Control (Name) Text DropDownStyle Other Properties
    Label Label   Room #:    
    TextBox TextBox txtRoomNumber      
    Label Label   Room Type:    
    ComboBox Combo Box txtRoomTypes   DropDownList Items:
    Studio
    Bedroom
    Conference Room
    Label Label   Bed Type:    
    ComboBox Combo Box txtBedTypes   DropDownList Items:
    King
    Queen
    1-Double
    2-Double
    Label Label   Rate:    
    TextBox TextBox txtRate 85.95   TextAlign: Right
    Label Label   Room Status:    
    ComboBox Combo Box cbxRoomsStatus   DropDownList Items:
    Available
    Not Ready
    Label Label   Notes:    
    TextBox TextBox txtNotes     Multine: True
    Scrollbars: Vertical
    Form         AcceptButton: btnOK
    CancelButton: btnCancel
  8. On the main menu, click Project -> Add Windows Form...
  9. Set the Name to HotelRooms
  10. Click Add
  11. Design the form as follows:
     
    Ceil Inn - Hotel Rooms
    Control (Name) Text Anchor
    DataGridView Data Grid View dgvRooms   Top, Bottom, Left, Right
    Button Button btnNewRoom New Room... Bottom, Right
    Button Button btnClose Close Bottom, Right
  12. Double-click an unoccupied area of the form
  13. 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 CeilInn1
{
    public partial class HotelRooms : Form
    {
        public HotelRooms()
        {
            InitializeComponent();
        }

        private void ShowRooms()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source='" + strServerName + "';" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdRooms =
                            new SqlCommand("SELECT RoomNumber, RoomType, BedType, " +
                                           "       Rate, RoomStatus, Notes " +
                                           "FROM Hotel.Rooms; ",
                                           cntCeilInn);
                SqlDataAdapter sdaRooms = new SqlDataAdapter();
                DataSet dsRooms = new DataSet("Rooms");

                cntCeilInn.Open();

                sdaRooms.SelectCommand = cmdRooms;
                sdaRooms.Fill(dsRooms);

                dgvRooms.DataSource = dsRooms.Tables[0];
            }
        }

        private void HotelRooms_Load(object sender, EventArgs e)
        {
            ShowRooms();
        }
    }
}
  14. Return to the form and double-click the New Room button
  15. Implement the event as follows: 
    private void btnNewRoom_Click(object sender, EventArgs e)
{
    string strServerName = "EXPRESSION";
    NewHotelRoom rm = new NewHotelRoom();

    if (rm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(rm.txtRoomNumber.Text))
                {
                    MessageBox.Show("You must specify the room number.",
                                    "Ceil Inn Hotel - Rooms",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntCeilInn =
                       new SqlConnection("Data Source='" + strServerName + "';" +
                                         "Database=CeilInn1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCeilInn =
                        new SqlCommand("INSERT INTO Hotel.Rooms( " +
                                       "RoomNumber, RoomType, BedType, " +
                                       "Rate, RoomStatus, Notes) " +
                                       "VALUES('" + rm.txtRoomNumber.Text + "', '" +
                                         rm.cbxBedTypes.Text + "', '" + rm.cbxBedTypes.Text +
                                         "', " + double.Parse(rm.txtRate.Text) + ", '" +
                                         rm.cbxRoomsStatus.Text + "', '" + rm.txtNotes.Text + "');",
                                         cntCeilInn);

                    cntCeilInn.Open();
                    cmdCeilInn.ExecuteNonQuery();

                    MessageBox.Show("A new Room has been built.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowRooms();
}
  16. Return to the form and double-click the Close button
  17. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Rooms Assignments

After registering a customer, an employee can assign a room. Normally, th employee would ask what type of room the customer wants to rent. After all, some people want a bedroom, others a studio, and others a conference room. The employee can use a form that would show the list of available rooms.

When assigning a room to a customer, we first need to know who (what employee) performed this operation. We also would like to know the date this operation was performed. We may just use that same date as the first day a customer rented a room or the day a conference room was used. Of course, the employee needs to identify the customer who is renting the room. The employee must also specify the room that is being rented and how much will be charged. To know the rate, the employee can use a form that shows available rooms.

In our hotel, the Internet is free to all customers (we remember when hotels used to charge for Internet use; most of them now offer free Internet and many of them have computers somewhere at the receiption or in some room for customers use). If using the telephone in the room, if a customer makes local calls, they are free. Long distance calls are not free. We need to keep, on a daily basis, a calendar and rate of phone consumption (this is one of the reasons we create a new account for every customer, including returning customers).

As you can imagine, each room assignment must have a unique number that identifies its record. We will use an automatically incrementing number. To make it a little fancy, we will start the numbering at 100001 and increment by 1 for each subsequent record.

Practical Learning: Assigning a Room
  1. Access the CeilInn.cs file and change the document 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreateRoomsAssignments()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE TABLE Room.Assignments" +
                                   "(" +
                                   "	AssignmentNumber int identity(100001, 1) NOT NULL," +
                                   "	ProcessedBy nvarchar(10)," +
                                   "	DateOccupied Date not null," +
                                   "	ProcessedFor nvarchar(12)," +
                                   "	RoomNumber nvarchar(10)," +
                                   "	RateApplied money default 0," +
                                   "	PhoneCharge money," +
                                   "	Notes nvarchar(max)," +
                                   "    Constraint PK_Occupancies Primary Key (AssignmentNumber)," +
                                   "    Constraint FK_Registrant Foreign Key(ProcessedBy) " +
                                   "        References HumanResources.Employees(EmployeeNumber), " +
                                   "    Constraint FK_Customers Foreign Key(ProcessedFor) " +
                                   "        References Hotel.Customers(AccountNumber), " +
                                   "    Constraint FK_Rooms Foreign Key(RoomRented) " +
                                   "        References Hotel.Rooms(RoomNumber) " +
                                   ");",
                                   cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

           MessageBox.Show("A table used to keep records of rooms assigned to customers has been created.",
                           "Ceil Inn - Hotel Rooms",
                           MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreateRoomsAssignments();
        }
    }
}
  2. Execute the application
  3. When the message box displays, click OK
  4. To launch a new form, on the main menu, Project -> Add Windows Form...
  5. Set the Name to RoomAssignment
  6. Click Add
  7. Design the form as follows:
     
    Ceil Inn - New Room Assignment
    Control (Name) Text DropDownStyle Other Properties
    Label Label   Processed By   BackColor: Gray
    BorderStyle: FixedSingle
    Label Label   Employee #:    
    TextBox TextBox txtEmployeeNumber      
    TextBox TextBox txtEmployeeName      
    Label Label   Date Processed:    
    DateTimePicker Date Time Picker dtpDateProcessed      
    Label Label   Processed For   BackColor: Gray
    BorderStyle: FixedSingle
    Label Label   Customer Acnt #:    
    TextBox Text Box txtAccountNumber      
    TextBox TextBox txtCustomerName      
    Label Label   Room Assigned:    
    TextBox Text Box txtRoomNumber     Multine: True
    Scrollbars: Vertical
    TextBox Text Box txtRoomDetails      
    Label Label   Phone Charge    
    TextBox Text Box txtPhoneCharge      
    Label Label   Rate Applied    
    TextBox Text Box txtRateApplied     TextAlign: Right
    Label Label   Notes:    
    TextBox TextBox txtNotes     Multine: True
    Scrollbars: Vertical
    Form         AcceptButton: btnOK
    CancelButton: btnCancel
  8. On the form, click the txtEmployeeNumber text box
  9. In the Properties window, click Events and double Leave
  10. 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 CeilInn1
{
    public partial class RoomAssignment : Form
    {
        public RoomAssignment()
        {
            InitializeComponent();
        }

        private void txtEmployeeNumber_Leave(object sender, EventArgs e)
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

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

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdEmployees =
                   new SqlCommand("SELECT * FROM HumanResources.Employees " +
                          "WHERE EmployeeNumber = '" + txtEmployeeNumber.Text + "';",
                                           cntCeilInn);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("Employees");

                cntCeilInn.Open();

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

                foreach (DataRow drEmployee in dsEmployees.Tables[0].Rows)
                    txtEmployeeName.Text = (drEmployee["EmployeeName"].ToString());
            }
        }
    }
}
  11. Return to the form and click the txtAccountNumber text box
  12. In the Events section of the Properties window, double-click Leave
  13. Implement the event as follows: 
    private void txtAccountNumber_Leave(object sender, EventArgs e)
{
    // Specify the name of your server. Ours is named EXPRESSION
    string strServerName = "EXPRESSION";

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

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCustomers =
                   new SqlCommand("SELECT * FROM Hotel.Customers " +
                          "WHERE AccountNumber = '" + txtAccountNumber.Text + "';",
                                           cntCeilInn);
                SqlDataAdapter sdaCustomers = new SqlDataAdapter();
                DataSet dsCustomers = new DataSet("Customers");

                cntCeilInn.Open();

                sdaCustomers.SelectCommand = cmdCustomers;
                sdaCustomers.Fill(dsCustomers);

                foreach (DataRow drCustomer in dsCustomers.Tables[0].Rows)
                    txtCustomerName.Text = (drCustomer["CustomerName"].ToString());
            }
}
  14. Return to the form and click the txtRoomNumber text box
  15. In the Events section of the Properties window, double-Leave
  16. Implement the event as follows: 
    private void txtRoomNumber_Leave(object sender, EventArgs e)
{
    // Specify the name of your server. Ours is named EXPRESSION
     string strServerName = "EXPRESSION";

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

    using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
    {
        SqlCommand cmdCustomers =
                   new SqlCommand("SELECT * FROM Hotel.Rooms " +
                          "WHERE RoomNumber = '" + txtRoomNumber.Text + "';",
                                           cntCeilInn);
        SqlDataAdapter sdaRooms = new SqlDataAdapter();
        DataSet dsRooms = new DataSet("Rooms");

        cntCeilInn.Open();

        sdaRooms.SelectCommand = cmdCustomers;
        sdaRooms.Fill(dsRooms);

        foreach (DataRow drRoom in dsRooms.Tables[0].Rows)
            txtRoomDetails.Text = "Room Type: " + drRoom["RoomType"].ToString() + Environment.NewLine +
                                  "Bed Type:  " + drRoom["BedType"].ToString() + Environment.NewLine + 
                                  "Rate:      " + drRoom["Rate"].ToString();
    }
}
  17. On the main menu, click Project -> Add Windows Form...
  18. Set the name to RoomsAssignments
  19. Click Add
  20. Design the form as follows:
     
    Ceil Inn - Rooms Assignments
    Control (Name) Text Anchor
    DataGriView Data Grid View dgvRegistrations   Top, Bottom, Left, Right
    Button Button btnRoomAssignment New Room Assignment ... Bottom, Right
    Button Button btnClose Close Bottom, Right
  21. Double-click an unoccupied area of the form
  22. 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 CeilInn1
{
    public partial class RoomsAssignments : Form
    {
        public RoomsAssignments()
        {
            InitializeComponent();
        }

        private void ShowRoomsAssignments()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source='" + strServerName + "';" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdAssignments =
                            new SqlCommand("SELECT AssignmentNumber, ProcessedBy, DateOccupied, " +
                                           "       ProcessedFor, RoomNumber, RateApplied, PhoneCharge " +
                                           "FROM Room.Assignments; ",
                                           cntCeilInn);
                SqlDataAdapter sdaAssignments = new SqlDataAdapter();
                DataSet dsAssignments = new DataSet("Assignments");

                cntCeilInn.Open();

                sdaAssignments.SelectCommand = cmdAssignments;
                sdaAssignments.Fill(dsAssignments);

                dgvAssignments.DataSource = dsAssignments.Tables[0];
            }
        }

        private void RoomsAssignments_Load(object sender, EventArgs e)
        {
            ShowRoomsAssignments();
        }
    }
}
  23. Return to the form and double-click the New Room Assignment button
  24. Implement the event as follows: 
    private void btnRoomAssingment_Click(object sender, EventArgs e)
{
    string strServerName = "EXPRESSION";
    RoomAssignment ra = new RoomAssignment();

    if (ra.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(ra.txtEmployeeNumber.Text))
                {
                    MessageBox.Show("You must specify the employee who processed the transaction.",
                                    "Ceil Inn Hotel - Rooms Assignments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(ra.txtAccountNumber.Text))
                {
                    MessageBox.Show("You must specify the customer whose transaction was processed.",
                                    "Ceil Inn Hotel - Rooms Assignments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(ra.txtRoomNumber.Text))
                {
                    MessageBox.Show("You must specify the room number that was processed.",
                                    "Ceil Inn Hotel - Rooms Assignments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(ra.txtRateApplied.Text))
                {
                    MessageBox.Show("You must specify the rate that was applied.",
                                    "Ceil Inn Hotel - Rooms Assignments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntCeilInn =
                       new SqlConnection("Data Source='" + strServerName + "';" +
                                         "Database=CeilInn1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCeilInn =
                        new SqlCommand("INSERT INTO Room.Assignments( " +
                                       "ProcessedBy, DateOccupied, ProcessedFor, " +
                                       "RoomNumber, RateApplied, Notes) " +
                                       "VALUES('" + ra.txtEmployeeNumber.Text + "', '" +
                                         ra.dtpDateProcessed.Value + "', '" + ra.txtAccountNumber.Text +
                                         "', '" + ra.txtRoomNumber.Text + "', " + double.Parse(ra.txtRateApplied.Text) + ", " +
                                         ra.txtPhoneCharge.Text + ", '" + ra.txtNotes.Text + "');",
                                         cntCeilInn);

                    cntCeilInn.Open();
                    cmdCeilInn.ExecuteNonQuery();

                    MessageBox.Show("A new Room has been built.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowRoomsAssignments();
}
  25. Return to the form and double-click the Close button
  26. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
 
 
 

Payments

A conference room is usually rented for one day or one evening. A bedroom is usually rented for one or more nights. If a room (whether a conference room or a bedroom) is rented for one day or night, the day will be registed. The rate will be applied. At the end of the day, the customer can pay. If a room is rented for one whole night (the classic case of a bedroom), the employee will register the first day but no rate will be applied. Each subsequent day will be registered and a rate will be applied for that day. After the number of days the customer would have used the room, the number of days will be counted and the sum of daily rates made. The customer can then pay the total.

We will need or use the following pieces of information for each payment:

  • Receipt Number: A receipt should be given to a customer
  • Employee Number: This is the employee who processed the payment. This is usually the employee at the desk when the customer checked out
  • Payment Date: This is the date the payment was made. It may not be the checked out date (a customer could pay on the check-iin date or a few days after check-out)
  • Account Number: As mentioned previously, when a customer registers with the hotel, (s)he gets an account number. While the customer is using the room, this account number is used to keep track of both the customer's occupancy and phone use. This the reason we decide to create a new account every time, including a returning customer. When the customer decides to pay the bill, the account number holds a summary of his or her consumption
  • Amount Charged: This is the total resulting from the customer renting a conference room or occupying the bedroom
  • Tax Rate: The government needs to collect its due
  • Notes: Comments can be made in this field

Practical Learning: Making Payments
  1. Access the CeilInn.cs file and change the document 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 CeilInn1
{
    public partial class CeilInn : Form
    {
        public CeilInn()
        {
            InitializeComponent();
        }

        private void CreatePayments()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCeilInn =
                    new SqlCommand("CREATE TABLE Room.Payments" +
                                   "(" +
                                   "	ReceiptNumber int identity(1001, 1) NOT NULL," +
                                   "	ProcessedBy nvarchar(10)," +
                                   "	PaymentDate Date not null," +
                                   "	ProcessedFor nvarchar(12)," +
                                   "	AmountPaid money," +
                                   "	Notes nvarchar(max)," +
                                   "    Constraint PK_Payments Primary Key (ReceiptNumber)," +
                                   "    Constraint FK_Clerks Foreign Key(ProcessedBy) " +
                                   "        References HumanResources.Employees(EmployeeNumber), " +
                                   "    Constraint FK_Clents Foreign Key(ProcessedFor) " +
                                   "        References Hotel.Customers(AccountNumber) " +
                                   ");",
                                   cntCeilInn);
                cntCeilInn.Open();
                cmdCeilInn.ExecuteNonQuery();
            }

           MessageBox.Show("A table used to keep records of rooms payments has been created.",
                           "Ceil Inn - Hotel Rooms",
                           MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void CeilInn_Load(object sender, EventArgs e)
        {
            CreatePayments();
        }
    }
}
  2. Execute the application
  3. When the message box displays, click OK
  4. To launch a new form, on the main menu, Project -> Add Windows Form...
  5. Set the Name to NewPayment
  6. Click Add
  7. Design the form as follows:
     
    Ceil Inn - Room Payment
    Control (Name) Text DropDownStyle Other Properties
    Label Label   Processed By   BackColor: Gray
    BorderStyle: FixedSingle
    Label Label   Employee #:    
    TextBox TextBox txtEmployeeNumber      
    TextBox TextBox txtEmployeeName      
    Label Label   Payment Date:    
    DateTimePicker Date Time Picker dtpPaymentDate      
    Label Label   Processed For   BackColor: Gray
    BorderStyle: FixedSingle
    Label Label   Customer Acnt #:    
    TextBox Text Box txtAccountNumber      
    TextBox TextBox txtCustomerName      
    Label Label   Amount Paid:    
    TextBox Text Box txtAmountPaid     TextAlign: Right
    Label Label   Notes:    
    TextBox TextBox txtNotes     Multine: True
    Scrollbars: Vertical
    Form         AcceptButton: btnOK
    CancelButton: btnCancel
  8. On the form, click the txtEmployeeNumber text box
  9. In the Properties window, click Events and double Leave
  10. 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 CeilInn1
{
    public partial class NewPayment : Form
    {
        public RoomAssignment()
        {
            InitializeComponent();
        }

        private void txtEmployeeNumber_Leave(object sender, EventArgs e)
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

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

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdEmployees =
                   new SqlCommand("SELECT * FROM HumanResources.Employees " +
                          "WHERE EmployeeNumber = '" + txtEmployeeNumber.Text + "';",
                                           cntCeilInn);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("Employees");

                cntCeilInn.Open();

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

                foreach (DataRow drEmployee in dsEmployees.Tables[0].Rows)
                    txtEmployeeName.Text = (drEmployee["EmployeeName"].ToString());
            }
        }
    }
}
  11. Return to the form and click the txtAccountNumber text box
  12. In the Events section of the Properties window, double-click Leave
  13. Implement the event as follows: 
    private void txtAccountNumber_Leave(object sender, EventArgs e)
{
    // Specify the name of your server. Ours is named EXPRESSION
    string strServerName = "EXPRESSION";

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

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source=" + strServerName + ";" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdCustomers =
                   new SqlCommand("SELECT * FROM Hotel.Customers " +
                          "WHERE AccountNumber = '" + txtAccountNumber.Text + "';",
                                           cntCeilInn);
                SqlDataAdapter sdaCustomers = new SqlDataAdapter();
                DataSet dsCustomers = new DataSet("Customers");

                cntCeilInn.Open();

                sdaCustomers.SelectCommand = cmdCustomers;
                sdaCustomers.Fill(dsCustomers);

                foreach (DataRow drCustomer in dsCustomers.Tables[0].Rows)
                    txtCustomerName.Text = (drCustomer["CustomerName"].ToString());
            }
}
  14. On the main menu, click Project -> Add Windows Form...
  15. Set the name to Payments
  16. Click Add
  17. Design the form as follows:
     
    Ceil Inn - Rooms Payments
    Control (Name) Text Anchor
    DataGriView Data Grid View dgvPayments   Top, Bottom, Left, Right
    Button Button btnPayment New Payment ... Bottom, Right
    Button Button btnClose Close Bottom, Right
  18. Double-click an unoccupied area of the form
  19. 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 CeilInn1
{
    public partial class Payments : Form
    {
        public Payments()
        {
            InitializeComponent();
        }

        private void ShowPayments()
        {
            // Specify the name of your server. Ours is named EXPRESSION
            string strServerName = "EXPRESSION";

            using (SqlConnection cntCeilInn =
                new SqlConnection("Data Source='" + strServerName + "';" +
                                  "Database='CeilInn1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdPayments =
                            new SqlCommand("SELECT ReceiptNumber, ProcessedBy, PaymentDate, " +
                                           "       ProcessedFor, AmountPaid " +
                                           "FROM Room.Payments; ",
                                           cntCeilInn);
                SqlDataAdapter sdaPayments = new SqlDataAdapter();
                DataSet dsPayments = new DataSet("Payments");

                cntCeilInn.Open();

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

                dgvPayments.DataSource = dsPayments.Tables[0];
            }
        }

        private void Payments_Load(object sender, EventArgs e)
        {
            ShowPayments();
        }
    }
}
  20. Return to the form and double-click the New Room Assignment button
  21. Implement the event as follows: 
     private void btnPayment_Click(object sender, EventArgs e)
{
    string strServerName = "EXPRESSION";
    NewPayment pmt = new NewPayment();

    if (pmt.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
                if (string.IsNullOrEmpty(pmt.txtEmployeeNumber.Text))
                {
                    MessageBox.Show("You must specify the employee who processed the payment.",
                                    "Ceil Inn Hotel - Payments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(pmt.txtAccountNumber.Text))
                {
                    MessageBox.Show("You must specify the customer whose payment was processed.",
                                    "Ceil Inn Hotel - Payments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(pmt.txtAmountPaid.Text))
                {
                    MessageBox.Show("You must specify the amount that was payed.",
                                    "Ceil Inn Hotel - Payments",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                using (SqlConnection cntCeilInn =
                       new SqlConnection("Data Source='" + strServerName + "';" +
                                         "Database=CeilInn1;" +
                                         "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCeilInn =
                        new SqlCommand("INSERT INTO Room.Payments( " +
                                       "ProcessedBy, PaymentDate, ProcessedFor, " +
                                       "AmountPaid, RateApplied, Notes) " +
                                       "VALUES('" + pmt.txtEmployeeNumber.Text + "', '" +
                                         pmt.dtpPaymentDate.Value + "', '" + pmt.txtAccountNumber.Text +
                                         "', " + double.Parse(pmt.txtAmountPaid.Text) + pmt.txtNotes.Text + "');",
                                         cntCeilInn);

                    cntCeilInn.Open();
                    cmdCeilInn.ExecuteNonQuery();

                    MessageBox.Show("A new payment has been made.",
                                    "Ceil Inn Hotel - Customers",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

    ShowPayments();
}
  22. Return to the form and double-click the Close button
  23. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  24. Display the CeilInn Form
  25. Design it as follows:
     
    Ceil Inn Hotel - Switchboard
    Control (Name) Text
    Button Button btnCustomers Customers ...
    Button Button btnHotelRooms Hotel Rooms...
    Button Button btnRoomsAssignments Rooms Assignments ...
    Button Button btnPayments Payments...
    Button Button btnEmployees Employees...
    Button Button btnClose Close
  26. Double-click the Customers button
  27. Implement its event as follows: 
    private void btnCustomers_Click(object sender, EventArgs e)
        {
            Customers clients = new Customers();
            clients.Show();
        }
  28. Return to the form and double-click the Hotel Rooms button
  29. Implement its event as follows: 
    private void btnHotelRooms_Click(object sender, EventArgs e)
        {
            HotelRooms ht = new HotelRooms();
            ht.Show();
        }
  30. Return to the form and double-click the Rooms Assignments button
  31. Implement its event as follows: 
    private void btnRoomsAssignments_Click(object sender, EventArgs e)
        {
            RoomsAssignments ras = new RoomsAssignments();
            ras.Show();
        }
  32. Return to the form and double-click the Payments button
  33. Implement its event as follows: 
    private void btnPayments_Click(object sender, EventArgs e)
        {
            Payments pmts = new Payments();
            pmts.Show();
        }
  34. Return to the form and double-click the Employees button
  35. Implement its event as follows: 
    private void btnEmployees_Click(object sender, EventArgs e)
        {
            Employees empls = new Employees();
            empls.Show();
        }
  36. Return to the form and double-click the Close button
  37. Implement its event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }

 

Data Entry

Data entry of our hotel application should be classic. As examples to test the tables and form, we will provide sample values.

Practical Learning: Testing the Database
  1. Execute the application
  2. Click the the Employees button
  3. Click New Employee
  4. Create the following records:
     
    Employee # First Name Last Name Title
    22958 Andrew Laskin General Manager
    72947 Sheryl Shegger Intern
    27049 Harriett Dovecott Accounts Associate
    70429 Lynda Fore Shift Manager
    24095 Fred Barclay Accounts Associate
    28405 Peggy Thompson Accounts Associate
  5. Close the form
  6. Click the Customers button
  7. Click New Customer
  8. Create the following records:
     
    Account # First Name Last Name Phone Number Emergency Name Emergency Phone
    208405 Peter Carney 990-585-1886 Spencer Miles 990-750-8666
    204795 Juliette Beckins 410-944-1440 Bernard Brodsky 410-385-2235
    902840 Daniel Peters 624-802-1686 Grace Peters 877-490-9333
    383084 Peter Carney 990-585-1886 Spencer Miles 990-750-8666
    660820 Anne Sandt 953-172-9347 William Sandt 953-279-2475
    100752 Caroline Lomey 301-652-0700 Albert Lomey 301-412-5055
    608208 Alfred Owens 804-798-3257 Jane Owens 240-631-1445
    180204 Randy Whittaker 703-631-1200 Bryan Rattner 703-506-9200
    284085 Lucy Chen 425-979-7413 Edward Lamb 425-720-9247
    260482 Caroline Lomey 301-652-0700 Albert Lomey 301-412-5055
    474065 Peter Carney 990-585-1886 Spencer Miles 990-750-8666
    640800 Randy Whittaker 703-631-1200 Bryan Rattner 703-506-9200
    294209 Doris Wilson 703-416-0934 Gabriela Dawson 703-931-1000
    946090 Peter Carney 990-585-1886 Spencer Miles 990-750-8666
    629305 Joan Davids 202-789-0500 Rebecca Boiron 202-399-3600
    608502 Caroline Lomey 301-652-0700 Albert Lomey 301-412-5055
  9. Close the form
  10. Click Hotel Rooms
  11. Click New Room
  12. Create the following records:
     
    Room #ID Room Type Bed Type Rate Room Status
    101 Bedroom Queen 85.95  
    102 Bedroom Double 92.50 Available
    103 Studio Double 112.50  
    104 Bedroom Queen 85.95 Available
    105 Bedroom King 98.75 Available
    106 Bedroom Queen 85.95 Available
    107 Bedroom King 98.75 Available
    108 Bedroom Queen 85.95 Available
    110 Conference   450.00 Available
    112 Conference   650.00 Available
    114 Bedroom King 98.75 Available
    115 Bedroom King 98.75 Available
    116 Bedroom Queen 85.95 Available
    117 Bedroom Queen 85.95 Available
    118 Bedroom King 98.75 Available
    120 Studio King 124.95 Available
    122 Conference   725.00  
    125 Bedroom King 95.50 Available
    126 Studio King 124.95 Available
    127 Bedroom Double 92.50 Available
    128 Bedroom Double 92.50 Available
    130 Conference   500.00 Available
    201 Bedroom Double 92.50 Available
    202 Studio King 124.95  
    203 Studio Queen 94.50 Available
    204 Bedroom Double 96.50 Available
    205 Bedroom Queen 85.95 Available
    206 Bedroom King 92.75  
    207 Bedroom Queen 85.95 Available
    208 Bedroom Queen 85.95 Available
    209 Bedroom King 92.75 Available
    210 Studio Double 112.50  
  13. Close the form
  14. Click Rooms Assignments
  15. Click New Room Assignment
  16. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    24095 100752 Monday, January 02, 2012 106

    		The customer is here for a conference and will occupy the room for a week.

    		100752 Tuesday, January 03, 2012 106 85.95


    		100752 Wednesday, January 04, 2012 106 85.95


    		100752 Thursday, January 05, 2012 106 85.95

    28405 100752 Friday, January 06, 2012 106 85.95

  17. Close the form
  18. Re-open the Customers form to see its first record
  19. Close the Customers form
  20. Click Payments
  21. Click New Payment
  22. Create a record as follows:
    Payment Date: Friday, January 06, 2012
    Employee #: 28405
    Customer Acnt #: 100752
    Amount Charged: 343.80
  23. Close the New Payment form
  24. Open the Rooms Assignments form
  25. Click New Room Assignment
  26. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    28405 946090 Saturday, January 07, 2012 114
    		3.55
    28405 474065 Saturday, January 07, 2012 110 450.00
    		The room is rented for an on-going conference
    24095 204795 Sunday, January 07, 2012 104  

    27049 204795 Saturday, January 08, 2012 104 85.95

    28405 946090 Sunday, January 08, 2012 114 98.95 18.86
  27.  Close the New Room Assignment form
  28. Open the New Payment form and create a record as follows:
    Payment Date: Friday, January 08, 2012
    Employee #: 28405
    Customer Acnt #: 946090
    Amount Charged: 98.95
  29. Create another record as follows:
    Payment Date: Friday, January 08, 2012
    Employee #: 24095
    Customer Acnt #: 204795
    Amount Charged: 85.95
  30. Close the New Payment form
  31. Open the New Room Assignment form
  32. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    24095 208405 Monday, January 09, 2012 203      
    24095 284085 Monday, January 09, 2012 106


    24095 294209 Monday, January 09, 2012 205


  33. Close the New Room Assignment form
  34. Open the New Payment form and create a record as follows:
    Payment Date: Friday, January 09, 2012
    Employee #: 70429
    Customer Acnt #: 474065
    Amount Charged: 450.00
  35. Close the New Payment form
  36. Open the New Room Assignment form
  37. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    24095 208405 Monday, January 09, 2012 203      
    24095 284085 Monday, January 09, 2012 106      
    24095 294209 Monday, January 09, 2012 205      
      208405 Tuesday, January 10, 2012 203 94.50   The customer is here for a conference and will occupy the room for a week.
      284085 Tuesday, January 10, 2012 106 85.95    
      294209 Tuesday, January 10, 2012 205 85.95    
      208405 Wednesday, January 11, 2012 203 94.50 2.25  
      284085 Wednesday, January 11, 2012 106 85.95    
      294209 Wednesday, January 11, 2012 205 85.95    
      208405 Thursday, January 12, 2012 203 94.50    
      284085 Thursday, January 12, 2012 106 85.95 3.15  
      294209 Thursday, January 12, 2012 205 85.95    
      208405 Friday, January 13, 2012 203 94.50 4.05  
      284085 Friday, January 13, 2012 106 85.95 5.52  
      294209 Friday, January 13, 2012 205 85.95    
  38. Close the New Room Assignment form
  39. Open the New Payment form
  40. Create the following records:
     
    Processed By Payment Date Account #    
    28405 Saturday, January 14, 2012 208405 472.50
    28405 Saturday, January 14, 2012 284085 387.92
    24095 Saturday, January 14, 2012 294209 379.25 The customer was charged $300
  41. Close the New Payment form
  42. Open the New Room Assignment form
  43. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    28405 208405 Saturday, January 14, 2012 203 94.50

    70429 383084 Saturday, January 14, 2012 112 650.00 22.64 The conference room is rented for a wedding party
    28405 284085 Saturday, January 14, 2012 106 85.95    
    24095 294209 Saturday, January 14, 2012 205 85.95    
    28405 902840 Saturday, January 14, 2012 107      
      608502 Saturday, January 14, 2012 120   4.26  
      180204 Saturday, January 14, 2012 126      
      629305 Saturday, January 14, 2012 122 725.00    
    28405 660820 Saturday, January 14, 2012 105      
  44. Close the New Room Assignment form
  45. Re-open the New Payment form
  46. Create the following record:
     
    Processed By Payment Date Account #   Notes
    70429 Saturday, January 14, 2012 383084 670.00 The customer was charged a flat rate of $670 for the conference rom.
  47. Close the New Payment form
  48. Open the New Room Assingment form
  49. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    28405 208405 Saturday, January 14, 2012 203 94.50

    70429 383084 Saturday, January 14, 2012 112 650.00 22.64 The conference room is rented for a wedding party
    28405 284085 Saturday, January 14, 2012 106 85.95

    24095 294209 Saturday, January 14, 2012 205 85.95

    28405 902840 Saturday, January 14, 2012 107



    		608502 Saturday, January 14, 2012 120
    		4.26

    		180204 Saturday, January 14, 2012 126



    		629305 Saturday, January 14, 2012 122 725.00

    28405 660820 Saturday, January 14, 2012 105


  50. Close the New Room Assignment form
  51. Re-open the New Payment form
  52. Create the following records:
     
    Processed By Payment Date Account #  
    70429 Sunday, January 15, 2012 260482 450.00
  53. Close the New Payment form
  54. Open the New Room Assingnment form
  55. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    27049 902840 Sunday, January 15, 2012 107 85.75    
    27049 608502 Sunday, January 15, 2012 120 98.85 8.48  
    28405 180204 Sunday, January 15, 2012 126 98.85    
    70429 260482 Sunday, January 15, 2012 110 450.00    
      660820 Sunday, January 15, 2012 105 92.75    
    28405 660820 Monday, January 16, 2012 105 92.75    
    28405 608502 Wednesday, January 18, 2012 114      
  56. Close the New Room Assignment form
  57. Re-open the New Payment form
  58. Create the following record:
     
    Processed By Payment Date Account #  
    28405 Wednesday, January 18, 2012 180204 98.85
  59. Close the New Payment form
  60. Open the New Room Assignment form
  61. Create the following records:
     
    Employee # Customer Acnt #: Date Occupied Room # Rate Applied Phone Charge Notes
    28405 608502 Wednesday, January 18, 2012 114


    28405 608502 Thursday, January 19, 2012 114 92.75 6.82
    70429 608502 Friday, January 20, 2012 114 92.75


    		640800 Friday, January 20, 2012 204



    		640800 Saturday, January 21, 2012 204 96.60

    27049 640800 Sunday, January 22, 2012 204 96.60

  62. Close the New Room Assignment form
  63. Re-open the New Payment form
  64. Create the following record:
     
    Processed By Payment Date Account #  
    70429 Friday, January 27, 2012 608208 192.32
  65. Close the New Payment form
  66. Re-open the Customers form and see its records
  67. Close the Customers form
  68. Close the application

Download
 
 
   
 
Home Copyright © 2012 FunctionX Home