Home

Data Binding

 

.NET Framework Data Binding Objects

 

Introduction

To present data to the user, we can use some familiar objects such as the data grid view, the text box, and the combo box. Although the data grid view is the most complex, one of the most complete, and one of the most aesthetic controls of the .NET Framework, it cannot suit every possible scenario. Besides, the data grid view is a .NET object, not a Win32 control. As you may know already from your familiarity with Microsoft Windows, the operating system provides many more controls than that. Most users are more familiar with those controls and they would appreciate if the database is presented to them through these controls. The Windows controls in the .NET Framework were created to suit various types of controls, not just databases. Because they were made as broad as possible, the Windows controls are not readily made to display data. They need an intermediary object that can transmit data to them.

Using a Data Reader

In Lesson 18, we saw that you could use a data reader to read the value of a table. Once the values have been read, to access these values, you first call the Read() method of the class. The values read by the data adapter are stored in an indexed property of the class. When accessing each value, you must remember the order in which they are listed in the table.

As you read and access each value by its index, you can retrieve it and do what you want with it. For example you can assign it to a Windows control to display to the user. Here is an example:

private void btnLocate_Click(object sender, EventArgs e)
{
    using (SqlConnection Connect =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=SSPI;"))
    {
	string strItems =
	    "SELECT * FROM StoreItems WHERE [Stock Number] = '1482';";
	SqlCommand cmdEmployees =
		new SqlCommand(strItems, Connect);

	Connect.Open();

	SqlDataReader rdr = cmdEmployees.ExecuteReader();

	while (rdr.Read())
	    txtMerchandiseDescription.Text = rdr[1].ToString();
    }
}

When reading the records of a table, as mentioned already, the data reader reads one record at a time and moves to the next. Before moving to the next record, you can access the values stored in the current record. To help with this, the columns of the table being read are stored in a collection and each column can be referred to with a numeric index. The first column has an index of 1. The second column has an index of 2, and so on. To retrieve the actual data stored in a column, you may need to know the type of information the column is holding so you can read it accurately.

Depending on the data type that a column was created with, you can access it as follows:

If the column holds the following data type Use the following method System.Data.SqlTypes Equivalent
bit GetBoolean()   GetSqlBoolean()
char, nchar GetChar()   GetSqlChar()
varchar, nvarchar GetString()   GetSqlString()
text, ntext GetString()   GetSqlString()
binary, varbinary GetBinary()   GetSqlBinary()
decimal GetDecimal() GetDouble() GetSqlDecimal()
float GetFloat()   GetSqlSingle()
int GetInt32()   GetSqlInt32()
money, smallmoney GetDecimal()   GetSqlDecimal()
bigint GetInt64()   GetSqlInt64()
datetime, smalldatetime GetDateTime()   GetSqlDateTime()
smallint, tinyint GetInt16() GetByte() GetSqlInt16()
 

When using one of the Get... or GetSql... methods, the compiler does not perform any conversion. This means that, before sending the data, you must convert the value read to the appropriate (and probably exact) format. For example, if you read a natural number from a column created with the tinyint data type, even though C# allows a short to be implicitly converted to an int, the compiler you use for your application would not perform or assume the conversion. For example, the value of a column created with tinyint must be read with GetByte() or GetSqlByte() and trying to use GetInt32() or GetSqlInt32() would throw an error.

Practical LearningPractical Learning: Using a Data Reader 

  1. Start Microsoft Visual C# and create a new Windows Application named CollegeParkAutoRepair4
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type RepairOrders.cs and press Enter twice
  4. Design the form as follows:
     
    College Park Auto Repair
     
    Control Name Text Additional Properties
    GroupBox GroupBox   Order Identification  
    Label Label   Customer Name:  
    TextBox TextBox txtCustomerName    
    Label Label    Address:  
    TextBox TextBox txtAddress    
    Label Label   City:  
    TextBox TextBox txtCity    
    Label Label   State:  
    TextBox TextBox txtState    
    Label Label   ZIP Code:  
    TextBox TextBox txtZIPCode    
    Label Label   Make/Model:  
    TextBox TextBox txtMake    
    TextBox TextBox txtModel    
    Label Label   Year:  
    TextBox TextBox txtYear    
    Label Label   Problem Description:  
    TextBox TextBox txtProblemDescription   Scrollbars: Vertical
    Multiline: True
    GroupBox GroupBox   Parts Used  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    TextBox TextBox txtPart1Name    
    TextBox TextBox txtUnitPrice1 0.00 TextAlign: Right
    TextBox TextBox txtQuantity1 0 TextAlign: Right
    TextBox TextBox txtSubTotal1 0.00 TextAlign: Right
    TextBox TextBox txtPart2Name    
    TextBox TextBox txtUnitPrice2 0.00 TextAlign: Right
    TextBox TextBox txtQuantity2 0 TextAlign: Right
    TextBox TextBox txtSubTotal2 0.00 TextAlign: Right
    TextBox TextBox txtPart3Name    
    TextBox TextBox txtUnitPrice3 0.00 TextAlign: Right
    TextBox TextBox txtQuantity3 0 TextAlign: Right
    TextBox TextBox txtSubTotal3 0.00 TextAlign: Right
    TextBox TextBox txtPart4Name    
    TextBox TextBox txtUnitPrice4 0.00 TextAlign: Right
    TextBox TextBox txtQuantity4 0 TextAlign: Right
    TextBox TextBox txtSubTotal4 0.00 TextAlign: Right
    TextBox TextBox txtPart5Name    
    TextBox TextBox txtUnitPrice5 0.00 TextAlign: Right
    TextBox TextBox txtQuantity5 0 TextAlign: Right
    TextBox TextBox txtSubTotal5 0.00 TextAlign: Right
    GroupBox GroupBox   Jobs Performed  
    Label Label   Job Description  
    Label Label   Price  
    TextBox TextBox txtJobDescription1    
    TextBox TextBox txtJobPrice1 0.00 TextAlign: Right
    TextBox TextBox txtJobDescription2    
    TextBox TextBox txtJobPrice2 0.00 TextAlign: Right
    TextBox TextBox txtJobDescription3    
    TextBox TextBox txtJobPrice3 0.00 TextAlign: Right
    TextBox TextBox txtJobDescription4    
    TextBox TextBox txtJobPrice4 0.00 TextAlign: Right
    TextBox TextBox txtJobDescription5    
    TextBox TextBox txtJobPrice5 0.00 TextAlign: Right
    GroupBox GroupBox   Order Summary   
    Label  Label    Total Parts:   
    TextBox TextBox txtTotalParts 0.00 TextAlign: Right
    Label   Label    Total Labor:   
    TextBox TextBox txtTotalLabor 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   Total Order:  
    TextBox TextBox txtTotalOrder 0.00 TextAlign: Right
    Label Label   Recommendations:  
    TextBox TextBox txtRecommendations   Scrollbars: Vertical
    Multiline: True 
    Button Button btnSave Save  
    Label Label   Receipt #:  
    TextBox TextBox txtReceiptNumber    
    Button Button btnOpen Open  
    Button Button btnNewRepairOrder New Repair Order  
    Button Button btnClose Close  
  5. Double-click an unoccupied area of the form and, to create the database, implement the event as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace CollegeParkAutoRepair4
    {
        public partial class RepairOrders : Form
        {
    	public RepairOrders()
        	{
    	    InitializeComponent();
    	}
    
    	void CreateDatabase()
    	{
    	    using (SqlConnection conCPAR =
    		new SqlConnection("Data Source=(local); " +
    				  "Integrated Security='SSPI';"))
    	    {
    		string strCreateDatabase =
    		    "CREATE DATABASE CollegeParkAutoRepair1";
    
    		SqlCommand cmdCPAR = 
    		    new SqlCommand(strCreateDatabase,
    						    conCPAR);
    
    		conCPAR.Open();
    		cmdCPAR.ExecuteNonQuery();
    
    		MessageBox.Show("A database named " +
    		    	"CollegeParkAutoRepair1 has been created");
    	    }
    
    	    using (SqlConnection conCPAR =
    		new SqlConnection("Data Source=(local); " +
    			"Database='CollegeParkAutoRepair1'; " +
    			"Integrated Security='SSPI';"))
    	    {
    		string strCreateTable =
    		    "CREATE TABLE RepairOrders( " +
    		    "RepairOrderID int identity(100001, 1) NOT NULL, " +
    		    "CustomerName varchar(80) NOT NULL, " +
    		    "Address varchar(100) NOT NULL, " +
    		    "City varchar(50), State varchar(50), " +
    		    "ZIPCode varchar(50), CarMake varchar(50), " +
    		    "CarModel varchar(50), CarYear smallint, " +
    		    "ProblemDescription text, Part1Name varchar(80), " +
    		    "Part1UnitPrice money, " +
    		    "Part1Quantity tinyint, " +
    		    "Part1SubTotal money, " +
    		    "Part2Name varchar(80), " +
    		    "Part2UnitPrice money, " +
    		    "Part2Quantity tinyint, " +
    		    "Part2SubTotal money, " +
    		    "Part3Name varchar(80), " +
    		    "Part3UnitPrice money, " +
    		    "Part3Quantity tinyint, " +
    		    "Part3SubTotal money, " +
    		    "Part4Name varchar(80), " +
    		    "Part4UnitPrice money, " +
    		    "Part4Quantity tinyint, " +
    		    "Part4SubTotal money, " +
    		    "Part5Name varchar(80), " +
    		    "Part5UnitPrice money, " +
    		    "Part5Quantity tinyint, " +
    		    "Part5SubTotal money, " +
    		    "Job1Description varchar(80), " +
    		    "Job1Price money, " +
    		    "Job2Description varchar(80), " +
    		    "Job2Price money, " +
    		    "Job3Description varchar(80), " +
    		    "Job3Price money, " +
    		    "Job4Description varchar(80), " +
    		    "Job4Price money, " +
    		    "Job5Description varchar(80), " +
    		    "Job5Price money, " +
    		    "TotalParts money, " +
    		    "TotalLabor money, " +
    		    "TaxRate decimal(6,2), " +
    		    "TaxAmount money, " +
    		    "TotalOrder money, " +
    		    "Recommendations text, " +
    	"CONSTRAINT PK_RepairOrders PRIMARY KEY (RepairOrderID));";
    
    	SqlCommand cmdCPAR = new SqlCommand(strCreateTable, conCPAR);
    
    		conCPAR.Open();
    	    	cmdCPAR.ExecuteNonQuery();
    MessageBox.Show("A table named RepairOrders has been created");
    	    }
        	}
    
    	private void RepairOrders_Load(object sender, EventArgs e)
    	{
    	    CreateDatabase();
        	}
        }
    }
  6. Execute the application to create the database and its table
  7. Close the form and return to your programming environment
  8. On the form, double-click the New Repair Order button and implement its event as follows:
     
    private void btnNewRepairOrder_Click(object sender, EventArgs e)
    {
        // This code is used to reset the form
        txtReceiptNumber.Text = "";
    
        txtCustomerName.Text = "";
        txtAddress.Text = ""; txtCity.Text = "";
        txtState.Text = ""; txtZIPCode.Text = "";
    
        txtMake.Text = ""; txtModel.Text = "";
        txtCarYear.Text = ""; txtProblemDescription.Text = "";
    
        txtPart1Name.Text = ""; txtUnitPrice1.Text = "0.00";
        txtQuantity1.Text = "0"; txtSubTotal1.Text = "0.00";
    
        txtPart2Name.Text = ""; txtUnitPrice2.Text = "0.00";
        txtQuantity2.Text = "0"; txtSubTotal2.Text = "0.00";
    
        txtPart3Name.Text = ""; txtUnitPrice3.Text = "0.00";
        txtQuantity3.Text = "0"; txtSubTotal3.Text = "0.00";
    
        txtPart4Name.Text = ""; txtUnitPrice4.Text = "0.00";
        txtQuantity4.Text = "0"; txtSubTotal4.Text = "0.00";
    
        txtPart5Name.Text = ""; txtUnitPrice5.Text = "0.00";
        txtQuantity5.Text = "0"; txtSubTotal5.Text = "0.00";
    
        txtJobDescription1.Text = ""; txtJobPrice1.Text = "0.00";
        txtJobDescription2.Text = ""; txtJobPrice2.Text = "0.00";
        txtJobDescription3.Text = ""; txtJobPrice3.Text = "0.00";
        txtJobDescription4.Text = ""; txtJobPrice4.Text = "0.00";
        txtJobDescription5.Text = ""; txtJobPrice5.Text = "0.00";
    
        txtRecommendations.Text = ""; txtTotalParts.Text = "0.00";
        txtTotalLabor.Text = "0.00"; txtTaxRate.Text = "7.75";
        txtTaxAmount.Text = "0.00"; txtTotalLabor.Text = "0.00";
    
        txtCustomerName.Focus();
    }
  9. Change to Load event as follows:
     
    private void RepairOrders_Load(object sender, EventArgs e)
    {
        btnNewRepairOrder_Click(sender, e);
    }
  10. Return to the form and click the first unit price text box
  11. Press and hold Shift
  12. Click the other unit price text boxes
  13. Click each text box under the Qty label
  14. Click each text box under Price (in the Jobs Performed group box)
  15. Click the Tax Rate text box
  16. Release Shift
  17. In the Properties window, click the Events button and double-click Leave
  18. Implement the event as follows:
     
    internal void CalculateOrder()
    {
        double UnitPrice1 = 0.00, UnitPrice2 = 0.00,
    	   UnitPrice3 = 0.00, UnitPrice4 = 0.00,
    	   UnitPrice5 = 0.00,
    	   SubTotal1, SubTotal2, SubTotal3,
    	   SubTotal4, SubTotal5, TotalParts;
        int Quantity1 = 0, Quantity2 = 0, Quantity3 = 0,
    	Quantity4 = 0, Quantity5 = 0;
        double JobPrice1 = 0.00, JobPrice2 = 0.00, JobPrice3 = 0.00,
    	   JobPrice4 = 0.00, JobPrice5 = 0.00, TotalLabor = 0.00;
        double TaxAmount, TotalOrder, TaxRate = 0.00;
    
        // Don't charge a part unless it is clearly identified
        if( txtPart1Name.Text == "")
        {
    	txtUnitPrice1.Text = "0.00";
    	txtQuantity1.Text = "0";
    	txtSubTotal1.Text = "0.00";
    	UnitPrice1 = 0.00;
        }
        else
        {
    	try
            {
    	    UnitPrice1 = double.Parse(this.txtUnitPrice1.Text);
        	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Unit Price");
    	    txtUnitPrice1.Text = "0.00";
    	    txtUnitPrice1.Focus();
    	}
    
    	try
    	{
    	    Quantity1 = int.Parse(this.txtQuantity1.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Quantity");
    	    txtQuantity1.Text = "0";
    	    txtQuantity1.Focus();
    	}
        }
    
        if (this.txtPart2Name.Text == "")
        {
    	txtUnitPrice2.Text = "0.00";
    	txtQuantity2.Text = "0";
    	txtSubTotal2.Text = "0.00";
    	UnitPrice2 = 0.00;
        }
        else
        {
    	try
        	{
    	    UnitPrice2 = double.Parse(this.txtUnitPrice2.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Unit Price");
    	    txtUnitPrice2.Text = "0.00";
    	    txtUnitPrice2.Focus();
    	}
    
    	try
    	{
    	    Quantity2 = int.Parse(this.txtQuantity2.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Quantity");
    	    txtQuantity2.Text = "0";
    	    txtQuantity2.Focus();
    	}
        }
    
        if (this.txtPart3Name.Text == "")
        {
    	txtUnitPrice3.Text = "0.00";
    	txtQuantity3.Text = "0";
    	txtSubTotal3.Text = "0.00";
    	UnitPrice3 = 0.00;
        }
        else
        {
    	try
    	{
    	    UnitPrice3 = double.Parse(this.txtUnitPrice3.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Unit Price");
    	    txtUnitPrice3.Text = "0.00";
    	    txtUnitPrice3.Focus();
    	}
    
    	try
    	{
    	    Quantity3 = int.Parse(this.txtQuantity3.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Quantity");
    	    txtQuantity3.Text = "0";
    	    txtQuantity3.Focus();
    	}
        }
    
        if (this.txtPart4Name.Text == "")
        {
    	txtUnitPrice4.Text = "0.00";
    	txtQuantity4.Text = "0";
    	txtSubTotal4.Text = "0.00";
    	UnitPrice4 = 0.00;
        }
        else
        {
    	try
    	{
    	    UnitPrice4 = double.Parse(this.txtUnitPrice4.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Unit Price");
    	    txtUnitPrice4.Text = "0.00";
    	    txtUnitPrice4.Focus();
    	}
    
    	try
    	{
    	    Quantity4 = int.Parse(this.txtQuantity4.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Quantity");
    	    txtQuantity4.Text = "0";
    	    txtQuantity4.Focus();
    	}
        }
    
        if (txtPart5Name.Text == "")
        {
    	txtUnitPrice5.Text = "0.00";
    	txtQuantity5.Text = "0";
    	txtSubTotal5.Text = "0.00";
    	UnitPrice5 = 0.00;
        }
        else
        {
    	try
    	{
    	    UnitPrice5 = double.Parse(this.txtUnitPrice5.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Unit Price");
    	    txtUnitPrice5.Text = "0.00";
    	    txtUnitPrice5.Focus();
    	}
    
    	try
    	{
    	    Quantity5 = int.Parse(this.txtQuantity5.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Quantity");
    	    txtQuantity5.Text = "0";
    	    txtQuantity5.Focus();
    	}
        }
    
        // Don't bill the customer for a job that is not specified
        if( txtJobDescription1.Text == "" )
        {
    	txtJobPrice1.Text = "0.00";
    	JobPrice1 = 0.00;
        }
        else
        {
    	try
    	{
    	    JobPrice1 = double.Parse(this.txtJobPrice1.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Job Price");
    	    txtJobPrice1.Text = "0.00";
    	    txtJobPrice1.Focus();
    	}
        }
    
        if (txtJobDescription2.Text == "")
        {
    	txtJobPrice2.Text = "0.00";
    	JobPrice2 = 0.00;
        }
        else
        {
    	try
    	{
    	    JobPrice2 = double.Parse(this.txtJobPrice2.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Job Price");
    	    txtJobPrice2.Text = "0.00";
    	    txtJobPrice2.Focus();
    	}
        }
    
        if (txtJobDescription3.Text == "")
        {
    	txtJobPrice3.Text = "0.00";
    	JobPrice3 = 0.00;
        }
        else
        {
    	try
    	{
    	    JobPrice3 = double.Parse(this.txtJobPrice3.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Job Price");
    	    txtJobPrice3.Text = "0.00";
    	    txtJobPrice3.Focus();
    	}
        }
    
        if (txtJobDescription4.Text == "")
        {
    	txtJobPrice4.Text = "0.00";
    	JobPrice4 = 0.00;
        }
        else
        {
    	try
    	{
    	    JobPrice4 = double.Parse(this.txtJobPrice4.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Job Price");
    	    txtJobPrice4.Text = "0.00";
    	    txtJobPrice4.Focus();
    	}
        }
    
        if (txtJobDescription5.Text == "")
        {
    	txtJobPrice5.Text = "0.00";
    	JobPrice5 = 0.00;
        }
        else
        {
    	try
    	{
    	    JobPrice5 = double.Parse(this.txtJobPrice5.Text);
    	}
    	catch (FormatException)
    	{
    	    MessageBox.Show("Invalid Job Price");
    	    txtJobPrice5.Text = "0.00";
    	    txtJobPrice5.Focus();
    	}
        }
    
        SubTotal1 = UnitPrice1 * Quantity1;
        SubTotal2 = UnitPrice2 * Quantity2;
        SubTotal3 = UnitPrice3 * Quantity3;
        SubTotal4 = UnitPrice4 * Quantity4;
        SubTotal5 = UnitPrice5 * Quantity5;
    
        txtSubTotal1.Text = SubTotal1.ToString("F");
        txtSubTotal2.Text = SubTotal2.ToString("F");
        txtSubTotal3.Text = SubTotal3.ToString("F");
        txtSubTotal4.Text = SubTotal4.ToString("F");
        txtSubTotal5.Text = SubTotal5.ToString("F");
    
        TotalParts = SubTotal1 + SubTotal2 + SubTotal3 +
    		 SubTotal4 + SubTotal5;
    
        TotalLabor = JobPrice1 + JobPrice2 + JobPrice3 +
    		 JobPrice4 + JobPrice5;
    
        try
        {
    	TaxRate = double.Parse(this.txtTaxRate.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid Tax Rate");
    	txtTaxRate.Text = "7.75";
    	txtTaxRate.Focus();
        }
    
        double TotalPartsAndLabor = TotalParts + TotalLabor;
        TaxAmount = TotalPartsAndLabor * TaxRate / 100;
        TotalOrder = TotalPartsAndLabor + TaxAmount;
    
        txtTotalParts.Text = TotalParts.ToString("F");
        txtTotalLabor.Text = TotalLabor.ToString("F");
        txtTaxAmount.Text = TaxAmount.ToString("F");
        txtTotalOrder.Text = TotalOrder.ToString("F");
    }
    
    private void txtUnitPrice1_Leave(object sender, EventArgs e)
    {
        CalculateOrder();
    } 
  19. Return to the form and double-click the Save button
  20. Implement its event as follows:
     
    private void btnSave_Click(object sender, EventArgs e)
    {
        string strCommand = "";
    
        using (SqlConnection cnnRepairOrders =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='CollegeParkAutoRepair1';" +
    		 	  "Integrated Security=SSPI;"))
        {
    	// If the Receipt Number is empty, it appears that
    	// the user/clerk wants to create a new cleaning order
    	if( txtReceiptNumber.Text == "" )
    	{
    	    strCommand = "INSERT INTO RepairOrders( " +
    		"CustomerName, Address, City, " +
    		"State, ZIPCode, CarMake, " +
    		"CarModel, CarYear, ProblemDescription, " +
    		"Part1Name, Part1UnitPrice, Part1Quantity, " +
    		"Part1SubTotal, Part2Name, Part2UnitPrice, " +
    		"Part2Quantity, Part2SubTotal, Part3Name, " +
    		"Part3UnitPrice, Part3Quantity, Part3SubTotal, " +
    		"Part4Name, Part4UnitPrice, Part4Quantity, " +
    		"Part4SubTotal, Part5Name, Part5UnitPrice, " +
    		"Part5Quantity, Part5SubTotal, Job1Description, " +
    		"Job1Price, Job2Description, Job2Price, " +
    		"Job3Description, Job3Price, Job4Description, " +
    		"Job4Price, Job5Description, Job5Price, " +
    		"Recommendations, TotalParts, TotalLabor, " +
    		"TaxRate, TaxAmount, TotalOrder) " +
    		"VALUES('" + txtCustomerName.Text + "', '" +
    		txtAddress.Text + "', '" + txtCity.Text +
    		"', '" + txtState.Text + "', '" +
    		txtZIPCode.Text + "', '" + txtMake.Text +
    		"', '" + txtModel.Text + "', '" +
    		txtCarYear.Text + "', '" +
    		txtProblemDescription.Text + "', '" +
    	txtPart1Name.Text + "', '" + txtUnitPrice1.Text + "', '" +
    	txtQuantity1.Text + "', '" + txtSubTotal1.Text + "', '" +
    	txtPart2Name.Text + "', '" + txtUnitPrice2.Text + "', '" +
    	txtQuantity2.Text + "', '" + txtSubTotal2.Text + "', '" +
    	txtPart3Name.Text + "', '" + txtUnitPrice3.Text + "', '" +
    	txtQuantity3.Text + "', '" + txtSubTotal3.Text + "', '" +
    	txtPart4Name.Text + "', '" + txtUnitPrice4.Text + "', '" +
    	txtQuantity4.Text + "', '" + txtSubTotal4.Text + "', '" +
    	txtPart5Name.Text + "', '" + txtUnitPrice5.Text + "', '" +
    	txtQuantity5.Text + "', '" + txtSubTotal5.Text + "', '" +
    	txtJobDescription1.Text + "', '" + txtJobPrice1.Text + "', '" +
    	txtJobDescription2.Text + "', '" + txtJobPrice2.Text + "', '" +
    	txtJobDescription3.Text + "', '" + txtJobPrice3.Text + "', '" +
    	txtJobDescription4.Text + "', '" + txtJobPrice4.Text + "', '" +
    	txtJobDescription5.Text + "', '" + txtJobPrice5.Text + "', '" +
    	txtRecommendations.Text + "', '" + txtTotalParts.Text + "', '" +
    	txtTotalLabor.Text + "', '" + txtTaxRate.Text + "', '" +
    	txtTaxAmount.Text + "', '" + txtTotalOrder.Text + "');";
    	}
    	else // Since there is a receipt number, update/edit the cleaning order
    	{
    	    strCommand = "UPDATE CleaningOrders " +
    		"SET CustomerName = '" + txtCustomerName.Text + "', " +
    		"    Address = '" + txtAddress.Text + "', " +
    		"    City = '" + txtCity.Text + "', " +
    		"    State = '" + txtState.Text + "', " +
    		"    ZIPCode = '" + txtZIPCode.Text + "', " +
    		"    CarMake = '" + txtMake.Text + "', " +
    		"    CarModel = '" + txtModel.Text + "', " +
    		"    CarYear = '" + txtCarYear.Text + "', " +
    	" ProblemDescription = '" + txtProblemDescription.Text + "', " +
    		"    Part1Name = '" + txtPart1Name.Text + "', " +
    		"    Part1UnitPrice = '" + txtUnitPrice1.Text + "', " +
    		"    Part1Quantity = '" + txtQuantity1.Text + "', " +
    		"    Part1SubTotal = '" + txtSubTotal1.Text + "', " +
    		"    Part2Name = '" + txtPart2Name.Text + "', " +
    		"    Part2UnitPrice = '" + txtUnitPrice2.Text + "', " +
    		"    Part2Quantity = '" + txtQuantity2.Text + "', " +
    		"    Part2SubTotal = '" + txtSubTotal2.Text + "', " +
    		"    Part3Name = '" + txtPart3Name.Text + "', " +
    		"    Part3UnitPrice = '" + txtUnitPrice3.Text + "', " +
    		"    Part3Quantity = '" + txtQuantity3.Text + "', " +
    		"    Part3SubTotal = '" + txtSubTotal3.Text + "', " +
    		"    Part4Name = '" + txtPart4Name.Text + "', " +
    		"    Part4UnitPrice = '" + txtUnitPrice4.Text + "', " +
    		"    Part4Quantity = '" + txtQuantity4.Text + "', " +
    		"    Part4SubTotal = '" + txtSubTotal4.Text + "', " +
    		"    Part5Name = '" + txtPart5Name.Text + "', " +
    		"    Part5UnitPrice = '" + txtUnitPrice5.Text + "', " +
    		"    Part5Quantity = '" + txtQuantity5.Text + "', " +
    		"    Part5SubTotal = '" + txtSubTotal5.Text + "', " +
    		"    Job1Description = '" + txtJobDescription1.Text + "', " +
    		"    Job1Price = '" + txtJobDescription1.Text + "', " +
    		"    Job2Description = '" + txtJobDescription2.Text + "', " +
    		"    Job2Price = '" + txtJobDescription2.Text + "', " +
    		"    Job3Description = '" + txtJobDescription3.Text + "', " +
    		"    Job3Price = '" + txtJobDescription3.Text + "', " +
    		"    Job4Description = '" + txtJobDescription4.Text + "', " +
    		"    Job4Price = '" + txtJobDescription4.Text + "', " +
    		"    Job5Description = '" + txtJobDescription5.Text + "', " +
    		"    Job5Price = '" + txtJobDescription5.Text + "', " +
    		"    TotalParts = '" + txtTotalParts.Text + "', " +
    		"    TotalLabor = '" + txtTotalLabor.Text + "', " +
    		"    TaxRate = '" + txtTaxRate.Text + "', " +
    		"    TaxAmount = '" + txtTaxAmount.Text + "', " +
    		"    TotalOrder = '" + txtTotalOrder.Text + "' " +
    		"    WHERE RepairOrderID = '" + txtReceiptNumber.Text + "';";
    	}
    
    	SqlCommand cmdCleaningOrders = new SqlCommand(strCommand,
    						      cnnRepairOrders);
    
    	cnnRepairOrders.Open();
    	cmdCleaningOrders.ExecuteNonQuery();
    
    	btnNewRepair_Click(sender, e);
        }
    } 
  21. Return to the form and double-click the Open button
  22. Implement its event as follows:
     
    private void btnOpen_Click(object sender, EventArgs e)
    {
        string strReceiptNumber = txtReceiptNumber.Text;
    
        if (strReceiptNumber.Length == 0 )
        {
    	MessageBox.Show("You open a repair order, " +
    			"enter its receipt number and click Open.");
    	return;
        }
    
        using (SqlConnection conDatabase = new
    	SqlConnection("Data Source=(local);" +
    		      "Database='CollegeParkAutoRepair1';" +
    		      "Integrated Security=yes"))
        {
    	string strFindRepair =
    	    "SELECT * FROM RepairOrders WHERE RepairOrderID = '" +
    	    strReceiptNumber + "'";
    	SqlCommand cmdDatabase = new
    	SqlCommand(strFindRepair, conDatabase);
    
    	conDatabase.Open();
    
    	SqlDataReader rdrRepairOrder;
    	rdrRepairOrder = cmdDatabase.ExecuteReader();
    
    	while (rdrRepairOrder.Read())
    	{
    	    txtCustomerName.Text = rdrRepairOrder.GetString(1);
    	    txtAddress.Text = rdrRepairOrder.GetString(2);
    	    txtCity.Text = rdrRepairOrder.GetString(3);
    	    txtState.Text = rdrRepairOrder.GetString(4);
    	    txtZIPCode.Text = rdrRepairOrder.GetString(5);
    	    txtMake.Text = rdrRepairOrder.GetString(6);
    	    txtModel.Text = rdrRepairOrder.GetString(7);
    	    txtCarYear.Text = 
    		rdrRepairOrder.GetSqlInt16(8).ToString();
    	    txtProblemDescription.Text = rdrRepairOrder.GetString(9);
    
    	    txtPart1Name.Text = rdrRepairOrder.GetString(10);
    	    txtUnitPrice1.Text = 
    		rdrRepairOrder.GetSqlMoney(11).ToString();
    	    txtQuantity1.Text = 
    		rdrRepairOrder.GetSqlByte(12).ToString();
    	    txtSubTotal1.Text = 
    		rdrRepairOrder.GetSqlMoney(13).ToString();
    
    	    txtPart2Name.Text = rdrRepairOrder.GetString(14);
    	    txtUnitPrice2.Text = 
    		rdrRepairOrder.GetSqlMoney(15).ToString();
    	    txtQuantity2.Text = 
    		rdrRepairOrder.GetSqlByte(16).ToString();
    	    txtSubTotal2.Text = 
    		rdrRepairOrder.GetSqlMoney(17).ToString();
    
    	    txtPart3Name.Text = rdrRepairOrder.GetString(18);
    	    txtUnitPrice3.Text = 
    		rdrRepairOrder.GetSqlMoney(19).ToString();
    	    txtQuantity3.Text = 
    		rdrRepairOrder.GetSqlByte(20).ToString();
    	    txtSubTotal3.Text = 
    		rdrRepairOrder.GetSqlMoney(21).ToString();
    
    	    txtPart4Name.Text = rdrRepairOrder.GetString(22);
    	    txtUnitPrice4.Text = 
    		rdrRepairOrder.GetSqlMoney(23).ToString();
    	    txtQuantity4.Text = 
    		rdrRepairOrder.GetSqlByte(24).ToString();
    	    txtSubTotal4.Text = 
    		rdrRepairOrder.GetSqlMoney(25).ToString();
    
    	    txtPart5Name.Text = rdrRepairOrder.GetString(26);
    	    txtUnitPrice5.Text = 
    		rdrRepairOrder.GetSqlMoney(27).ToString();
    	    txtQuantity5.Text = 
    		rdrRepairOrder.GetSqlByte(28).ToString();
    	    txtSubTotal5.Text = 
    		rdrRepairOrder.GetSqlMoney(29).ToString();
    
    	    txtJobDescription1.Text = rdrRepairOrder.GetString(30);
    	    txtJobPrice1.Text = 
    		rdrRepairOrder.GetSqlMoney(31).ToString();
    	    txtJobDescription2.Text = rdrRepairOrder.GetString(32);
    	    txtJobPrice2.Text = 
    		rdrRepairOrder.GetSqlMoney(33).ToString();
    	    txtJobDescription3.Text = rdrRepairOrder.GetString(34);
    	    txtJobPrice3.Text = 
    		rdrRepairOrder.GetSqlMoney(35).ToString();
    	    txtJobDescription4.Text = rdrRepairOrder.GetString(36);
    	    txtJobPrice4.Text = 
    		rdrRepairOrder.GetSqlMoney(37).ToString();
    	    txtJobDescription5.Text = rdrRepairOrder.GetString(38);
    	    txtJobPrice5.Text = 
    		rdrRepairOrder.GetSqlMoney(39).ToString();
    
    	    txtTotalParts.Text = 
    		rdrRepairOrder.GetSqlMoney(40).ToString();
    	    txtTotalLabor.Text = 
    		rdrRepairOrder.GetSqlMoney(41).ToString();
    	    txtTaxRate.Text = 
    		rdrRepairOrder.GetSqlDecimal(42).ToString();
    	    txtTaxAmount.Text = 
    		rdrRepairOrder.GetSqlMoney(43).ToString();
    	    txtTotalOrder.Text = 
    		rdrRepairOrder.GetSqlMoney(44).ToString();
    
    	    txtRecommendations.Text = rdrRepairOrder.GetString(45);
    	}
    
    	rdrRepairOrder.Close();
        }
    } 
  23. Return to the form and double-click the Close button
  24. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  25. Execute the application
  26. Create a few repair orders. Here are two examples:
     
    College Park Auto Repair: Repair Order
      
    College Park Auto Repair: Repair Order
  27. Close the form and return to your programming environment
  28. Execute the application again and open a few previously saved cleaning orders

Using a Data Adapter

In Lesson 31, we saw that you could 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. 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 GeorgetownDryCleaner5
  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.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace GeorgetownDryCleaner5
    {
        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 GeorgetownDryCleaner1";
    
    		SqlCommand cmdGCS = new SqlCommand(strCreateDatabase,
    						   conGCS);
    
    		conGCS.Open();
    	    	cmdGCS.ExecuteNonQuery();
    
    	        MessageBox.Show("A database named " + 
    		    "GeorgetownDryCleaner1 has been created");
    	   }
    
    	   using (SqlConnection conGCS =
    	   	new SqlConnection("Data Source=(local); " +
    			"Database='GeorgetownDryCleaner1'; " +
    				  "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='GeorgetownDryCleaner1'; " +
    			          "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='GeorgetownDryCleaner1'; " +
    			          "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.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace GeorgetownDryCleaner5
    {
        public partial class Employees : Form
        {
    	public Employees()
    	{
    	    InitializeComponent();
    	}
    
    	void ShowEmployees()
    	{
    	    using (SqlConnection Connect =
    		new SqlConnection("Data Source=(local);" +
    			"Database='GeorgetownDryCleaner1';" +
    			"Integrated Security=SSPI;"))
    	    {
    		string strEmployees =
    			"SELECT * FROM Employees;";
    		SqlCommand cmdEmployees =
    		    new SqlCommand(strEmployees, Connect);
    		SqlDataAdapter daEmployees = new SqlDataAdapter();
    
    		daEmployees.SelectCommand = cmdEmployees;
    		DataSet dsEmployees = new DataSet("EmployeesSet");
    		daEmployees.Fill(dsEmployees);
    
    		Connect.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 Connect =
    	new SqlConnection("Data Source=(local);" +
    		"Database='GeorgetownDryCleaner1';" +
    		"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,
    						 Connect);
    
    	Connect.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.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace GeorgetownDryCleaner5a
    {
        public partial class Customers : Form
        {
    	public Customers()
    	{
    	    InitializeComponent();
    	}
    
    	void ShowCustomers()
    	{
    	    using (SqlConnection cnnCustomers =
    		new SqlConnection("Data Source=(local);" +
    			"Database='GeorgetownDryCleaner1';" +
    			"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='GeorgetownDryCleaner1';" +
    			  "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 Connect =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='GeorgetownDryCleaner1';" +
    			  "Integrated Security=SSPI;"))
        {
    	string strEmployees =
    	    "SELECT FullName FROM Employees WHERE EmployeeNumber = '" +
    		txtEmployeeNumber.Text + "';";
    	SqlCommand cmdEmployees =
    		new SqlCommand(strEmployees, Connect);
    	SqlDataAdapter daEmployees = new SqlDataAdapter();
    
    	daEmployees.SelectCommand = cmdEmployees;
    	DataSet dsEmployees = new DataSet("EmployeesSet");
    	daEmployees.Fill(dsEmployees);
    
    	Connect.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='GeorgetownDryCleaner1';" +
    			  "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='GeorgetownDryCleaner1';" +
    		"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='GeorgetownDryCleaner1';" +
    			  "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

Using Visual Database Objects

 

Using a Binding Source

After specifying the data source, you can use the binding source because it then holds a list. If you want a singly list-based control such as a list box, a combo box or a checked list box to use the list, these controls are equipped with a property named DataSource. This allows you to apply the list of a binding source directly to the control. To do this, assign the binding source to the DataSource property of the control. Here is an example:

private void btnBindingSource_Click(object sender, EventArgs e)
{
    BindingSource bsNames = new BindingSource();
    List<string> strNames = new List<string>();

    strNames.Add("Vicky Bisso");
    strNames.Add("Amy Warren");
    strNames.Add("Chrissie Childs");
    strNames.Add("Martial Otto");

    bsNames.DataSource = strNames;
    lbxNames.DataSource = bsNames;
}

After this assignment, the control can display the list.

The Display Member of a List-Based Control

In some cases, the list is not single. For example, you may already know that a data set can contain one or more tables and a table can contain one or more columns. In this case, you must specify the sub-list or column that holds the values that you want the control to display. To support this scenario, the singly list-based controls are equipped with a property named DisplayMember.

Practical LearningPractical Learning: Binding Data With Visual Databases Objects

  1. Create a new Windows Application named BethesdaCarRental3
  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, to create the database including its tables, implement the event as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace BethesdaCarRental3a
    {
        public partial class Central : Form
        {
    	public Central()
    	{
    	    InitializeComponent();
    	}
    
    	void CreateDatabase()
    	{
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    				  "Integrated Security='SSPI';"))
    	    {
    		string strCreateDatabase = "IF EXISTS ( " +
    		    "SELECT name " +
    		    "FROM sys.databases " +
    		    "WHERE name = N'BethesdaCarRental2' " +
    		    ") " +
    		    "DROP DATABASE BethesdaCarRental2; " +
    		    "CREATE DATABASE BethesdaCarRental2";
    
    		SqlCommand cmdBCR = 
    		    new SqlCommand(strCreateDatabase, conBCR);
    
    		conBCR.Open();
    		cmdBCR.ExecuteNonQuery();
    
    		MessageBox.Show("A database named " +
    				"BethesdaCarRental2 has been created");
    	    }
    
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    			"Database='BethesdaCarRental2'; " +
    			"Integrated Security='SSPI';"))
    	    {
    		string strCreateTable = "CREATE TABLE RentalRates( " +
    			"RentalRateID int identity(1, 1) NOT NULL, " +
    			"Category varchar(50), " +
    			"Daily smallmoney, Weekly smallmoney, " +
    			"Monthly smallmoney, Weekend smallmoney, " +
    		"CONSTRAINT PK_RentalRates PRIMARY KEY (RentalRateID));";
    
    		SqlCommand cmdBCR = new SqlCommand(strCreateTable, conBCR);
    
    		conBCR.Open();
    		cmdBCR.ExecuteNonQuery();
    		MessageBox.Show("A table named RentalRates has been " +
    			"added to the BethesdaCarRental2 database.");
    	    }
    
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    				  "Database='BethesdaCarRental2'; " +
    				  "Integrated Security='SSPI';"))
    	    {
    		string strCreateTable = "CREATE TABLE Employees( " +
    			"EmployeeID int identity(1, 1) NOT NULL, " +
    			"EmployeeNumber nchar(5), " +
    			"FirstName varchar(32), " +
    			"LastName varchar(32) NOT NULL, " +
    			"FullName AS (([LastName]+', ')+[FirstName]), " +
    			"Title varchar(80), " +
    			"HourlySalary smallmoney, " +
    			"CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));";
    
    		SqlCommand cmdBCR = new SqlCommand(strCreateTable, conBCR);
    
    		conBCR.Open();
    		cmdBCR.ExecuteNonQuery();
    		MessageBox.Show("A table named Employees has been " +
    			"added to the BethesdaCarRental2 database.");
    	    }
    
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    			"Database='BethesdaCarRental2'; " +
    			"Integrated Security='SSPI';"))
    	    {
    		string strCreateTable = "CREATE TABLE Customers( " +
    			"CustomerID int identity(1, 1) NOT NULL, " +
    			"DrvLicNumber varchar(50), " +
    			"FullName varchar(80), " +
    			"Address varchar(100) NOT NULL, " +
    			"City varchar(50), " +
    			"State varchar(50), " +
    			"ZIPCode varchar(20), " +
    		    "CONSTRAINT PK_Customer PRIMARY KEY (CustomerID));";
    
    		SqlCommand cmdBCR = new SqlCommand(strCreateTable, conBCR);
    
    		conBCR.Open();
    		cmdBCR.ExecuteNonQuery();
    		MessageBox.Show("A table named Customers has been " +
    				"added to the BethesdaCarRental2 database.");
    	    }
    
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    			"Database='BethesdaCarRental2'; " +
    			"Integrated Security='SSPI';"))
    	    {
    		string strCreateTable = "CREATE TABLE Cars( " +
    			"CarID int identity(1, 1) NOT NULL, " +
    			"TagNumber varchar(20), " +
    			"Make varchar(50), " +
    			"Model varchar(50) NOT NULL, " +
    			"CarYear smallint, " +
    			"Category varchar(50), " +
    			"CDPlayer bit, " +
    			"DVDPlayer bit, " +
    			"Available bit, " +
    			"CONSTRAINT PK_Car PRIMARY KEY (CarID));";
    
    		SqlCommand cmdBCR = 
    		    new SqlCommand(strCreateTable, conBCR);
    
    		conBCR.Open();
    		cmdBCR.ExecuteNonQuery();
    		MessageBox.Show("A table named Cars has been " +
    			    "added to the BethesdaCarRental2 database.");
    	    }
    
    	    using (SqlConnection conBCR =
    		new SqlConnection("Data Source=(local); " +
    			"Database='BethesdaCarRental2'; " +
    			"Integrated Security='SSPI';"))
    	    {
    		string strCreateTable = "CREATE TABLE RentalOrders( " +
    			"RentalOrderID int identity(1, 1) NOT NULL, " +
    			"DateProcessed datetime, " +
    			"EmployeeID int Constraint " +
    			"FK_Employees References " +
    			"Employees(EmployeeID) NOT NULL, " +
    			"EmployeeName varchar(80), " +
    			"CustomerID int Constraint " +
    			"FK_Customers References " +
    			"Customers(CustomerID) NOT NULL, " +
    			"CustomerName varchar(80), " +
    			"CustomerAddress varchar(100), " +
    			"CustomerCity varchar(50), " +
    			"CustomerState varchar(50), " +
    			"CustomerZIPCode varchar(20), " +
    			"CarID int Constraint " +
    			"FK_Cars References Cars(CarID) NOT NULL, " +
    			"CarMake varchar(50), " +
    			"CarModel varchar(50), " +
    			"CarYear smallint, " +
    			"CarCondition varchar(50), " +
    			"TankLevel varchar(40), " +
    			"MileageStart int, " +
    			"MileageEnd int, " +
    			"RentStartDate datetime, " +
    			"RentEndDate datetime, " +
    			"Days int, " +
    			"RateApplied money, " +
    			"SubTotal money, " +
    			"TaxRate decimal(6, 2), " +
    			"TaxAmount money, " +
    			"OrderTotal money, " +
    			"OrderStatus varchar(50), " +
    			"CONSTRAINT PK_RentalOrder " +
    			"PRIMARY KEY (RentalOrderID));";
    
    	    	SqlCommand cmdBCR = new SqlCommand(strCreateTable, conBCR);
    
    	    	conBCR.Open();
    	    	cmdBCR.ExecuteNonQuery();
    	    	MessageBox.Show("A table named RentalOrder has been " +
    	 		      "added to the BethesdaCarRental2 database.");
    	    }
        	}
    
    	private void Central_Load(object sender, EventArgs e)
    	{
    	    CreateDatabase(); 
    	}
        }
    }
  5. Execute the application to actually create the database and its tables
  6. Close the form and return to your programming environment
  7. To create a new form, on the main menu, click Project -> Add Windows Form...
  8. Set the name to Employees and click Add
  9. To create a data source, on the main menu, click Data -> Add New Data Source...
  10. In the first page of the wizard, make sure Database is selected and click Next
  11. In the combo box
    1. If you see a BethesdaCarRental2, select it
    2. If you do not have BethesdaCarRental2, 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 BethesdaCarRental2. 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 csBethesdaCarRental and click Next. Expand the Tables node and expand the Employees node. Click the check boxes of EmployeeID, EmployeeNumber, FirstName, LastName, Title, and HourlySalary. Change the DataSet Name to dsBethesdaCarRental
       
      Data Source
  12. Click Finish
  13. In the Data Sources window, click Employees and click the arrow of its combo box to select DataGridView (it should be selected already as the default).
    Drag the Employees node and drop it on the form
  14. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    employeesBindingSource bsEmployees
    employeesTableAdapter taEmployees
    employeesBindingNavigator bnEmployees
  15. On the form, click the data grid view control and, in the Properties window, change its (Name) to dgvEmployees
  16. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    EmployeeID Empl ID 50
    EmployeeNumber Empl # 50
    FirstName First Name 65
    LastName Last Name 65
    Title   120
    HourlySalary Hourly Salary 75
  17. Click OK
  18. Set DataGridView's Anchor property to Top, Bottom, Left, Right
  19. Under the DataGridView control, add a button and set its properties as follows:
    Text: Close
    (Name): btnClose
    Anchor: Bottom, Right
     
    Bethesda Car Rental - Employees Records
  20. Double-click the Close button and implement its even as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
         Close();
    }
  21. Access the Central.cs [Design] tab
  22. Add a button to it and set its properties as follows:
    Text: Employees
    (Name): btnEmployees
  23. Double-click the Employees button and implement its even as follows:
     
    private void Central_Load(object sender, EventArgs e)
    {
        // CreateDatabase(); 
    }
    
    private void btnEmployees_Click(object sender, EventArgs e)
    {
        Employees clerks = new Employees();
        clerks.ShowDialog();
    }
  24. Execute the application to test it
  25. Click the Employees button and create the following records:
     
    Empl # First Name Last Name Title HourlySalary
    20725 Julie Flanell Regional Manager 36.55
    92705 Paulette Simms Sales Manager 26.65
    84002 Alexandra  Ulm Sales Representative 12.85
    47295 Ellie Tchenko Sales Representative 11.95
     
    Employees
  26. Close the forms and return to your programming environment
  27. To create a new form, on the main menu, click Project -> Add Windows Form...
  28. Set the name to Customers and click Add
  29. In the Data Sources window, right-click dsBethesdaCaRental and click Configure Dataset with Wizard...
  30. Expand the Tables node and click the check box of Customers
  31. Click Finish
  32. In the Data Sources window, drag Customers and drop it on the form
  33. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource bsCustomers
    customersTableAdapter taCustomers
    customersBindingNavigator bnCustomers
  34. On the form, click the data grid view control and, in the Properties window, change its (Name) to dgvCustomers
  35. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    CustomerID Cust ID 50
    DrvLicNumber Driver's Lic # 80
    FullName Full Name 120
    Address   120
    City   120
    State   40
    ZIPCode ZIP Code 60
  36. Click OK
  37. Set DataGridView's Anchor property to Top, Bottom, Left, Right
  38. Under the DataGridView control, add a button and set its properties as follows:
    Text: Close
    (Name): btnClose
    Anchor: Bottom, Right
     
    Bethesda Car Rental - Customers
  39. Double-click the Close button and implement its even as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
         Close();
    }
  40. Access the Central.cs [Design] tab
  41. Add a button to it and set its properties as follows:
    Text: Customers
    (Name): btnCustomers
  42. Double-click the Employees button and implement its even as follows:
     
    private void btnCustomers_Click(object sender, EventArgs e)
    {
        Customers clients = new Customers();
        clients.ShowDialog();
    } 
  43. Execute the application to test it
  44. Click the Customers button and create Create a few records
  45. Close the forms and return to your programming environment
  46. To create a new form, on the main menu, click Project -> Add Windows Form...
  47. Set the name to Cars and click Add
  48. In the Data Sources window, right-click dsBethesdaCaRental and click Configure Dataset with Wizard...
  49. Expand the Tables node and click the check box of Cars
  50. Click Finish
  51. In the Data Sources window, drag Cars and drop it on the form
  52. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    carsBindingSource bsCars
    carsTableAdapter taCars
    carsBindingNavigator bnCars
  53. On the form, click the data grid view control and, in the Properties window, change its (Name) to dgvCars
  54. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    CarID Car ID 50
    TagNumber Tag # 70
    Make   80
    Model   80
    CarYear Year 50
    Category   80
    CDPlayer CD Player 65
    DVDPlayer DVD Player 70
    Available   65
  55. Click OK
  56. Set DataGridView's Anchor property to Top, Bottom, Left, Right
  57. Under the DataGridView control, add a button and set its properties as follows:
    Text: Close
    (Name): btnClose
    Anchor: Bottom, Right
     
    Bethesda Car Rental - Car
  58. Double-click the Close button and implement its even as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
         Close();
    }
  59. Access the Central.cs [Design] tab
  60. Add a button to it and set its properties as follows:
    Text: Cars
    (Name): btnCars
  61. Double-click the Employees button and implement its even as follows:
     
    private void btnCars_Click(object sender, EventArgs e)
    {
        Cars vehicles = new Cars();
        vehicles.ShowDialog();
    } 
  62. Execute the application to test it
  63. Click the Cars button and create Create a few records
     
    Cars
  64. Close the forms and return to your programming environment
  65. To add a new form to the project, on the main menu, click Project -> Add -> Windows Form...
  66. Set the Name to RentalRates and press Enter
  67. In the Data Sources window, right-click dsBethesdaCaRental and click Configure Dataset with Wizard...
  68. Expand the Tables node and click the check box of RentalRates
  69. Click Finish
  70. In the Data Sources window, drag RentalRates and drop it on the form
  71. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    rentalRatesBindingSource bsRentalRates
    rentalRatesTableAdapter taRentalRates
    rentalRatesBindingNavigator bnRentalRates
  72. On the form, click the data grid view control and, in the Properties window, change its (Name) to dgvRentalRates
  73. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    RentalRateID Rate ID 50
    Category    75
    Daily   50
    Weekly   50
    Monthly   50
    Weekend   50
  74. Click OK
  75. To create a new form, on the main menu, click Project -> Add Windows Form...
  76. Set the name to OrderProcessing and click Add
  77. In the Data Source window, right-click dsBethesdaCarRental and click Configure DataSet With Wizard...
  78. Expand the Tables node and click the check box of RentalOrders and click Finish
  79. In the Data Sources window, click RentalOrders and click the arrow of its combo box
  80. Select Details
  81. From the Data Sources window, drag RentalOrders and drop it on the form
  82. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    rentalOrdersBindingSource bsRentalOrders
    rentalOrdersTableAdapter taRentalOrders
    rentalOrdersBindingNavigator bnRentalOrders
  83. Again, from the Data Sources window, drag Employees and drop it on the form
  84. While the DataGridView control is still selected on the form, press Delete to remove it from the form
  85. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    employeesBindingSource bsEmployees
    employeesTableAdapter taEmployees
  86. From the Data Sources window, drag Customers and drop it in an empty area of the form
  87. While the DataGridView control is still selected on the form, press Delete to remove it from the form
  88. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource bsCustomers
    customersTableAdapter taCustomers
  89. Once again, from the Data Sources window, drag Cars and drop it on the form
  90. While the DataGridView control is still selected on the form, press Delete to remove it from the form
  91. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    carsBindingSource bsCars
    carsTableAdapter taCars
  92. On the form, delete the text boxes on the right side of EmployeeID, CustomerID, CarID, CarCondition, TankLevel, and OrderStatus
  93. Design the form as follows (you will add new controls to replace those that were deleted):
     
    Order Processing
    Control Old Name  Text Name Additional Properties
    Label New Processed By:   BackColor: Gray
    BorderStyle: FixedSingle
    Label Old Employee #:    
    ComboBox New   cboEmployeeID  
    TextBox  employeeNameTextBox    txtEmployeeName  
    Label   Processing For   BackColor: Gray
    BorderStyle: FixedSingle 
    Label  Old  Driver's Lic. #:    
    ComboBox New   cbxCustomerID  
    Label Old  Customer Name:    
    TextBox customerNameTextBox   txtCustomerName  
    Label Old  Address:    
    TextBox customerAddressTextBox   txtCustomerAddress  
    Label Old  City:    
    TextBox customerCityTextBox   txtCustomerCity  
    Label Old  State:    
    TextBox customerStateTextBox   txtCustomerState  
    Label Old  ZIP Code:    
    TextBox customerZIPCodeTextBox   txtCustomerZIPCode  
    Label  New Car Selected   BackColor: Gray
    BorderStyle: FixedSingle 
    Label Old Tag Number:    
    ComboBox New   cbxCarID  
    Label Old Car Condition:    
    ComboBox New   cbxCarConditions Items:
    Needs Repair
    Drivable
    Excellent
    Label  Old Make:    
    TextBox carMakeTextBox   txtCarMake  
    Label    Model:    
    TextBox carModelTextBox   txtCarModel  
    Label  Old Year:     
    TextBox Old    txtCarYear  
    Label Old Tank Level:    
    ComboBox New   cbxTankLevels Items:
    Empty
    1/4 Empty
    1/2 Full
    3/4 Full
    Full 
    Label  Old Mileage Start:    
    TextBox mileageStartTextBox    txtMileageStart TextAlign: Right  
    Label   Mileage End:    
    TextBox mileageEndTextBox   txtMileageEnd TextAlign: Right
    Label    Order Timing   BackColor: Gray
    BorderStyle: FixedSingle 
    Label Old  Date Processed    
    DateTimePicker dateProcessedDateTimePicker   dtpDateProcessed  
    Label Old  Rent Start Date:    
    DateTimePicker rentStartDateDateTimePicker     dtpRentStartDate  
    Label   Rent End Date:    
    DateTimePicker rentEndDateDateTimePicker     dtpRentEndDate  
    Label Old  Total Days:    
    TextBox daysTextBox   txtTotalDays  
    Label   Order Evaluation   BackColor: Gray
    BorderStyle: FixedSingle 
    Label Old  Rate Applied    
    TextBox rateAppliedTextBox   txtRateApplied TextAlign: Right
    Button New Rental Rates btnRentalRates   
    Label Old  Sub Total:     
    TextBox subTotalTextBox   txtSubTotal TextAlign: Right
    Button  New Calculate btnCalculate  
    Label Old Tax Rate:    
    TextBox taxRateTextBox   txtTaxRate TextAlign: Right
    Label   %    
    Label   Tax Amount:    
    TextBox taxAmountTextBox    txtTaxAmount TextAlign: Right
    Label   Order Total:    
    TextBox orderTotalTextBox    txtOrderTotal TextAlign: Right
    Label  Old Order Status:    
    ComboBox  New   cbxOrderStatus Items:
    Car On Road
    Car Returned
    Order Reserved 
    Label  Old Rental Order ID:    
    TextBox rentalOrderIDTextBox    txtRentalOrderID TextAlign: Right 
    Button   Close btnClose  
    StatusStrip        
  94. Click each control that was added and change its data bindings
  95. On the form, double-click the Employee # combo box and implement its Click event as follows:
     
    private void cbxEmployeeID_SelectedIndexChanged(object sender, EventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conEmployees =
    	new System.Data.SqlClient.SqlConnection("Data Source=(local);" +
    						"Database=BethesdaCarRental2;" +
    						"Integrated Security=yes"))
       {
    	string strEmployee = "SELECT FullName FROM Employees " +
    			     "WHERE EmployeeNumber = '" +
    			      cbxEmployeeID.Text + "';";
    
    	System.Data.SqlClient.SqlCommand cmdEmployees =
    		new System.Data.SqlClient.SqlCommand(strEmployee, conEmployees);
    	conEmployees.Open();
    
    	System.Data.SqlClient.SqlDataReader rdrEmployees =
    		cmdEmployees.ExecuteReader();
    
    	while (rdrEmployees.Read())
    	    txtEmployeeName.Text = rdrEmployees.GetString(0);
        }
    } 
  96. Return to the OrderProcessing form
  97. On the form, double-click the Driver's Lic # combo box and implement its Click event as follows:
     
    private void cbxCustomerID_SelectedIndexChanged(object sender, EventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conCustomers =
    	new System.Data.SqlClient.SqlConnection("Data Source=(local);" +
    					"Database=BethesdaCarRental2;" +
    						"Integrated Security=yes"))
        {
    	string strCustomer = "SELECT * FROM Customers " +
    			     "WHERE DrvLicNumber = '" +
    			     cbxCustomerID.Text + "';";
    
    	System.Data.SqlClient.SqlCommand cmdCustomers =
    	    new System.Data.SqlClient.SqlCommand(strCustomer, conCustomers);
    	conCustomers.Open();
    
    	System.Data.SqlClient.SqlDataReader rdrCustomers = 
    		cmdCustomers.ExecuteReader();
    
    	while (rdrCustomers.Read())
    	{
    	    txtCustomerName.Text = rdrCustomers.GetString(2);
    	    txtCustomerAddress.Text = rdrCustomers.GetString(3);
    	    txtCustomerCity.Text = rdrCustomers.GetString(4);
    	    txtCustomerState.Text = rdrCustomers.GetString(5);
    	    txtCustomerZIPCode.Text = rdrCustomers.GetString(6);
    	}
        }
    }
  98. Return to the OrderProcessing form
  99. On the form, double-click the Tag Number combo box and implement its Click event as follows:
     
    private void cbxCarID_SelectedIndexChanged(object sender, EventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conCars =
    	new System.Data.SqlClient.SqlConnection("Data Source=(local);" +
    	    "Database=BethesdaCarRental2;" +
    	    "Integrated Security=yes"))
       {
    	string strCar = "SELECT * FROM Cars " +
    		        "WHERE TagNumber = '" +
    			cbxCarID.Text + "';";
    
    	System.Data.SqlClient.SqlCommand cmdCars =
    	    new System.Data.SqlClient.SqlCommand(strCar, conCars);
    	conCars.Open();
    
    	System.Data.SqlClient.SqlDataReader rdrCars =
    	    cmdCars.ExecuteReader();
    
    	while (rdrCars.Read())
    	{
    	    txtCarMake.Text = rdrCars.GetString(2);
    	    txtCarModel.Text = rdrCars.GetString(3);
    	    txtCarYear.Text = rdrCars.GetSqlInt16(4).ToString();
    	}
        }
    }
  100. Return to the OrderProcessing form
  101. On the form, double-click the Rent End Date control and implement the event as follows:
     
    private void dtpRentEndDate_ValueChanged(object sender, EventArgs e)
    {
        if( dtpRentStartDate.Value > dtpRentEndDate.Value )
    	txtTotalDays.Text = "0";
        else if (dtpRentStartDate.Value == dtpRendependDate.Value)
    	txtTotalDays.Text = "1";
        else
        {
    	int days;
    
    	DateTime dteStart = dtpRentStartDate.Value;
    	DateTime dteEnd = dtpRentEndDate.Value;
    
    	// Let's calculate the difference in days
    	TimeSpan tme = dteEnd - dteStart;
    	days = tme.Days;
    
    	// If the customer returns the car the same day, 
    	// we consider that the car was rented for 1 day
    	if (days == 0)
    	    days = 1;
    
    	txtTotalDays.Text = days.ToString();
    	// At any case, we will let the clerk specify the actual number of days
        }
    }
  102. Return to the OrderProcessing form and double-click the Rental Rates button
  103. Implement the event as follows:
     
    private void btnRentalRates_Click(object sender, EventArgs e)
    {
        RentalRates rates = new RentalRates();
        rates.ShowDialog();
    } 
  104. Return to the OrderProcessing form and doouble-click the Calculate button
  105. Implement the events as follows:
     
    private void btnCalculate_Click(object sender, EventArgs e)
    {
        int Days = 0;
        double RateApplied = 0.00;
        double TaxRate = 0.00;
        double SubTotal, TaxAmount, OrderTotal;
    
        try
        {
    	Days = int.Parse(txtTotalDays.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid number of days.");
        }
    
        try
        {
    	RateApplied = double.Parse(txtRateApplied.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid rate applied.");
        }
    
        try
        {
    	TaxRate = double.Parse(txtTaxRate.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid tax rate.");
        }
    
        SubTotal = Days * RateApplied;
        TaxAmount = SubTotal * TaxRate / 100;
        OrderTotal = SubTotal + TaxAmount;
    
        txtSubTotal.Text = SubTotal.ToString("F");
        txtTaxAmount.Text = TaxAmount.ToString("F");
        txtOrderTotal.Text = OrderTotal.ToString("F");
    } 
  106. Return to OrderProcessing the form and double-click the Close button
  107. Implement its even as follows:
     
    private void btnClose_Click(object  sender, EventArgs  e)
    {
    	 Close();
    }
  108. Access the Central form
  109. Add a button to it and set its properties as follows:
    Text: OrderProcessing
    (Name): btnOrderProcessing
  110. Double-click the OrderProcessing button and implement the even as follows:
     
    private void btnOrderProcessing_Click(object sender, EventArgs e)
    {
        OrderProcessing frmOrder = new OrderProcessing();
        frmOrder.ShowDialog();
    }
  111. Return to the Central form and double-click the Close button
  112. Implement the event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    } 
  113. Execute the application and display the OrderProcessing form
  114. To create an order, click the + button, complete the form, and click the Save button. Here are examples:
     
    Order Processing 
      
    Order Processing 
  115. Close the forms and return to your programming environment
  116. Execute the application again and display the Order Processing form again
  117. Click the Rental Rates button and create the values as follows:
     
    Category  Daily Weekly Monthly Weekend
    Economy 35.95 32.75 28.95 24.95
    Compact 39.95 35.75 32.95 28.95
    Standard 45.95 39.75 35.95 32.95
    Full Size 49.95 42.75 38.95 35.95
    Mini Van 55.95 50.75 45.95 42.95
    SUV 55.95 50.75 45.95 42.95
    Truck 42.75 38.75 35.95 32.95
    Van 69.95 62.75 55.95 52.95
  118. Update the orders you previously processed and save them
     
    Order Processing 
      
    Order Processing 
  119. Close the forms and return to your programming environment

Databases and Collection Classes

 

Introduction

In our introductory lessons to databases, we studied collections to get acquainted with lists. As you may imagine, a database is primarily a list of objects. As such, you can use a collection class in your database or even use a formal database in your collection-based application. To do this, we saw that you start by creating a connection to a database:

Connection

After establishing a connection to a database, you can use a command to specify the type of operation you want to perform, such as selecting records from the columns of a table, optionally using a condition. To get the data produced by the command, we saw that you could either use a data reader or a data adapter. If you use a data adapter, you can retrieve the values from the data adapter and store them in a data set object:

Data Connection to a Data Set

This is where, once again, a data set object becomes (very) valuable. Remember that a data set object contains one or more tables. Once you can identify a table from a data set, you can create a class that shares its structure. Then use the data from that table and temporarily store it in a collection-based class:

Once the data is available in a collection class, you can manipulate it using a regular class you would have created. This allows you to use your knowledge of collections to explore a database.

Practical LearningPractical Learning: Introducing Databases and Collection Classes

  1. Create a new Windows Application named CollegeParkAutoParts3
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type CollegeParkAutoParts.cs and press Enter
  4. Double-click the middle of the form and write code 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 CollegeParkAutoParts3
    {
        public partial class CollegeParkAutoParts : Form
        {
            public CollegeParkAutoParts()
            {
                InitializeComponent();
            }
    
            void CreateDatabase()
            {
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCPAP = 
    		    new SqlCommand("CREATE DATABASE CollegeParkAutoParts1;", cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show(
    		  "A database named CollegeParkAutoParts has been created.");
                }
    
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CollegeParkAutoParts1;" +
                                      "Integrated Security=SSPI;"))
                {
                    string strCreate = "CREATE TABLE AutoParts(" +
                                       "PartNumber int identity(100001, 1) NOT NULL, " +
                                       "CarYear int, " +
                                       "Make varchar(50), " +
                                       "Model varchar(50), " +
                                       "Category varchar(50), " +
                                       "PartName varchar(100), " +
                                       "UnitPrice money, " +
                                       "CONSTRAINT PK_AutoParts PRIMARY " +
                                       "    KEY (PartNumber));";
                    SqlCommand cmdCPAP = new SqlCommand(strCreate, cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show("A table named Parts has been created.");
                }
    
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CollegeParkAutoParts1;" +
                                      "Integrated Security=SSPI;"))
                {
                    string strCreate = "CREATE TABLE CustomersOrders(" +
                                       "CustomerOrderID int identity(1001, 1) NOT NULL, " +
                                       "ReceiptNumber int NOT NULL, " +
                                       "PartNumber int NULL, " +
                                       "PartName varchar(100), " +
                                       "UnitPrice money, " +
                                       "Quantity int, " +
                                       "SubTotal money, " +
    				   "PartsTotal money, " +
    				   "TaxRate decimal(6, 2), " +
    				   "TaxAmount money, " +
    				   "OrderTotal money, " +
                                       "CONSTRAINT PK_CustomersOrderts PRIMARY " +
                                       "    KEY (CustomerOrderID));";
                    SqlCommand cmdCPAP = new SqlCommand(strCreate, cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show("A table named Parts has been created.");
                }
            }
    
            private void CollegeParkAutoParts_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 dialog box, on the main menu, click Project -> Add Windows Form...
  8. Set the name to MakeEditor and click Add
  9. Design the form as follows:
     
    Make Editor
    Control Text Name Other Properties
    Label &Make:    
    TextBox   txtMake Modifiers: Public
    Button OK btnOK DialogResult: OK
    Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Make Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  10. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  11. Set the name to ModelEditor and click Add
  12. Design the form as follows:
     
    College Park Auto Parts: Model Editor
    Control Text Name Other Properties
    Label &Model:    
    TextBox   txtModel Modifiers: Public
    Button OK btnOK DialogResult: OK
    Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Model Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  13. To create a dialog box, in the Solution Explorer, right-click CollegeParkAutoParts3 -> Add -> Windows Form...
  14. Set the name to CategoryEditor and click Add
  15. Design the form as follows:
     
    College Park Auto Parts: Category Editor
    Control Text Name Other Properties
    Label &Category:    
    TextBox   txtCategory Modifiers: Public
    Button OK btnOK DialogResult: OK
    Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Category Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  16. On the main menu, click Project -> Add Windows Form...
  17. Set the Name to PartEditor and click Add
  18. Design the form as follows:
     
    College Park Auto-Part - Part Editor
     
    Control Text Name Other Properties
    Label &Year:    
    ComboBox   cbxYears  
    Label &Make:    
    ComboBox   cbxMakes  
    Button New C&ategory... btnNewMake  
    Label M&odel:    
    ComboBox   cbxModels  
    Button New Mo &del... btnNewModel  
    Label &Category:    
    ComboBox   cbxCategories  
    Button New Ca&tegory btnNewCategory  
    Label &Part Name:    
    TextBox   txtPartName  
    Label &Unit Price:    
    TextBox 0.00 txtUnitPrice TextAlign: Right
    Label Part #:    
    TextBox   txtPartNumber Enabled: False
    Button Submit btnSubmit  
    Button Close btnClose DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text College Park Auto -Parts: Part Editor
    StartPosition CenterScreen
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  19. Double-click the New Make button and implement its 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 CollegeParkAutoParts3
    {
        public partial class PartEditor : Form
        {
            public PartEditor()
            {
                InitializeComponent();
            }
    
            private void btnNewMake_Click(object sender, EventArgs e)
            {
                MakeEditor editor = new MakeEditor();
    
                if (editor.ShowDialog() == DialogResult.OK)
                {
                    if (editor.txtMake.Text.Length > 0)
                    {
                        string strMake = editor.txtMake.Text;
    
                        // Make sure the category is not yet in the list
                        if (cbxMakes.Items.Contains(strMake))
                            MessageBox.Show(strMake + " is already in the list");
                        else
                        {
                            // Since this is a new category, add it to the combox box
                            cbxMakes.Items.Add(strMake);
                        }
    
                        cbxMakes.Text = strMake;
                    }
                }
            }        
        }
    }
  20. Return to the Part Editor form and double-click the New Model button
  21. Implement its event as follows:
     
    private void btnNewModel_Click(object sender, EventArgs e)
    {
        ModelEditor editor = new ModelEditor();
    
        if (editor.ShowDialog() == DialogResult.OK)
        {
            if (editor.txtModel.Text.Length > 0)
            {
                string strModel = editor.txtModel.Text;
    
                // Make sure the category is not yet in the list
                if (cbxModels.Items.Contains(strModel))
                    MessageBox.Show(strModel + " is already in the list");
                else
                {
                    // Since this is a new category, add it to the combox box
                    cbxModels.Items.Add(strModel);
                }
    
                cbxModels.Text = strModel;
            }
        }
    }
  22. Return to the Part Editor form and double-click the New Category button
  23. Implement its event as follows:
     
    private void btnNewCategory_Click(object sender, EventArgs e)
    {
        CategoryEditor editor = new CategoryEditor();
    
        if (editor.ShowDialog() == DialogResult.OK)
        {
            if (editor.txtCategory.Text.Length > 0)
            {
                string strCategory = editor.txtCategory.Text;
    
                // Make sure the category is not yet in the list
                if (cbxCategories.Items.Contains(strCategory))
                    MessageBox.Show(strCategory + " is already in the list");
                else
                {
                    // Since this is a new category, add it to the combo box
                    cbxCategories.Items.Add(strCategory);
                }
    
                cbxCategories.Text = strCategory;
            }
        }
    }
  24. Return to the Part Editor form and double-click the Submit button
  25. Implement the event as follows:
     
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        double UnitPrice = 0.00;
    
        if (cbxYears.Text == "")
        {
            MessageBox.Show("You must specify the year of the car.");
            return;
        }
    
        if (cbxMakes.Text == "")
        {
            MessageBox.Show("You must specify the car manufacturer.");
            return;
        }
    
        if (cbxModels.Text == "")
        {
            MessageBox.Show("You must specify the car model.");
            return;
        }
    
        if (txtPartName.Text == "")
        {
            MessageBox.Show("You must enter the name of the part.");
            return;
        }
    
        if (txtUnitPrice.Text == "")
        {
            MessageBox.Show("You must enter the unit price of the part.");
            return;
        }
    
        try
        {
            UnitPrice = double.Parse(txtUnitPrice.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Unit Price.");
        }
    
        using (SqlConnection cnnNewPart =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strAutoPart = "INSERT INTO AutoParts(" +
                                 "CarYear, Make, Model, Category, " +
                                 "PartName, UnitPrice) VALUES('" +
                                 cbxYears.Text + "', '" +
                                 cbxMakes.Text + "', '" +
                                 cbxModels.Text + "', '" +
                                 cbxCategories.Text + "', '" +
                                 txtPartName.Text + "', '" +
                                 txtUnitPrice.Text + "');";
    
            SqlCommand cmdAutoPart = new SqlCommand(strAutoPart, cnnNewPart);
    
            cnnNewPart.Open();
            cmdAutoPart.ExecuteNonQuery();
    
            MessageBox.Show("The new part has been added.");
        }
    }
  26. Save the file and close the form
  27. In the Solution Explorer, double-click CollegeParkAutoParts.cs to open the form
  28. To create an icon, on the main menu, click Project -> Add New Item...
  29. In the Templates list, click Icon File
  30. Set the Name to cpap1 and click Add
  31. Right-click the white area and click Delete Image Type
  32. Design the 16x16, 16 colors version of the icon as follows:
     
  33. On the main menu, click File -> Save cpap1.ico As
  34. Select the bin\Debug folder of the current folder and click Save
  35. On the main menu, click File -> Close
  36. In the Solution Explorer, expand bin and expand Debug
  37. In the Solution Explorer, right-click the Debug folder -> Add -> New Item...
  38. In the Templates list, make sure Icon File is selected.
    Set the Name to cpap2 and click Add
  39. Right-click the white area and click Delete Image Type
  40. Design the 16x16, 16 colors version of the icon as follows:
     
  41. Save the file and close the icon window
  42. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  43. In the Templates list, make sure Icon File is selected.
    Set the Name to year1 and click Add
  44. Right-click the white area and click Delete Image Type
  45. Design the 16x16, 16 colors version of the icon as follows:
     
  46. Save the file and close the icon window
  47. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  48. In the Templates list, make sure Icon File is selected.
    Set the Name to year2 and click Add
  49. Right-click the white area and click Delete Image Type
  50. Design the 16x16, 16 colors version of the icon as follows:
     
  51. Save the file and close the icon window
  52. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  53. In the Templates list, make sure Icon File is selected.
    Set the Name to make1 and click Add
  54. Right-click the white area and click Delete Image Type
  55. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  56. Save the file and close the icon window
  57. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  58. In the Templates list, make sure Icon File is selected.
    Set the Name to make2 and click Add
  59. Right-click the white area and click Delete Image Type
  60. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  61. Save the file and close the icon window
  62. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  63. In the Templates list, make sure Icon File is selected.
    Set the Name to model1 and click Add
  64. Right-click the white area and click Delete Image Type
  65. Design the 16x16, 16 colors version of the icon as follows:
     
  66. Save the file and close the icon window
  67. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  68. In the Templates list, make sure Icon File is selected.
    Set the Name to model2 and click Add
  69. Right-click the white area and click Delete Image Type
  70. Design the 16x16, 16 colors version of the icon as follows:
     
  71. Save the file and close the icon window
  72. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  73. In the Templates list, make sure Icon File is selected.
    Set the Name to category1 and click Add
  74. Right-click the white area and click Delete Image Type
  75. Design the 16x16, 16 colors version of the icon as follows:
     
  76. Save the file and close the icon window
  77. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  78. In the Templates list, make sure Icon File is selected.
    Set the Name to category2 and click Add
  79. Right-click the white area and click Delete Image Type
  80. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Minus
  81. Save the file and close the icon window
  82. From the Components section of the Toolbox, click ImageList and click the form
  83. In the Properties window, click (Name) and type imgAutoParts
  84. Click the ellipsis button of the Images field
  85. In the Image Collection Editor, click Add
  86. Locate the folder that contains the icons you created and display it in the Look In combo box
  87. Select cpap1.ico and click Open
  88. In the same way, add the other pictures in the following order: cpap2.ico, year1.ico, year2.ico, make1.ico, make2.ico, model1.ico, model2.ico, category1.ico, and category1.ico
     
    Image Collection Editor
  89. Click OK
  90. Design the form as follows:
     
    College Park Auto Parts - Form Design
    Control Text Name Other Properties
    Label Label College Park Auto-Parts   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Panel     Height: 2
    GroupBox GroupBox Part Identification    
    TreeView TreeView   tvwAutoParts ImageList: imgAutoParts
    GroupBox GroupBox Available Parts    
    ListView ListView   lvwAutoParts FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colPartNumber Part #    
    colPartName Part Name   300
    colUnitPrice Unit Price Right 80
    GroupBox GroupBox Customer Order - Selected Parts    
    Label Label Part #    
    Label Label Part Name    
    Label Label Unit Price    
    Label Label Qty    
    Label Label Sub Total    
    TextBox TextBox   txtPartNumber  
    TextBox TextBox   txtPartName  
    TextBox TextBox 0.00 txtUnitPrice TextAlign: Right
    TextBox TextBox 0 txtQuantity TextAlign: Right
    TextBox TextBox 0.00 txtSubTotal TextAlign: Right
    Button Button Add/Select btnAdd
    ListView ListView   lvwSelectedParts FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colPartNumberSelected Part #   45
    colPartNameSelected Part Name   274
    colUnitPriceSelected Unit Price Right 58
    colQuantitySelected Qty Right 28
    colSubTotalSelected Sub-Total Right 58
    GroupBox GroupBox Order Summary
    Button Button New Au&to Part... btnNewAutoPart  
    Label Label Receipt #:  
    TextBox TextBox txtSave
    Button Button Save btnSave
    Label Label Tax Rate:
    TextBox TextBox 7.75 txtTaxRate TextAlign: Right
    Label Label %
    Label Label Parts Total:
    TextBox TextBox 0.00 txtPartsTotal TextAlign: Right
    Button Button &New Customer Order btnNewCustomerOrder  
    Label Label Receipt #:  
    TextBox TextBox txtOpen
    Button Button Save btnOpen
    Label Label Tax Amount:
    TextBox TextBox 0.00 txtTaxAmount TextAlign: Right
    Label Label Order Total:
    TextBox TextBox 0.00 txtOrderTotal TextAlign: Right
    Button Button Close btnClose  
  91. Click the Available Parts list view
  92. In the Properties window, click the Events button and, in the Events section, double-click DoubleClick
  93. Implement the event as follows:
     
    private void CollegeParkAutoParts_Load(object sender, EventArgs e)
    {
        // CreateDatabase();
    }
    
    private void lvwAutoParts_DoubleClick(object sender, EventArgs e)
    {
        ListViewItem lviAutoPart = lvwAutoParts.SelectedItems[0];
    
        if( (lvwAutoParts.SelectedItems.Count == 0) ||
            (lvwAutoParts.SelectedItems.Count > 1) )
                    return;
    
        txtPartNumber.Text = lviAutoPart.Text;
        txtPartName.Text = lviAutoPart.SubItems[1].Text;
        txtUnitPrice.Text = lviAutoPart.SubItems[2].Text;
    
        txtQuantity.Text = "1";
        txtSubTotal.Text = lviAutoPart.SubItems[2].Text;
    
        txtQuantity.Focus();
    }
  94. Return to the CollegeParkAutoParts form
  95. Click the Unit Price text box and, in the Events section of the Properties window, double-click Leave
  96. Implement the event as follows:
     
    private void txtUnitPrice_Leave(object sender, EventArgs e)
    {
        double UnitPrice = 0.00D;
        int Quantity = 0;
        double SubTotal = 0.00D;
    
        try
        {
            UnitPrice = double.Parse(txtUnitPrice.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Unit Price!");
        }
    
        try { Quantity = int.Parse(txtQuantity.Text); }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Quandtity!");
        }
    
        SubTotal = UnitPrice * Quantity;
        txtSubTotal.Text = SubTotal.ToString("F");
    }
    
    internal void CalculateOrder()
    {
        if (lvwSelectedParts.Items.Count == 0)
        {
            txtTaxAmount.Text = "0.00";
            txtPartsTotal.Text = "0.00";
            txtOrderTotal.Text = "0.00";
            return;
        }
    
        // Calculate the current total order and update the order
        double PartsTotal = 0.00;
        double TaxRate = 0.00;
        double TaxAmount = 0.00;
        double OrderTotal = 0.00;
    
        foreach (ListViewItem lvi in lvwSelectedParts.Items)
        {
            ListViewItem.ListViewSubItem SubItem = lvi.SubItems[4];
    
            PartsTotal += double.Parse(SubItem.Text);
        }
    
        try
        {
            TaxRate = double.Parse(txtTaxRate.Text) / 100;
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Tax Rate");
        }
    
        TaxAmount = PartsTotal * TaxRate;
        OrderTotal = PartsTotal + TaxAmount;
    
        txtPartsTotal.Text = PartsTotal.ToString("F");
        txtTaxAmount.Text = TaxAmount.ToString("F");
        txtOrderTotal.Text = OrderTotal.ToString("F");
    }
  97. Return to the CollegeParkAutoParts form and click the Qty text box
  98. In the Events section of the Properties, click Leave, then click the arrow of its combo box and select txtUnitPrice_Leave
  99. Return to the CollegeParkAutoParts form and click the Selected Part list view (the list view in the bottom-right section of the form)
  100. In the Events section of the Properties window, double-click DoubleClick
  101. Implement the event as follows:
     
    private void lvwSelectedParts_DoubleClick(object sender, EventArgs e)
    {
        ListViewItem lviSelectedPart = lvwSelectedParts.SelectedItems[0];
    
        if ((lvwSelectedParts.SelectedItems.Count == 0) ||
            (lvwSelectedParts.SelectedItems.Count > 1))
            return;
    
        txtPartNumber.Text = lviSelectedPart.Text;
        txtPartName.Text = lviSelectedPart.SubItems[1].Text;
        txtUnitPrice.Text = lviSelectedPart.SubItems[2].Text;
        txtQuantity.Text = lviSelectedPart.SubItems[3].Text;
        txtSubTotal.Text = lviSelectedPart.SubItems[4].Text;
    
        lvwSelectedParts.Items.Remove(lviSelectedPart);
        CalculateOrder();
    }
  102. Save all

Using a Collection-Based Class

As reviewed above, before using a regular class to manipulate a database, you should first create a class. To do this, you must know the structure of the table that will be represented by the class. This means that you should be familiar with the tables in the data set object. After creating the class, to get the values from a table, you can declare a variable from a collection-based class. Then, you can use the properties and methods of the collection class, just as we saw in previous lessons.

Practical Learning Practical Learning: Using a Collection-Based Class

  1. In the Solution Explorer, right-click CollegeParkAutoParts3 -> Add -> Class...
  2. Set the name to AutoPart and press Enter
  3. Create a class as follows:
     
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace CollegeParkAutoParts3
    {
        public class AutoPart
        {
            public int PartNumber;
            public int CarYear;
            public string Make;
            public string Model;
            public string Category;
            public string PartName;
            public double UnitPrice;
        }
    }
  4. Display the Part Editor form and double-click an empty area of its body
  5. Implement the event as follows:
     
    private void ResetPartEditor()
    {
        cbxYears.Items.Clear();
        cbxMakes.Items.Clear();
        cbxModels.Items.Clear();
        cbxCategories.Items.Clear();
        txtPartName.Text = "";
        txtUnitPrice.Text = "0.00";
        txtPartNumber.Text = "";
    
        // Put the years in the top combo box
        // Start with next year down to 1960
        for (int i = DateTime.Today.Year + 1; i >= 1960; i--)
            cbxYears.Items.Add(i.ToString());
    
        // Prepare a data set object for the parts
        DataSet dsParts = new DataSet("PartsSet");
    
        // Establish a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            // Create a command that will select the parts
            SqlCommand cmdParts =
                new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            // Open the connection
            cnnParts.Open();
    
            // Create a data adapter that will get the values from the table
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            // Store those values in the data set
            sdaParts.Fill(dsParts);
    
            // Create an auto part object
            AutoPart StoreItem = null;
            // Create an empty list of auto parts
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            // Check each record from the (only) table in the data set
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                // Use the auto part object ...
                StoreItem = new AutoPart();
                // ... to create a record object
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                // Once the record is ready, store it in the collection variable
                lstAutoParts.Add(StoreItem);
            }
    
            // To avoid duplicate values in the combo boxes, 
            // we will use collection classes
            List<string> lstMakes = new List<string>();
            List<string> lstCategories = new List<string>();
    
            // Check the list of makes
            foreach (AutoPart part in lstAutoParts)
            {
                // If the list doesn't yet contain the make, add it
                if (!lstMakes.Contains(part.Make))
                    lstMakes.Add(part.Make);
            }
    
            // Once we have the list of makes, 
            // put them in the Make combo box
            foreach (string strMake in lstMakes)
                cbxMakes.Items.Add(strMake);
    
            foreach (AutoPart part in lstAutoParts)
            {
                if (!lstCategories.Contains(part.Category))
                    lstCategories.Add(part.Category);
            }
    
            foreach (string strCategory in lstCategories)
                cbxCategories.Items.Add(strCategory);
        }
    }
    
    private void PartEditor_Load(object sender, EventArgs e)
    {
        ResetPartEditor();
    }
  6. Call the ResetPartEditor() method before the closing curly bracket of the Click event of the Submit button:
     
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        . . . No Change            
                 
        ResetPartEditor();
    }
  7. Return to the Part Editor form and double-click the Year combo box
  8. Implement the event as follows:
     
    private void cbxYears_SelectedIndexChanged(object sender, EventArgs e)
    {
        // If the user was working on a previous part, cancel it
        cbxModels.Items.Clear();
    }
  9. Return to the Part Editor form and double-click the Make combo box
  10. Implement its event as follows:
     
    private void cbxMakes_SelectedIndexChanged(object sender, EventArgs e)
    {
        // Create a data set of parts
        DataSet dsParts = new DataSet("PartsSet");
    
        // Establish a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
    	// Create a command that will select the parts
            SqlCommand cmdParts = 
    	    new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
    	// Open the connection
            cnnParts.Open();
    
    	// Create a data adapter that will get 
    	// the record from the command
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
    
    	// Store those records in the data set
            sdaParts.Fill(dsParts);
    
    	// Create an empty object that can receive one record
            AutoPart StoreItem = null;
    	// Create an empty list that can receive a list of records
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
    	// Check each record
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
    	    // Get the values of the current record
                StoreItem = new AutoPart();
    	    // Store the values in the object
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
    	    // Once the record is ready, store it in the collection
                lstAutoParts.Add(StoreItem);
            }
    
    	// This will hold the list of parts
            List<string> lstModels = new List<string>();
    
            foreach (AutoPart part in lstAutoParts)
            {
                if ((part.CarYear == int.Parse(cbxYears.Text)) &&
                    (part.Make == cbxMakes.Text))
                {
                    if (!lstModels.Contains(part.Model))
                        lstModels.Add(part.Model);
                }
            }
    
            foreach (string strModel in lstModels)
                cbxModels.Items.Add(strModel);
        }
    }
  11. Access the CollegeParkAutoParts form, right-click it and click View Code
  12. Make the following changes:
     
    void ShowAutoParts()
    {
        tvwAutoParts.Nodes.Clear();
        TreeNode nodRoot = 
    	tvwAutoParts.Nodes.Add("College Park Auto-Parts",
                                   "College Park Auto-Parts", 0, 1);
        // Show the years nodes
        for (int years = DateTime.Today.Year + 1; years >= 1960; years--)
            nodRoot.Nodes.Add(years.ToString(), years.ToString(), 2, 3);
    
        tvwAutoParts.SelectedNode = nodRoot;
        // Expand the root node
        tvwAutoParts.ExpandAll();
    
        // Create an empty data set
        DataSet dsParts = new DataSet("PartsSet");
    
        // Create a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            // Use a command to specify what action we want to take
            SqlCommand cmdParts = 
    		new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            // Open the connection
            cnnParts.Open();
    
            // Use a data adapter to retrieve the values from the command
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            // Fill the data set with the values from the data adapater
            sdaParts.Fill(dsParts);
    
            // Prepare a variable from the class we will use
            AutoPart StoreItem = null;
            // Create a collection
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            // Check each record from the table from the data set
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                // Identify each record as a value of our class
                StoreItem = new AutoPart();
                // Get the values of the record and put them in the class
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                // Store the record in the collection
                lstAutoParts.Add(StoreItem);
            }
    
            // Now that we have the records, 
            // we can use them as a normal collection
            foreach (TreeNode nodYear in nodRoot.Nodes)
            {
                List<string> lstMakes = new List<string>();
    
                foreach (AutoPart  part in lstAutoParts)
                {
                    if (nodYear.Text == part.CarYear.ToString())
                    {
                        if (!lstMakes.Contains(part.Make))
                            lstMakes.Add(part.Make);
                    }
                }
    
                foreach (string strMake in lstMakes)
                    nodYear.Nodes.Add(strMake, strMake, 4, 5);
            }
    
            foreach (TreeNode nodYear in nodRoot.Nodes)
            {
                foreach (TreeNode nodMake in nodYear.Nodes)
                {
                    List<string> lstModels = new List<string>();
    
                    foreach (AutoPart  part in lstAutoParts)
                    {
                        if ((nodYear.Text == part.CarYear.ToString()) &&
                            (nodMake.Text == part.Make))
                        {
                            if (!lstModels.Contains(part.Model))
                                lstModels.Add(part.Model);
                        }
                    }
    
                    foreach (string strModel in lstModels)
                        nodMake.Nodes.Add(strModel, strModel, 6, 7);
                }
            }
    
            foreach (TreeNode nodYear in nodRoot.Nodes)
            {
                foreach (TreeNode nodMake in nodYear.Nodes)
                {
                    foreach (TreeNode nodModel in nodMake.Nodes)
                    {
                        List<string> lstCategories = new List<string>();
    
                        foreach (AutoPart  part in lstAutoParts)
                        {
                            if ((nodYear.Text == part.CarYear.ToString()) &&
                                (nodMake.Text == part.Make) &&
                                (nodModel.Text == part.Model))
                            {
                                if (!lstCategories.Contains(part.Category))
                                    lstCategories.Add(part.Category);
                            }
                        }
    
                        foreach (string strCategory in lstCategories)
                        nodModel.Nodes.Add(strCategory, strCategory, 8, 9);
                    }
                }
            }
        }
    }
    
    private void CollegeParkAutoParts_Load(object sender, EventArgs e)
    {
        // CreateDatabase();
        ShowAutoParts();
    }
    
    . . . No Change
    	
    
  13. Return to the CollegeParkAutoParts form and double-click the New Auto Part button
  14. Implement the event as follows:
     
    private void btnNewAutoPart_Click(object sender, EventArgs e)
    {
        PartEditor editor = new PartEditor();
    
        if (editor.ShowDialog() == DialogResult.Cancel)
            ShowAutoParts();
    }
  15. Execute the application
  16. Click the New Auto Part button and use the Part Editor to create a few parts
  17. Close the forms and return to your programming environment
  18. Access the  CollegeParkAutoParts form and click the tree view
  19. In the Properties window, click the Events button and, in the Events section, double-click NodeMouseClick
  20. Implement the event as follows:
     
    private void tvwAutoParts_NodeMouseClick(object sender, 
    		TreeNodeMouseClickEventArgs e)
    {
        TreeNode nodClicked = e.Node;
    
        if (nodClicked.Level == 4)
            lvwAutoParts.Items.Clear();
    
        DataSet dsParts = new DataSet("PartsSet");
    
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts = 
    		new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            cnnParts.Open();
    
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            sdaParts.Fill(dsParts);
    
            AutoPart StoreItem = null;
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                StoreItem = new AutoPart();
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
               lstAutoParts.Add(StoreItem);
           }
    
           try
           {
                foreach (AutoPart part in lstAutoParts)
                {
                    if ((part.Category == nodClicked.Text) &&
                        (part.Model == nodClicked.Parent.Text) &&
                        (part.Make == nodClicked.Parent.Parent.Text) &&
                        (part.CarYear.ToString() == 
    			nodClicked.Parent.Parent.Parent.Text))
                    {
                        ListViewItem lviAutoPart =
                            new ListViewItem(part.PartNumber.ToString());
    
                        lviAutoPart.SubItems.Add(part.PartName);
                  lviAutoPart.SubItems.Add(part.UnitPrice.ToString("F"));
                        lvwAutoParts.Items.Add(lviAutoPart);
                    }
                }
            }
            catch (NullReferenceException)
            {
            }
        }
    }
  21. Return to the CollegeParkAutoParts form and double-click the Add/Select button
  22. Implement the event as follows:
     
    private void btnAdd_Click(object sender, EventArgs e)
    {
        if (txtPartNumber.Text.Length == 0)
        {
            MessageBox.Show("There is no part to be added to the order");
            return;
        }
                
        DataSet dsParts = new DataSet("PartsSet");
    
        using (SqlConnection cnnParts =
              new SqlConnection("Data Source=(local);" +
                                "Database=CollegeParkAutoParts1;" +
                                "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts = 
    	    new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            cnnParts.Open();
    
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            sdaParts.Fill(dsParts);
    
            AutoPart StoreItem = null;
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                StoreItem = new AutoPart();
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                lstAutoParts.Add(StoreItem);
            }
    
            foreach (AutoPart part in lstAutoParts)
            {
                 if (part.PartNumber == int.Parse(txtPartNumber.Text))
                 {
                    ListViewItem lviSelectedPart =
                        new ListViewItem(part.PartNumber.ToString());
    
                    lviSelectedPart.SubItems.Add(part.PartName);
                    lviSelectedPart.SubItems.Add(part.UnitPrice.ToString());
                    lviSelectedPart.SubItems.Add(txtQuantity.Text);
                    lviSelectedPart.SubItems.Add(txtSubTotal.Text);
                    lvwSelectedParts.Items.Add(lviSelectedPart);
                }
            }
    
            CalculateOrder();
        }
    }
  23. Return to the CollegeParkAutoParts form and double-click the Save Customer Order button
  24. Implement the event as follows:
     
    private void btnSaveCustomerOrder_Click(object sender, EventArgs e)
    {
        if (lvwSelectedParts.Items.Count == 0)
            return;
        else
        {
            int ReceiptNumber = 0;
            string strReceiptNumber = "";
    
            // The following code allows us to get 
    	// the highest receipt number
            //1. Create a connection to the database
            using (SqlConnection cnnReceiptNumber =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts1;" +
                                     "Integrated Security=SSPI;"))
            {
                // Use the MAX() function to locate 
    	    // the highest receipt number
                string strSelect = 
    		"SELECT MAX(ReceiptNumber) FROM CustomersOrders;";
                SqlCommand cmdReceiptNumber = 
    		new SqlCommand(strSelect, cnnReceiptNumber);
    
                // 2. Open the connection
                cnnReceiptNumber.Open();
    
                // 3. Use a data reader to get 
    	    //  the values from the Customer Orders table
                SqlDataReader rdrReceiptNumber = 
    		cmdReceiptNumber.ExecuteReader();
    
                // 4. Read through the receipt numbers to get to the last
                while (rdrReceiptNumber.Read())
                    strReceiptNumber = rdrReceiptNumber[0].ToString();
    
                // If there is no receipt number (yet), 
    	    // then set the first to 1000
                if (strReceiptNumber == "")
                    strReceiptNumber = "1000";
    
                // Now that we have a receipt number, we will increase it by 1
                ReceiptNumber = int.Parse(strReceiptNumber) + 1;
            }
    
            // The following variable will hold the whole INSERT statement
            string strCustomerOrder = "";
    
            // Check each selected part and get it ready to add to the table
            // Each selected part of this order will have a common receipt #
            for (int i = 0; i < lvwSelectedParts.Items.Count; i++)
            {
                strCustomerOrder = 
    		strCustomerOrder + 
    		"INSERT INTO CustomersOrders(" +
                    "ReceiptNumber, PartNumber, PartName, " +
                    "UnitPrice, Quantity, SubTotal) VALUES('" +
                    ReceiptNumber.ToString() + "', '" +
                    lvwSelectedParts.Items[i].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[1].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[2].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[3].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[4].Text + "'); ";
            }
    
            // We will also use the same common receipt number 
            // to save the values of the receipt
             strCustomerOrder = 
    		strCustomerOrder + 
    		"INSERT INTO CustomersOrders(" +
                    "ReceiptNumber, PartsTotal, TaxRate, " +
                    "TaxAmount, OrderTotal) VALUES('" +
                    ReceiptNumber.ToString() + "', '" +
                    txtPartsTotal.Text + "', '" +
                    txtTaxRate.Text + "', '" +
                    txtTaxAmount.Text + "', '" +
                    txtOrderTotal.Text + "');";
    
            using (SqlConnection cnnCustomerOrder =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts1;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomerOrder = 
    		new SqlCommand(strCustomerOrder, cnnCustomerOrder);
                cnnCustomerOrder.Open();
    
                cmdCustomerOrder.ExecuteNonQuery();
    
                // Let the user know that the order has been created
                MessageBox.Show("The customer order has been saved.");
                // Reset the form
                btnNewCustomerOrder_Click(sender, e);
            }
        }
    }
  25. Return to the CollegeParkAutoParts form and double-click the Open button
  26. Implement the event as follows:
     
    private void btnOpen_Click(object sender, EventArgs e)
    {
        lvwSelectedParts.Items.Clear();
    
        using (SqlConnection cnnCustomerOrder =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strSelect = "SELECT * FROM CustomersOrders " +
                               "WHERE (ReceiptNumber = '" +
                               txtReceiptNumber.Text + "') AND " +
                               "PartNumber IS NOT NULL;";
    
            SqlCommand cmdCustomerOrder =
            new SqlCommand(strSelect, cnnCustomerOrder);
            SqlDataAdapter sdaCustomerOrder =
    	        new SqlDataAdapter(cmdCustomerOrder);
    
            cnnCustomerOrder.Open();
    
            DataSet dsCustomerOrder = new DataSet("CustomersOrdersSet");
            sdaCustomerOrder.Fill(dsCustomerOrder);
    
            for (int i = 0; i < dsCustomerOrder.Tables[0].Rows.Count; i++)
            {
                DataRow rowOrder = dsCustomerOrder.Tables[0].Rows[i];
    
                ListViewItem lviOrder =
                    new ListViewItem(rowOrder["PartNumber"].ToString());
                lviOrder.SubItems.Add(rowOrder["PartName"].ToString());
                lviOrder.SubItems.Add(rowOrder["UnitPrice"].ToString());
                lviOrder.SubItems.Add(rowOrder["Quantity"].ToString());
                lviOrder.SubItems.Add(rowOrder["SubTotal"].ToString());
                lvwSelectedParts.Items.Add(lviOrder);
            }
        }
    
        using (SqlConnection cnnInvoice =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strInvoice = "SELECT ReceiptNumber, PartsTotal, TaxRate, " +
                                "TaxAmount, OrderTotal " +
                                "FROM CustomersOrders " +
                                "WHERE (ReceiptNumber = '" +
                                txtReceiptNumber.Text + "') AND " +
                                "      (PartNumber IS NULL) AND " +
                                "      (PartName IS NULL);";
    
            SqlCommand cmdInvoice = new SqlCommand(strInvoice, cnnInvoice);
    
            cnnInvoice.Open();
            SqlDataReader rdrInvoice = cmdInvoice.ExecuteReader();
    
            while (rdrInvoice.Read())
            {
                txtPartsTotal.Text = rdrInvoice[1].ToString();
                txtTaxRate.Text = rdrInvoice[2].ToString();
                txtTaxAmount.Text = rdrInvoice[3].ToString();
                txtOrderTotal.Text = rdrInvoice[4].ToString();
            }
        }
    }
  27. Return to the CollegeParkAutoParts form and double-click the New Customer button
  28. Implement the event as follows:
     
    private void btnNewCustomerOrder_Click(object sender, EventArgs e)
    {
        ShowAutoParts();
        lvwAutoParts.Items.Clear();
        txtPartNumber.Text = "";
        txtPartName.Text = "";
        txtUnitPrice.Text = "0.00";
        txtQuantity.Text = "0";
        txtSubTotal.Text = "0.00";
        lvwSelectedParts.Items.Clear();
        txtPartsTotal.Text = "0.00";
        txtTaxRate.Text = "5.75";
        txtTaxAmount.Text = "0.00";
        txtOrderTotal.Text = "0.00";
        txtReceiptNumber.Text = "";
    }
  29. Return to the CollegeParkAutoParts form and double-click the Close button
  30. Implement the event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  31. Create a few customer orders and save them
  32. Close the forms and return to your programming environment
  33. Execute the application again and open a previously saved order
  34. Close the forms and return to your programming environment

Exercises

 

College Park Auto Parts

  1. Open the CollegeParkAutoParts3 database from this lesson
  2. Add a context menu for the Available Parts list view with the items: Select, Edit..., and Delete
     
  3. Configure the context menu so that
    1. If the user clicks Select, the behavior would be the same as if the user had double-clicked the item
    2. If the user clicks Edit..., the Part Editor dialog box would display with the part in it. The user can then edit any part (year, make, model, category, part name, or unit price) except the part number. Then the user can save the changed part
    3. If the user clicks Delete, a message box would warn the user and ask for confirmation with Yes/No answers. If the user clicks Yes, the part would be deleted from the AutoParts table
  4. Configure the application so that the user can open an order, add new parts to it, or delete parts from it, then save the order
  5. Extend the application so that the store can also sell items that are, or are not, car-related, such as books, t-shirts, cleaning items, maintenance items (steering oils, brake oils, etc), license plates, etc. Every item in the store should have an item number. The user can enter that item number in the Part # text box and press Tab or Enter. The corresponding item would then be retrieved from the database and displayed on the form. If there is no item with that number, a message box should let the user know

Previous Copyright © 2008-2009 Yevol.com Next