ADO.NET Examples: Georgetown Cleaning Services

	Georgetown Cleaning Services

The Data View in ADO.NET

In a database environment, a query is a list of values created from another list. For example, a query can be created from a table by isolating records that follow a criterion. The .NET Framework supports queries through the DataView class. With a DataView object, you can create a selected list of records and you can then perform all types of regular operations of a database object, including:

Sorting Records
Filtering Records
Creating a new record
Updating one or more existing records

To create a DataView object, you can first declare a pointer to DataView. If you are working in Microsoft Visual Studio, in the Data section of the Toolbox, you can click the DataView button and click a container such as a form. After creating a DataView object, to perform a desired operation on it, you can use the SQL.

Practical Learning: Using a Data View

Display the form then, in the Data section of the Toolbox, click DataView and click the form
In the Properties window, change its values as follows:
(Name): dvwCleaningOrder
Table: dsCleaningOrders1.CleaningOrders

Double-click the Open button and implement its event as follows:

System::Void btnOpen_Click(System::Object *  sender, System::EventArgs *  e)
{
	 if( this->txtReceiptNumber->Text->Equals(S"") )
	 {
		 MessageBox::Show(S"Please enter a receipt number");
		 this->txtReceiptNumber->Focus();
		 return;
	 }

	 int iReceiptNumber = this->txtReceiptNumber->Text->ToInt32(0);

	 this->oleDbDataAdapter1->Fill(this->dsCleaningOrders1);
this->dvwCleaningOrder->RowFilter = String::Concat(S"CleaningOrderID = '", iReceiptNumber.ToString(), "'");

	 this->txtCustomerName->DataBindings->Clear();
	 this->txtCustomerPhone->DataBindings->Clear();
	 this->dtpDateLeft->DataBindings->Clear();
	 this->dtpTimeLeft->DataBindings->Clear();
	 this->dtpDateExpected->DataBindings->Clear();
	 this->dtpTimeExpected->DataBindings->Clear();
	 this->txtShirtsUnitPrice->DataBindings->Clear();
	 this->txtShirtsQuantity->DataBindings->Clear(); 
this->txtShirtsSubTotal->DataBindings->Clear();
this->txtPantsUnitPrice->DataBindings->Clear();
this->txtPantsQuantity->DataBindings->Clear();
this->txtPantsSubTotal->DataBindings->Clear(); 
this->cboItem1->DataBindings->Clear(); 
this->txtItem1UnitPrice->DataBindings->Clear(); 
 this->txtItem1Quantity->DataBindings->Clear(); 
 this->txtItem1SubTotal->DataBindings->Clear(); 
 this->cboItem2->DataBindings->Clear(); 
 this->txtItem2UnitPrice->DataBindings->Clear(); 
 this->txtItem2Quantity->DataBindings->Clear(); 
 this->txtItem2SubTotal->DataBindings->Clear(); 
 this->cboItem3->DataBindings->Clear(); 
 this->txtItem3UnitPrice->DataBindings->Clear(); 
 this->txtItem3Quantity->DataBindings->Clear(); 
 this->txtItem3SubTotal->DataBindings->Clear(); 
 this->cboItem4->DataBindings->Clear(); 
 this->txtItem4UnitPrice->DataBindings->Clear(); 
 this->txtItem4Quantity->DataBindings->Clear(); 
 this->txtItem4SubTotal->DataBindings->Clear(); 
 this->txtCleaningTotal->DataBindings->Clear(); 
 this->txtTaxRate->DataBindings->Clear(); 
 this->txtTaxAmount->DataBindings->Clear(); 
 this->txtOrderTotal->DataBindings->Clear();

	 this->txtCustomerName->DataBindings->Add(S"Text", this->dvwCleaningOrder, "CustomerName");
	 this->txtCustomerPhone->DataBindings->Add(S"Text", this->dvwCleaningOrder, "CustomerPhone");
	 this->dtpDateLeft->DataBindings->Add("Value", this->dvwCleaningOrder, "DateLeft");
	 this->dtpTimeLeft->DataBindings->Add("Value", this->dvwCleaningOrder, "TimeLeft");
	 this->dtpDateExpected->DataBindings->Add("Value", this->dvwCleaningOrder, "DateExpected");
	 this->dtpTimeExpected->DataBindings->Add("Value", this->dvwCleaningOrder, "TimeExpected");
	 this->txtShirtsUnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsUnitPrice");
	 this->txtShirtsQuantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsQuantity");
	 this->txtShirtsSubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsSubTotal");
	 this->txtPantsUnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsUnitPrice");
	 this->txtPantsQuantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsQuantity");
	 this->txtPantsSubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsSubTotal");
	 this->cboItem1->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1Name");
	 this->txtItem1UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1UnitPrice");
	 this->txtItem1Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1Quantity");
	 this->txtItem1SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1SubTotal");
	 this->cboItem2->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2Name");
	 this->txtItem2UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2UnitPrice");
	 this->txtItem2Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2Quantity");
	 this->txtItem2SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2SubTotal");
	 this->cboItem3->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3Name");
	 this->txtItem3UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3UnitPrice");
	 this->txtItem3Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3Quantity");
	 this->txtItem3SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3SubTotal");
	 this->cboItem4->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4Name");
	 this->txtItem4UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4UnitPrice");
	 this->txtItem4Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4Quantity");
	 this->txtItem4SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4SubTotal");
	 this->txtCleaningTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"CleaningTotal");
	 this->txtTaxRate->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"TaxRate");
	 this->txtTaxAmount->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"TaxAmount");
	 this->txtOrderTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"OrderTotal");

	 IsNewOrder = false;
}

