Sample Data Set Application: College Park Auto Parts

	Sample Data Set Application: College Park Auto Parts

Introduction

This is a sample database used by a company that sells auto parts. It is called College Park Auto-Parts.

This sample XML application explores the DataSet class of the .NET Framework. The necessary tables of the database are created as DataTable objects. Of course, each data table contains its columns that are based on the DataColumn class.

Once the data set and its tables have been created, the user can create the records. These start with a list of the store items, which are mostly the auto parts sold in the store. When using the application, the user is then able to create customers orders.

Practical Learning: Introducing Data Sets

Start Microsoft Visual C#
Create a new Console Application named CollegeParkAutoParts4
To create an icon, in the Solution Explorer, right-click CollegeParkAutoParts4 -> Add -> New Item...
In the Templates list, click Icon File
Set the Name to cpap1 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
On the main menu, click File -> Save cpap1.ico As
Select the bin\Debug folder of the current folder and click Save
On the main menu, click File -> Close
In the Solution Explorer, expand bin and expand Debug
In the Solution Explorer, right-click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to cpap2 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to year1 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to year2 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to make1 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to make2 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to model1 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to model2 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to category1 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
In the Templates list, make sure Icon File is selected.
Set the Name to category2 and click Add
Right-click the white area and click Delete Image Type
Design the 16x16, 16 colors version of the icon as follows:
Save the file and close the icon window
In the Solution Explorer, right-click Form1.cs and click Rename
Type Central.cs and press Enter twice to display the Central form
From the Components section of the Toolbox, click ImageList and click the form
In the Properties window, click (Name) and type imgAutoParts
Click the ellipsis button of the Images field
In the Image Collection Editor, click Add
Locate the folder that contains the icons you created and display it in the Look In combo box
Select cpap1.ico and click Open
In the same way, add the other pictures in the following order: cpap2.ico, year1.ico, year2.ico, make1.ico, make2.ico, model1.ico, model2.ico, category1.ico, and category1.ico
Click OK

Design the form as follows:

Control	Text	Name	Other Properties
Label	College Park Auto-Parts		Font: Times New Roman, 20.25pt, style=Bold ForeColor: Blue
Panel			Height: 2
GroupBox	Part Identification
TreeView		tvwAutoParts	ImageList: imgAutoParts
GroupBox	Available Parts
ListView		lvwAutoParts	FullRowSelect: True GridLines: True View: Details
Columns	(Name)	Text	TextAlign	Width
	colPartNumber	Part #
	colPartName	Part Name		300
	colUnitPrice	Unit Price	Right	80
GroupBox	Customer Order - Selected Parts
Label	Part #
Label	Part Name
Label	Unit Price
Label	Qty
Label	Sub Total
TextBox		txtPartNumber
TextBox		txtPartName
TextBox	0.00	txtUnitPrice	TextAlign: Right
TextBox	0	txtQuantity	TextAlign: Right
TextBox	0.00	txtSubTotal	TextAlign: Right
Button	Add/Select	btnAdd
ListView		lvwSelectedParts	FullRowSelect: True GridLines: True View: Details
Columns	(Name)	Text	TextAlign	Width
	colPartNumberSelected	Part #		45
	colPartNameSelected	Part Name		274
	colUnitPriceSelected	Unit Price	Right	58
	colQuantitySelected	Qty	Right	28
	colSubTotalSelected	Sub-Total	Right	58
GroupBox	Order Summary
Button	New Au&to Part...	btnNewAutoPart
Label	Receipt #:
TextBox		txtSave
Button	Save	btnSave
Label	Tax Rate:
TextBox	7.75	txtTaxRate	TextAlign: Right
Label	%
Label	Parts Total:
TextBox	0.00	txtPartsTotal	TextAlign: Right
Button	&New Customer Order	btnNewCustomerOrder
Label	Receipt #:
TextBox		txtOpen
Button	Save	btnOpen
Label	Tax Amount:
TextBox	0.00	txtTaxAmount	TextAlign: Right
Label	Order Total:
TextBox	0.00	txtOrderTotal	TextAlign: Right
Button	Close	btnClose

Click the Available Parts list view
In the Properties window, click the Events button and, in the Events section, double-click DoubleClick

Implement the event as follows:

private void lvwAutoParts_DoubleClick(object sender, EventArgs e)
{
    ListViewItem lviAutoPart = lvwAutoParts.SelectedItems[0];

    if( (lvwAutoParts.SelectedItems.Count == 0) ||
        (lvwAutoParts.SelectedItems.Count > 1) )
                return;

    txtPartNumber.Text = lviAutoPart.Text;
    txtPartName.Text = lviAutoPart.SubItems[1].Text;
    txtUnitPrice.Text = lviAutoPart.SubItems[2].Text;

    txtQuantity.Text = "1";
    txtSubTotal.Text = lviAutoPart.SubItems[2].Text;

    txtQuantity.Focus();
}

Return to the Central form
Click the Unit Price text box and, in the Events section of the Properties window, double-click Leave

Implement the event as follows:

private void txtUnitPrice_Leave(object sender, EventArgs e)
{
    double UnitPrice = 0.00D;
    int Quantity = 0;
    double SubTotal = 0.00D;

    try
    {
        UnitPrice = double.Parse(txtUnitPrice.Text);
    }
    catch (FormatException)
    {
        MessageBox.Show("Invalid Unit Price!");
    }

    try { Quantity = int.Parse(txtQuantity.Text); }
    catch (FormatException)
    {
        MessageBox.Show("Invalid Quandtity!");
    }

    SubTotal = UnitPrice * Quantity;
    txtSubTotal.Text = SubTotal.ToString("F");
}

internal void CalculateOrder()
{
    // Calculate the current total order and update the order
    double PartsTotal = 0.00;
    double TaxRate = 0.00;
    double TaxAmount = 0.00;
    double OrderTotal = 0.00;

    ListViewItem lviSelectedPart = lvwSelectedParts.Items[0];

    foreach (ListViewItem lvi in lvwSelectedParts.Items)
    {
        ListViewItem.ListViewSubItem SubItem = lvi.SubItems[4];

        PartsTotal += double.Parse(SubItem.Text);
    }

    try
    {
        TaxRate = double.Parse(txtTaxRate.Text) / 100;
    }
    catch (FormatException)
    {
        MessageBox.Show("Invalid Tax Rate");
    }

    TaxAmount = PartsTotal * TaxRate;
    OrderTotal = PartsTotal + TaxAmount;

    txtTotalOrder.Text = PartsTotal.ToString("F");
    txtTaxAmount.Text = TaxAmount.ToString("F");
    txtOrderTotal.Text = OrderTotal.ToString("F");
}

Return to the Central form and click the Qty text box
In the Events section of the Properties, click Leave, then click the arrow of its combo box and select txtUnitPrice_Leave
On the Central form, click the Selected Part list view (the list view in the bottom-right section of the form)
In the Events section of the Properties window, double-click DoubleClick

Implement the event as follows:

private void lvwSelectedParts_DoubleClick(object sender, EventArgs e)
{
    ListViewItem lviSelectedPart = lvwSelectedParts.SelectedItems[0];

    if( (lvwSelectedParts.SelectedItems.Count == 0) ||
        (lvwSelectedParts.SelectedItems.Count > 1) )
        return;

    txtPartNumber.Text = lviSelectedPart.Text;
    txtPartName.Text = lviSelectedPart.SubItems[1].Text;
    txtUnitPrice.Text = lviSelectedPart.SubItems[2].Text;
    txtQuantity.Text = lviSelectedPart.SubItems[3].Text;
    txtSubTotal.Text = lviSelectedPart.SubItems[4].Text;

    lvwSelectedParts.Items.Remove(lviSelectedPart);
    CalculateOrder();
}

Save all

A Data Grid View for Data Entry

The data grid view is a control that resembles a list view; that is, it is made of cells that hold values. One of the differences is that, when a data grid view is added to a form, it is empty and dark.

As opposed to a list view, instead of visually creating the columns and/or the items, you can specify the source of the records. This would equipped the data grid view with the necessary columns and the records in the data set.

To visually create a data grid view, from the Data section of the Toolbox, you can click DataGridView and click the form .

Practical Learning: Introducing Data Records

To create a dialog box, on the main menu, click Project -> Add Windows Form...
Set the name to ListOfCategories and click Add
From the Data section of the Toolbox, click DataSet and click the form
On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
In the Properties window, change the characteristics of the data set as follows:
DataSetName: Categories
(Name): dsCategories
Click the Tables field and click its ellipsis button
In the Tables Collection Editor, click Add
In the Properties list, change the characteristics of the table as follows:
TableName: Category
(Name): tblCategory
Click Columns and click its ellipsis button
In the Columns Collection Editor, click Add
Change the characteristics of the colums as follows:
ColumnName: CategoryName
(Name): colCategoryName
Click Close and click Close

