Home

Example Application: Yugo National Bank
 

Introduction

This is an introduction to bank applications. It is a Microsoft SQL Server database. The interface objects made of forms are designed using Microsoft Visual Studio. This means that, to follow the instructions in these lessons, you should have Microsoft SQL Server 2005 (the application was not tested on the smaller version of the database server). You should also have Microsoft Visual Studio 2005 Professional (the Express versions were not tested but they may as well work).
 

This section introduces the application. It instructs your to create the database and the regular objects that the users will use (forms).

Practical LearningPractical Learning: Introducing Views
  1. Start Microsoft Visual C# and create a new Windows Application named YugoNationalBank1
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type Central.cs and press Enter
  4. Double-click the middle of the form and implement the Load 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.Data.SqlClient;

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

        void CreateDatabase()
        {
            string strAction = "";
            SqlConnection cnnYNB = null;
            SqlCommand cmdYNB = null;

            using (cnnYNB = new SqlConnection("Data Source=(local); " +
                              "Integrated Security='SSPI';"))
            {
                strAction = "IF EXISTS ( " +
                        "SELECT name " +
                        "FROM sys.databases " +
                        "WHERE name = N'YugoNationalBank1') " +
                        "DROP DATABASE YugoNationalBank1; " +
                        "CREATE DATABASE YugoNationalBank1";

                cmdYNB = new SqlCommand(strAction, cnnYNB);

                cnnYNB.Open();
                cmdYNB.ExecuteNonQuery();

                MessageBox.Show("A database named YugoNationalBank1 " +
                        "has been created.", "Yugo National Bank");
            }

            using (cnnYNB = new SqlConnection("Data Source=(local); " +
                              "Database='YugoNationalBank1'; " +
                              "Integrated Security='SSPI';"))
            {
                strAction = "CREATE TABLE dbo.AccountTypes( " +
                        "AccountTypeID int Identity(1,1) NOT NULL, " +
                        "AccountType nvarchar(40) NOT NULL, " +
                        "Notes text NULL, " +
                        "CONSTRAINT PK_AccountTypes PRIMARY " +
                        "    KEY (AccountTypeID));";
                cmdYNB = new SqlCommand(strAction, cnnYNB);

                cnnYNB.Open();
                cmdYNB.ExecuteNonQuery();
                MessageBox.Show("A table named AccountTypes " +
                        "has been added to the database.", "Yugo National Bank");
            }

            using (cnnYNB = new SqlConnection("Data Source=(local); " +
                              "Database='YugoNationalBank1'; " +
                              "Integrated Security='SSPI';"))
            {
                strAction = "CREATE TABLE dbo.Employees( " +
                        "EmployeeNumber nchar(10) NOT NULL UNIQUE, FirstName nvarchar(32), " +
                        "LastName nvarchar(32) NOT NULL, " +
                        "Title nvarchar(50), CanCreateNewAccount bit, " +
                        "HourlySalary nvarchar(50), Username nvarchar(20), " +
                        "Password nvarchar(20), EmailAddress nvarchar(100), " +
                        "Notes text, " +
                        "CONSTRAINT PK_Employees PRIMARY KEY (EmployeeNumber));";
                cmdYNB = new SqlCommand(strAction, cnnYNB);

                cnnYNB.Open();
                cmdYNB.ExecuteNonQuery();
                MessageBox.Show("A table named Employees has " +
                        "been added to the database.", "Yugo National Bank");
            }

            using (cnnYNB = new SqlConnection("Data Source=(local); " +
                              "Database='YugoNationalBank1'; " +
                              "Integrated Security='SSPI';"))
            {
                strAction = "CREATE TABLE dbo.Customers( " +
                        "AccountNumber nchar(12) NOT NULL UNIQUE, " +
                        "EmployeeNumber nchar(10) Constraint FK_Employee " +
                        "    References Employees(EmployeeNumber), " +
                        "DateCreated nvarchar(50), " +
                        "AccountTypeID int Constraint FK_TypeOfAccount " +
                        "   References AccountTypes(AccountTypeID), " +
                        "CustomerName nvarchar(50) NOT NULL, " +
                        "Address nvarchar(100), City nvarchar(50), " +
                        "State nvarchar(50), ZIPCode nvarchar(50), " +
                        "AccountStatus nvarchar(50), Username nvarchar(20), " +
                        "Password nvarchar(20), EmailAddress nvarchar(100), " +
                        "Notes text, " +
                        "CONSTRAINT PK_Customers PRIMARY KEY (AccountNumber));";
                cmdYNB = new SqlCommand(strAction, cnnYNB);

                cnnYNB.Open();
                cmdYNB.ExecuteNonQuery();
                MessageBox.Show("A table named Customers has " +
                        "been added to the database.", "Yugo National Bank");
            }

            using (cnnYNB = new SqlConnection("Data Source=(local); " +
                              "Database='YugoNationalBank1'; " +
                              "Integrated Security='SSPI';"))
            {
                strAction = "CREATE TABLE dbo.AccountsTransactions( " +
                       "AccountTransactionID int identity(1, 1) NOT NULL, " +
                        "EmployeeNumber nchar(10) Constraint FK_Clerk " +
                        "    References Employees(EmployeeNumber), " +
                        "AccountNumber nchar(12) Constraint FK_Depositor " +
                        "    References Customers(AccountNumber) NOT NULL, " +
                        "TransactionDate nvarchar(50), TransactionType nvarchar(50), " +
                        "CurrencyType nvarchar(50), DepositAmount nvarchar(50), " +
                        "WithdrawalAmount nvarchar(50), ChargeAmount nvarchar(50), " +
                        "ChargeReason nvarchar(50), Notes text, " +
                        "CONSTRAINT PK_AccountTransactions PRIMARY KEY " +
                        "    (AccountTransactionID));";
                cmdYNB = new SqlCommand(strAction, cnnYNB);

                cnnYNB.Open();
                cmdYNB.ExecuteNonQuery();
                MessageBox.Show("A table named AccountTransactions " +
                        "has been added to the database.", "Yugo National Bank");
            }

            using (SqlConnection cnnTimesheets =
                    new SqlConnection("Data Source=(local);" +
                              "Database='YugoNationalBank1';" +
                              "Integrated Security=SSPI;"))
            {
                string strTimesheets = "CREATE TABLE dbo.Timesheets ( " +
                "TimesheetCode nchar(15) NOT NULL UNIQUE, Week1Monday nvarchar(6), " +
                "EmployeeNumber nchar(10) Constraint FK_EmployeeTimeSheet " +
                        "    References Employees(EmployeeNumber), " +
                "StartDate nvarchar(50), " +
                "Week1Tuesday nvarchar(6), Week1Wednesday nvarchar(6), " +
                "Week1Thursday nvarchar(6), Week1Friday nvarchar(6), " +
                "Week1Saturday nvarchar(6), Week1Sunday nvarchar(6), " +
                "Week2Monday nvarchar(6), Week2Tuesday nvarchar(6), " +
                "Week2Wednesday nvarchar(6), Week2Thursday nvarchar(6), " +
                "Week2Friday nvarchar(6), Week2Saturday nvarchar(6), " +
                "Week2Sunday nvarchar(6), Notes text, " +
                "CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetCode));";

                SqlCommand cmdTimesheets =
            new SqlCommand(strTimesheets, cnnTimesheets);
                cnnTimesheets.Open();
                cmdTimesheets.ExecuteNonQuery();
                MessageBox.Show("A table named Timesheets has been created.", "Yugo National Bank");
            }
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            CreateDatabase();
        }
    }
}
  5. Execute the application to create the database
  6. Close the form and return to your programming environment
  7. To create a data source, on the main menu, click Data -> Add New Data Source...
  8. In the first page of the wizard, make sure Database is selected and click Next
  9. In the combo box
    1. If you see a YugoNationalBank1, select it
    2. If you do not have YugoNationalBank1, click New Connection... In the Server combo box, select the server or type (local). In the Select Or Enter A Database Name combo box, select YugoNationalBank1. Click Test Connection. Click OK twice. In the Data Source Configuration Wizard, make sure the new connection is selected and click Next. Change the Connection String to csYugoNationalBank and click Next. Click the check box of Tables. Change the DataSet Name to dsYugoNationalBank
       
      Data Source
  10. Click Finish
  11. To create a new form, on the main menu, click Project -> Add Windows Form...
  12. Set the Name to AccountTypes and click Add
  13. From the Data Sources window, drag the AccountTypes node and drop it on the form
  14. Design the form as follows:
     
    Yugo National Bank - AccountTypes 
     
    Control Text Name Other Properties 
    DataGridView   dgvProperties Anchor: Top, Bottom, Left, Right
    Button Close btnClose Anchor: Bottom, Right 
  15. Double-click the Close button and implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  16. Access the Central form, add a button and change its properties as follows:
    (Name): btnAccountTypes
    Text: Account Types...
  17. Double-click the Account Types button and implement its event as follows:
     
    private void Central_Load(object s}er, EventArgs e)
{
    // CreateDatabase();
}

