Home

Data Relationships

 

Fundamentals of Data Relationships

 

Introduction

In previous lessons, we created applications that included various objects. In those applications, we were using the objects as independent as possible. A relational database is an application in which information flows from one object to another. To illustrate this, imagine you are creating a database for a car rental company (actually our Bethesda Car Rental application). When processing an order, the user would have to select a car and the customer who would rent it. To make this possible, one one hand you must create a special relationship between the list that holds the cars and the object used to process orders, on the other hand you must create a relationship between the list of customers and the order processor.

The DataSet and the other data-based classes of the System.Data namespace provide all the functionalities you need to create relationships among objects.

Consider the following starting points of a data set:

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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        DataSet dsStudents;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));

            colUsername = new DataColumn("Username", Type.GetType("System.String"));

            tblStudents = new DataTable("Student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}

Practical LearningPractical Learning: Introducing Data Relationships

  1. Start Microsoft Visual C#
  2. Create a new Windows Application named BethesdaCarRental2
  3. To add a new form to the project, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  4. Set the Name to OrderProcessing and press Enter
  5. Design the form as follows:
     
    Bethesda Car Rental: Order Processing
    Control Text Name Other Properties
    Label Processed By:   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label Employee #:    
    MaskedTextBox   cbxEmployeeID  
    Label Employee Name:    
    TextBox   txtEmployeeName  
    Label Processed For   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label Driver's Lic #:    
    TextBox   cbxCustomerID  
    Label Cust Name:    
    TextBox   txtCustomerName  
    Label Address:    
    TextBox   txtCustomerAddress  
    Label City:    
    TextBox   txtCustomerCity  
    Label State:    
    ComboBox   cbxCustomerStates DropDownStyle: DropDownList
    Sorted: True
    Items: AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY
    Label ZIP Code    
    TextBox   txtCustomerZIPCode  
    Label Car Selected   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label Tag Number:    
    TextBox   cbxCarID  
    Label Car Condition:    
    ComboBox   cbxCarConditions Sorted: True
    Items:
    Needs Repair
    Drivable
    Excellent
    Label Make:    
    TextBox   txtMake  
    Label Model:    
    TextBox   txtModel  
    Label Year:    
    TextBox   txtCarYear  
    label Tank Level:    
    ComboBox   cbxTankLevels Empty
    1/4 Empty
    1/2 Full
    3/4 Full
    Full
    Label Mileage Start:    
    TextBox   txtMileageStart TextAlign: Right
    Label Mileage }:    
    TextBox   txtMileageEnd TextAlign: Right
    Label Order Timing   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label Start Date:    
    DateTimePicker   dtpStartDate  
    Label End Date:    
    DateTimePicker   dtpEndDate  
    Label Days:    
    TextBox 0 txtDays TextAlign: Right
    Label Order Status    
    ComboBox   cbxOrderStatus Items:
    Car On Road
    Car Returned
    Order Reserved
    Label Order Evaluation   AutoSize: False
    BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    TextAlign: MiddleLeft
    Label Rate Applied:    
    TextBox 0.00 txtRateApplied TextAlign: Right
    Button Rental Rates btnRentalRates  
    Label Sub-Total:    
    TextBox 0.00 txtSubTotal TextAlign: Right
    Button Calculate btnCalculate  
    Label Tax Rate:    
    TextBox 7.75 txtTaxRate TextAlign: Right
    Label %    
    Button Save btnSave  
    Label Tax Amount:    
    TextBox 0.00 txtTaxAmount TextAlign: Right
    Button Print... btnPrint  
    Label Order Total:    
    TextBox 0.00 txtOrderTotal TextAlign: Right
    Button Print Preview... btnPrintPreview  
    Label Receipt #:    
    TextBox 0 txtReceiptNumber  
    Button Open btnOpen  
    Button New Rental Order/Reset btnNewRentalOrder  
    Button Close btnClose  
  6. On to the Order Processing form, double-click the Start Date date time picker control
  7. 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 BethesdaCarRental2
    {
        public partial class OrderProcessing : Form
        {
            public OrderProcessing()
            {
                InitializeComponent();
            }
    
            private void dtpStartDate_ValueChanged(object sender, EventArgs e)
            {
                dtpEndDate.Value = dtpStartDate.Value;
            }
        }
    }
  8. Return to the Order Processing form and double-click the End Date control
  9. 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 = this.dtpStartDate.Value;
        DateTime dteEnd = this.dtpEndDate.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;
    
        txtDays.Text = days.ToString();
        // At any case, we will let the clerk specify the actual number of days
    }
  10. Return to the Order Processing form and double-click the Rental Rates button
  11. Implement its event as follows:
     
    private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates wndRates = new RentalRates();
        wndRates.Show();
    }
  12. Return to the Order Processing form and double-click the Calculate button
  13. Implement its 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(this.txtDays.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Number of Days");
        }
    
        try
        {
            RateApplied = double.Parse(txtRateApplied.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Amount for Rate Applied");
        }
    
        SubTotal = Days * RateApplied;
        txtSubTotal.Text = SubTotal.ToString("F");
    
        try
        {
            TaxRate = double.Parse(txtTaxRate.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Tax Rate");
        }
    
        TaxAmount = SubTotal * TaxRate / 100;
        txtTaxAmount.Text = TaxAmount.ToString("F");
    
        OrderTotal = SubTotal + TaxAmount;
        txtOrderTotal.Text = OrderTotal.ToString("F");
    }
  14. Return to the Order Processing form
  15. From the Printing section of the Toolbox, click PrintDocument and click the form
  16. In the Properties window, set its (Name) to docPrint and press Enter
  17. 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 = "Car Rental Order";
        fntString = new System.Drawing.Font("Times New Roman", 22,
                            FontStyle.Regular);
        e.Graphics.DrawString(strDisplay, fntString,
                            Brushes.Black, 320, 150);
    
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 80, 187, 750, 187);
        e.Graphics.DrawLine(new Pen(Color.Black, 2), 80, 190, 750, 190);
    
        fntString = new System.Drawing.Font("Times New Roman", 12,
                    FontStyle.Bold);
        e.Graphics.DrawString("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("Driver's License #: ", fntString,
                                      Brushes.Black, 100, 300);
        e.Graphics.DrawString("Name: ", fntString,
                                     Brushes.Black, 420, 300);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(cbxCustomerID.Text, fntString,
                                      Brushes.Black, 260, 300);
        e.Graphics.DrawString(txtCustomerName.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(cbxCarID.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("Year: ", fntString,
                                      Brushes.Black, 420, 450);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtCarYear.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(cbxCarConditions.Text, fntString,
                                      Brushes.Black, 260, 510);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 100, 530, 380, 530);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Tank Level: ", fntString,
                                      Brushes.Black, 420, 510);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(cbxTankLevels.Text, fntString,
                                      Brushes.Black, 530, 510);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 420, 530, 720, 530);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Mileage Start:", fntString,
                                      Brushes.Black, 100, 540);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtMileageStart.Text, fntString,
                                      Brushes.Black, 260, 540);
        e.Graphics.DrawLine(new Pen(Color.Black, 1), 
    				100, 560, 380, 560);
    
        fntString = new Font("Times New Roman", 12, FontStyle.Bold);
        e.Graphics.DrawString("Mileage }:", 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(dtpStartDate.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(dtpEndDate.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("Days:", fntString,
                              Brushes.Black, 550, 650);
        fntString = new Font("Times New Roman", 12, FontStyle.Regular);
        e.Graphics.DrawString(txtDays.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);
    }
  18. Return to the Order Processing form
  19. From the Printing section of the Toolbox, click PrintDialog and click the form
  20. In the Properties window, change its Name to dlgPrint
  21. Still in the Properties windows, set its Document property to docPrint
  22. 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();
    }
  23. Return to the Order Processing form
  24. From the Printing section of the Toolbox, click PrintPreviewDialog and click the form
  25. In the Properties window, change its (Name) to dlgPrintPreview
  26. Still in the Properties windows, set its Document property to docPrint
  27. On the Order Processing form, double-click the Print Preview button
  28. Implement the event as follows:
     
    private void btnPrintPreview_Click(object sender, EventArgs e)
    {
        dlgPrintPreview.ShowDialog();
    }
  29. Return to the Order Processing form and double-click the New Rental Order/Reset button
  30. Implement the event as follows:
     
    private void btnNewRentalOrder_Click(object sender, EventArgs e)
    {
        cbxEmployeeID.SelectedIndex = -1;
        txtEmployeeName.Text = "";
        cbxCustomerID.SelectedIndex = -1;
        txtCustomerName.Text = "";
        txtCustomerAddress.Text = "";
        txtCustomerCity.Text = "";
        cbxCustomerStates.Text = "DC";
        txtCustomerZIPCode.Text = "";
        cbxCarID.SelectedIndex = -1;
        cbxCarConditions.SelectedIndex = 2;
        txtMake.Text = "";
        txtModel.Text = "";
        txtCarYear.Text = "";
        cbxTankLevels.SelectedIndex = 0;
        txtMileageStart.Text = "0";
        txtMileageEnd.Text = "0";
        dtpDateProcessed.Value = DateTime.Today;
        dtpStartDate.Value = DateTime.Today;
        dtpEndDate.Value = DateTime.Today;
        txtDays.Text = "1";
        txtRateApplied.Text = "0.00";
        txtSubTotal.Text = "0.00";
        txtTaxAmount.Text = "0.00";
        txtOrderTotal.Text = "0.00";
        cbxOrderStatus.SelectedIndex = 0;
    }
  31. In the Solution Explorer, right-click Form1.cs and click Rename
  32. Type Central.cs and press Enter
  33. Design the form as follows:
     
    Control Text Name
    Button Order Processing... btnOrderProcessing
    Button Customers... btnCustomers
    Button Car Editor... btnCarEditor
    Button Employees btnEmployees
    Button Close btnClose
  34. Double-click an unoccupied area of the form and 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 BethesdaCarRental2
    {
        public partial class Central : Form
        {
            public Central()
            {
                InitializeComponent();
            }
    
            private void Central_Load(object sender, EventArgs e)
            {
                // If this directory doesn't exist, create it
                Directory.CreateDirectory(@"C:\Bethesda Car Rental1");
            }
        }
    }
  35. Return to the Central form and double-click the Order Processing button
  36. Implement its event as follows:
     
    private void btnOrderProcessing_Click(object sender, EventArgs e)
    {
        OrderProcessing order = new OrderProcessing();
        order.ShowDialog();
    }
  37. To add a new form to the project, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  38. Set the Name to RentalRates and press Enter
  39. 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  
  40. 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
  41. Complete the design of the form as follows:
     
    Rental Rates
  42. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  43. Set the Name to Employees and click Add
  44. From the Data section of the Toolbox, click DataSet and click the form
  45. Click Untyped Dataset and click OK
  46. In the Properties window, change the following characteristics:
    DataSetName: Employees
    (Name): dsEmployees
  47. Click Tables and click its button
  48. Click Add and change the following characteristics:
    TableName: Employee
    (Name) tblEmployee
  49. Click Columns and click its button
  50. In the Columns Collection Editor, click Add change the characteristics as follows:
    ColumnName: EmployeeID
    (Name): colEmployeeID

Using Unique Values

In order to create a relationship, you need two tables. One table would hold the original data. That table is referred to as the parent. That table would provide its data to another table. The table that receives data is referred to as a child table. The table that acts as the parent must have a way to uniquely identify each record.

In the previous lesson, we saw that, to create unique values for a column, you could set its Unique property to True. 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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
        }
    }
}