In the Class View, right-click Form1 -> Add -> Add Function...
Set the characteristics as follows:
Return Type: void
Function Name: UpdateCleaningOrder
Access: private

Click Finish and implement the event as follows:

// This method is used to update an existing record if the user changes any of its values
void UpdateCleaningOrder(void)
{
	 // Find out if this is a new, usually started as blank, order
	 // If it is, trust that the user will save it by clicking the Save button
	 // If it's not, then, if the user changed the string that was in the
	 // Customer Name text box, update the current record
	 if( IsNewOrder == false )
	 {
	 String *strUpdate = String::Concat(S"UPDATE CleaningOrders ",
                       S"SET CustomerName = '",  this->txtCustomerName->Text, S"', ",
		S"CustomerPhone = '",  this->txtCustomerPhone->Text, S"', ",
		S"DateLeft = '", this->dtpDateLeft->Value.ToString(), S"', ",
		S"TimeLeft = '", this->dtpTimeLeft->Value.ToString(), S"', ",
		S"DateExpected = '", this->dtpDateExpected->Value.ToString(), S"', ",
		S"TimeExpected = '", this->dtpTimeExpected->Value.ToString(), S"', ",
		S"ShirtsUnitPrice = '", this->txtShirtsUnitPrice->Text, S"', ",
		S"ShirtsQuantity = '", this->txtShirtsQuantity->Text, S"', ",
		S"ShirtsSubTotal = '", this->txtShirtsSubTotal->Text, S"', ",
		S"PantsUnitPrice = '", this->txtPantsUnitPrice->Text, S"', ",
		S"PantsQuantity = '", this->txtPantsQuantity->Text, S"', ",
		S"PantsSubTotal = '", this->txtPantsSubTotal->Text, S"', ",
		S"Item1Name = '", this->cboItem1->Text, S"', ",
		S"Item1UnitPrice = '", this->txtItem1UnitPrice->Text, S"', ",
		S"Item1Quantity = '", this->txtItem1Quantity->Text, S"', ",
		S"Item1SubTotal = '", this->txtItem1SubTotal->Text, S"', ",
		S"Item2Name = '", this->cboItem2->Text, S"', ",
		S"Item2UnitPrice = '", this->txtItem2UnitPrice->Text, S"', ",
		S"Item2Quantity = '", this->txtItem2Quantity->Text, S"', ",
		S"Item2SubTotal = '", this->txtItem2SubTotal->Text, S"', ",
		S"Item3Name = '", this->cboItem3->Text, S"', ",
		S"Item3UnitPrice = '", this->txtItem3UnitPrice->Text, S"', ",
		S"Item3Quantity = '", this->txtItem3Quantity->Text, S"', ",
		S"Item3SubTotal = '", this->txtItem3SubTotal->Text, S"', ",
		S"Item4Name = '", this->cboItem4->Text, S"', ",
		S"Item4UnitPrice = '", this->txtItem4UnitPrice->Text, S"', ",
		S"Item4Quantity = '", this->txtItem4Quantity->Text, S"', ",
		S"Item4SubTotal = '", this->txtItem4SubTotal->Text, S"', ",
		S"CleaningTotal = '", this->txtCleaningTotal->Text, S"', ",
		S"TaxRate = '", this->txtTaxRate->Text, S"', ",
		S"TaxAmount = '", this->txtTaxAmount->Text, S"', ",
		S"OrderTotal = '", this->txtOrderTotal->Text, S"' ",
		S"WHERE CleaningOrderID = ",
		this->txtReceiptNumber->Text, S";");

	 OleDbCommand    *cmdDatabase = new OleDbCommand(strUpdate, oleDbConnection1);

	 oleDbConnection1->Open();

	 cmdDatabase->ExecuteNonQuery();
	 oleDbConnection1->Close();
	 }
}

