Introducing the Tables of a Join

Introduction

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. If needed, you can then create the necessary records for the table. Here is an example:

CREATE TABLE Majors
(
    MajorCode nchar(4) not null,
    MajorName nvarchar(50),
    CONSTRAINT PK_Majors PRIMARY KEY(MajorCode)
);
GO

INSERT INTO Majors(MajorCode, MajorName)
VALUES(N'CMSC', N'Computer Science'),(N'BIOL', N'Biology'),
      (N'ACCT', N'Accounting'), (N'ENGL', N'English');
GO

When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table. Here is an example:

CREATE TABLE Majors
(
    MajorCode nchar(4) not null,
    MajorName nvarchar(50),
    CONSTRAINT PK_Majors PRIMARY KEY(MajorCode)
);
GO
CREATE TABLE Students
(
    StudentNumber int identity not null, 
    FirstName nvarchar(20), 
    LastName nvarchar(20), 
    MajorCode nchar(4),
    CONSTRAINT PK_Students PRIMARY KEY(StudentNumber)
);
GO

INSERT INTO Majors(MajorCode, MajorName)
VALUES(N'CMSC', N'Computer Science'),(N'BIOL', N'Biology'),
      (N'ACCT', N'Accounting'), (N'ENGL', N'English');
GO
INSERT INTO Students(FirstName, LastName, MajorCode)
VALUES(N'John', N'Franks', N'ACCT'), (N'Peter', N'Sonnens', N'BIOL'),
      (N'Leslie', N'Aronson', NULL), (N'Mary', N'Shamberg', N'CMSC'),
      (N'Chryssa', N'Lurie', N'CMSC'), (N'Hellah', N'Zanogh', N'ENGL'),
      (N'Olympia', N'Sumners', N'CMSC'), (N'Roberta', N'Jerseys', N'CMSC'),
      (N'Helène', N'Campo', NULL), (NULL, N'Millam', N'ACCT'), 
      (NULL, N'Hessia', N'ENGL'), (N'Stanley', N'Webbs', N'CMSC'), 
      (N'Arnie', N'Ephron', N'ENGL'), (N'Mike', N'Pastore', N'ACCT'), 
      (NULL, N'Salim', NULL), (N'Mary', N'Shamberg', N'CMSC'), 
      (N'Chryssa', N'Lurie', N'CMSC'), (NULL, N'Millers', NULL), 
      (N'Robert', NULL, N'BIOL');
GO

Notice that the table of Students contains 19 records including studing who have no major:

Records Selection

