Home

LINQ and Data Sets Application: Bethesda Car Rental

   

Fundamentals of LINQ and Data Sets

 

Introduction

As you may know already, DataSet is the most fundamental data-based class of the .NET Framework. In the same way, DataTable is the most fundamental class used for tables in databases of the .NET Framework. Like many early objects of the .NET Framework, when the DataTable class was first created, the LINQ was not yet ready. Later on, to provide the ability to query some records, some extensions were added to many classes. Therefore, the DataTable class received a series of members as extension methods. This was done in a new class named DataTableExtensions.

Practical LearningPractical Learning: Introducing LINQ and Data Sets

  1. Start Microsoft Visual Studio
  2. Create a new Windows Application named BethesdaCarRental3
  3. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental1 -> Add -> Windows Form...
  4. Set the Name to Employees and click Add
  5. From the Data section of the Toolbox, click DataSet and click the form
  6. Click Untyped Dataset and click OK
  7. In the Properties window, change the following characteristics:
    DataSetName: Employees
    (Name): dsEmployees
  8. Click Tables and click its button
  9. Click Add and change the following characteristics:
    TableName: Employee
    (Name) tblEmployee
  10. Click Columns and click its button
  11. Click Add continuously and create the following columns:
     
    AllowDBNull ColumnName (Name) Expression
    False EmployeeNumber colEmployeeNumber  
      FirstName colFirstName  
    False LastName colLastName  
      EmployeeName colEmployeeName LastName + ', ' + FirstName
      Title colTitle  
  12. Click Close and click Close
  13. Design the form as follows:
     
    Bethesda Car Rental - Employees
     
    Control Text Name Other Properties
    DataGridView   dgvEmployees DataSource: dsEmployees
    DataMember: Employee
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    EmployeeNumber Employee # 75
    FirstName First Name 75
    LastName Last Name 75
    EmployeeName Employee Name 120
    Title   140
  14. Double-click an unoccupied area of the form
  15. Return to the Employees form and click an unoccupied area of its body
  16. In the Properties window, click Events and double-click FormClosing
  17. Return to the form and double-click the Close button
  18. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental3
    {
        public partial class Employees : Form
        {
            public Employees()
            {
                InitializeComponent();
            }
    
            private void Employees_Load(object sender, EventArgs e)
            {
                string strFilename = @"C:\Bethesda Car Rental1\Employees.xml";
    
                if (File.Exists(strFilename))
                    dsEmployees.ReadXml(strFilename);
            }
    
            private void Employees_FormClosing(object sender, FormClosingEventArgs e)
            {
                dsEmployees.WriteXml(@"C:\Bethesda Car Rental1\Employees.xml");
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  19. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental1 -> Add -> Windows Form...
  20. Set the Name to NewVehicle and click Add
  21. Design the form as follows: 
     
    Bethesda Car Rental - New Vehicle
    Control (Name) Text Other Properties
    Label Label   Tag Number:  
    TextBox Text Box txtTagNumber    
    Label Label   Make:  
    TextBox Text Box txtMake    
    Label Label   Model:  
    TextBox Text Box txtModel    
    Label Label   Doors:  
    TextBox Text Box txtDoors    
    Label Label   Passengers:  
    TextBox Text Box txtPassengers    
    Label Label   Condition:  
    ComboBox Combo Box cbxConditions   DropDownStyle: DropDownList
    Items: Good
    Other
    Excellent
    Driveable
    Needs Repair
    Label Label   Category:  
    ComboBox Combo Box cbxCategories   DropDownStyle: DropDownList
    Items: SUV
    Truck
    Full Size
    Mini Van
    Compact
    Standard
    Economy
    Passenger Van
    Label Label   Availability:  
    ComboBox Combo Box cbxAvalabilities   DropDownStyle: DropDownList
    Items: Rented
    Reserved
    Available
    Being Serviced
    Vehicle Rented
    Vehicle With Customer
    PictureBox Picture Box pbxVehicle   SizeMode: Zoom
    Button Button btnSelectPicture Select Vehicle Picture...  
    Label Label lblPictureName Picture Name  
    Button Button btnSubmit Submit  
    Button Button btnClose Close  
    OpenFileDialog   dlgOpen Title: Select Item Picture
    DefaultExt: jpg
    Filter: JPEG Files (*.jpg,*.jpeg)|*.jpg|GIF Files (*.gif)|*.gif|Bitmap Files (*.bmp)|*.bmp|PNG Files (*.png)|*.png
    Form       FormBorderStyle: FixedDialog
    MaximizeBox: False
    MinimizeBox: False
    ShowInTaskbar: False
  22. From the Data section of the Toolbox, click DataSet and click the form
  23. Click Untyped Dataset and click OK
  24. In the Properties window, change the following characteristics:
    DataSetName: Vehicles
    (Name): dsVehicles
  25. Click Tables and click its button
  26. Click Add and change the following characteristics:
    TableName: Vehicle
    (Name) tblVehicle
  27. Click Columns and click its button
  28. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    TagNumber colTagNumber AllowDBNull: False
    Unique: True
    Make colMake  
    Model colModel  
    Doors colDoors DataType: System.UInt16
    Passengers colPassengers DataType: System.UInt16
    Condition colCondition  
    Category colCategory  
    Availability colAvailability  
  29. Click Close and click Close
  30. Double-click an unoccupied area of the form
  31. Return to the New Vehicle dialog box and double-click the Select Vehicle Picture button
  32. Return to the dialog box and double-click the Submit button
  33. Return to the dialog box and double-click Close
  34. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental3
    {
        public partial class NewVehicle : Form
        {
            public NewVehicle()
            {
                InitializeComponent();
            }
    
            private void NewVehicle_Load(object sender, EventArgs e)
            {
                string strFileName = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
                if (File.Exists(strFileName))
                    dsVehicles.ReadXml(strFileName);
            }
    
            private void btnSelectPicture_Click(object sender, EventArgs e)
            {
                if (dlgPicture.ShowDialog() == DialogResult.OK)
                {
                    lblPictureName.Text = dlgPicture.FileName;
                    pbxVehicle.Image = Image.FromFile(lblPictureName.Text);
                }
            }
    
            private void btnSubmit_Click(object sender, EventArgs e)
            {
                if (txtTagNumber.Text.Length == 0)
                {
                    MessageBox.Show("You must enter the car's tag number.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                if (txtMake.Text.Length == 0)
                {
                    MessageBox.Show("You must specify the car's manufacturer.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                if (txtModel.Text.Length == 0)
                {
                    MessageBox.Show("You must enter the model of the car.",
                                    "Bethesda Car Rental",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                try
                {
                    DataRow rowVehicle = tblVehicle.NewRow();
    
                    rowVehicle["TagNumber"]    = txtTagNumber.Text;
                    rowVehicle["Make"]         = txtMake.Text;
                    rowVehicle["Model"]        = txtModel.Text;
                    rowVehicle["Doors"]        = txtDoors.Text;
                    rowVehicle["Passengers"]   = txtPassengers.Text;
                    rowVehicle["Condition"]    = cbxVehiclesConditions.Text;
                    rowVehicle["Category"]     = cbxCategories.Text;
                    rowVehicle["Availability"] = cbxAvailabilities.Text;
                    tblVehicle.Rows.Add(rowVehicle);
    
                    dsVehicles.WriteXml(@"C:\Bethesda Car Rental1\Vehicles.xml");
    
                    if (lblPictureName.Text.Length != 0)
                    {
                        FileInfo flePicture = new FileInfo(lblPictureName.Text);
                        flePicture.CopyTo(@"C:\Bethesda Car Rental1\" + txtTagNumber.Text + flePicture.Extension);
                    }
                }
                catch (ConstraintException ce)
                {
                    MessageBox.Show(ce.Message);
                }
    
                Close();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  35. Return to the New Vehicle form, click dsVehicles and press Ctrl + C to copy
  36. To add a new form, on the main menu, click PROJECT -> Add Windows Form ...
  37. Set the Name to Vehicles and click Add
  38. Set the Size to around 765, 524
  39. Press Ctrl + V to paste dsVehicles
  40. From the Containers section of the Toolbox, click Panel and click the form
  41. Set its Dock property to Top
  42. From the Containers section of the Toolbox, click Panel and click the form
  43. Set its Dock property to Bottom
     
    Bethesda Car Rental: Vehicles Inventory
  44. From the Containers section of the Toolbox, click SplitContainer and click the middle-unoccupied area of the form
     
    Bethesda Car Rental: Vehicles Inventory
  45. From the Containers section of the Toolbox, click SplitContainer and click the right panel (Panel2) of the form
  46. In the Properties window, set the Oriantation to Horizontal
     
    Bethesda Car Rental: Vehicles Inventory
  47. Complete the design of the form as follows:
     
    Bethesda Car Rental: Vehicles Inventory
    Control (Name) Text Other Properties
    Label   Bethesda Car Rental - Vehicles Inventory ForeColor: Blue
    Label   ________________ Anchor: Left, Right
    AutoSize: False
    BackColor: Maroon
    BorderStyle: FixedSingle
    TreeView tvwVehicles   Dock: Fill
    FullRowSelect: True
    GridLines: True
    View: Details
    ListView lvwVehicles   Dock: Fill
     
    (Name) Text TextAlign Width
    lvcTagNumber Tag #   55
    lvcMake Make   70
    lvcModel Model   75
    lvcDoors Doors Right 40
    lvcPassengers Passengers Right 68
    lvcCondition Condition    
    lvcCategory Category   70
    lvcAvailability Availability   85
    PictureBox pbxVehicle   Dock: Fill
    SizeMode: Zoom
    Button btnNewVehicle New Vehicle...  
    Button Close btnClose  
  48. To add a new form to the project, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  49. Set the Name to RentalRates and press Enter
  50. In the Properties window, set FormBorderStyle to FixedToolWindow
  51. Add a ListView to the form and create its Columns as follows:
     
    (Name) Text TextAlign Width
    colCategory Category   90
    colDaily Daily Right  
    colWeekly Weekly Right  
    colMonthly Monthly Right  
    colWeekend Weekend Right  
  52. Create its Items as follows:
     
    ListViewItem SubItems SubItems SubItems SubItems
      Text Text Text Text
    Economy 35.95 32.75 28.95 24.95
    Compact 39.95 35.75 32.95 28.95
    Standard 45.95 39.75 35.95 32.95
    Full Size 49.95 42.75 38.95 35.95
    Mini Van 55.95 50.75 45.95 42.95
    SUV 55.95 50.75 45.95 42.95
    Truck 42.75 38.75 35.95 32.95
    Van 69.95 62.75 55.95 52.95
  53. Complete the design of the form as follows:
     
    Rental Rates
  54. To add a new form to the project, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  55. Set the Name to UpdateRentalOrder and press Enter
  56. Display the Employees form. Under it, click and copy dsEmployees
  57. Paste it on the UpdateRentalOrder form
  58. Display the NewVehicle form. Under it, click and copy dsVehicle
  59. Paste it on the UpdateRentalOrder form
  60. From the Data section of the Toolbox, click DataSet and click the form
  61. Click Untyped Dataset and click OK
  62. In the Properties window, change the following characteristics:
    DataSetName: RentalOrders
    (Name): dsRentalOrders
  63. Click Tables and click its button
  64. Click Add and change the following characteristics:
    TableName: RentalOrder
    (Name) tblRentalOrder
  65. Click Columns and click its ellipsis button
  66. In the Columns Collection Editor, click Add continuously and create the following columns:
       
    ColumnName (Name) DataType
    ReceiptNumber colReceiptNumber DataType: System.UInt32
    DateProcessed colDateProcessed System.DateTime
    EmployeeNumber colClerkNumber  
    CustomerFirstName colCustomerFirstName  
    CustomerLastName colCustomerLastName  
    CustomerAddress colCustomerAddress  
    CustomerCity colCustomerCity  
    CustomerState colCustomerState  
    CustomerZIPCode colCustomerZIPCode  
    TagNumber colVehicleTagNumber  
    VehicleCondition colVehicleCondition  
    TankLevel colTankLevel  
    MileageStart colMileageStart DataType: System.UInt32
    MileageEnd colMileageEnd DataType: System.UInt32
    MileageTotal colMileageTotal DataType: System.UInt32
    RentStartDate colRentStartDate DataType: System.DateTime
    RentEndDate colRendEndDate DataType: System.DateTime
    TotalDays colTotalDays DataType: System.UInt16
    RateApplied colRateApplied DataType: System.Double
    SubTotal colSubTotal DataType: System.Double
    TaxRate colTaxRate DataType: System.Double
    TaxAmount colTaxAmount DataType: System.Double
    OrderTotal colOrderTotal DataType: System.Double
    OrderStatus colOrderStatus  
    Notes colNotes  
  67. Click Close and click Close
  68. Click OK and click Close
  69. Design the form as follows:
     
    Bethesda Car Rental: Update Rental Order Processing
    Control (Name) Text Other Properties
    Label   Processed By: AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label   Order Timing AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label   Employee #:  
    TextBox txtEmployeeNumber    
    TextBox txtEmployeeName    
    Label   Date Processed:  
    TextBox dtpDateProcessed    
    Label   Processed For AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label   Rent Start Date:  
    DateTimePicker dtpRentStartDate    
    Label   First Name:  
    TextBox txtCustomerFirstName    
    Label   Last Name:  
    TextBox txtCustomerLastName    
    Label   Rent End Date:  
    DateTimePicker dtpRentEndDate    
    Label   Address:  
    TextBox txtCustomerAddress    
    Label   Total Days:  
    TextBox txtTotalDays 1 TextAlign: Right
    Label   City:  
    TextBox txtCustomerCity    
    Label   State:  
    Label Order Evaluation   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    ComboBox cbxCustomerStates   DropDownStyle: DropDownList
    Sorted: True
    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   ZIP/Postal Code:  
    TextBox txtCustomerZIPCode    
    Label   Rate Applied:  
    TextBox txtRateApplied 0.00 TextAlign: Right
    Button btnRentalRates Rental Rates  
    Label Vehicle Selected   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label   Sub-Total:  
    TextBox txtSubTotal 0.00 TextAlign: Right
    Button btnCalculate Calculate  
    Label   Tag Number:  
    TextBox txtTagNumber    
    Label   Condition:  
    ComboBox cbxVehicleConditions   Sorted: True
    Items:
    Bad
    Good
    Other
    Excellent
    Driveable
    Needs Repair
    Label   Tax Rate:  
    TextBox txtTaxRate 7.75 TextAlign: Right
    Label   %  
    Label   Make:  
    TextBox txtMake    
    Label   Model:  
    TextBox txtModel    
    Label   Tax Amount:  
    TextBox txtTaxAmount 0.00 TextAlign: Right
    Button Print... btnPrint  
    Label   Mileage Start:  
    TextBox txtMileageStart   TextAlign: Right
    Label   Tank Level:  
    ComboBox cbxTanksLevels   Empty
    1/4 Empty
    Half Tank
    3/4 Full
    Full
    Label   Order Total:  
    TextBox txtOrderTotal 0.00 TextAlign: Right
    Button btnPrintPreview Print Preview...  
    Label   Mileage End:  
    TextBox txtMileageEnd   TextAlign: Right
    Label   Mileage Total:  
    TextBox txtMileageTotal   TextAlign: Right
    Label Order Status    
    ComboBox   cbxOrderStatus Items:
    Order Reserved
    Vehicle With Customer
    Rental Order Completed
    Label   Notes:  
    TextBox txtNotes   Multiline: True
    ScrollBars: Vertical
    Label Receipt #:    
    TextBox   txtReceiptNumber  
    Button btnOpen btnOpen  
    Button btnSave Save  
    Button btnNewRentalOrder New Rental Order/Reset  
    Button btnClose Close  
  70. Double-click an unoccupied area of the form
  71. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using System.Xml;
    
    namespace BethesdaCarRental1
    {
        public partial class UpdateRentalOrder : Form
        {
            public UpdateRentalOrder()
            {
                InitializeComponent();
            }
    
            private void UpdateRentalOrder_Load(object sender, EventArgs e)
            {
                string strFilename = @"C:\Bethesda Car Rental1\Employees.xml";
    
                if (File.Exists(strFilename))
                    dsEmployees.ReadXml(strFilename);
    
                strFilename = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
                if (File.Exists(strFilename))
                    dsVehicles.ReadXml(strFilename);
    
                strFilename = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
                if (File.Exists(strFilename))
                    dsRentalOrders.ReadXml(strFilename);
            }
        }
    }
  72. To add a new form to the project, in the Solution Explorer, right-click BethesdaCarRental1 -> Add -> Windows Form...
  73. Set the Name to NewRentalOrder and press Enter
  74. Using the Properties window, apply the same Size of the UpdateRentalOrder form to the NewRentalOrder form
  75. Click the body of the UpdateRentalOrder form, press Ctrl + A to select everything, and press Ctrl + C to copy everything
  76. Click the body of the NewRentalOrder form and press Ctrl + V to paste everyting
  77. Click each control that is not necessary and press Delete to get:
     
    Bethesda Car Rental: New Rental Order Processing
  78. Display the Update Rental Order form and double-click the Rent Start Date date time picker control
  79. Implement the event as follows:
     private void dtpRentStartDate_ValueChanged(object sender, EventArgs e)
    {
        dtpRentEndDate.Value = dtpRentStartDate.Value;
    }
  80. Return to the Update Rental Order form and double-click the Calculate button
  81. 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");
    }
  82. Return to the Update Rental Order form and double-click the Rent End Date control
  83. Implement its event as follows:
    // This event approximately evaluates the number of days as a
    // difference between the end date and the starting date
    private void dtpEndDate_ValueChanged(object sender, EventArgs e)
    {
        int days;
    
        DateTime dteStart = dtpRentStartDate.Value;
        DateTime dteEnd   = dtpRentEndDate.Value;
    
        // Let's calculate 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();
        // Still, we will let the clerk specify the actual number of days
    
        btnCalculate_Click(sender, e);
    }
  84. Return to the Update Rental Order form and click the Mileage End text box
  85. In the Properties window, click the Events button
  86. In the Events section, 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);
        }
    }
  87. Return to the Update Rental Order form and double-click the Rental Rates button
  88. Implement the event as follows:
    private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates rrs = new RentalRates();
        rrs.Show();
    }
  89. Return to the Update Rental Order form and click the Rate Applied text box
  90. 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);
        }
    }
  91. Display the New Rental Order form and double-click the Rental Rates button
  92. Implement the event as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental1
    {
        public partial class NewRentalOrder : Form
        {
            public NewRentalOrder()
            {
                InitializeComponent();
            }
    
            private void btnRentalRates_Click(object sender, EventArgs e)
    	{
    	    RentalRates rrs = new RentalRates();
    	    rrs.Show();
            }
        }
    }
  93. Return to the New Rental Order form
  94. From the Printing section of the Toolbox, click PrintDocument and click the form
  95. In the Properties window, set its (Name) to docPrint and press Enter
  96. Under the form, double-click docPrint and implement its event as follows:
    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 = "Vehicle Rental Order";
        fntString = new System.Drawing.Font("Times New Roman", 22, FontStyle.Regular);
        e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 280, 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("Receipt #:  ", fntString, Brushes.Black, 100, 220);
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtReceiptNumber.Text, fntString, Brushes.Black, 260, 220);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 240, 380, 240);
    
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Processed By:  ", fntString, Brushes.Black, 420, 220);
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtEmployeeName.Text, fntString, Brushes.Black, 550, 220);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 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(txtCustomerFirstName.Text, fntString, Brushes.Black, 260, 300);
        e.Graphics.DrawString("Last Name: ", fntString, Brushes.Black, 420, 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("Car 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, 380, 470);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Condition: ", fntString, Brushes.Black, 420, 450);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(cbxVehiclesConditions.Text, fntString, Brushes.Black, 530, 450); // ?
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 470, 720, 470);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Make: ", fntString, Brushes.Black, 100, 480);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtMake.Text, fntString, Brushes.Black, 260, 480);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 500, 380, 500);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Model: ", fntString, Brushes.Black, 420, 480);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtModel.Text, fntString, Brushes.Black, 530, 480);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 500, 720, 500);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Car Condition: ", fntString, Brushes.Black, 100, 510);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(cbxVehiclesConditions.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(cbxTanksLevels.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, 380, 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(dtpRentStartDate.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);
    }
  97. Return to the New Rental Order form
  98. From the Printing section of the Toolbox, click PrintDialog and click the form
  99. In the Properties window, change its Name to dlgPrint
  100. Still in the Properties windows, set its Document property to docPrint
  101. 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();
    }
  102. Return to the New Rental Order form
  103. From the Printing section of the Toolbox, click PrintPreviewDialog and click the form
  104. In the Properties window, change its (Name) to dlgPrintPreview
  105. Still in the Properties windows, set its Document property to docPrint
  106. On the Order Processing form, double-click the Print Preview button
  107. Implement the event as follows:
    private void btnPrintPreview_Click(object sender, EventArgs e)
    {
        dlgPrintPreview.ShowDialog();
    }
  108. Return to the New Rental Order form and double-click the Save button
  109. Implement its event as follows:
    private void btnSave_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(txtEmployeeNumber.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;
        }
    
        DataRow rowRentalOrder = tblRentalOrder.NewRow();
    
        rowRentalOrder["ReceiptNumber"]     = txtReceiptNumber.Text;
        rowRentalOrder["DateProcessed"]     = dtpDateProcessed.Value.ToShortDateString();
        rowRentalOrder["EmployeeNumber"]    = txtEmployeeNumber.Text;
        rowRentalOrder["CustomerFirstName"] = txtCustomerFirstName.Text;
        rowRentalOrder["CustomerLastName"]  = txtCustomerLastName.Text;
        rowRentalOrder["CustomerAddress"]   = txtCustomerAddress.Text;
        rowRentalOrder["CustomerCity"]      = txtCustomerCity.Text;
        rowRentalOrder["CustomerState"]     = cbxCustomerStates.Text;
        rowRentalOrder["CustomerZIPCode"]   = txtCustomerZIPCode.Text;
        rowRentalOrder["TagNumber"]         = txtTagNumber.Text;
        rowRentalOrder["VehicleCondition"]  = cbxVehiclesConditions.Text;
        rowRentalOrder["TankLevel"]         = cbxTanksLevels.Text;
        rowRentalOrder["MileageStart"]      = int.Parse(txtMileageStart.Text);
        rowRentalOrder["MileageEnd"]        = int.Parse(txtMileageStart.Text);
        rowRentalOrder["MileageTotal"]      = 0;
        rowRentalOrder["RentStartDate"]     = dtpRentStartDate.Value.ToShortDateString();
        rowRentalOrder["RentEndDate"]       = dtpRentStartDate.Value.ToShortDateString();
        rowRentalOrder["TotalDays"]         = 0;
        rowRentalOrder["RateApplied"]       = double.Parse(txtRateApplied.Text);
        rowRentalOrder["SubTotal"]          = double.Parse(txtRateApplied.Text);
        rowRentalOrder["TaxRate"]           = "7.75";
        rowRentalOrder["TaxAmount"]         = double.Parse(txtRateApplied.Text);
        rowRentalOrder["OrderTotal"]        = 0;
        rowRentalOrder["OrderStatus"]       = cbxOrderStatus.Text;
        rowRentalOrder["Notes"]             = txtNotes.Text;
        tblRentalOrder.Rows.Add(rowRentalOrder);
    
        dsRentalOrders.WriteXml(@"C:\Bethesda Car Rental1\RentalOrders.xml");
        Close();
    }
  110. From the bottom section of the form, select docPrint, dlgPrint, and dlgPrintPreview
  111. Press Ctrl + C to copy them
  112. Display the Update Rental Order form and press Ctrl + V to paste
  113. Under the form, double-click docPrint
  114. Return to the Update Rental Order form and double-click the Print button
  115. Return to the Update Rental Order form and double-click the Print Preview button
  116. Implement the events as follows:
    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 = "Vehicle Rental Order";
        fntString = new System.Drawing.Font("Times New Roman", 22, FontStyle.Regular);
        e.Graphics.DrawString(strDisplay, fntString, Brushes.Black, 280, 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("Receipt #:  ", fntString, Brushes.Black, 100, 220);
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtReceiptNumber.Text, fntString, Brushes.Black, 260, 220);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 240, 380, 240);
    
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Processed By:  ", fntString, Brushes.Black, 420, 220);
        fntString = new System.Drawing.Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtEmployeeName.Text, fntString, Brushes.Black, 550, 220);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 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(txtCustomerFirstName.Text, fntString, Brushes.Black, 260, 300);
        e.Graphics.DrawString("Last Name: ", fntString, Brushes.Black, 420, 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("Car 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, 380, 470);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Condition: ", fntString, Brushes.Black, 420, 450);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(cbxVehiclesConditions.Text, fntString, Brushes.Black, 530, 450); // ?
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 470, 720, 470);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Make: ", fntString, Brushes.Black, 100, 480);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtMake.Text, fntString, Brushes.Black, 260, 480);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 500, 380, 500);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Model: ", fntString, Brushes.Black, 420, 480);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtModel.Text, fntString, Brushes.Black, 530, 480);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 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(cbxVehiclesConditions.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(cbxTanksLevels.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, 380, 560);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Mileage End:", fntString, Brushes.Black, 420, 540);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtMileageEnd.Text, fntString, Brushes.Black, 530, 540);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 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(dtpRentStartDate.Value.ToString("D"), fntString, Brushes.Black, 260, 620);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 640, 720, 640);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("End Date:", fntString, Brushes.Black, 100, 650);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(dtpRentEndDate.Value.ToString("D"), fntString, Brushes.Black, 260, 650);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 670, 520, 670);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Total Days:", fntString, Brushes.Black, 550, 650);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtTotalDays.Text, fntString, Brushes.Black, 640, 650);
        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));
    
        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);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Tax Rate:", fntString, Brushes.Black, 420, 740);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtTaxRate.Text, fntString, Brushes.Black, 640, 740, fmtString);
        e.Graphics.DrawString("%", fntString, Brushes.Black, 640, 740);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 760, 720, 760);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Sub-Total:", fntString, Brushes.Black, 100, 770);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtSubTotal.Text, fntString, Brushes.Black, 300, 770, fmtString);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 790, 380, 790);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Tax Amount:", fntString, Brushes.Black, 420, 770);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtTaxAmount.Text, fntString, Brushes.Black, 640, 770, fmtString);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 790, 720, 790);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Order Total:", fntString, Brushes.Black, 420, 800);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtOrderTotal.Text, fntString, Brushes.Black, 640, 800, fmtString);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 820, 720, 820);
    }
    
    private void btnPrint_Click(object sender, EventArgs e)
    {
        if (dlgPrint.ShowDialog() == DialogResult.OK)
            docPrint.Print();
    }
    
    private void btnPrintPreview_Click(object sender, EventArgs e)
    {
        dlgPrintPreview.ShowDialog();
    }
  117.  Return to the Update Rental Order form and double-click the Save button
  118. Implement its event as follows:
    private void btnSave_Click(object sender, EventArgs e)
    {
        XmlDocument xdRentalOrders = new XmlDocument();
        string strFileName = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
        if (File.Exists(strFileName))
        {
            xdRentalOrders.Load(strFileName);
    
            XmlNodeList xnlRentalOrders = xdRentalOrders.DocumentElement.SelectNodes("descendant::RentalOrder[ReceiptNumber='" + txtReceiptNumber.Text + "']");
    
            foreach (XmlNode nodRentalOrder in xnlRentalOrders)
            {
                nodRentalOrder.InnerXml = "<ReceiptNumber>" + txtReceiptNumber.Text + "</ReceiptNumber>" +
                                          "<DateProcessed>" + dtpDateProcessed.Value.ToString("yyyy-MM-dd") + "</DateProcessed>" +
                                          "<EmployeeNumber>" + txtEmployeeNumber.Text + "</EmployeeNumber>" +
                                          "<CustomerFirstName>" + txtCustomerFirstName.Text + "</CustomerFirstName>" +
                                          "<CustomerLastName>" + txtCustomerLastName.Text + "</CustomerLastName>" +
                                          "<CustomerAddress>" + txtCustomerAddress.Text + "</CustomerAddress>" +
                                          "<CustomerCity>" + txtCustomerCity.Text + "</CustomerCity>" +
                                          "<CustomerState>" + cbxCustomerStates.Text + "</CustomerState>" +
                                          "<CustomerZIPCode>" + txtCustomerZIPCode.Text + "</CustomerZIPCode>" +
                                          "<TagNumber>" + txtTagNumber.Text + "</TagNumber>" +
                                          "<VehicleCondition>" + cbxVehiclesConditions.Text + "</VehicleCondition>" +
                                          "<TankLevel>" + cbxTanksLevels.Text + "</TankLevel>" +
                                          "<MileageStart>" + int.Parse(txtMileageStart.Text) + "</MileageStart>" +
                                          "<MileageEnd>" + int.Parse(txtMileageEnd.Text) + "</MileageEnd>" +
                                          "<MileageTotal>" + int.Parse(txtMileageTotal.Text) + "</MileageTotal>" +
                                          "<RentStartDate>" + dtpRentStartDate.Value.ToString("yyyy-MM-dd") + "</RentStartDate>" +
                                          "<RentEndDate>" + dtpRentEndDate.Value.ToString("yyyy-MM-dd") + "</RentEndDate>" +
                                          "<TotalDays>" + int.Parse(txtTotalDays.Text) + "</TotalDays>" +
                                          "<RateApplied>" + double.Parse(txtRateApplied.Text) + "</RateApplied>" +
                                          "<SubTotal>" + double.Parse(txtSubTotal.Text) + "</SubTotal>" +
                                          "<TaxRate>" + double.Parse(txtTaxRate.Text) + "</TaxRate>" +
                                          "<TaxAmount>" + double.Parse(txtTaxAmount.Text) + "</TaxAmount>" +
                                          "<OrderTotal>" + double.Parse(txtOrderTotal.Text) + "</OrderTotal>" +
                                          "<OrderStatus>" + cbxOrderStatus.Text + "</OrderStatus>" +
                                          "<Notes>" + txtNotes.Text + "</Notes>";
            }
    
            xdRentalOrders.Save(@"C:\Bethesda Car Rental1\RentalOrders.xml");
        }
            
        Close();
    }
  119. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental1 -> Add -> Windows Form...
  120. Set the Name to RentalOrders and click Add
  121. Display the Employees form and copy dsEmployees
  122. Display the RentalOrders form and paste dsEmployees
  123. Display the Vehicles form and copy dsVehicles
  124. Display the RentalOrders form and paste dsVehicles
  125. Design the form as follows:
     
 
 