Once this property is set, the user must remember to specify the value of that column, otherwise, the record would not be created. In some cases, the user may not have the right value at the time of data entry or the user may simply be confused.

To assist you with specifying the unique value of a column, you can ask the compiler to take care of this. To make this happen, you can set an incremental value on the column. To support this, the DataColumn class is equipped with a Boolean property named AutoIncrement. The default value of this property is False. When this property is set to True, when the user decides to add a new record, the compiler would increment the current value of the column. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
}

By default, if the DataColumn.AutoIncrement property is set to True, before any record is created on the table, the initial value of the column is set to 0 and that would be the first value of the column. If you want, you can start the records with another value. To support this, the DataColumn class is equipped with a property named AutoIncrementSeed, which is a Long integral type. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
}

By default, if the AutoIncrement property of the DataColumn class is set to True, when a record is being created, the value of the column would be incremented by 1. If you want it to be incremented by another value, you can use the AutoIncrementStep property. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
    colStudentID.AutoIncrementStep = 5;
}

Practical LearningPractical Learning: Introducing Data Relationships

  1. While the EmployeeID member is selected, in the Properties list, double-click AutoIncrement to set its value to True
  2. Click AutoIncrementSeed and type 1
  3. Click Add continuously and create the following columns:
     
    AllowDBNull ColumnName (Name) Expression
    False EmployeeNumber colEmployeeNumber  
      FirstName colFirstName  
    False LastName colLastName  
      FullName colFullName LastName + ', ' + FirstName
      Title colTitle  
  4. Click Close and click Close
  5. Design the form as follows:
     
     
    Control Text Name Other Properties
    DataGridView   dgvEmployees DataSource: dsEmployees
    DataMember: Employee
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    EmployeeID Empl ID 50
    EmployeeNumber Empl # 65
    FirstName First Name 70
    LastName Last Name 70
    FullName Full Name 120
    Title   110
  6. Double-click an unoccupied area of the form and 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 BethesdaCarRental2
    {
        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);
            }
        }
    }
  7. Return to the form and click an unoccupied area of its body
  8. In the Properties window, click Events and double-click FormClosing
  9. Implement its event as follows:
     
    private void Employees_FormClosing(object sender, FormClosingEventArgs e)
    {
        dsEmployees.WriteXml(@"C:\Bethesda Car Rental1\employees.xml");
    }
  10. Return to the form and double-click the Close button
  11. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  12. Display the Central form and double-click the Employees button
  13. Implement it as follows:
     
    private void btnEmployees_Click(object sender, EventArgs e)
    {
        Employees frmEmployees = new Employees();
        frmEmployees.ShowDialog();
    }
  14. Execute the application
  15. On the Central form, click the Employees button
  16. Create a few employees as follows:
     
    Employee # First Name Last Name Title
    62-845 Patricia Katts General Manager
    92-303 Henry Larson Sales Representative
    25-947 Gertrude Monay Sales Representative
    73-947 Helene Sandt Intern
    40-508 Melanie Karron Sales Representative
    22-580 Ernest Chisen Sales Manager
    20-308 Melissa Roberts Administrative Assistant
     
  17. Close the forms and return to your programming environment
  18. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  19. Set the Name to Customers and click Add
  20. From the Data section of the Toolbox, click DataSet and click the form
  21. Click Untyped Dataset and click OK
  22. In the Properties window, change the following characteristics:
    DataSetName: Customers
    (Name): dsCustomers
  23. Click Tables and click its button
  24. Click Add and change the following characteristics:
    TableName: Customer
    (Name) tblCustomer
  25. Click Columns and click its button
  26. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    CustomerID colCustomerID AutoIncrement: True
    AutoIncrementSeed: 1
    DrvLicNumber colDrvLicNumber AllowDBNull: False
    Unique: True
    FullName colFullName AllowDBNull: False
    Address colAddress  
    City colCity  
    State colState  
    ZIPCode colZIPCode  
  27. Click Close and click Close
  28. Design the form as follows:
     
    Bethesda Car Rental: Customers
     
    Control Text Name Other Properties
    DataGridView   dgvCustomers DataSource: dsCustomers
    DataMember: Customer
    Button Close btnClose  
    Data Grid Columns
    DataPropertyName HeaderText Width
    CustomerID Cust ID 50
    DrvLicNumber Drv Lic # 80
    FullName Full Name  
    Address    
    City   80
    State   45
    ZIPCode ZIP Code 60
  29. Double-click an unoccupied area of the form and 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 BethesdaCarRental2
    {
        public partial class Customers : Form
        {
            public Customers()
            {
                InitializeComponent();
            }
    
            private void Customers_Load(object sender, EventArgs e)
            {
                string strFilename = @"C:\Bethesda Car Rental1\customers.xml";
    
                if (File.Exists(strFilename))
                    dsCustomers.ReadXml(strFilename);
            }
        }
    }
  30. Return to the form and click an unoccupied area of its body
  31. In the Properties window, click Events and double-click FormClosing
  32. Implement its event as follows:
     
    private void Customers_FormClosing(object sender, FormClosingEventArgs e)
    {
         dsCustomers.WriteXml(@"C:\Bethesda Car Rental1\customers.xml");
    }
  33. Return to the form and double-click the Close button
  34. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  35. Display the Central form and double-click the Customers button
  36. Implement it as follows:
     
    private void btnCustomers_Click(object sender, EventArgs e)
    {
        Customers frmCustomers = new Customers();
        frmCustomers.ShowDialog();
    }
  37. Execute the application
  38. Click the Customers button and create a few customers as follows:
     
    Driver's Lic. # State Full Name Address City ZIP Code
    M-505-862-575 MD Lynda Melman 4277 Jamison Avenue Silver Spring 20904
    379-82-7397 DC John Villard 108 Hacken Rd NE Washington 20012
    J-938-928-274 MD Chris Young 8522 Aulage Street Rockville 20852
    497-22-0614 PA Pamela Ulmreck 12075 Famina Rd Blain 17006
    922-71-8395 VA Helene Kapsco 806 Hyena Drive Alexandria 22231
    C-374-830-422 MD Hermine Crasson 6255 Old Georgia Ave Silver Spring 20910
    836-55-2279 NY Alan Pastore 4228 Talion Street Amherst 14228
    397-59-7487 TN Phillis Buster 724 Cranston Circle Knoxville 37919
    115-80-2957 FL Elmus Krazucki 808 Rasters Ave Orlando 32810
    294-90-7744 VA Helena Weniack 10448 Great Pollard Hwy Arlington 22232
  39. Close the Customers form
  40. To add a new form to the application, in the Solution Explorer, right-click BethesdaCarRental2 -> Add -> Windows Form...
  41. Set the Name to CarEditor and click Add
  42. Design the form as follows: 
     
    Bethesda Car Rental - Car Editor
    Control Text Name Other Properties
    Label Text #    
    TextBox   txtTagNumber  
    Label Make:    
    TextBox   txtMake  
    Label Model:    
    TextBox   txtModel  
    Label Year:    
    TextBox   txtYear  
    Label Category:    
    ComboBox   cboCategories DropDownStyle: DropDownList
    Items: Economy
    Compact
    Standard
    Full Size
    Mini Van
    SUV
    Truck
    Van
    PictureBox   pbxCar SizeMode: Zoom
    CheckBox CD Player chkCDPlayer CheckAlign: MiddleRight
    CheckBox DVD Player chkDVDPlayer CheckAlign: MiddleRight
    Button Select Car Picture... btnSelectPicture  
    CheckBox Available chkAvailable CheckAlign: MiddleRight
    Label Picture Name lblPictureName  
    Button Submit btnSubmit  
    Button Close btnClose DialogResult: Cancel
    OpenFileDialog (Name): 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
  43. Double-click the Select Car Picture button and implement its event as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    
    namespace BethesdaCarRental2
    {
        public partial class CarEditor : Form
        {
            public CarEditor()
            {
                InitializeComponent();
            }
    
            private void btnSelectPicture_Click(object sender, EventArgs e)
            {
                if (dlgPicture.ShowDialog() == DialogResult.OK)
                {
                    lblPictureName.Text = dlgPicture.FileName;
                    pbxCar.Image = Image.FromFile(lblPictureName.Text);
                }
            }
        }
    }
  44. Return to the Car Editor form
  45. From the Data section of the Toolbox, click DataSet and click the form
  46. Click Untyped Dataset and click OK
  47. In the Properties window, change the following characteristics:
    DataSetName: Cars
    (Name): dsCars
  48. Click Tables and click its button
  49. Click Add and change the following characteristics:
    TableName: Car
    (Name) tblCar
  50. Click Columns and click its button
  51. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    CarID colCarID AutoIncrement: True
    AutoIncrementSeed: 1
    TagNumber colTagNumber AllowDBNull: False
    Unique: True
    Make colMake  
    Model colModel  
    Year colYear DataType: System.UInt16
    Category colCategory  
    CDPlayer colCDPlayer DataType: System.Boolean
    DVDPlayer colDVDPlayer DataType: System.Boolean
    Available colAvailable DataType: System.Boolean
  52. Click Close and click Close
  53. Double-click the Submit button and implement the event as follows:
     
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        if (txtTagNumber.Text.Length == 0)
        {
            MessageBox.Show("You must enter the car's tag number");
            return;
        }
    
        if (txtMake.Text.Length == 0)
        {
            MessageBox.Show("You must specify the car's manufacturer");
            return;
        }
    
        if (txtModel.Text.Length == 0)
        {
            MessageBox.Show("You must enter the model of the car");
            return;
        }
    
        if (txtYear.Text.Length == 0)
        {
            MessageBox.Show("You must enter the year of the car");
            return;
        } 
                
        DataRow rowCar = tblCar.NewRow();
    
        rowCar["TagNumber"] = txtTagNumber.Text;
        rowCar["Make"] = txtMake.Text;
        rowCar["Model"] = txtModel.Text;
        rowCar["CarYear"] = txtYear.Text;
        rowCar["Category"] = cbxCategories.Text;
        rowCar["CDPlayer"] = chkCDPlayer.Checked;
        rowCar["DVDPlayer"] = chkDVDPlayer.Checked;
        rowCar["Available"] = chkAvailable.Checked;
        tblCar.Rows.Add(rowCar);
    
        dsCars.WriteXml(@"C:\Bethesda Car Rental1\cars.xml");
    
        if (lblPictureName.Text.Length != 0)
        {
            FileInfo flePicture = new FileInfo(lblPictureName.Text);
            flePicture.CopyTo(@"C:\Bethesda Car Rental1\" +
                              txtTagNumber.Text + flePicture.Extension);
        }
    
        txtTagNumber.Text = "";
        txtMake.Text = "";
        txtModel.Text = "";
        txtYear.Text = "";
        cbxCategories.Text = "Economy";
        chkCDPlayer.Checked = false;
        chkDVDPlayer.Checked = false;
        chkAvailable.Checked = false;
        lblPictureName.Text = ".";
        pbxCar.Image = null;
    }
  54. Return to the form and double-click the Close button
  55. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  56. Display the Central form and double-click the Car Editor button
  57. Implement it as follows:
     
    private void btnCarEditor_Click(object sender, EventArgs e)
    {
        CarEditor editor = new CarEditor();
        editor.ShowDialog();
    }
  58. Copy the following pictures to any folder somewhere on your computer:
     
    Chevrolet Avalanche
    BMW: 335i Chevrolet Avalanche
    Mazda Miata
    Honda Accord Mazda Miata
    Chevrolet Aveo Ford E150XL
    Chevrolet Aveo Ford E150XL
    Buick LaCrosse Honda Civic
    Buick Lacrosse Honda Civic
    Ford F-150 Mazda Mazda5
    Ford F-150 Mazda Mazda5
    Volvo S40 Land Rover LR3
    Volvo S40 Land Rover LR3
  59. Execute the application
  60. Click the Car Editor button and create the cars
     
    Bethesda Car Rental - Car Editor
  61. Close the Car Editor form
  62. Return to your programming environment
  63. Display the Order Processing form
  64. From the Data section of the Toolbox, click DataSet and click the form
  65. Click Untyped Dataset and click OK
  66. In the Properties window, change the following characteristics:
    DataSetName: RentalOrders
    (Name): dsRentalOrders
  67. Click Tables and click its button
  68. Click Add and change the following characteristics:
    TableName: RentalOrder
    (Name) tblRentalOrder
  69. Click Columns and click its ellipsis button
  70. In the Columns Collection Editor, click Add twice and create the following columns:
     
    ColumnName (Name) Additional Properties
    RentalOrderID colRentalOrderID AutoIncrement: True
    AutoIncrementSeed: 1000
    DateProcessed colDateProcessed DataType: System.DateTime
  71. Click Close

