Home

Data Set Example Application: Watts A Loan

     

Introduction

 

This is a sample application for a fictitious company that makes small loans to individuals and companies (loans for people who need cash or money to purchase something like a musical instrument or furniture).

To follow the instructions in this application, you should be familiar with data sets of the .NET Framework.

Practical Learning: Creating the Application

  1. Start Microsoft Visual Studio 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)
    DateAllocated colDateAllocated
    LoanNumber colLoanNumber
    PreparedBy colPreparedBy
    PreparedFor colPreparedFor
    Principal colPrincipal
    InterestRate colInterestRate
    Periods colPeriods
    InterestEarned colInterestEarned
    FutureValue colFutureValue
    MonthlyPayment colMonthlyPayment
  11. In the Members list, click LoanNumber
  12. In the Properties list, double-click Unique to change its value to True

Practical Learning: Applying Data Types on Columns

  1. In the Members list, click Principal
  2. In the Properties list, click DataType, click the arrow of its combo box and select System.Double
  3. In the same way, change the data types of the following columns:
     
    Member DataType
    DateAllocated System.DateTime
    LoanNumber System.String
    PreparedBy System.String
    PreparedFor System.String
    Principal System.Double
    InterestRate System.Double
    Periods System.Double
    InterestEarned System.Double
    FutureValue System.Double
    MonthlyPayment System.Double
  4. In the Members list, click Principal
  5. In the Properties list, click DefaultValue and delete <DBNull>
  6. Type 0.00
  7. In the same way, change the default values of the following columns:
     
    Member DefaultValue
    Principal 0.00
    InterestRate 8.75
    Periods 36
  8. In the Members list, click FutureValue
  9. In the Properties list, click Expression and type Principal + InterestEarned
  10. 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

