Home

Application: Watts A Loan
 

Introduction

Watts A Loan is a fictitious application used by a company that gives various types of loans to regular consumers. Loans types include car purchase, furniture purchase, or cash, etc.

The purpose of this application is to explore the characteristics of the DataSet class, including its objects: tables, columns, and records. This lessons also illustrates how to assist a user with data entry. To follow this lesson, you should be familiar with:

Practical Learning: Assisting With Data Entry
  1. Start Microsoft Visual C# and create a Windows Application named WattsALoan1
  2. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  3. Set the Name to LoanAllocations and click Add
  4. From the Data section of the Toolbox, click DataSet and click the form
  5. Select the Untyped Dataset radio button and click OK
  6. In the Properties window, change the following characteristics:
    DataSetName: dsLoanAllocations
    (Name): LoanAllocations
  7. Click Tables and click its ellipsis button
  8. To create a new table, click Add and change the properties as follows:
    TableName: Loan
    (Name): tblLoan
  9. Click Columns and click its ellipsis button
  10. Click Add 10 times and change the properties as follows:
     
    ColumnName (Name) DataType DefaultValue
    DateAllocated colDateAllocated System.DateTime  
    LoanNumber colLoanNumber System.String  
    PreparedBy colPreparedBy System.String  
    PreparedFor colPreparedFor System.String  
    Principal colPrincipal System.Double 0.00
    InterestRate colInterestRate System.Double 8.75
    Periods colPeriods System.Double 36
    InterestEarned colInterestEarned System.Double  
    FutureValue colFutureValue System.Double  
    MonthlyPayment colMonthlyPayment System.Double  
  11. In the Members list, click LoanNumber

Using Expressions

This section shows how to specify an expression on a column.