Relationship Constraints

 

Introduction

In a relation, a constraint is a rule that would specify how some data is created or provided to a table. For example, a constraint can provide the means by which some values of a column are entered. Another type of constraint can create a rule that controls the types or ranges of values that can be accepted for a column; non-acceptable values would be rejected. There are various types of constraints and most are supported in the DataSet system.

To visually create a constraint, display the Table Collection Editor. In the Members list, click the name of the table. In the Properties list, click the Constraints list and click its ellipsis button:

Column Editor

To support constraints, the DataTable class is equipped with a property named Constraints, which is an object of type ConstraintCollection.

Unique Constraints

A unique constraint is a rule that states that the value of one column or the combination of values of some columns must be unique among the records of a table. The unique constraint can involve only one column as we saw for the Unique property of a column. For example, if you are creating a list of usernames for the students of a school, you would not want two students to have the same username. Here is an example:

 

First Name MI Last Name Username
Frank   Adams fadams
Fannie H Adams fadams
Virginie   Mengue vmengue
Christine M Chambers cchambers
Cynthia P Chambers cchambers
Carlton   Chambers cchambers
Alexis   Leandro aleandro

When creating the table, you can set up a unique constraint so that duplicate values would be rejected. In this case, every time the user enters a new value, the compiler (actually an interpreter) would check if that value exists already. If so, the user would be informed and must take appropriate actions to correct it. Here are examples:

