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

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;
}

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

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();
	}
}

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:

private void txtCustomerName_Leave(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:

private void txtCustomerPhone_Leave(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:

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();
	}
}

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

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();
	}
}

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

Implement its ValueChanged event as follows:

private void dtpDateExpected_ValueChanged(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:

private void dtpTimeExpected_ValueChanged(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:

private void txtShirtsUnitPrice_Leave(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:

private void txtShirtsQuantity_Leave(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:

private void txtPantsUnitPrice_Leave(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:

private void txtPantsQuantity_Leave(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:

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();
}

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:

private void txtItem1UnitPrice_Leave(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:

private void txtItem1Quantity_Leave(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:

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();
}

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:

private void txtItem2UnitPrice_Leave(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:

private void txtItem2Quantity_Leave(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:

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();
}

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:

private void txtItem3UnitPrice_Leave(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:

private void txtItem3Quantity_Leave(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:

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();
}

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:

private void txtItem4UnitPrice_Leave(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:

private void txtItem4Quantity_Leave(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:

private void txtTaxRate_Leave(object sender, System.EventArgs e)
{
	CalculateCleaningOrder();

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

Display the form and double-click the Close button

Implement its event as follows:

private void btnClose_Click(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
Change some values of an order
Close the form