Design the form as follows:

Control	Text	Name	Additional Properties
DataGridView		dgvStudents	DataSource: dsCategories DataMember: Category
Button	Close	btnClose

To create a dialog box, on the main menu, click Project -> Add Windows Form...
Set the name to ListOfMakes and click Add
From the Data section of the Toolbox, click DataSet and click the form
On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
In the Properties window, change the characteristics of the data set as follows:
DataSetName: Makes
(Name): dsMakes
Click the Tables field and click its ellipsis button
In the Tables Collection Editor, click Add
In the Properties list, change the characteristics of the table as follows:
TableName: Make
(Name): tblMake
Click Columns and click its ellipsis button
In the Columns Collection Editor, click Add
Change the characteristics of the colums as follows:
ColumnName: MakeName
(Name): colMakeName
Click Close and click Close

Design the form as follows:

Control	Text	Name	Additional Properties
DataGridView		dgvStudents	DataSource: dsMakes DataMember: Makes
Button	Close	btnClose

To create a dialog box, on the main menu, click Project -> Add Windows Form...
Set the name to ListOfModels and click Add
Display the list of makes form
Right-click dsMakes and click Copy
Display the list of models form
Right-click its body and click Paste
From the Data section of the Toolbox, click DataSet and click the form
On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
In the Properties window, change the characteristics of the data set as follows:
DataSetName: Models
(Name): dsModels
Click the Tables field and click its ellipsis button
In the Tables Collection Editor, click Add
In the Properties list, change the characteristics of the table as follows:
TableName: Car
(Name): tblCar
Click Columns and click its ellipsis button
In the Columns Collection Editor, click Add
Change the characteristics of the colums as follows:
ColumnName: Make
(Name): colMake
In the Columns Collection Editor, click Add
Change the characteristics of the colums as follows:
ColumnName: Model
(Name): colModel
Click Close and click Close
From the Data section of the Toolbox, click DataGridView and click the form
In the Properties window, change its characteristics as follows:
DataSource: dsModels
DataMember: Models

Under the Properties window, click Edit Columns... and change the columns as follows:

Selected Columns:	HeaderText	DataPropertyName	ColumnType	DataSource	DisplayMember
Make	Make	Make	DataGridViewComboBoxColumn	dsMakes	Make.MakeName
Model	Model	Model

Click OK

Design the form as follows:

Control	Text	Name	Additional Properties
DataGridView		dgvStudents	Anchor: Top, Bottom, Left, Right
Button	Close	btnClose	Anchor: Bottom, Right

To create a new form, in the Solution Explorer, right-click CollegeParkAutoParts4 -> Add -> Windows Form...
Set the Name to AutoParts and click Add
Display the list of models form
Click dsMakes
Press and hold Shift
Click dsModels
Release Shift
Press Ctrl + C
Display the auto parts form
Right-click its body and click Paste
Display the list of categories form
Right-click dsCategories and click Copy
Display the auto parts form
Right-click its body and click Paste
From the Data section of the Toolbox, click DataSet and click the form
In the Add Dataset dialog box, click the Untyped dataset radio button and click OK
In the Properties window, change the characteristics of the data set as follows:
DataSetName: StoreItems
(Name): dsStoreItems
Click Tables and click its ellipsis button
In the Properties list, change the characteristics of the table as follows:
TableName: StoreItem
(Name): tblStoreItem
Click Columns and click its ellipsis button

In the Columns Collection Editor, click Add continuously and create the columns as follows:

ColumnName	(Name)
PartNumber	colPartNumber
Year	colYear
Make	colMake
Model	colModel
Category	colCategory
PartName	colPartName
UnitPrice	colUnitPrice

Click Close and click Close
From the Data section of the Toolbox, click DataGridView and click the form
In the Properties window, change its characteristics as follows:
DataSource: dsStoreItems
DataMember: StoreItems

Under the Properties window, click Edit Columns... and change the columns as follows:

Selected Columns:	HeaderText	DataPropertyName	ColumnType	DataSource	DisplayMember	Width
Part #	Part #	PartNumber				50
Year	Year	Year				40
Make	Make	Make	DataGridViewComboBoxColumn	dsMakes	Make.MakeName	85
Model	Model	Model	DataGridViewComboBoxColumn	dsModels	CarModel.Model	130
Category	Category	Category	DataGridViewComboBoxColumn	dsCategories	Category.CategoryName	120
Part Name	Part Name	PartName				185
Unit Price	Unit Price	UnitPrice				65

