Home

Database Example Application:
College Park Auto-Repair

   

Introduction

 

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.

   

Practical LearningPractical Learning: Using a Data Reader 

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

Published on Thursday 10 January 2008

 

Home Copyright © 2007 FunctionX, Inc.