Practical Learning: Creating Expressions on Columns
  1. In the Members list, click FutureValue
  2. In the Properties list, click Expression and type Principal + InterestEarned
  3. In the same way, change the data types of the following columns:
     
    Member Expression
    InterestEarned Principal * (InterestRate / 100) * (Periods / 12)
    FutureValue Principal + InterestEarned
    MonthlyPayment FutureValue / Periods
  4. In the Members list, click DateAllocated
  5. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  6. In the Members list, click LoanNumber
  7. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  8. Click Close and click Close
  9. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  10. Set the Name to Employees and click Add
  11. From the Data section of the Toolbox, click DataSet and click the form
  12. Select the Untyped Dataset radio button and click OK
  13. In the Properties window, change the following characteristics:
    DataSetName: dsEmployees
    (Name): Employees
  14. Click Tables and click its ellipsis button
  15. To create a new table, click Add and change the properties as follows:
    TableName: Employee
    (Name): tblEmployee
  16. Click Columns and click its ellipsis button
  17. Click Add 5 times and change the properties as follows:
     
    AllowDBNull ColumnName DefaultValue DataType Expression Unique (Name)
    False EmployeeNumber       True colEmployeeNumber
      FirstName         colFirstName
    False LastName         colLastName
      FullName     LastName + ', ' + FirstName   colFullName
      Title         colTitle
      HourlySalary 8.75 System.Double     colHourlySalary
  18. Click Close and click Close
  19. Design the form as follows:
     
    Watts a Loan
     
    Control Text Name Other Properties
    DataGridView   dgvEmployees DataSource: dsEmployees
    DataMember: Employee
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    EmployeeNumber Empl # 65
    FirstName First Name 65
    LastName Last Name 65
    FullName Full Name 120
    Title   110
    HourlySalary Salary/hr 60
  20. 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 WattsALoan4
{
    public partial class Employees : Form
    {
        public Employees()
        {
            InitializeComponent();
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            string strFilename = "employees.xml";

            if (File.Exists(strFilename))
                dsEmployees.ReadXml(strFilename);
        }
    }
}
  21. Return to the form and click an unoccupied area of its body
  22. In the Properties window, click the Events button and double-click FormClosing
  23. Implement the event as follows:
      
    private void Employees_FormClosing(object sender, FormClosingEventArgs e)
{
    dsEmployees.WriteXml("employees.xml");
}
  24. Return to the form and double-click the Close button
  25. Implement the event as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  26. Return to the form
  27. Under the form, right-click dsEmployees and click Copy
  28. Display the LoanAllocations form
  29. Right-click it and click Paste
  30. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  31. Set the Name to Customers and click Add
  32. From the Data section of the Toolbox, click DataSet and click the form
  33. Select the Untyped Dataset radio button and click OK
  34. In the Properties window, change the following characteristics:
    DataSetName: dsCustomers
    (Name): Customers
  35. Click Tables and click its ellipsis button
  36. To create a new table, click Add and change the properties as follows:
    TableName: Customer
    (Name): tblCustomer
  37. Click Columns and click its ellipsis button
  38. Click Add 5 times and change the properties as follows:
     
    AllowDBNull ColumnName Unique (Name)
    False AccountNumber True colAccountNumber
    False FullName   colFullName
      EmailAddress   colEmailAddress
      PhoneNumber   colPhoneNumber
  39. Click Close and click Close
  40. Design the form as follows:
     
    Watts A Loan: Customers
     
    Control Text Name Other Properties
    DataGridView   dgvCustomers DataSource: dsCustomers
    DataMember: Customer
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    AccountNumber Account # 65
    FullName Full Name 120
    EmailAddress Email Address 120
    PhoneNumber Phone # 90
  41. 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 WattsALoan4
{
    public partial class Customers : Form
    {
        public Customers()
        {
            InitializeComponent();
        }

        private void Customers_Load(object sender, EventArgs e)
        {
            string strFilename = "customers.xml";

            if (File.Exists(strFilename))
                dsCustomers.ReadXml(strFilename);
        }
    }
}
  42. Return to the form and click an unoccupied area of its body
  43. In the Properties window, click the Events button and double-click FormClosing
  44. Implement the event as follows:
      
    private void Customers_FormClosing(object sender, FormClosingEventArgs e)
{
    dsCustomers.WriteXml("customers.xml");
}
  45. Return to the form and double-click the Close button
  46. Implement the event as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  47. Return to the form
  48. Under the form, right-click dsCustomers and click Copy
  49. Display the LoanAllocations form
  50. Right-click it and click Paste
  51. Design the form as follows:
     
    Watts A Loan
     
    Control Text Name Other Properties
    DataGridView   dgvCustomers DataSource: dsCustomers
    DataMember: Customer
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width DefaultCellStyle -> Format  
    DateAllocated Date Allocated   Date Time  
    LoanNumber Loan # 65    
    PreparedBy Prepared By 110   ColumnType: DataGridViewComboBoxColumn
    DataSource: dsEmployees
    DisplayMember: Employee.EmployeeNumber
    PreparedFor Prepared For 110   ColumnType: DataGridViewComboBoxColumn
    DataSource: dsCustomers
    DisplayMember: Customer.AccountNumber
    Principal   70 Currency  
    InterestRate Rate (%) 65 Numeric  
    Periods Prd (Months) 65 Numeric  
    InterestEarned Interest Earned   Currency  
    FutureValue Future Value   Currency  
    MonthlyPayment Pmt/Month   Currency  
  52. 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 WattsALoan4
{
    public partial class LoanAllocations : Form
    {
        public LoanAllocations()
        {
            InitializeComponent();
        }

        private void LoanAllocations_Load(object sender, EventArgs e)
        {
            string strFilename = "employees.xml";

            if (File.Exists(strFilename))
                dsEmployees.ReadXml(strFilename);

            strFilename = "customers.xml";

            if (File.Exists(strFilename))
                dsCustomers.ReadXml(strFilename);

            strFilename = "loans.xml";

            if (File.Exists(strFilename))
                dsLoanAllocations.ReadXml(strFilename);
        }
    }
}
  53. Return to the form and click an unoccupied area of its body
  54. In the Properties window, click the Events button and double-click FormClosing
  55. Implement the event as follows:
      
    private void LoanAllocations_FormClosing(object sender, FormClosingEventArgs e)
{
    dsLoanAllocations.WriteXml("loans.xml");
}
  56. Return to the form and double-click the Close button
  57. Implement the event as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  58. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  59. Set the Name to Payments and click Add
  60. Display the Employees form
  61. Right-click dsEmployees and click Copy
  62. Display the Payments form
  63. Right-click it and click Paste
  64. Display the LoanAllocations form
  65. Right-click dsLoanAllocations and click Copy
  66. Display the Payments form
  67. Right-click it and click Paste
  68. From the Data section of the Toolbox, click DataSet and click the Payments form
  69. Select the Untyped Dataset radio button and click OK
  70. In the Properties window, change the following characteristics:
    DataSetName: dsPayments
    (Name): Payments
  71. Click Tables and click its ellipsis button
  72. To create a new table, click Add and change the properties as follows:
    TableName: Payment
    (Name): tblPayment
  73. Click Columns and click its ellipsis button
  74. Click Add 6 times and change the properties as follows:
     
    AllowDBNull ColumnName DataType DefaultValue Expression Unique (Name)
    False PaymentNumber       True colPaymentNumber
    False PaymentDate System.DateTime       colPaymentDate
    False ReceivedBy         colReceivedBy
    False PaymentFor         colPaymentFor
    False PaymentAmount System.Double 0.00     colPaymentAmount
      Balance System.Double 0.00     colBalance
  75. Click Close and click Close
  76. Design the form as follows:
     
    Watts A Loan: Payments
     
    Control Text Name Other Properties
    DataGridView   dgvPayments DataSource: dsPayments
    DataMember: Payment
    Button Loans... btnLoans  
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width DefaultCellStyle -> Format  
    PaymentNumber Pmt # 55    
    PaymentDate Pmt Date 70 Date Time  
    ReceivedBy Received By     ColumnType: 
    DataGridViewComboBoxColumn
    DataSource: dsEmployees
    DisplayMember: 
    Employee.EmployeeNumber
    PaymentFor Payment For     ColumnType: 
    DataGridViewComboBoxColumn
    DataSource: dsLoanAllocations
    DisplayMember:
    Loan.LoanNumber
    PaymentAmount Pmt Amt 70 Currency  
    Balance   80 Currency  
  77. 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 WattsALoan4
{
    public partial class Payments : Form
    {
        public Payments()
        {
            InitializeComponent();
        }

        private void Payments_Load(object sender, EventArgs e)
        {
            string strFilename = "loans.xml";

            if (File.Exists(strFilename))
                dsLoanAllocations.ReadXml(strFilename);

            strFilename = "payments.xml";

            if (File.Exists(strFilename))
                dsPayments.ReadXml(strFilename);
        }
    }
}
  78. Return to the form and click an unoccupied area of its body
  79. In the Properties window, click the Events button and double-click FormClosing
  80. Implement the event as follows:
      
    private void Payments_FormClosing(object sender, FormClosingEventArgs e)
{
    dsPayments.WriteXml("payments.xml");
}
  81. Return to the form and double-click the Loans button
  82. Implement the event as follows:
      
    private void btnLoans_Click(object sender, EventArgs e)
{
    LoanAllocations frmLoans = new LoanAllocations();
    frmLoans.Show();
}
  83. Return to the form and double-click the Close button
  84. Implement the event as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  85. In the Solution Explorer, right-click Form1.cs and click Rename
  86. Type Central.cs and press Enter twice to display the form
  87. Design the form as follows:
     
    Control Text Name
    Button ... btnPayments
    Label Loan Payments lblPayments
    Button ... btnAllocations
    Label Loan Allocations lblAllocations
    Button ... btnCustomers
    Label Customers lblCustomers
    Button ... btnEmployees
    Label Employees lblEmployees
    Button Close btnClose
  88. Double-click the top button and implement its event as follows:
      
    private void btnPayments_Click(object sender, EventArgs e)
{
    Payments frmPayments = new Payments();
    frmPayments.ShowDialog();
}
  89. Return to the form and double-click the Loan Payments label
  90. Implement its event as follows:
      
    private void lblPayments_Click(object sender, EventArgs e)
{
    Payments frmPayments = new Payments();
    frmPayments.ShowDialog();
}
  91. Return to the form and double-click the second button from top
  92. Implement its event as follows:
      
    private void btnLoanAllocations_Click(object sender, EventArgs e)
{
    LoanAllocations frmLoans = new LoanAllocations();
    frmLoans.ShowDialog();
}
  93. Return to the form and double-click the Loan Allocations label
  94. Implement its event as follows:
      
    private void lblLoanAllocations_Click(object sender, EventArgs e)
{
    LoanAllocations frmLoans = new LoanAllocations();
    frmLoans.ShowDialog();
}
  95. Return to the form and double-click the third button from top
  96. Implement its event as follows:
      
    private void btnCustomers_Click(object sender, EventArgs e)
{
    Customers frmClients = new Customers();
    frmClients.ShowDialog();
}
  97. Return to the form and double-click the Customers label
  98. Implement its event as follows:
      
    private void lblCustomers_Click(object sender, EventArgs e)
{
    Customers frmBorrowers = new Customers();
    frmBorrowers.ShowDialog();
}
  99. Return to the form and double-click the fourth button from top
  100. Implement its event as follows:
      
    private void btnEmployees_Click(object sender, EventArgs e)
{
    Employees frmStaff = new Employees();
    frmStaff.ShowDialog();
}
  101. Return to the form and double-click the Employees label
  102. Implement its event as follows:
      
    private void lblEmployees_Click(object sender, EventArgs e)
{
    Employees frmClerks = new Employees();
    frmClerks.ShowDialog();
}
  103. Return to the form and double-click the Close button from top
  104. Implement its event as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  105. Execute the application
  106. Click the Employees label and create the following records:
     
    Employee # First Name Last Name Title Salary/hr
    7973-45 Bernard Wallow Account Manager 24.85
    2497-94 Justine Bogley Sales Representative 12.75
    2930-75 Nestor Rosenblatt Sales Representative 14.25
     
  107. Close the form
  108. Click the Customers label and create the following records:
     
    Account # Full Name Email Address Phone Number
    937-497 Joan Fairbanks fairbie1288@hotmail.com (301) 937-5888
    293-759 Ernie Lipps ernie.rowdie@comcast.net (703) 506-0000
    502-850 Christopher Owens owenchris@yahoo.com (202) 529-6100
    520-840 Ann Rowdy rowdiant@msn.com (301) 855-2090
    602-475 Sarah Thompson lolitta448@yahoo.com (301) 870-7454
     
    Watts A Loan
  109. Close the form
  110. Click the Loan Allocations label and create the following records:
     
    Date Allocated Loan # Prepared By Prepared For Principal Rate (%) Prd (Months)
    08/18/06 52-9739-5 2497-94 937-497 6500 16.25  
    10/26/2006 20-5804-8 7973-45 602-475 3260    
    02/06/07 77-3907-2 2497-94 502-850 25605 12.50 60
    03/20/07 92-7495-4 2930-75 293-759 14800   48
     
    Watts a Loan
  111. Close the form
  112. Click the Loan Payments label and create the following records:
     
    Pmt # Pmt Date Received By Payment For Pmt Amt Balance
    1001 10/25/06 2497-94 52-9739-5 268.58 9400.17
    1002 11/30/06 2930-75 52-9739-5 268.58 9131.59
    1003 12/24/2006 7973-45 20-5804-8 114.33 4001.42
    1004 12/28/06 2497-94 52-9739-5 268.58 8863.01
    1005 01/26/07 2497-94 20-5804-8 114.33 3887.09
    1006 01/31/07 2930-75 52-9739-5 268.58 8594.43
    1007 02/20/07 2497-94 20-5804-8 114.33 3772.76
    1008 03/02/07 2930-75 52-9739-5 268.58 8325.85
    1009 03/25/2007 2930-75 20-5804-8 114.33 3658.43
    1010 04/25/07 7973-45 92-7495-4 416.25 19563.75
    1011 04/28/07 2497-94 77-3907-2 693.47 40914.66
    1012 04/28/07 7973-45 20-5804-8 114.33 3544.10
    1013 05/01/07 7973-45 52-9739-5 268.58 8057.27
    1014 05/26/07 2497-94 77-3907-2 693.47 40221.19
     
  113. Close the forms and return to your programming environment
 
Home Copyright © 2008-2009 C# Key