First Name MI Last Name Username Valid
Frank   Adams fadams fadams
Fannie H Adams fadams fhadams
Virginie   Mengue vmengue vmengue
Christine M Chambers cchambers cmchambers
Cynthia P Chambers cchambers cphambers
Carlton   Chambers cchambers cchambers
Alexis   Leandro aleandro aleandro

A unique constraint can also involve more than one column. For example, if you are creating a list of members of a club, it could be confusing to have two members with the exact same full name. You could allow same last names. As long as the first names are different, there would be less or no confusion. Using a unique constraint, you can set a rule so that each combination of a first and last name would be different.

To visually create a unique constraint, display the Table Collection Editor and create the desired columns for a table. In the Members list, click the desired table. In the Properties list, click the ellipsis button of the Constraints field. In the Constraints Collection Editor, click Add -> Unique Constraint, give it a name, click the check box of the column that will hold unique values. Here is an example:

Unique Constraint

If the unique constraint will involve more than one column, click the check box of each. After providing the necessary information, click OK.

To support unique constraints, the System.Data namespace provides the UniqueConstraint class. Therefore, to programmatically create a unique constraint, you can declare a variable to type UniqueConstraint and initialize it with one of its many constructors. If you want to specify only the column that will hold unique values, you can use the following constructor:

public UniqueConstraint(DataColumn column);

This method expects the variable name of a column as argument. After creating the constraint variable, you can add it to the Constraints collection of the table. To support this, the ConstraintCollection class is equipped with a method named Add that comes in many versions. If you had created a UniqueContraint object and you want to add it, you can use the following syntax of the method:

public void Add(Constraint constraint);

This version expects a Constraint-derived object as argument. 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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        DataSet dsStudents;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.AutoIncrementStep = 5;

            colUsername = new DataColumn("Username", Type.GetType("System.String"));
            tblStudents = new DataTable("Student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            cnsUniqueUsername = new UniqueConstraint(colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}

If you create a unique constraint using the UniqueConstraint(DataColumn column) constructor, a default name would be assigned to it. If this is the first constraint, it would be named Constraint1 and the names would be incremental. If you want, you can provide your own name. To do this, you can use the following constructor of the UniqueConstraint class:

public UniqueConstraint(string name, DataColumn column);

The first argument is the name of the unique constraint. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
    colStudentID.AutoIncrementStep = 5;

    colUsername = new DataColumn("Username", Type.GetType("System.String"));
    tblStudents = new DataTable("Student");
    tblStudents.Columns.Add(colStudentID);
    tblStudents.Columns.Add(colUsername);

    cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
    tblStudents.Constraints.Add(cnsUniqueUsername);

    dsStudents = new DataSet("Students");
    dsStudents.Tables.Add(tblStudents);
}

If the unique constraint is a combination of columns, create them in an array before adding them.

The Primary Key

To create a scenario where data flows from one list to another, we saw that the first list must hold the records that would be supplied to other lists. In the first list, each record must be distinguishable from the others. One way you can take care of this is to create what is referred to as a primary key.

To visually create a primary key, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click the arrow of the PrimaryKey combo box and click the left button of the column. Here is an example:

Primary Key

Like a unique constraint, a primary key can involve more than one column. In this case, you would click the gray button of each of the columns that would be involved in the combination. After doing this, you can click Close.

In reality, a primary key is a unique constraint. Therefore, if you create a primary key as just described, the studio would configure it behind the scenes. Otherwise, you can explicitly create a primary key as a unique constraint. To do this, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click Constraints and click its ellipsis button. In the Constraints Collection Editor, click Add -> Unique Constraint. In the Unique Constraint dialog box, accept the default name or change it. In the Columns list, click the check box of the column that will be the primary key, and click the Primary Key check box. Here is an example:

Primary Key Unique Constraint

If the primary key involves more than one column, click the check box of each. After making the selections, click OK.

To assist you with programmatically creating a primary key, the UniqueConstraint class is equipped with the following constructor:

public UniqueConstraint(DataColumn column, bool isPrimaryKey);

In this case, the second argument is passed as true or false. If passed as true, the column specified as the first argument would be treated as the primary key. If you use this constructor, a default name would be given to the constraint. If you want to specify a name, you can use the following constructor:

public UniqueConstraint(
	string name,
	DataColumn column,
	bool isPrimaryKey
);

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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

            colGender = new DataColumn("colGender", Type.GetType("System.String"));

            tblGenders = new DataTable("Gender");
            tblGenders.Columns.Add(colGenderID);
            tblGenders.Columns.Add(colGender);

            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.AutoIncrementStep = 5;

            colUsername = new DataColumn("Username", Type.GetType("System.String"));

            tblStudents = new DataTable("Student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            PKGenderID = new UniqueConstraint("PKGenderID", colGenderID, true);
            tblGenders.Constraints.Add(PKGenderID);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}

Practical LearningPractical Learning: Creating a Primary Key

  1. While the RentalOrder table is still selected in the Members list, in the Properties list, click Constraints and click its button
  2. In the Constraints Collection Editor, click Add -> Unique Constraint
     
  3. Set the Name to PKRentalOrders
  4. Click the check box of RentalOrderID and click the Primary Key check box
     
  5. Click OK and click Close
  6. Click Columns and create the following columns:
     
    ColumnName (Name) Other Properties
    RentalOrderID colRentalOrderID  
    DateProcessed colDateProcessed  
    EmployeeID colEmplID DataType: System.Int32
    EmployeeName colEmployeeName  
    CustomerID colCustID DataType: System.Int32
    CustomerName colCustomerName  
    CustomerAddress colCustomerAddress  
    CustomerCity colCustomerCity  
    CustomerState colCustomerState  
    CustomerZIPCode colCustomerZIPCode  
    CarID colVehicleID DataType: System.Int32
    Make colCarMake  
    Model colCarModel  
    Year colCarYear DataType: System.UInt16
    Condition colCarCondition  
    TankLevel colTankLevel  
    MileageStart colMileageStart DataType: System.UInt32
    MileageEnd colMileageEnd DataType: System.UInt32
    RentStartDate colRentStartDate DataType: System.DateTime
    RendependDate colRendependDate DataType: System.DateTime
    Days colDays 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  
  7. Click Close
  8. While the RentalOrder table is selected, in the RentalOder Properties list, click PrimaryKey and click the arrow of its combo box.
    Notice the check box on RentalOrderID
     
  9. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Car
    (Name) tblCar
  10. Click Columns and click its button
  11. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    CarID colCarID AutoIncrement: True
    AutoIncrementSeed: 1
    TagNumber colTagNumber AllowDBNull: False
    Unique: True
    Make colMake  
    Model colModel  
    Year colYear DataType: System.UInt16
    Category colCategory  
    CDPlayer colCDPlayer DataType: System.Boolean
    DVDPlayer colDVDPlayer DataType: System.Boolean
    Available colAvailable DataType: System.Boolean
  12. Click Close
  13. While the Car table is selected, in the Properties list, click Constraints and its button
  14. In the Constraints Collection Editor, if Constraint1 is selected, click Edit (otherwise, click Add -> Unique Constraint)
  15. Set the Name to PKCars
  16. Click the check box of CarID and click the Primary Key check box:
     
  17. Click OK and click Close
  18. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Customer
    (Name) tblCustomer
  19. Click Columns and click its button
  20. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    CustomerID colCustomerID AutoIncrement: True
    AutoIncrementSeed: 1
    DrvLicNumber colDrvLicNumber AllowDBNull: False
    Unique: True
    FullName colFullName AllowDBNull: False
    Address colAddress  
    City colCity  
    State colState  
    ZIPCode colZIPCode  
  21. Click Close
  22. Click Constraints and click its button
  23. In the Constraints Collection Editor, if Constraint1 is selected, click Edit (otherwise, click Add -> Unique Constraint)
  24. Set the Name to PKCustomers
  25. Click the check box of CustomerID and click the Primary Key check box (clear any other check box)
  26. Click OK and click Close
  27. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Employee
    (Name) tblEmployee
  28. Click Columns and click its button
  29. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    EmployeeID colEmployeeID AutoIncrement: True
    AutoIncrementSeed: 1
    EmployeeNumber colEmployeeNumber AllowDBNull: False
    Unique: True
    FirstName colFirstName AllowDBNull: False
    LastName colLastName  
    FullName colEmplFullName  
    Title colTitle  
  30. Click Close
  31. Click Constraints and click its button
  32. In the Constraints Collection Editor, if Constraint1 is selected, click Edit (otherwise, click Add -> Unique Constraint)
  33. Set the Name to PKEmployees
  34. Click the check box of EmployeeID and click the Primary Key check box (clear any other check box)
  35. Click OK and click Close