Click OK

Design the form as follows:

Control	Text	Name	Other Properties
DataGridView		dgvStoreItems	DataSource: dsStoreItems DataMember: AutoPart Anchor: Top, Bottom, Left, Right
Button	New Make...	btnNewMake	Anchor: Bottom, Left
Button	New Model...	btnNewModel	Anchor: Bottom, Left
Button	New Category...	btnNewCategory	Anchor: Bottom, Left
Button	Close	btnClose	Anchor: Bottom, Right

Double-click the New Make button and implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace CollegeParkAutoParts4a
{
    public partial class AutoParts : Form
    {
        public AutoParts()
        {
            InitializeComponent();
        }

        private void btnNewMake_Click(object sender, EventArgs e)
        {
            ListOfMakes frmMakes = new ListOfMakes();
            frmMakes.ShowDialog();
        }
    }
}

Return to the Part Editor dialog box and double-click the New Model button

Implement the event as follows:

private void btnNewModel_Click(object sender, EventArgs e)
{
    ListOfModels frmModels = new ListOfModels();
    frmModels.ShowDialog();
}

Return to the Part Editor dialog box and double-click the New Category button

Implement the event as follows:

private void btnNewCategory_Click(object sender, EventArgs e)
{
    ListOfCategories frmCategories = new ListOfCategories();
    frmCategories.ShowDialog();
}

Display the Central form and double-click the New Auto Part button

Implement its event as follows:

private void btnNewAutoPart_Click(object sender, EventArgs e)
{
    AutoParts frmParts = new AutoParts();
            
    if (frmParts.ShowDialog() == DialogResult.Cancel)
        ShowAutoParts();
}

Save all

Introduction to Data Entry

A record on a table is represented as a row (horizontal) of data. A row, or record, is an object based on the DataRow class.

Before adding a new record to a table, you must let the table know. This is done by calling the DataTable.NewRow() method. Its syntax is:

public DataRow NewRow();

The DataTable.NewRow() method returns a DataRow object.

Practical Learning: Saving the Records of a Data Set

Display the Categories form and double-click the Close button

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace CollegeParkAutoParts4a
{
    public partial class ListOfCategories : Form
    {
        public ListOfCategories()
        {
            InitializeComponent();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            string strDirectory = @"C:\College Park Auto Parts";
            // If this directory doesn't exist, create it
            Directory.CreateDirectory(strDirectory);

            dsCategories.WriteXml(strDirectory + "\\categories.xml");
            Close();
        }
    }
}

Display the Makes form and double-click the Close button

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace CollegeParkAutoParts4a
{
    public partial class ListOfMakes : Form
    {
        public ListOfMakes()
        {
            InitializeComponent();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            string strDirectory = @"C:\College Park Auto Parts";
            // If this directory doesn't exist, create it
            Directory.CreateDirectory(strDirectory);

            dsMakes.WriteXml(strDirectory + "\\makes.xml");
            Close();
        }
    }
}

Display the Models form and double-click the Close button

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace CollegeParkAutoParts4a
{
    public partial class ListOfModels : Form
    {
        public ListOfModels()
        {
            InitializeComponent();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            string strDirectory = @"C:\College Park Auto Parts";
            // If this directory doesn't exist, create it
            Directory.CreateDirectory(strDirectory);

            dsModels.WriteXml(strDirectory + "\\models.xml");
            Close();
        }
    }
}

Display the Auto Parts form and double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    string strDirectory = @"C:\College Park Auto Parts";
    // If this directory doesn't exist, create it
    Directory.CreateDirectory(strDirectory);

    dsStoreItems.WriteXml(strDirectory + "\\StoreItems.xml");
    Close();
}

In the Events section of the Properties window, double-click Activated and implement the event as follows:
Execute the application
Click the New Auto Part button and click the New Make button
Create a few makes as follows:

Acura

Audi

BMW

Buick
Click the New Auto Part button and click the New Make button
Create a few makes as follows:

MDX

RDX

A4

A6

LaCrosse

Lucerne
Click the New Auto Part button and click the New Make button
Create a few makes as follows:

Exhaust

Air Intake

Cooling System

Engine Electrical
Click the Close button to close the form and return to your programming environment

Display the Part Editor form and double-click the Submit button
Implement the event as follows:
Display the Central form
Right-click the form and click View Code
Add the following namespaces in the top section of the file:
Return to the Central form and double-click the Save button