Return to the form and click the Customer Name text box
In the Properties window, click the Events button and double-click Leave

Implement its event as follows:

System::Void txtCustomerName_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 if( IsNewOrder == false )
	 {
		UpdateCleaningOrder();
	 }
}

Return to the form and click the Customer Phone text box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtCustomerPhone_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 if( IsNewOrder == false )
	 {
		UpdateCleaningOrder();
	 }
}

Return to the form and double-click the Date Left date time picker control

Implement its ValueChanged event as follows:

System::Void dtpDateLeft_ValueChanged(System::Object *  sender, System::EventArgs *  e)
{
	 DateTime dateLeft = this->dtpDateLeft->Value;
	 DateTime timeLeft = this->dtpTimeLeft->Value;
	 DateTime time9AM  = 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 = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0);
	 }
	 else
	 {
 // If the clothese were left after 9AM, they will be availablethe following morning at 8AM
	 this->dtpDateExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
 this->dtpTimeExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
	 }
	
	 if( IsNewOrder == false )
	 {
		UpdateCleaningOrder();
	 }
}

Return to the form and double-click the Time Left date time picker control

Implement its ValueChanged event as follows:

System::Void dtpTimeLeft_ValueChanged(System::Object *  sender, System::EventArgs *  e)
{
	 DateTime dateLeft = this->dtpDateLeft->Value;
	 DateTime timeLeft = this->dtpTimeLeft->Value;
	 DateTime time9AM  = 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 = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0);
	 }
	 else
	 {
 // If the clothese were left after 9AM, they will be availablethe following morning at 8AM
 this->dtpDateExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
this->dtpTimeExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
	 }
	
	 if( IsNewOrder == false )
	 {
		 UpdateCleaningOrder();
	 }
}

Return to the form and double-click the Date Expected date time picker control

Implement its ValueChanged event as follows:

System::Void dtpDateExpected_ValueChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( IsNewOrder == false )
	 	 UpdateCleaningOrder();
}

Return to the form and double-click the Time Expected date time picker control

Implement its ValueChanged event as follows:

System::Void dtpTimeExpected_ValueChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( IsNewOrder == false )
	 	UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the Shirts

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtShirtsUnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the Shirts

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtShirtsQuantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the Pants

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtPantsUnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the Pants

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtPantsQuantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and double-click the first combo box under Pants

Implement its event as follows:

System::Void cboItem1_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( this->cboItem1->Text->Equals(S"None") )
	 {
		 this->txtItem1UnitPrice->Text = S"0.00";
		 this->txtItem1Quantity->Text  = S"0";
		 this->txtItem1SubTotal->Text  = S"0.00";
	 }

	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the first combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem1UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the first combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem1Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and double-click the second combo box under Pants

Implement its event as follows:

System::Void cboItem2_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( this->cboItem2->Text->Equals(S"None") )
	 {
		 this->txtItem2UnitPrice->Text = S"0.00";
		 this->txtItem2Quantity->Text  = S"0";
		 this->txtItem2SubTotal->Text  = S"0.00";
	 }

	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the second combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem2UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the second combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem2Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and double-click the third combo box under Pants

Implement its event as follows:

System::Void cboItem3_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( this->cboItem3->Text->Equals(S"None") )
	 {
		 this->txtItem3UnitPrice->Text = S"0.00";
		 this->txtItem3Quantity->Text  = S"0";
		 this->txtItem3SubTotal->Text  = S"0.00";
	 }

	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the third combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem3UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the third combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem3Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and double-click the fourth combo box under Pants

Implement its event as follows:

System::Void cboItem4_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
{
	 if( this->cboItem4->Text->Equals(S"None") )
	 {
		 this->txtItem4UnitPrice->Text = S"0.00";
		 this->txtItem4Quantity->Text  = S"0";
		 this->txtItem4SubTotal->Text  = S"0.00";
	 }

	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Unit Price text box corresponding to the fourth combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem4UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Qty text box corresponding to the fourth combo box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtItem4Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Return to the form and click the Tax Rate text box

In the Events section of the Properties window, double-click Leave and implement its event as follows:

System::Void txtTaxRate_Leave(System::Object *  sender, System::EventArgs *  e)
{
	 CalculateCleaningOrder();

	 if( IsNewOrder == false )
		 UpdateCleaningOrder();
}

Display the form and double-click the Close button

Implement its event as follows:

System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
{
	 Close();
}

Execute the application and create a few cleaning orders
Close the form
Execute the application again and try opening a cleaning order whose receipt number is 1001, then 1002, then 1003
Close the form