Bethesda Car Rental: Rental Orders

Control (Name) Text Other Properties
ListView lvwRentalOrders   Anchor: Top, Bottom, Left, Right
 
(Name) Text TextAlign Width
colReceiptOrderNumber Receipt #   65
colProcessedDate Date Processed Center 90
colEmployee Employee   140
colCustomer Customer   120
colVehicle Vehicle   140
colCarCondition Condition    
colTank Tank Level   70
colStartingMileage Mileage Start Right 75
colEndingMileage Mileage End Right 75
colTotalMileage Total Right 45
colRentStartingDate Start Date Center  
colRentEndingDate End Date Center  
colDaysTotal Total Right 45
colDailyRate Cost Right 50
colDailySubTotal Sub-Total Right  
colTaxPercentage Tax Rate Right  
colTaxPaid Tax Amt Right 55
colTotalOrder Total Order Right 65
colStatusOfOrder Order Status   150
Button btnNewRentalOrder New Rental Order ... Anchor: Bottom, Right
Button btnUpdateRentalOrder Update Rental Order ... Anchor: Bottom, Right
Button btnClose Close Anchor: Bottom, Right
 
 

Selecting Records

As you may know already, the DataTable class provides its own mechanism for selecting its records and storing them in an array of DataRows. This is done using the Select() method. Here is an example:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class MusicStore : Form
{
    DataSet dsMusicStore;
    DataTable tblStoreItem;

    private ColumnHeader colItemCode;
    private ColumnHeader colCategory;
    private ColumnHeader colItemName;
    private ColumnHeader colUnitPrice;

    ListView lvwStoreItems;

    public MusicStore()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lvwStoreItems = new ListView();
        lvwStoreItems.Anchor = AnchorStyles.Left  | AnchorStyles.Top |
                               AnchorStyles.Right | AnchorStyles.Bottom;
        lvwStoreItems.FullRowSelect = true;
        lvwStoreItems.GridLines = true;
        lvwStoreItems.Location = new Point(12, 12);
        lvwStoreItems.Size = new System.Drawing.Size(530, 192);
        lvwStoreItems.View = View.Details;

        colItemCode = new ColumnHeader();
        colItemCode.Text = "Item #";
        colItemCode.Width = 50;
        lvwStoreItems.Columns.Add(colItemCode);

        colCategory = new ColumnHeader();
        colCategory.Text = "Category";
        colCategory.Width = 105;
        lvwStoreItems.Columns.Add(colCategory);

        colItemName = new ColumnHeader();
        colItemName.Text = "Item Name/Description";
        colItemName.Width = 310;
        lvwStoreItems.Columns.Add(colItemName);

        colUnitPrice = new ColumnHeader();
        colUnitPrice.Text = "Unit Price";
        colUnitPrice.TextAlign = HorizontalAlignment.Right;
        lvwStoreItems.Columns.Add(colUnitPrice);

        tblStoreItem = new DataTable("StoreItem");
        tblStoreItem.Columns.Add("ItemNumber", Type.GetType("System.UInt32"));
        tblStoreItem.Columns.Add("Category", Type.GetType("System.String"));
        tblStoreItem.Columns.Add("ItemName", Type.GetType("System.String"));
        tblStoreItem.Columns.Add("UnitPrice", Type.GetType("System.Decimal"));

        dsMusicStore = new DataSet("StoreItems");
        dsMusicStore.Tables.Add(tblStoreItem);

        Text = "Music Store";
        MaximizeBox = false;
        Size = new System.Drawing.Size(562, 243);
        Controls.Add(lvwStoreItems);
        Load += new EventHandler(MusicStoreLoad);
    }

    private void MusicStoreLoad(object sender, EventArgs e)
    {
        Directory.CreateDirectory(@"C:\Music Store");

        DataRow rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 925173;
        rowStoreItem["Category"] = "Acoustic Guitars";
        rowStoreItem["ItemName"] = "Yamaha F335 Acoustic Guitar";
        rowStoreItem["UnitPrice"] = 129.85;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 274930;
        rowStoreItem["Category"] = "Drums";
        rowStoreItem["ItemName"] = "Simmons SD5X Electronic Drum Set";
        rowStoreItem["UnitPrice"] = 395.85;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 927974;
        rowStoreItem["ItemName"] = "TASCAM DP-24 24-Track Digital Portastudio";
        rowStoreItem["UnitPrice"] = 595.55;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 402884;
        rowStoreItem["Category"] = "Synthesizers";
        rowStoreItem["ItemName"] = "Roland JUNO-Gi Synthesizer";
        rowStoreItem["UnitPrice"] = 795.85;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 402884;
        rowStoreItem["ItemName"] = "ART SLA1 Studio Power Amplifier";
        rowStoreItem["UnitPrice"] = 265.55;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 974951;
        rowStoreItem["Category"] = "Microphone Cables";
        rowStoreItem["ItemName"] = "Live Wire Advantage Standard EXM Series Microphone Cable";
        rowStoreItem["UnitPrice"] = 14.95;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 208148;
        rowStoreItem["Category"] = "Electric Bass";
        rowStoreItem["ItemName"] = "Fender Modern Player Jazz Electric Bass Guitar";
        rowStoreItem["UnitPrice"] = 495.95;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 802840;
        rowStoreItem["Category"] = "Acoustic Guitars";
        rowStoreItem["ItemName"] = "Taylor Big Baby Taylor Acoustic Guitar";
        rowStoreItem["UnitPrice"] = 449.95;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 724051;
        rowStoreItem["Category"] = "Drums";
        rowStoreItem["ItemName"] = "Gretsch Drums Energy 5-Piece Drum Set";
        rowStoreItem["UnitPrice"] = 695.65;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 524037;
        rowStoreItem["Category"] = "Electric Bass";
        rowStoreItem["ItemName"] = "Fender Deluxe P Bass Special 4-String Bass";
        rowStoreItem["UnitPrice"] = 699.95;
        tblStoreItem.Rows.Add(rowStoreItem);
        rowStoreItem["ItemNumber"] = 274703;

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["Category"] = "Synthesizers";
        rowStoreItem["ItemName"] = "Access Virus TI v2 Keyboard";
        rowStoreItem["UnitPrice"] = 2992.25;
        tblStoreItem.Rows.Add(rowStoreItem);

        rowStoreItem = tblStoreItem.NewRow();
        rowStoreItem["ItemNumber"] = 408408;
        rowStoreItem["Category"] = "Speakers";
        rowStoreItem["ItemName"] = "Behringer EUROLIVE B210D Active PA Speaker System";
        rowStoreItem["UnitPrice"] = 195.95;
        tblStoreItem.Rows.Add(rowStoreItem);

        dsMusicStore.WriteXml(@"C:\Music Store\StoreItems.xml");

        DataRow[] rows = dsMusicStore.Tables[0].Select();

        foreach (var item in rows)
        {
            ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

            lviCollection.SubItems.Add(item["Category"].ToString());
            lviCollection.SubItems.Add(item["ItemName"].ToString());
            lviCollection.SubItems.Add(item["UnitPrice"].ToString());
            lvwStoreItems.Items.Add(lviCollection);
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new MusicStore());
        return 0;
    }
}

 One of the members of the DataTableExtensions class is AsEnumerable. The syntax of the DataTableExtensions.AsEnumerable() method is:

public static EnumerableRowCollection<DataRow> AsEnumerable(this DataTable source);

As you can see, this method takes no argument. It is called by a DataTable object and it returns a set of DataRow records as an IEnumerable collection. Once you have gotten that collection, you can use it in LINQ. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
            
            IEnumerable storeItems = from recStoreItems
                                     in dsStoreItems.Tables[0].AsEnumerable()
                                     select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
                
                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

Practical LearningPractical Learning: Selecting Records in LINQ

  1. Display the New Rental Order form and double-click an unoccupied area of its body
  2. Implement the event as follows:
    private void NewRentalOrder_Load(object sender, EventArgs e)
    {
        int iReceiptNumber = 100000;
        string strFilename = @"C:\Bethesda Car Rental1\Employees.xml";
    
        if (File.Exists(strFilename))
            dsEmployees.ReadXml(strFilename);
    
        strFilename = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
        if (File.Exists(strFilename))
            dsVehicles.ReadXml(strFilename);
    
        strFilename = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
        if (File.Exists(strFilename))
            dsRentalOrders.ReadXml(strFilename);
    
        var rentalOrders = from recRentalOrders
                           in dsRentalOrders.Tables[0].AsEnumerable()
                           select recRentalOrders;
    
        foreach (var rentalOrder in rentalOrders)
            iReceiptNumber = int.Parse(rentalOrder["ReceiptNumber"].ToString());
    
        txtReceiptNumber.Text = (iReceiptNumber + 1).ToString();
    }
  3. Return to the New Rental Order form and click the Employee Number text box
  4. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        IEnumerable<DataRow> empls = from recEmployees
                                     in dsEmployees.Tables[0].AsEnumerable()
                                     select recEmployees;
        foreach (var empl in empls)
            if (empl.Field<string>("EmployeeNumber") == txtEmployeeNumber.Text)
                txtEmployeeName.Text = empl.Field<string>("EmployeeName");
    }
  5. Display the Vehicles form
  6. Click the title bar of the form (or, in the combo box on top of the Properties windows, select Vehicles) and click the Events button of the Properties window
  7. Double-click Load and change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental1
    {
        public partial class Vehicles : Form
        {
            public VehiclesInventory()
            {
                InitializeComponent();
            }
    
            private void ShowVehicles()
            {
                string strFileName = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
                if (File.Exists(strFileName))
                {
                    dsVehicles.ReadXml(strFileName);
                    List<string> lstCategories = new List<string>();
                    List<string> lstAvailabilities = new List<string>();
    
                    TreeNode nodBCR = tvwVehicles.Nodes.Add("Bethesda Car Rental");
                    TreeNode nodCategories = nodBCR.Nodes.Add("Categories");
                    TreeNode nodAvailabilities = nodBCR.Nodes.Add("Availabilities");
    
                    IEnumerable<DataRow> vehicles = from recVehicles
                                                    in dsVehicles.Tables[0].AsEnumerable()
                                                    select recVehicles;
                    foreach (var category in vehicles)
                    {
                        if (!(lstCategories.Contains(category.Field<string>("Category"))))
                            lstCategories.Add(category.Field<string>("Category"));
                    }
                    foreach (var availability in vehicles)
                    {
                        if (!(lstAvailabilities.Contains(availability.Field<string>("Availability"))))
                            lstAvailabilities.Add(availability.Field<string>("Availability"));
                    }
    
                    foreach (string strCategory in lstCategories)
                        nodCategories.Nodes.Add(strCategory);
                    foreach (string strAvailability in lstAvailabilities)
                        nodAvailabilities.Nodes.Add(strAvailability);
    
                    foreach (var vehicle in vehicles)
                    {
                        ListViewItem lviVehicle = new ListViewItem(vehicle["TagNumber"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Make"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Model"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Doors"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Passengers"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Condition"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Category"].ToString());
                        lviVehicle.SubItems.Add(vehicle["Availability"].ToString());
                        lvwVehicles.Items.Add(lviVehicle);
                    }
                }
            }
    
            private void VehiclesInventory_Load(object sender, EventArgs e)
            {
                ShowVehicles();
            }
        }
    }
  8. Return to the Vehicles form and double-click the New Vehicle button
  9. Implement the event as follows:
    private void btnNewVehicle_Click(object sender, EventArgs e)
    {
        NewVehicle nv = new NewVehicle();
        nv.ShowDialog();
    
        tvwVehicles.Nodes.Clear();
        lvwVehicles.Items.Clear();
    
        ShowVehicles();
    }
  10. Return to the Vehicles form and click the list view
  11. In the Events section of the Properties window, double-click ItemSelectionChanged
  12. Implement the event as follows:
    private void lvwVehicles_ItemSelectionChanged(object sender,
    					      ListViewItemSelectionChangedEventArgs e)
    {
        string strPath = @"C:\Bethesda Car Rental1";
        DirectoryInfo diVehicles = new DirectoryInfo(strPath);
        FileInfo[] aryVehicles = diVehicles.GetFiles("jpg", SearchOption.AllDirectories);
    
        IEnumerable<FileInfo> pictureFiles = from picts
                                             in diVehicles.GetFiles()
                                             select picts;
          
        foreach (var file in pictureFiles)
            if (file.Name == e.Item.Text + ".jpg")
                pbxVehicle.Image = Image.FromFile(file.Name);
    }