The Foreign Key

For a parent list to supply its information to another list, the child list must have a column that would serve as a relay. This means that, in the child list, you must create a column that would correspond to the primary key of the parent table. This column of the child list is called a foreign key.

To visually create a foreign key, in the Tables Collection Editor, you can click the ellipsis button of the Constraints field. In the Constraints Collection Editor, you can click Add -> Foreign Key Constraint. In Foreign Key Constraint:

Here is an example:

Foreign Key

In the same way, you can create the other foreign keys for your table. The list of foreign keys would appear in the Members list of the Constraints Collection Editor.

To support foreign keys, the System.Data namespace provides a class named ForeignKeyConstraint. The ForeignKeyConstraint class is derived from the Constraint class. To programmatically create a foreign key, declare a variable of type ForeignKeyConstraint and initialize it with one of its six constructors. If you want to specify (only) the names of the primary key and the foreign key columns, you can use the following constructor:

public ForeignKeyConstraint(DataColumn parentColumn,
			    DataColumn childColumn);

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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataColumn colStudentGenderID;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        ForeignKeyConstraint FKGenderID;

        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

            colGender = new DataColumn("colGender", Type.GetType("System.String"));

            tblGenders = new DataTable("Gender");
            tblGenders.Columns.Add(colGenderID);
            tblGenders.Columns.Add(colGender);

            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.AutoIncrementStep = 5;

            colUsername = new DataColumn("Username", Type.GetType("System.String"));
            colStudentGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));

            tblStudents = new DataTable("Student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);
            tblStudents.Columns.Add(colStudentGenderID);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            PKGenderID = new UniqueConstraint("PKGenderID", colGenderID, true);
            tblGenders.Constraints.Add(PKGenderID);

            FKGenderID = new ForeignKeyConstraint(colGenderID, colStudentGenderID);
            tblStudents.Constraints.Add(FKGenderID);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);
        }
    }
}

Using Data Relationships

 

Introduction

A relational database is an application in which different tables work together so that information in one table can be made available to other tables. To make this possible, you start by creating the tables as we have done above. Each table must have a primary key. As we saw above, to make data from a parent table available to data from a child table, the child table must have a foreign key that would "represent" the information from the parent table. Once the tables and their keys have been created, you can link them.

Creating a Relationship

To visually create a relationship in a data set, first select the DataSet object. In the Properties window, click Relations and click its ellipsis button. In the Relations Collection Editor, to create a relationship, click Add. In the Relation dialog box, you can complete the text boxes and combo boxes using the same options as when creating a foreign key.

If you had previously created a(the) foreign key(s) in your table, the relationship(s) would automatically be created and configured so you do not have to recreate it(them).

To support relations in a database, the DataSet class is equipped with a property named Relations. The DataSet.Relations property is an object of type DataRelationCollection. The DataRelationCollection class is a collection of objects where each member is of type DataRelation. To create a relationship, declare a variable of type DataRelation and initialize it using one of its six constructors. To specify the primary key and the foreign key, you can use the following constructor:

public DataRelation(string relationName,
		    DataColumn parentColumn,
		    DataColumn childColumn)

The first argument is the name of the relationship. The second argument is the column name of the primary key. The last argument is the column name of the foreign key. After creating the relationship, you can add it to the DataSet.Relations property. To support this, the DataRelationCollection class is equipped with the Add() method that is provided in various versions. One of the versions uses the following syntax:

public void Add(DataRelation relation);

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 Exercise10
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataColumn colStudentGenderID;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        ForeignKeyConstraint FKGenderID;

        DataRelation relSchool;
        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

            colGender = new DataColumn("colGender", Type.GetType("System.String"));

            tblGenders = new DataTable("Gender");
            tblGenders.Columns.Add(colGenderID);
            tblGenders.Columns.Add(colGender);

            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.AutoIncrementStep = 5;

            colUsername = new DataColumn("Username", Type.GetType("System.String"));
            colStudentGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));

            tblStudents = new DataTable("Student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);
            tblStudents.Columns.Add(colStudentGenderID);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            PKGenderID = new UniqueConstraint("PKGenderID", colGenderID, true);
            tblGenders.Constraints.Add(PKGenderID);

            FKGenderID = new ForeignKeyConstraint(colGenderID, colStudentGenderID);
            tblStudents.Constraints.Add(FKGenderID);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);

            relSchool = new DataRelation("SchoolRelations", colGenderID, colStudentGenderID);
            dsStudents.Relations.Add(relSchool);
        }
    }
}

There are many other ways you can create a relationship.

Data Binding

Once a relationship exists between two tables, you can use that relationship to have the information flow from one list to another. To support this, the visual controls of the .NET Framework are equipped with various properties, including DataSource and DisplayMember.

The DataSource property specifies the name of the data set variable that holds the database. The DisplayMember property specifies the name of the column from the table that has the value to be displayed.

