Data Binding

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.

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:

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.

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:
    

   Control Name Text Additional Properties GroupBox   Order Identification   Label   Customer Name:   TextBox txtCustomerName     Label     Address:   TextBox txtAddress     Label   City:   TextBox txtCity     Label   State:   TextBox txtState     Label   ZIP Code:   TextBox txtZIPCode     Label   Make/Model:   TextBox txtMake     TextBox txtModel     Label   Year:   TextBox txtYear     Label   Problem Description:   TextBox txtProblemDescription   Scrollbars: Vertical Multiline: True GroupBox   Parts Used   Label   Part Name   Label   Unit Price   Label   Qty   Label   Sub Total   TextBox txtPart1Name     TextBox txtUnitPrice1 0.00 TextAlign: Right TextBox txtQuantity1 0 TextAlign: Right TextBox txtSubTotal1 0.00 TextAlign: Right TextBox txtPart2Name     TextBox txtUnitPrice2 0.00 TextAlign: Right TextBox txtQuantity2 0 TextAlign: Right TextBox txtSubTotal2 0.00 TextAlign: Right TextBox txtPart3Name     TextBox txtUnitPrice3 0.00 TextAlign: Right TextBox txtQuantity3 0 TextAlign: Right TextBox txtSubTotal3 0.00 TextAlign: Right TextBox txtPart4Name     TextBox txtUnitPrice4 0.00 TextAlign: Right TextBox txtQuantity4 0 TextAlign: Right TextBox txtSubTotal4 0.00 TextAlign: Right TextBox txtPart5Name     TextBox txtUnitPrice5 0.00 TextAlign: Right TextBox txtQuantity5 0 TextAlign: Right TextBox txtSubTotal5 0.00 TextAlign: Right GroupBox   Jobs Performed   Label   Job Description   Label   Price   TextBox txtJobDescription1     TextBox txtJobPrice1 0.00 TextAlign: Right TextBox txtJobDescription2     TextBox txtJobPrice2 0.00 TextAlign: Right TextBox txtJobDescription3     TextBox txtJobPrice3 0.00 TextAlign: Right TextBox txtJobDescription4     TextBox txtJobPrice4 0.00 TextAlign: Right TextBox txtJobDescription5     TextBox txtJobPrice5 0.00 TextAlign: Right GroupBox   Order Summary    Label      Total Parts:    TextBox txtTotalParts 0.00 TextAlign: Right Label       Total Labor:    TextBox txtTotalLabor 0.00 TextAlign: Right Label   Tax Rate:   TextBox txtTaxRate 7.75 TextAlign: Right Label   %   Label   Tax Amount:   TextBox txtTaxAmount 0.00 TextAlign: Right Label   Total Order:   TextBox txtTotalOrder 0.00 TextAlign: Right Label   Recommendations:   TextBox txtRecommendations   Scrollbars: Vertical Multiline: True  Button btnSave Save   Label   Receipt #:   TextBox txtReceiptNumber     Button btnOpen Open   Button btnNewRepairOrder New Repair Order   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:
     