Applying a Condition

The DataTable class supports query conditions both in the LINQ and the IEnumerable interface. When formulating the where condition, to locate a DataColumn column, apply its name as a string to the value holder, then compare its value to the desired criterion. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
            
            IEnumerable<DataRow> storeItems = from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              where recStoreItems["Category"].ToString() == "Acoustic Guitars"
                                              select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
                
                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

Remember the rules based on the type of value and use the appropriate operator: ==, <, <=, >, >=, and != . Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
            
            IEnumerable<DataRow> storeItems = from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              where double.Parse(recStoreItems["UnitPrice"].ToString()) <= 500
                                              select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
                
                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

This would produce:

Numbers

Remember that the Enumerable class supports the condition through the Where() method. To use it, first include your select statement in parentheses and create your condition outside. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
            
            IEnumerable<DataRow> storeItems = (from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              select recStoreItems).Where(name => name["ItemName"].ToString().Contains("Guitar"));

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
                
                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

To negate a condition, you can precede the where expression by the ! operator.

Practical LearningPractical Learning: Setting a Condition in LINQ

  1. Display the Update Rental Order form and click the Employee # text box
  2. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        IEnumerable<DataRow> empls = from recEmployees
                                     in dsEmployees.Tables[0].AsEnumerable()
                                     where recEmployees["EmployeeNumber"].ToString() == txtEmployeeNumber.Text 
                                     select recEmployees;
        foreach (var empl in empls)
            txtEmployeeName.Text = empl.Field<string>("EmployeeName");
    }
  3. Return to the Update Rental Order form and double-click the Open button
  4. Implement the event as follows:
    private void btnOpen_Click(object sender, EventArgs e)
    {
        string strFileName = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
        if (File.Exists(strFileName))
        {
            dsRentalOrders.ReadXml(strFileName);
    
            var rentalOrders = from orders
                               in dsRentalOrders.Tables[0].AsEnumerable()
                               where orders["ReceiptNumber"].ToString() == txtReceiptNumber.Text
                               select orders;
    
            foreach (var rentalOrder in rentalOrders)
            {
                dtpDateProcessed.Value = DateTime.Parse(rentalOrder["DateProcessed"].ToString());
                txtEmployeeNumber.Text = rentalOrder["EmployeeNumber"].ToString();
                txtCustomerFirstName.Text = rentalOrder["CustomerFirstName"].ToString();
                txtCustomerLastName.Text = rentalOrder["CustomerLastName"].ToString();
                txtCustomerAddress.Text = rentalOrder["CustomerAddress"].ToString();
                txtCustomerCity.Text = rentalOrder["CustomerCity"].ToString();
                cbxCustomerStates.Text = rentalOrder["CustomerState"].ToString();
                txtCustomerZIPCode.Text = rentalOrder["CustomerZIPCode"].ToString();
                txtTagNumber.Text = rentalOrder["TagNumber"].ToString();
                cbxVehiclesConditions.Text = rentalOrder["VehicleCondition"].ToString();
                cbxTanksLevels.Text = rentalOrder["TankLevel"].ToString();
                txtMileageStart.Text = rentalOrder["MileageStart"].ToString();
                txtMileageEnd.Text = rentalOrder["MileageEnd"].ToString();
                txtMileageTotal.Text = rentalOrder["MileageTotal"].ToString();
                dtpRentStartDate.Value = DateTime.Parse(rentalOrder["RentStartDate"].ToString());
                dtpRentEndDate.Value = DateTime.Parse(rentalOrder["RentEndDate"].ToString());
                txtTotalDays.Text = rentalOrder["TotalDays"].ToString();
                txtRateApplied.Text = rentalOrder["RateApplied"].ToString();
                txtSubTotal.Text = rentalOrder["SubTotal"].ToString();
                txtTaxRate.Text = rentalOrder["TaxRate"].ToString();
                txtTaxAmount.Text = rentalOrder["TaxAmount"].ToString();
                txtOrderTotal.Text = rentalOrder["OrderTotal"].ToString();
                cbxOrderStatus.Text = rentalOrder["OrderStatus"].ToString();
                txtNotes.Text = rentalOrder["Notes"].ToString();
            }
        }
    
        strFileName = @"C:\Bethesda Car Rental1\Employees.xml";
    
        if (File.Exists(strFileName))
        {
            dsEmployees.ReadXml(strFileName);
    
            var employees = from empls
                            in dsEmployees.Tables[0].AsEnumerable()
                            where empls["EmployeeNumber"].ToString() == txtEmployeeNumber.Text
                            select empls;
            foreach (var empl in employees)
            {
                txtEmployeeName.Text = empl["EmployeeName"].ToString();
            }
        }
    
        strFileName = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
        if (File.Exists(strFileName))
        {
            dsVehicles.ReadXml(strFileName);
    
            var vehicles = from cars
                           in dsVehicles.Tables["Vehicle"].AsEnumerable()
                           where cars["TagNumber"].ToString() == txtTagNumber.Text
                           select cars;
    
            foreach (var vehicle in vehicles)
            {
                txtMake.Text = vehicle["Make"].ToString();
                txtModel.Text = vehicle["Model"].ToString();
            }
        }
    }