Practical LearningPractical Learning: Using the Relationships

  1. If necessary, display the OrderProcessing form.
    Double-click an unoccupied area of the form and change its Load event as follows:
     
    private void OrderProcessing_Load(object sender, EventArgs e)
    {
        string strFilename = @"C:\Bethesda Car Rental1\employees.xml";
    
        if (File.Exists(strFilename))
            dsRentalOrders.ReadXml(strFilename);
    
        strFilename = @"C:\Bethesda Car Rental1\customers.xml";
    
        if (File.Exists(strFilename))
            dsRentalOrders.ReadXml(strFilename);
    
        strFilename = @"C:\Bethesda Car Rental1\cars.xml";
    
        if (File.Exists(strFilename))
            dsRentalOrders.ReadXml(strFilename);
    }
  2. Return to the Order Processing form and click the Employee # combo box
  3. In the Properties window, change the following characteristics:
    DataSource: dsRentalOrders
    DisplayMember: Employee.EmployeeNumber
  4. On the form, double-click the Employee # combo box and implement the event as follows:
     
    private void cbxEmployeeID_SelectedIndexChanged(object sender, EventArgs e)
    {
        foreach (DataRow row in tblEmployee.Rows)
        {
            if (cbxEmployeeID.Text == row["EmployeeNumber"].ToString())
                txtEmployeeName.Text = row["FullName"].ToString();
        }
    }
  5. On the form, click the Driver's Lic # combo box
  6. In the Properties window, change the following characteristics:
    DataSource: dsRentalOrders
    DisplayMember: Customer.DrvLicNumber
  7. On the form, double-click the Driver's Lic # combo box and implement the event as follows:
     
    private void cbxCustomerID_SelectedIndexChanged(object sender, EventArgs e)
    {
        foreach (DataRow row in tblCustomer.Rows)
        {
            if (cbxCustomerID.Text == row["DrvLicNumber"].ToString())
            {
                txtCustomerName.Text = row["FullName"].ToString();
                txtCustomerAddress.Text = row["Address"].ToString();
                txtCustomerCity.Text = row["City"].ToString();
                cbxCustomerStates.Text = row["State"].ToString();
                txtCustomerZIPCode.Text = row["ZIPCode"].ToString();
            }
        }
    }
  8. On the form, click the Tag Number combo box
  9. In the Properties window, change the following characteristics:
    DataSource: dsRentalOrders
    DisplayMember: Car.TagNumber
  10. On the form, double-click the Tag Number combo box and implement its ValueChanged event as follows:
     
    private void cbxCarID_SelectedIndexChanged(object sender, EventArgs e)
    {
        foreach (DataRow row in tblCar.Rows)
        {
            if (cbxCarID.Text == row["TagNumber"].ToString())
            {
                txtMake.Text = row["Make"].ToString();
                txtModel.Text = row["Model"].ToString();
                txtCarYear.Text = row["Year"].ToString();
            }
        }
    }
  11. Return to the OrderProcessing form
  12. From the Data section of the Toolbox, click DataSet and click the form
  13. Click Untyped Dataset and click OK
  14. In the Properties window, change the following characteristics:
    DataSetName: OrderProcessing
    (Name):dsOrderProcessing
  15. Click the ellipsis button of the Tables field
  16. In the Tables Collection Editor, click Add and change the characteristics as follows:
    TableName: RentalOrder
    (Name): tblRental
  17. Click the ellipsis button of the Columns field
  18. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Other Properties
    ReceiptNumber colReceiptNumber AutoIncrement: True
    AutoIncrementSeed: 1001
    DateProcessed colProcessedDate  
    OrderStatus colStatus  
    EmployeeNumber colEmplNumber  
    EmployeeName colEmplName  
    CustomerDrvLicNbr colDrvLicNbr  
    CustomerName colCustName  
    CustomerAddress colCustAddress  
    CustomerCity colCustCity  
    CustomerState colCustState  
    CustomerZIPCode colCustZIPCode  
    CarTagNumber colCarTagNumber  
    CarMake colMake  
    CarModel colVehicleModel  
    CarYear colVehicleYear  
    CarCondition colCondition  
    CarTankLevel colCarTankLevel  
    MileageStart colCarMileageStart  
    MileageEnd colCarMileageEnd  
    RentStartDate colStartDate  
    RendependDate colEndDate  
    TotalDays colTotalDays  
    RateApplied colAppliedRate  
    SubTotal colTotalSub  
    TaxRate colRateTax  
    TaxAmount colAmountTax  
    OrderTotal colTotalOrder  
  19. Click Close and click Close
  20. On the form, double-click the Save button and implement its event as follows:
     
    private void btnSave_Click(object sender, EventArgs e)
    {
        // Don't save this rental order if we don't
        // know who processed it
        if (cbxEmployeeID.SelectedIndex < 0)
        {
            MessageBox.Show("You must select the employee number or " +
                            "the clerk who processed this order.");
            return;
        }
    
        // Don't save this rental order if we don't
        // know who is renting the car
        if (cbxCustomerID.SelectedIndex < 0)
        {
            MessageBox.Show("You must select the driver's license number " +
                            "of the customer who is renting the car");
            return;
        }
    
        // Don't save the rental order if we don't
        // know what car is being rented
        if (cbxCarID.SelectedIndex < 0)
        {
            MessageBox.Show("You must select the tag number " +
                            "of the car that is being rented");
            return;
        }
    
        // This variable will allow us to know whether 
        // we are only updating a rental order
        bool found = false;
        // This is the XML file that holds the rental orders
        string strFilename = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
        // This is the stream that holds the file
        FileStream bcrStream = new FileStream(strFilename,
                                              FileMode.OpenOrCreate,
                                              FileAccess.ReadWrite,
                                              FileShare.ReadWrite);
    
        // Check the rental orders
        foreach (DataRow rowRentalOrder in tblRental.Rows)
        {
            // Find out if there is already a rental
            // order with the current receipt number
            if (rowRentalOrder["ReceiptNumber"].ToString() ==
                txtReceiptNumber.Text)
            {
                // Since the rental order was found, make note
                found = true;
    
                // Get ready to update the rental order
                rowRentalOrder["DateProcessed"] =
                            dtpDateProcessed.Value.ToString("d");
                rowRentalOrder["EmployeeNumber"] = cbxEmployeeID.Text;
                rowRentalOrder["EmployeeName"] = txtEmployeeName.Text;
                rowRentalOrder["CustomerDrvLicNbr"] = cbxCustomerID.Text;
                rowRentalOrder["CustomerName"] = txtCustomerName.Text;
                rowRentalOrder["CustomerAddress"] = txtCustomerAddress.Text;
                rowRentalOrder["CustomerCity"] = txtCustomerCity.Text;
                rowRentalOrder["CustomerState"] = cbxCustomerStates.Text;
                rowRentalOrder["CustomerZIPCode"] = txtCustomerZIPCode.Text;
                rowRentalOrder["CarTagNumber"] = cbxCarID.Text;
                rowRentalOrder["CarMake"] = txtMake.Text;
                rowRentalOrder["CarModel"] = txtModel.Text;
                rowRentalOrder["CarYear"] = txtCarYear.Text;
                rowRentalOrder["CarCondition"] = cbxCarConditions.Text;
                rowRentalOrder["CarTankLevel"] = cbxTankLevels.Text;
                rowRentalOrder["MileageStart"] = txtMileageStart.Text;
                rowRentalOrder["MileageEnd"] = txtMileageEnd.Text;
                rowRentalOrder["RentStartDate"] =
                            dtpStartDate.Value.ToString("d");
                rowRentalOrder["RendependDate"] =
                            dtpEndDate.Value.ToString("d");
                rowRentalOrder["TotalDays"] = txtDays.Text;
                rowRentalOrder["RateApplied"] = txtRateApplied.Text;
                rowRentalOrder["SubTotal"] = txtSubTotal.Text;
                rowRentalOrder["TaxRate"] = txtTaxRate.Text;
                rowRentalOrder["TaxAmount"] = txtTaxAmount.Text;
                rowRentalOrder["OrderTotal"] = txtOrderTotal.Text;
                rowRentalOrder["OrderStatus"] = cbxOrderStatus.Text;
                // STOP!!!
                break;
            }
        }
    
        // If the receipt number was not found,
        // then prepare to create a new rental order
        if (found == false)
        {
            DataRow rowRentalOrder = tblRental.NewRow();
    
            rowRentalOrder["DateProcessed"] =
                        dtpDateProcessed.Value.ToString("d");
            rowRentalOrder["EmployeeNumber"] = cbxEmployeeID.Text;
            rowRentalOrder["EmployeeName"] = txtEmployeeName.Text;
            rowRentalOrder["CustomerDrvLicNbr"] = cbxCustomerID.Text;
            rowRentalOrder["CustomerName"] = txtCustomerName.Text;
            rowRentalOrder["CustomerAddress"] = txtCustomerAddress.Text;
            rowRentalOrder["CustomerCity"] = txtCustomerCity.Text;
            rowRentalOrder["CustomerState"] = cbxCustomerStates.Text;
            rowRentalOrder["CustomerZIPCode"] = txtCustomerZIPCode.Text;
            rowRentalOrder["CarTagNumber"] = cbxCarID.Text;
            rowRentalOrder["CarMake"] = txtMake.Text;
            rowRentalOrder["CarModel"] = txtModel.Text;
            rowRentalOrder["CarYear"] = txtCarYear.Text;
            rowRentalOrder["CarCondition"] = cbxCarConditions.Text;
            rowRentalOrder["CarTankLevel"] = cbxTankLevels.Text;
            rowRentalOrder["MileageStart"] = txtMileageStart.Text;
            rowRentalOrder["MileageEnd"] = txtMileageEnd.Text;
            rowRentalOrder["RentStartDate"] = 
    		dtpStartDate.Value.ToString("d");
            rowRentalOrder["RendependDate"] = dtpEndDate.Value.ToString("d");
            rowRentalOrder["TotalDays"] = txtDays.Text;
            rowRentalOrder["RateApplied"] = txtRateApplied.Text;
            rowRentalOrder["SubTotal"] = txtSubTotal.Text;
            rowRentalOrder["TaxRate"] = txtTaxRate.Text;
            rowRentalOrder["TaxAmount"] = txtTaxAmount.Text;
            rowRentalOrder["OrderTotal"] = txtOrderTotal.Text;
            rowRentalOrder["OrderStatus"] = cbxOrderStatus.Text;
            tblRental.Rows.Add(rowRentalOrder);
        }
    
        // Now that the rental order is ready, ...
        try
        {
            // ... save it
            dsOrderProcessing.WriteXml(bcrStream);
        }
        finally
        {
            bcrStream.Close();
        }
    }
  21. Return to the form and double-click the Open button
  22. Implement its event as follows:
     
    private void btnOpen_Click(object sender, EventArgs e)
    {
        bool found = false;
    
        try
        {
            string strFilename = @"C:\Bethesda Car Rental1\RentalOrders.xml";
    
            if (File.Exists(strFilename))
            {
                dsOrderProcessing.ReadXml(strFilename);
    
                foreach (DataRow rowRentalOrder in tblRental.Rows)
                {
                    if (rowRentalOrder["ReceiptNumber"].ToString() == txtReceiptNumber.Text)
                    {
                        found = true;
                        dtpDateProcessed.Value = 
    			DateTime.Parse(rowRentalOrder["DateProcessed"].ToString());
                        cbxEmployeeID.Text = rowRentalOrder["EmployeeNumber"].ToString();
                        txtEmployeeName.Text = rowRentalOrder["EmployeeName"].ToString();
                        cbxCustomerID.Text = rowRentalOrder["CustomerDrvLicNbr"].ToString();
                        txtCustomerName.Text = rowRentalOrder["CustomerName"].ToString();
                        txtCustomerAddress.Text = 
    			rowRentalOrder["CustomerAddress"].ToString();
                        txtCustomerCity.Text = rowRentalOrder["CustomerCity"].ToString();
                        cbxCustomerStates.Text = rowRentalOrder["CustomerState"].ToString();
                        txtCustomerZIPCode.Text = 
    			rowRentalOrder["CustomerZIPCode"].ToString();
                        cbxCarID.Text = rowRentalOrder["CarTagNumber"].ToString();
                        txtMake.Text = rowRentalOrder["CarMake"].ToString();
                        txtModel.Text = rowRentalOrder["CarModel"].ToString();
                        txtCarYear.Text = rowRentalOrder["CarYear"].ToString();
                        cbxCarConditions.Text = rowRentalOrder["CarCondition"].ToString();
                        cbxTankLevels.Text = rowRentalOrder["CarTankLevel"].ToString();
                        txtMileageStart.Text = rowRentalOrder["MileageStart"].ToString();
                        txtMileageEnd.Text = rowRentalOrder["MileageEnd"].ToString();
                        dtpStartDate.Value = 
    			DateTime.Parse(rowRentalOrder["RentStartDate"].ToString());
                        dtpEndDate.Value = 
    			DateTime.Parse(rowRentalOrder["RendependDate"].ToString());
                        txtDays.Text = rowRentalOrder["TotalDays"].ToString();
                        txtRateApplied.Text = rowRentalOrder["RateApplied"].ToString();
                        txtSubTotal.Text = rowRentalOrder["SubTotal"].ToString();
                        txtTaxRate.Text = rowRentalOrder["TaxRate"].ToString();
                        txtTaxAmount.Text = rowRentalOrder["TaxAmount"].ToString();
                        txtOrderTotal.Text = rowRentalOrder["OrderTotal"].ToString();
                        cbxOrderStatus.Text = rowRentalOrder["OrderStatus"].ToString();
                        break;
                    }
                }
            }
            else
                MessageBox.Show("There is no cleaning order to open");
        }
        catch (ConstraintException)
        {
        }
    
        if (found == false)
            MessageBox.Show("There is no rental order with that receipt number");
    }
  23. Execute the application

Previous Copyright © 2008-2009 Yevol.com Next