Practical Learning: Nullifying a Column

  1. In the Members list, click DateAllocated
  2. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  3. In the Members list, click LoanNumber
  4. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  5. Click Close and click Close
  6. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  7. Set the Name to Employees and click Add
  8. From the Data section of the Toolbox, click DataSet and click the form
  9. Select the Untyped Dataset radio button and click OK
  10. In the Properties window, change the following characteristics:
    DataSetName: dsEmployees
    (Name): Employees
  11. Click Tables and click its ellipsis button
  12. To create a new table, click Add and change the properties as follows:
    TableName: Employee
    (Name): tblEmployee
  13. Click Columns and click its ellipsis button
  14. 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
  15. Click Close and click Close
  16. 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
    EmployeeNumber Empl # 65
    FirstName First Name 65
    LastName Last Name 65
    FullName Full Name 120
    Title   110
    HourlySalary Salary/hr 60
  17. 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);
            }
        }
    }
  18. Return to the form and click an unoccupied area of its body
  19. In the Properties window, click the Events button and double-click FormClosing
  20. Implement the event as follows:
    private void Employees_FormClosing(object sender, FormClosingEventArgs e)
    {
        dsEmployees.WriteXml("employees.xml");
    }
  21. Return to the form and double-click the Close button
  22. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  23. Return to the form
  24. Under the form, right-click dsEmployees and click Copy
  25. Display the LoanAllocations form
  26. Right-click it and click Paste
  27. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  28. Set the Name to Customers and click Add
  29. From the Data section of the Toolbox, click DataSet and click the form
  30. Select the Untyped Dataset radio button and click OK
  31. In the Properties window, change the following characteristics:
    DataSetName: dsCustomers
    (Name): Customers
  32. Click Tables and click its ellipsis button
  33. To create a new table, click Add and change the properties as follows:
    TableName: Customer
    (Name): tblCustomer
  34. Click Columns and click its ellipsis button
  35. 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
  36. Click Close and click Close
  37. 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
  38. 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);
            }
        }
    }
  39. Return to the form and click an unoccupied area of its body
  40. In the Properties window, click the Events button and double-click FormClosing
  41. Implement the event as follows:
    private void Customers_FormClosing(object sender, FormClosingEventArgs e)
    {
        dsCustomers.WriteXml("customers.xml");
    }
  42. Return to the form and double-click the Close button
  43. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  44. Return to the form
  45. Under the form, right-click dsCustomers and click Copy
  46. Display the LoanAllocations form
  47. Right-click it and click Paste
  48. Design the form as follows:
     
     
    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  
  49. 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);
            }
        }
    }
  50. Return to the form and click an unoccupied area of its body
  51. In the Properties window, click the Events button and double-click FormClosing
  52. Implement the event as follows:
    private void LoanAllocations_FormClosing(object sender, FormClosingEventArgs e)
    {
        dsLoanAllocations.WriteXml("loans.xml");
    }
  53. Return to the form and double-click the Close button
  54. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  55. To create a new form, in the Solution Explorer, right-click WattsALoan4 -> Add -> Windows Forms...
  56. Set the Name to Payments and click Add
  57. Display the Employees form
  58. Right-click dsEmployees and click Copy
  59. Display the Payments form
  60. Right-click it and click Paste
  61. Display the LoanAllocations form
  62. Right-click dsLoanAllocations and click Copy
  63. Display the Payments form
  64. Right-click it and click Paste
  65. From the Data section of the Toolbox, click DataSet and click the Payments form
  66. Select the Untyped Dataset radio button and click OK
  67. In the Properties window, change the following characteristics:
    DataSetName: dsPayments
    (Name): Payments
  68. Click Tables and click its ellipsis button
  69. To create a new table, click Add and change the properties as follows:
    TableName: Payment
    (Name): tblPayment
  70. Click Columns and click its ellipsis button
  71. 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
  72. Click Close and click Close
  73. 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  
  74. 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);
            }
        }
    }
  75. Return to the form and click an unoccupied area of its body
  76. In the Properties window, click the Events button and double-click FormClosing
  77. Implement the event as follows:
    private void Payments_FormClosing(object sender, FormClosingEventArgs e)
    {
        dsPayments.WriteXml("payments.xml");
    }
  78. Return to the form and double-click the Loans button
  79. Implement the event as follows:
    private void btnLoans_Click(object sender, EventArgs e)
    {
        LoanAllocations frmLoans = new LoanAllocations();
        frmLoans.Show();
    }
  80. Return to the form and double-click the Close button
  81. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  82. In the Solution Explorer, right-click Form1.cs and click Rename
  83. Type Central.cs and press Enter twice to display the form
  84. 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
  85. 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();
    }
  86. Return to the form and double-click the Loan Payments label
  87. Implement its event as follows:
    private void lblPayments_Click(object sender, EventArgs e)
    {
        Payments frmPayments = new Payments();
        frmPayments.ShowDialog();
    }
  88. Return to the form and double-click the second button from top
  89. Implement its event as follows:
    private void btnLoanAllocations_Click(object sender, EventArgs e)
    {
        LoanAllocations frmLoans = new LoanAllocations();
        frmLoans.ShowDialog();
    }
  90. Return to the form and double-click the Loan Allocations label
  91. Implement its event as follows:
    private void lblLoanAllocations_Click(object sender, EventArgs e)
    {
        LoanAllocations frmLoans = new LoanAllocations();
        frmLoans.ShowDialog();
    }
  92. Return to the form and double-click the third button from top
  93. Implement its event as follows:
    private void btnCustomers_Click(object sender, EventArgs e)
    {
        Customers frmClients = new Customers();
        frmClients.ShowDialog();
    }
  94. Return to the form and double-click the Customers label
  95. Implement its event as follows:
    private void lblCustomers_Click(object sender, EventArgs e)
    {
        Customers frmBorrowers = new Customers();
        frmBorrowers.ShowDialog();
    }
  96. Return to the form and double-click the fourth button from top
  97. Implement its event as follows:
    private void btnEmployees_Click(object sender, EventArgs e)
    {
        Employees frmStaff = new Employees();
        frmStaff.ShowDialog();
    }
  98. Return to the form and double-click the Employees label
  99. Implement its event as follows:
    private void lblEmployees_Click(object sender, EventArgs e)
    {
        Employees frmClerks = new Employees();
        frmClerks.ShowDialog();
    }
  100. Return to the form and double-click the Close button from top
  101. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  102. Execute the application
  103. 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
     
  104. Close the form
  105. 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
     
  106. Close the form
  107. 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
     
  108. Close the form
  109. 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
     
  110. Close the forms and return to your programming environment
 
 

Home Copyright © 2010-2014, FunctionX Home