Home

Reading Data Using a Data Reader

 

A Data Reader
 

Introduction

As reviewed in the previous lesson, a data adapter allows you to retrieve records from a database and make them available to your Windows Forms Application. To make reading data of a database a little faster, the .NET Framework provides a class used to read data from a database. For s SQL Server database, this class is called SqlDataReader

Practical Learning Practical Learning: Introducing the Data Reader
  1. Start Microsoft Visual Studio .NET or Visual C++ .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
  3. Right-click the server and click New Database
  4. Set the New Data Name to CPAS and accept to use Windows NT Integrated Security
  5. Click OK
  6. Under the name of the server in Server Explorer, expand the GCS node
  7. Right-click the Tables node and click New Table
  8. 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
  9. Save the table as RepairOrders and close it
  10. Create a new Windows Forms Application named CPAS2
  11. Design the form as follows (you could select (all) and copy the form from lesson 7, paste it to this form and make the necessary changes):
     
    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
  12. Double-click the New Order/Reset button and implement its Click event as follows:
      
    private: System::Void btnReset_Click(System::Object *  sender, System::EventArgs *  e)
{
	 this->dtpOrderDate->Value = DateTime::Today;
	 this->dtpOrderTime->Value = DateTime::Now;
	 this->txtCustomerName->Text = S"";
	 this->txtAddress->Text = S"";
	 this->txtCity->Text = S"";
	 this->txtState->Text = S"";
	 this->txtZIPCode->Text = S"";
	 this->txtMake->Text = S"";
	 this->txtModel->Text = S"";
	 this->txtCarYear->Text = S"";
	 this->txtProblem->Text = S"";

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

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

	 this->txtTotalParts->Text = S"0.00";
	 this->txtTotalLabor->Text = S"0.00";
	 this->txtTaxRate->Text    = S"7.75";
	 this->txtTaxAmount->Text  = S"0.00";
	 this->txtTotalOrder->Text = S"0.00";

	 this->txtRecommendations->Text = S"";
	 this->txtCustomerName->Focus();
}
  13. Under the above event, implement the follow method:
       
    void CalculateOrder(void)
{
	double part1UnitPrice, part1SubTotal, part2UnitPrice, part2SubTotal,
		    part3UnitPrice, part3SubTotal, part4UnitPrice, part4SubTotal,
		part5UnitPrice, part5SubTotal, totalParts;
	 int    part1Quantity = 0, part2Quantity = 0, part3Quantity = 0,
		    part4Quantity = 0, part5Quantity = 0;
	 double job1Price = 0.00, job2Price = 0.00, job3Price = 0.00,
		    job4Price = 0.00, job5Price = 0.00;
	 double totalLabor;
	 double taxRate, taxAmount, totalOrder;

	 // Don't charge a part unless it is clearly identified
	 if( this->txtPartName1->Text->Equals(S"") )
	 {
		 this->txtUnitPrice1->Text = S"0.00";
		 this->txtQuantity1->Text  = S"0";
		 this->txtSubTotal1->Text  = S"0.00";
		 part1UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part1UnitPrice = this->txtUnitPrice1->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Unit Price");
			 this->txtUnitPrice1->Text = S"0.00";
			 this->txtUnitPrice1->Focus();
		 }

		 try {
			 part1Quantity = this->txtQuantity1->Text->ToInt16(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Quantity");
			 this->txtQuantity1->Text = S"0";
			 this->txtQuantity1->Focus();
		 }
	 }

	 if( this->txtPartName2->Text->Equals(S"") )
	 {
		 this->txtUnitPrice2->Text = S"0.00";
		 this->txtQuantity2->Text  = S"0";
		 this->txtSubTotal2->Text  = S"0.00";
		 part2UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part2UnitPrice = this->txtUnitPrice2->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Unit Price");
			 this->txtUnitPrice2->Text = S"0.00";
			 this->txtUnitPrice2->Focus();
		 }

		 try {
			 part2Quantity = this->txtQuantity2->Text->ToInt16(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Quantity");
			 this->txtQuantity2->Text = S"0";
			 this->txtQuantity2->Focus();
		 }
	 }

	 if( this->txtPartName3->Text->Equals(S"") )
	 {
		 this->txtUnitPrice3->Text = S"0.00";
		 this->txtQuantity3->Text  = S"0";
		 this->txtSubTotal3->Text  = S"0.00";
		 part3UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part3UnitPrice = this->txtUnitPrice3->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Unit Price");
			 this->txtUnitPrice3->Text = S"0.00";
			 this->txtUnitPrice3->Focus();
		 }

		 try {
			 part3Quantity = this->txtQuantity3->Text->ToInt16(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Quantity");
			 this->txtQuantity3->Text = S"0";
			 this->txtQuantity3->Focus();
		 }
	 }

	 if( this->txtPartName4->Text->Equals(S"") )
	 {
		 this->txtUnitPrice4->Text = S"0.00";
		 this->txtQuantity4->Text  = S"0";
		 this->txtSubTotal4->Text  = S"0.00";
		 part4UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part4UnitPrice = this->txtUnitPrice4->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Unit Price");
			 this->txtUnitPrice4->Text = S"0.00";
			 this->txtUnitPrice4->Focus();
		 }

		 try {
			 part4Quantity = this->txtQuantity4->Text->ToInt16(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Quantity");
			 this->txtQuantity4->Text = S"0";
			 this->txtQuantity4->Focus();
		 }
	 }

	 if( this->txtPartName5->Text->Equals(S"") )
	 {
		 this->txtUnitPrice5->Text = S"0.00";
		 this->txtQuantity5->Text  = S"0";
		 this->txtSubTotal5->Text  = S"0.00";
		 part5UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part5UnitPrice = this->txtUnitPrice5->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Unit Price");
			 this->txtUnitPrice5->Text = S"0.00";
			 this->txtUnitPrice5->Focus();
		 }

		 try {
			 part5Quantity = this->txtQuantity5->Text->ToInt16(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Quantity");
			 this->txtQuantity5->Text = S"0";
			 this->txtQuantity5->Focus();
		 }
	 }

	 // Don't bill the customer for a job that is not specified
	 if( this->txtJobPerformed1->Text->Equals(S"") )
	 {
		 this->txtJobPrice1->Text = S"0.00";
		 job1Price = 0.00;
	 }
	 else
	 {
		 try {
			 job1Price = this->txtJobPrice1->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Job Price");
			 this->txtJobPrice1->Text = S"0.00";
			 this->txtJobPrice1->Focus();
		 }
	 }
				 
	 if( this->txtJobPerformed2->Text->Equals(S"") )
	 {
		 this->txtJobPrice2->Text = S"0.00";
		 job2Price = 0.00;
	 }
	 else
	 {
		 try {
			 job2Price = this->txtJobPrice2->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Job Price");
			 this->txtJobPrice2->Text = S"0.00";
			 this->txtJobPrice2->Focus();
		 }
	 }
				 
	 if( this->txtJobPerformed3->Text->Equals(S"") )
	 {
		 this->txtJobPrice3->Text = S"0.00";
		 job3Price = 0.00;
	 }
	 else
	 {
		 try {
			 job3Price = this->txtJobPrice3->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Job Price");
			 this->txtJobPrice3->Text = S"0.00";
			 this->txtJobPrice3->Focus();
		 }
	 }
				 
	 if( this->txtJobPerformed4->Text->Equals(S"") )
	 {
		 this->txtJobPrice4->Text = S"0.00";
		 job4Price = 0.00;
	 }
	 else
	 {
		 try {
			 job4Price = this->txtJobPrice4->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Job Price");
			 this->txtJobPrice4->Text = S"0.00";
			 this->txtJobPrice4->Focus();
		 }
	 }
				 
	 if( this->txtJobPerformed5->Text->Equals(S"") )
	 {
		 this->txtJobPrice5->Text = S"0.00";
		 job5Price = 0.00;
	 }
	 else
	 {
		 try {
			 job5Price = this->txtJobPrice5->Text->ToDouble(0);
		 }
		 catch(FormatException *)
		 {
			 MessageBox::Show(S"Invalid Job Price");
			 this->txtJobPrice5->Text = S"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(S"F");
	 this->txtSubTotal2->Text = part2SubTotal.ToString(S"F");
	 this->txtSubTotal3->Text = part3SubTotal.ToString(S"F");
	 this->txtSubTotal4->Text = part4SubTotal.ToString(S"F");
	 this->txtSubTotal5->Text = part5SubTotal.ToString(S"F");

	 totalParts    = part1SubTotal + part2SubTotal + part3SubTotal +
			 part4SubTotal + part5SubTotal;
				 
	 totalLabor    = job1Price + job2Price + job3Price +
		             job4Price + job5Price;
				 
	 try {
		 taxRate = this->txtTaxRate->Text->ToDouble(0);
	 }
	 catch(FormatException *)
	 {
		 MessageBox::Show(S"Invalid Tax Rate");
		 this->txtTaxRate->Text = S"7.75";
		 this->txtTaxRate->Focus();
	 }
				 
	 double totalPartsAndLabor = totalParts + totalLabor;
	 taxAmount  = totalPartsAndLabor * taxRate / 100;
	 totalOrder = totalPartsAndLabor + taxAmount;
				 
	 this->txtTotalParts->Text = totalParts.ToString(S"F");
	 this->txtTotalLabor->Text = totalLabor.ToString(S"F");
	 this->txtTaxAmount->Text  = taxAmount.ToString(S"F");
	 this->txtTotalOrder->Text = totalOrder.ToString(S"F");
}
  14. Return to the form. Click the first text box under Qty and, in the Properties window, click the Events button
  15. In the Events section of the Properties window, generate the Leave event and implement it as follows:
      
    System::Void txtQuantity1_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  16. 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:
      
    System::Void txtQuantity2_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  17. 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:
      
    System::Void txtQuantity3_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  18. 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:
      
    System::Void txtQuantity4_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  19. 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:
      
    System::Void txtQuantity5_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  20. 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:
      
    System::Void txtJobPrice1_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  21. 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:
      
    System::Void txtJobPrice2_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  22. 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:
      
    System::Void txtJobPrice3_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  23. 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:
      
    System::Void txtJobPrice4_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  24. 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:
      
    System::Void txtJobPrice5_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateOrder();
}
  25. Return to the form. Double-click the Save Order button and implement its Click event as follows:
      
    System::Void btnSaveOrder_Click(System::Object *  sender, System::EventArgs *  e)
{
	 String *strCustomerName = this->txtCustomerName->Text;

	 if( strCustomerName->Equals(S"") )
	 {
		 MessageBox::Show(S"You must provide a name for the customer");
		 return;
	 }

	 String *strOrderDate = this->dtpOrderDate->Value.ToString(S"d");
	 String *strOrderTime = this->dtpOrderTime->Value.ToString(S"t");

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

	 System::Data::SqlClient::SqlConnection *conDatabase = new 
       System::Data::SqlClient::SqlConnection(
	   S"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();
}
  26. Return to the form. Double-click the Close button and implement its Click event as follows:
      
    System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
{
	 Close();
}
  27. Execute the application and create a repair order. Here is an example:
     
  28. Save the order
  29. Close the form and return to your programming environment

The SQL Data Reader

In Lesson 7, we saw that the .NET Framework provided XML-supported classes used to read from, and write to, an XML file going forth but without back. To support a unidirectional approach to reading data from a SQL Server database, the .NET Framework provides the SqlDataReader. This class reads data in a top-down direction without referring back to a record it passed already:

In other words, the SqlDataReader reads the first record, moves down, reads the second record, moves down, and so on, until it gets to the last record. Once it has passed a record, it doesn't and cannot refer back to it.

To create a data reader, you can declare a pointer to SqlDataReader. This class doesn't have a constructor. This means that, to use it, you must (directly) specify where it would read its data. To provide data to the reader, the SqlCommand class is equipped with the ExecuteReader() method that is overloaded with two versions. The simplest version of this method uses the following syntax:

public: SqlDataReader* ExecuteReader();

Based on this, before using a data reader, you should first create a command that would specify how data would be acquired. Once the data is reader, you can pass it to the data reader by assigning the result of a call to a SqlCommand::ExecuteReader() method to a SqlDataReader object.

Using a SQL Data Reader

Once data is supplied to the reader, you can access it, one record at a time, from top to bottom. To access data that the reader acquired, you can call its Read() method whose syntax is: 

public: virtual bool Read();

As you can see, the Read() method simply reads a record and moves on. When reading the records of a table, as mentioned already many times, 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 that column is holding so you can read it accurately.

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

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

When using one of the Get... or GetSql... methods, the compiler doesn't perform any conversion. This means that, before sending the data, you have two responsibilities. First 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: the value of a column created with tinyint must be read with GetByte() or GetSqlByte() and trying to use GetInt32() or GetSqlInt32() would throw an error.

Practical Learning Practical Learning: Reading Data
  1. Display the form. On the form, double-click the Open button and implement its Click event as follows:
      
    private: System::Void btnOpen_Click(System::Object *  sender, System::EventArgs *  e)
{
	 String *strReceiptNumber = this->txtReceiptNumber->Text;

	 if( strReceiptNumber->Equals(S"") )
	 {
		 MessageBox::Show(S"You must provide a receipt number to look for the repair");
		 return;
	 }

	 String *strFindRepair = String::Concat(S"SELECT * FROM RepairOrders WHERE RepairOrderID = '",
		 strReceiptNumber, S"'");
			 
 	 System::Data::SqlClient::SqlConnection *conDatabase = new 
System::Data::SqlClient::SqlConnection(S"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();
}
  2. Execute the application
  3. In the bottom receipt number text box, type 1 and click Open
  4. Close the form and return to your programming environment
Home Copyright © 2005-2016, FunctionX