private void btnAccountTypes_Click(object s}er, EventArgs e)
{
    AccountTypes types = new AccountTypes();
    types.ShowDialog();
} 
  18. Execute the application and open the Account Types form
  19. Create the following records:
     
    AccountType 
    Saving
    Checking
    Certificate of Deposit
     
    Yugo National Bank - Account Types 
  20. Close the forms and return to your programming environment
  21. To create a new form, on the main menu, click Project -> Add Windows Form...
  22. Set the Name to Employees and click Add
  23. In the Data Sources window, click Employees and click the arrow on its right side to drop the combo box
  24. Select Details
  25. Drag the Employees node and drop it on the form
  26. Design the form as follows:
     
    Yugo National Bank - AccountTypes
     
  27. Double-click the Close button and implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  28. Access the Central form, add a button and change its properties as follows:
    (Name): btnEmployees
    Text: Employees...
  29. Double-click the Account Types button and implement its event as follows:
     
    private void btnEmployees_Click(object s}er, EventArgs e)
{
    Employees staff = new Employees();
    staff.ShowDialog();
}
  30. Execute the application and open the Employees form
  31. Create the following records:
     
     
    Yugo National Bank - Account Types 
  32. Close the forms and return to your programming environment
  33. To create a new form, on the main menu, click Project -> Add Windows Form...
  34. Set the Name to Timesheet and click Add
  35. Design the form as follows:
     
    Time Sheet
    Control Text Name Other Properties
    Label Employee #:    
    MaskedTextBox   txtEmployeeNumber Mask: 00000
    Label . lblEmployeeName  
    Label Start Date:    
    DateTimePicker   dtpStartDate  
    Label End Date:    
    Label . lblEndDate  
    Label Mon    
    Label Tue    
    Label Wed    
    Label Thu    
    Label Fri    
    Label Sat    
    Label Sun    
    Label Week 1:    
    TextBox 0.00 txtWeek1Monday TextAlign: Right
    TextBox 0.00 txtWeek1Tuesday TextAlign: Right
    TextBox 0.00 txtWeek1Wednesday TextAlign: Right
    TextBox 0.00 txtWeek1Thursday TextAlign: Right
    TextBox 0.00 txtWeek1Friday TextAlign: Right
    TextBox 0.00 txtWeek1Saturday TextAlign: Right
    TextBox 0.00 txtWeek1Sunday TextAlign: Right
    Label Week 2:    
    TextBox 0.00 txtWeek2Monday TextAlign: Right
    TextBox 0.00 txtWeek2Tuesday TextAlign: Right
    TextBox 0.00 txtWeek2Wednesday TextAlign: Right
    TextBox 0.00 txtWeek2Thursday TextAlign: Right
    TextBox 0.00 txtWeek2Friday TextAlign: Right
    TextBox 0.00 txtWeek2Saturday TextAlign: Right
    TextBox 0.00 txtWeek2Sunday TextAlign: Right
    Label Notes    
    TextBox   txtNotes Multiline: true
    Button Submit btnSubmit  
    Button Reset btnReset  
    Button Close btnClose  
  36. Double-click the middle of the form and implement the event as follows:
  37. Make the following changes:
      
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace YugoNationalBank1g
{
    public partial class Timesheet : Form
    {
        int EmployeeID;
        bool bNewRecord;
        bool ValidTimesheet;
        string strTimesheetCode;

        public Timesheet()
        {
            InitializeComponent();
        }

        private void Timesheet_Load(object s}er, EventArgs e)
        {
            EmployeeID = 0;
            bNewRecord = true;
            ValidTimesheet = false;
            strTimesheetCode = "";
        }
    }
}
  38. Return to the form, click the EmployeeNumber text box and, on the Properties window, click the Events button
  39. In the Events section, double-click Leave and implement the even as follows:
      
    private void txtEmployeeNumber_Leave(object s}er, EventArgs e)
{
    if (this.txtEmployeeNumber.Text == "")
    {
        ValidTimesheet = false;
        return;
    }

    string strSelect = "SELECT * FROM Employees " +
                       "WHERE EmployeeNumber = '" +
                       txtEmployeeNumber.Text + "';";

    SqlConnection conDatabase =
         new SqlConnection("Data Source=(local); " +
                           "Database='YugoNationalBank1';" +
                           "Integrated Security=true");
    SqlCommand cmdDatabase = new SqlCommand(strSelect, conDatabase);

    DataSet dsEmployees = new DataSet();
    SqlDataAdapter sda = new SqlDataAdapter();

            sda.SelectCommand = cmdDatabase;
            sda.Fill(dsEmployees);

            try
            {
                DataRow recEmployee = dsEmployees.Tables[0].Rows[0];

                if (recEmployee.IsNull("EmployeeNumber"))
                {
                    ValidTimesheet = false;
                    throw new System.IndexOutOfRangeException(
			"Bad Employee Number!");
                }
                else
                {
                    ValidTimesheet = true;
                    EmployeeID = (int)recEmployee["EmployeeID"];
                    string strFullName =
                        (string)recEmployee["FirstName"] +
                            " " + (string)recEmployee["LastName"];
                    lblEmployeeName.Text = "Welcome " + strFullName;
                }
            }
            catch (IndexOutOfRangeException)
            {
                MessageBox.Show("There is no employee with that number!");
                ValidTimesheet = false;
                lblEmployeeName.Text = "";
                txtEmployeeNumber.Text = "";
            }

            dtpStartDate.Value = DateTime.Today;

            txtWeek1Monday.Text = "0.00";
            txtWeek1Tuesday.Text = "0.00";
            txtWeek1Wednesday.Text = "0.00";
            txtWeek1Thursday.Text = "0.00";
            txtWeek1Friday.Text = "0.00";
            txtWeek1Saturday.Text = "0.00";
            txtWeek1Sunday.Text = "0.00";

            txtWeek2Monday.Text = "0.00";
            txtWeek2Tuesday.Text = "0.00";
            txtWeek2Wednesday.Text = "0.00";
            txtWeek2Thursday.Text = "0.00";
            txtWeek2Friday.Text = "0.00";
            txtWeek2Saturday.Text = "0.00";
            txtWeek2Sunday.Text = "0.00";

            conDatabase.Close();
        }
    }
}
  40. Return to the form and double-click the Reset button
  41. Implement its event as follows:
      
    private void btnReset_Click(object s}er, EventArgs e)
{
    txtWeek1Monday.Text = "0.00";
    txtWeek1Tuesday.Text = "0.00";
    txtWeek1Wednesday.Text = "0.00";
    txtWeek1Thursday.Text = "0.00";
    txtWeek1Friday.Text = "0.00";
    txtWeek1Saturday.Text = "0.00";
    txtWeek1Sunday.Text = "0.00";

    txtWeek2Monday.Text = "0.00";
    txtWeek2Tuesday.Text = "0.00";
    txtWeek2Wednesday.Text = "0.00";
    txtWeek2Thursday.Text = "0.00";
    txtWeek2Friday.Text = "0.00";
    txtWeek2Saturday.Text = "0.00";
    txtWeek2Sunday.Text = "0.00";

    bNewRecord = true;
}
  42. Return to the Timesheet form and click the Start Date control
     

    To implement the electronic time, we will use two pieces of information are required: an employee's number and a starting period. After an employee has opened a time sheet:

    1. The employee must first provide an employee number, which we will check in the Employees table. If the employee provides a valid employee number, we can continue with the time sheet. If the employee number is invalid, we will let the user know and we cannot continue with the time sheet
    2. After the employee has provided a valid employee number, we will request the starting period. After entering a (valid) date, we will check the time. If there is a record that holds both the employee number and the start date, this means that the employee had previously worked on a time sheet and we will open that existing time sheet.

    After the the employee or contractor has entered a valid employee number and a start date, we will create a number called a time sheet code, represented in the TimeSheet as the TimeSheetCode column. This number is created as follows:

    0000000000000

    The first 5 digits represent the employee's number. The second 4 digits represent the year of the start date. The next 2 digits represent the month, and the last 2 digits represent the day. This number must be unique so that there would not be a duplicate number throughout the time sheet.

    To make sure the value of the TimeSheetCode is unique for each record, after the employee has provided a valid employee number and a start date, we will create the time sheet code and check if that number exists in the TimeSheet table already:

    • If that number exists already, this means that the employee has previously worked on that time sheet and he or she simply wants to verify or update it. We will then open the time values for that record and let the user view or change it
    • If there is no record with the specified time sheet code, we will conclude that the employee is working on a new time sheet
  43. In the Events section of the Properties window, double-click CloseUP and implement the event as follows:
      
    private void dtpStartDate_CloseUp(object s}er, EventArgs e)
        {
            lblEndDate.Text = dtpStartDate.Value.AddDays(14).ToString();

            if (txtEmployeeNumber.Text.Equals(""))
            {
                ValidTimesheet = false;
                return;
            }

            string strMonth;
            string strDay;
            int iMonth;
            int iDay;
            DateTime dteStart;

            dteStart = dtpStartDate.Value;
            iMonth = dteStart.Month;
            iDay = dteStart.Day;

            if (iMonth < 10)
                strMonth = dteStart.Year + "0" + iMonth.ToString();
            else
                strMonth = dteStart.Year + iMonth.ToString();

            if (iDay < 10)
                strDay = strMonth + "0" + iDay.ToString();
            else
                strDay = strMonth + iDay.ToString();
           
            strTimesheetCode = txtEmployeeNumber.Text + strDay;

            MessageBox.Show(strTimesheetCode);

            SqlConnection conTimeSheet = null;
            string strSQL =
            String.Concat("SELECT * FROM dbo.Timesheets WHERE TimeSheetCode = '",
                     strTimesheetCode, "';");

            conTimeSheet =
                 new SqlConnection("Data Source=(local); " +
                                   "Database='YugoNationalBank1';" +
                                   "Integrated Security=true");
            SqlCommand cmdTimeSheet = new SqlCommand(strSQL, conTimeSheet);

            DataSet dsTimeSheet = new DataSet("TimeSheetSet");
            SqlDataAdapter sdaTimeSheet = new SqlDataAdapter();
            sdaTimeSheet.SelectCommand = cmdTimeSheet;
            sdaTimeSheet.Fill(dsTimeSheet);

            conTimeSheet.Close();

            try
            {
                DataRow recTimeSheet = dsTimeSheet.Tables[0].Rows[0];
                strTimesheetCode = (string)(recTimeSheet["TimeSheetCode"]);

                if (recTimeSheet.IsNull("TimeSheetCode"))
                {
                    bNewRecord = true;
                    throw new System.IndexOutOfRangeException(
                    "No TimeSheet with that number exists!");
                }
                else
                {
                    txtWeek1Monday.Text = (string)(recTimeSheet["Week1Monday"]);
                    txtWeek1Tuesday.Text = (string)(recTimeSheet["Week1Tuesday"]);
                    txtWeek1Wednesday.Text = 
			(string)(recTimeSheet["Week1Wednesday"]);
                    txtWeek1Thursday.Text = 
			(string)(recTimeSheet["Week1Thursday"]);
                    txtWeek1Friday.Text = (string)(recTimeSheet["Week1Friday"]);
                    txtWeek1Saturday.Text = 
			(string)(recTimeSheet["Week1Saturday"]);
                    txtWeek1Sunday.Text = (string)(recTimeSheet["Week1Sunday"]);

                    txtWeek2Monday.Text = (string)(recTimeSheet["Week2Monday"]);
                    txtWeek2Tuesday.Text = (string)(recTimeSheet["Week2Tuesday"]);
                    txtWeek2Wednesday.Text = 
			(string)(recTimeSheet["Week2Wednesday"]);
                    txtWeek2Thursday.Text = 
			(string)(recTimeSheet["Week2Thursday"]);
                    txtWeek2Friday.Text = (string)(recTimeSheet["Week2Friday"]);
                    txtWeek2Saturday.Text = 
			(string)(recTimeSheet["Week2Saturday"]);
                    txtWeek2Sunday.Text = (string)(recTimeSheet["Week2Sunday"]);

                    bNewRecord = false;
                }
            }
            catch (IndexOutOfRangeException)
            {
                btnReset_Click(s}er, e);
            }
        }
  44. Access the Central form, add a button and change its properties as follows:
    (Name): btnTimesheet
    Text: Employee's Time Sheet...
  45. Double-click the Account Types button and implement its event as follows:
     
    private void btnTimesheet_Click(object s}er, EventArgs e)
{
    Timesheet sheet = new Timesheet();
    sheet.ShowDialog();
}
  46. Save all

