Home

Views-Based Applications: Bethesda Car Rental

     

Introduction

A view is a particular way to present the records of one table, more than one table, another view, or a combination of related tables and views.

To explore views, we will create an application for a fictitious car rental company.

Practical LearningPractical Learning: Introducing Views

  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click FILE -> New -> Project...
  3. In the middle list, click Windows Forms Application and change the Name to BethesdaCarRental4
  4. Click OK
  5. To create a new database, in the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
  6. In the Server Name combo box, select your server or type (local)
  7. Set the database name as BethesdaCarRental1 and click OK
  8. Open the Bethesda Car Rental1 file and select everything in it
  9. Copy the whole content (to the clipboard)
  10. In the Server Explorer, right-click the BethesdaCarRental1 connection and click New Query
  11. Paste it in the Query window
  12. Right-click inside the document and click Execute
  13. In the Server Explorer, expand the BethesdaCarRental2 connection
  14. In the Solution Explorer, right-click Form1.cs and click Rename
  15. Type BethesdaCarRental.cs and press Enter twice
  16. In the Solution Explorer, double-click BethesdaCarRental.cs to display the form
  17. Add a button to the form and change its design as follows:
     
    Bethesda Car Rental
     
    Control (Name) Text
    Button Button btnRentalOrders Rental Orders ...
    Button Button btnVehicles Vehicles ...
    Button Button btnEmployees Employees ...
    Button Button btnClose Close
  18. To create a new form, in the Solution Explorer, right-click BethesdaCarRental1 -> Add -> Windows Form...
  19. Set the name to RentalRates and press Enter
  20. Change the properties as follows:
    FormBorderStyle: FixedToolWindow
    ShowInTaskbar: False
  21. Add a list view to the form and change its characteristics as follows:
    (Name): lvwRentalRates
    Dock: Fill
    FullRowSelect: True
    GridLines: True
    View: Details
    Columnes:
    (Name) Text TextAlign Width
    colCategory Category   72
    colDaily Daily Right  
    colWeekly Weekly Right  
    colMonthly Monthly Right  
    colWeekend Weekend Right 742
  22. Click the title bar of the form
  23. In the Properties window, click Events and double-click Load
  24. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class RentalRates : Form
        {
            public RentalRates()
            {
                InitializeComponent();
            }
    
            private void ShowRentalRates()
            {
                lvwRentalRates.Items.Clear();
    
                using (SqlConnection scBethesdaCarRental = new SqlConnection("Data Source=(local);" +
                                                                        "Database=BethesdaCarRental1;" +
                                                                        "Integrated Security=Yes;"))
                {
                    SqlCommand cmdRentalRates = new SqlCommand("SELECT * FROM Assets.Categories;",
                                                                 scBethesdaCarRental);
                    scBethesdaCarRental.Open();
                    SqlDataAdapter sdaRentalRates = new SqlDataAdapter(cmdRentalRates);
                    DataSet dsRentalRates = new DataSet("RentalRatesSet");
                    sdaRentalRates.Fill(dsRentalRates);
    
                    for (int i = 0; i < dsRentalRates.Tables[0].Rows.Count; i++)
                    {
                        ListViewItem lviRentalRate = new ListViewItem(dsRentalRates.Tables[0].Rows[i]["Category"].ToString());
                        if (i % 2 == 0)
                        {
                            lviRentalRate.BackColor = Color.FromArgb(192, 64, 0);
                            lviRentalRate.ForeColor = Color.White;
                        }
                        else
                        {
                            lviRentalRate.BackColor = Color.FromArgb(255, 128, 0);
                            lviRentalRate.ForeColor = Color.Yellow;
                        }
                        lviRentalRate.SubItems.Add(dsRentalRates.Tables[0].Rows[i]["Daily"].ToString());
                        lviRentalRate.SubItems.Add(dsRentalRates.Tables[0].Rows[i]["Weekly"].ToString());
                        lviRentalRate.SubItems.Add(dsRentalRates.Tables[0].Rows[i]["Monthly"].ToString());
                        lviRentalRate.SubItems.Add(dsRentalRates.Tables[0].Rows[i]["Weekend"].ToString());
                        lvwRentalRates.Items.Add(lviRentalRate);
                    }
                }
            }
    
            private void RentalRates_Load(object sender, EventArgs e)
            {
                ShowRentalRates();
            }
        }
    }
  25. To create a new form, in the Solution Explorer, right-click BethedaCarRental1 -> Add -> Windows Form...
  26. Set the Name to NewRentalOrder
  27. Click Add
  28. Design the form as follows:
     
    Bethesda Car Rental - New Rental Order
    Control (Name) Text TextAlign
    Label Label   Rent Start Processed By AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    Text Box Text Box txtRentStartProcessedBy    
    Text Box Text Box txtRentStartEmployeeName    
    Label Label   Processed For - Customer Information AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Order Timing AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   First Name:  
    Text Box Text Box txtCustomerFirstName    
    Label Label   Last Name:  
    Text Box Text Box txtCustomerLastName    
    Label Label   Start Date:  
    Date Time Picker Date Time Picker dtpStartDate    
    Label Label   Address:  
    Text Box Text Box txtCustomerAddress    
    Label Label   City:  
    Text Box Text Box txtCustomerCity    
    Label Label   Order Evaluation AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   State/Province:  
    Combo Box Combo Box cbxCustomerStates   Items: AB, AL, AK, AZ,
    AR, BC, CA, CO, CT,
    DE, DC, FL, GA, HI,
    ID, IL, IN, IA, KS, KY,
    LA, ME, MD, MA, MI,
    MN, MS, MO, MT, NB,
    NE, NV, NH, NJ, NL,
    NM, NS, NY, NC, ND,
    OH, OK, ON, OR, PA,
    PE, QC, RI, SC, SD, SK,
    TN, TX, UT, VT, VA,
    WA, WV, WI, WY
    Label Label   ZIP/Postal Code:  
    Text Box Text Box txtCustomerZIPCode    
    Label Label   Rate Applied:  
    Text Box Text Box txtRateApplied 0.00 TextAlign: Right
    Button Button btnRentalRates Rental Rates...  
    Label Label   Vehicle Selected AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Tag Number:  
    Text Box Text Box txtTagNumber    
    Label Label   Tank Level:  
    Combo Box Combo Box cbxTankLevels   Items:
    Empty
    1/4 Empty
    Half Tank
    3/4 Full
    Full
    Label Label   Vehicle:  
    Text Box Text Box txtVehicleSelected    
    Label Label   Mileage Start:  
    Text Box Text Box txtMileageStart    
    Label Label   Condition:  
    Combo Box Combo Box cbxConditions   Items:
    Drivable
    Excellent
    Needs Repair
    Label Label   Order Status:  
    Combo Box Combo Box cbxOrdersStatus   Items:
    Unknown
    Processing
    Order Complete
    Vehicle With Customer
    Label Label   Notes  
    Text Box Text Box txtNotes   Multiline: True
    ScrollBars: Verticle
    Button Button btnSave Save  
    Button Button btnPrint Print...  
    Button Button btnPrintPreview Print Preview  
    Button Button btnClose Close  
  29. From the Printing section of the Toolbox, click PrintDocument and click the form
  30. In the Properties window, set its (Name) to docPrint and press Enter
  31. Under the form, double-click docPrint and implement its event as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class NewRentalOrder : Form
        {
            public NewRentalOrder()
            {
                InitializeComponent();
            }
    
            private void docPrint_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
            {
                e.Graphics.DrawLine(new Pen(Color.Black, 2), 80, 90, 750, 90);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 80, 93, 750, 93);
    
                string strDisplay = "Bethesda Car Rental";
                System.Drawing.Font fntString = new Font("Times New Roman", 28, FontStyle.Bold);
                e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 240, 100);
    
                strDisplay = "Car Rental Order";
                fntString = new System.Drawing.Font("Times New Roman", 22, FontStyle.Regular);
                e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 320, 150);
    
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 80, 187, 750, 187);
                e.Graphics.DrawLine(new Pen(Color.Black, 2), 80, 190, 750, 190);
    
                fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Processed By:  ", fntString, Brushes.Black, 100, 220);
                fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtRentStartProcessedBy.Text + ": " + txtRentStartEmployeeName.Text, fntString, Brushes.Black, 260, 220);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 240, 720, 240);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 260, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 260, 620, 20));
    
                e.Graphics.DrawString("Customer", fntString, Brushes.White, 100, 260);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("First Name: ", fntString, Brushes.Black, 100, 300);
                e.Graphics.DrawString("Last Name: ", fntString, Brushes.Black, 420, 300);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtCustomerFirstName.Text, fntString, Brushes.Black, 260, 300);
                e.Graphics.DrawString(txtCustomerLastName.Text, fntString, Brushes.Black, 540, 300);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 320, 720, 320);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Address: ", fntString, Brushes.Black, 100, 330);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtCustomerAddress.Text, fntString, Brushes.Black, 260, 330);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 350, 720, 350);
    
                strDisplay = txtCustomerCity.Text + " " + cbxCustomerStates.Text + " " + txtCustomerZIPCode.Text;
                fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 260, 360);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 260, 380, 720, 380);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 410, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 410, 620, 20));
    
                e.Graphics.DrawString("Vehicle Information", fntString, Brushes.White, 100, 410);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Tag #: ", fntString, Brushes.Black, 100, 450);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtTagNumber.Text, fntString, Brushes.Black, 260, 450);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 470, 720, 470);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Vehicle: ", fntString, Brushes.Black, 100, 480);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtVehicleSelected.Text, fntString, Brushes.Black, 260, 480);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 500, 720, 500);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Vehicle Condition: ", fntString, Brushes.Black, 100, 510);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(cbxConditions.Text, fntString, Brushes.Black, 260, 510);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 530, 380, 530);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Tank Level: ", fntString, Brushes.Black, 420, 510);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(cbxTankLevels.Text, fntString, Brushes.Black, 530, 510);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 530, 720, 530);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Mileage Start:", fntString, Brushes.Black, 100, 540);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtMileageStart.Text, fntString, Brushes.Black, 260, 540);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 560, 720, 560);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 590, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 590, 620, 20));
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Order Timing Information", fntString, Brushes.White, 100, 590);
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Start Date:", fntString, Brushes.Black, 100, 620);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(dtpStartDate.Value.ToString("D"), fntString, Brushes.Black, 260, 620);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 640, 720, 640);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 700, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 700, 620, 20));
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Order Evaluation", fntString, Brushes.White, 100, 700);
    
                StringFormat fmtString = new StringFormat();
                fmtString.Alignment = StringAlignment.Far;
    
                fntString = new Font("Times New Roman", 12, FontStyle.Bold);
                e.Graphics.DrawString("Rate Applied:", fntString, Brushes.Black, 100, 740);
                fntString = new Font("Times New Roman", 12, FontStyle.Regular);
                e.Graphics.DrawString(txtRateApplied.Text, fntString, Brushes.Black, 300, 740, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 760, 380, 760);
            }
        }
    }
  32. Return to the New Rental Order form
  33. From the Printing section of the Toolbox, click PrintDialog and click the form
  34. In the Properties window, change its Name to dlgPrint
  35. Still in the Properties windows, set its Document property to docPrint
  36. On the Order Processing form, double-click the Print button and implement its event as follows:
    private void btnPrint_Click(object sender, EventArgs e)
    {
        if (dlgPrint.ShowDialog() == DialogResult.OK)
            docPrint.Print();
    }
  37. Return to the New Rental Order form
  38. From the Printing section of the Toolbox, click PrintPreviewDialog and click the form
  39. In the Properties window, change its (Name) to dlgPrintPreview
  40. Still in the Properties windows, set its Document property to docPrint
  41. On the New Rental Order form, double-click the Print Preview button
  42. Implement the event as follows:
    private void btnPrintPreview_Click(object sender, EventArgs e)
    {
        dlgPrintPreview.ShowDialog();
    }
  43. Return to the New Rental Order form and double-click the Rental Rates button
  44. Implement the event as follows:
     private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates rrs = new RentalRates();
        rrs.Show();
    }
  45. Return to the New Rental Order form
  46. Under the form, select docPrint, dlgPrint, and dlgPrintPreview (you can click one, press and hold either Ctrl or Shift, then click each of the others, and release Ctrl or Shift)
  47. Press Ctrl + C to copy
  48. To create a new form, in the Solution Explorer, right-click BethedaCarRental1 -> Add -> Windows Form...
  49. Set the Name to UpdateRentalOrder
  50. Click Add
  51. Click the middle of the form and press Ctrl + V to paste
  52. Design the form as follows:
     
    Bethesda Car Rental - Rental Orders Updates
    Control (Name) Text TextAlign
    Label Label   Rent Start Processed By AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Rent End Processed By AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    Text Box Text Box txtRentStartProcessedBy    
    Text Box Text Box txtRentStartEmployeeName    
    Label Label   Employee #:  
    Text Box Text Box txtRentEndProcessedBy    
    Text Box Text Box txtRentEndEmployeeName    
    Label Label   Processed For Customer AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Order Timing AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   First Name:  
    Text Box Text Box txtCustomerFirstName    
    Label Label   Last Name:  
    Text Box Text Box txtCustomerLastName    
    Label Label   Start Date:  
    Date Time Picker Date Time Picker dtpStartDate    
    Label Label   Address:  
    Text Box Text Box txtCustomerAddress    
    Label Label   End Date:  
    Date Time Picker Date Time Picker dtpEndDate    
    Label Label   City:  
    Text Box Text Box txtCustomerCity    
    Label Label   Total Days:  
    Text Box Text Box txtTotalDays 0.00 TextAlign: Right
    Label Label   State/Province:  
    Combo Box Combo Box cbxCustomerStates   Items: AB, AL, AK, AZ,
    AR, BC, CA, CO, CT,
    DE, DC, FL, GA, HI,
    ID, IL, IN, IA, KS, KY,
    LA, ME, MD, MA, MI,
    MN, MS, MO, MT, NB,
    NE, NV, NH, NJ, NL,
    NM, NS, NY, NC, ND,
    OH, OK, ON, OR, PA,
    PE, QC, RI, SC, SD, SK,
    TN, TX, UT, VT, VA,
    WA, WV, WI, WY
    Label Label   ZIP/Postal Code:  
    Text Box Text Box txtCustomerZIPCode    
    Label Label   Vehicle Selected AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Order Evaluation AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Tag Number:  
    Text Box Text Box txtTagNumber    
    Label Label   Tank Level:  
    Combo Box Combo Box cbxTankLevels   Items:
    Empty
    1/4 Empty
    Half Tank
    3/4 Full
    Full
    Label Label   Rate Applied:  
    Text Box Text Box txtRateApplied 0.00 TextAlign: Right
    Button Button btnRentalRates Rental Rates...  
    Label Label   Vehicle:  
    Text Box Text Box txtVehicleSelected    
    Label Label   Sub-Total:  
    Text Box Text Box txtSubTotal 0.00 TextAlign: Right
    Button Button btnCalculate Calculate  
    Label Label   Mileage Start:  
    Text Box Text Box txtMileageStart    
    Label Label   Mileage End:  
    Text Box Text Box txtMileageEnd    
    Label Label   Tax Rate:  
    Text Box Text Box txtTaxRate 7.70 TextAlign: Right
    Label Label   %  
    Label Label   Condition:  
    Combo Box Combo Box cbxConditions   Items:
    Drivable
    Excellent
    Needs Repair
    Label Label   Mileage Total:  
    Text Box Text Box txtMileageTotal   TextAlign: Right
    Label Label   Tax Amount:  
    Text Box Text Box txtTaxAmount 0.00 TextAlign: Right
    Button Button btnPrint Print...  
    Label Label   Order Status:  
    Combo Box Combo Box cbxOrdersStatus   Items:
    Unknown
    Processing
    Order Complete
    Vehicle With Customer
    Label Label   Order Total:  
    Text Box Text Box txtOrderTotal 0.00 TextAlign: Right
    Button Button btnPrintPreview Print Preview  
    Label Label   Notes  
    Text Box Text Box txtNotes   Multiline: True
    ScrollBars: Verticle
    Label Label   Receipt #  
    Text Box Text Box txtReceiptNumber 0  
    Button Button btnOpen Open  
    Button Button btnUpdateRentalOrder Update Rental Order  
    Button Button btnResetForm Reset Form  
    Button Button btnClose Close  
  53. Under the form, double-click docPrint and implement its event as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class UpdateRentalOrder : Form
        {
            public UpdateRentalOrder()
            {
                InitializeComponent();
            }
    
            private void docPrint_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
            {
                e.Graphics.DrawLine(new Pen(Color.Black, 2), 80, 90, 750, 90);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 80, 93, 750, 93);
    
                string strDisplay = "Bethesda Car Rental";
                System.Drawing.Font fntString = new Font("Times New Roman", 28, FontStyle.Bold);
                e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 240, 100);
    
                strDisplay = "Car Rental Order";
                fntString = new System.Drawing.Font("Times New Roman", 22, FontStyle.Regular);
                e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 320, 150);
    
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 80, 187, 750, 187);
                e.Graphics.DrawLine(new Pen(Color.Black, 2), 80, 190, 750, 190);
    
                System.Drawing.Font boldString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Bold);
                System.Drawing.Font regularString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
    
                e.Graphics.DrawString("Receipt #:  ", boldString, Brushes.Black, 100, 220);
                e.Graphics.DrawString(txtReceiptNumber.Text, regularString, Brushes.Black, 260, 220);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 240, 380, 240);
    
                e.Graphics.DrawString("Processed By:  ", boldString, Brushes.Black, 420, 220);
                e.Graphics.DrawString(txtRentStartEmployeeName.Text, regularString, Brushes.Black, 550, 220);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 240, 720, 240);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 260, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 260, 620, 20));
    
                e.Graphics.DrawString("Customer", boldString, Brushes.White, 100, 260);
    
                e.Graphics.DrawString("First Name: ", boldString, Brushes.Black, 100, 300);
                e.Graphics.DrawString("Last Name: ", boldString, Brushes.Black, 420, 300);
                e.Graphics.DrawString(txtCustomerFirstName.Text, regularString, Brushes.Black, 260, 300);
                e.Graphics.DrawString(txtCustomerLastName.Text, regularString, Brushes.Black, 540, 300);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 320, 720, 320);
    
                e.Graphics.DrawString("Address: ", boldString, Brushes.Black, 100, 330);
                e.Graphics.DrawString(txtCustomerAddress.Text, regularString, Brushes.Black, 260, 330);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 350, 720, 350);
    
                strDisplay = txtCustomerCity.Text + " " + cbxCustomerStates.Text + " " + txtCustomerZIPCode.Text;
                e.Graphics.DrawString(strDisplay, regularString, Brushes.Black, 260, 360);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 260, 380, 720, 380);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 410, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 410, 620, 20));
    
                e.Graphics.DrawString("Vehicle Information", boldString, Brushes.White, 100, 410);
    
                e.Graphics.DrawString("Tag #: ", boldString, Brushes.Black, 100, 450);
                e.Graphics.DrawString(txtTagNumber.Text, regularString, Brushes.Black, 260, 450);
    
                e.Graphics.DrawString("Make & Model: ", boldString, Brushes.Black, 420, 450);
                e.Graphics.DrawString(txtVehicleSelected.Text, regularString, Brushes.Black, 540, 450);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 470, 720, 470);
    
                StringFormat fmtString = new StringFormat();
                fmtString.Alignment = StringAlignment.Far;
    
                e.Graphics.DrawString("Vehicle Condition: ", boldString, Brushes.Black, 100, 480);
                e.Graphics.DrawString(cbxConditions.Text, regularString, Brushes.Black, 260, 480);
                e.Graphics.DrawString("Mileage Start:", boldString, Brushes.Black, 420, 480);
                e.Graphics.DrawString(txtMileageStart.Text, regularString, Brushes.Black, 600, 480, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 500, 720, 500);
    
                e.Graphics.DrawString("Tank Level: ", boldString, Brushes.Black, 100, 510);
                e.Graphics.DrawString(cbxTankLevels.Text, regularString, Brushes.Black, 260, 510);
                e.Graphics.DrawString("Mileage End:", boldString, Brushes.Black, 420, 510);
                e.Graphics.DrawString(txtMileageEnd.Text, regularString, Brushes.Black, 600, 510, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 530, 720, 530);
    
                e.Graphics.DrawString("Mileage Total: ", boldString, Brushes.Black, 420, 540);
                e.Graphics.DrawString(txtMileageTotal.Text, regularString, Brushes.Black, 600, 540, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 560, 720, 560);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 590, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 590, 620, 20));
    
                e.Graphics.DrawString("Order Timing Information", boldString, Brushes.White, 100, 590);
    
                e.Graphics.DrawString("Start Date:", boldString, Brushes.Black, 100, 620);
                e.Graphics.DrawString(dtpStartDate.Value.ToString("D"), regularString, Brushes.Black, 260, 620);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 640, 720, 640);
    
                e.Graphics.DrawString("End Date:", boldString, Brushes.Black, 100, 650);
                e.Graphics.DrawString(dtpEndDate.Value.ToString("D"), regularString, Brushes.Black, 260, 650);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 670, 520, 670);
    
                e.Graphics.DrawString("Total Days:", boldString, Brushes.Black, 550, 650);
                e.Graphics.DrawString(txtTotalDays.Text, regularString, Brushes.Black, 700, 650, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 550, 670, 720, 670);
    
                e.Graphics.FillRectangle(Brushes.Gray, new Rectangle(100, 700, 620, 20));
                e.Graphics.DrawRectangle(Pens.Black, new Rectangle(100, 700, 620, 20));
    
                e.Graphics.DrawString("Order Evaluation", boldString, Brushes.White, 100, 700);
    
                e.Graphics.DrawString("Rate Applied:", boldString, Brushes.Black, 100, 740);
                e.Graphics.DrawString(txtRateApplied.Text, regularString, Brushes.Black, 300, 740, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 760, 380, 760);
    
                e.Graphics.DrawString("Tax Rate:", boldString, Brushes.Black, 420, 740);
                e.Graphics.DrawString(txtTaxRate.Text, regularString, Brushes.Black, 640, 740, fmtString);
                e.Graphics.DrawString("%", regularString, Brushes.Black, 640, 740);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 760, 720, 760);
    
                e.Graphics.DrawString("Sub-Total:", boldString, Brushes.Black, 100, 770);
                e.Graphics.DrawString(txtSubTotal.Text, regularString, Brushes.Black, 300, 770, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 790, 380, 790);
    
                e.Graphics.DrawString("Tax Amount:", boldString, Brushes.Black, 420, 770);
                e.Graphics.DrawString(txtTaxAmount.Text, regularString, Brushes.Black, 640, 770, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 790, 720, 790);
    
                e.Graphics.DrawString("Order Total:", boldString, Brushes.Black, 420, 800);
                e.Graphics.DrawString(txtOrderTotal.Text, regularString, Brushes.Black, 640, 800, fmtString);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 820, 720, 820);
    
                e.Graphics.DrawString("Order Status:", boldString, Brushes.Black, 100, 830);
                e.Graphics.DrawString(cbxOrdersStatus.Text, regularString, Brushes.Black, 260, 830);
                e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 850, 720, 850);
    
                e.Graphics.DrawString("Notes:", boldString, Brushes.Black, 100, 860);
                e.Graphics.DrawString(txtNotes.Text, regularString, Brushes.Black, 260, 860);
            }
        }
    }
  54. Return the Update Rental Order form and double-click the Print button
  55. Implement its event as follows:
    private void btnPrint_Click(object sender, EventArgs e)
    {
        if (dlgPrint.ShowDialog() == DialogResult.OK)
            docPrint.Print();
    }
  56. Return to the Update Rental Order form and double-click the Print Preview button
  57. Implement the event as follows:
    private void btnPrintPreview_Click(object sender, EventArgs e)
    {
        dlgPrintPreview.ShowDialog();
    }
  58. Return to the Update Rental Order form and double-click the Rental Rates button
  59. Implement the event as follows:
    private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates rrs = new RentalRates();
        rrs.Show();
    }
  60. Return to the Update Rental Order form and click the Mileage End text box
  61. In the Events section of the Properties window, double-click Leave and implement the event as follows:
     private void txtMileageEnd_Leave(object sender, EventArgs e)
    {
    	    int mileageStart = 0, mileageEnd = 0, mileageTotal;
    
    	    if (string.IsNullOrEmpty(txtMileageStart.Text))
            	return;
    	    if (string.IsNullOrEmpty(txtMileageEnd.Text))
            	return;
    
    	    try
    	    {
            	mileageStart = int.Parse(txtMileageStart.Text);
    	    }
    	    catch (FormatException fe)
    	    {
            	MessageBox.Show("The value you provided for the mileage start is not valid.\n" +
                    	        "Please report the error as: " + fe.Message,
                            	"Bethesda Car Rental",
            	                MessageBoxButtons.OK, MessageBoxIcon.Information);
    	    }
    
    	    try
    	    {
            	mileageEnd = int.Parse(txtMileageEnd.Text);
    	        mileageTotal = mileageEnd - mileageStart;
    
    	        txtMileageTotal.Text = mileageTotal.ToString();
    	    }
    	    catch (FormatException fe)
    	    {
            	MessageBox.Show("The value you provided for the mileage end is not valid.\n" +
                    	        "Please report the error as: " + fe.Message,
                            	"Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
    	    }
    }
  62. Return to the form and double-click the Start Date date time picker
  63. Implement the event as follows:
    private void dtpStartDate_ValueChanged(object sender, EventArgs e)
    {
        dtpEndDate.Value = dtpStartDate.Value;
    }
  64. Return to the form and double-click the End Date date time picker
  65. Implement the event as follows:
    private void dtpEndDate_ValueChanged(object sender, EventArgs e)
    {
        int days;
    
        DateTime dteStart = this.dtpStartDate.Value;
        DateTime dteEnd = this.dtpEndDate.Value;
    
        // Let's calculat the difference in days
        TimeSpan tme = dteEnd - dteStart;
        days = tme.Days;
    
        // If the customer returns the car the same day, 
        // we consider that the car was rented for 1 day
        if (days == 0)
            days = 1;
    
        txtTotalDays.Text = days.ToString();
        // At any case, we will let the clerk specify the actual number of days
    }
  66. Return to the form and double-click the Rental Rates button
  67. Implement the event as follows:
     private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates rrs = new RentalRates();
        rrs.Show();
    }
  68. Return to the form and click the Rate Applied text box
  69. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtRateApplied_Leave(object sender, EventArgs e)
    {
        int days = 0;
        double rateApplied = 0D;
    
        if (string.IsNullOrEmpty(txtRateApplied.Text))
            return;
    
        try
        {
            rateApplied = double.Parse(txtRateApplied.Text);
            days = int.Parse(txtTotalDays.Text);
    
            txtSubTotal.Text = (rateApplied * days).ToString("F");
        }
        catch (FormatException fe)
        {
            MessageBox.Show("The value you provided for the rate is not valid.\n" +
                            "Please report the error as: " + fe.Message,
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
  70. Return to the form and double-click the Calculate button
  71. Implement the event as follows:
    private void btnCalculate_Click(object sender, EventArgs e)
    {
        int Days = 0;
        double RateApplied = 0.00;
        double SubTotal = 0.00;
        double TaxRate = 0.00;
        double TaxAmount = 0.00;
        double OrderTotal = 0.00;
    
        try
        {
            Days = int.Parse(txtTotalDays.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Number of Days",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        try
        {
            RateApplied = double.Parse(txtRateApplied.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Amount for Rate Applied",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        SubTotal = Days * RateApplied;
        txtSubTotal.Text = SubTotal.ToString("F");
    
        try
        {
            TaxRate = double.Parse(txtTaxRate.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Tax Rate",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        TaxAmount = SubTotal * TaxRate / 100;
        txtTaxAmount.Text = TaxAmount.ToString("F");
    
        OrderTotal = SubTotal + TaxAmount;
        txtOrderTotal.Text = OrderTotal.ToString("F");
    }
  72. Return to the form and double-click the Reset Form button
  73. Implement the event as follows:
    private void btnResetForm_Click(object sender, EventArgs e)
    {
        txtReceiptNumber.Text = "";
        txtRentStartProcessedBy.Text = "";
        txtRentStartEmployeeName.Text = "";
        txtRentEndProcessedBy.Text = "";
        txtRentEndEmployeeName.Text = "";
        txtCustomerFirstName.Text = "";
        txtCustomerLastName.Text = "";
        txtCustomerAddress.Text = "";
        txtCustomerCity.Text = "";
        cbxCustomerStates.Text = "";
        txtCustomerZIPCode.Text = "";
        txtTagNumber.Text = "";
        txtCustomerAddress.Text = "";
        txtCustomerCity.Text = "";
        cbxCustomerStates.Text = "";
        txtCustomerZIPCode.Text = "";
        cbxConditions.SelectedIndex = 0;
        cbxTankLevels.SelectedIndex = 0;
        txtMileageStart.Text = "";
        txtMileageEnd.Text = "";
        txtMileageTotal.Text = "";
        txtRateApplied.Text = "";
        txtTaxRate.Text = "7.70";
        txtTaxAmount.Text = "0.00";
        txtSubTotal.Text = "0.00";
        txtOrderTotal.Text = "";
        dtpStartDate.Value = DateTime.Today;
        dtpEndDate.Value = DateTime.Today;
        txtTotalDays.Text = "0.00";
        cbxOrdersStatus.SelectedIndex = 0;
        txtNotes.Text = "";
    }

Creating a View in SQL

The simplest formula to create a view is as follows:

CREATE VIEW [Schema].ViewName
AS
SELECT Statement

Practical LearningPractical Learning: Creating a View

  1. Click inside the SQL Query window and press Ctrl + A
  2. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW Assets.Cars
    AS
        SELECT TagNumber,
    	   Category,
    	   Make,
    	   Model,
    	   Doors,
    	   Passengers,
    	   Condition,
    	   Availability
        FROM Assets.Vehicles
    GO
  3. Right-click inside the SQL Query window and click Execute
  4. To create a new form, in the Solution Explorer, right-click BethedaCarRental2 -> Add -> Windows Form...
  5. Set the Name to Vehicles
  6. Click Add
  7. Design the form as follows:
     
    Bethesda Car Rental - Vehicles
    Control (Name) Text Other Properties
    List View List View lvwVehicles   FullRowSelect: True
    GridLines: True
       
    (Name) Text TextAlign Width
    colTagNumber Tag #    
    colCategory Category   70
    colMake Make   70
    colModel Model   100
    colDoors Doors Right 40
    colPassengers Passengers Right 68
    colCondition Condition    
    colAvailability Availability   85
    Button Button btnClose Close  
  8. Double-click an unoccupied area of the form
  9. Return to the form and double-click the Close button
  10. change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class Vehicles : Form
        {
            public Vehicles()
            {
                InitializeComponent();
            }
    
            private void ShowVehicles()
            {
                lvwVehicles.Items.Clear();
    
                using (SqlConnection scBethesdaCarRental = new SqlConnection("Data Source=(local);" +
                                                                        "Database=BethesdaCarRental12;" +
                                                                        "Integrated Security=Yes;"))
                {
                    SqlCommand cmdVehicles = new SqlCommand("SELECT * FROM Assets.Cars;",
                                                                 scBethesdaCarRental);
                    scBethesdaCarRental.Open();
                    SqlDataAdapter sdaVehicles = new SqlDataAdapter(cmdVehicles);
                    DataSet dsVehicles = new DataSet("SubCategoriesSet");
                    sdaVehicles.Fill(dsVehicles);
    
                    foreach (DataRow drVehicle in dsVehicles.Tables[0].Rows)
                    {
                        ListViewItem lviVehicle = new ListViewItem(drVehicle["TagNumber"].ToString());
                        lviVehicle.SubItems.Add(drVehicle["Category"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Make"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Model"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Doors"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Passengers"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Condition"].ToString());
    		    lviVehicle.SubItems.Add(drVehicle["Availability"].ToString());
                        lvwVehicles.Items.Add(lviVehicle);
                    }
                }
            }
    
            private void Cars_Load(object sender, EventArgs e)
            {
                ShowVehicles();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
 

Deleting a View

The formula to programmatically delete a view in SQL is:

DROP VIEW [Schema.]ViewName

Data Entry Using a View

Besides the ability to select records, views can be used to add records to a table:

Practical LearningPractical Learning: Performing Data Entry Using a View

  1. Click inside the SQL Query window and press Ctrl + A
  2. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW Transactions.NewRentalOrders
    AS
        SELECT RentStartProcessedBy,
    	   RentEndProcessedBy,
    	   CustomerFirstName,
    	   CustomerLastName,
    	   CustomerAddress,
    	   CustomerCity,
    	   CustomerState,
    	   CustomerZIPCode,
    	   TagNumber,
    	   Condition,
    	   TankLevel,
    	   MileageStart,
    	   StartDate,
    	   RateApplied,
    	   OrderStatus,
    	   Notes
        FROM Transactions.RentalOrders;
    GO
  3. Right-click inside the SQL Query window and click Execute
  4. Display the New Rental Order form and double-click the Save button
  5. Change the document as follows:
    private void btnSave_Click(object sender, EventArgs e)
    {
                if (string.IsNullOrEmpty(txtRentStartProcessedBy.Text))
                {
                    MessageBox.Show("You must specify the employee who processed the rental order.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                if (string.IsNullOrEmpty(txtTagNumber.Text))
                {
                    MessageBox.Show("You must specify the car that was rented.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                using (SqlConnection scBethesdaCarRental =
                    new SqlConnection(@"Data Source='CENTRAL\MSSQLSERVER2012';" +
                                      "Database='BethesdaCarRental1';" +
                                      "Integrated Security=yes;"))
                {
                    SqlCommand cmdRentalOrders = new SqlCommand("INSERT INTO Transactions.NewRentalOrders(" +
                        "RentStartProcessedBy, RentEndProcessedBy, CustomerFirstName, CustomerLastName, " +
                        "CustomerAddress, CustomerCity, CustomerState, CustomerZIPCode, TagNumber, " +
                        "Condition, TankLevel, MileageStart, StartDate, RateApplied, OrderStatus) VALUES(N'" +
                        txtRentStartProcessedBy.Text + "', N'" + txtRentStartProcessedBy.Text + "', N'" +
                        txtCustomerFirstName.Text + "', N'" + txtCustomerLastName.Text + "', N'" +
                        txtCustomerAddress.Text + "', N'" + txtCustomerCity.Text + "', N'" +
                        cbxCustomerStates.Text + "', N'" + txtCustomerZIPCode.Text + "', N'" +
                        txtTagNumber.Text + "', N'" + cbxConditions.Text + "', N'" + cbxTankLevels.Text +
                        "', N'" + txtMileageStart.Text + "', N'" + dtpStartDate.Value.ToShortDateString() + "', " +
                        double.Parse(txtRateApplied.Text) + ", N'" + cbxOrdersStatus.Text + "');",
                                                scBethesdaCarRental);
    
                    scBethesdaCarRental.Open();
                    cmdRentalOrders.ExecuteNonQuery();
    
                    MessageBox.Show("A new rental order has been created.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    }

Updating Records Using a View

Besides being used as intermediary for data entry, a view can be specially created to update one or more records of a table.

 
 
 

Practical LearningPractical Learning: Updating Records Using a View

  1. Click inside the SQL Query window and press Ctrl + A
  2. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW Transactions.RentalOrdersUpdates
    AS
        SELECT RentStartProcessedBy,
    	   RentEndProcessedBy,
    	   CustomerFirstName,
    	   CustomerLastName,
    	   CustomerAddress,
    	   CustomerCity,
    	   CustomerState,
    	   CustomerZIPCode,
    	   Condition,
    	   TankLevel,
    	   MileageStart,
    	   MileageEnd,
    	   MileageTotal,
    	   StartDate,
    	   EndDate,
    	   TotalDays,
    	   RateApplied,
    	   SubTotal,
    	   TaxRate,
    	   TaxAmount,
    	   OrderTotal,
    	   OrderStatus,
    	   Notes
        FROM Transactions.RentalOrders;
    GO
  3. Right-click inside the SQL Query window and click Execute
  4. Display the Update Rental Orders form and double-click the Update Rental Order button
  5. Implement the event as follows:
    private void btnUpdateRentalOrder_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(txtRentEndProcessedBy.Text))
        {
            MessageBox.Show("You must specify the employee who processed the rental return.",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (string.IsNullOrEmpty(txtMileageEnd.Text))
        {
            MessageBox.Show("You must specify the mileage end of the rented car.",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        using (SqlConnection scBethesdaCarRental =
            new SqlConnection("Data Source=(local);" +
                              "Database='BethesdaCarRental1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdRentalOrders =
                new SqlCommand("UPDATE Transactions.RentalOrders " +
                               "SET RentStartProcessedBy = N'" + txtRentStartProcessedBy.Text +  "', " +
                               "    RentEndProcessedBy   = N'" + txtRentStartProcessedBy.Text + "', " +
                               "    CustomerFirstName    = N'" + txtCustomerFirstName.Text + "', " +
                               "    CustomerLastName     = N'" + txtCustomerLastName.Text + "', " +
                               "    CustomerAddress      = N'" + txtCustomerAddress.Text + "', " +
                               "    CustomerCity         = N'" + txtCustomerCity.Text + "', " +
                               "    CustomerState        = N'" + cbxCustomerStates.Text + "', " +
                               "    CustomerZIPCode      = N'" + txtCustomerZIPCode.Text + "', " +
                               "    TagNumber            = N'" + txtTagNumber.Text + "', " +
                               "    Condition            = N'" + cbxConditions.Text + "', " +
                               "    TankLevel            = N'" + cbxTankLevels.Text + "', " +
                               "    MileageStart         = " + txtMileageStart.Text + ", " +
                               "    MileageEnd           = " + txtMileageEnd.Text + ", " +
                               "    MileageTotal         = " + txtMileageTotal.Text + ", " +
                               "    StartDate            = N'" + dtpStartDate.Value.ToShortDateString() + "', " +
                               "    EndDate              = N'" + dtpEndDate.Value.ToShortDateString() + "', " +
                               "    TotalDays            = " + txtTotalDays.Text + ", " +
                               "    RateApplied          = " + txtRateApplied.Text + ", " +
                               "    SubTotal             = " + txtSubTotal.Text + ", " +
                               "    TaxRate              = " + txtTaxRate.Text + ", " +
                               "    TaxAmount            = " + txtTaxAmount.Text + ", " +
                               "    OrderTotal           = " + txtOrderTotal.Text + ", " +
                               "    OrderStatus          = N'" + cbxOrdersStatus.Text + "', " +
                               "    Notes                = N'" + txtNotes.Text + "' " +
                               "WHERE ReceiptNumber      = " + txtReceiptNumber.Text + ";",
                                        scBethesdaCarRental);
    
            scBethesdaCarRental.Open();
            cmdRentalOrders.ExecuteNonQuery();
    
            MessageBox.Show("The rental order has been updated.",
                            "Bethesda Car Rental",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        btnResetForm_Click(sender, e);
    }
  6. Display the Bethesda Car Rental form and double-click the Vehicles button
  7. Implement the event as follows:
    private void btnCars_Click(object sender, EventArgs e)
    {
        Vehicles cs = new Vehicles();
        cs.Show();
    }
  8. Execute the application and click the Vehicles button
  9. Close the form and return to your programming environment
  10. If necessary, start Microsoft Visual Studio and open the Bethesda Car Rental Application from the previous lesson (if you had closed the project, in the Server Explorer, right-click the BethesdaCarRental3 connection and click New Query).
    Click inside the SQL Query window and press Ctrl + A
  11. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW Transactions.RentalOrdersPreviews
    AS
        SELECT RentStartProcessedBy,
    	   RentEndProcessedBy,
    	   CustomerFirstName,
    	   CustomerLastName,
    	   CustomerAddress,
    	   CustomerCity,
    	   CustomerState,
    	   CustomerZIPCode,
    	   TagNumber,
    	   Condition,
    	   TankLevel,
    	   MileageStart,
    	   MileageEnd,
    	   MileageTotal,
    	   StartDate,
    	   EndDate,
    	   TotalDays,
    	   RateApplied,
    	   SubTotal,
    	   TaxRate,
    	   TaxAmount,
    	   OrderTotal,
    	   OrderStatus,
    	   Notes,
    	   RentalOrderNumber
        FROM Transactions.RentalOrders;
    GO
  12. Right-click inside the document and click Execute

Views and Expressions

An expression can be used to combine a column and a constant value, or more than one column to get a new column that presents some values to the user.

Practical LearningPractical Learning: Adding an Expression to a View

  1. Click inside the SQL Query window and press Ctrl + A
  2. To create views, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW HumanResources.Clerks
    AS
        SELECT EmployeeNumber,
    	   FirstName + N' ' + LastName AS Clerk
        FROM HumanResources.Employees;
    GO
    
    CREATE VIEW Assets.VehiclesSelected
    AS
        SELECT TagNumber,
    	   Make + N' ' + Model AS Vehicle
        FROM Assets.Cars;
    GO
  3. Right-click inside the document and click Execute
  4. Display the New Rental Order form and click the Employee # text box
  5. In the Properties window, click the Events button if necessary and double-click Leave
  6. Implement the event as follows:
    private void txtRentStartProcessedBy_Leave(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
        		new SqlConnection("Data Source=(local);" +
                                       "Database=BethesdaCarRental;" +
                                       "Integrated Security=Yes;"))
        {
            SqlCommand cmdClerks =
                new SqlCommand("SELECT Clerk " +
                               "FROM HumanResources.Clerks " +
                               "WHERE EmployeeNumber = N'" + txtRentStartProcessedBy.Text + "';",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataReader sdrClerks = cmdClerks.ExecuteReader();
    
            while(sdrClerks.Read())
                txtRentStartEmployeeName.Text = sdrClerks[0].ToString();
        }
    }
  7. Return to the New Rental Order form and click the Tag Number text box
  8. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtTagNumber_Leave(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
        		new SqlConnection("Data Source=(local);" +
                                       "Database=BethesdaCarRental1;" +
                                       "Integrated Security=Yes;"))
        {
            SqlCommand cmdVehicles =
                new SqlCommand("SELECT Vehicle " +
                               "FROM Assets.VehiclesSelected " +
                               "WHERE TagNumber = N'" + txtTagNumber.Text + "';",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataReader sdrVehicles = cmdVehicles.ExecuteReader();
    
            while (sdrVehicles.Read())
                txtVehicleSelected.Text = sdrVehicles[0].ToString();
        }
    }
  9. Display the Update Rental Order form and click the Employee # text box under Rent Start Processed By
  10. In the Events  section of the Properties window, double-click Leave
  11. Implement the event as follows:
    private void txtRentStartProcessedBy_Leave(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
        		new SqlConnection("Data Source=(local);" +
                                       "Database=BethesdaCarRental1;" +
                                       "Integrated Security=Yes;"))
        {
            SqlCommand cmdClerks =
                new SqlCommand("SELECT Clerk " +
                               "FROM HumanResources.Clerks " +
                           "WHERE EmployeeNumber = N'" + txtRentStartProcessedBy.Text + "';",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataReader sdrClerks = cmdClerks.ExecuteReader();
    
            while(sdrClerks.Read())
                txtRentStartEmployeeName.Text = sdrClerks[0].ToString();
        }
    }
  12. Return to the Update Rental Order form and click the Employee # text box under Rent End Processed By
  13. In the Events  section of the Properties window, double-click Leave
  14. Implement the event as follows:
    private void txtRentEndProcessedBy_Leave(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
        		new SqlConnection("Data Source=(local);" +
                                       "Database=BethesdaCarRental1;" +
                                       "Integrated Security=Yes;"))
        {
            SqlCommand cmdClerks =
                new SqlCommand("SELECT Clerk " +
                               "FROM HumanResources.Clerks " +
                             "WHERE EmployeeNumber = N'" + txtRentEndProcessedBy.Text + "';",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataReader sdrClerks = cmdClerks.ExecuteReader();
    
            while(sdrClerks.Read())
                txtRentEndEmployeeName.Text = sdrClerks[0].ToString();
        }
    }
  15. Return to the Update Rental Order form and click the Tag Number text box
  16. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtTagNumber_Leave(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
        		new SqlConnection("Data Source=(local);" +
                                       "Database=BethesdaCarRental1;" +
                                       "Integrated Security=Yes;"))
        {
            SqlCommand cmdVehicles =
                new SqlCommand("SELECT Vehicle " +
                               "FROM Assets.VehiclesSelected " +
                               "WHERE TagNumber = N'" + txtTagNumber.Text + "';",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataReader sdrVehicles = cmdVehicles.ExecuteReader();
    
            while (sdrVehicles.Read())
                txtVehicleSelected.Text = sdrVehicles[0].ToString();
        }
    }
  17. Return to the Update Rental Order form and double-click the Open button
  18. Implement the event as follows:
    private void btnOpen_Click(object sender, EventArgs e)
    {
        using (SqlConnection scBethesdaCarRental =
                new SqlConnection("Data Source=(local);" +
                                   "Database=BethesdaCarRental1;" +
                                   "Integrated Security=Yes;"))
        {
            SqlCommand cmdRentalOrders =
                new SqlCommand("SELECT RentStartProcessedBy, RentEndProcessedBy, " +
                               "       CustomerFirstName, CustomerLastName, " +
                               "       CustomerAddress, CustomerCity, CustomerState, " +
                               "       CustomerZIPCode, TagNumber, Condition, TankLevel, " +
                               "       MileageStart, MileageEnd, MileageTotal, StartDate, " +
                               "       EndDate, TotalDays, RateApplied, SubTotal, TaxRate, " +
                               "       TaxAmount, OrderTotal, OrderStatus, Notes " +
                               "FROM Transactions.RentalOrdersPreviews " +
                               "WHERE ReceiptNumber = " + txtReceiptNumber.Text + ";",
                               scBethesdaCarRental);
            scBethesdaCarRental.Open();
            SqlDataAdapter sdaRentalOrders = new SqlDataAdapter(cmdRentalOrders);
            DataSet dsRentalOrders = new DataSet("RentalOrdersSet");
            sdaRentalOrders.Fill(dsRentalOrders);
    
            foreach (DataRow drRentalOrder in dsRentalOrders.Tables[0].Rows)
            {
                txtRentStartProcessedBy.Text = drRentalOrder["RentStartProcessedBy"].ToString();
                txtRentEndProcessedBy.Text   = drRentalOrder["RentEndProcessedBy"].ToString();
                txtCustomerFirstName.Text    = drRentalOrder["CustomerFirstName"].ToString();
                txtCustomerLastName.Text     = drRentalOrder["CustomerLastName"].ToString();
                txtCustomerAddress.Text      = drRentalOrder["CustomerAddress"].ToString();
                txtCustomerCity.Text         = drRentalOrder["CustomerCity"].ToString();
                cbxCustomerStates.Text       = drRentalOrder["CustomerState"].ToString();
                txtCustomerZIPCode.Text      = drRentalOrder["CustomerZIPCode"].ToString();
                txtTagNumber.Text            = drRentalOrder["TagNumber"].ToString();
                cbxConditions.Text           = drRentalOrder["Condition"].ToString();
                cbxTankLevels.Text           = drRentalOrder["TankLevel"].ToString();
                txtMileageStart.Text         = drRentalOrder["MileageStart"].ToString();
                txtMileageEnd.Text           = drRentalOrder["MileageEnd"].ToString();
                txtMileageTotal.Text         = drRentalOrder["MileageTotal"].ToString();
                dtpStartDate.Text            = drRentalOrder["StartDate"].ToString();
                dtpEndDate.Text  	         = drRentalOrder["EndDate"].ToString();
                txtTotalDays.Text   	 = drRentalOrder["TotalDays"].ToString();
                txtRateApplied.Text	     	 = drRentalOrder["RateApplied"].ToString();
                txtSubTotal.Text		 = drRentalOrder["SubTotal"].ToString();
                txtTaxRate.Text		 = drRentalOrder["TaxRate"].ToString();
                txtTaxAmount.Text		 = drRentalOrder["TaxAmount"].ToString();
                txtOrderTotal.Text		 = drRentalOrder["OrderTotal"].ToString();
                cbxOrdersStatus.Text	 = drRentalOrder["OrderStatus"].ToString();
                txtNotes.Text		 = drRentalOrder["Notes"].ToString();
            }
        }
    
        txtRentStartProcessedBy_Leave(sender, e);
        txtRentEndProcessedBy_Leave(sender, e);
        txtTagNumber_Leave(sender, e);
    }

Views and Functions

A function can be called in a view to create better columns to present records to a user.

Practical LearningPractical Learning: Using a Function in a View

  1. Click inside the SQL Query window and press Ctrl + A
  2. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW HumanResources.StaffMembers
    AS
        SELECT empls.EmployeeNumber [Employee #],
    	   CONCAT(empls.FirstName, N' ', empls.LastName) [Employee Name],
    	   empls.Title,
    	   empls.PhoneNumber    [Phone #]
        FROM HumanResources.Employees empls
    GO
  3. Right-click inside the document and click Execute
  4. To create a new form, in the Solution Explorer, right-click BethedaCarRental1 -> Add -> Windows Form...
  5. Set the Name to Employees
  6. Click Add
  7. Design the form as follows:
     
    Bethesda Car Rental - Employees
    Control (Name) Text TextAlign
    DataGridView DataGridView dgvEmployees    
    Button Button btnClose Close  
  8. Double-click an unoccupied area of the form
  9. Return to the form and double-click the Close button
  10. change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class Employees : Form
        {
            public Employees()
            {
                InitializeComponent();
            }
    
            private void ShowEmployees()
            {
                using (SqlConnection scBethesdaCarRental = new SqlConnection("Data Source=(local);" +
                                                                        "Database=BethesdaCarRental1;" +
                                                                        "Integrated Security=Yes;"))
                {
                    SqlCommand cmdEmployees = new SqlCommand("SELECT * FROM HumanResources.StaffMembers;",
                                                                 scBethesdaCarRental);
                    scBethesdaCarRental.Open();
                    SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
                    DataSet dsEmployees = new DataSet("StaffMembersSet");
                    sdaEmployees.Fill(dsEmployees);
                    dgvEmployees.DataSource = dsEmployees.Tables[0];
                }
            }
    
            private void Employees_Load(object sender, EventArgs e)
            {
                ShowEmployees();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  11. Display the Bethesda Car Rental form and double-click the Employees button
  12. Implement the event as follows:
    private void btnEmployees_Click(object sender, EventArgs e)
    {
        Employees empls = new Employees();
        empls.Show();
    }
  13. Click inside the SQL Query window and press Ctrl + A
  14. To create a view, type the following code:
    USE BethesdaCarRental1;
    GO
    
    CREATE VIEW Transactions.RentalContracts
    AS
        SELECT Ros.ReceiptNumber [Recept #],
    	   CONCAT(RSClerks.EmployeeNumber, N': ', RSClerks.FirstName, N', ', RSClerks.LastName) [Rent Start Clerk],
    	   CONCAT(REClerks.EmployeeNumber, N': ', REClerks.FirstName, N' ', REClerks.LastName) [Rent End Clerk], 
    	   CONCAT(Ros.CustomerFirstName, N' ', Ros.CustomerLastName) Customer,
    	   CONCAT(Cs.TagNumber, N': ', Cs.Make, N' ', Cs.Model) Vehicle,
    	   Ros.Condition,
    	   Ros.TankLevel Tank,
    	   Ros.MileageStart [Mileage Start],
    	   Ros.MileageEnd [Mileage End],
    	   Ros.MileageTotal [Mileage Total],
    	   Ros.StartDate [Start Date],
    	   Ros.EndDate [End Date],
    	   Ros.TotalDays Days,
    	   ros.RateApplied Rate,
    	   Ros.SubTotal [Sub-Total],
    	   Ros.TaxRate [Tax Rate],
    	   Ros.TaxAmount [Tax Amount],
    	   Ros.OrderTotal [Order Total],
    	   Ros.OrderStatus [Status]
        FROM Transactions.RentalOrders Ros
        INNER JOIN HumanResources.Employees RSClerks ON Ros.RentStartProcessedBy = RSClerks.EmployeeNumber
        INNER JOIN HumanResources.Employees REClerks ON Ros.RentEndProcessedBy = REClerks.EmployeeNumber
        INNER JOIN Assets.Vehicles Cs ON Ros.TagNumber = Cs.TagNumber;
    GO
  15. Right-click inside the document and click Execute
  16. To create a new form, in the Solution Explorer, right-click BethedaCarRental1 -> Add -> Windows Form...
  17. Set the Name to RentalOrders
  18. Click Add
  19. Design the form as follows:
     
    Bethesda Car Rental - Rental Orders
    Control (Name) Text Other Properties
    Data Grid View Data Grid View dgvRentalOrders   Anchor: Top, Bottom, Left, Right
    ColumnHeadersHeightSizeMode:
    EnableResizing
    Button Button btnNewRentalOrder New Rental Order ... Anchor: Bottom, Right
    Button Button btnUpdateRentalOrder Return Car/Update Rental Order ... Anchor: Bottom, Right
    Button Button btnClose Close Anchor: Bottom, Right
  20. Double-click an unoccupied area of the form
  21. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental2
    {
        public partial class RentalOrders : Form
        {
            public RentalOrders()
            {
                InitializeComponent();
            }
    
            private void ShowRentalOrders()
            {
                using (SqlConnection scBethesdaCarRental = new SqlConnection("Data Source=(local);" +
                                                                        "Database=BethesdaCarRental1;" +
                                                                        "Integrated Security=Yes;"))
                {
                    SqlCommand cmdRentalOrders = new SqlCommand("SELECT * FROM Transactions.RentalContracts;",
                                                                 scBethesdaCarRental);
                    scBethesdaCarRental.Open();
                    SqlDataAdapter sdaRentalOrders = new SqlDataAdapter(cmdRentalOrders);
                    DataSet dsRentalOrders = new DataSet("RentalOrdersSet");
                    sdaRentalOrders.Fill(dsRentalOrders);
                    dgvRentalOrders.DataSource = dsRentalOrders.Tables[0];
                }
            }
    
            private void RentalOrders_Load(object sender, EventArgs e)
            {
                ShowRentalOrders();
            }
        }
    }
  22. Return to the Rental Orders form and double-click the New Rental Order button
  23. Implement the event as follows:
    private void btnNewRentalOrder_Click(object sender, EventArgs e)
    {
        NewRentalOrder nro = new NewRentalOrder();
        nro.ShowDialog();
    
        ShowRentalOrders();
    }
  24. Return to the Rental Orders form and double-click the Update Rental Order button
  25. Implement the event as follows:
    private void btnUpdateRentalOrder_Click(object sender, EventArgs e)
    {
        UpdateRentalOrder uro = new UpdateRentalOrder();
        uro.ShowDialog();
    
        ShowRentalOrders();
    }
  1. Return to the Rental Orders form and double-click the Close button
  2. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  3. Display the Bethesda Car Rental form and double-click the Rental Orders button
  4. Implement the event as follows:
    private void btnRentalOrders_Click(object sender, EventArgs e)
    {
        RentalOrders ros = new RentalOrders();
        ros.Show();
    }
  5. Return to the Bethesda Car Rental form and double-click the Close Order button
  6. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
 
 
   

Practical LearningPractical Learning: Executing the Application

  1. Execute the application
  2. Click the Rental Orders button and the New Rental Order button
  3. Create three new rental orders as follows:
     
    RentStartProcessedBy First Name Last Name Address City State ZIP Code Tag # Condition Tank Level Mileage Start Start Date Rate Applied Order Status
    20480 Marcel Buhler 6800 Haxell Crt Alexandria VA 22314 DFP924 Excellent Empty 12728 6/16/2014 69.95 Vehicle With Customer
    24793 Joan Altman 3725 South Dakota Ave NW Washington DC 20012 4AF9284 Good 1/2 Tank 24715 6/20/2014 28.95 Vehicle With Customer
    38240 Thomas Filder 4905 Herrenden St Arlington VA 22204 BND927 Good Full 6064 6/20/2014 34.95 Vehicle With Customer
  4. Close the New Rental Order form
  5. Click the Update Rental Order button
  6. Type the rental order number in the Receipt # and click Open, enter the other values and click Calculate, then click the Update button:
     
    Receipt # RentEndProcessedBy Tank Level Mileage End End Date Order Status
    100003 40508   6229 6/26/2014 Order Complete
    100001 24793 3/4 Full 13022 6/20/2014 Order Complete
  7. Close the Update Rental Orders form
  8. Click the New Rental Order button
  9. Create a new rental order as follows:
     
    RentStartProcessedBy First Name Last Name Address City State ZIP Code Tag # Condition Tank Level Mileage Start Start Date Rate Applied Order Status
    73948 Gregory Strangeman 5530 Irving St College Park MD 20740 2AT9274 Excellent 1/2 Tank 8206 12/15/2014 28.95 Vehicle With Customer
  10. Close the New Rental Order form
  11. Click the Update Rental Order button and update the following rental order:
     
    Receipt # RentEndProcessedBy Tank Level Mileage End End Date Order Status
    100002 38240 Full 25694 12/15/2014 Order Complete
  12. Close the Update Rental Orders form
  13. Click the New Rental Order button
  14. Create a new rental order as follows:
     
    RentStartProcessedBy First Name Last Name Address City State ZIP Code Tag # Condition Tank Level Mileage Start Start Date Rate Applied Order Status Notes
    73948 Michelle Russell 10070 Weatherwood Drv Rockville MD 20853 8AE9294 Excellent Full 3659 12/15/2013 32.75 Car With Customer The weekly rate for the Compact size was applied because there was no Full-Size vehicle available.
  15. Close the New Rental Order form
  16. Click the Update Rental Order button and update the following rental order:
     
    Receipt # RentEndProcessedBy Tank Level Mileage End End Date Order Status
    100004 73948 3/4 Full 8412 12/10/2014 Order Complete
    100005 24793 Full 3806 12/17/2013 Order Complete
  17. Close the Update Rental Orders form
  18. Close the application

Application

 

Home/a> Copyright © 2014, FunctionX