Data Set Example Application: Watts A Loan

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

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

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

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:
     

    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:
     

    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