Sorting the Records in LINQ

You can arrange the records of a data table using either LINQ or the Enumerable class. Once you have gotten the records using the AsEnumerable() method, you can apply the orderdy operator to your select statement. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

            IEnumerable<DataRow> storeItems = from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              orderby recStoreItems["ItemName"].ToString()
                                              select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

This would produce:

Numbers

To arrange the records using the Enumerable class, create your select statement and call the OrderBy() method on it. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
            
            IEnumerable<DataRow> storeItems = (from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              select recStoreItems).OrderBy(item => item["UnitPrice"]);

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
                
                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

To arrange the result in reverse order, call the OrderByDescending() method.

LINQ Operations on a  Data Set

 

Conjunctions and Disjunctions

A logical conjunction is created in a where statement as an addition of two conditions  combined using the && operator. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

            IEnumerable<DataRow> storeItems = from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              where (recStoreItems["Category"].ToString() == "Electric Bass") && (double.Parse(recStoreItems["UnitPrice"].ToString()) <= 500)
                                              select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

This would produce:

Numbers

Remember that, to negate a conjunction, precede it with a ! operator.

A logical disjunction is done using the || operator. Here is an example:

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;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

            IEnumerable<DataRow> storeItems = from recStoreItems
                                              in dsStoreItems.Tables[0].AsEnumerable()
                                              where (recStoreItems["Category"].ToString() == "Acoustic Guitars") || (recStoreItems["Category"].ToString() == "Electric Guitars")
                                              select recStoreItems;

            foreach (var item in storeItems)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }
}

This would produce:

Numbers

