Home

Database Example Application: Georgetown Cleaning Services

   

Introduction

 

You would obtain data from a table and use it in your database. One way you could do this consists of using a data adapter. This is possible because a data adapter uses a command performed on a database through a database. The command could perform the action of selecting data from a table. The data adapter then gets this data and fills a data set with it.

After a data set has been filled, the records are available. Through the features of a data set, you can access the table(s), its(their) column(s), and its(their) record(s). You can either create a new record, check the existence of a record, delete an existing record, or edit/update a record.

   

Practical LearningPractical Learning: Binding Data With a Data Adapter 

  1. Create a new Windows Application named GeorgetownCleaningServices5
  2. To create a new form, on the main menu, click Project -> Add Windows Form...
  3. Set the Name to Employees and press Enter
  4. Design the form as follows:
     
    Georgetown Cleaning Services: Employees
     
    Control Name Text Additional Properties
    Group Box Group Box   New Employee  
    Label Label   First Name:  
    TextBox Text Box txtFirstName    
    Label Label   Last Name:  
    TextBox Text Box txtLastName    
    Label Label   Employee #:  
    MaskedTextBox Text Box txtEmployeeNumber   Mask: 00-000
    Label Label   Title:  
    TextBox Text Box txtTitle    
    Label Label   Hourly Salary:  
    TextBox Text Box txtHourlySalary   TextAlign: Right
    Button Button btnSubmit Submit  
    DataGridView Data Grid View dgvEmployees    
    Button Button btnClose Close  
  5. Save the form
  6. To create a new form, on the main menu, click Project -> Add Windows Form...
  7. Set the Name to Customers and press Enter
  8. Design the form as follows:
     
    Georgetown Cleaning Services: Customers
     
    Control Name Text Additional Properties
    Group Box Group Box   New Customer  
    Label Label   Customer Phone:  
    MaskedTextBox Text Box txtCustomerPhone   Mask: (999) 000-0000
    Label Label   Customer Name:  
    TextBox Text Box txtCustomerName    
    Button Button btnSubmit Submit  
    DataGridView Data Grid View dgvCustomers    
    Button Button btnClose Close  
  9. Save the form
  10. In the Solution Explorer, right-click Form1.cs and click Rename
  11. Type OrderCleaning.cs and press Enter twice
  12. Design the form as follows:
     
    Georgetown Cleaning Services: Cleaning Orders
     
    Control Name Text Additional Properties
    GroupBox GroupBox   Processed By  
    Label Label   Employee #:  
    MaskedTextBox Masked Text Box txtEmployeeNumber   Mask: 00-000
    Button  Button  btnNewEmployee  New Employee...  
    Label Label   Employee Name:  
    TextBox TextBox txtEmployeeName    
    GroupBox GroupBox   Processed For  
    Label Label   Customer Phone:  
    MaskedTextBox Masked Text Box txtCustomerPhone   Mask: (999) 000-0000
    Button  Button btnNewCustomer  New Cust...  
    Label Label   Customer Name:  
    TextBox TextBox txtCustomerName    
    GroupBox GroupBox   Order Timing  
    Label Label   Date Left:  
    DateTimePicker DateTimePicker dtpDateLeft    
    Label Label   Time Left:  
    DateTimePicker Date Time Picker dtpTimeLeft   Format: Time
    Label Label   Date Expected:  
    DateTimePicker DateTimePicker dtpDateExpected    
    Label Label   Time Expected:  
    DateTimePicker DateTimePicker dtpTimeExpected   Format: Time
    Label Label   D&ate Picked Up:  
    DateTimePicker DateTimePicker dtpDatePickedUp    
    Label Label   Time Pic&kep Up:  
    DateTimePicker DateTimePicker dtpTimePickedUp    
    GroupBox GroupBox   Order Processing  
    Label Label   Item Type  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    Label Label   Shirts  
    TextBox TextBox txtUnitPriceShirts 1.25 TextAlign: Right
    TextBox TextBox txtQuantityShirts 0 TextAlign: Right
    TextBox TextBox txtSubTotalShirts 0.00 TextAlign: Right
    Label Label   Pants  
    TextBox TextBox txtUnitPricePants 1.95 TextAlign: Right
    TextBox TextBox txtQuantityPants   TextAlign: Right
    TextBox TextBox txtSubTotalPants 0.00 TextAlign: Right
    ComboBox ComboBox cbxItem1Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men's Suit 2Pc
    Men's Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox txtUnitPriceItem1 0.00 TextAlign: Right
    TextBox TextBox txtQuantityItem1 0 TextAlign: Right
    TextBox TextBox txtSubTotalItem1 0.00 TextAlign: Right
    ComboBox ComboBox cbxItem2Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men's Suit 2Pc
    Men's Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox txtUnitPriceItem2 0.00 TextAlign: Right
    TextBox TextBox txtQuantityItem2 0 TextAlign: Right
    TextBox TextBox txtSubTotalItem2 0.00 TextAlign: Right
    ComboBox ComboBox cbxItem3Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men's Suit 2Pc
    Men's Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox txtUnitPriceItem3 0.00 TextAlign: Right
    TextBox TextBox txtQuantityItem3 0 TextAlign: Right
    TextBox TextBox txtSubTotalItem3 0.00 TextAlign: Right
    ComboBox ComboBox cbxItem4Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men's Suit 2Pc
    Men's Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox txtUnitPriceItem4 0.00 TextAlign: Right
    TextBox TextBox txtQuantityItem4 0 TextAlign: Right
    TextBox TextBox txtSubTotalItem4 0.00 TextAlign: Right
    GroupBox GroupBox   Order Summary  
    Label Label   Cleaning Total:  
    TextBox TextBox txtCleaningTotal 0.00 TextAlign: Right
    Label Label   Tax Rate:  
    TextBox TextBox txtTaxRate 7.75 TextAlign: Right
    Label Label   %  
    Label Label   Tax Amount:  
    TextBox TextBox txtTaxAmount 0.00 TextAlign: Right
    Label Label   Net Total:  
    TextBox TextBox txtNetPrice 0.00 TextAlign: Right
    Label Label   Order &Status:  
    ComboBox ComboBox cbxOrderStatus    
    Button Button btnSave Save  
    GroupBox  Group Box    Cleaning Order Details   
    Label Label   &Receipt #:  
    TextBox TextBox txtCleaningOrderID    
    Button Button btnOpen Open  
    Label  Label    Notes  
    TextBox  TextBox  txtNotes    
    Button Button btnNewCleaningOrder New Cleaning Order  
    Button Button btnClose Close  
  13. Double-click the New Employee button and implement its event as follows:
     
    private void btnNewEmployee_Click(object sender, EventArgs e)
    {
        Employees clerks = new Employees();
        clerks.ShowDialog();
    } 
  14. Return to the form and double-click the New Cust button
  15. Implement the event as follows:
     
    private void btnNewCustomer_Click(object sender, EventArgs e)
    {
        Customers clients = new Customers();
        clients.ShowDialog();
    } 
  16. Return to the form and double-click an unoccupied area of its body
  17. To create a database, including the tables with their primary keys and their foreign keys, implement the Load event as follows:
     
    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 GeorgetownCleaningServices5
    {
        public partial class OrderCleaning: Form
        {
    	public Exercise()
    	{
    	    InitializeComponent();
            }
    
    	private void OrderCleaning_Load(object sender, EventArgs e)
    	{
    	    using (SqlConnection conGCS = 
    	        new SqlConnection("Data Source=(local); " +
    			 	  "Integrated Security='SSPI';"))
       	    {
    	    	string strCreateDatabase = 
    		    "CREATE DATABASE GeorgetownCleaningServices1";
    
    		SqlCommand cmdGCS = new SqlCommand(strCreateDatabase,
    						   conGCS);
    
    		conGCS.Open();
    	    	cmdGCS.ExecuteNonQuery();
    
    	        MessageBox.Show("A database named " + 
    		    "GeorgetownCleaningServices1 has been created");
    	   }
    
    	   using (SqlConnection conGCS =
    	   	new SqlConnection("Data Source=(local); " +
    			"Database='GeorgetownCleaningServices1'; " +
    				  "Integrated Security='SSPI';"))
    	    {
    	        string strCreateTable = "CREATE TABLE Employees( " +
    			"EmployeeID int identity(1, 1) NOT NULL, " +
    			"EmployeeNumber nchar(6), " +
    			"FirstName varchar(32), " +
    			"LastName varchar(32) NOT NULL, " +
    			"FullName AS (([LastName]+', ')+[FirstName]), " +
    			"Title varchar(80), " +
    			"HourlySalary smallmoney, " +
    			"CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));";
    
    	        SqlCommand cmdGCS = new SqlCommand(strCreateTable, conGCS);
    
        		conGCS.Open();
    	    	cmdGCS.ExecuteNonQuery();
    	    	MessageBox.Show("A table named Employees has been created");
    	    }
    
    	    using (SqlConnection conGCS =
    	    	new SqlConnection("Data Source=(local); " +
    			          "Database='GeorgetownCleaningServices1'; " +
    			          "Integrated Security='SSPI';"))
    	    {
    	    	string strCreateTable = "CREATE TABLE Customers( " +
    			"CustomerID int identity(1, 1) NOT NULL, " +
    			"PhoneNumber varchar(20), " +
    			"FullName varchar(80), " +
    			"CONSTRAINT PK_Customer PRIMARY KEY (CustomerID));";
    
    	    	SqlCommand cmdGCS = new SqlCommand(strCreateTable, conGCS);
    
    	    	conGCS.Open();
    	    	cmdGCS.ExecuteNonQuery();
    	    	MessageBox.Show("A table named Customers has been created");
    	    }
    
    	    using (SqlConnection conGCS =
    	    	new SqlConnection("Data Source=(local); " +
    			          "Database='GeorgetownCleaningServices1'; " +
    			          "Integrated Security='SSPI';"))
    	    {
    	    	string strCreateTable = "CREATE TABLE CleaningOrders( " +
    			"CleaningOrderID int identity(1000, 1) NOT NULL, " +
    			"EmployeeNumber nchar(6) NOT NULL, " +
    			"CustomerNumber varchar(20) NOT NULL, " +
    			"DateLeft smalldatetime, " +
    			"TimeLeft smalldatetime, " +
    			"DateExpected smalldatetime, " +
    			"TimeExpected smalldatetime, " +
    			"OrderStatus varchar(50), " +
    			"DatePickedUp smalldatetime, " +
    			"TimePickedUp smalldatetime, " +
    			"UnitPriceShirts smallmoney, " +
    			"QuantityShirts smallmoney, " +
    			"SubTotalShirts smallmoney, " +
    			"UnitPricePants smallmoney, " +
    			"QuantityPants smallmoney, " +
    			"SubTotalPants smallmoney, " +
    			"Item1Name varchar(50), " +
    			"UnitPriceItem1 smallmoney, " +
    			"QuantityItem1 smallmoney, " +
    			"SubTotalItem1 smallmoney, " +
    			"Item2Name varchar(50), " +
    			"UnitPriceItem2 smallmoney, " +
    			"QuantityItem2 smallmoney, " +
    			"SubTotalItem2 smallmoney, " +
    			"Item3Name varchar(50), " +
    			"UnitPriceItem3 smallmoney, " +
    			"QuantityItem3 smallmoney, " +
    			"SubTotalItem3 smallmoney, " +
    			"Item4Name varchar(50), " +
    			"UnitPriceItem4 smallmoney, " +
    			"QuantityItem4 smallmoney, " +
    			"SubTotalItem4 smallmoney, " +
    			"CleaningTotal smallmoney, " +
    			"TaxRate decimal(6,2), " +
    			"TaxAmount smallmoney, " +
    			"NetPrice smallmoney, " +
    			"Text smallmoney);";
    
    	        SqlCommand cmdGCS = new SqlCommand(strCreateTable, conGCS);
    
    	    	conGCS.Open();
    	    	cmdGCS.ExecuteNonQuery();
    	    	MessageBox.Show("A table named CleaningOrders has been created");
    	    }
        	}
        }
    }
  18. Execute the application to create the database and its tables
  19. Close the form and return to your programming environment
  20. In the source file, delete the whole content of the Load event and return to the form
  21. Access the Employees form
  22. Double-click an unoccupied area of the form to generate its Load event and implement it as follows:
     
    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 GeorgetownCleaningServices5
    {
        public partial class Employees : Form
        {
    	public Employees()
    	{
    	    InitializeComponent();
    	}
    
    	void ShowEmployees()
    	{
    	    using (SqlConnection cnnEmployees =
    		new SqlConnection("Data Source=(local);" +
    			"Database='GeorgetownCleaningServices1';" +
    			"Integrated Security=SSPI;"))
    	    {
    		string strEmployees =
    			"SELECT * FROM Employees;";
    		SqlCommand cmdEmployees =
    		    new SqlCommand(strEmployees, cnnEmployees);
    		SqlDataAdapter daEmployees = new SqlDataAdapter();
    
    		daEmployees.SelectCommand = cmdEmployees;
    		DataSet dsEmployees = new DataSet("EmployeesSet");
    		daEmployees.Fill(dsEmployees);
    
    		cnnEmployees.Open();
    
    	    	dgvEmployees.DataSource = dsEmployees;
    		dgvEmployees.DataMember = dsEmployees.Tables[0].TableName;
    	   }
    	}
    
    	private void Employees_Load(object sender, EventArgs e)
    	{
    	    ShowEmployees();
    	}
        }
    }
  23. Return to the form and double-click the Submit button
  24. Implement its event as follows:
     
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        using (SqlConnection cnnEmployees =
    	new SqlConnection("Data Source=(local);" +
    		"Database='GeorgetownCleaningServices1';" +
    		"Integrated Security=SSPI;"))
        {
    	string strEmployees = "INSERT INTO Employees(EmployeeNumber, " +
    			      "FirstName, LastName, Title, HourlySalary) " +
    			      "VALUES('" + txtEmployeeNumber.Text +
    			      "', '" + txtFirstName.Text + "', '" +
    			      txtLastName.Text + "', '" + txtTitle.Text +
    			      "', '" + txtHourlySalary.Text + "');";
    	SqlCommand cmdEmployees = new SqlCommand(strEmployees,
    						 cnnEmployees);
    
    	cnnEmployees.Open();
    	cmdEmployees.ExecuteNonQuery();
    
    	txtEmployeeNumber.Text = "";
    	txtFirstName.Text = "";
    	txtLastName.Text = "";
    	txtTitle.Text = "";
    	txtHourlySalary.Text = "0.00";
    
    	ShowEmployees();
        }
    }
  25. Return to the form and double-click the Close button
  26. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    } 
  27. Access the Customers form
  28. Double-click an unoccupied area of the form to generate its Load event and implement it as follows:
     
    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 GeorgetownCleaningServices5a
    {
        public partial class Customers : Form
        {
    	public Customers()
    	{
    	    InitializeComponent();
    	}
    
    	void ShowCustomers()
    	{
    	    using (SqlConnection cnnCustomers =
    		new SqlConnection("Data Source=(local);" +
    			"Database='GeorgetownCleaningServices1';" +
    			"Integrated Security=SSPI;"))
    	    {
    		string strCustomers = "SELECT * FROM Customers;";
    		SqlCommand cmdCustomers =
    		    new SqlCommand(strCustomers, cnnCustomers);
    		SqlDataAdapter daCustomers = new SqlDataAdapter();
    
    		daCustomers.SelectCommand = cmdCustomers;
    		DataSet dsCustomers = new DataSet("CustomersSet");
    		daCustomers.Fill(dsCustomers);
    
    		cnnCustomers.Open();
    
    		dgvCustomers.DataSource = dsCustomers;
    		dgvCustomers.DataMember = dsCustomers.Tables[0].TableName;
        	    }
    	}
    
    	private void Customers_Load(object sender, EventArgs e)
    	{
    	    ShowCustomers();
    	}
        }
    } 
  29. Return to the form and double-click the Submit button
  30. Implement its event as follows:
     
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        using (SqlConnection cnnCustomers =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='GeorgetownCleaningServices1';" +
    			  "Integrated Security=SSPI;"))
        {
    	string strCustomers = "INSERT INTO Customers(PhoneNumber, " +
    			      "FullName) " +
    			      "VALUES('" + txtCustomerPhone.Text +
    			      "', '" + txtCustomerName.Text + "');";
    	SqlCommand cmdCustomers = new SqlCommand(strCustomers,
    						 cnnCustomers);
    
    	cnnCustomers.Open();
    	cmdCustomers.ExecuteNonQuery();
    
    	txtCustomerPhone.Text = "";
    	txtCustomerName.Text = "";
    
    	ShowCustomers();
        }
    } 
  31. Return to the form and double-click the Close button
  32. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    } 
  33. Access the CleaningOrders form
  34. On the form, click the txtEmployeeNumber text box and, on the Properties window, click the Events button
  35. In the Events section, double-click Leave and implement its event as follows:
     
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        using (SqlConnection cnnEmployees =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='GeorgetownCleaningServices1';" +
    			  "Integrated Security=SSPI;"))
        {
    	string strEmployees =
    	    "SELECT FullName FROM Employees WHERE EmployeeNumber = '" +
    		txtEmployeeNumber.Text + "';";
    	SqlCommand cmdEmployees =
    		new SqlCommand(strEmployees, cnnEmployees);
    	SqlDataAdapter daEmployees = new SqlDataAdapter();
    
    	daEmployees.SelectCommand = cmdEmployees;
    	DataSet dsEmployees = new DataSet("EmployeesSet");
    	daEmployees.Fill(dsEmployees);
    
    	cnnEmployees.Open();
    
    	foreach (DataRow rowEmployee in dsEmployees.Tables[0].Rows)
    	{
    	    foreach (DataColumn colEmployee in dsEmployees.Tables[0].Columns)
    	    {
    		txtEmployeeName.Text = rowEmployee[colEmployee].ToString();
    	    }
    	    break;
    	}
        }
    }
  36. Return to the CleaningOrders form
  37. On the form, click the txtCustomerPhone text box and, in the Events section of the Properties window, double-click Leave
  38. Implement its event as follows:
     
    private void txtCustomerPhone_Leave(object sender, EventArgs e)
    {
        using (SqlConnection cnnCustomers =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='GeorgetownCleaningServices1';" +
    			  "Integrated Security=SSPI;"))
        {
    	string strCustomers =
    		"SELECT FullName FROM Customers WHERE PhoneNumber = '" +
    		txtCustomerPhone.Text + "';";
    	SqlCommand cmdCustomers =
    	new SqlCommand(strCustomers, cnnCustomers);
    	SqlDataAdapter daCustomers = new SqlDataAdapter();
    
    	daCustomers.SelectCommand = cmdCustomers;
    	DataSet dsCustomers = new DataSet("CustomersSet");
    	daCustomers.Fill(dsCustomers);
    
    	cnnCustomers.Open();
    
    	foreach (DataRow rowCustomer in dsCustomers.Tables[0].Rows)
    	{
    	    foreach (DataColumn colCustomer in dsCustomers.Tables[0].Columns)
    	    {
    		txtCustomerName.Text = rowCustomer[colCustomer].ToString();
    	    }
    	    break;
    	}
        }
    }
  39. Return to the CleaningOrders form
  40. On the form, double-click the New Cleaning Order button and implement its event as follows:
     
    private void btnNewCleaningOrder_Click(object sender, EventArgs e)
    {
        txtEmployeeNumber.Text = "";
        txtEmployeeName.Text = "";
        txtCustomerPhone.Text = "";
        txtCustomerName.Text = "";
    
        txtCleaningOrderID.Text = "";
    
        dtpDateLeft.Value = DateTime.Today;
        dtpTimeLeft.Value = DateTime.Today;
        dtpDateExpected.Value = DateTime.Today;
        dtpTimeExpected.Value = DateTime.Today;
    
        cbxOrderStatus.Text = "Not Yet Ready";
        dtpDatePickedUp.Value = DateTime.Today;
        dtpTimePickedUp.Value = DateTime.Today;
    
        txtUnitPriceShirts.Text = "1.25";
        txtQuantityShirts.Text = "0";
        txtSubTotalShirts.Text = "0.00";
    
        txtUnitPricePants.Text = "1.95";
        txtQuantityPants.Text = "0";
        txtSubTotalPants.Text = "0.00";
        cbxItem1Name.Text = "None";
        txtUnitPriceItem1.Text = "0.00";
        txtQuantityItem1.Text = "0";
        txtSubTotalItem1.Text = "0.00";
        cbxNameItem2.Text = "None";
        txtUnitPriceItem2.Text = "0.00";
        txtQuantityItem2.Text = "0";
        txtSubTotalItem2.Text = "0.00";
        cbxNameItem3.Text = "None";
        txtUnitPriceItem3.Text = "0.00";
        txtQuantityItem3.Text = "0";
        txtSubTotalItem3.Text = "0.00";
        cbxNameItem4.Text = "None";
        txtUnitPriceItem4.Text = "0.00";
        txtQuantityItem4.Text = "0";
        txtSubTotalItem4.Text = "0.00";
    
        txtCleaningTotal.Text = "0.00";
        txtTaxRate.Text = "7.75";
        txtTaxAmount.Text = "0.00";
        txtCleaningTotal.Text = "0.00";
        txtNotes.Text = "";
    
        txtEmployeeNumber.Focus();
    }
  41. Change to Load event as follows:
     
    private void OrderCleaning_Load(object sender, EventArgs e)
    {
        btnNewCleaningOrder_Click(sender, e);
    }
  42. Double-click the Time Left control and implement its ValueChanged event as follows:
     
    private void dtpTimeLeft_ValueChanged(object sender, EventArgs e)
    {
        DateTime dateLeft = this.dtpDateLeft.Value;
        DateTime timeLeft = this.dtpTimeLeft.Value;
    
        DateTime time9AM = new DateTime(timeLeft.Year, timeLeft.Month,
                                        timeLeft.Day, 9, 0, 0);
    
        // If the customer leaves clothes before 9AM...
        if (timeLeft <= time9AM)
        {
            // ... then they should be ready the same day after 5PM
            this.dtpDateExpected.Value = dateLeft;
            this.dtpTimeExpected.Value = new DateTime(dateLeft.Year,
                                                      dateLeft.Month,
    					dateLeft.Day, 17, 0, 0);
        }
        else
        {
            // If the clothes were left after 9AM,
            // then they will be available
    	// the following business morning at 8AM
            // If the following day is Sunday,
            // then they will be ready the following Monday
            if (dateLeft.DayOfWeek == DayOfWeek.Saturday)
            {
                dtpDateExpected.Value = dateLeft.AddDays(2.00D);
                dtpTimeExpected.Value = new DateTime(dateLeft.Year,
                                                     dateLeft.Month,
    					dateLeft.Day + 2, 8, 0, 0);
            }
            else
            {
                dtpDateExpected.Value = new DateTime(dateLeft.Year,
                                                     dateLeft.Month,
    						 dateLeft.Day + 1);
                dtpTimeExpected.Value = new DateTime(dateLeft.Year,
                                                     dateLeft.Month,
    					dateLeft.Day + 1, 8, 0, 0);
            }
        }
    }
  43. Return to the form and double-click the Save button
  44. Implement its event as follows:
     
    private void btnSave_Click(object sender, EventArgs e)
    {
        string strCommand = "";
    	using (SqlConnection cnnCleaningOrders =
    	    new SqlConnection("Data Source=(local);" +
    		"Database='GeorgetownCleaningServices1';" +
    		"Integrated Security=SSPI;"))
        {
    	// If the Receipt Number is empty, it appears that
    	// the user/clerk wants to create a new cleaning order
    	if (txtCleaningOrderID.Text.Length == 0)
    	{
    	    strCommand = "INSERT INTO CleaningOrders( " +
    			 "EmployeeNumber, CustomerNumber, DateLeft, " +
    			 "TimeLeft, DateExpected, TimeExpected, " +
    			 "OrderStatus, DatePickedUp, " +
    			 "TimePickedUp, UnitPriceShirts, " +
    			 "QuantityShirts, SubTotalShirts, " +
    			 "UnitPricePants, QuantityPants, " +
    			 "SubTotalPants, Item1Name, " +
    			 "UnitPriceItem1, QuantityItem1, " +
    			 "SubTotalItem1, Item2Name, " +
    			 "UnitPriceItem2, QuantityItem2, " +
    			 "SubTotalItem2, Item3Name, " +
    			 "UnitPriceItem3, QuantityItem3, " +
    			 "SubTotalItem3, Item4Name, " +
    			 "UnitPriceItem4, QuantityItem4, " +
    			 "SubTotalItem4, CleaningTotal, " +
    			 "TaxRate, TaxAmount, NetPrice, Notes) " +
    			 "VALUES('" + txtEmployeeNumber.Text + "', '" +
    			 txtCustomerPhone.Text + "', '" +
    			 dtpDateLeft.Value.ToString("d") + "', '" +
    			 dtpTimeLeft.Value.ToString("t") + "', '" +
    			 dtpDateExpected.Value.ToString("d") + "', '" +
    			 dtpTimeExpected.Value.ToString("t") + "', '" +
    			 cbxOrderStatus.Text + "', '" +
    			 dtpDatePickedUp.Value.ToString("d") + "', '" +
    			 dtpDatePickedUp.Value.ToString("t") + "', '" +
    			 txtUnitPriceShirts.Text + "', '" + 
    			 txtQuantityShirts.Text + "', '" + 
    			 txtSubTotalShirts.Text + "', '" +
    			 txtUnitPricePants.Text + "', '" + 
    			 txtQuantityPants.Text + "', '" +
    			 txtSubTotalPants.Text + "', '" + 
    			 cbxItem1Name.Text + "', '" +
    			 txtUnitPriceItem1.Text + "', '" + 
    			 txtQuantityItem1.Text + "', '" +
    			 txtSubTotalItem1.Text + "', '" + 
    			 cbxItem2Name.Text + "', '" +
    			 txtUnitPriceItem2.Text + "', '" + 
    			 txtQuantityItem2.Text + "', '" +
    			 txtSubTotalItem2.Text + "', '" + 
    			 cbxItem3Name.Text + "', '" +
    			 txtUnitPriceItem3.Text + "', '" + 
    			 txtQuantityItem3.Text + "', '" +
    			 txtSubTotalItem3.Text + "', '" + 
    			 cbxItem4Name.Text + "', '" +
    			 txtUnitPriceItem4.Text + "', '" + 
    			 txtQuantityItem4.Text + "', '" +
    			 txtSubTotalItem4.Text + "', '" + 
    			 txtCleaningTotal.Text + "', '" +
    			 txtTaxRate.Text + "', '" + 
    			 txtTaxAmount.Text + "', '" +
    			 txtNetPrice.Text + "', '" + txtNotes.Text + "');";
    	}
    	else // Since there is a receipt number, update/edit the cleaning order
    	{
    	    strCommand = "UPDATE CleaningOrders " +
    			 "SET EmployeeNumber = '" +
    			 txtEmployeeNumber.Text + "', " +
    			" CustomerNumber = '" + txtCustomerPhone.Text + "', " +
    		" DateLeft = '" + dtpDateLeft.Value.ToString("d") + "', " +
    		" TimeLeft = '" + dtpTimeLeft.Value.ToString("t") + "', " +
    		" DateExpected = '" + dtpDateExpected.Value.ToString("d") + "', " +
    		" TimeExpected = '" + dtpTimeExpected.Value.ToString("t") + "', " +
    		" OrderStatus = '" + cbxOrderStatus.Text + "', " +
    		" DatePickedUp = '" + dtpDatePickedUp.Value.ToString("d") + "', " +
    		" TimePickedUp = '" + dtpDatePickedUp.Value.ToString("t") + "', " +
    		" UnitPriceShirts = '" + txtUnitPriceShirts.Text + "', " +
    		" QuantityShirts = '" + txtQuantityShirts.Text + "', " +
    		" SubTotalShirts = '" + txtSubTotalShirts.Text + "', " +
    		" UnitPricePants = '" + txtUnitPricePants.Text + "', " +
    		" QuantityPants = '" + txtQuantityPants.Text + "', " +
    		" SubTotalPants = '" + txtSubTotalPants.Text + "', " +
    		" Item1Name = '" + cbxItem1Name.Text + "', " +
    		" UnitPriceItem1 = '" + txtUnitPriceItem1.Text + "', " +
    		" QuantityItem1 = '" + txtQuantityItem1.Text + "', " +
    		" SubTotalItem1 = '" + txtSubTotalItem1.Text + "', " +
    		" Item2Name = '" + cbxItem2Name.Text + "', " +
    		" UnitPriceItem2 = '" + txtUnitPriceItem2.Text + "', " +
    		" QuantityItem2 = '" + txtQuantityItem2.Text + "', " +
    		" SubTotalItem2 = '" + txtSubTotalItem2.Text + "', " +
    		" Item3Name = '" + cbxItem3Name.Text + "', " +
    		" UnitPriceItem3 = '" + txtUnitPriceItem3.Text + "', " +
    		" QuantityItem3 = '" + txtQuantityItem3.Text + "', " +
    		" SubTotalItem3 = '" + txtSubTotalItem3.Text + "', " +
    		" Item4Name = '" + cbxItem4Name.Text + "', " +
    		" UnitPriceItem4 = '" + txtUnitPriceItem4.Text + "', " +
    		" QuantityItem4 = '" + txtQuantityItem4.Text + "', " +
    		" SubTotalItem4 = '" + txtSubTotalItem4.Text + "', " +
    		" CleaningTotal = '" + txtCleaningTotal.Text + "', " +
    		" TaxRate = '" + txtTaxRate.Text + "', " +
    		" TaxAmount = '" + txtTaxAmount.Text + "', " +
    		" NetPrice = '" + txtNetPrice.Text + "', " +
    		" Notes = '" + txtNotes.Text + "' " +
    		" WHERE CleaningOrderID = '" + txtCleaningOrderID.Text + "';";
    	}
    
    	SqlCommand cmdCleaningOrders = new SqlCommand(strCommand,
    	cnnCleaningOrders);
    
    	cnnCleaningOrders.Open();
    	cmdCleaningOrders.ExecuteNonQuery();
    
    	btnNewCleaningOrder_Click(sender, e);
        }
    }
  45. On the form, double-click the Open button and implement the event as follows:
     
    private void btnOpen_Click(object sender, EventArgs e)
    {
        if (txtCleaningOrderID.Text.Length == 0)
        {
    	MessageBox.Show("To open a cleaning order, " +
    			"enter its receipt number and click Open.");
      	return;
        }
    
        using (SqlConnection cnnCleaningOrders =
    	new SqlConnection("Data Source=(local);" +
    	    		  "Database='GeorgetownCleaningServices1';" +
    			  "Integrated Security=SSPI;"))
        {
    	string strCleaningOrders =
    		"SELECT EmployeeNumber, CustomerNumber, DateLeft, " +
    		"TimeLeft, DateExpected, TimeExpected, " +
    		"OrderStatus, DatePickedUp, " +
    		"TimePickedUp, UnitPriceShirts, " +
    		"QuantityShirts, SubTotalShirts, " +
    		"UnitPricePants, QuantityPants, " +
    		"SubTotalPants, Item1Name, " +
    		"UnitPriceItem1, QuantityItem1, " +
    		"SubTotalItem1, Item2Name, " +
    		"UnitPriceItem2, QuantityItem2, " +
    		"SubTotalItem2, Item3Name, " +
    		"UnitPriceItem3, QuantityItem3, " +
    		"SubTotalItem3, Item4Name, " +
    		"UnitPriceItem4, QuantityItem4, " +
    		"SubTotalItem4, CleaningTotal, " +
    		"TaxRate, TaxAmount, NetPrice, Notes " +
    		"FROM CleaningOrders WHERE CleaningOrderID = '" +
    		txtCleaningOrderID.Text + "';";
        	SqlCommand cmdCleaningOrders =
    		new SqlCommand(strCleaningOrders, cnnCleaningOrders);
    	SqlDataAdapter daCleaningOrders = new SqlDataAdapter();
    
    
    	daCleaningOrders.SelectCommand = cmdCleaningOrders;
    	DataSet dsCleaningOrders = new DataSet("CleaningOrdersSet");
    	daCleaningOrders.Fill(dsCleaningOrders);
    
    	cnnCleaningOrders.Open();
    
    	for (int i = 0; i < dsCleaningOrders.Tables[0].Rows.Count; i++)
    	{
    	    DataRow record = dsCleaningOrders.Tables[0].Rows[i];
    
    	    txtEmployeeNumber.Text = record[0].ToString();
    	    txtCustomerPhone.Text = record[1].ToString();
    
    	    dtpDateLeft.Value = DateTime.Parse(record[2].ToString());
    	    dtpTimeLeft.Value = DateTime.Parse(record[3].ToString());
    	    dtpDateExpected.Value = DateTime.Parse(record[4].ToString());
    	    dtpTimeExpected.Value = DateTime.Parse(record[5].ToString());
    
    	    cbxOrderStatus.Text = record[6].ToString();
    	
    	    dtpDatePickedUp.Value = DateTime.Parse(record[7].ToString());
    	    dtpTimePickedUp.Value = DateTime.Parse(record[8].ToString());
    
    	txtUnitPriceShirts.Text = double.Parse(record[9].ToString()).ToString("F");
    	    txtQuantityShirts.Text = record[10].ToString();
    	txtSubTotalShirts.Text = double.Parse(record[11].ToString()).ToString("F");
    
    	txtUnitPricePants.Text = double.Parse(record[12].ToString()).ToString("F");
    	    txtQuantityPants.Text = record[13].ToString();
    	txtSubTotalPants.Text = double.Parse(record[14].ToString()).ToString("F");
    
    	    cbxItem1Name.Text = record[15].ToString();
    	txtUnitPriceItem1.Text = double.Parse(record[16].ToString()).ToString("F");
    	    txtQuantityItem1.Text = record[17].ToString();
    	txtSubTotalItem1.Text = double.Parse(record[18].ToString()).ToString("F");
    
    	    cbxItem2Name.Text = record[19].ToString();
    	txtUnitPriceItem2.Text = double.Parse(record[20].ToString()).ToString("F");
    	    txtQuantityItem2.Text = record[21].ToString();
    	txtSubTotalItem2.Text = double.Parse(record[22].ToString()).ToString("F");
    
    	    cbxItem3Name.Text = record[23].ToString();
    	txtUnitPriceItem3.Text = double.Parse(record[24].ToString()).ToString("F");
    	    txtQuantityItem3.Text = record[25].ToString();
    	txtSubTotalItem3.Text = double.Parse(record[26].ToString()).ToString("F");
    
    	    cbxItem4Name.Text = record[27].ToString();
    	txtUnitPriceItem4.Text = double.Parse(record[28].ToString()).ToString("F");
    	    txtQuantityItem4.Text = record[29].ToString();
    	txtSubTotalItem4.Text = double.Parse(record[30].ToString()).ToString("F");
    
    	txtCleaningTotal.Text = double.Parse(record[31].ToString()).ToString("F");
    	txtTaxRate.Text = double.Parse(record[32].ToString()).ToString("F");
    	txtTaxAmount.Text = double.Parse(record[33].ToString()).ToString("F");
    	txtCleaningTotal.Text = double.Parse(record[34].ToString()).ToString("F");
    
    	    txtNotes.Text = record[35].ToString();
    
    	    txtEmployeeNumber_Leave(sender, e);
    	   txtCustomerPhone_Leave(sender, e);
    	}
        }
    }
  46. Return to the Cleaning Orders form and click the unit price text box that corresponds to the pants
  47. Press and hold Shift
  48. Click the unit price text boxes for item 1, item 2, item 3, and item 4
  49. Click each text box under the Qty label
  50. Click the Tax Rate text box
  51. Release Shift
  52. In the Events section of the Properties window, double-click Leave and implement the event as follows:
     
    private void txtShirtsUnitPrice_Leave(object sender, EventArgs e)
    {
        double unitPriceShirts = 0.00, unitPricePants = 0.00,
               unitPriceItem1 = 0.00, unitPriceItem2 = 0.00,
               unitPriceItem3 = 0.00, unitPriceItem4 = 0.00;
               double subTotalShirts = 0.00, subTotalPants = 0.00,
               subTotalItem1 = 0.00, subTotalItem2 = 0.00,
               subTotalItem3 = 0.00, subTotalItem4 = 0.00;
        int qtyShirts = 1, qtyPants = 1, qtyItem1 = 1,
            qtyItem2 = 1, qtyItem3 = 1, qtyItem4 = 4;
        double cleaningTotal = 0.00, taxRate = 0.00,
               taxAmount = 0.00, netPrice = 0.00;
    
        // Retrieve the unit price of this item
        // Just in case the user types an invalid value,
        // we are using a try...catch
        try
        {
            unitPriceShirts = double.Parse(txtShirtsUnitPrice.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("The value you entered for the price of " +
                            "shirts is not valid" +
                            "\nPlease try again");
            return;
        }
    
        // Retrieve the number of this item
        // Just in case the user types an invalid value,
        // we are using a try...catch
        try
        {
            qtyShirts = int.Parse(txtShirtsQuantity.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("The value you entered for the number of " +
                            "shirts is not valid" +
                            "\nPlease try again");
            return;
        }
    
                try
                {
                    unitPricePants = double.Parse(txtPantsUnitPrice.Text);
                }
                catch (FormatException)
                {
                    MessageBox.Show("The value you entered for the price of " +
                                    "pants is not valid" +
                                    "\nPlease try again");
                    return;
                }
    
                try
                {
                    qtyPants = int.Parse(txtPantsQuantity.Text);
                }
                catch (FormatException)
                {
                    MessageBox.Show("The value you entered for the number of " +
                                    "pants is not valid" +
                                    "\nPlease try again");
                    return;
                }
    
                if ((cbxItem1Name.Text == "None") ||
                (cbxItem1Name.Text == ""))
                {
                    qtyItem1 = 0;
                    unitPriceItem1 = 0.00;
                }
                else
                {
                    try
                    {
                        unitPriceItem1 = double.Parse(txtItem1UnitPrice.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered for the price is not valid" +
                                        "\nPlease try again");
                        return;
                    }
    
                    try
                    {
                        qtyItem1 = int.Parse(txtItem1Quantity.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered is not valid" +
                                        "\nPlease try again");
                        return;
                    }
                }
    
                if ((cbxItem2Name.Text == "None") ||
                (cbxItem2Name.Text == ""))
                {
                    qtyItem2 = 0;
                    unitPriceItem2 = 0.00;
                }
                else
                {
                    try
                    {
                        unitPriceItem2 = double.Parse(txtItem2UnitPrice.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered for " +
                            "the price is not valid" +
                                        "\nPlease try again");
                        return;
                    }
    
                    try
                    {
                        qtyItem2 = int.Parse(txtItem2Quantity.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered is not valid" +
                                        "\nPlease try again");
                        return;
                    }
                }
    
                if ((cbxItem3Name.Text == "None") ||
                (cbxItem3Name.Text == ""))
                {
                    qtyItem3 = 0;
                    unitPriceItem3 = 0.00;
                }
                else
                {
                    try
                    {
                        unitPriceItem3 = double.Parse(txtItem3UnitPrice.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered for the " +
                            "price is not valid" +
                                        "\nPlease try again");
                        return;
                    }
    
                    try
                    {
                        qtyItem3 = int.Parse(txtItem3Quantity.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered is not valid" +
                                        "\nPlease try again");
                        return;
                    }
                }
    
                if ((cbxItem4Name.Text == "None") || (cbxItem4Name.Text == ""))
                {
                    qtyItem4 = 0;
                    unitPriceItem4 = 0.00;
                }
                else
                {
                    try
                    {
                        unitPriceItem4 = double.Parse(txtItem4UnitPrice.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered for the price is not valid" +
                                "\nPlease try again");
                        return;
                    }
                    try
                    {
                        qtyItem4 = int.Parse(txtItem4Quantity.Text);
                    }
                    catch (FormatException)
                    {
                        MessageBox.Show("The value you entered is not valid" +
                                "\nPlease try again");
                        return;
                    }
                }
    
                // Calculate the sub-total for this item
                subTotalShirts = qtyShirts * unitPriceShirts;
                subTotalPants = qtyPants * unitPricePants;
                subTotalItem1 = qtyItem1 * unitPriceItem1;
                subTotalItem2 = qtyItem2 * unitPriceItem2;
                subTotalItem3 = qtyItem3 * unitPriceItem3;
                subTotalItem4 = qtyItem4 * unitPriceItem4;
    
                // Calculate the total based on sub-totals
                cleaningTotal = subTotalShirts + subTotalPants + subTotalItem1 +
                        subTotalItem2 + subTotalItem3 + subTotalItem4;
    
                taxRate = double.Parse(txtTaxRate.Text);
                // Calculate the amount owed for the taxes
                taxAmount = cleaningTotal * taxRate / 100;
                // Add the tax amount to the total order
                netPrice = cleaningTotal + taxAmount;
    
                // Display the sub-total in the corresponding text box
                txtShirtsSubTotal.Text = subTotalShirts.ToString("F");
                txtPantsSubTotal.Text = subTotalPants.ToString("F");
                txtItem1SubTotal.Text = subTotalItem1.ToString("F");
                txtItem2SubTotal.Text = subTotalItem2.ToString("F");
                txtItem3SubTotal.Text = subTotalItem3.ToString("F");
                txtItem4SubTotal.Text = subTotalItem4.ToString("F");
    
                txtCleaningTotal.Text = cleaningTotal.ToString("F");
                txtTaxAmount.Text = taxAmount.ToString("F");
                txtNetPrice.Text = netPrice.ToString("F");
    
                SaveCleaningOrder();
    }
  53. Return to the form and double-click the Close button
  54. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  55. Execute the application
  56. Create a few employees. Here are a few examples:
     
    Georgetown Cleaning Services - Employees
  57. Create a few customers. Here are some examples:
     
    Georgetown Cleaning Services - Customers
  58. Create a few cleaning orders. Here are examples:
     
    Georgetown Cleaning Services - Cleaning Orders
      
    Georgetown Cleaning Services - Cleaning Orders

  59. Close the form and return to your programming environment
  60. Execute the application again and open a few previously saved cleaning orders
  61. After opening an order, update it. Here are examples:
     
    Georgetown Cleaning Services - Cleaning Orders
      
    Georgetown Cleaning Services - Cleaning Orders
  62. Close the form and return to your programming environment

 

 

Published on Thursday 10 January 2008

 

Home Copyright © 2007 FunctionX, Inc.