Introduction to Views

The primary motivation for creating this application was to illustrate the use fulness of database views.

Practical LearningPractical Learning: Visually Creating a View
  1. In the Server Explorer, expand the YugoNationalBank1 node if necessary.
    Right-click Views and click Add New View
  2. In the Add Table dialog box, click Employees, click Add, and click Close
  3. In the Diagram section of the view, click the check box of EmployeeNumber
  4. In the Criteria section, click the empty box under EmployeeNumber and type 
    LastName + ', ' + FirstName
  5. Set its Alias to EmployeeName
  6. In the Diagram section, click the check box of CanCreateNewAccount
     
    View
  7. Close the view
  8. When asked whether you want to save it, click Yes
  9. In the Choose Name dialog box, set the name to EmployeeIdentification and click OK
  10. In the Data Sources window, right-click dsYugoNationalBank and click Configure DataSet With Wizard...
  11. Click the check box of Views
     
    Data Source
  12. Click Finish
  13. To create a new form, on the main menu, click Project -> Add Windows Form...
  14. Set the Name to Customers and click Add
  15. In the Data Sources window, click Customers and click the arrow on its right side to drop the combo box
  16. Select Details
  17. Drag the Customers node and drop it on the form
  18. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource bsCustomers
    customersTableAdapter taCustomers
    customersBindingNavigator bnCustomers
  19. Once again, from the Data Sources window, drag EmployeeIdentification and drop it on the form
  20. While the data grid view is still selected, press Delete to remove it
  21. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name Filter 
    employeeIdentificationBindingSource bsEmployeeIdentification CanCreateNewAccount = True
    employeeIdentificationTableAdapter taEmployeeIdentification  
  22. Once again, from the Data Sources window, drag AccountTypes and drop it on the form
  23. While the data grid view is still selected, press Delete to remove it
  24. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    accountTypesBindingSource bsAccountTypes
    accountTypesTableAdapter taAccountTypes
  25. On the form, click the text box on the right side of Employee ID and press Delete
  26. On the form, click the text box on the right side of Date Created and press Delete
  27. On the form, click the text box on the right side of Account Type ID and press Delete
  28. On the form, click the text box on the right side of Account Number and press Delete
  29. On the form, click the text box on the right side of Account Status and press Delete
  30. Design the form as follows:
     
    Yugo National Bank - Customers Form Design
    New Control Text Name Other Properties 
    ComboBox   cbxEmployeeID DropDownStyle: DropDownList
    DataSource: bsAccountManagers
    DisplayMember: EmployeeName
    ValueMember: EmployeeID
    (DataBindings) -> Selected Value: bsCustomers - EmployeeID
    ComboBox    cbxAccountTypeID  DropDownStyle: DropDownList
    DataSource: bsAccountTypes
    DisplayMember: AccountType
    ValueMember: AccountTypeID
    (DataBindings) -> Selected Value: bsCustomers - AccountTypeID
    MaskedTextBox   txtAccountNumber Mask: 00-000000-00
    (DataBindings) -> Text: bsCustomers - AccountNumber
    ComboBox   cbxAccountStatus DropDownStyle: DropDownList
    (DataBindings) -> Text: bsCustomers - DateCreated
    Items:
    Active
    Closed
    Suspended
    Button Close btnClose Anchor: Bottom, Right 
     
     
  31. Double-click the Close button and implement its event as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  32. Access the Central form, add a button and change its properties as follows:
    (Name): btnCustomers
    Text: Customers...
  33. Double-click the Account Types button and implement its event as follows:
     
    private void btnCustomers_Click(object s}er, EventArgs e)
{
    Customers clients = new Customers();
    clients.ShowDialog();
} 
  34. Execute the application and open the Employees form
  35. Create the following records:
  36. Close the forms and return to your programming environment
  37. To create a new form, on the main menu, click Project -> Add Windows Form...
  38. Set the Name to NewDeposit and click Add
  39. Design the form as follows:
     
    Yugo National Bank - Deposits Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   dtpTransactionDate  
    Label Processed By:    
    MaskedTextBox   txtEmployeeNumber Mask: 00000
    TextBox   txtEmployeeName  
    Label Processed For:    
    MaskedTextBox   txtAccountNumber Mask: 00-000000-00
    TextBox   txtCustomerName  
    Label Currency Type:    
    ComboBox   cbxCurrencyTypes DropDownStyle: DropDownList
    Items:
    Cash
    Check
    Money Order
    Label Amount Deposited:    
    TextBox   txtAmount TextAlign: Right
    Label  Notes    
    TextBox    txtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit btnSubmit  
    Button Close btnClose  
  40. Double-click the middle of the form and implement the event as follows:
  41. Make the following changes:
      
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace YugoNationalBank1
{
    public partial class NewDeposit : Form
    {
	int EmployeeID;
	int CustomerID;

	public NewDeposit()
	{
	    InitializeComponent();
	}

	private void NewDeposit_Load(object s}er, EventArgs e)
	{
	    EmployeeID = 0;
	    CustomerID = 0;
	}

    }
} 
  42. On the form, click the EmployeeNumber text box and, on the Properties window, click the Events button
  43. In the Events section, double-click Leave and implement the even as follows:
      
    private void txtEmployeeNumber_Leave(object s}er, EventArgs e)
{
    if (txtEmployeeNumber.Text.Length == 0)
    {
	MessageBox.Show("You must specify the employee number " +
			"of the clerk who is processing the deposit.");
	return;
    }
    else
    {
	using (SqlConnection cnnYNB =
	    new SqlConnection("Data Source=(local);" +
			      "Database='YugoNationalBank1';" +
			      "Integrated Security=SSPI;"))
	{
	   string strYNB = "SELECT EmployeeID, FirstName, LastName " +
			   "FROM Employees WHERE EmployeeNumber = '" +
			   txtEmployeeNumber.Text + "';";
	    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);

	    cnnYNB.Open();

	    SqlDataReader rdrEmployees = cmdYNB.ExecuteReader();

	    while (rdrEmployees.Read())
	    {
		EmployeeID = int.Parse(rdrEmployees.GetSqlInt32(0).ToString());
	  	txtEmployeeName.Text = rdrEmployees.GetString(1) + " " +
				       rdrEmployees.GetString(2);
	    }

	    if (EmployeeID == 0)
	    {
		MessageBox.Show("The employee number you entered " +
				"is not recognized in our database.");
		txtEmployeeNumber.Text = "";
	    }
	}
    }
}
  44. Return to the form, click the AccountNumber text box and, in the Events section of the Properties window, double-click Leave
  45. Implement the even as follows:
      
    private void txtAccountNumber_Leave(object s}er, EventArgs e)
{
    if( txtAccountNumber.Text.Length == 0)
    {
	MessageBox.Show("You must specify the account number " +
			"of the customer whose deposit you are entering.");
	return;
    }
    else
    {
	using (SqlConnection cnnYNB =
	    new SqlConnection("Data Source=(local);" +
			      "Database='YugoNationalBank1';" +
			      "Integrated Security=SSPI;"))
	{
	    string strYNB = "SELECT CustomerID, CustomerName FROM " +
			    "Customers WHERE AccountNumber = '" +
			    txtAccountNumber.Text + "';";
	    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);
	    SqlDataAdapter daYNB = new SqlDataAdapter();

	    daYNB.SelectCommand = cmdYNB;
	    DataSet dsCustomers = new DataSet("CustomersSet");
	    daYNB.Fill(dsCustomers);

	    cnnYNB.Open();

	    foreach (DataRow rowCustomer in dsCustomers.Tables[0].Rows)
	    {
		CustomerID = int.Parse(rowCustomer["CustomerID"].ToString());
		txtCustomerName.Text = rowCustomer["CustomerName"].ToString();
		break;
	    }

	    if (CustomerID == 0)
	    {
		MessageBox.Show("The account number you entered " +
				"is not recognized in our database.");
		txtAccountNumber.Text = "";
	    }
	}
    }
}
  46. Return to the form and double-click the Submit button
  47. Implement the even as follows:
      
    private void btnSubmit_Click(object s}er, EventArgs e)
{
    DateTime dteTransaction = DateTime.Today;
    string strCurrencyType = "Unknown";
    double Amount = 0.00;

    if( EmployeeID == 0 )
    {
	MessageBox.Show("You must specify the employee number " +
			"of the clerk who is processing the deposit.");
    	return;
    }

    if( CustomerID == 0)
    {
	MessageBox.Show("You must enter an account number " +
			"for the new customer.");
	return;
    }

    strCurrencyType = cbxCurrencyTypes.Text;

    try
    {
	Amount = double.Parse(txtAmount.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Amount.");
    }

    using (SqlConnection cnnYNB =
	new SqlConnection("Data Source=(local);" +
			  "Database='YugoNationalBank1';" +
			  "Integrated Security=SSPI;"))
    {
	string strEmployees = "INSERT INTO AccountsTransactions(" +
			      "EmployeeID, CustomerID, " +
			      "TransactionDate, TransactionType, " +
			      "CurrencyType, DepositAmount, Notes) " +
			      "VALUES('" + EmployeeID + "', '" +
			      CustomerID + "', '" +
			      dtpTransactionDate.Value.ToString("d") +
			"', 'Deposit', '" + cbxCurrencyTypes.Text +
			"', '" + Amount + "', '" + txtNotes.Text + "');";
	SqlCommand cmdEmployees = new SqlCommand(strEmployees, cnnYNB);

	cnnYNB.Open();
	cmdEmployees.ExecuteNonQuery();

	dtpTransactionDate.Value = DateTime.Today;
	txtEmployeeNumber.Text = "";
	txtEmployeeName.Text = "";
	txtAccountNumber.Text = "";
	txtCustomerName.Text = "";
	cbxCurrencyTypes.SelectedIndex = 0;
	txtAmount.Text = "0.00";
	txtNotes.Text = "";
    }
}
  48. Return to the form and double-click the Close button
  49. Implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  50. Access the Central form, add a button and change its properties as follows:
    (Name): btnNewDeposit
    Text: New Deposit...
  51. Double-click the Account Types button and implement its event as follows:
     
    private void btnNewDeposit_Click(object s}er, EventArgs e)
{
    NewDeposit deposit = new NewDeposit();
    deposit.ShowDialog();
}
  52. To create a new form, on the main menu, click Project -> Add Windows Form...
  53. Set the Name to NewWithdrawal and click Add
  54. Design the form as follows:
     
    Yugo National Bank - New Withdrawal Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   dtpTransactionDate  
    Label Processed By:    
    MaskedTextBox   txtEmployeeNumber Mask: 00000
    TextBox   txtEmployeeName  
    Label Processed For:    
    MaskedTextBox   txtAccountNumber Mask: 00-000000-00
    TextBox   txtCustomerName  
    Label Currency Type:    
    ComboBox   cbxCurrencyTypes DropDownStyle: DropDownList
    Items:
    Cash
    Check
    Money Order
    Label Amount Withdrawn:    
    TextBox   txtAmount TextAlign: Right
    Label  Notes    
    TextBox    txtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit btnSubmit  
    Button Close btnClose  
  55. Double-click the middle of the form and implement the event as follows:
  56. Make the following changes:
      
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace YugoNationalBank1
{
    public partial class NewDeposit : Form
    {
	int EmployeeID;
	int CustomerID;

	public NewDeposit()
	{
	    InitializeComponent();
	}

	private void NewDeposit_Load(object s}er, EventArgs e)
	{
	    EmployeeID = 0;
	    CustomerID = 0;
	}

    }
} 
  57. Return to the form, click the EmployeeNumber text box and, on the Properties window, click the Events button
  58. In the Events section, double-click Leave and implement the even as follows:
      
    private void txtEmployeeNumber_Leave(object s}er, EventArgs e)
{
    if (txtEmployeeNumber.Text.Length == 0)
    {
	MessageBox.Show("You must specify the employee number " +
			"of the clerk who is processing the transaction.");
	return;
    }
    else
    {
	using (SqlConnection cnnYNB =
	    new SqlConnection("Data Source=(local);" +
			      "Database='YugoNationalBank1';" +
			      "Integrated Security=SSPI;"))
	{
	   string strYNB = "SELECT EmployeeID, FirstName, LastName " +
			   "FROM Employees WHERE EmployeeNumber = '" +
			   txtEmployeeNumber.Text + "';";
	    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);

	    cnnYNB.Open();

	    SqlDataReader rdrEmployees = cmdYNB.ExecuteReader();

	    while (rdrEmployees.Read())
	    {
		EmployeeID = int.Parse(rdrEmployees.GetSqlInt32(0).ToString());
	  	txtEmployeeName.Text = rdrEmployees.GetString(1) + " " +
				       rdrEmployees.GetString(2);
	    }

	    if (EmployeeID == 0)
	    {
		MessageBox.Show("The employee number you entered " +
				"is not recognized in our database.");
		txtEmployeeNumber.Text = "";
	    }
	}
    }
}
  59. Return to the form, click the AccountNumber text box and, in the Events section of the Properties window, double-click Leave
  60. Implement the even as follows:
      
    private void txtAccountNumber_Leave(object s}er, EventArgs e)
{
    if( txtAccountNumber.Text.Length == 0)
    {
	MessageBox.Show("You must specify the account number " +
			"of the customer whose withdrawal you are processing.");
	return;
    }
    else
    {
	using (SqlConnection cnnYNB =
	    new SqlConnection("Data Source=(local);" +
			      "Database='YugoNationalBank1';" +
			      "Integrated Security=SSPI;"))
	{
	    string strYNB = "SELECT CustomerID, CustomerName FROM " +
			    "Customers WHERE AccountNumber = '" +
			    txtAccountNumber.Text + "';";
	    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);
	    SqlDataAdapter daYNB = new SqlDataAdapter();

	    daYNB.SelectCommand = cmdYNB;
	    DataSet dsCustomers = new DataSet("CustomersSet");
	    daYNB.Fill(dsCustomers);

	    cnnYNB.Open();

	    foreach (DataRow rowCustomer in dsCustomers.Tables[0].Rows)
	    {
		CustomerID = int.Parse(rowCustomer["CustomerID"].ToString());
		txtCustomerName.Text = rowCustomer["CustomerName"].ToString();
		break;
	    }

	    if (CustomerID == 0)
	    {
		MessageBox.Show("The account number you entered " +
				"is not recognized in our database.");
		txtAccountNumber.Text = "";
	    }
	}
    }
}
  61. Return to the form and double-click the Submit button
  62. Implement the even as follows:
      
    private void btnSubmit_Click(object s}er, EventArgs e)
{
    DateTime dteTransaction = DateTime.Today;
    string strCurrencyType = "Unknown";
    double Amount = 0.00;

    if (EmployeeID == 0)
    {
	MessageBox.Show("You must specify a valid employee number " +
			"of the clerk who is processing the withdrawal.");
	return;
    }

    if (CustomerID == 0)
    {
	MessageBox.Show("You must enter a valid account number " +
			"for the new customer.");
	return;
    }

    strCurrencyType = cbxCurrencyTypes.Text;

    try
    {
	Amount = double.Parse(txtAmount.Text);
    }
    catch (FormatException)
    {
 	MessageBox.Show("Invalid Amount.");
	return;
    }

    using (SqlConnection cnnYNB =
	new SqlConnection("Data Source=(local);" +
			  "Database='YugoNationalBank1';" +
			  "Integrated Security=SSPI;"))
    {
	string strEmployees = "INSERT INTO AccountsTransactions(" +
			      "EmployeeID, CustomerID, " +
			      "TransactionDate, TransactionType, " +
			      "CurrencyType, WithdrawalAmount, Notes) " +
			      "VALUES('" + EmployeeID + "', '" +
			      CustomerID + "', '" +
			      dtpTransactionDate.Value.ToString("d") +
			      "', 'Withdraw', '" + cbxCurrencyTypes.Text +
			"', '" + Amount + "', '" + txtNotes.Text + "');";
	SqlCommand cmdEmployees = new SqlCommand(strEmployees, cnnYNB);

	cnnYNB.Open();
	cmdEmployees.ExecuteNonQuery();

	dtpTransactionDate.Value = DateTime.Today;
	txtEmployeeNumber.Text = "";
	txtEmployeeName.Text = "";
	txtAccountNumber.Text = "";
	txtCustomerName.Text = "";
	cbxCurrencyTypes.SelectedIndex = 0;
	txtAmount.Text = "0.00";
	txtNotes.Text = "";
    }
} 
  63. Return to the form and double-click the Close button
  64. Implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  65. Access the Central form, add a button and change its properties as follows:
    (Name): btnNewWithdrawal
    Text: New Withdrawal...
  66. Double-click the Account Types button and implement its event as follows:
     
    private void btnNewWithdrawal_Click(object s}er, EventArgs e)
{
    NewWithdrawal withdraw = new NewWithdrawal();
    withdraw.ShowDialog();
}
  67. To create a new form, on the main menu, click Project -> Add Windows Form...
  68. Set the Name to NewCharge and click Add
  69. Design the form as follows:
     
    Yugo National Bank - New Withdrawal Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   dtpTransactionDate  
    Label Processed For:    
    MaskedTextBox   txtAccountNumber Mask: 00-000000-00
    TextBox   txtCustomerName  
    Label Charge Reason:    
    ComboBox   cbxChargeReason DropDownStyle: DropDownList
    Items:
    Overdraft
    Money Order
    Check Stopping
    Monthly Charge
    Label Amount Charged:    
    TextBox   txtAmount TextAlign: Right
    Label  Notes    
    TextBox    txtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit btnSubmit  
    Button Close btnClose  
  70. Double-click the middle of the form and implement the event as follows:
  71. Make the following changes:
      
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace YugoNationalBank1
{
    public partial class NewDeposit : Form
    {
	int CustomerID;

	public NewDeposit()
	{
	    InitializeComponent();
	}

	private void NewDeposit_Load(object s}er, EventArgs e)
	{
	    CustomerID = 0;
	}

    }
} 
  72. On the form, click the AccountNumber text box and, in the Events section of the Properties window, double-click Leave
  73. Implement the even as follows:
      
    private void txtAccountNumber_Leave(object s}er, EventArgs e)
{
    if( txtAccountNumber.Text.Length == 0)
    {
	MessageBox.Show("You must specify the account number " +
		"of the customer whose withdrawal you are processing.");
	return;
    }
    else
    {
	using (SqlConnection cnnYNB =
	    new SqlConnection("Data Source=(local);" +
			      "Database='YugoNationalBank1';" +
			      "Integrated Security=SSPI;"))
	{
	    string strYNB = "SELECT CustomerID, CustomerName FROM " +
			    "Customers WHERE AccountNumber = '" +
			    txtAccountNumber.Text + "';";
	    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);
	    SqlDataAdapter daYNB = new SqlDataAdapter();

	    daYNB.SelectCommand = cmdYNB;
	    DataSet dsCustomers = new DataSet("CustomersSet");
	    daYNB.Fill(dsCustomers);

	    cnnYNB.Open();

	    foreach (DataRow rowCustomer in dsCustomers.Tables[0].Rows)
	    {
		CustomerID = int.Parse(rowCustomer["CustomerID"].ToString());
		txtCustomerName.Text = rowCustomer["CustomerName"].ToString();
		break;
	    }

	    if (CustomerID == 0)
	    {
		MessageBox.Show("The account number you entered " +
				"is not recognized in our database.");
		txtAccountNumber.Text = "";
	    }
	}
    }
}
  74. Return to the form and double-click the Submit button
  75. Implement the even as follows:
      
    private void btnSubmit_Click(object s}er, EventArgs e)
{
    DateTime dteTransaction = DateTime.Today;
    double Amount = 0.00;

    if (CustomerID == 0)
    {
	MessageBox.Show("You must enter a valid account number " +
			"for the new customer.");
	return;
    }

    try
    {
	Amount = double.Parse(txtAmount.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Amount.");
	return;
    }

    using (SqlConnection cnnYNB =
	new SqlConnection("Data Source=(local);" +
			  "Database='YugoNationalBank1';" +
			  "Integrated Security=SSPI;"))
    {
	string strCharges = "INSERT INTO AccountsTransactions(" +
			    "CustomerID, " +
			    "TransactionDate, TransactionType, " +
			    "ChargeAmount, ChargeReason, Notes) " +
			    "VALUES('" + CustomerID + "', '" +
			    dtpTransactionDate.Value.ToString("d") +
			    "', 'Charge', '"+ Amount + "', '" +
			    cbxChargeReasons.Text + "', '" +
			    txtNotes.Text + "');";
	SqlCommand cmdCharges = new SqlCommand(strCharges, cnnYNB);

	cnnYNB.Open();
	cmdCharges.ExecuteNonQuery();

	dtpTransactionDate.Value = DateTime.Today;
	txtAccountNumber.Text = "";
	txtCustomerName.Text = "";
	cbxChargeReasons.SelectedIndex = 0;
	txtAmount.Text = "0.00";
	txtNotes.Text = "";
    }
} 
  76. Return to the form and double-click the Close button
  77. Implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  78. Access the Central form, add a button and change its properties as follows:
    (Name): btnNewCharge
    Text: New Charge...
  79. Double-click the Account Types button and implement its event as follows:
     
    private void btnNewCharge_Click(object s}er, EventArgs e)
{
    NewCharge charge = new NewCharge();
    charge.ShowDialog();
}
  80. Execute the application and open the Employees form
  81. Create a few records
  82. Close the forms and return to your programming environment
  83. To create a new view, in the Server Explorer, under YugoNationalBank1, right-click Views and click Add New View
  84. In the Add Table dialog box, double-click Customers and AccountTypes
  85. Click Close
  86. In the Diagram section, click the check boxes of CustomerID, CustomerName, AccountNumber, AccountType, DateCreated, and AccountStatus
     
    View: Customer Identification
  87.  Close the view
  88. When asked whether you want to save it, click Yes
  89. Set the Name to CustomerIdentification and click OK
  90. In the Data Sources window, right-click dsYugoNationalBank and click Configure DataSet With Wizard...
  91. Click the check box of Views to remove the check mark
  92. Click it again to put the check mark and click Finish
  93. To create a new view, in the Server Explorer, under YugoNationalBank1, right-click Views and click Add New View
  94. In the Add Table dialog box, double-click Customers and AccountsTransactions
  95. Click Close
  96. In the Diagram section, click the check boxes of AccountNumber, TransactionDate, TransactionType, CurrencyType, DepositAmount, WithdrawalAmount, ChargeAmount, ChargeReason, and Balance
     
    Transactions
  97. Close the view
  98. When asked whether you want to save it, click Yes
  99. Set the Name to AccountTransactions and click OK
  100. In the Data Sources window, right-click dsYugoNationalBank and click Configure DataSet With Wizard...
  101. Click the check box of View to remove the check mark
  102. Click it again to put the check mark
     
    Data Source
  103. Click Finish
  104. To create a new form, on the main menu, click Project -> Add Windows Form...
  105. Set the Name to AccountTransactions and click Add
  106. From the Data Sources window, drag AccountTransactions and drop it on the form
  107. Under the form, click accountTransactionsBindingNavigator and press Delete
  108. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    accountTransactionsBindingSource bsAccountTransactions
    accountTypesTableAdapter taAccountTransactions
  109. Design the form as follows:
     
    Yugo National Bank - Account Transactions
    Control Text Name Other Properties 
    Label Account Number:    
    MaskedTextBox   txtAccountNumber Mask: 00-000000-00
    Button  Locate btnLocate  
    Label  Customer Name:    
    TextBox   txtCustomerName  
    Label Account Type:    
    TextBox   Account Type  
    Label Account Status:    
    TextBox   txtAccountStatus  
    Label  Date Created:    
    DateTimePicker   dtpDateCreated  
    DataGridView   dgvAccountProperties  
    Label  Total Deposits     
    TextBox    txtTotalDeposits  Text: 0.00
    TextAlign: Right
    Label   Total Charges     
    TextBox     txtTotalCharges  Text: 0.00
    TextAlign: Right 
    Button Close btnClose   
    Label   Total Withdrawals    
    TextBox     txtTotalWithdrawals  Text: 0.00
    TextAlign: Right 
    Label   Balance     
    TextBox     txtBalance Text: 0.00
    TextAlign: Right 
  110. On the form, double-click the Locate and change the following changes:
     
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace YugoNationalBank1
{
    public partial class AccountTransactions : Form
    {
	public AccountTransactions()
        {
	    InitializeComponent();
	}

	private void AccountTransactions_Load(object s}er, EventArgs e)
	{
	    // TODO: This line of code loads data into the
	    // 'dsYugoNationalBank.AccountTransactions' table.
	    // You can move, or remove it, as needed.
taAccountTransactions.Fill(dsYugoNationalBank.AccountTransactions);
	   bsAccountTransactions.Filter = "AccountNumber = '00-000000-00'";
	}

	private void btnLocate_Click(object s}er, EventArgs e)
	{
	    int CustomerID = 0;

	    if (txtAccountNumber.Text.Length == 0)
	    {
		MessageBox.Show("You must specify the account number " +
		"of the customer whose transactions you want to view.");
		return;
	    }
	    else
	    {
		using (SqlConnection cnnYNB =
			new SqlConnection("Data Source=(local);" +
				"Database='YugoNationalBank1';" +
				"Integrated Security=SSPI;"))
		{
		    string strYNB = "SELECT * FROM " +
			"CustomerIdentification WHERE AccountNumber = '" +
			txtAccountNumber.Text + "';";
		    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);
		    SqlDataAdapter daYNB = new SqlDataAdapter();

		    daYNB.SelectCommand = cmdYNB;
		    DataSet dsCustomers = new DataSet("CustomersSet");
		    daYNB.Fill(dsCustomers);

		    cnnYNB.Open();

		    foreach (DataRow rowCustomer in dsCustomers.Tables[0].Rows)
		    {
			CustomerID = 
				int.Parse(rowCustomer["CustomerID"].ToString());

			txtCustomerName.Text = 
				rowCustomer["CustomerName"].ToString();
			txtAccountType.Text = 
				rowCustomer["AccountType"].ToString();
			txtAccountStatus.Text = 
				rowCustomer["AccountStatus"].ToString();
			dtpDateCreated.Value = 
			DateTime.Parse(rowCustomer["DateCreated"].ToString());
			break;
		    }

		    bsAccountTransactions.Filter =
			"AccountNumber = '" + txtAccountNumber.Text + "'";
		}
	    }

	    if (CustomerID != 0)
	    {
		double Deposits = 0.00, Withdraws = 0.00,
		Charges = 0.00, Balance = 0.00;

		using (SqlConnection cnnYNB =
			new SqlConnection("Data Source=(local);" +
					  "Database='YugoNationalBank1';" +
					  "Integrated Security=SSPI;"))
		{
		    string strYNB =
			"SELECT SUM(CAST(DepositAmount AS money)), " +
			"SUM(CAST(WithdrawalAmount AS money)), " +
			"SUM(CAST(ChargeAmount AS money)) FROM " +
			"AccountsTransactions WHERE CustomerID = '" +
		    CustomerID.ToString() + "';";
		    SqlCommand cmdYNB = new SqlCommand(strYNB, cnnYNB);

		    cnnYNB.Open();
		    SqlDataReader rdrTransactions = cmdYNB.ExecuteReader();

		    while (rdrTransactions.Read())
		    {
			try
			{
			    Deposits = double.Parse(rdrTransactions[0].ToString());
			}
			catch (FormatException)
			{
			}

			try
			{
			    Withdraws = double.Parse(rdrTransactions[1].ToString());
			}
			catch (FormatException)
			{
			}

			try
			{
			    Charges = double.Parse(rdrTransactions[2].ToString());
			}
			catch (FormatException)
			{
			}

			txtTotalDeposits.Text = Deposits.ToString("F");
			txtTotalWithdrawals.Text = Withdraws.ToString("F");
			txtTotalCharges.Text = Charges.ToString("F");

			Balance = Deposits - (Withdraws + Charges);
			txtBalance.Text = Balance.ToString("F");
		    }
		}
	    }
	}
    }
} 
  111. Return to the form and double-click the Close button
  112. Implement its even as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}
  113. Access the Central form, add a button and change its properties as follows:
    (Name): btnAccountTransactions
    Text: View an Account's Transactions...
     
    Yugo National Bank
  114. Double-click the Account Types button and implement its event as follows:
     
    private void btnAccountsTransactions_Click(object s}er, EventArgs e)
{
    AccountTransactions transactions = new AccountTransactions();
    transactions.ShowDialog();
} 
  115. Execute the application
  116. Open the Account's Transactions form, enter an account number and click Locate
     
    Yugo National Bank 
    Yugo National Bank 
  117. Close the forms and return to your programming environment

