Home

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 Practical Learning: Using a Data View

 
  1. Display the form then, in the Data section of the Toolbox, click DataView and click the form
  2. In the Properties window, change its values as follows:
    (Name): dvwCleaningOrder
    Table: dsCleaningOrders1.CleaningOrders
  3. Double-click the Open button
  4. Implement the event as follows:
     
    private void btnOpen_Click(object sender, System.EventArgs e)
    {
    	if( this.txtReceiptNumber.Text == "" )
    	{
    		MessageBox.Show("Please enter a receipt number");
    		this.txtReceiptNumber.Focus();
    		return;
    	}
    
    	int iReceiptNumber = int.Parse(this.txtReceiptNumber.Text);
    
    	this.oleDbDataAdapter1.Fill(this.dsCleaningOrders1);
    this.dvwCleaningOrder.RowFilter = "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("Text", this.dvwCleaningOrder, "CustomerName");
    	this.txtCustomerPhone.DataBindings.Add("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("Text", this.dvwCleaningOrder, "ShirtsUnitPrice");
    	this.txtShirtsQuantity.DataBindings.Add("Text", this.dvwCleaningOrder, "ShirtsQuantity");
    	this.txtShirtsSubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "ShirtsSubTotal");
    	this.txtPantsUnitPrice.DataBindings.Add("Text", this.dvwCleaningOrder, "PantsUnitPrice");
    	this.txtPantsQuantity.DataBindings.Add("Text", this.dvwCleaningOrder, "PantsQuantity");
    	this.txtPantsSubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "PantsSubTotal");
    	this.cboItem1.DataBindings.Add("Text", this.dvwCleaningOrder, "Item1Name");
    	this.txtItem1UnitPrice.DataBindings.Add("Text", this.dvwCleaningOrder, "Item1UnitPrice");
    	this.txtItem1Quantity.DataBindings.Add("Text", this.dvwCleaningOrder, "Item1Quantity");
    	this.txtItem1SubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "Item1SubTotal");
    	this.cboItem2.DataBindings.Add("Text", this.dvwCleaningOrder, "Item2Name");
    	this.txtItem2UnitPrice.DataBindings.Add("Text", this.dvwCleaningOrder, "Item2UnitPrice");
    	this.txtItem2Quantity.DataBindings.Add("Text", this.dvwCleaningOrder, "Item2Quantity");
    	this.txtItem2SubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "Item2SubTotal");
    	this.cboItem3.DataBindings.Add("Text", this.dvwCleaningOrder, "Item3Name");
    	this.txtItem3UnitPrice.DataBindings.Add("Text", this.dvwCleaningOrder, "Item3UnitPrice");
    	this.txtItem3Quantity.DataBindings.Add("Text", this.dvwCleaningOrder, "Item3Quantity");
    	this.txtItem3SubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "Item3SubTotal");
    	this.cboItem4.DataBindings.Add("Text", this.dvwCleaningOrder, "Item4Name");
    	this.txtItem4UnitPrice.DataBindings.Add("Text", this.dvwCleaningOrder, "Item4UnitPrice");
    	this.txtItem4Quantity.DataBindings.Add("Text", this.dvwCleaningOrder, "Item4Quantity");
    	this.txtItem4SubTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "Item4SubTotal");
    	this.txtCleaningTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "CleaningTotal");
    	this.txtTaxRate.DataBindings.Add("Text", this.dvwCleaningOrder, "TaxRate");
    	this.txtTaxAmount.DataBindings.Add("Text", this.dvwCleaningOrder, "TaxAmount");
    	this.txtOrderTotal.DataBindings.Add("Text", this.dvwCleaningOrder, "OrderTotal");
    
    	IsNewOrder = false;
    }
  5. In the Class View, right-click Form1 -> Add -> Add Method...
  6. Set the characteristics as follows:
    Method Access: internal
    Return Type: void
    Function Name: UpdateCleaningOrder
  7. Click Finish and implement the event as follows:
     
    internal void UpdateCleaningOrder()
    {
    	// 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 = "UPDATE CleaningOrders " +
    	"SET CustomerName = '" +  this.txtCustomerName.Text + "', " +
    	"CustomerPhone = '" +  this.txtCustomerPhone.Text + "', " +
    	"DateLeft = '" + this.dtpDateLeft.Value.ToString() + "', " +
    	"TimeLeft = '" + this.dtpTimeLeft.Value.ToString() + "', " +
    	"DateExpected = '" + this.dtpDateExpected.Value.ToString() + "', " +
    	"TimeExpected = '" + this.dtpTimeExpected.Value.ToString() + "', " +
    	"ShirtsUnitPrice = '" + this.txtShirtsUnitPrice.Text + "', " +
    	"ShirtsQuantity = '" + this.txtShirtsQuantity.Text + "', " +
    	"ShirtsSubTotal = '" + this.txtShirtsSubTotal.Text + "', " +
    	"PantsUnitPrice = '" + this.txtPantsUnitPrice.Text + "', " +
    	"PantsQuantity = '" + this.txtPantsQuantity.Text + "', " +
    	"PantsSubTotal = '" + this.txtPantsSubTotal.Text + "', " +
    	"Item1Name = '" + this.cboItem1.Text + "', " +
    	"Item1UnitPrice = '" + this.txtItem1UnitPrice.Text + "', " +
    	"Item1Quantity = '" + this.txtItem1Quantity.Text + "', " +
    	"Item1SubTotal = '" + this.txtItem1SubTotal.Text + "', " +
    	"Item2Name = '" + this.cboItem2.Text + "', " +
    	"Item2UnitPrice = '" + this.txtItem2UnitPrice.Text + "', " +
    	"Item2Quantity = '" + this.txtItem2Quantity.Text + "', " +
    	"Item2SubTotal = '" + this.txtItem2SubTotal.Text + "', " +
    	"Item3Name = '" + this.cboItem3.Text + "', " +
    	"Item3UnitPrice = '" + this.txtItem3UnitPrice.Text + "', " +
    	"Item3Quantity = '" + this.txtItem3Quantity.Text + "', " +
    	"Item3SubTotal = '" + this.txtItem3SubTotal.Text + "', " +
    	"Item4Name = '" + this.cboItem4.Text + "', " +
    	"Item4UnitPrice = '" + this.txtItem4UnitPrice.Text + "', " +
    	"Item4Quantity = '" + this.txtItem4Quantity.Text + "', " +
    	"Item4SubTotal = '" + this.txtItem4SubTotal.Text + "', " +
    	"CleaningTotal = '" + this.txtCleaningTotal.Text + "', " +
    	"TaxRate = '" + this.txtTaxRate.Text + "', " +
    	"TaxAmount = '" + this.txtTaxAmount.Text + "', " +
    	"OrderTotal = '" + this.txtOrderTotal.Text + "' " +
    	"WHERE CleaningOrderID = " +
    	this.txtReceiptNumber.Text + ";";
    
    OleDbCommand cmdDatabase = new OleDbCommand(strUpdate, oleDbConnection1);
    
    		oleDbConnection1.Open();
    
    		cmdDatabase.ExecuteNonQuery();
    		oleDbConnection1.Close();
    	}
    }
  8. Return to the form and click the Customer Name text box
  9. In the Properties window, click the Events button and double-click Leave
  10. Implement its event as follows:
     
    private void txtCustomerName_Leave(object sender, System.EventArgs e)
    {
    	if( IsNewOrder == false )
    	{
    		UpdateCleaningOrder();
    	}
    }
  11. Return to the form and click the Customer Phone text box
  12. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtCustomerPhone_Leave(object sender, System.EventArgs e)
    {
    	if( IsNewOrder == false )
    	{
    		UpdateCleaningOrder();
    	}
    }
  13. Return to the form and double-click the Date Left date time picker control
  14. Implement its ValueChanged event as follows:
     
    private void dtpDateLeft_ValueChanged(object sender, System.EventArgs e)
    {
    	DateTime dateLeft = this.dtpDateLeft.Value;
    	DateTime timeLeft = this.dtpTimeLeft.Value;
    	DateTime time9AM  = new DateTime(timeLeft.Year, timeLeft.Month, timeLeft.Day, 9, 0, 0);
    
    	// If the customer leaves clothes before 9AM...
    	if( timeLeft <= time9AM )
    	{
    		// ... then they should be ready the same day after 5PM
    		this.dtpDateExpected.Value = dateLeft;
    this.dtpTimeExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0);
    	}
    	else
    	{
    	// If the clothese were left after 9AM, they will be availablethe following morning at 8AM
    	this.dtpDateExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
    	this.dtpTimeExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
    	}
    	
    	if( IsNewOrder == false )
    	{
    		UpdateCleaningOrder();
    	}
    }
  15. Return to the form and double-click the Time Left date time picker control
  16. Implement its ValueChanged event as follows:
     
    private void dtpTimeLeft_ValueChanged(object sender, System.EventArgs e)
    {
    	DateTime dateLeft = this.dtpDateLeft.Value;
    	DateTime timeLeft = this.dtpTimeLeft.Value;
    	DateTime time9AM  = new DateTime(timeLeft.Year, timeLeft.Month, timeLeft.Day, 9, 0, 0);
    
    	// If the customer leaves clothes before 9AM...
    	if( timeLeft <= time9AM )
    		{
    		// ... then they should be ready the same day after 5PM
    		this.dtpDateExpected.Value = dateLeft;
    this.dtpTimeExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0);
    	}
    	else
    	{
    	// If the clothese were left after 9AM, they will be availablethe following morning at 8AM
    	this.dtpDateExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
    this.dtpTimeExpected.Value = new DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
    	}
    	
    	if( IsNewOrder == false )
    	{
    		UpdateCleaningOrder();
    	}
    }
  17. Return to the form and double-click the Date Expected date time picker control
  18. Implement its ValueChanged event as follows:
     
    private void dtpDateExpected_ValueChanged(object sender, System.EventArgs e)
    {
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  19. Return to the form and double-click the Time Expected date time picker control
  20. Implement its ValueChanged event as follows:
     
    private void dtpTimeExpected_ValueChanged(object sender, System.EventArgs e)
    {
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  21. Return to the form and click the Unit Price text box corresponding to the Shirts
  22. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtShirtsUnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  23. Return to the form and click the Qty text box corresponding to the Shirts
  24. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtShirtsQuantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  25. Return to the form and click the Unit Price text box corresponding to the Pants
  26. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtPantsUnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  27. Return to the form and click the Qty text box corresponding to the Pants
  28. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtPantsQuantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  29. Return to the form and double-click the first combo box under Pants
  30. Implement its event as follows:
     
    private void cboItem1_SelectedIndexChanged(object sender, System.EventArgs e)
    {
    	if( this.cboItem1.Text == "None" )
    	{
    		this.txtItem1UnitPrice.Text = "0.00";
    		this.txtItem1Quantity.Text  = "0";
    		this.txtItem1SubTotal.Text  = "0.00";
    	}
    
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  31. Return to the form and click the Unit Price text box corresponding to the first combo box
  32. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem1UnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  33. Return to the form and click the Qty text box corresponding to the first combo box
  34. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem1Quantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  35. Return to the form and double-click the second combo box under Pants
  36. Implement its event as follows:
     
    private void cboItem2_SelectedIndexChanged(object sender, System.EventArgs e)
    {
    	if( this.cboItem2.Text == "None" )
    	{
    		this.txtItem2UnitPrice.Text = "0.00";
    		this.txtItem2Quantity.Text  = "0";
    		this.txtItem2SubTotal.Text  = "0.00";
    	}
    
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  37. Return to the form and click the Unit Price text box corresponding to the second combo box
  38. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem2UnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  39. Return to the form and click the Qty text box corresponding to the second combo box
  40. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem2Quantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  41. Return to the form and double-click the third combo box under Pants
  42. Implement its event as follows:
     
    private void cboItem3_SelectedIndexChanged(object sender, System.EventArgs e)
    {
    	if( this.cboItem3.Text == "None" )
    	{
    		this.txtItem3UnitPrice.Text = "0.00";
    		this.txtItem3Quantity.Text  = "0";
    		this.txtItem3SubTotal.Text  = "0.00";
    	}
    
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  43. Return to the form and click the Unit Price text box corresponding to the third combo box
  44. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem3UnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  45. Return to the form and click the Qty text box corresponding to the third combo box
  46. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem3Quantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  47. Return to the form and double-click the fourth combo box under Pants
  48. Implement its event as follows:
     
    private void cboItem4_SelectedIndexChanged(object sender, System.EventArgs e)
    {
    	if( this.cboItem4.Text == "None" )
    	{
    		this.txtItem4UnitPrice.Text = "0.00";
    		this.txtItem4Quantity.Text  = "0";
    		this.txtItem4SubTotal.Text  = "0.00";
    	}
    
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  49. Return to the form and click the Unit Price text box corresponding to the fourth combo box
  50. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem4UnitPrice_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  51. Return to the form and click the Qty text box corresponding to the fourth combo box
  52. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtItem4Quantity_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  53. Return to the form and click the Tax Rate text box
  54. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    private void txtTaxRate_Leave(object sender, System.EventArgs e)
    {
    	CalculateCleaningOrder();
    
    	if( IsNewOrder == false )
    		UpdateCleaningOrder();
    }
  55. Display the form and double-click the Close button
  56. Implement its event as follows:
     
    private void btnClose_Click(object sender, System.EventArgs e)
    {
    	Close();
    }
  57. Execute the application and create a few cleaning orders
     
    Georgetown Cleaning Services - Cleaning Order
    Georgetown Cleaning Services - Cleaning Order
  58. Close the form
  59. Execute the application again and try opening a cleaning order whose receipt number is 1001, then 1002, then 1003
  60. Change some values of an order
  61. Close the form

 
 

Previous Copyright © 2005-2016, FunctionX