Practical LearningPractical Learning: Using a Logical Disjunction in LINQ

  1. Display the Vehicles form and click the tree view
  2. In the Events section of the Properties window, double-click NodeMouseClick
  3. Implement the event as follows:
    private void tvwVehicles_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
    {
        if ((e.Node.Text == "Bethesda Car Rental") ||
            (e.Node.Text == "Categories") ||
            (e.Node.Text == "Availabilities"))
        {
            dsVehicles.Clear();
            lvwVehicles.Items.Clear();
            dsVehicles.ReadXml(@"C:\Bethesda Car Rental100\Vehicles.xml");
    
            IEnumerable<DataRow> vehicles = from recVehicles
                                            in dsVehicles.Tables[0].AsEnumerable()
                                            select recVehicles;
    
            foreach (var vehicle in vehicles)
            {
                ListViewItem lviVehicle = new ListViewItem(vehicle["TagNumber"].ToString());
                lviVehicle.SubItems.Add(vehicle["Make"].ToString());
                lviVehicle.SubItems.Add(vehicle["Model"].ToString());
                lviVehicle.SubItems.Add(vehicle["Doors"].ToString());
                lviVehicle.SubItems.Add(vehicle["Passengers"].ToString());
                lviVehicle.SubItems.Add(vehicle["Condition"].ToString());
                lviVehicle.SubItems.Add(vehicle["Category"].ToString());
                lviVehicle.SubItems.Add(vehicle["Availability"].ToString());
                lvwVehicles.Items.Add(lviVehicle);
            }
        }
        else
        {
            dsVehicles.Clear();
            lvwVehicles.Items.Clear();
            dsVehicles.ReadXml(@"C:\Bethesda Car Rental100\Vehicles.xml");
    
            IEnumerable<DataRow> vehicles = from recVehicles
                                            in dsVehicles.Tables[0].AsEnumerable()
                                            where (recVehicles.Field<string>("Category") == e.Node.Text) ||
                                                  (recVehicles.Field<string>("Availability") == e.Node.Text)
                                            select recVehicles;
            foreach (var vehicle in vehicles)
            {
                ListViewItem lviVehicle = new ListViewItem(vehicle["TagNumber"].ToString());
                lviVehicle.SubItems.Add(vehicle["Make"].ToString());
                lviVehicle.SubItems.Add(vehicle["Model"].ToString());
                lviVehicle.SubItems.Add(vehicle["Doors"].ToString());
                lviVehicle.SubItems.Add(vehicle["Passengers"].ToString());
                lviVehicle.SubItems.Add(vehicle["Condition"].ToString());
                lviVehicle.SubItems.Add(e.Node.Text);
                lviVehicle.SubItems.Add(vehicle["Availability"].ToString());
                lvwVehicles.Items.Add(lviVehicle);
            }
        }
    }
  4. Return to the Vehicles form and double-click the Close button
  5. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }

letting New Local Variables

The LINQ provides the let keyword that allows you to select records from a data table. The simplest way to use the let operator is to produce all the records from the data table. To do this, declare a variable using let in your query, assign the ValueHolder to the variable, and select that let variable. Outside the query, access each table column using the period operator applied to the foreach variable. Here is an example:

private void MusicStoreLoad(object sender, EventArgs e)
{
    DataSet dsStoreItems = new DataSet("StoreItems");
    dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

    IEnumerable<DataRow> storeItems = from recStoreItems
                                          in dsStoreItems.Tables[0].AsEnumerable()
                                          let acousticGuitars = recStoreItems
                                          select recStoreItems;

    foreach (var item in storeItems)
    {
        ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

        lviCollection.SubItems.Add(item["Category"].ToString());
        lviCollection.SubItems.Add(item["ItemName"].ToString());
        lviCollection.SubItems.Add(item["UnitPrice"].ToString());
        lvwStoreItems.Items.Add(lviCollection);
    }
}

This would produce:

Letting a Local Variable

In reality, the let operator allows you to declare a local variable in your query and use that variable any way you want. For example, you can assign a table column to the let variable, then select that variable and access it outside as the returned value of the query. Here is an example:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class MusicStore : Form
{
    private ListBox lbxMusicStore;

    public MusicStore()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lbxMusicStore = new ListBox();
        lbxMusicStore.Location = new Point(12, 12);
	lbxMusicStore.Size = new System.Drawing.Size(305, 170);

        Text = "Music Store";
        MaximizeBox = false;
        Size = new System.Drawing.Size(340, 214);
        Controls.Add(lbxMusicStore);
        Load += new EventHandler(MusicStoreLoad);
    }

    private void MusicStoreLoad(object sender, EventArgs e)
    {
        DataSet dsStoreItems = new DataSet("StoreItems");
	dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

        var storeItems = from recStoreItems
                         in dsStoreItems.Tables[0].AsEnumerable()
                         let name = recStoreItems["ItemName"]
                         select name;

        foreach (var item in storeItems)
            lbxMusicStore.Items.Add(item);
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new MusicStore());
        return 0;
    }
}

This would produce:

Letting a Local Variable

In the same way, you can create an expression made of constant values, some columns of the table, or a combination of columns and constains. When you exit the query, you can select that let variable. Here is an example:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class MusicStore : Form
{
    private ListBox lbxMusicStore;

    public MusicStore()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lbxMusicStore = new ListBox();
        lbxMusicStore.Location = new Point(12, 12);
	lbxMusicStore.Size = new System.Drawing.Size(335, 170);

        Text = "Music Store";
        MaximizeBox = false;
        Size = new System.Drawing.Size(365, 214);
        Controls.Add(lbxMusicStore);
        Load += new EventHandler(MusicStoreLoad);
    }

    private void MusicStoreLoad(object sender, EventArgs e)
    {
        DataSet dsStoreItems = new DataSet("StoreItems");
	dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

        var storeItems = from recStoreItems
                         in dsStoreItems.Tables[0].AsEnumerable()
                         let description = string.Concat(recStoreItems["ItemName"], ", ", recStoreItems["UnitPrice"])
                         select description;

        foreach (var item in storeItems)
            lbxMusicStore.Items.Add(item);
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new MusicStore());
        return 0;
    }
}

This would produce:

Letting a Local Variable

In the body of the query, you can declare many variables, each with its own let keyword. Still inside the query, you can use the variable as you see fit. At the end, select only one of the variables and use it outside of the query.

If necessary, you can add a where condition to your query, before the let section(s).

Creating new Local Variables

As opposed to the let operator that can produce only one variable from a query, the LINQ provides the new keyword that allows you to create as many names as you want in your query and access those names outside the query. To do this, create a select new {} section at the end your query. Inside the curly brackets, define each name and assign a value to it. The name can be any, as lonog as it follows the rules of names in C#. Separate each expression (the name and its assignment) with commas. As seen for the let operator, a value can be a constant or the value of a table column accessed from the ValueHolder. Outside the query, you can access each new name from he foreach variable. Here is an example:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class MusicStore : Form
{
    DataSet dsMusicStore;
    DataTable tblStoreItem;

    private ColumnHeader colItemCode;
    private ColumnHeader colCategory;
    private ColumnHeader colItemName;
    private ColumnHeader colUnitPrice;

    ListView lvwStoreItems;

    public MusicStore()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lvwStoreItems = new ListView();
        lvwStoreItems.Anchor = AnchorStyles.Left  | AnchorStyles.Top |
                               AnchorStyles.Right | AnchorStyles.Bottom;
        lvwStoreItems.FullRowSelect = true;
        lvwStoreItems.GridLines = true;
        lvwStoreItems.Location = new Point(12, 12);
        lvwStoreItems.Size = new System.Drawing.Size(530, 192);
        lvwStoreItems.View = View.Details;

        colItemCode = new ColumnHeader();
        colItemCode.Text = "Item #";
        colItemCode.Width = 50;
        lvwStoreItems.Columns.Add(colItemCode);

        colCategory = new ColumnHeader();
        colCategory.Text = "Category";
        colCategory.Width = 105;
        lvwStoreItems.Columns.Add(colCategory);

        colItemName = new ColumnHeader();
        colItemName.Text = "Item Name/Description";
        colItemName.Width = 310;
        lvwStoreItems.Columns.Add(colItemName);

        colUnitPrice = new ColumnHeader();
        colUnitPrice.Text = "Unit Price";
        colUnitPrice.TextAlign = HorizontalAlignment.Right;
        lvwStoreItems.Columns.Add(colUnitPrice);

        tblStoreItem = new DataTable("StoreItem");
        tblStoreItem.Columns.Add("ItemNumber", Type.GetType("System.UInt32"));
        tblStoreItem.Columns.Add("Category", Type.GetType("System.String"));
        tblStoreItem.Columns.Add("ItemName", Type.GetType("System.String"));
        tblStoreItem.Columns.Add("UnitPrice", Type.GetType("System.Decimal"));

        dsMusicStore = new DataSet("StoreItems");
        dsMusicStore.Tables.Add(tblStoreItem);

        Text = "Music Store";
        MaximizeBox = false;
        Size = new System.Drawing.Size(562, 244);
        Controls.Add(lvwStoreItems);
        Load += new EventHandler(MusicStoreLoad);
    }

    private void MusicStoreLoad(object sender, EventArgs e)
    {
        DataSet dsStoreItems = new DataSet("StoreItems");
	dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

        var storeItems = from recStoreItems
                         in dsStoreItems.Tables[0].AsEnumerable()
                         select new
			 {
			     Code  = recStoreItems["ItemNumber"],
			     Type  = recStoreItems["Category"],
                             Name  = recStoreItems["ItemName"],
                             Price = recStoreItems["UnitPrice"],
			 };

        foreach (var item in storeItems)
        {
            ListViewItem lviCollection = new ListViewItem(item.Code.ToString());

            lviCollection.SubItems.Add(item.Type.ToString());
            lviCollection.SubItems.Add(item.Name.ToString());
            lviCollection.SubItems.Add(item.Price.ToString());
            lvwStoreItems.Items.Add(lviCollection);
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new MusicStore());
        return 0;
    }
}

Remember that you can combine the values of columns and assign such an expression to a new name.

Practical LearningPractical Learning: Creating New Local Variables

  1. Display the New Rental Order form and click the Tag Number text box
  2. 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)
    {
        var vehicles = from recVehicles
                       in dsVehicles.Tables["Vehicle"].AsEnumerable()
                       where recVehicles["TagNumber"].ToString() == txtTagNumber.Text
                       select new
                       {
                           Make = recVehicles["Make"].ToString(),
                           Model = recVehicles["Model"].ToString(),
                           Conodition = recVehicles["Condition"].ToString()
                       };
    
        foreach (var drVehicle in vehicles)
        {
            txtMake.Text = drVehicle.Make;
            txtModel.Text = drVehicle.Model;
            cbxVehiclesConditions.Text = drVehicle.Conodition;
        }
    }
  3. Return to the New Rental Order form and double-click the Close button
  4. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  5. Display the Update Rental Order form and click the Tag Number text box
  6. 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)
    {
        var vehicles = from recVehicles
                       in dsVehicles.Tables["Vehicle"].AsEnumerable()
                       where recVehicles["TagNumber"].ToString() == txtTagNumber.Text
                       select new
                       {
                           Make = recVehicles["Make"].ToString(),
                           Model = recVehicles["Model"].ToString(),
                           Conodition = recVehicles["Condition"].ToString()
                       };
    
        foreach (var drVehicle in vehicles)
        {
            txtMake.Text = drVehicle.Make;
            txtModel.Text = drVehicle.Model;
            cbxVehiclesConditions.Text = drVehicle.Conodition;
        }
    }
  7. Return to the Update Rental Order form and double-click the Close button
  8. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }

Data Sets and Numeric Querying

The LINQ provides a rich collection of methods to perform various types of operations, including calculations on numeric values. For example, if your table has a column with numbers, you can find the minimum, the maximum, the sum, or the average of the number. To get the value, create a a query that produces the values of the number-based column and call the Average() method on the returned value. Here are examples:

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.IO;

namespace DataSetLINQ
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            DataSet dsStoreItems = new DataSet("StoreItems");
            dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

            DataRow[] rows = dsStoreItems.Tables[0].Select();

            foreach (var item in rows)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }

            IEnumerable<double> prices = from storeItems
                                         in dsStoreItems.Tables[0].AsEnumerable()
                                         let price = double.Parse(storeItems["UnitPrice"].ToString())
                                         select price;

            txtMinimum.Text = prices.Min().ToString();
            txtMaximum.Text = prices.Max().ToString(); 

            double avgPrice = (from storeItems
                               in dsStoreItems.Tables[0].AsEnumerable()
                               let price = double.Parse(storeItems["UnitPrice"].ToString())
                               select price).Average();

            txtAverage.Text = avgPrice.ToString();
        }
    }
}

Grouping the Records of a Data Set

One way you can present the records of a table is to display them in groups. In LINQ, this is done using the group ... by operation. To do this, type the group keyword followed the ValueHolder and by. Then specify the column by which the results should be grouped. To present the results, start with a foreach loop that is based on the from variable. Nest a foreach loop that is based on the first foreach variable. Here is an example:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class MusicStore : Form
{
    private ColumnHeader colItemCode;
    private ColumnHeader colCategory;
    private ColumnHeader colItemName;
    private ColumnHeader colUnitPrice;

    ListView lvwStoreItems;

    public MusicStore()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lvwStoreItems = new ListView();
        lvwStoreItems.Anchor = AnchorStyles.Left  | AnchorStyles.Top |
                               AnchorStyles.Right | AnchorStyles.Bottom;
        lvwStoreItems.FullRowSelect = true;
        lvwStoreItems.GridLines = true;
        lvwStoreItems.Location = new Point(12, 12);
        lvwStoreItems.Size = new System.Drawing.Size(530, 192);
        lvwStoreItems.View = View.Details;

        colItemCode = new ColumnHeader();
        colItemCode.Text = "Item #";
        colItemCode.Width = 50;
        lvwStoreItems.Columns.Add(colItemCode);

        colCategory = new ColumnHeader();
        colCategory.Text = "Category";
        colCategory.Width = 105;
        lvwStoreItems.Columns.Add(colCategory);

        colItemName = new ColumnHeader();
        colItemName.Text = "Item Name/Description";
        colItemName.Width = 310;
        lvwStoreItems.Columns.Add(colItemName);

        colUnitPrice = new ColumnHeader();
        colUnitPrice.Text = "Unit Price";
        colUnitPrice.TextAlign = HorizontalAlignment.Right;
        lvwStoreItems.Columns.Add(colUnitPrice);

        Text = "Music Store";
        MaximizeBox = false;
        Size = new System.Drawing.Size(562, 244);
        Controls.Add(lvwStoreItems);
        Load += new EventHandler(MusicStoreLoad);
    }

    private void MusicStoreLoad(object sender, EventArgs e)
    {
        DataSet dsStoreItems = new DataSet("StoreItems");
        dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

        var storeItems = from recStoreItems
                         in dsStoreItems.Tables[0].AsEnumerable()
                         group recStoreItems by recStoreItems["Category"];

        foreach (var items in storeItems)
        {
            foreach (var item in items)
            {
                ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

                lviCollection.SubItems.Add(item["Category"].ToString());
                lviCollection.SubItems.Add(item["ItemName"].ToString());
                lviCollection.SubItems.Add(item["UnitPrice"].ToString());
                lvwStoreItems.Items.Add(lviCollection);
            }
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new MusicStore());
        return 0;
    }
}

To support grouping, the Enumerable class is equipped with the GroupBy() method that is overloaded with various versions. This method takes one argument as a function. Here is an example of calling it:

private void MusicStoreLoad(object sender, EventArgs e)
{
    DataSet dsStoreItems = new DataSet("StoreItems");
    dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

    var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"]);

    foreach (var items in storeItems)
    {
        foreach (var item in items)
        {
            ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());

            lviCollection.SubItems.Add(item["Category"].ToString());
            lviCollection.SubItems.Add(item["ItemName"].ToString());
            lviCollection.SubItems.Add(item["UnitPrice"].ToString());
            lvwStoreItems.Items.Add(lviCollection);
        }
    }
}

The GroupBy() method is equipped with a property named Key that you can use to get a list of the values in the column that is used for grouping. Here is an example:

private void MusicStoreLoad(object sender, EventArgs e)
{
    DataSet dsStoreItems = new DataSet("StoreItems");
    dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

    var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"]);

    ListBox lbxStoreItems = new ListBox();
    lbxStoreItems.Location = new Point(12, 250);
    Controls.Add(lbxStoreItems);

    foreach (var item in storeItems)
        lbxStoreItems.Items.Add(item.Key);
}

In reality, the GroupBy() method takes two arguments. The first argument is used to specify the column by which the records should be grouped. The second argument is used to list the values of the column in each category. Here is an example of calling the method with both arguments:

private void MusicStoreLoad(object sender, EventArgs e)
{
    DataSet dsStoreItems = new DataSet("StoreItems");
    dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");

    var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"], name => name["ItemName"]);

    foreach (var items in storeItems)
    {
        foreach (var item in items)
        {
            ListViewItem lviCollection = new ListViewItem(items.Key.ToString());

            lviCollection.SubItems.Add("");
            lviCollection.SubItems.Add(item.ToString());
            lviCollection.SubItems.Add("");
            lvwStoreItems.Items.Add(lviCollection);
        }
    }
}

Joining the Records of Two Sets

Joining records is the fundamental aspect of relational databases. It is done by getting records from one set related to those records through a common colmun. There are various types of joins.The most common type is called inner join. To support joins, the LINQ provides the join operator.ch others in another set.