Using a View for a Time Sheet

As opposed to working visually, you can also create a view programmatically.

Practical LearningPractical Learning: Programmatically Creating a View
  1. Display the Central form and double-click the Close button
  2. To create a new view, change the event as follows:
      
    private void btnClose_Click(object s}er, EventArgs e)
{
    using (SqlConnection cnnTimesheet =
	new SqlConnection("Data Source=(local);" +
			  "Database='YugoNationalBank1';" +
			  "Integrated Security=SSPI;"))
    {
	string strTimesheet = "CREATE VIEW dbo.Timesheet " +
		"AS " +
		"SELECT EmployeeID, StartDate, " +
                "	TimesheetCode, Week1Monday, " +
                "	Week1Tuesday, Week1Wednesday, " +
                "	Week1Thursday, Week1Friday, " +
                "	Week1Saturday, Week1Sunday, " +
                "	Week2Monday, Week2Tuesday, " +
                "	Week2Wednesday, Week2Thursday, " +
                "	Week2Friday, Week2Saturday, " +
                "	Week2Sunday, Notes " +
		"FROM dbo.Timesheets;";

	SqlCommand cmdTimesheet = 
	    new SqlCommand(strTimesheet, cnnTimesheet);
	cnnTimesheet.Open();
	cmdTimesheet.ExecuteNonQuery();
	MessageBox.Show("A view named Timesheet has been created.");
    }

    Close();
}
  3. Execute the application and click the Close button
  4. Click OK
  5. Display the Timesheet form and double-click the Submit button
  6. To create a new view, change the event as follows:
      
    private void btnSubmit_Click(object s}er, EventArgs e)
        {
            string strTimeSheet = "";

            // If this is new record, then create a new time sheet
            if (bNewRecord == true)
            {
                strTimeSheet = "INSERT INTO dbo.Timesheet " +
                               "VALUES('" +
                               txtEmployeeNumber.Text + "', '" +
                               dtpStartDate.Value.ToString("MM/dd/yyyy") + "', '" +
                               strTimesheetCode + "', '" +
                               txtWeek1Monday.Text + "', '" +
                               txtWeek1Tuesday.Text + "', '" +
                               txtWeek1Wednesday.Text + "', '" +
                               txtWeek1Thursday.Text + "', '" +
                               txtWeek1Friday.Text + "', '" +
                               txtWeek1Saturday.Text + "', '" +
                               txtWeek1Sunday.Text + "', '" +
                               txtWeek2Monday.Text + "', '" +
                               txtWeek2Tuesday.Text + "', '" +
                               txtWeek2Wednesday.Text + "', '" +
                               txtWeek2Thursday.Text + "', '" +
                               txtWeek2Friday.Text + "', '" +
                               txtWeek2Saturday.Text + "', '" +
                               txtWeek2Sunday.Text + "', '" +
                               txtNotes.Text + "');";
            }

            // If this is an existing record, then, only update it
            if (bNewRecord == false)
            {
                strTimeSheet = "UPDATE dbo.Timesheets SET Week1Monday = '" +
                               txtWeek1Monday.Text + "', Week1Tuesday = '" +
                               txtWeek1Tuesday.Text + "', Week1Wednesday = '" +
                               txtWeek1Wednesday.Text + "', Week1Thursday = '" +
                               txtWeek1Thursday.Text + "', Week1Friday = '" +
                               txtWeek1Friday.Text + "', Week1Saturday = '" +
                               txtWeek1Saturday.Text + "', Week1Sunday = '" +
                               txtWeek1Sunday.Text + "', Week2Monday = '" +
                               txtWeek2Monday.Text + "', Week2Tuesday = '" +
                               txtWeek2Tuesday.Text + "', Week2Wednesday = '" +
                               txtWeek2Wednesday.Text + "', Week2Thursday = '" +
                               txtWeek2Thursday.Text + "', Week2Friday = '" +
                               txtWeek2Friday.Text + "', Week2Saturday = '" +
                               txtWeek2Saturday.Text + "', Week2Sunday = '" +
                               txtWeek2Sunday.Text + "', Notes = '" + txtNotes.Text +
                               "' WHERE TimeSheetCode = '" + strTimesheetCode + "';";
            }

            if (ValidTimesheet == true)
            {
                SqlConnection conTimeSheet =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='YugoNationalBank1';" +
                                      "Integrated Security=true");
                SqlCommand cmdTimeSheet = new SqlCommand(strTimeSheet, conTimeSheet);

                conTimeSheet.Open();
                cmdTimeSheet.ExecuteNonQuery();
                conTimeSheet.Close();

                MessageBox.Show("Your time sheet has been submitted");

                // Reset the timesheet
                txtEmployeeNumber.Text = "";
                dtpStartDate.Value = DateTime.Today;

                btnReset_Click(s}er, e);
            }
    else
    {
        MessageBox.Show("The time sheet is not valid\n" +
                        "either you didn't enter a valid employee number, " +
                        "or you didn't select a valid start date\n" +
                        "The time sheet will not be saved");
    }
}
  7. Execute the application
  8. Open the employees timesheet and create a few entries
  9. Close the form and return to your programming environment
 
Home Copyright © 2007-2009 FunctionX, Inc.