Implement its event as follows:

private void btnSave_Click(object sender, EventArgs e)
{
    BinaryFormatter bfmCustomerOrder = new BinaryFormatter();

    // We will store our files in the following folder    
    string strDirectory = @"C:\College Park Auto Parts\Receipts";
    DirectoryInfo dirInfo = new DirectoryInfo(strDirectory);

    string strFilename = strDirectory + "\\" + txtSave.Text + ".cap";

    List<PartsOrdered> lstOrderedParts = null;

    if (lvwSelectedParts.Items.Count == 0)
        return;
    else
    {
        lstOrderedParts = new List<PartsOrdered>();

        for (int i = 0; i < lvwSelectedParts.Items.Count; i++)
        {
            PartsOrdered part = new PartsOrdered();

            part.PartNumber = long.Parse(lvwSelectedParts.Items[i].Text);
            part.PartName = lvwSelectedParts.Items[i].SubItems[1].Text;
            part.UnitPrice = double.Parse(lvwSelectedParts.Items[i].SubItems[2].Text);
            part.Quantity = int.Parse(lvwSelectedParts.Items[i].SubItems[3].Text);
            part.SubTotal = double.Parse(lvwSelectedParts.Items[i].SubItems[4].Text);
            lstOrderedParts.Add(part);
        }

        FileStream stmCustomerOrder = new FileStream(strFilename, FileMode.Create);

        try
        {
            bfmCustomerOrder.Serialize(stmCustomerOrder, lstOrderedParts);
        }
        finally
        {
            stmCustomerOrder.Close();
        }
    }
}

Display the Makes form and double-click its Close button
Implement its event as follows:
Display the Models form and double-click its Close button
Implement its event as follows:
Display the Categories form and double-click its Close button
Implement its event as follows:
Display the AutoParts form and double-click the Close button
Implement the event as follows:

Display the list of Categories form and double-click an unoccupied area of its body

Implement the event as follows:

private void ListOfCategories_Load(object sender, EventArgs e)
{
    string strFilename = @"C:\College Park Auto Parts\categories.xml";

    if (File.Exists(strFilename))
        dsCategories.ReadXml(strFilename);
}

Display the Makes form and double-click an unoccupied area of its body

Implement the event as follows:

private void ListOfMakes_Load(object sender, EventArgs e)
{
    string strFilename = @"C:\College Park Auto Parts\makes.xml";

    if (File.Exists(strFilename))
        dsMakes.ReadXml(strFilename);
}

Display the Models form and double-click an unoccupied area of its body

Implement the event as follows:

private void ListOfModels_Load(object sender, EventArgs e)
{
    string strFilename = @"C:\College Park Auto Parts\makes.xml";

    if (File.Exists(strFilename))
        dsMakes.ReadXml(strFilename); 
            
    strFilename = @"C:\College Park Auto Parts\models.xml";
            if (File.Exists(strFilename))
                dsModels.ReadXml(strFilename);
}

Display the Models form and double-click an unoccupied area of its body
Implement the event as follows:
Return to the form and double-click an unoccupied area of its body

Implement the event as follows:

private void AutoParts_Load(object sender, EventArgs e)
{
    string strFilename = @"C:\College Park Auto Parts\StoreItems.xml";

    if( File.Exists(strFilename) )
        dsStoreItems.ReadXml(strFilename);
}

Return to the form and, in the Properties window, click the Events button

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

private void AutoParts_Activated(object sender, EventArgs e)
{
    string strFilename = @"C:\College Park Auto Parts\makes.xml";
            
    if (File.Exists(strFilename))
        dsMakes.ReadXml(strFilename);

    strFilename = @"C:\College Park Auto Parts\models.xml";

    if (File.Exists(strFilename))
        dsModels.ReadXml(strFilename);

    strFilename = @"C:\College Park Auto Parts\categories.xml";

    if (File.Exists(strFilename))
        dsCategories.ReadXml(strFilename);
}

Display the Central form and double-click the Save button
Implement its event as follows:
Save all

Locating a Record

Before performing any operation on a record, you must be able to locate it. That is, you must be able to identify a record among the various records of a table. The records of a table are stored in a list based on the DataRowCollection class. To locate a record in the DataTable.Rows collection, the DataRowCollection class has an indexed property that is defined as follows:

public DataRow this[int index] {get;}

The first record has an index of 0. The second record has an index of 1, and so on.