27. Close the form and return to your programming environment
28. Execute the application again and open a few previously saved cleaning orders

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.

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:
    

   Control Name Text Additional Properties Group Box   New Employee   Label   First Name:   TextBox txtFirstName     Label   Last Name:   TextBox txtLastName     Label   Employee #:   MaskedTextBox txtEmployeeNumber   Mask: 00-000 Label   Title:   TextBox txtTitle     Label   Hourly Salary:   TextBox txtHourlySalary   TextAlign: Right Button btnSubmit Submit   DataGridView dgvEmployees     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:
    

   Control Name Text Additional Properties Group Box   New Customer   Label   Customer Phone:   MaskedTextBox txtCustomerPhone   Mask: (999) 000-0000 Label   Customer Name:   TextBox txtCustomerName     Button btnSubmit Submit   DataGridView dgvCustomers     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:
     

    Control Name Text Additional Properties GroupBox   Processed By   Label   Employee #:   MaskedTextBox txtEmployeeNumber   Mask: 00-000 Button    btnNewEmployee  New Employee...   Label   Employee Name:   TextBox txtEmployeeName     GroupBox   Processed For   Label   Customer Phone:   MaskedTextBox txtCustomerPhone   Mask: (999) 000-0000 Button  btnNewCustomer  New Cust...   Label   Customer Name:   TextBox txtCustomerName     GroupBox   Order Timing   Label   Date Left:   DateTimePicker dtpDateLeft     Label   Time Left:   DateTimePicker dtpTimeLeft   Format: Time Label   Date Expected:   DateTimePicker dtpDateExpected     Label   Time Expected:   DateTimePicker dtpTimeExpected   Format: Time Label   D&ate Picked Up:   DateTimePicker dtpDatePickedUp     Label   Time Pic&kep Up:   DateTimePicker dtpTimePickedUp     GroupBox   Order Processing   Label   Item Type   Label   Unit Price   Label   Qty   Label   Sub Total   Label   Shirts   TextBox txtUnitPriceShirts 1.25 TextAlign: Right TextBox txtQuantityShirts 0 TextAlign: Right TextBox txtSubTotalShirts 0.00 TextAlign: Right Label   Pants   TextBox txtUnitPricePants 1.95 TextAlign: Right TextBox txtQuantityPants   TextAlign: Right TextBox txtSubTotalPants 0.00 TextAlign: Right 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 txtUnitPriceItem1 0.00 TextAlign: Right TextBox txtQuantityItem1 0 TextAlign: Right TextBox txtSubTotalItem1 0.00 TextAlign: Right 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 txtUnitPriceItem2 0.00 TextAlign: Right TextBox txtQuantityItem2 0 TextAlign: Right TextBox txtSubTotalItem2 0.00 TextAlign: Right 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 txtUnitPriceItem3 0.00 TextAlign: Right TextBox txtQuantityItem3 0 TextAlign: Right TextBox txtSubTotalItem3 0.00 TextAlign: Right 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 txtUnitPriceItem4 0.00 TextAlign: Right TextBox txtQuantityItem4 0 TextAlign: Right TextBox txtSubTotalItem4 0.00 TextAlign: Right GroupBox   Order Summary   Label   Cleaning Total:   TextBox txtCleaningTotal 0.00 TextAlign: Right Label   Tax Rate:   TextBox txtTaxRate 7.75 TextAlign: Right Label   %   Label   Tax Amount:   TextBox txtTaxAmount 0.00 TextAlign: Right Label   Net Total:   TextBox txtNetPrice 0.00 TextAlign: Right Label   Order &Status:   ComboBox cbxOrderStatus     Button btnSave Save   GroupBox      Cleaning Order Details    Label   &Receipt #:   TextBox txtCleaningOrderID     Button btnOpen Open   Label      Notes   TextBox    txtNotes     Button btnNewCleaningOrder New Cleaning Order   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:
     
    
57. Create a few customers. Here are some examples:
     
    
58. Create a few cleaning orders. Here are examples:
     
    
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:
     
62. Close the form and return to your programming environment

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.

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.

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
        
       
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
     
    
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

     
    
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
     
    
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
     
    
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
     
    
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):
     

    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:
      
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
      
119. Close the forms and return to your programming environment

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:

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:

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.

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:
    

   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:
     

    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:
     

    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:
     

    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:
     
    
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:
     
    
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:
     
    
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
     
    
89. Click OK
90. Design the form as follows:
     

    Control Text Name Other Properties Label College Park Auto-Parts   Font: Times New Roman, 20.25pt, style=Bold ForeColor: Blue Panel     Height: 2 GroupBox Part Identification     TreeView   tvwAutoParts ImageList: imgAutoParts GroupBox Available Parts     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 Customer Order - Selected Parts     Label Part #     Label Part Name     Label Unit Price     Label Qty     Label Sub Total     TextBox   txtPartNumber   TextBox   txtPartName   TextBox 0.00 txtUnitPrice TextAlign: Right TextBox 0 txtQuantity TextAlign: Right TextBox 0.00 txtSubTotal TextAlign: Right Button Add/Select btnAdd 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 Order Summary Button New Au&to Part... btnNewAutoPart   Label Receipt #:   TextBox txtSave Button Save btnSave Label Tax Rate: TextBox 7.75 txtTaxRate TextAlign: Right Label % Label Parts Total: TextBox 0.00 txtPartsTotal TextAlign: Right Button &New Customer Order btnNewCustomerOrder   Label Receipt #:   TextBox txtOpen Button Save btnOpen Label Tax Amount: TextBox 0.00 txtTaxAmount TextAlign: Right Label Order Total: TextBox 0.00 txtOrderTotal TextAlign: Right 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

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.

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

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