Practical LearningPractical Learning: Joining Records in LINQ

  1. Display the Rental Orders form and double-click an unoccupied area of its body
  2. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental1
    {
        public partial class RentalOrders : Form
        {
            public RentalOrders()
            {
                InitializeComponent();
            }
    
            private void ShowRentalOrders()
            {
                bool bVehiclesExist = false;
                bool bEmployeesExist = false;
                bool bRentalOrdersExist = false;
    
                dsVehicles.Clear();
                dsEmployees.Clear();
                dsRentalOrders.Clear();
                lvwRentalOrders.Items.Clear();
                string strFileName = @"C:\Bethesda Car Rental1\Employees.xml";
    
                if (File.Exists(strFileName))
                {
                    dsEmployees.ReadXml(strFileName);
                    bEmployeesExist = true;
                }
    
                strFileName = @"C:\Bethesda Car Rental1\Vehicles.xml";
    
                if (File.Exists(strFileName))
                {
                    dsVehicles.ReadXml(strFileName);
                    bVehiclesExist = true;
                }
    
                strFileName = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
                if (File.Exists(strFileName))
                {
                    dsRentalOrders.ReadXml(strFileName);
                    bRentalOrdersExist = true;
                }
    
                if ((bRentalOrdersExist == true) &&
                    (bEmployeesExist == true) &&
                    (bVehiclesExist == true))
                {
                    var rentalOrders = from recRentalOrders in dsRentalOrders.Tables[0].AsEnumerable()
                                       join recEmployees in dsEmployees.Tables[0].AsEnumerable() on recRentalOrders["EmployeeNumber"].ToString() equals recEmployees["EmployeeNumber"].ToString()
                                       join recVehicles in dsVehicles.Tables[0].AsEnumerable() on recRentalOrders["TagNumber"].ToString() equals recVehicles["TagNumber"].ToString()
                                       select new
                                       {
                                           ReceiptNumber = recRentalOrders["ReceiptNumber"].ToString(),
                                           DateProcessed = DateTime.Parse(recRentalOrders["DateProcessed"].ToString()).ToShortDateString(),
                                           Employee = recEmployees["EmployeeNumber"].ToString() + ": " + recEmployees["EmployeeName"],
                                           Customer = string.Concat(recRentalOrders["CustomerLastName"].ToString(), ", ", recRentalOrders["CustomerFirstName"].ToString()),
                                           Vehicle = string.Concat(recVehicles["TagNumber"].ToString(), ": ", recVehicles["Make"].ToString(), " ", recVehicles["Model"].ToString()),
                                           Condition = recRentalOrders["VehicleCondition"].ToString(),
                                           TankLevel = recRentalOrders["TankLevel"].ToString(),
                                           MileageStart = recRentalOrders["MileageStart"].ToString(),
                                           MileageEnd = recRentalOrders["MileageEnd"].ToString(),
                                           MileageTotal = recRentalOrders["MileageTotal"].ToString(),
                                           StartDate = DateTime.Parse(recRentalOrders["RentStartDate"].ToString()).ToShortDateString(),
                                           EndDate = DateTime.Parse(recRentalOrders["RentEndDate"].ToString()).ToShortDateString(),
                                           TotalDays = recRentalOrders["TotalDays"].ToString(),
                                           RateApplied = recRentalOrders["RateApplied"].ToString(),
                                           SubTotal = recRentalOrders["SubTotal"].ToString(),
                                           TaxRate = recRentalOrders["TaxRate"].ToString(),
                                           TaxAmount = recRentalOrders["TaxAmount"].ToString(),
                                           OrderTotal = recRentalOrders["OrderTotal"].ToString(),
                                           OrderStatus = recRentalOrders["OrderStatus"].ToString()
                                       };
    
                    foreach (var rentalOrder in rentalOrders)
                    {
                        ListViewItem lviRentalOrder = new ListViewItem(rentalOrder.ReceiptNumber);
    
                        lviRentalOrder.SubItems.Add(rentalOrder.DateProcessed);
                        lviRentalOrder.SubItems.Add(rentalOrder.Employee);
                        lviRentalOrder.SubItems.Add(rentalOrder.Customer);
                        lviRentalOrder.SubItems.Add(rentalOrder.Vehicle);
                        lviRentalOrder.SubItems.Add(rentalOrder.Condition);
                        lviRentalOrder.SubItems.Add(rentalOrder.TankLevel);
                        lviRentalOrder.SubItems.Add(rentalOrder.MileageStart);
                        lviRentalOrder.SubItems.Add(rentalOrder.MileageEnd);
                        lviRentalOrder.SubItems.Add(rentalOrder.MileageTotal);
                        lviRentalOrder.SubItems.Add(rentalOrder.StartDate);
                        lviRentalOrder.SubItems.Add(rentalOrder.EndDate);
                        lviRentalOrder.SubItems.Add(rentalOrder.TotalDays);
                        lviRentalOrder.SubItems.Add(rentalOrder.RateApplied);
                        lviRentalOrder.SubItems.Add(rentalOrder.SubTotal);
                        lviRentalOrder.SubItems.Add(rentalOrder.TaxRate);
                        lviRentalOrder.SubItems.Add(rentalOrder.TaxAmount);
                        lviRentalOrder.SubItems.Add(rentalOrder.OrderTotal);
                        lviRentalOrder.SubItems.Add(rentalOrder.OrderStatus);
    
                        lvwRentalOrders.Items.Add(lviRentalOrder);
                    }
                }
            }
    
            private void RentalOrders_Load(object sender, EventArgs e)
            {
                ShowRentalOrders();
            }
        }
    }
  3. Return to the Rental Order form and double-click New Rental Order
  4. Implement the event as follows:
    private void btnNewRentalOrder_Click(object sender, EventArgs e)
    {
        NewRentalOrder nro = new NewRentalOrder();
        nro.ShowDialog();
        ShowRentalOrders();
    }
  5. Return to the Rental Order form and double-click Update Rental Order ....
  6. Implement the event as follows:
    private void btnUpdateRentalOrder_Click(object sender, EventArgs e)
    {
        UpdateRentalOrder uro = new UpdateRentalOrder();
        uro.ShowDialog();
        ShowRentalOrders();
    }
  7. Return to the Rental Order form and double-click the Close button
  8. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  9. In the Solution Explorer, right-click Form1.cs and click Rename
  10. Type BethesdaCarRental.cs and press Enter
  11. Design the form as follows:
     
    Bethesda Car Rental
    Control Text Name
    Button Rental Orders... btnRentalOrders
    Button Vehicles... btnVehicles
    Button Employees btnEmployees
    Button Close btnClose
  12. Double-click an unoccupied area of the form
  13. Return to the form and double click Rental Orders
  14. Return to the form and double-click Vehicles
  15. Return to the form and double-click Employees
  16. Return to the form and double-click Close
  17. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental20
    {
        public partial class BethesdaCarRental : Form
        {
            public BethesdaCarRental()
            {
                InitializeComponent();
            }
    
            private void BethesdaCarRental_Load(object sender, EventArgs e)
            {
                // If this directory doesn't exist, create it
                Directory.CreateDirectory(@"C:\Bethesda Car Rental1");
            }
    
            private void btnRentalOrders_Click(object sender, EventArgs e)
            {
                RentalOrders ros = new RentalOrders();
                ros.Show();
            }
    
            private void btnVehicles_Click(object sender, EventArgs e)
            {
                Vehicles cars = new Vehicles();
                cars.Show();
            }
    
            private void btnEmployees_Click(object sender, EventArgs e)
            {
                Employees empls = new Employees();
                empls.Show();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
 
 
 
  1. Execute the application
  2. On the Bethesda Car Rental form, click the Employees button
  3. Create a few employees as follows:
     
    Employee # First Name Last Name Title
    92735 Jeffrey Leucart General Manager
    29268 Catherine Rawley Administrative Assistant
    73948 Allison Garlow Rental Associate
    40508 David Stillson Technician
    24793 Michelle Taylor Accounts Manager
    20480 Peter Futterman Rental Associate
    72084 Georgia Rosen Customer Service Representative
    38240 Karen Blackney Rental Associate
  4. Close the Employees form
  5. Click the Vehicles button and create a few vehicles records as follows:
     
    Tag # Make Model Doors Passengers Condition Category Availability
    2AM9952 Ford Fiesta SE 4 5 Driveable Economy Available
    6AD8274 Mazda CX-9 4 5 Excellent Mini Van Available
    8AG3584 Toyota Sienna LE FWD 4 8 Excellent Passenger Van Available
    KER204 Ford Focus SF 4 5 Excellent Compact Being Serviced
    3AD9283 Kia Rio EX 4 5 Excellent Economy Rented
    8AE9294 Lincoln MKT 3.5L 4 5 Excellent Full Size Available
    KLT840 Ford E-350 XL 3 15 Driveable Passenger Van Available
    8AL8033 Toyota Corolla LE 4 5 Excellent Compact Available
    4AF9284 Toyota Tacoma 2 2 Needs Repair Pickup Truck Available
    ADG279 GMC Acadia SLE 4 5 Excellent SUV Rented
    1AD8049 Dodge Charger SXT 4 5 Excellent Standard Being Serviced
    9MD3974 Toyota Sienna LE FWD 4 8 Driveable Passenger Van Rented
    5AJ9274 BMW 528i 4 5 Excellent Full Size Available
    GTH295 Kia Rio5 4 5 Excellent Economy Available
    8AT2408 Mazda Miata MX-5 2 2 Excellent Compact Available
    6AP2486 Fiat 500 2 4 Excellent Economy Available
    2AL9485 Chrysler 200 2 2 Excellent Compact Available
    DFP924 Toyota Sienna LE FWD 4 8 Driveable Passenger Van Available
    2MD8382 Toyota RAV4 I4 4X4 4 5 Excellent SUV Available
    8AR9374 Honda Accord LX 4 5 Excellent Standard Rented
    5MD2084 Chevrolet Equinox LS 4 5 Driveable Mini Van Available
    BND927 Ford Fiesta SE 4 5 Driveable Economy Available
    6AP2749 Toyota Corolla LE 4 5 Excellent Compact Rented
    8AL7394 Ford F-250 SD Reg Cab 4X4 2 2 Excellent Pickup Truck Available
    4MD2840 Chevrolet 2500 LS 3 15 Excellent Passenger Van Rented
    G249580 Nissan Sentra SR 4 5 Excellent Compact Available
    3AK7397 Chrysler 200 2 2 Excellent Compact Available
    VGT927 Toyota Tundra Dbl Cab 4X4 2 5 Excellent Pickup Truck Available
    2AT9274 Ford Focus SF 4 5 Excellent Compact Available
    6AH8429 Lincoln MKT 3.5L 4 5 Needs Repair Full Size Available
    8MD9284 Ford Escape SE I4 4 5 Excellent Mini Van Available
    PLD937 Chevrolet Imapala LT 4 5 Excellent Compact Being Serviced
    5AK2974 Fiat 500 2 4 Excellent Economy Available
    1MD9284 Ford Escape SE I4 4 5 Excellent Mini Van Being Serviced
    SDG624 Chevrolet Volt 4 5 Excellent Standard Available
    2AR9274 Kia Rio SX 4 5 Excellent Economy Available
    JWJ814 Cadillac CTS-V 4 5 Excellent Full Size Available
    7MD9794 Ford Focus SF 4 5 Excellent Compact Rented
    UQW118 Chevrolet 2500 LS 3 15 Needs Repair Passenger Van Available
    2MD9247 Toyota RAV4 I4 4X4 4 5 Excellent SUV Available
  6. Close the Vehicles forms
  7. Create new rental orders as follows:
     
    Receipt # RPPB First Name Last Name Address City State ZIP Code Tag # Car Condition Tank Level Mileage Start Start Date Rate Applied Order Status
    100001 20480 Marcel Buhler 6800 Haxell Crt Alexandria VA 22314 8AG3584 Excellent Empty 12728 7/14/2014 69.95 Vehicle With Customer
    100002 24793 Joan Altman 3725 South Dakota Ave NW Washington DC 20012 KER204 Good 3/4 Full 24715 7/18/2014 62.95 Vehicle With Customer
    100003 38240 Thomas Filder 4905 Herrenden St Arlington VA 22204 8AL8033 Excellent Full 6064 7/18/2014 34.95 Vehicle With Customer
  8. Update the following rental orders:
     
    Receipt # to Open Tank Level Mileage End End Date Total Days Rate Applied Sub-Total Tax Rate Tax Amount Order Total Order Status
    100001 Half Tank 13022 7/19/2014 5 69.95 349.75 7.75% 27.11 376.86 Rental Order Complete
    100003 Full 6229 7/21/2014 3 34.95 104.85 7.75% 8.13 112.98 Rental Order Complete
  9. Create a new rental order as follows:
     
    Receipt # RPPB First Name Last Name Address City State ZIP Code Tag # Car Condition Tank Level Mileage Start Start Date Rate Applied Order Status
    100004 73948 Gregory Strangeman 5530 Irving St College Park MD 20740 2AT9274 Excellent 1/2 Tank 8206 7/21/2014 28.95 Vehicle With Customer
  10. Update the following rental order:
     
    Receipt # to Open Tank Level Mileage End End Date Total Days Rate Applied Sub-Total Tax Rate Tax Amount Order Total Order Status
    100002 Full 25694 7/22/2014 4 62.95 251.8 7.75% 19.39 271.19 Rental Order Complete
  11. Create a new rental order as follows:
     
    Receipt # RPPB First Name Last Name Address City State ZIP Code Tag # Car Condition Tank Level Mileage Start Start Date Rate Applied Order Status
    100005 38240 Michelle Russell 10070 Weatherwood Drv Rockville MD 20853 8AE9294 Excellent Full 3659 7/22/2014 38.95 Vehicle With Customer
  12. Update the following rental orders:
     
    Receipt # to Open Tank Level Mileage End End Date Total Days Rate Applied Sub-Total Tax Rate Tax Amount Order Total Order Status
    100005 Full 3806 7/23/2014 1 38.95 38.95 7.75% 3.00 41.95 Rental Order Complete
    100004 3/4 Full 8412 7/25/2014 2 28.95 57.9 7.75% 4.46 62.36 Rental Order Complete
  13. Close the forms and return to your programming environment
 

Home Copyright © 2008-2014, FunctionX