Practical Learning: Getting the Values of a Data Set

Display the Central form and, in the combo box on top of the Properties window, select Central
In the Properties window, click the Events button and double-click Loan

Implement the event as follows:

void ShowAutoParts()
{
    tvwAutoParts.Nodes.Clear();
    TreeNode nodRoot =
        tvwAutoParts.Nodes.Add("College Park Auto-Parts",
                               "College Park Auto-Parts", 0, 1);
    // Show the years nodes
    for (int years = DateTime.Today.Year + 1; years >= 1960; years--)
        nodRoot.Nodes.Add(years.ToString(), years.ToString(), 2, 3);

    tvwAutoParts.SelectedNode = nodRoot;
    // Expand the root node
    tvwAutoParts.ExpandAll();

    // This is the file that holds the list of store items on sale
    string strFilename = @"C:\College Park Auto Parts\StoreItems.xml";

    if (File.Exists(strFilename))
    {
        dsStoreItems.ReadXml(strFilename);

        // Add the makes to the years
        foreach (TreeNode nodYear in nodRoot.Nodes)
        {
            List<string> lstMakes = new List<string>();

            foreach (DataRow row in tblAutoPart.Rows)
            {
                if (nodYear.Text == row["Year"].ToString())
                {
                    if (!lstMakes.Contains(row["Make"].ToString()))
                        lstMakes.Add(row["Make"].ToString());
                }
            }

            foreach (string strMake in lstMakes)
                nodYear.Nodes.Add(strMake, strMake, 4, 5);
        }

        // Add the models to the makes
        foreach (TreeNode nodYear in nodRoot.Nodes)
        {
            foreach (TreeNode nodMake in nodYear.Nodes)
            {
                List<string> lstModels = new List<string>();

                foreach (DataRow row in tblAutoPart.Rows)
                {
                    if ((nodYear.Text == row["Year"].ToString()) &&
                        (nodMake.Text == row["Make"].ToString()))
                    {
                        if (!lstModels.Contains(row["Model"].ToString()))
                            lstModels.Add(row["Model"].ToString());
                    }
                }

                foreach (string strModel in lstModels)
                    nodMake.Nodes.Add(strModel, strModel, 6, 7);
            }
        }

        // Show the categories nodes
        foreach (TreeNode nodYear in nodRoot.Nodes)
        {
            foreach (TreeNode nodMake in nodYear.Nodes)
            {
                foreach (TreeNode nodModel in nodMake.Nodes)
                {
                    List<string> lstCategories = new List<string>();

                    foreach (DataRow row in tblAutoPart.Rows)
                    {
                        if ((nodYear.Text == row["Year"].ToString()) &&
                            (nodMake.Text == row["Make"].ToString()) &&
                            (nodModel.Text == row["Model"].ToString()))
                        {
                            if (!lstCategories.Contains(row["Category"].ToString()))
                                lstCategories.Add(row["Category"].ToString());
                        }
                    }

                    foreach (string strCategory in lstCategories)
                        nodModel.Nodes.Add(strCategory, strCategory, 8, 9);
                }
            }
        }
    }
}

private void Central_Load(object sender, EventArgs e)
{
    ShowAutoParts();
}

Return to the Central form and click the Part # text box

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

private void txtPartNumber_Leave(object sender, EventArgs e)
{
    foreach (DataRow row in tblAutoPart.Rows)
    {
        if (row["PartNumber"].ToString() == txtPartNumber.Text)
        {
            txtPartName.Text = row["PartName"].ToString();
            txtUnitPrice.Text = row["UnitPrice"].ToString();
            txtQuantity.Text = "0";
            txtSubTotal.Text = "0.00";
        }
    }
}

Return to the Central form and double-click the Add/Select button

Implement the event as follows:

private void btnAdd_Click(object sender, EventArgs e)
{
    if (txtPartNumber.Text.Length == 0)
    {
        MessageBox.Show("There is no part to be added to the order");
        return;
    }

    ListViewItem lviSelectedPart =
                new ListViewItem(txtPartNumber.Text);

    lviSelectedPart.SubItems.Add(txtPartName.Text);
    lviSelectedPart.SubItems.Add(txtUnitPrice.Text);
    lviSelectedPart.SubItems.Add(txtQuantity.Text);
    lviSelectedPart.SubItems.Add(txtSubTotal.Text);
    lvwSelectedParts.Items.Add(lviSelectedPart);

    CalculateOrder();
}

Display the Central form and double-click the New Customer Order button