Practical LearningPractical Learning: Introducing the Tables of a Join

  1. Start Microsoft Visual Studio
  2. On the main menu of Microsoft Visual Studio, click File -> New -> Project...
  3. In the middle list, click ASP.NET Web Application (.NET Framework).
    Change the project Name to ApartmentsRentalManagement1
  4. Click OK
  5. In the dialog box, click the MVC icon and click OK
  6. To create a database:
    • If you will use a Microsoft SQL Server Database:
      1. Start SQL Server Management Studio and login
      2. Right-click the name of the computer and click New Query
      3. Type the following code:
        USE master;
        GO
        CREATE DATABASE ApartmentsRentalManagement;
        GO
        USE ApartmentsRentalManagement;
        GO
      4. To create the database, right-click inside the Query window and click Execute
      5. Press Ctrl + A to select everything in the Query Editor and press Delete
    • If you will use a local database:
      1. In the Solution Explorer of Microsoft Visual Studio, right-click App_Data -> Add -> New Item...
      2. In the middle frame of the Add New Item dialog box, click SQL Server Database.
        Change the Name to ApartmentsRentalManagement
      3. Click Add
      4. Type the following code:
      5. In the Solution Explorer, under App_Data, right-click ApartmentsRentalManagement and click Open
      6. In the Server Explorer, right-click ApartmentsRentalManagement and click New Query
  7. In both cases, type the following code:
    CREATE SCHEMA Management;
    GO
    CREATE SCHEMA HumanResources;
    GO
    
    CREATE TABLE HumanResources.Employees
    (
    	EmployeeID	   INT IDENTITY(1, 1),
    	EmployeeNumber NVARCHAR(10),
    	FirstName      NVARCHAR(20),
    	LastName       NVARCHAR(20),
    	EmploymentTitle	       NVARCHAR(50), 
    	CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID)
    );
    GO
    CREATE TABLE Management.Apartments
    (
    	ApartmentID	    INT IDENTITY(1, 1),
    	UnitNumber	    NVARCHAR(5),
    	Bedrooms	    TINYINT,
    	Bathrooms	    TINYINT,
    	MonthlyRate	    INT,
    	SecurityDeposit	INT,
    	OccupancyStatus NVARCHAR(25),
    	CONSTRAINT PK_Apartments PRIMARY KEY(ApartmentID)
    );
    GO
    CREATE TABLE Management.RentContracts
    (
    	RentContractID	 INT IDENTITY(1, 1),
    	ContractNumber	 INT,
    	EmployeeID	     INT,
    	ContractDate	 DATE,
    	FirstName		 NVARCHAR(20),
    	LastName		 NVARCHAR(20),
    	MaritalStatus    NVARCHAR(25),
    	NumberOfChildren TINYINT,
    	ApartmentID	     INT,
    	RentStartDate	 DATE,
    	CONSTRAINT FK_Registrars FOREIGN KEY(EmployeeID)  REFERENCES HumanResources.Employees(EmployeeID),
    	CONSTRAINT FK_Apartments FOREIGN KEY(ApartmentID) REFERENCES Management.Apartments(ApartmentID),
    	CONSTRAINT PK_Contracts  PRIMARY KEY(RentContractID)
    );
    GO
    CREATE TABLE Management.Payments
    (
    	PaymentID	   INT IDENTITY(1, 1),
    	ReceiptNumber  INT,
    	EmployeeID	   INT,
    	RentContractID INT,
    	PaymentDate    DATE,
    	Amount	       INT,
    	Notes	       NVARCHAR(MAX) NOT NULL,
    	CONSTRAINT FK_ProcessedBy FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID),
    	CONSTRAINT FK_Contracts   FOREIGN KEY(RentContractID) REFERENCES Management.RentContracts(RentContractID),
    	CONSTRAINT PK_Payments    PRIMARY KEY(PaymentID)
    );
    GO
  8. To execute, on the SQL Editor toolbar, click the Execute button Execute
    If you are using Microsoft SQL Server for your database:
    • In the Object Explorer, right-click Databases and click Refresh
    • In the Object Explorer, double-click ApartmentsRentalManagement to expand it
    • In the Object Explorer, below ApartmentsRentalManagement, right-click Database Diagram and click Install Diagram Support
    • Read the message and click Yes
    • In the Object Explorer, below ApartmentsRentalManagement, right-click Database Diagram and click New Database Diagram
    • In the Add Table dialog box, double-click each table to add them
    • On the dialog box, click Close

      Accessing the Members of the Array

    • Close the diagram window
    • When asked whether you want to save it, click Yes
    • Type dgmApartmentsRentalManagement as the name of the file
    • Click OK
  9. Close the Query window
  10. When asked whether you want to save, click No
  11. In the Solution Explorer of Microsoft Visual Studio, double-click the very bottom Web.config file
    • If you created your database in Microsoft SQL Server Management Studio, create connection string as follows:
      <?xml version="1.0" encoding="utf-8"?>
      <!--
        For more information on how to configure your ASP.NET application, please visit
        https://go.microsoft.com/fwlink/?LinkId=301880
        -->
      <configuration>
        <appSettings>
          <add key="webpages:Version" value="3.0.0.0"/>
          <add key="webpages:Enabled" value="false"/>
          <add key="ClientValidationEnabled" value="true"/>
          <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
        </appSettings>
        <system.web>
          <compilation debug="true" targetFramework="4.6.1"/>
          <httpRuntime targetFramework="4.6.1"/>
          <httpModules>
            <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
          </httpModules>
        </system.web>
        <connectionStrings>
          <add name="csApartmentsRentalManagement"
               connectionString="Data Source=(local); Database='ApartmentsRentalManagement'; Integrated Security=True;MultipleActiveResultSets=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
    • If you are using a local database, create a connection string section as follows:
      <?xml version="1.0" encoding="utf-8"?>
      <!--
        For more information on how to configure your ASP.NET application, please visit
        https://go.microsoft.com/fwlink/?LinkId=301880
        -->
      <configuration>
        <appSettings>
          <add key="webpages:Version" value="3.0.0.0"/>
          <add key="webpages:Enabled" value="false"/>
          <add key="ClientValidationEnabled" value="true"/>
          <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
        </appSettings>
        <system.web>
          <compilation debug="true" targetFramework="4.6.1"/>
          <httpRuntime targetFramework="4.6.1"/>
          <httpModules>
            <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
          </httpModules>
        </system.web>
        <connectionStrings>
          <add name="csApartmentsRentalManagement"
               connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ApartmentsRentalManagement.mdf;Initial Catalog=ApartmentsRentalManagement;Integrated Security=True"
               providerName="System.Data.SqlClient" />
        </connectionStrings>
      
        . . . No Change
      </configuration>
  12. In the Solution Explorer, right-click ApartmentsRentalManagement -> Add -> New Folder
  13. Type Images
  14. Copy the follwing photo to the Images folder:

    Apartments Rental Management

  15. In the Solution Explorer, right-click Content -> Add -> Style Sheet
  16. Type RentManagement
  17. Press Enter
  18. Add the following formats:
    body {
        background-color: #FFF;
    }
    
    .bold              { font-weight:      600;     }
    .blue              { color:            #286090; }
    .top-padding       { padding-top:      0.50em;  }
    .top-banner        { top:              0;
                         left:             0;
                         right:            0;
                         z-index:          1050;
                         height:           20em;
                         position:         fixed;
                         background-image: url(/Images/arm1.png); }
    .jumbotron         { background-color: white;                 }
    .common-font       { font-family:      Georgia, Garamond, 'Times New Roman', serif; }
    .navbar-top-fixed  { left:             0;
                         right:            0;
                         top:              20em;
                         z-index:          1100;
                         position:         fixed;
                         border-width:     0 0 1px; }
    .navbar-top-fixed .navbar-collapse {  max-height: 340px; }
    
    @media (min-width: 768px) {
        .navbar-top-fixed .navbar-collapse {
            padding-right: 0;
            padding-left: 0;
        }
    }
    
    @media (max-device-width: 480px) and (orientation: landscape) {
        .navbar-top-fixed .navbar-collapse {
            max-height: 200px;
        }
    }
    
    @media (min-width: 768px) {
        .navbar-top-fixed {
            border-radius: 0;
        }
    }
  19. In the Solution Explorer, right-click Models -> Add -> Class...
  20. Type Apartment
  21. Click Add
  22. Change the document as follows:
    using System.ComponentModel.DataAnnotations;
    
    namespace ApartmentsRentalManagement1.Models
    {
        public class Apartment
        {
            [Display(Name = "Apartment ID")]
            public int ApartmentID        { get; set; }
            [Display(Name = "Unit #")]
            public string UnitNumber      { get; set; }
            public int Bedrooms           { get; set; }
            public int Bathrooms          { get; set; }
            [Display(Name = "Monthly Rate")]
            public int MonthlyRate        { get; set; }
            [Display(Name = "Deposit")]
            public int SecurityDeposit    { get; set; }
            [Display(Name = "Occupancy Status")]
            public string OccupancyStatus { get; set; }
    
            public string Residence
            {
                get
                {
                    string beds = Bedrooms + " bedrooms";
                    string baths = Bathrooms + " bathrooms";
    
                    if (Bedrooms == 1)
                        beds = Bedrooms + " bedroom";
                    if (Bathrooms == 1)
                        baths = Bedrooms + " bathroom";
    
                    return UnitNumber + " - " + beds + ", " + baths + ", rent = " + 
                           MonthlyRate.ToString() + "/month, deposit = " + 
                           SecurityDeposit.ToString() + ", " + OccupancyStatus;
                }
            }
        }
    }
  23. In the Solution Explorer, right-click Models -> Add -> Class...
  24. Type Employee
  25. Click Add
  26. Create the class as follows:
    using System.ComponentModel.DataAnnotations;
    
    namespace ApartmentsRentalManagement1.Models
    {
        public class Employee
        {
            [Display(Name = "Employee ID")]
            public int EmployeeID         { get; set; }
            [Display(Name = "Employee #")]
            public string EmployeeNumber  { get; set; }
            [Display(Name = "First Name")]
            public string FirstName       { get; set; }
            [Display(Name = "Last Name")]
            public string LastName        { get; set; }
            [Display(Name = "Employment Title")]
            public string EmploymentTitle { get; set; }
    
            public string Identification
            {
                get
                {
                    return EmployeeNumber + " - " + FirstName + " " + LastName + " (" + EmploymentTitle + ")";
                }
            }
        }
    }
  27. In the Solution Explorer, right-click Models -> Add -> Class...
  28. Type RentContract
  29. Press Enter
  30. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.ComponentModel.DataAnnotations;
    
    namespace ApartmentsRentalManagement1.Models
    {
        public class RentContract
        {
            [Display(Name = "Rent Contract ID")]
            public int RentContractID     { get; set; }
            [Display(Name = "Contract #")]
            public int ContractNumber     { get; set; }
            [Display(Name = "Employee ID")]
            public int EmployeeID         { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Contract Date")]
            public DateTime ContractDate  { get; set; }
            [Display(Name = "First Name")]
            public string FirstName       { get; set; }
            [Display(Name = "Last Name")]
            public string LastName        { get; set; }
            [Display(Name = "Marital Status")]
            public string MaritalStatus   { get; set; }
            [Display(Name = "Children")]
            public int NumberOfChildren   { get; set; }
            [Display(Name = "Apartment")]
            public int ApartmentID        { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Rent Start Date")]
            public DateTime RentStartDate { get; set; }
    
            public string Description
            {
                get
                {
                    return ContractNumber + " - " + FirstName + " " + LastName + " (renting since " + RentStartDate + ")";
                }
            }
        }
    }
  31. In the Solution Explorer, right-click Models -> Add -> Class...
  32. Type Payment
  33. Click Add
  34. Create the class as follows:
    using System;
    using System.ComponentModel.DataAnnotations;
    
    namespace ApartmentsRentalManagement1.Models
    {
        public class Payment
        {
            [Display(Name = "Payment ID")]
            public int PaymentID        { get; set; }
            [Display(Name = "Receipt #")]
            public int ReceiptNumber    { get; set; }
            [Display(Name = "Employee ID")]
            public int EmployeeID       { get; set; }
            [Display(Name = "Rent Contract ID")]
            public int RentContractID   { get; set; }
            [DataType(DataType.Date)]
            [Display(Name = "Payment Date")]
            public DateTime PaymentDate { get; set; }
            public int Amount           { get; set; }
            public string Notes         { get; set; }
        }
    }
  35. In the Solution Explorer, right-click Models -> Add -> Class...
  36. Type BusinessObjects as the name of the class
  37. Click Add
  38. Completet the class as follows:
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    
    namespace ApartmentsRentalManagement1.Models
    {
        public class BusinessObjects
        {
            public List<Apartment> GetApartments()
            {
                List<Apartment> apartments = new List<Apartment>();
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
                                                              "       Bathrooms, MonthlyRate, " +
                                                              "       SecurityDeposit, OccupancyStatus " +
                                                              "FROM Management.Apartments;",
                                                              scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdApartments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
                    DataSet dsApartments = new DataSet("apartments");
    
                    sdaApartments.Fill(dsApartments);
    
                    for (int i = 0; i < dsApartments.Tables[0].Rows.Count; i++)
                    {
                        DataRow drApartment = dsApartments.Tables[0].Rows[i];
    
                        Apartment unit = new Apartment()
                        {
                            ApartmentID = int.Parse(drApartment[0].ToString()),
                            UnitNumber = drApartment[1].ToString(),
                            Bedrooms = int.Parse(drApartment[2].ToString()),
                            Bathrooms = int.Parse(drApartment[3].ToString()),
                            MonthlyRate = int.Parse(drApartment[4].ToString()),
                            SecurityDeposit = int.Parse(drApartment[5].ToString()),
                            OccupancyStatus = drApartment[6].ToString()
                        };
    
                        apartments.Add(unit);
                    }
                }
    
                return apartments;
            }
    
            public List<Employee> GetEmployees()
            {
                List<Employee> employees = new List<Employee>();
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdEmployees = new SqlCommand("SELECT EmployeeID, EmployeeNumber, " +
                                                             "       FirstName, LastName, EmploymentTitle " +
                                                             "FROM HumanResources.Employees;",
                                                             scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdEmployees.ExecuteNonQuery();
    
                    SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
                    DataSet dsEmployees = new DataSet("employees");
    
                    sdaEmployees.Fill(dsEmployees);
    
                    Employee staff = null;
    
                    for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
                    {
                        DataRow drEmployee = dsEmployees.Tables[0].Rows[i];
    
                        staff = new Employee()
                        {
                            EmployeeID = int.Parse(drEmployee[0].ToString()),
                            EmployeeNumber = drEmployee[1].ToString(),
                            FirstName = drEmployee[2].ToString(),
                            LastName = drEmployee[3].ToString(),
                            EmploymentTitle = drEmployee[4].ToString()
                        };
    
                        employees.Add(staff);
                    }
                }
    
                return employees;
            }
    
            public Employee FindEmployee(int? id)
            {
                Employee employee = null;
    
                foreach (var staff in GetEmployees())
                {
                    if (staff.EmployeeID == id)
                    {
                        employee = staff;
                        break;
                    }
                }
    
                return employee;
            }
    
            public List<RentContract> GetRentContracts()
            {
                List<RentContract> rentContracts = new List<RentContract>();
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdRentContracts = new SqlCommand("SELECT RentContractID, ContractNumber, EmployeeID, " +
                                                                 "       ContractDate, FirstName, LastName, " +
                                                                 "       MaritalStatus, NumberOfChildren, " +
                                                                 "       ApartmentID, RentStartDate " +
                                                                 "FROM   Management.RentContracts;",
                                                                 scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdRentContracts.ExecuteNonQuery();
    
                    SqlDataAdapter sdaRentContracts = new SqlDataAdapter(cmdRentContracts);
                    DataSet dsRentContracts = new DataSet("rent-contracts");
    
                    sdaRentContracts.Fill(dsRentContracts);
    
                    for (int i = 0; i < dsRentContracts.Tables[0].Rows.Count; i++)
                    {
                        RentContract contract = new RentContract()
                        {
                            RentContractID = int.Parse(dsRentContracts.Tables[0].Rows[i][0].ToString()),
                            ContractNumber = int.Parse(dsRentContracts.Tables[0].Rows[i][1].ToString()),
                            EmployeeID = int.Parse(dsRentContracts.Tables[0].Rows[i][2].ToString()),
                            ContractDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[i][3].ToString()),
                            FirstName = dsRentContracts.Tables[0].Rows[i][4].ToString(),
                            LastName = dsRentContracts.Tables[0].Rows[i][5].ToString(),
                            MaritalStatus = dsRentContracts.Tables[0].Rows[i][6].ToString(),
                            NumberOfChildren = int.Parse(dsRentContracts.Tables[0].Rows[i][7].ToString()),
                            ApartmentID = int.Parse(dsRentContracts.Tables[0].Rows[i][8].ToString()),
                            RentStartDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[i][9].ToString())
                        };
    
                        rentContracts.Add(contract);
                    }
                }
    
                return rentContracts;
            }
    
            public RentContract FindRentContract(int? id)
            {
                RentContract contract = null;
    
                foreach (var rent in GetRentContracts())
                {
                    if (rent.RentContractID == id)
                    {
                        contract = rent;
                        break;
                    }
                }
    
                return contract;
            }
    
            public List<Payment> GetPayments()
            {
                List<Payment> payments = new List<Payment>();
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, ReceiptNumber, EmployeeID, " +
                                                            "       RentContractID, PaymentDate, " +
                                                            "       Amount, Notes " +
                                                            "FROM   Management.Payments;",
                                                            scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdPayments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaPayments = new SqlDataAdapter(cmdPayments);
                    DataSet dsPayments = new DataSet("payments");
    
                    sdaPayments.Fill(dsPayments);
    
                    for (int i = 0; i < dsPayments.Tables[0].Rows.Count; i++)
                    {
                        payments.Add(new Payment()
                        {
                            PaymentID = int.Parse(dsPayments.Tables[0].Rows[i][0].ToString()),
                            ReceiptNumber = int.Parse(dsPayments.Tables[0].Rows[i][1].ToString()),
                            EmployeeID = int.Parse(dsPayments.Tables[0].Rows[i][2].ToString()),
                            RentContractID = int.Parse(dsPayments.Tables[0].Rows[i][3].ToString()),
                            PaymentDate = DateTime.Parse(dsPayments.Tables[0].Rows[i][4].ToString()),
                            Amount = int.Parse(dsPayments.Tables[0].Rows[i][5].ToString()),
                            Notes = dsPayments.Tables[0].Rows[i][6].ToString()
                        });
                    }
                }
    
                return payments;
            }
    
            public Payment FindPayment(int? id)
            {
                Payment payment = null;
    
                foreach (var invoice in GetPayments())
                {
                    if (invoice.PaymentID == id)
                    {
                        payment = invoice;
                        break;
                    }
                }
    
                return payment;
            }
        }
    }
  39. In the Solution Explorer, expand Views and expand Shared
  40. In the Solution Explorer, under Shared, double-click _Layout.cshtml to open it
  41. Change the document as follows:
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Apartment Rental Management :: @ViewBag.Title</title>
        @Styles.Render("~/Content/css")
        @Scripts.Render("~/bundles/modernizr")
    </head>
    <body>
        <div class="top-banner"></div>
        
        <div class="navbar navbar-inverse navbar-top-fixed">
            <div class="container">
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                    </button>
                    @Html.ActionLink("HOME", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
                </div>
                <div class="navbar-collapse collapse">
                    <ul class="nav navbar-nav">
                        <li>@Html.ActionLink("LEASE APPLICATION", "Index", "Home")</li>
                        <li>@Html.ActionLink("COMMUNITY", "Index", "Home")</li>
                        <li>@Html.ActionLink("FLOOR PLANS", "Index", "Home")</li>
                        <li>@Html.ActionLink("CAREERS", "Index", "Home")</li>
                        <li>@Html.ActionLink("RENT MANAGEMENT", "Index", "RentContracts")</li>
                        <li>@Html.ActionLink("ABOUT ARM", "About", "Home")</li>
                        <li>@Html.ActionLink("CONTACT US", "Contact", "Home")</li>
                    </ul>
                </div>
            </div>
        </div>
        <div class="container body-content">
            @RenderBody()
            <hr />
            <footer>
                <p class="text-center common-font blue">&copy; @DateTime.Now.Year - Apartment Rental Management</p>
            </footer>
        </div>
    
        @Scripts.Render("~/bundles/jquery")
        @Scripts.Render("~/bundles/bootstrap")
        @RenderSection("scripts", required: false)
    </body>
    </html>
  42. To save everything, on the main menu, click File and click Save All
  43. In the Solution Explorer, under Views and under Shared, right-click _Layout.cshtml and click Copy
  44. Still in the Solution Explorer, right-click Shared and click Paste
  45. Right-click _Layout - Copy.cshtml and click Rename
  46. Type _Management to get _Management.cshtml, and press Enter
  47. Double-click _Management.cshtml to open it
  48. Change the document as follows:
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Apartment Rental Management :: @ViewBag.Title</title>
        @Styles.Render("~/Content/css")
        @Scripts.Render("~/bundles/modernizr")
    </head>
    <body>
        <div class="navbar navbar-inverse navbar-fixed-top">
            <div class="container">
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                    </button>
                    @Html.ActionLink("Apartment Rental Management", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
                </div>
                <div class="navbar-collapse collapse">
                    <ul class="nav navbar-nav">
                        <li>@Html.ActionLink("Home", "Index", "Home")</li>
                        <li>@Html.ActionLink("Employees", "Index", "Employees")</li>
                        <li>@Html.ActionLink("Apartments", "Index", "Apartments")</li>
                        <li>@Html.ActionLink("Rent Contracts", "Index", "RentContracts")</li>
                        <li>@Html.ActionLink("Payments", "Index", "Payments")</li>
                        <li>@Html.ActionLink("About ARM", "About", "Home")</li>
                        <li>@Html.ActionLink("Contact Us", "Contact", "Home")</li>
                    </ul>
                </div>
            </div>
        </div>
        <div class="container body-content">
            @RenderBody()
            <hr />
            <footer>
                <p class="text-center common-font blue">&copy; @DateTime.Now.Year - Apartment Rental Management</p>
            </footer>
        </div>
    
        @Scripts.Render("~/bundles/jquery")
        @Scripts.Render("~/bundles/bootstrap")
        @RenderSection("scripts", required: false)
    </body>
    </html>
  49. In the Solution Explorer, under Views, expand Home, and double-click Index.cshtml
  50. In the document, delete one of the <p> lines below the jumbotron format. Here is an example:
    @{
        ViewBag.Title = "Home Page";
    }
    
    <div class="jumbotron">
        <h1>ASP.NET</h1>
        <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
    </div>
    
    <div class="row">
        <div class="col-md-4">
            <h2>Getting started</h2>
            <p>
                ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
                enables a clean separation of concerns and gives you full control over markup
                for enjoyable, agile development.
            </p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>Get more libraries</h2>
            <p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>Web Hosting</h2>
            <p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
        </div>
    </div>
  51. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  52. In the middle list of the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions
  53. Click Add
  54. Type Employees to get EmployeesController
  55. Click Add
  56. Change the class as follows:
    using System.Net;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using ApartmentsRentalManagement1.Models;
    
    namespace ApartmentsRentalManagement1.Controllers
    {
        public class EmployeesController : Controller
        {
            BusinessObjects objects = new BusinessObjects();
    
            // GET: Employees
            public ActionResult Index()
            {
                return View(objects.GetEmployees());
            }
    
            // GET: Employees/Details/5
            public ActionResult Details(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Employee employee = objects.FindEmployee(id);
    
                if (employee == null)
                {
                    return HttpNotFound();
                }
    
                return View(employee);
            }
    
            // GET: Employees/Create
            public ActionResult Create()
            {
                return View();
            }
    
            // POST: Employees/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdEmployees = new SqlCommand("INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName, LastName, EmploymentTitle) " +
                                                                 "VALUES(N'" + collection["EmployeeNumber"] + "', " +
                                                                 "       N'" + collection["FirstName"] + "', " +
                                                                 "       N'" + collection["LastName"] + "', " +
                                                                 "       N'" + collection["EmploymentTitle"] + "');",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdEmployees.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Employees/Edit/5
            public ActionResult Edit(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Employee employee = objects.FindEmployee(id);
    
                if (employee == null)
                {
                    return HttpNotFound();
                }
    
                return View(employee);
            }
    
            // POST: Employees/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdEmployees = new SqlCommand("UPDATE HumanResources.Employees           " +
                                                                 "SET    EmployeeNumber  = N'" + collection["EmployeeNumber"] + "', " +
                                                                 "       FirstName       = N'" + collection["FirstName"] + "', " +
                                                                 "       LastName        = N'" + collection["LastName"] + "', " +
                                                                 "       EmploymentTitle = N'" + collection["EmploymentTitle"] + "'  " +
                                                                 "WHERE  EmployeeID      =   " + id + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdEmployees.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Employees/Delete/5
            public ActionResult Delete(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Employee employee = objects.FindEmployee(id);
    
                if (employee == null)
                {
                    return HttpNotFound();
                }
    
                return View();
            }
    
            // POST: Employees/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdEmployees = new SqlCommand("DELETE FROM HumanResources.Employees " +
                                                                 "WHERE EmployeeID = " + id + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdEmployees.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  57. In the class, right-click Index() and click Add View...
  58. In the Add View dialog box, make sure the View Name text box is displaying Index.
    On the right side of the Use A Layout Page text box, click the Browser button Object Browser
  59. In the Project Folders tree list, expand Views and click Shared
  60. In the Contents of Folder list, click _Management.cshtml:

    Select a Layout Page

  61. Click OK

    Add View

  62. Click Add
  63. Create the webpage as follows:
    @model IEnumerable<ApartmentsRentalManagement1.Models.Employee>
    
    @{
        ViewBag.Title = "Employees";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Employees - Staff Members</h2>
    
    <table class="table table-hover common-font">
        <tr>
            <th>@Html.DisplayNameFor(model => model.EmployeeID)</th>
            <th>@Html.DisplayNameFor(model => model.EmployeeNumber)</th>
            <th>@Html.DisplayNameFor(model => model.FirstName)</th>
            <th>@Html.DisplayNameFor(model => model.LastName)</th>
            <th>@Html.DisplayNameFor(model => model.EmploymentTitle)</th>
            <th>@Html.ActionLink("Hire New Employee", "Create")</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
                <td>@Html.DisplayFor(modelItem => item.EmployeeNumber)</td>
                <td>@Html.DisplayFor(modelItem => item.FirstName)</td>
                <td>@Html.DisplayFor(modelItem => item.LastName)</td>
                <td>@Html.DisplayFor(modelItem => item.EmploymentTitle)</td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.EmployeeID }) |
                    @Html.ActionLink("Details", "Details", new { id = item.EmployeeID }) |
                    @Html.ActionLink("Delete", "Delete", new { id = item.EmployeeID })
                </td>
            </tr>
        }
    </table>
  64. In the Solution Explorer, under Views, right-click Employees -> Add -> View...
  65. In the View Name text box, type Details
  66. Make sure the Use A Layout Page text displays ~/Views/Shared/_Management.cshtml.
    Click Add
  67. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.Employee
    
    @{
        ViewBag.Title = "Employee Details";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Employee Details</h2>
    
    <hr />
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmploymentTitle)</dt>
            <dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
        </dl>
    </div>
    <p>
        @Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
        @Html.ActionLink("Employees", "Index")
    </p>
  68. In the Solution Explorer, under Views, right-click Employees -> Add -> View...
  69. Type Create
  70. Make sure the Use A Layout Page text displays ~/Views/Shared/_Management.cshtml.
    Click Add
  71. Create a form as follows:
    @{
        ViewBag.Title = "Employment Application";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Employment Application</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            <div class="form-group">
                <label for="emplNbr" class="control-label col-md-2">Employee #</label>
                <div class="col-md-10">
                    @Html.TextBox("EmployeeNumber", null, htmlAttributes: new { @class = "form-control", id = "emplNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="fName" class="control-label col-md-2">First Name</label>
                <div class="col-md-10">
                    @Html.TextBox("FirstName", null, htmlAttributes: new { @class = "form-control", id = "fName" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="lName" class="control-label col-md-2">Last Name</label>
                <div class="col-md-10">
                    @Html.TextBox("LastName", null, htmlAttributes: new { @class = "form-control", id = "lName" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="title" class="control-label col-md-2">Employment Title</label>
                <div class="col-md-10">
                    @Html.TextBox("EmploymentTitle", null, htmlAttributes: new { @class = "form-control", id = "title" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Employees", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Hire this Employee" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  72. In the Solution Explorer, under Views, right-click Employees -> Add -> View...
  73. Type Edit as the name of the view
  74. Make sure the Use A Layout Page text displays ~/Views/Shared/_Management.cshtml.
    Click Add
  75. Create a form as follows:
    @model ApartmentsRentalManagement1.Models.Employee
    
    @{
        Layout = "~/Views/Shared/_Management.cshtml";
        ViewBag.Title = "Edit/Update Employee Information";
    }
    
    <h2 class=" bold blue common-font">Edit/Update Employee Information</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            @Html.Hidden("EmployeeID")
    
            <div class="form-group">
                @Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.EmployeeNumber, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.EmploymentTitle, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.EmploymentTitle, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Employees", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Update Employee Record" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  76. In the Solution Explorer, under Views, right-click Employees -> Add -> View...
  77. Type Delete
  78. Make sure the Use A Layout Page text displays ~/Views/Shared/_Management.cshtml.
    Click Add
  79. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.Employee
    
    @{
        ViewBag.Title = "Deleting Employee Record";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Deleting Employee Record</h2>
    
    <hr />
    
    <h3 class="common-font blue">Are you sure you want to delete this employee's record?</h3>
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmploymentTitle)</dt>
            <dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
        </dl>
    
        @using (Html.BeginForm())
        {
            @Html.AntiForgeryToken()
    
            <div class="form-actions no-color">
                @Html.ActionLink("Employees", "Index") ::
                <input type="submit" value="Delete Employee Record" class="btn btn-primary" />
            </div>
        }
    </div>
  80. In the Solution Explorer, expand App_Start and double-click BundleConfig.cs
  81. Change the document as follows:
    using System.Web.Optimization;
    
    namespace ApartmentsRentalManagement1
    {
        public class BundleConfig
        {
            // For more information on bundling, visit https://go.microsoft.com/fwlink/?LinkId=301862
            public static void RegisterBundles(BundleCollection bundles)
            {
                bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                            "~/Scripts/jquery-{version}.js"));
    
                bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                            "~/Scripts/jquery.validate*"));
    
                // Use the development version of Modernizr to develop with and learn from. Then, when you're
                // ready for production, use the build tool at https://modernizr.com to pick only the tests you need.
                bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                            "~/Scripts/modernizr-*"));
    
                bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                          "~/Scripts/bootstrap.js"));
    
                bundles.Add(new StyleBundle("~/Content/css").Include(
                          "~/Content/bootstrap.css",
                          "~/Content/site.css",
                          "~/Content/RentManagement.css"));
            }
        }
    }
  82. To execute, on the main menu, click Debug -> Start Without Debugging

    Joins Fundamentals

  83. In the address bar, click on the right side of the right forward slash
  84. Type Employees and press Enter

    Joins Fundamentals

  85. In the webpage, click Hire New Employee

    Joins Fundamentals

  86. Create the following records:
     
    Employee # First Name Last Name Title
    93947 Catherine Watts Owner - General Manager
    40685 Justine Sandt Rent Manager
    73048 Raymond Wilkinson Intern
    60949 Mark Reason Maintenance Technician
    38408 Marc Knights Rent Associate
    20448 Nancy Longhorn Rent Associate
    INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName,LastName, EmploymentTitle)
    VALUES(N'93947', N'Catherine', N'Watts',	   N'Owner - General Manager'),
    	    (N'40685', N'Justine',	 N'Sandt',	   N'Rent Manager'),
    	    (N'73048', N'Raymond',	 N'Wilkinson', N'Intern'),
    	    (N'60949', N'Mark', 	 N'Reason',	   N'Maintenance Technician'),
    	    (N'38408', N'Marc', 	 N'Knights',   N'Rent Associate'),
    	    (N'20448', N'Nancy',	 N'Longhorn',  N'Rent Associate');
    GO

    Joins Fundamentals

  87. Close the browser and return to your programming environment
  88. Close the following tabs: _Layout.cshtml, _Management.cshtml, Index.cshtml, Delete.cshtml. Edit.cshtml, Employee.cs, Apartment.cs, RentContract.cs, Payment.cs, EmployeesController.cs, and BundleConfig.cs
  89. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  90. Make sure MVC 5 Controller With Read/Write Actions is selected
    Click Add
  91. Type Apartments to get ApartmentsController
  92. Click Add
  93. Change the controller as follows:
    using System.Net;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using ApartmentsRentalManagement1.Models;
    
    namespace ApartmentsRentalManagement1.Controllers
    {
        public class ApartmentsController : Controller
        {
            BusinessObjects objects = new BusinessObjects();
    
            // GET: Apartments
            public ActionResult Index()
            {
                List<Apartment> apartments = objects.GetApartments();
    
                return View(apartments);
            }
    
            // GET: Apartments/Details/5
            public ActionResult Details(int id)
            {
                Apartment residence = null;
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
                                                              "       Bathrooms, MonthlyRate, " +
                                                              "       SecurityDeposit, OccupancyStatus " +
                                                              "FROM Management.Apartments " +
                                                              "WHERE ApartmentID = " + id + ";",
                                                              scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdApartments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
                    DataSet dsApartments = new DataSet("apartment");
    
                    sdaApartments.Fill(dsApartments);
    
                    if (dsApartments.Tables[0].Rows.Count > 0)
                    {
                        residence = new Apartment()
                        {
                            ApartmentID     = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()),
                            UnitNumber      =           dsApartments.Tables[0].Rows[0][1].ToString(),
                            Bedrooms        = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()),
                            Bathrooms       = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()),
                            MonthlyRate     = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()),
                            SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()),
                            OccupancyStatus =           dsApartments.Tables[0].Rows[0][6].ToString()
                        };
                    }
                }
    
                return View(residence);
            }
    
            // GET: Apartments/Create
            public ActionResult Create()
            {
                List<SelectListItem> conditions = new List<SelectListItem>();
    
                conditions.Add(new SelectListItem() { Text = "Unknown",      Value = "Unknown"      });
                conditions.Add(new SelectListItem() { Text = "Occupied",     Value = "Occupied"     });
                conditions.Add(new SelectListItem() { Text = "Available",    Value = "Available"    });
                conditions.Add(new SelectListItem() { Text = "Not Ready",    Value = "Not Ready"    });
                conditions.Add(new SelectListItem() { Text = "Needs Maintenance", Value = "Needs Maintenance" });
    
                ViewBag.OccupancyStatus = conditions;
    
                return View();
            }
    
            // POST: Apartments/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdApartments = new SqlCommand("INSERT INTO Management.Apartments(UnitNumber, Bedrooms, Bathrooms, " +
                                                                  "                                  MonthlyRate, SecurityDeposit, " +
                                                                  "                                  OccupancyStatus) " +
                                                                 "VALUES(N'" + collection["UnitNumber"] + "', " + collection["Bedrooms"] +
                                                                 ", " + collection["Bathrooms"] + ", " + collection["MonthlyRate"] + ", " +
                                                                 collection["SecurityDeposit"] + ", N'" + collection["OccupancyStatus"] + "');",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdApartments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Apartments/Edit/5
            public ActionResult Edit(int id)
            {
                Apartment residence = null;
    
                using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                Configuration.
                                                                                ConfigurationManager.
                                                                                ConnectionStrings["csApartmentsRentalManagement"].
                                                                                ConnectionString))
                {
                    SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
                                                              "       Bathrooms, MonthlyRate, " +
                                                              "       SecurityDeposit, OccupancyStatus " +
                                                              "FROM Management.Apartments " +
                                                              "WHERE ApartmentID = " + id + ";",
                                                              scApartmentsManagement);
    
                    scApartmentsManagement.Open();
                    cmdApartments.ExecuteNonQuery();
    
                    SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
                    DataSet dsApartments = new DataSet("apartment");
    
                    sdaApartments.Fill(dsApartments);
    
                    if (dsApartments.Tables[0].Rows.Count > 0)
                    {
                        residence = new Apartment()
                        {
                            ApartmentID     = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()),
                            UnitNumber      =           dsApartments.Tables[0].Rows[0][1].ToString(),
                            Bedrooms        = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()),
                            Bathrooms       = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()),
                            MonthlyRate     = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()),
                            SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()),
                            OccupancyStatus =           dsApartments.Tables[0].Rows[0][6].ToString()
                        };
                    }
                }
    
                List<SelectListItem> conditions = new List<SelectListItem>
                {
                    new SelectListItem() { Text = "Unknown",      Value = "Unknown",      Selected = (residence.OccupancyStatus == "Unknown")      },
                    new SelectListItem() { Text = "Occupied",     Value = "Occupied",     Selected = (residence.OccupancyStatus == "Occupied")     },
                    new SelectListItem() { Text = "Available",    Value = "Available",    Selected = (residence.OccupancyStatus == "Available")    },
                    new SelectListItem() { Text = "Not Ready",    Value = "Not Ready",    Selected = (residence.OccupancyStatus == "Not Ready")    },
                    new SelectListItem() { Text = "Needs Maintenance", Value = "Needs Maintenance", Selected = (residence.OccupancyStatus == "Needs Maintenance") }
                };
    
                ViewBag.OccupancyStatus = conditions;
    
                return View(residence);
            }
    
            // POST: Apartments/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " +
                                                                  "SET   UnitNumber      = N'" + collection["UnitNumber"]      + "', " +
                                                                  "      Bedrooms        =   " + collection["Bedrooms"]        + ",  " +
                                                                  "      Bathrooms       =   " + collection["Bathrooms"]       + ",  " +
                                                                  "      MonthlyRate     =   " + collection["MonthlyRate"]     + ",  " +
                                                                  "      SecurityDeposit =   " + collection["SecurityDeposit"] + ",  " +
                                                                  "      OccupancyStatus = N'" + collection["OccupancyStatus"] + "'  " +
                                                                  "WHERE ApartmentID     =   " + id + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdApartments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Apartments/Delete/5
            public ActionResult Delete(int id)
            {
                Apartment residence = null;
    
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                 ConfigurationManager.
                                                                                 ConnectionStrings["csApartmentsRentalManagement"].
                                                                                 ConnectionString))
                {
                    SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
                                                              "       Bathrooms, MonthlyRate, " +
                                                              "       SecurityDeposit, OccupancyStatus " +
                                                              "FROM Management.Apartments " +
                                                              "WHERE ApartmentID = " + id + ";",
                                                              scRentManagement);
                    scRentManagement.Open();
    
                    SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
                    DataSet dsApartments = new DataSet("apartments");
    
                    sdaApartments.Fill(dsApartments);
    
                    if (dsApartments.Tables[0].Rows.Count > 0)
                    {
                        residence = new Apartment()
                        {
                            ApartmentID     = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()),
                            UnitNumber      =           dsApartments.Tables[0].Rows[0][1].ToString(),
                            Bedrooms        = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()),
                            Bathrooms       = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()),
                            MonthlyRate     = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()),
                            SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()),
                            OccupancyStatus =           dsApartments.Tables[0].Rows[0][6].ToString()
                        };
                    }
                }
    
                return residence == null ? HttpNotFound() : (ActionResult)View(residence);
            }
    
            // POST: Apartments/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdApartments = new SqlCommand("DELETE Management.Apartments " +
                                                                  "WHERE ApartmentID = " + id + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdApartments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  94. In the Solution Explorer, under Views, right-click Apartments -> Add -> View...
  95. Type Details
  96. n the class, right-click (int id) and click Add View...
  97. In the Add View dialog box, make sure the View Name text box displays Details. Also make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  98. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.Apartment
    
    @{
        ViewBag.Title = "Apartment Details";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Apartment Details</h2>
    
    <hr />
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
            <dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Bedrooms)</dt>
            <dd>@Html.DisplayFor(model => model.Bedrooms)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Bathrooms)</dt>
            <dd>@Html.DisplayFor(model => model.Bathrooms)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.MonthlyRate)</dt>
            <dd>@Html.DisplayFor(model => model.MonthlyRate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.SecurityDeposit)</dt>
            <dd>@Html.DisplayFor(model => model.SecurityDeposit)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.OccupancyStatus)</dt>
            <dd>@Html.DisplayFor(model => model.OccupancyStatus)</dd>
        </dl>
    </div>
    <p>
        @Html.ActionLink("Edit Rent Contract", "Edit", new { id = Model.ApartmentID }) |
        @Html.ActionLink("Rental Contracts", "Index")
    </p>
  99. In the Solution Explorer, under Views, right-click Apartments -> Add -> View...
  100. Type Create
  101. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  102. Create a form as follows:
    @{
        ViewBag.Title = "New Apartment";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">New Apartment</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            <div class="form-group">
                <label for="unitNbr" class="control-label col-md-2">Unit #:</label>
                <div class="col-md-10">
                    @Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="beds" class="control-label col-md-2">Bedrooms:</label>
                <div class="col-md-10">
                    @Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="baths" class="control-label col-md-2">Bathrooms:</label>
                <div class="col-md-10">
                    @Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="rentPerMonth" class="control-label col-md-2">Monthly Rate:</label>
                <div class="col-md-10">
                    @Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="deposit" class="control-label col-md-2">Security Deposit:</label>
                <div class="col-md-10">
                    @Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="occupancyStatus" class="control-label col-md-2">Occupancy Status:</label>
                <div class="col-md-10">
                    @Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Apartments Inventory", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Create Apartment Record" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  103. In the Solution Explorer, under Views, right-click Apartments -> Add -> View...
  104. Type Edit
  105. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Press Enter
  106. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.Apartment
    
    @{
        Layout = "~/Views/Shared/_Management.cshtml";
        ViewBag.Title = "Edit/Update Apartment Information";
    }
    
    <h2 class="bold common-font blue">Edit/Update Residence Details</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            <div class="form-group">
                <label for="unitNbr" class="control-label col-md-2">Unit #:</label>
                <div class="col-md-10">
                    @Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="beds" class="control-label col-md-2">Bedrooms:</label>
                <div class="col-md-10">
                    @Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="baths" class="control-label col-md-2">Bathrooms:</label>
                <div class="col-md-10">
                    @Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="rentPerMonth" class="control-label col-md-2">Monthly Rate:</label>
                <div class="col-md-10">
                    @Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="deposit" class="control-label col-md-2">Security Deposit:</label>
                <div class="col-md-10">
                    @Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="occupancyStatus" class="control-label col-md-2">Occupancy Status:</label>
                <div class="col-md-10">
                    @Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Apartments Inventory", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Update Apartment Information" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  107. In the Solution Explorer, under Views, right-click Apartments -> Add -> View...
  108. Type Delete
  109. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  110. Change the code as follows:
    @model ApartmentsRentalManagement1.Models.Apartment
    
    @{
        Layout = "~/Views/Shared/_Management.cshtml";
        ViewBag.Title = "Deleting Apartment/Residence";
    }
    
    <h2 class="bold common-font blue">Deleting Apartment/Residence</h2>
    
    <hr />
    
    <h3 class="common-font blue">Are you sure you want to delete this apartment's record?</h3>
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
            <dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.UnitNumber)</dt>
            <dd>@Html.DisplayFor(model => model.UnitNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Bedrooms)</dt>
            <dd>@Html.DisplayFor(model => model.Bedrooms)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Bathrooms)</dt>
            <dd>@Html.DisplayFor(model => model.Bathrooms)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.MonthlyRate)</dt>
            <dd>@Html.DisplayFor(model => model.MonthlyRate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.SecurityDeposit)</dt>
            <dd>@Html.DisplayFor(model => model.SecurityDeposit)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.OccupancyStatus)</dt>
            <dd>@Html.DisplayFor(model => model.OccupancyStatus)</dd>
        </dl>
    
        @using (Html.BeginForm())
        {
            @Html.AntiForgeryToken()
    
            <div class="form-actions no-color">
                @Html.ActionLink("Apartments", "Index") ::
                <input type="submit" value="Delete this Apartment's Record" class="btn btn-primary" />
            </div>
        }
    </div>
  111. Click the ApartmentsController tab to access it
  112. In the class, right-click Index and click Add View...
  113. In the Add View dialog box, make sure the View Name text box displays Index. Also make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  114. Change the document as follows:
    @model IEnumerable<ApartmentsRentalManagement1.Models.Apartment>
    
    @{
        ViewBag.Title = "Apartments";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Apartments</h2>
    
    <table class="table table-hover common-font">
        <tr>
            <th>@Html.DisplayNameFor(model => model.ApartmentID)</th>
            <th>@Html.DisplayNameFor(model => model.UnitNumber)</th>
            <th>@Html.DisplayNameFor(model => model.Bedrooms)</th>
            <th>@Html.DisplayNameFor(model => model.Bathrooms)</th>
            <th>@Html.DisplayNameFor(model => model.MonthlyRate)</th>
            <th>@Html.DisplayNameFor(model => model.SecurityDeposit)</th>
            <th>@Html.DisplayNameFor(model => model.OccupancyStatus)</th>
            <th>@Html.ActionLink("New Apartment", "Create")</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">@Html.DisplayFor(modelItem => item.ApartmentID)</td>
                <td>@Html.DisplayFor(modelItem => item.UnitNumber)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.Bedrooms)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.Bathrooms)</td>
                <td>@Html.DisplayFor(modelItem => item.MonthlyRate)</td>
                <td>@Html.DisplayFor(modelItem => item.SecurityDeposit)</td>
                <td>@Html.DisplayFor(modelItem => item.OccupancyStatus)</td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.ApartmentID }) |
                    @Html.ActionLink("Details", "Details", new { id = item.ApartmentID }) |
                    @Html.ActionLink("Delete", "Delete", new { id = item.ApartmentID })
                </td>
            </tr>
        }
    </table>
  115. To execute, on the main menu, click Debug -> Start Without Debugging

    Joins Fundamentals

  116. Click the New Apartment link

    Joins Fundamentals

  117. Create the following records:
     
    Unit # Bedrooms Bathrooms Monthly Rate Security Deposit Occupancy Status
    1012213501100Available
    102111150 850Needs Maintenance
    103111150 850Available
    1043215001250Available
    1052112501000 Not Ready
    1063215501250Available
    1073214501250Needs Maintenance
    108111100 850Available
    1092213501100Available
    110111050 850 Not Ready
    1112213501100Needs Maintenance
    1122112851000Available
    2012111851000 Not Ready
    202111150 850Available
    203111150 850Available
    2043216001250Available
    2052111001000Needs Maintenance
    2063215001250Available
    2073215501250Available
    20811 985 850Available
    2092213501100Available
    210111150 850 Not Ready
    2112213501100Available
    2122110751000Available
    3012211751000Available
    302111150 850Needs Maintenance
    303111100 850Available
    3043212501100Available
    3052111001000Available
    3063212501100Available
    3073211001250Available
    308111100 850Available
    309221100 950Available
    310111100 850Available
    3112211001000 Not Ready
    3122111001000Available
    INSERT INTO Management.Apartments(UnitNumber, Bedrooms, Bathrooms, MonthlyRate, SecurityDeposit, OccupancyStatus)
    VALUES(N'101', 2, 2, 1350, 1100, N'Available'),
          (N'102', 1, 1, 1150,  850, N'Needs Maintenance'),
          (N'103', 1, 1, 1150,  850, N'Available'),
          (N'104', 3, 2, 1500, 1250, N'Available'),
          (N'105', 2, 1, 1250, 1000, N'Not Ready'),
          (N'106', 3, 2, 1550, 1250, N'Available'),
          (N'107', 3, 2, 1450, 1250, N'Needs Maintenance'),
          (N'108', 1, 1, 1100,  850, N'Available'),
          (N'109', 2, 2, 1350, 1100, N'Available'),
          (N'110', 1, 1, 1050,  850, N'Unknown'),
          (N'111', 2, 2, 1350, 1100, N'Needs Maintenance'),
          (N'112', 2, 1, 1285, 1000, N'Available'),
          (N'201', 2, 1, 1185, 1000, N'Unknown'),
          (N'202', 1, 1, 1150,  850, N'Available'),
          (N'203', 1, 1, 1150,  850, N'Available'),
          (N'204', 3, 2, 1600, 1250, N'Available'),
          (N'205', 2, 1, 1100, 1000, N'Needs Maintenance'),
          (N'206', 3, 2, 1500, 1250, N'Available'),
          (N'207', 3, 2, 1550, 1250, N'Available'),
          (N'208', 1, 1,  985,  850, N'Available'),
          (N'209', 2, 2, 1350, 1100, N'Available'),
          (N'210', 1, 1, 1150,  850, N'Unknown'),
          (N'211', 2, 2, 1350, 1100, N'Available'),
          (N'212', 2, 1, 1075, 1000, N'Available'),
          (N'301', 2, 2, 1175, 1000, N'Available'),
          (N'302', 1, 1, 1150,  850, N'Needs Maintenance'),
          (N'303', 1, 1, 1100,  850, N'Available'),
          (N'304', 3, 2, 1250, 1100, N'Available'),
          (N'305', 2, 1, 1100, 1000, N'Needs Maintenance'),
          (N'306', 3, 2, 1250, 1100, N'Available'),
          (N'307', 3, 2, 1100, 1250, N'Available'),
          (N'308', 1, 1, 1100,  850, N'Available'),
          (N'309', 2, 2, 1100,  950, N'Available'),
          (N'310', 1, 1, 1100,  850, N'Available'),
          (N'311', 2, 2, 1100, 1000, N'Unknown'),
          (N'312', 2, 1, 1100, 1000, N'Available');
    GO

    Joins Fundamentals

  118. Close the browser and return to your programming environment
  119. Close the Index.cshtml, Delete.cshtml. Edit.cshtml, Create.cshtml, ApartmentsController.cs, and Details.cshtml tabs
  120. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  121. In the left frame of the Add Scaffold dialog box, click MVC and, in the middle frame, click MVC 5 Controller With Read/write Actions
  122. Click Add
  123. Type RentContracts to get RentContractsController
  124. Press Enter
  125. Change the class as follows:
    using System;
    using System.Net;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using ApartmentsRentalManagement1.Models;
    
    namespace ApartmentsRentalManagement1.Controllers
    {
        public class RentContractsController : Controller
        {
            BusinessObjects objects = new BusinessObjects();
    
            // GET: RentContracts
            public ActionResult Index()
            {
                return View(objects.GetRentContracts());
            }
    
            // GET: RentContracts/Details/5
            public ActionResult Details(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                RentContract contract = objects.FindRentContract(id);
    
                if (contract == null)
                {
                    return HttpNotFound();
                }
    
                return View(contract);
            }
    
            // GET: RentContracts/Create
            public ActionResult Create()
            {
                List<SelectListItem> maritals = new List<SelectListItem>
                {
                    new SelectListItem() { Text = "Unknown",   Value = "Unknown"   },
                    new SelectListItem() { Text = "Single",    Value = "Single"    },
                    new SelectListItem() { Text = "Widdow",    Value = "Widdow"    },
                    new SelectListItem() { Text = "Married",   Value = "Married"   },
                    new SelectListItem() { Text = "Divorced",  Value = "Divorced"  },
                    new SelectListItem() { Text = "Separated", Value = "Separated" }
                };
    
                ViewBag.MaritalStatus = maritals;
    
                ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence");
                ViewBag.EmployeeID  = new SelectList(objects.GetEmployees(),  "EmployeeID",  "Identification");
    
                return View();
            }
    
            // POST: RentContracts/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                    Configuration.
                                                                                    ConfigurationManager.
                                                                                    ConnectionStrings["csApartmentsRentalManagement"].
                                                                                    ConnectionString))
                    {
                        // This command is used to create a rental contract.
                        SqlCommand cmdRentContracts = new SqlCommand("INSERT INTO Management.RentContracts(ContractNumber, EmployeeID, " +
                                                                     "                                     ContractDate, FirstName, " +
                                                                     "                                     LastName, MaritalStatus, " +
                                                                     "                                     NumberOfChildren, ApartmentID, " +
                                                                     "                                     RentStartDate) " +
                                                                     "VALUES(" + collection["ContractNumber"] + ", " +
                                                                     collection["EmployeeID"] + ", N'" + collection["ContractDate"] +
                                                                     "', N'" + collection["FirstName"] + "', N'" + collection["LastName"] +
                                                                     "', N'" + collection["MaritalStatus"] + "', " +
                                                                     collection["NumberOfChildren"] + ", " + collection["ApartmentID"] +
                                                                     ", N'" + collection["RentStartDate"] + "');",
                                                                     scApartmentsManagement);
    
                        scApartmentsManagement.Open();
                        cmdRentContracts.ExecuteNonQuery();
                    }
    
                    /* When an apartment has been selected for a rental contract, 
                     * we must change the status of that apartment from Available to Occupied. */
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                    ConfigurationManager.
                                                                                    ConnectionStrings["csApartmentsRentalManagement"].
                                                                                    ConnectionString))
                    {
                        SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " +
                                                                  "SET   OccupancyStatus = N'Occupied'  " +
                                                                  "WHERE ApartmentID     =   " + collection["ApartmentID"] + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdApartments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: RentContracts/Edit/5
            public ActionResult Edit(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                RentContract contract = objects.FindRentContract(id);
    
                if (contract == null)
                {
                    return HttpNotFound();
                }
    
                List<SelectListItem> maritals = new List<SelectListItem>
                {
                    new SelectListItem() { Text = "Single",    Value = "Single",    Selected = (contract.MaritalStatus == "Single")    },
                    new SelectListItem() { Text = "Widdow",    Value = "Widdow",    Selected = (contract.MaritalStatus == "Widdow")    },
                    new SelectListItem() { Text = "Married",   Value = "Married",   Selected = (contract.MaritalStatus == "Married")   },
                    new SelectListItem() { Text = "Unknown",   Value = "Unknown",   Selected = (contract.MaritalStatus == "Unknown")   },
                    new SelectListItem() { Text = "Divorced",  Value = "Divorced",  Selected = (contract.MaritalStatus == "Divorced")  },
                    new SelectListItem() { Text = "Separated", Value = "Separated", Selected = (contract.MaritalStatus == "Separated") }
                };
    
                ViewBag.MaritalStatus = maritals;
    
                ViewBag.EmployeeID  = new SelectList(objects.GetEmployees(),  "EmployeeID",  "Identification", contract.EmployeeID);
                ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence",      contract.ApartmentID);
    
                return View(contract);
            }
    
            // POST: RentContracts/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    if (ModelState.IsValid)
                    {
                        using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                        Configuration.
                                                                                        ConfigurationManager.
                                                                                        ConnectionStrings["csApartmentsRentalManagement"].
                                                                                        ConnectionString))
                        {
                            string strUpdate = "UPDATE Management.RentContracts " +
                                               "SET   ContractNumber     =   " + collection["ContractNumber"] + ", " +
                                               "      EmployeeID         =   " + collection["EmployeeID"] + ", " +
                                               "      FirstName          = N'" + collection["FirstName"] + "', " +
                                               "      LastName           = N'" + collection["LastName"] + "', " +
                                               "      MaritalStatus      = N'" + collection["MaritalStatus"] + "', " +
                                               "      NumberOfChildren   =   " + collection["NumberOfChildren"] + ", " +
                                               "      ApartmentID        =   " + collection["ApartmentID"] + " " +
                                               "WHERE RentContractID = " + id + ";";
                            if (DateTime.Parse(collection["ContractDate"]) != new DateTime(1900, 1, 1))
                                strUpdate += "UPDATE Management.RentContracts " +
                                             "SET   ContractDate       = N'" + collection["ContractDate"] + "' " +
                                             "WHERE RentContractID = " + id + ";";
                            if (DateTime.Parse(collection["RentStartDate"]) != new DateTime(1900, 1, 1))
                                strUpdate += "UPDATE Management.RentContracts " +
                                             "SET   RentStartDate       = N'" + collection["RentStartDate"] + "' " +
                                             "WHERE RentContractID = " + id + ";";
    
                            SqlCommand cmdRentContracts = new SqlCommand(strUpdate,
                                                                         scApartmentsManagement);
    
                            scApartmentsManagement.Open();
                            cmdRentContracts.ExecuteNonQuery();
                        }
    
                        /* Change the status of the newly selected apartment (the apartment that has just been applied to the contract), 
                         * to Occupied (from whatever was its status). */
                        using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                    ConfigurationManager.
                                                                                    ConnectionStrings["csApartmentsRentalManagement"].
                                                                                    ConnectionString))
                        {
                            SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " +
                                                                      "SET   OccupancyStatus = N'Occupied'  " +
                                                                      "WHERE ApartmentID     =   " + collection["ApartmentID"] + ";",
                                                                      scRentManagement);
    
                            scRentManagement.Open();
                            cmdApartments.ExecuteNonQuery();
                        }
    
                        return RedirectToAction("Index");
                    }
    
                    RentContract contract = objects.FindRentContract(id);
    
                    List<SelectListItem> maritals = new List<SelectListItem>
                    {
                        new SelectListItem() { Text = "Single", Value = "Single" },
                        new SelectListItem() { Text = "Widdow", Value = "Widdow" },
                        new SelectListItem() { Text = "Married", Value = "Married" },
                        new SelectListItem() { Text = "Unknown", Value = "Unknown" },
                        new SelectListItem() { Text = "Divorced", Value = "Divorced" },
                        new SelectListItem() { Text = "Separated", Value = "Separated" }
                    };
    
                    ViewBag.MaritalStatus = maritals;
    
                    ViewBag.EmployeeID  = new SelectList(objects.GetEmployees(),  "EmployeeID",  "Identification", contract.EmployeeID);
                    ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence",      contract.ApartmentID);
    
                    return View(contract);
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: RentContracts/Delete/5
            public ActionResult Delete(int id)
            {
                RentContract contract = null;
    
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                 ConfigurationManager.
                                                                                 ConnectionStrings["csApartmentsRentalManagement"].
                                                                                 ConnectionString))
                {
                    SqlCommand cmdRentContracts = new SqlCommand("SELECT RentContractID, ContractNumber, " +
                                                                 "       EmployeeID, ContractDate, " +
                                                                 "       FirstName, LastName, " +
                                                                 "       MaritalStatus, NumberOfChildren, " +
                                                                 "       ApartmentID, RentStartDate " +
                                                                 "FROM Management.RentContracts " +
                                                                 "WHERE RentContractID = " + id + ";",
                                                                 scRentManagement);
                    scRentManagement.Open();
    
                    SqlDataAdapter sdaRentContracts = new SqlDataAdapter(cmdRentContracts);
                    DataSet dsRentContracts = new DataSet("rent-contracts");
    
                    sdaRentContracts.Fill(dsRentContracts);
    
                    if (dsRentContracts.Tables[0].Rows.Count > 0)
                    {
                        contract = new RentContract()
                        {
                            RentContractID = int.Parse(dsRentContracts.Tables[0].Rows[0][0].ToString()),
                            ContractNumber = int.Parse(dsRentContracts.Tables[0].Rows[0][1].ToString()),
                            EmployeeID = int.Parse(dsRentContracts.Tables[0].Rows[0][2].ToString()),
                            ContractDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[0][3].ToString()),
                            FirstName = dsRentContracts.Tables[0].Rows[0][4].ToString(),
                            LastName = dsRentContracts.Tables[0].Rows[0][5].ToString(),
                            MaritalStatus = dsRentContracts.Tables[0].Rows[0][6].ToString(),
                            NumberOfChildren = int.Parse(dsRentContracts.Tables[0].Rows[0][7].ToString()),
                            ApartmentID = int.Parse(dsRentContracts.Tables[0].Rows[0][8].ToString()),
                            RentStartDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[0][9].ToString())
                        };
                    }
                }
    
                return contract == null ? HttpNotFound() : (ActionResult)View(contract);
            }
    
            // POST: RentContracts/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                    Configuration.
                                                                                    ConfigurationManager.
                                                                                    ConnectionStrings["csApartmentsRentalManagement"].
                                                                                    ConnectionString))
                    {
                        SqlCommand cmdRentContracts = new SqlCommand("DELETE Management.RentContracts " +
                                                                     "WHERE RentContractID = " + id + ";",
                                                                     scApartmentsManagement);
    
                        scApartmentsManagement.Open();
                        cmdRentContracts.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  126. In the Solution Explorer, under Views, right-click RentContracts -> Add -> View...
  127. In the Add View dialog box, type Details in the View Name text box
  128. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  129. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.RentContract
    
    @{
        ViewBag.Title = "Rental Contract Details";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Rental Contract Details</h2>
    
    <hr />
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.RentContractID)</dt>
            <dd>@Html.DisplayFor(model => model.RentContractID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ContractDate)</dt>
            <dd>@Html.DisplayFor(model => model.ContractDate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.MaritalStatus)</dt>
            <dd>@Html.DisplayFor(model => model.MaritalStatus)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.NumberOfChildren)</dt>
            <dd>@Html.DisplayFor(model => model.NumberOfChildren)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
            <dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.RentStartDate)</dt>
            <dd>@Html.DisplayFor(model => model.RentStartDate)</dd>
        </dl>
    </div>
    <p>
        @Html.ActionLink("Edit", "Edit", new { id = Model.RentContractID }) |
        @Html.ActionLink("Rental Contracts", "Index")
    </p>
  130. In the Solution Explorer, under Views, right-click RentContracts -> Add -> View...
  131. Type Create
  132. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  133. Create a form as follows:
    @model ApartmentsRentalManagement1.Models.RentContract
    
    @{
        ViewBag.Title = "New Rental Contract";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Create Rental Contract</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
    <div class="form-horizontal common-font">
        <div class="form-group">
            @Html.LabelFor(model => model.ContractNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ContractNumber, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="employee" class="control-label col-md-2">Processed By</label>
            <div class="col-md-10">
                @Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.ContractDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ContractDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="maritalStatus" class="control-label col-md-2">Marital Status</label>
            <div class="col-md-10">
                @Html.DropDownList("MaritalStatus", null, htmlAttributes: new { @class = "form-control", id = "maritalStatus" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.NumberOfChildren, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.NumberOfChildren, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="apart" class="control-label col-md-2">Apartment</label>
            <div class="col-md-10">
                @Html.DropDownList("ApartmentID", null, htmlAttributes: new { @class = "form-control", id = "apart" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.RentStartDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.RentStartDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
            </div>
        </div>
    
        <div class="form-group">
            <label class="control-label col-md-2">@Html.ActionLink("Rental Contracts", "Index")</label>
            <div class="col-md-10">
                <input type="submit" value="Create Rental Contract" class="btn btn-primary" />
            </div>
        </div>
    </div>
    }
  134. In the Solution Explorer, under Views, right-click RentContracts -> Add -> View...
  135. Type Edit
  136. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Press Enter
  137. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.RentContract
    
    @{
        Layout = "~/Views/Shared/_Management.cshtml";
        ViewBag.Title = "Edit/Update Rental Contract";
    }
    
    <h2 class="bold common-font blue">Edit/Update Rental Contract</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
    <div class="form-horizontal common-font">
        <div class="form-group">
            @Html.LabelFor(model => model.ContractNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ContractNumber, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="employee" class="control-label col-md-2">Processed By</label>
            <div class="col-md-10">
                @Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.ContractDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ContractDate, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="maritalStatus" class="control-label col-md-2">Marital Status</label>
            <div class="col-md-2">
                @Html.DropDownList("MaritalStatus", null, htmlAttributes: new { @class = "form-control", id = "maritalStatus" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.NumberOfChildren, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.NumberOfChildren, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label for="apart" class="control-label col-md-2">Apartment</label>
            <div class="col-md-10">
                @Html.DropDownList("ApartmentID", null, htmlAttributes: new { @class = "form-control", id = "apart" })
            </div>
        </div>
    
        <div class="form-group">
            @Html.LabelFor(model => model.RentStartDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.RentStartDate, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>
    
        <div class="form-group">
            <label class="control-label col-md-2">@Html.ActionLink("Rental Contracts", "Index")</label>
            <div class="col-md-10">
                <input type="submit" value="Update this Rental Contract" class="btn btn-primary" />
            </div>
        </div>
    </div>
    }
  138. In the Solution Explorer, under Views, right-click RentContracts -> Add -> View...
  139. Type Delete
  140. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Press Enter
  141. Change the code as follows:
    @model ApartmentsRentalManagement1.Models.RentContract
    
    @{
        ViewBag.Title = "Delete Rent Contract";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Delete Rent Contract</h2>
    
    <hr />
    
    <h3 class="common-font blue">Are you sure you want to delete this rent contract?</h3>
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.RentContractID)</dt>
            <dd>@Html.DisplayFor(model => model.RentContractID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ContractNumber)</dt>
            <dd>@Html.DisplayFor(model => model.ContractNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ContractDate)</dt>
            <dd>@Html.DisplayFor(model => model.ContractDate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
            <dd>@Html.DisplayFor(model => model.FirstName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.LastName)</dt>
            <dd>@Html.DisplayFor(model => model.LastName)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.MaritalStatus)</dt>
            <dd>@Html.DisplayFor(model => model.MaritalStatus)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.NumberOfChildren)</dt>
            <dd>@Html.DisplayFor(model => model.NumberOfChildren)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
            <dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.RentStartDate)</dt>
            <dd>@Html.DisplayFor(model => model.RentStartDate)</dd>
        </dl>
    
        @using (Html.BeginForm())
        {
            <div class="form-actions no-color">
                @Html.ActionLink("Rent Contracts", "Index") ::
                <input type="submit" value="Delete this Rent Contract" class="btn btn-primary" />
            </div>
        }
    </div>
  142. Click the RentContractsController.cs tab to access it
  143. In the class, right-click below Index() and click Add View...
  144. In the Add View dialog box, make sure the View Name text box displays Index.
    Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml
  145. Click Add
  146. Change the document as follows:
    @model IEnumerable<ApartmentsRentalManagement1.Models.RentContract>
    
    @{
        ViewBag.Title = "Rent Contracts";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Rent Contracts</h2>
    
    <hr />
    
    <table class="table table-hover common-font">
        <tr>
            <th class="text-center">@Html.DisplayNameFor(model => model.RentContractID)</th>
            <th class="text-center">@Html.DisplayNameFor(model => model.ContractNumber)</th>
            <th class="text-center">@Html.DisplayNameFor(model => model.EmployeeID)</th>
            <th class="text-center">@Html.DisplayNameFor(model => model.ContractDate)</th>
            <th>@Html.DisplayNameFor(model => model.FirstName)</th>
            <th>@Html.DisplayNameFor(model => model.LastName)</th>
            <th>@Html.DisplayNameFor(model => model.MaritalStatus)</th>
            <th>@Html.DisplayNameFor(model => model.NumberOfChildren)</th>
            <th>@Html.DisplayNameFor(model => model.ApartmentID)</th>
            <th class="text-center">@Html.DisplayNameFor(model => model.RentStartDate)</th>
            <th class="text-center">@Html.ActionLink("Start New Rent Contract", "Create")</th>
        </tr>
    
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">@Html.DisplayFor(modelItem => item.RentContractID)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.ContractNumber)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
                <td class="text-center">@item.ContractDate.ToShortDateString()</td>
                <td>@Html.DisplayFor(modelItem => item.FirstName)</td>
                <td>@Html.DisplayFor(modelItem => item.LastName)</td>
                <td>@Html.DisplayFor(modelItem => item.MaritalStatus)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.NumberOfChildren)</td>
                <td class="text-center">@Html.DisplayFor(modelItem => item.ApartmentID)</td>
                <td class="text-center">@item.RentStartDate.ToShortDateString()</td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.RentContractID }) |
                    @Html.ActionLink("Details", "Details", new { id = item.RentContractID }) |
                    @Html.ActionLink("Delete", "Delete", new { id = item.RentContractID })
                </td>
            </tr>
        }
    </table>
  147. To execute, on the main menu, click Debug -> Start Without Debugging
  148. In the webpage, click the RENT MANAGEMENT link

    Joins Fundamentals

  149. Click the Rent Contracts link and click Start New Rent Contract

    Joins Fundamentals

  150. Create the following records:
     

    Joins Fundamentals

    Joins Fundamentals

    Joins Fundamentals

    Joins Fundamentals

    Contract # Processed By Contract Date First Name Last Name Marital Status # of Children Unit # Rent Start Date
    1001 38408 6/12/2019 Ann Sanders Married 1 109 7/1/2019
    1002 20448 6/15/2019 Mahty Shaoul   2 104 9/1/2019
    1003 40685 6/22/2019 Frank Ulm Single 0 302 7/1/2019
    1004 93947 6/22/2019 Elise Provowski Separated 1 305 8/1/2019
    1005 93947 7/23/2019 Grace Curryan   1 105 9/1/2019
    1006 38408 7/25/2019 Tracy Warrens Divorced 2 307 8/1/2019
    1007 38408 8/1/2019 Paul Yamo Married 3 204 10/1/2019
    1008 40685 8/10/2019 Nancy Shermann Single 1 108 9/1/2019
    1009 20448 9/12/2019 Michael Tiernan   0 209 11/1/2019
    1010 38408 10/5/2019 Phillippe Anderson Single 0 202 11/1/2019

    Joins Fundamentals

  151. Click the Apartments link

    Creating a Join

  152. Close the browser and return to your programming environment
  153. Close the Index.cshtml, Delete.cshtml. Edit.cshtml, Create.cshtml, Details.cshtml, and RentContractsController.cs tabs
  154. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
  155. In the middle frame of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/write Actions is selected
  156. Click Add
  157. Type Payments to get PaymentsCoontroller
  158. Press Enter
  159. Change the class as follows:
    using System.Net;
    using System.Collections.Generic;
    using System.Data;
    using System.Web.Mvc;
    using System.Data.SqlClient;
    using ApartmentsRentalManagement1.Models;
    
    namespace ApartmentRentalManagement1.Controllers
    {
        public class PaymentsController : Controller
        {
            BusinessObjects objects = new BusinessObjects();
    
            // GET: Payments
            public ActionResult Index()
            {
                return View(objects.GetPayments());
            }
    
            // GET: Payments/Details/5
            public ActionResult Details(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Payment rentPayment = objects.FindPayment(id);
    
                if (rentPayment == null)
                {
                    return HttpNotFound();
                }
    
                return View(rentPayment);
            }
    
            // GET: Payments/Create
            public ActionResult Create()
            {
                ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification");
                ViewBag.RentContractID = new SelectList(objects.GetRentContracts(), "RentContractID", "Description");
    
                return View();
            }
    
            // POST: Payments/Create
            [HttpPost]
            public ActionResult Create(FormCollection collection)
            {
                try
                {
                    // TODO: Add insert logic here
                    using (SqlConnection scApartmentsManagement = new SqlConnection(System.
                                                                                    Configuration.
                                                                                    ConfigurationManager.
                                                                                    ConnectionStrings["csApartmentsRentalManagement"].
                                                                                    ConnectionString))
                    {
                        SqlCommand cmdPayments = new SqlCommand("INSERT INTO Management.Payments(ReceiptNumber, EmployeeID, " +
                                                                "                                RentContractID, PaymentDate, " +
                                                                "                                Amount, Notes) " +
                                                                "VALUES(" + collection["ReceiptNumber"] + ", " +
                                                                collection["EmployeeID"] + ", " + collection["RentContractID"] +
                                                                ", N'" + collection["PaymentDate"] + "', " + collection["Amount"] +
                                                                ", N'" + collection["Notes"] + "');",
                                                                scApartmentsManagement);
    
                        scApartmentsManagement.Open();
                        cmdPayments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Payments/Edit/5
            public ActionResult Edit(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Payment pmt = objects.FindPayment(id);
    
                if (pmt == null)
                {
                    return HttpNotFound();
                }
    
                ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification", pmt.EmployeeID);
                ViewBag.RentContractID = new SelectList(objects.GetRentContracts(), "RentContractID", "Description", pmt.RentContractID);
    
                return View(pmt);
            }
    
            // POST: Payments/Edit/5
            [HttpPost]
            public ActionResult Edit(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add update logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Payments " +
                                                                  "SET   ReceiptNumber  =   " + collection["ReceiptNumber"] + ",  " +
                                                                  "      EmployeeID     =   " + collection["EmployeeID"] + ",  " +
                                                                  "      RentContractID =   " + collection["RentContractID"] + ",  " +
                                                                  "      PaymentDate    = N'" + collection["PaymentDate"] + "', " +
                                                                  "      Amount         =   " + collection["Amount"] + ",  " +
                                                                  "      Notes          = N'" + collection["Notes"] + "'  " +
                                                                  "WHERE PaymentID     =    " + id + ";  ",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdApartments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
    
            // GET: Payments/Delete/5
            public ActionResult Delete(int id)
            {
                if (id == 0)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
    
                Payment pmt = objects.FindPayment(id);
    
                if (pmt == null)
                {
                    return HttpNotFound();
                }
    
                return View(pmt);
            }
    
            // POST: Payments/Delete/5
            [HttpPost]
            public ActionResult Delete(int id, FormCollection collection)
            {
                try
                {
                    // TODO: Add delete logic here
                    using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
                                                                                     ConfigurationManager.
                                                                                     ConnectionStrings["csApartmentsRentalManagement"].
                                                                                     ConnectionString))
                    {
                        SqlCommand cmdPayments = new SqlCommand("DELETE FROM Management.Payments " +
                                                                 "WHERE PaymentID = " + id + ";",
                                                                 scRentManagement);
    
                        scRentManagement.Open();
                        cmdPayments.ExecuteNonQuery();
                    }
    
                    return RedirectToAction("Index");
                }
                catch
                {
                    return View();
                }
            }
        }
    }
  160. In the Solution Explorer, under Views, right-click Payments -> Add -> View...
  161. In the Add View dialog box, type Details in the View Name text box
  162. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  163. Change the document as follows:
    @model ApartmentsRentalManagement1.Models.Payment
    
    @{
        ViewBag.Title = "Rent Payment Details";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Rent Payment Details</h2>
    
    <hr />
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.PaymentID)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ReceiptNumber)</dt>
            <dd>@Html.DisplayFor(model => model.ReceiptNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.RentContractID)</dt>
            <dd>@Html.DisplayFor(model => model.RentContractID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Amount)</dt>
            <dd>@Html.DisplayFor(model => model.Amount)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Notes)</dt>
            <dd>@Html.DisplayFor(model => model.Notes)</dd>
        </dl>
    </div>
    <p>
        @Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
        @Html.ActionLink("Employees", "Index")
    </p>
  164. In the Solution Explorer, under Views, right-click Payments -> Add -> View...
  165. Type Create
  166. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  167. Create a form as follows:
    @model ApartmentsRentalManagement1.Models.Payment
    
    @{
        ViewBag.Title = "Make Rent Payment";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Make Rent Payment</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            <div class="form-group">
                @Html.LabelFor(model => model.ReceiptNumber, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.ReceiptNumber, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                <label for="employee" class="control-label col-md-2">Processed By</label>
                <div class="col-md-10">
                    @Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="contractNbr" class="control-label col-md-2">Rent Contract</label>
                <div class="col-md-10">
                    @Html.DropDownList("RentContractID", null, htmlAttributes: new { @class = "form-control", id = "contractNbr" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Amount, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Amount, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Notes, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Notes, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Rent Payments", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Make Rent Payment" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  168. Click the PaymentsController.cs tab to access the controller
  169. In the class, right-click Edit() and click Add View...
  170. Make sure the View Name text box is displaying Edit and the Use A Layout Page text box is displaying~/Views/Shared/_Management.cshtml. Click Add
  171. Create the form as follows:
    @model ApartmentsRentalManagement1.Models.Payment
    
    @{
        ViewBag.Title = "Edit/Update Rent Payment";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Edit/Update Rent Payment</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="form-horizontal common-font">
            <div class="form-group">
                @Html.LabelFor(model => model.ReceiptNumber, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.ReceiptNumber, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                <label for="employee" class="control-label col-md-2">Processed By</label>
                <div class="col-md-10">
                    @Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
                </div>
            </div>
    
            <div class="form-group">
                <label for="contractNbr" class="control-label col-md-2">Rent Contract</label>
                <div class="col-md-10">
                    @Html.DropDownList("RentContractID", null, htmlAttributes: new { @class = "form-control", id = "contractNbr" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Amount, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Amount, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Notes, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Notes, new { htmlAttributes = new { @class = "form-control" } })
                </div>
            </div>
    
            <div class="form-group">
                <label class="control-label col-md-2">@Html.ActionLink("Rent Payments", "Index")</label>
                <div class="col-md-10">
                    <input type="submit" value="Update Rent Payment" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }
  172. Click the PaymentsController.cs tab to access the controller
  173. In the class, right-click Delete() and click Add View...
  174. Make sure the View Name text box is displaying Delete and the Use A Layout Page text box is displaying~/Views/Shared/_Management.cshtml. Click Add
  175. Create the form as follows:
    @model ApartmentsRentalManagement1.Models.Payment
    
    @{
        ViewBag.Title = "Cancel Rent Payment";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold common-font blue">Cancel Rent Payment</h2>
    
    <hr />
    
    <h3 class="common-font blue">Are you sure that you want to cancel this payment?</h3>
    
    <div>
        <dl class="dl-horizontal common-font">
            <dt>@Html.DisplayNameFor(model => model.PaymentID)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.ReceiptNumber)</dt>
            <dd>@Html.DisplayFor(model => model.ReceiptNumber)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
            <dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.RentContractID)</dt>
            <dd>@Html.DisplayFor(model => model.RentContractID)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
            <dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Amount)</dt>
            <dd>@Html.DisplayFor(model => model.Amount)</dd>
    
            <dt>@Html.DisplayNameFor(model => model.Notes)</dt>
            <dd>@Html.DisplayFor(model => model.Notes)</dd>
        </dl>
    
        @using (Html.BeginForm())
        {
            @Html.AntiForgeryToken()
    
            <div class="form-actions no-color">
                @Html.ActionLink("Rent Payments", "Index") ::
                <input type="submit" value="Delete Rent Payment" class="btn btn-primary" />
            </div>
        }
    </div>
  176. Click the PaymentsController.cs tab to access it
  177. In the class, right-click Index() and click Add View...
  178. In the Add View dialog box, make sure the View Name text box displays Index
  179. Make sure the Use A Layout Page text box displays ~/Views/Shared/_Management.cshtml.
    Click Add
  180. Change the document as follows:
    @model IEnumerable<ApartmentsRentalManagement1.Models.Payment>
    
    @{
        ViewBag.Title = "Rent Payments";
        Layout = "~/Views/Shared/_Management.cshtml";
    }
    
    <h2 class="bold blue common-font text-center">Rent Payments</h2>
    
    <table class="table table-hover common-font">
        <tr>
            <th>@Html.DisplayNameFor(model => model.PaymentID)</th>
            <th>@Html.DisplayNameFor(model => model.ReceiptNumber)</th>
            <th>@Html.DisplayNameFor(model => model.EmployeeID)</th>
            <th>@Html.DisplayNameFor(model => model.RentContractID)</th>
            <th>@Html.DisplayNameFor(model => model.PaymentDate)</th>
            <th>@Html.DisplayNameFor(model => model.Amount)</th>
            <th>@Html.DisplayNameFor(model => model.Notes)</th>
            <th>@Html.ActionLink("New Rent Payment", "Create")</th>
        </tr>
    
    @foreach (var item in Model)
    {
        //string strPaymentDate = ;
        <tr>
            <td class="text-center">@Html.DisplayFor(modelItem => item.PaymentID)</td>
            <td>@Html.DisplayFor(modelItem => item.ReceiptNumber)</td>
            <td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
            <td class="text-center">@Html.DisplayFor(modelItem => item.RentContractID)</td>
            <td>@item.PaymentDate.ToLongDateString()</td>
            <td>@Html.DisplayFor(modelItem => item.Amount)</td>
            <td>@Html.DisplayFor(modelItem => item.Notes)</td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id = item.PaymentID }) |
                @Html.ActionLink("Details", "Details", new { id = item.PaymentID }) |
                @Html.ActionLink("Delete", "Delete", new { id = item.PaymentID })
            </td>
        </tr>
    }
    </table>
  181. To execute, on the main menu, click Debug -> Start Without Debugging

    Joins Fundamentals

  182. Click the New Rent Payment link

    Joins Fundamentals

  183. Create the following records:
     
    Receipt # Employee # Contract # Payment Date Amount Notes
    100001 20448 1002 06/15/2019 1250 This is the payment of the security deposit
    100002 38408 1001 06/17/2019 1100 This is the first payment of the tenant. It is for the security deposit.
    100003 40685 1004 06/22/2019 1000 Security deposit payment
    100004 93947 1003 06/25/2019 850 This was the security deposit payment
    100005 40685 1003 07/26/2019 1100 This was the July 2019 rent payment
    100006 38408 1001 07/31/2019 1350 Rent payment for July 2019
    100007 40685 1004 08/25/2019 1100 August 2019 rent payment
    100008 20448 1003 08/30/2019 1100 August 2019 rent payment
    100009 20448 1001 08/31/2019 1350 Rent payment for August 2019
    100010 93947 1002 09/26/2019 1500 Rent payment - September 2019
    100011 38408 1003 09/27/2019 1100 Rent payment for September 2019
    100012 93947 1001 09/28/2019 1350 This is the rent payment for September 2019
    100013 20448 1004 09/29/2019 1100 September 2019 monthly payment
    100014 93947 1003 10/28/2019 1100 October 2019 Rent
    100015 20448 1002 10/29/2019 1500 This was the payment for October 2019
    INSERT INTO Management.Payments(ReceiptNumber,	EmployeeID,	RentContractID,	PaymentDate, Amount, Notes)
    VALUES(100001,	6,	2,	N'06/15/2019',	1250,	N'This is the payment of the security deposit'),
          (100002,	5,	1,	N'06/17/2019',	1100,	N'This is the payment of the security deposit'),
          (100003,	2,	4,	N'06/22/2019',	1000,	N'Security deposit payment'),
          (100004,	1,	3,	N'06/25/2019',	 850,	N'This was the security deposit payment'),
          (100005,	2,	3,	N'07/26/2019',	1100,	N'This was the July 2019 rent payment'),
          (100006,	5,	1,	N'07/31/2019',	1350,	N'Rent payment for July 2019'),
          (100007,	2,	4,	N'08/25/2019',	1100,	N'August 2019 rent payment'),
          (100008,	6,	3,	N'08/30/2019',	1100,	N'August 2019 rent payment'),
          (100009,	6,	1,	N'08/31/2019',	1350,	N'Rent payment for August 2019'),
          (100010,	1,	2,	N'09/26/2019',	1500,	N'Rent payment - September 2019'),
          (100011,	5,	3,	N'09/27/2019',	1100,	N'Rent payment for September 2019'),
          (100012,	1,	1,	N'09/28/2019',	1350,	N'This is the rent payment for September 2019'),
          (100013,	6,	4,	N'09/29/2019',	1100,	N'September 2019 monthly payment'),
          (100014,	1,	3,	N'10/28/2019',	1100,	N'October 2019 Rent'),
          (100015,	6,	2,	N'10/29/2019',	1500,	N'This was the payment for October 2019'),
          (100016,	1,	1,	N'10/30/2019',	1350,	N'Rent for October 2019'),
          (100017,	5,	4,	N'10/30/2019',	1100,	N'Rent payment for October 2019'),
          (100018,	6,	1,	N'11/26/2019',	1350,	N'November 2019 Rent'),
          (100019,	2,	3,	N'11/29/2019',	1100,	N'November 2019 Rent Payment'),
          (100020,	6,	2,	N'11/30/2019',	1500,	N'This is the rent payment for November 2019'),
          (100021,	2,	4,	N'11/30/2019',	1100,	N'Rent payment for November 2019'),
          (100022,	1,	2,	N'12/27/2019',	1500,	N'Rent payment - December 2019'),
          (100023,	6,	4,	N'12/30/2019',	1100,	N'December 2019 rent payment'),
          (100024,	5,	3,	N'12/31/2019',	1100,	N'Rent payment for December 2019'),
          (100025,	5,	1,	N'01/04/2019',	1350,	N'This was rent for December 2019'),
          (100026,	2,	4,	N'01/20/2019',	1100,	N'January 2020 rent payment'),
          (100027,	5,	3,	N'01/26/2019',	1100,	N'January 2020 rent payment'),
          (100028,	5,	2,	N'01/28/2019',	1500,	N'January 2020 - Rent Payment'),
          (100029,	6,	1,	N'02/02/2019',	1350,	N'This was rent for January 2020'),
          (100030,	5,	4,	N'02/25/2019',	1100,	N'February 2020 rent payment'),
          (100031,	6,	3,	N'02/26/2019',	1100,	N'Rent payment for February 2020'),
          (100032,	6,	2,	N'02/27/2019',	1500,	N'February 2020 Rent'),
          (100033,	5,	1,	N'03/01/2019',	1350,	N'Rent for February 2020'),
          (100034,	2,	3,	N'03/27/2019',	1100,	N'Rent payment for March 2020'),
          (100035,	6,	4,	N'03/29/2019',	1100,	N'March 2020 rent payment'),
          (100036,	5,	2,	N'03/30/2019',	1500,	N'Rent payment for March 2020'),
          (100037,	5,	1,	N'03/31/2019',	1350,	N'March 2020 Rent');
    GO

    Joins Fundamentals

  184. Close the browser and return to your programming environment
  185. Close the Create.cshtml, the Details.cshtml, the Index.cshtml, and the PaymentsController.cshtml tabs

Creating a Join

Once you have the necessary tables and their columns, you can create a join. To do this visually, in the Object Explorer of Microsoft SQL Server Management Studio, right-click the connection or any of its nodes and click New Query. The basic formula to create a join is:

SELECT column(s)
FROM child-table
type-of-join parent-table
ON condition

The child-table specifies the table that holds the records that will be retrieved. It can be represented as follows:

SELECT column(s)
FROM Students
type-of-join parent-table
ON condition

The parent-table specifies the table that holds the column with the primary key that will control what records, related to the child table, will be selected. This would be represented as follows:

SELECT column(s)
FROM Students
type-of-join Majors
ON condition

The condition is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT column(s)
FROM Students
type-of-join Majors
ON Students.MajorCode = Majors.MajorCode

The column(s) of our formula allow(s) you to create a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

SELECT *
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode

In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

SELECT LastName, FirstName, Gender
FROM Students
type-of-Join EmploymentsStatus
ON Students.StatusCode = Majors.MajorCode

If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name of the column by preceding it with the name of its parent table followed by a period. Here are examples:

SELECT LastName,
       FirstName,
       Students.MajorCode,
       Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode

In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here are examples:

SELECT Students.LastName,
       Students.FirstName, 
       Students.MajorCode,
       Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode

Cross and Inner Joins

Introduction

When studying data relationships, we saw the roles of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.

Cross Joins

A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.

To create a cross join, you can replace the type-of-join of our formula with CROSS JOIN or CROSS OUTER JOIN. Here is an example:

SELECT ContractNumber AS [Contract #],
	   HumanResources.Employees.EmployeeNumber + N': ' +
			HumanResources.Employees.FirstName + N' ' +
			HumanResources.Employees.LastName AS [Processed By],
	   ContractDate [Contract Date],
	   Management.RentContracts.FirstName + N' ' + Management.RentContracts.LastName AS Tenant,
	   MaritalStatus AS [Marital Status],
	   NumberOfChildren As Children,
	   ApartmentID AS [Unit #],
	   RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
CROSS JOIN Management.RentContracts;;
GO

This would produce:

An Error On Delete or On Update

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key. Some records in the child table may not have an entry for the foreign key column and would be marked with NULL by the database engine. When creating a query of records of the child table, if you want your list to include only records that have an entry, you can create it as an inner join.

To create an inner join, specify the type-of-join of our formula with the expression INNER JOIN. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName, 
       Students.LastName, 
       Students.MajorCode,
       Majors.MajorCode AS [Major]
FROM   Students INNER JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

Inner Joins

Notice that, because the query doesn't include students who don't have a major, those are students whose MajorCode is NULL, it (the query) produces 15 records.

An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName, 
       Students.LastName, 
       Students.MajorCode,
       Majors.MajorCode AS [Major]
FROM   Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO

We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the major of each Students record, we would not need the MajorCode column from the Majors table. Instead, we can access the MajorName. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName, 
       Students.LastName, 
       Majors.MajorName AS [Major]
FROM   Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

Obviously, once again, we get 15 records.

Outer Joins

Introduction

Instead of showing only records that have entries in the child table, you may want your query to get all records, including those that are null. To get this result, you would create an outer join. You have three options.

A Left Outer Join

A left outer join produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column are marked as NULL.

To create a left outer join, replace the type-of-join our formula with the LEFT OUTER JOIN expression. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.LastName, 
       Majors.MajorCode
FROM Students
LEFT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

A Left Outer Join

You can omit the OUTER keyword and get the same result. Here is an example:

SELECT StudentNumber,
       FirstName,
       LastName, 
       MajorName
FROM Students
LEFT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

A Left Outer Join

Notice that the result includes all records of the Students (also called the right) table and the records that don't have an entry in the MajorCode column of the Students (the right) table are marked with NULL.

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in our example the Majors table) and shows each record of the child table (in this case the Students table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Students records that have a CMSC value for the MajorCode column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To create a right outer join in SQL, replace the type-of-join factor of our formula with the RIGHT OUTER JOIN expression. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.LastName, 
       Majors.MajorCode
FROM Students
RIGHT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

Right Outer Join

You can omit the OUTER keyword and get the same result. Here is an example:

SELECT StudentNumber,
       FirstName,
       LastName, 
       MajorName
FROM Students
RIGHT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

A Right Outer Join

A Full Outer Join

A full outer join produces all records from both the parent and the child tables. If a record from one table does not have a value in the other value, the value of that record is marked as NULL.

To create a full outer join in SQL, replace the type-of-join of our formula with the FULL OUTER JOIN expression. Here is an example:

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.LastName,
       Majors.MajorCode
FROM Students
FULL OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

A Full Outer Join

You can omit the OUTER keyword and get the same result. Here is an example:

SELECT StudentNumber,
       FirstName,
       LastName,
       MajorName
FROM Students
FULL JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO

This would produce:

A Full Outer Join

Just as we have involved only two tables in our joins so far, you can create a join that includes many tables.

Practical LearningPractical Learning: Ending the Lesson


Previous Copyright © 2007-2019, FunctionX Next