Home

ADO.NET Example Applications:
College Park Auto-Shop
 

ADO.NET Example Applications: College Park Auto-Shop

Introduction
 

The SqlDataReader class of the .NET Framework provides a technique of moving in the records of a set in a forward-only one-way many. For example, imagine you get to a record to view it. Once you finish with such a record, you move to the next record. You cannot visit the previous record. This can provide a fast means of accessing record since there is little processing involved.

In this example, we will create a database used to process records for the customers of a car repair shop. When it's time to open a previously saved order, we will use a SqlDataReader object to demonstrate how it can be used.

 

Practical Learning Practical Learning: Creating the Application
  1. Start Microsoft Visual Studio .NET
  2. Display the Server Explorer. Expand the Servers node, followed by the name of the computer, followed by SQL Servers, followed by the name of the server.
    If you already have a database named CPAS, fine.
    If you don't have it, right-click the server and click New Database. Set the New Database Name to CPAS and accept to use Windows NT Integrated Security. Click OK. Under the name of the server in Server Explorer, expand the GCS node. Right-click the Tables node and click New Table. Create the table with the following columns (change only the indicated information; anything that is not mentioned should be ignored and use the default):
     
    Column Name Data Type Length Allow Nulls Other Properties
    RepairOrderID int     Primary Key
    Identity: Yes
    OrderDate datetime   Unchecked  
    OrderTime datetime 20 Unchecked  
    CustomerName varchar Unchecked  
    Address varchar      
    City varchar      
    State char 2    
    ZIPCode varchar 10    
    Make varchar 20 Unchecked  
    Model varchar 32 Unchecked  
    CarYear smallint 5 Unchecked  
    ProblemDescription text   Unchecked  
    PartName1 varchar      
    UnitPrice1 decimal 10   Default Value: 0.00
    Scale: 2
    Quantity1 tinyint 3   Default Value: 0
    SubTotal1 decimal 10   Default Value: 0.00
    Scale: 2
    PartName2 varchar  
    UnitPrice2 decimal 10   Default Value: 0.00
    Scale: 2
    Quantity2 tinyint 3   Default Value: 0
    SubTotal2 decimal     Default Value: 0.00
    Scale: 2
    PartName3 varchar      
    UnitPrice3 decimal     Scale: 2
    Quantity3 tinyint     Default Value: 0
    SubTotal3 decimal     Default Value: 0.00
    Scale: 2
    PartName4 varchar      
    UnitPrice4 decimal     Default Value: 0.00
    Scale: 2
    Quantity4 tinyint     Default Value: 0
    SubTotal4 decimal     Default Value: 0.00
    Scale: 2
    PartName5 varchar      
    UnitPrice5 decimal     Default Value: 0.00
    Scale: 2
    Quantity5 tinyint     Default Value: 0
    SubTotal5 decimal     Default Value: 0.00
    Scale: 2
    JobPerformed1 varchar 80    
    JobPrice1 decimal     Default Value: 0.00
    Scale: 2
    JobPerformed2 varchar 80    
    JobPrice2 decimal     Default Value: 0.00
    Scale: 2
    JobPerformed3 varchar 80    
    JobPrice3 decimal     Default Value: 0.00
    Scale: 2
    JobPerformed4 varchar 80     
    JobPrice4 decimal     Default Value: 0.00
    Scale: 2
    JobPerformed5 varchar 80     
    JobPrice5 decimal     Default Value: 0.00
    Scale: 2
    TotalParts decimal   Unchecked Default Value: 0.00
    Scale: 2
    TotalLabor decimal   Unchecked Default Value: 0.00
    Scale: 2
    TaxRate decimal   Unchecked Default Value: 7.75
    Scale: 2
    TaxAmount decimal   Unchecked Default Value: 0.00
    Scale: 2
    OrderTotal decimal   Unchecked Default Value: 0.00
    Scale: 2
    Recommendations text      

    Save the table as RepairOrders and close it
  3. Create a new Windows Forms Application named CPAS2
  4. Design the form as follows:
     
    Control Name Text Other Properties
    GroupBox   Customer and Car Information  
    Label   First Name:  
    TextBox txtFirstName    
    Label   Last Name:  
    TextBox txtLastName    
    Label   Address  
    TextBox txtAddress    
    Label   City:  
    TextBox txtCity    
    Label   State:  
    TextBox txtState    
    Label   ZIP Code:  
    TextBox txtZIPCode   TextAlign: Right
    Label   Make / Model:  
    TextBox txtMake    
    TextBox txtModel    
    Label   Year:  
    TextBox txtCarYear   TextAlign: Right
    Label   Problem Description:  
    TextBox txtProblem    
    GroupBox   Parts Used  
    Label   Part Name  
    Label   Unit Price  
    Label   Qty  
    Label   Sub Total  
    TextBox txtPartName1    
    TextBox txtPartUnitPrice1 0.00 TextAlign: Right
    TextBox txtPartQuantity1 0 TextAlign: Right
    TextBox txtPartSubTotal1 0.00 TextAlign: Right
    TextBox txtPartName2    
    TextBox txtPartUnitPrice2 0.00 TextAlign: Right
    TextBox txtPartQuantity2 0 TextAlign: Right
    TextBox txtPartSubTotal2 0.00 TextAlign: Right
    TextBox txtPartName3    
    TextBox txtPartUnitPrice3 0.00 TextAlign: Right
    TextBox txtPartQuantity3 0 TextAlign: Right
    TextBox txtPartSubTotal3 0.00 TextAlign: Right
    TextBox txtPartName4    
    TextBox txtPartUnitPrice4 0.00 TextAlign: Right
    TextBox txtPartQuantity4 0 TextAlign: Right
    TextBox txtPartSubTotal4 0.00 TextAlign: Right
    TextBox txtPartName5    
    TextBox txtPartUnitPrice5 0.00 TextAlign: Right
    TextBox txtPartQuantity5 0 TextAlign: Right
    TextBox txtPartSubTotal5 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  
    Button btnResetOrder Reset Order  
    Button btnCalculateOrder Calculate Order  
    Label   Total Parts:  
    TextBox txtTotalParts 0.00 TextAlign: Right
    Label   Total Labor:  
    Text 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
    Button btnSaveOrder Save this Order and Start New Order  
    Button btnOpenOrder Open an Existing Order  
    Label   Recommendations  
    TextBox txtRecommendations   Multiline: True
    ScrollBars: Vertical
  5. Double-click the New Order/Reset button and implement its Click event as follows:
      
    private void btnReset_Click(object sender, System.EventArgs e)
		{
			this.dtpOrderDate.Value = DateTime.Today;
			this.dtpOrderTime.Value = DateTime.Now;
			this.txtCustomerName.Text = "";
			this.txtAddress.Text = "";
			this.txtCity.Text = "";
			this.txtState.Text = "";
			this.txtZIPCode.Text = "";
			this.txtMake.Text = "";
			this.txtModel.Text = "";
			this.txtCarYear.Text = "";
			this.txtProblem.Text = "";

			this.txtPartName1.Text = "";
			this.txtUnitPrice1.Text = "0.00";
			this.txtQuantity1.Text  = "0";
			this.txtSubTotal1.Text  = "0.00";
			this.txtPartName2.Text = "";
			this.txtUnitPrice2.Text = "0.00";
			this.txtQuantity2.Text  = "0";
			this.txtSubTotal2.Text  = "0.00";
			this.txtPartName3.Text = "";
			this.txtUnitPrice3.Text = "0.00";
			this.txtQuantity3.Text  = "0";
			this.txtSubTotal3.Text  = "0.00";
			this.txtPartName4.Text = "";
			this.txtUnitPrice4.Text = "0.00";
			this.txtQuantity4.Text  = "0";
			this.txtSubTotal4.Text  = "0.00";
			this.txtPartName5.Text = "";
			this.txtUnitPrice5.Text = "0.00";
			this.txtQuantity5.Text  = "0";
			this.txtSubTotal5.Text  = "0.00";

			this.txtJobPerformed1.Text = "";
			this.txtJobPrice1.Text = "0.00";
			this.txtJobPerformed2.Text = "";
			this.txtJobPrice2.Text = "0.00";
			this.txtJobPerformed3.Text = "";
			this.txtJobPrice3.Text = "0.00";
			this.txtJobPerformed4.Text = "";
			this.txtJobPrice4.Text = "0.00";
			this.txtJobPerformed5.Text = "";
			this.txtJobPrice5.Text = "0.00";

			this.txtTotalParts.Text = "0.00";
			this.txtTotalLabor.Text = "0.00";
			this.txtTaxRate.Text    = "7.75";
			this.txtTaxAmount.Text  = "0.00";
			this.txtTotalOrder.Text = "0.00";

			this.txtRecommendations.Text = "";
			this.txtCustomerName.Focus();
		}
  6. Under the above event, implement the following function:
       
    internal void CalculateOrder()
{
	decimal part1UnitPrice = 0.00M, part2UnitPrice = 0.00M,
		    part3UnitPrice = 0.00M, part4UnitPrice = 0.00M,
		    part5UnitPrice = 0.00M, 
		    part1SubTotal, part2SubTotal, part3SubTotal,
		    part4SubTotal, part5SubTotal, totalParts;
	int     part1Quantity = 0, part2Quantity = 0, part3Quantity = 0,
		    part4Quantity = 0, part5Quantity = 0;
	decimal job1Price = 0.00M, job2Price = 0.00M, job3Price = 0.00M,
		    job4Price = 0.00M, job5Price = 0.00M;
	decimal totalLabor, taxAmount, totalOrder, taxRate = 0.00M;

	// Don't charge a part unless it is clearly identified
	if( this.txtPartName1.Text == "" )
	{
		this.txtUnitPrice1.Text = "0.00";
		this.txtQuantity1.Text  = "0";
		this.txtSubTotal1.Text  = "0.00";
		part1UnitPrice = 0.00M;
	}
	else
	{
		try 
		{
			part1UnitPrice = decimal.Parse(this.txtUnitPrice1.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Unit Price");
			this.txtUnitPrice1.Text = "0.00";
			this.txtUnitPrice1.Focus();
		}

		try 
		{
			part1Quantity = int.Parse(this.txtQuantity1.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Quantity");
			this.txtQuantity1.Text = "0";
			this.txtQuantity1.Focus();
		}
	}

	if( this.txtPartName2.Text == "" )
	{
		this.txtUnitPrice2.Text = "0.00";
		this.txtQuantity2.Text  = "0";
		this.txtSubTotal2.Text  = "0.00";
		part2UnitPrice = 0.00M;
	}
	else
	{
		try 
		{
			part2UnitPrice = decimal.Parse(this.txtUnitPrice2.Text);
		}
		catch(FormatException)
		{
			MessageBox.Show("Invalid Unit Price");
			this.txtUnitPrice2.Text = "0.00";
			this.txtUnitPrice2.Focus();
		}

		try 
		{
			part2Quantity = int.Parse(this.txtQuantity2.Text);
		}
		catch(FormatException)
		{
			MessageBox.Show("Invalid Quantity");
			this.txtQuantity2.Text = "0";
			this.txtQuantity2.Focus();
		}
	}

	if( this.txtPartName3.Text == "" )
	{
		this.txtUnitPrice3.Text = "0.00";
		this.txtQuantity3.Text  = "0";
		this.txtSubTotal3.Text  = "0.00";
		part3UnitPrice = 0.00M;
	}
	else
	{
		try 
		{
			part3UnitPrice = decimal.Parse(this.txtUnitPrice3.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Unit Price");
			this.txtUnitPrice3.Text = "0.00";
			this.txtUnitPrice3.Focus();
		}

		try 
		{
			part3Quantity = int.Parse(this.txtQuantity3.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Quantity");
			this.txtQuantity3.Text = "0";
			this.txtQuantity3.Focus();
		}
	}

	if( this.txtPartName4.Text == "" )
	{
		this.txtUnitPrice4.Text = "0.00";
		this.txtQuantity4.Text  = "0";
		this.txtSubTotal4.Text  = "0.00";
		part4UnitPrice = 0.00M;
	}
	else
	{
		try 
		{
			part4UnitPrice = decimal.Parse(this.txtUnitPrice4.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Unit Price");
			this.txtUnitPrice4.Text = "0.00";
			this.txtUnitPrice4.Focus();
		}

		try 
		{
			part4Quantity = int.Parse(this.txtQuantity4.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Quantity");
			this.txtQuantity4.Text = "0";
			this.txtQuantity4.Focus();
		}
	}

	if( this.txtPartName5.Text == "" )
	{
		this.txtUnitPrice5.Text = "0.00";
		this.txtQuantity5.Text  = "0";
		this.txtSubTotal5.Text  = "0.00";
		part5UnitPrice = 0.00M;
	}
	else
	{
		try 
		{
			part5UnitPrice = decimal.Parse(this.txtUnitPrice5.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Unit Price");
			this.txtUnitPrice5.Text = "0.00";
			this.txtUnitPrice5.Focus();
		}

		try 
		{
			part5Quantity = int.Parse(this.txtQuantity5.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Quantity");
			this.txtQuantity5.Text = "0";
			this.txtQuantity5.Focus();
		}
	}

	// Don't bill the customer for a job that is not specified
	if( this.txtJobPerformed1.Text == "" )
	{
		this.txtJobPrice1.Text = "0.00";
		job1Price = 0.00M;
	}
	else
	{
		try 
		{
			job1Price = decimal.Parse(this.txtJobPrice1.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Job Price");
			this.txtJobPrice1.Text = "0.00";
			this.txtJobPrice1.Focus();
		}
	}
				 
	if( this.txtJobPerformed2.Text == "" )
	{
		this.txtJobPrice2.Text = "0.00";
		job2Price = 0.00M;
	}
	else
	{
		try 
		{
			job2Price = decimal.Parse(this.txtJobPrice2.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Job Price");
			this.txtJobPrice2.Text = "0.00";
			this.txtJobPrice2.Focus();
		}
	}
				 
	if( this.txtJobPerformed3.Text == "" )
	{
		this.txtJobPrice3.Text = "0.00";
		job3Price = 0.00M;
	}
	else
	{
		try 
		{
			job3Price = decimal.Parse(this.txtJobPrice3.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Job Price");
			this.txtJobPrice3.Text = "0.00";
			this.txtJobPrice3.Focus();
		}
	}
				 
	if( this.txtJobPerformed4.Text == "" )
	{
		this.txtJobPrice4.Text = "0.00";
		job4Price = 0.00M;
	}
	else
	{
		try 
		{
			job4Price = decimal.Parse(this.txtJobPrice4.Text);
		}
		catch(FormatException )
		{
			MessageBox.Show("Invalid Job Price");
			this.txtJobPrice4.Text = "0.00";
			this.txtJobPrice4.Focus();
		}
	}
				 
	if( this.txtJobPerformed5.Text == "" )
	{
		this.txtJobPrice5.Text = "0.00";
		job5Price = 0.00M;
	}
	else
	{
		try 
		{
			job5Price = decimal.Parse(this.txtJobPrice5.Text);
		}
		catch(FormatException)
		{
			MessageBox.Show("Invalid Job Price");
			this.txtJobPrice5.Text = "0.00";
			this.txtJobPrice5.Focus();
		}
	}
			 
	part1SubTotal = part1UnitPrice * part1Quantity;
	part2SubTotal = part2UnitPrice * part2Quantity;
	part3SubTotal = part3UnitPrice * part3Quantity;
	part4SubTotal = part4UnitPrice * part4Quantity;
	part5SubTotal = part5UnitPrice * part5Quantity;

	this.txtSubTotal1.Text = part1SubTotal.ToString("F");
	this.txtSubTotal2.Text = part2SubTotal.ToString("F");
	this.txtSubTotal3.Text = part3SubTotal.ToString("F");
	this.txtSubTotal4.Text = part4SubTotal.ToString("F");
	this.txtSubTotal5.Text = part5SubTotal.ToString("F");

	totalParts    = part1SubTotal + part2SubTotal + part3SubTotal +
			part4SubTotal + part5SubTotal;
				 
	totalLabor    = job1Price + job2Price + job3Price +
			job4Price + job5Price;
				 
	try 
	{
		taxRate = decimal.Parse(this.txtTaxRate.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("Invalid Tax Rate");
		this.txtTaxRate.Text = "7.75";
		this.txtTaxRate.Focus();
	}
				 
	decimal totalPartsAndLabor = totalParts + totalLabor;
	taxAmount  = totalPartsAndLabor * taxRate / 100;
	totalOrder = totalPartsAndLabor + taxAmount;
				 
	this.txtTotalParts.Text = totalParts.ToString("F");
	this.txtTotalLabor.Text = totalLabor.ToString("F");
	this.txtTaxAmount.Text  = taxAmount.ToString("F");
	this.txtTotalOrder.Text = totalOrder.ToString("F");
}
  7. Return to the form. Click the first text box under Qty and, in the Properties window, click the Events button
  8. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtQuantity1_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  9. Return to the form. Click the second text box under Qty. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtQuantity2_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  10. Return to the form. Click the third text box under Qty. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtQuantity3_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  11. Return to the form. Click the fourth text box under Qty. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtQuantity4_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  12. Return to the form. Click the fifth text box under Qty. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtQuantity5_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  13. Return to the form. Click the first text box under Price on the right side of Job Performed. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtJobPrice1_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  14. Return to the form. Click the second text box under Price. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtJobPrice2_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  15. Return to the form. Click the third text box under Price. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtJobPrice3_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  16. Return to the form. Click the fourth text box under Price. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtJobPrice4_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  17. Return to the form. Click the fifth text box under Price. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    private void txtJobPrice5_Leave(object sender, System.EventArgs e)
{
	CalculateOrder();
}
  18. Return to the form. Double-click the Save Order button and implement its Click event as follows:
      
    private void btnSaveOrder_Click(object sender, System.EventArgs e)
{
	string strCustomerName = this.txtCustomerName.Text;

	 if( strCustomerName == "" )
	 {
		 MessageBox.Show("You must provide a name for the customer");
		 return;
	 }

	 string strOrderDate = this.dtpOrderDate.Value.ToString("d");
	 string strOrderTime = this.dtpOrderTime.Value.ToString("t");

 string strNewRepairOrder = "INSERT INTO RepairOrders(OrderDate, " +
                                   "OrderTime, CustomerName, Address, " +
			"City, State, ZIPCode, Make, Model, " +
			"CarYear, ProblemDescription, PartName1, " +
			"UnitPrice1, Quantity1, SubTotal1, " +
			"PartName2, UnitPrice2, Quantity2, " +
			"SubTotal2, PartName3, UnitPrice3, " +
			"Quantity3, SubTotal3, PartName4, " +
			"UnitPrice4, Quantity4, SubTotal4, " +
			"PartName5, UnitPrice5, Quantity5, " +
			"SubTotal5, JobPerformed1, JobPrice1, " +
			"JobPerformed2, JobPrice2, JobPerformed3, " +
			"JobPrice3, JobPerformed4, JobPrice4, " +
			"JobPerformed5, JobPrice5, TotalParts, " +
			"TotalLabor, TaxRate, TaxAmount, " +
			"OrderTotal, Recommendations) " +
			"VALUES('" + strOrderDate + "', '" + strOrderTime +
		"', '" + strCustomerName + "', '" + this.txtAddress.Text +
	   "', '" + this.txtCity.Text + "', '" + this.txtState.Text +
	   "', '" + this.txtZIPCode.Text + "', '" + this.txtMake.Text +
	   "', '" + this.txtModel.Text + "', '" + this.txtCarYear.Text +
   "', '" + this.txtProblem.Text + "', '" + this.txtPartName1.Text +
   "', '" + this.txtUnitPrice1.Text + "', '" + this.txtQuantity1.Text +
   "', '" + this.txtSubTotal1.Text + "', '" + this.txtPartName2.Text +
   "', '" + this.txtUnitPrice2.Text + "', '" + this.txtQuantity2.Text +
   "', '" + this.txtSubTotal2.Text + "', '" + this.txtPartName3.Text +
   "', '" + this.txtUnitPrice3.Text + "', '" + this.txtQuantity3.Text +
   "', '" + this.txtSubTotal3.Text + "', '" + this.txtPartName4.Text +
   "', '" + this.txtUnitPrice4.Text + "', '" + this.txtQuantity4.Text +
   "', '" + this.txtSubTotal4.Text + "', '" + this.txtPartName5.Text +
   "', '" + this.txtUnitPrice5.Text + "', '" + this.txtQuantity5.Text +
  "', '" + this.txtSubTotal5.Text +"', '" + this.txtJobPerformed1.Text +
  "', '" + this.txtJobPrice1.Text + "', '" + this.txtJobPerformed2.Text +
  "', '"+ this.txtJobPrice2.Text + "', '" + this.txtJobPerformed3.Text +
  "', '" + this.txtJobPrice3.Text + "', '" + this.txtJobPerformed4.Text +
  "', '" + this.txtJobPrice4.Text + "', '" + this.txtJobPerformed5.Text +
   "', '" + this.txtJobPrice5.Text + "', '" + this.txtTotalParts.Text +
   "', '" + this.txtTotalLabor.Text + "', '" + this.txtTaxRate.Text +
   "', '" + this.txtTaxAmount.Text + "', '" + this.txtTotalOrder.Text +
			   "', '" + this.txtRecommendations.Text + "');";

	 System.Data.SqlClient.SqlConnection conDatabase = new 
System.Data.SqlClient.SqlConnection(
   "Data Source=(local);Database='CPAS';Integrated Security=yes");
 System.Data.SqlClient.SqlCommand    cmdDatabase = new 
 System.Data.SqlClient.SqlCommand(strNewRepairOrder, conDatabase);

	 conDatabase.Open();

	 cmdDatabase.ExecuteNonQuery();
	 conDatabase.Close();
}
  19. Return to the form. Double-click the Close button and implement its Click event as follows:
      
    private void btnClose_Click(object sender, System.EventArgs e)
{
	Close();
}
  20. Execute the application and create a repair order. Here is an example:
     
  21. Save the order
  22. Close the form and return to your programming environment
  23. Display the form. On the form, double-click the Open button and implement its Click event as follows:
      
    private void btnOpen_Click(object sender, System.EventArgs e)
{
	string strReceiptNumber = this.txtReceiptNumber.Text;

	if( strReceiptNumber == "" )
	{
		MessageBox.Show("You must provide a receipt number to look for the repair");
		return;
	}

	string strFindRepair = "SELECT * FROM RepairOrders WHERE RepairOrderID = '" +
				strReceiptNumber + "'";
			 
	System.Data.SqlClient.SqlConnection conDatabase = new 
System.Data.SqlClient.SqlConnection("Data Source=(local);Database='CPAS';Integrated Security=yes");
	System.Data.SqlClient.SqlCommand    cmdDatabase = new 
				System.Data.SqlClient.SqlCommand(strFindRepair, conDatabase);

	conDatabase.Open();

	System.Data.SqlClient.SqlDataReader rdrRepairOrder;
	rdrRepairOrder = cmdDatabase.ExecuteReader();

	while( rdrRepairOrder.Read() )
	{
		this.dtpOrderDate.Value = rdrRepairOrder.GetDateTime(1);
		this.dtpOrderTime.Value = rdrRepairOrder.GetDateTime(2);
		this.txtCustomerName.Text = rdrRepairOrder.GetString(3);
		this.txtAddress.Text = rdrRepairOrder.GetString(4);
		this.txtCity.Text = rdrRepairOrder.GetString(5);
		this.txtState.Text = rdrRepairOrder.GetString(6);
		this.txtZIPCode.Text = rdrRepairOrder.GetString(7);
		this.txtMake.Text = rdrRepairOrder.GetString(8);
		this.txtModel.Text = rdrRepairOrder.GetString(9);
		this.txtCarYear.Text = rdrRepairOrder.GetSqlInt16(10).ToString();
		this.txtProblem.Text = rdrRepairOrder.GetString(11);

		this.txtPartName1.Text = rdrRepairOrder.GetString(12);
		this.txtUnitPrice1.Text = rdrRepairOrder.GetSqlDecimal(13).ToString();
		this.txtQuantity1.Text  = rdrRepairOrder.GetSqlByte(14).ToString();
		this.txtSubTotal1.Text  = rdrRepairOrder.GetSqlDecimal(15).ToString();
		this.txtPartName2.Text = rdrRepairOrder.GetString(16);
		this.txtUnitPrice2.Text = rdrRepairOrder.GetSqlDecimal(17).ToString();
		this.txtQuantity2.Text  = rdrRepairOrder.GetSqlByte(18).ToString();
		this.txtSubTotal2.Text  = rdrRepairOrder.GetSqlDecimal(19).ToString();
		this.txtPartName3.Text = rdrRepairOrder.GetString(20);
		this.txtUnitPrice3.Text = rdrRepairOrder.GetSqlDecimal(21).ToString();
		this.txtQuantity3.Text  = rdrRepairOrder.GetSqlByte(22).ToString();
		this.txtSubTotal3.Text  = rdrRepairOrder.GetSqlDecimal(23).ToString();
		this.txtPartName4.Text = rdrRepairOrder.GetString(24);
		this.txtUnitPrice4.Text = rdrRepairOrder.GetSqlDecimal(25).ToString();
		this.txtQuantity4.Text  = rdrRepairOrder.GetSqlByte(26).ToString();
		this.txtSubTotal4.Text  = rdrRepairOrder.GetSqlDecimal(27).ToString();
		this.txtPartName5.Text = rdrRepairOrder.GetString(28);
		this.txtUnitPrice5.Text = rdrRepairOrder.GetSqlDecimal(29).ToString();
		this.txtQuantity5.Text  = rdrRepairOrder.GetSqlByte(30).ToString();
		this.txtSubTotal5.Text  = rdrRepairOrder.GetSqlDecimal(31).ToString();

		this.txtJobPerformed1.Text = rdrRepairOrder.GetString(32);
		this.txtJobPrice1.Text = rdrRepairOrder.GetSqlDecimal(33).ToString();
		this.txtJobPerformed2.Text = rdrRepairOrder.GetString(34);
		this.txtJobPrice2.Text = rdrRepairOrder.GetSqlDecimal(35).ToString();
		this.txtJobPerformed3.Text = rdrRepairOrder.GetString(36);
		this.txtJobPrice3.Text = rdrRepairOrder.GetSqlDecimal(37).ToString();
		this.txtJobPerformed4.Text = rdrRepairOrder.GetString(38);
		this.txtJobPrice4.Text = rdrRepairOrder.GetSqlDecimal(39).ToString();
		this.txtJobPerformed5.Text = rdrRepairOrder.GetString(40);
		this.txtJobPrice5.Text = rdrRepairOrder.GetSqlDecimal(41).ToString();

		this.txtTotalParts.Text = rdrRepairOrder.GetSqlDecimal(42).ToString();
		this.txtTotalLabor.Text = rdrRepairOrder.GetSqlDecimal(43).ToString();
		this.txtTaxRate.Text    = rdrRepairOrder.GetSqlDecimal(44).ToString();
		this.txtTaxAmount.Text  = rdrRepairOrder.GetSqlDecimal(45).ToString();
		this.txtTotalOrder.Text = rdrRepairOrder.GetSqlDecimal(46).ToString();

		this.txtRecommendations.Text = rdrRepairOrder.GetString(47);
	}

	rdrRepairOrder.Close();
	conDatabase.Close();
}
  24. Execute the application
  25. In the bottom receipt number text box, type 1 and click Open
  26. Close the form and return to your programming environment
 
Home Copyright © 2005-2012 FunctionX