Implement the event as follows:

private void btnNewCustomerOrder_Click(object sender, EventArgs e)
{
    int ReceiptNumber = 0;
    string strFilename = @"C:\College Park Auto Parts\invoices.xml";
    XmlDocument docInvoices = new XmlDocument();

    if (File.Exists(strFilename))
    {
        docInvoices.Load(strFilename);
        XmlElement elmRoot = docInvoices.DocumentElement;

        XmlNodeList lstInvoices = elmRoot.ChildNodes;
        XmlNodeList lstInvoiceNumbers =
		elmRoot.GetElementsByTagName("ReceiptNumber");
        ReceiptNumber = int.Parse(lstInvoiceNumbers[
		lstInvoiceNumbers.Count - 1].InnerText);
    }

    txtSave.Text = (ReceiptNumber + 1).ToString();
    txtTaxRate.Text = "5.75";
    txtTaxAmount.Text = "0.00";
    txtPartsTotal.Text = "0.00";
    txtOrderTotal.Text = "0.00";
    lvwSelectedParts.Items.Clear();
}

Return to the Central form and click the tree view
In the Properties window, click the Events button and, in the Events section, double-click NodeMouseClick

Implement the event as follows:

private void tvwAutoParts_NodeMouseClick(object sender,
		TreeNodeMouseClickEventArgs e)
{
    TreeNode nodClicked = e.Node;

    if (nodClicked.Level == 4)
        lvwAutoParts.Items.Clear();

    try
    {
        foreach (DataRow row in tblAutoPart.Rows)
        {
            if ((row["Category"].ToString() == nodClicked.Text) &&
                (row["Model"].ToString() == nodClicked.Parent.Text) &&
                (row["Make"].ToString() == nodClicked.Parent.Parent.Text) &&
                (row["Year"].ToString() == nodClicked.Parent.Parent.Parent.Text))
            {
                ListViewItem lviAutoPart =
                    new ListViewItem(row["PartNumber"].ToString());

                lviAutoPart.SubItems.Add(row["PartName"].ToString());
                lviAutoPart.SubItems.Add(row["UnitPrice"].ToString());
                lvwAutoParts.Items.Add(lviAutoPart);
            }
        }
    }
    catch (NullReferenceException)
    {
    }
}

Return to the Central form and double-click the Save button

Implement its event as follows:

private void btnSave_Click(object sender, EventArgs e)
{
    // We will use a receipt number for each invoice
    int ReceiptNumber = 0;
    // This is the file that holds the invoices
    string strFilename = @"C:\College Park Auto Parts\invoices.xml";
    // Get a reference t
    XmlDocument docInvoices = new XmlDocument();

    // If at least one invoice had previously been created,
    // then open the XML file that holds the invoices
    if( File.Exists(strFilename) )
    {
        // Store the XML file structure into the DOM
        docInvoices.Load(strFilename);
        // Get a reference to the root element
        XmlElement elmRoot = docInvoices.DocumentElement;

        // Get a list of the receipt numbers
        XmlNodeList lstInvoiceNumbers =
		elmRoot.GetElementsByTagName("ReceiptNumber");
        // Locate the last receipt number
        ReceiptNumber = int.Parse(lstInvoiceNumbers[
		lstInvoiceNumbers.Count - 1].InnerText);
    }
    else // if (!File.Exists(strFilename))
    {
        // If this is the first invoice to be created,
        // set the receipt number to 1
        ReceiptNumber = 1;
        // Create the default XML structure
        docInvoices.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                            "<CustomersOrders></CustomersOrders>");
    }

    // We will create a list of all the parts 
    // that the customer wants to purchase
    string strParts = "";

    foreach (ListViewItem lviPart in lvwSelectedParts.Items)
    {
        strParts = strParts + "<Part>";
        strParts = strParts + "<PartNumber>" + 
			lviPart.SubItems[0].Text + "</PartNumber>";
        strParts = strParts + "<PartName>" + 
			lviPart.SubItems[1].Text + "</PartName>";
        strParts = strParts + "<UnitPrice>" + 
			lviPart.SubItems[2].Text + "</UnitPrice>";
        strParts = strParts + "<Quantity>" + 
			lviPart.SubItems[3].Text + "</Quantity>";
        strParts = strParts + "<SubTotal>" + 
			lviPart.SubItems[4].Text + "</SubTotal>";
        strParts = strParts + "</Part>";
    }

    // Create an element named Invoice
    XmlElement elmXml = docInvoices.CreateElement("Invoice");
    string strInvoice = "<ReceiptNumber>" + txtSave.Text +
                        "</ReceiptNumber>" + strParts + 
                        "<PartsTotal>" + txtPartsTotal.Text +
                        "</PartsTotal><TaxRate>" + txtTaxRate.Text +
                        "</TaxRate><TaxAmount>" + txtTaxAmount.Text +
                        "</TaxAmount><OrderTotal>" + txtOrderTotal.Text +
                        "</OrderTotal>";

    // Create the XML code of the new element
    elmXml.InnerXml = strInvoice;
    // Add the new invoice to the file
    docInvoices.DocumentElement.AppendChild(elmXml);
    // Save the XML file
    docInvoices.Save(strFilename);

    // Reset the customer order
    ReceiptNumber = int.Parse(txtSave.Text);

    txtSave.Text = (ReceiptNumber + 1).ToString();
    txtTaxRate.Text = "5.75";
    txtTaxAmount.Text = "0.00";
    txtPartsTotal.Text = "0.00";
    txtOrderTotal.Text = "0.00";

    txtPartNumber.Text = "";
    txtPartName.Text = "";
    txtUnitPrice.Text = "0.00";
    txtQuantity.Text = "0";
    txtSubTotal.Text = "0.00";

    lvwSelectedParts.Items.Clear();
}

Return to the Central form and double-click the Open button

Implement its event as follows:

private void btnOpen_Click(object sender, EventArgs e)
{
    XmlDocument docInvoices = new XmlDocument();
    string strFilename = @"C:\College Park Auto Parts\invoices.xml";

    // Check that the file exists. If so, open it
    if (File.Exists(strFilename))
    {
        // This variable will allow us to know if we have the receipt number
        bool found = false;
        // Empty the list of selected parts
        lvwSelectedParts.Items.Clear();

        // After opening the XML file, store it in the DOM
        docInvoices.Load(strFilename);
        // Get a reference to the root element
        XmlElement elmRoot = docInvoices.DocumentElement;

        // Get a list of the invoices in the file
        XmlNodeList lstInvoices = elmRoot.GetElementsByTagName("Invoice");

        // Check each invoice
        foreach (XmlNode nodInvoice in lstInvoices)
        {
            // Look for an invoice that has the same number
            // as on the Open text box
            if (nodInvoice["ReceiptNumber"].InnerText == txtOpen.Text)
            {
                // If you find it, make a note
                found = true;
                txtOpen.Text = nodInvoice["ReceiptNumber"].InnerText;
                txtSave.Text = nodInvoice["ReceiptNumber"].InnerText;

                // Retrieve the values of the invoice
		// and display them on the form
                try
                {   
                    foreach (XmlNode nodeReceipt in nodInvoice.ChildNodes)
                    {
                        XmlNode node =
				nodeReceipt.NextSibling.NextSibling.ChildNodes[0];

                        ListViewItem lviInvoice = 
				new ListViewItem(node.InnerText);
                        lviInvoice.SubItems.Add(node.NextSibling.InnerText);
                        lviInvoice.SubItems.Add(
	node.NextSibling.NextSibling.InnerText);
                        lviInvoice.SubItems.Add(
	node.NextSibling.NextSibling.NextSibling.InnerText);
                        lviInvoice.SubItems.Add(
	node.NextSibling.NextSibling.NextSibling.NextSibling.InnerText);

                        lvwSelectedParts.Items.Add(lviInvoice);
                    }

                    txtPartsTotal.Text = nodInvoice["PartsTotal"].InnerText;
                    txtTaxRate.Text = nodInvoice["TaxRate"].InnerText;
                    txtTaxAmount.Text = nodInvoice["TaxAmount"].InnerText;
                    txtOrderTotal.Text = nodInvoice["OrderTotal"].InnerText;
                }
                catch (NullReferenceException)
                {
                }
            }
        }

        // If the invoice was not found, let the user know
        if (found == false)
             MessageBox.Show("There is no invlice with that receipt number");
    }// IF the XML file was not found, let the user know
    else
        MessageBox.Show("The file " + strFilename + " was not found");
}

Execute the application
Click the New Auto Part button and use the Part Editor to create a few parts
Close the Part Editor
Create a few customer part orders and save them:
Close the forms and return to your programming environment
Execute the application again and open a previously saved order
Close the forms and return to your programming environment


Home	Copyright © 2007 FunctionX, Inc.