Home

ADO.NET: Using a Collection Class

     

Introduction

As you may imagine, a database is primarily a list of objects. As such, you can use a collection class in your database or even use a formal database in your collection-based application. To do this, we saw that you start by creating a connection to a database:

Connection

After establishing a connection to a database, you can use a command to specify the type of operation you want to perform, such as selecting records from the columns of a table, optionally using a condition. To get the data produced by the command, we saw that you could either use a data reader or a data adapter. If you use a data adapter, you can retrieve the values from the data adapter and store them in a data set object:

Data Connection to a Data Set

This is where, once again, a data set object becomes (very) valuable. Remember that a data set object contains one or more tables. Once you can identify a table from a data set, you can create a class that shares its structure. Then use the data from that table and temporarily store it in a collection-based class:

Once the data is available in a collection class, you can manipulate it using a regular class you would have created. This allows you to use your knowledge of collections to explore a database.

Practical LearningPractical Learning: Introducing Databases and Collection Classes

  1. Create a new Windows Application named CollegeParkAutoParts3
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type CollegeParkAutoParts.cs and press Enter
  4. Double-click the middle of the form and write code as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace CollegeParkAutoParts3
    {
        public partial class CollegeParkAutoParts : Form
        {
            public CollegeParkAutoParts()
            {
                InitializeComponent();
            }
    
            void CreateDatabase()
            {
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCPAP = 
    		    new SqlCommand("CREATE DATABASE CollegeParkAutoParts1;", cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show(
    		  "A database named CollegeParkAutoParts has been created.");
                }
    
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CollegeParkAutoParts1;" +
                                      "Integrated Security=SSPI;"))
                {
                    string strCreate = "CREATE TABLE AutoParts(" +
                                       "PartNumber int identity(100001, 1) NOT NULL, " +
                                       "CarYear int, " +
                                       "Make varchar(50), " +
                                       "Model varchar(50), " +
                                       "Category varchar(50), " +
                                       "PartName varchar(100), " +
                                       "UnitPrice money, " +
                                       "CONSTRAINT PK_AutoParts PRIMARY " +
                                       "    KEY (PartNumber));";
                    SqlCommand cmdCPAP = new SqlCommand(strCreate, cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show("A table named Parts has been created.");
                }
    
                using (SqlConnection cnnCPAP =
                    new SqlConnection("Data Source=(local);" +
                                      "Database=CollegeParkAutoParts1;" +
                                      "Integrated Security=SSPI;"))
                {
                    string strCreate = "CREATE TABLE CustomersOrders(" +
                                       "CustomerOrderID int identity(1001, 1) NOT NULL, " +
                                       "ReceiptNumber int NOT NULL, " +
                                       "PartNumber int NULL, " +
                                       "PartName varchar(100), " +
                                       "UnitPrice money, " +
                                       "Quantity int, " +
                                       "SubTotal money, " +
    				   "PartsTotal money, " +
    				   "TaxRate decimal(6, 2), " +
    				   "TaxAmount money, " +
    				   "OrderTotal money, " +
                                       "CONSTRAINT PK_CustomersOrderts PRIMARY " +
                                       "    KEY (CustomerOrderID));";
                    SqlCommand cmdCPAP = new SqlCommand(strCreate, cnnCPAP);
                    cnnCPAP.Open();
    
                    cmdCPAP.ExecuteNonQuery();
                    MessageBox.Show("A table named Parts has been created.");
                }
            }
    
            private void CollegeParkAutoParts_Load(object sender, EventArgs e)
            {
                CreateDatabase();
            }
        }
    }
  5. Execute the application to create the database
  6. Close the form and return to your programming environment
  7. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  8. Set the name to MakeEditor and click Add
  9. Design the form as follows:
     
    Make Editor
    Control Text Name Other Properties
    Label Label &Make:    
    TextBox TextBox   txtMake Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Make Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  10. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  11. Set the name to ModelEditor and click Add
  12. Design the form as follows:
     
    College Park Auto Parts: Model Editor
    Control Text Name Other Properties
    Label Label &Model:    
    TextBox TextBox   txtModel Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Model Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  13. To create a dialog box, in the Solution Explorer, right-click CollegeParkAutoParts3 -> Add -> Windows Form...
  14. Set the name to CategoryEditor and click Add
  15. Design the form as follows:
     
    College Park Auto Parts: Category Editor
    Control Text Name Other Properties
    Label Label &Category:    
    TextBox TextBox   txtCategory Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Category Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  16. On the main menu, click Project -> Add Windows Form...
  17. Set the Name to PartEditor and click Add
  18. Design the form as follows:
     
    College Park Auto-Part - Part Editor
     
    Control Text Name Other Properties
    Label Label &Year:    
    ComboBox ComboBox   cbxYears  
    Label Label &Make:    
    ComboBox ComboBox   cbxMakes  
    Button Button New C&ategory... btnNewMake  
    Label Label M&odel:    
    ComboBox ComboBox   cbxModels  
    Button Button New Mo &del... btnNewModel  
    Label Label &Category:    
    ComboBox ComboBox   cbxCategories  
    Button Button New Ca&tegory btnNewCategory  
    Label Label &Part Name:    
    TextBox TextBox   txtPartName  
    Label Label &Unit Price:    
    TextBox TextBox 0.00 txtUnitPrice TextAlign: Right
    Label Label Part #:    
    TextBox TextBox   txtPartNumber Enabled: False
    Button Button Submit btnSubmit  
    Button Button Close btnClose DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text College Park Auto -Parts: Part Editor
    StartPosition CenterScreen
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  19. Double-click the New Make button and implement its event as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace CollegeParkAutoParts3
    {
        public partial class PartEditor : Form
        {
            public PartEditor()
            {
                InitializeComponent();
            }
    
            private void btnNewMake_Click(object sender, EventArgs e)
            {
                MakeEditor editor = new MakeEditor();
    
                if (editor.ShowDialog() == DialogResult.OK)
                {
                    if (editor.txtMake.Text.Length > 0)
                    {
                        string strMake = editor.txtMake.Text;
    
                        // Make sure the category is not yet in the list
                        if (cbxMakes.Items.Contains(strMake))
                            MessageBox.Show(strMake + " is already in the list");
                        else
                        {
                            // Since this is a new category, add it to the combox box
                            cbxMakes.Items.Add(strMake);
                        }
    
                        cbxMakes.Text = strMake;
                    }
                }
            }        
        }
    }
  20. Return to the Part Editor form and double-click the New Model button
  21. Implement its event as follows:
    private void btnNewModel_Click(object sender, EventArgs e)
    {
        ModelEditor editor = new ModelEditor();
    
        if (editor.ShowDialog() == DialogResult.OK)
        {
            if (editor.txtModel.Text.Length > 0)
            {
                string strModel = editor.txtModel.Text;
    
                // Make sure the category is not yet in the list
                if (cbxModels.Items.Contains(strModel))
                    MessageBox.Show(strModel + " is already in the list");
                else
                {
                    // Since this is a new category, add it to the combox box
                    cbxModels.Items.Add(strModel);
                }
    
                cbxModels.Text = strModel;
            }
        }
    }
  22. Return to the Part Editor form and double-click the New Category button
  23. Implement its event as follows:
    private void btnNewCategory_Click(object sender, EventArgs e)
    {
        CategoryEditor editor = new CategoryEditor();
    
        if (editor.ShowDialog() == DialogResult.OK)
        {
            if (editor.txtCategory.Text.Length > 0)
            {
                string strCategory = editor.txtCategory.Text;
    
                // Make sure the category is not yet in the list
                if (cbxCategories.Items.Contains(strCategory))
                    MessageBox.Show(strCategory + " is already in the list");
                else
                {
                    // Since this is a new category, add it to the combo box
                    cbxCategories.Items.Add(strCategory);
                }
    
                cbxCategories.Text = strCategory;
            }
        }
    }
  24. Return to the Part Editor form and double-click the Submit button
  25. Implement the event as follows:
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        double UnitPrice = 0.00;
    
        if (cbxYears.Text == "")
        {
            MessageBox.Show("You must specify the year of the car.");
            return;
        }
    
        if (cbxMakes.Text == "")
        {
            MessageBox.Show("You must specify the car manufacturer.");
            return;
        }
    
        if (cbxModels.Text == "")
        {
            MessageBox.Show("You must specify the car model.");
            return;
        }
    
        if (txtPartName.Text == "")
        {
            MessageBox.Show("You must enter the name of the part.");
            return;
        }
    
        if (txtUnitPrice.Text == "")
        {
            MessageBox.Show("You must enter the unit price of the part.");
            return;
        }
    
        try
        {
            UnitPrice = double.Parse(txtUnitPrice.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Unit Price.");
        }
    
        using (SqlConnection cnnNewPart =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strAutoPart = "INSERT INTO AutoParts(" +
                                 "CarYear, Make, Model, Category, " +
                                 "PartName, UnitPrice) VALUES('" +
                                 cbxYears.Text + "', '" +
                                 cbxMakes.Text + "', '" +
                                 cbxModels.Text + "', '" +
                                 cbxCategories.Text + "', '" +
                                 txtPartName.Text + "', '" +
                                 txtUnitPrice.Text + "');";
    
            SqlCommand cmdAutoPart = new SqlCommand(strAutoPart, cnnNewPart);
    
            cnnNewPart.Open();
            cmdAutoPart.ExecuteNonQuery();
    
            MessageBox.Show("The new part has been added.");
        }
    }
  26. Save the file and close the form
  27. In the Solution Explorer, double-click CollegeParkAutoParts.cs to open the form
  28. To create an icon, on the main menu, click Project -> Add New Item...
  29. In the Templates list, click Icon File
  30. Set the Name to cpap1 and click Add
  31. Right-click the white area and click Delete Image Type
  32. Design the 16x16, 16 colors version of the icon as follows:
     
  33. On the main menu, click File -> Save cpap1.ico As
  34. Select the bin\Debug folder of the current folder and click Save
  35. On the main menu, click File -> Close
  36. In the Solution Explorer, expand bin and expand Debug
  37. In the Solution Explorer, right-click the Debug folder -> Add -> New Item...
  38. In the Templates list, make sure Icon File is selected.
    Set the Name to cpap2 and click Add
  39. Right-click the white area and click Delete Image Type
  40. Design the 16x16, 16 colors version of the icon as follows:
     
  41. Save the file and close the icon window
  42. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  43. In the Templates list, make sure Icon File is selected.
    Set the Name to year1 and click Add
  44. Right-click the white area and click Delete Image Type
  45. Design the 16x16, 16 colors version of the icon as follows:
     
  46. Save the file and close the icon window
  47. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  48. In the Templates list, make sure Icon File is selected.
    Set the Name to year2 and click Add
  49. Right-click the white area and click Delete Image Type
  50. Design the 16x16, 16 colors version of the icon as follows:
     
  51. Save the file and close the icon window
  52. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  53. In the Templates list, make sure Icon File is selected.
    Set the Name to make1 and click Add
  54. Right-click the white area and click Delete Image Type
  55. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  56. Save the file and close the icon window
  57. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  58. In the Templates list, make sure Icon File is selected.
    Set the Name to make2 and click Add
  59. Right-click the white area and click Delete Image Type
  60. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  61. Save the file and close the icon window
  62. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  63. In the Templates list, make sure Icon File is selected.
    Set the Name to model1 and click Add
  64. Right-click the white area and click Delete Image Type
  65. Design the 16x16, 16 colors version of the icon as follows:
     
  66. Save the file and close the icon window
  67. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  68. In the Templates list, make sure Icon File is selected.
    Set the Name to model2 and click Add
  69. Right-click the white area and click Delete Image Type
  70. Design the 16x16, 16 colors version of the icon as follows:
     
  71. Save the file and close the icon window
  72. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  73. In the Templates list, make sure Icon File is selected.
    Set the Name to category1 and click Add
  74. Right-click the white area and click Delete Image Type
  75. Design the 16x16, 16 colors version of the icon as follows:
     
  76. Save the file and close the icon window
  77. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  78. In the Templates list, make sure Icon File is selected.
    Set the Name to category2 and click Add
  79. Right-click the white area and click Delete Image Type
  80. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Minus
  81. Save the file and close the icon window
  82. From the Components section of the Toolbox, click ImageList and click the form
  83. In the Properties window, click (Name) and type imgAutoParts
  84. Click the ellipsis button of the Images field
  85. In the Image Collection Editor, click Add
  86. Locate the folder that contains the icons you created and display it in the Look In combo box
  87. Select cpap1.ico and click Open
  88. 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
     
    Image Collection Editor
  89. Click OK
  90. Design the form as follows:
     
    College Park Auto Parts - Form Design
    Control Text Name Other Properties
    Label Label College Park Auto-Parts   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Panel     Height: 2
    GroupBox GroupBox Part Identification    
    TreeView TreeView   tvwAutoParts ImageList: imgAutoParts
    GroupBox GroupBox Available Parts    
    ListView 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 GroupBox Customer Order - Selected Parts    
    Label Label Part #    
    Label Label Part Name    
    Label Label Unit Price    
    Label Label Qty    
    Label Label Sub Total    
    TextBox TextBox   txtPartNumber  
    TextBox TextBox   txtPartName  
    TextBox TextBox 0.00 txtUnitPrice TextAlign: Right
    TextBox TextBox 0 txtQuantity TextAlign: Right
    TextBox TextBox 0.00 txtSubTotal TextAlign: Right
    Button Button Add/Select btnAdd
    ListView 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 GroupBox Order Summary
    Button Button New Au&to Part... btnNewAutoPart  
    Label Label Receipt #:  
    TextBox TextBox txtSave
    Button Button Save btnSave
    Label Label Tax Rate:
    TextBox TextBox 7.75 txtTaxRate TextAlign: Right
    Label Label %
    Label Label Parts Total:
    TextBox TextBox 0.00 txtPartsTotal TextAlign: Right
    Button Button &New Customer Order btnNewCustomerOrder  
    Label Label Receipt #:  
    TextBox TextBox txtOpen
    Button Button Save btnOpen
    Label Label Tax Amount:
    TextBox TextBox 0.00 txtTaxAmount TextAlign: Right
    Label Label Order Total:
    TextBox TextBox 0.00 txtOrderTotal TextAlign: Right
    Button Button Close btnClose  
  91. Click the Available Parts list view
  92. In the Properties window, click the Events button and, in the Events section, double-click DoubleClick
  93. Implement the event as follows:
    private void CollegeParkAutoParts_Load(object sender, EventArgs e)
    {
        // CreateDatabase();
    }
    
    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();
    }
  94. Return to the CollegeParkAutoParts form
  95. Click the Unit Price text box and, in the Events section of the Properties window, double-click Leave
  96. 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()
    {
        if (lvwSelectedParts.Items.Count == 0)
        {
            txtTaxAmount.Text = "0.00";
            txtPartsTotal.Text = "0.00";
            txtOrderTotal.Text = "0.00";
            return;
        }
    
        // 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;
    
        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;
    
        txtPartsTotal.Text = PartsTotal.ToString("F");
        txtTaxAmount.Text = TaxAmount.ToString("F");
        txtOrderTotal.Text = OrderTotal.ToString("F");
    }
  97. Return to the CollegeParkAutoParts form and click the Qty text box
  98. In the Events section of the Properties, click Leave, then click the arrow of its combo box and select txtUnitPrice_Leave
  99. Return to the CollegeParkAutoParts form and click the Selected Part list view (the list view in the bottom-right section of the form)
  100. In the Events section of the Properties window, double-click DoubleClick
  101. 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();
    }

Using a Collection-Based Class

As reviewed above, before using a regular class to manipulate a database, you should first create a class. To do this, you must know the structure of the table that will be represented by the class. This means that you should be familiar with the tables in the data set object. After creating the class, to get the values from a table, you can declare a variable from a collection-based class. Then, you can use the properties and methods of the collection class, just as we saw in previous lessons.

Practical LearningPractical Learning: Using a Collection-Based Class

  1. In the Solution Explorer, right-click CollegeParkAutoParts3 -> Add -> Class...
  2. Set the name to AutoPart and press Enter
  3. Create a class as follows:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace CollegeParkAutoParts3
    {
        public class AutoPart
        {
            public int PartNumber;
            public int CarYear;
            public string Make;
            public string Model;
            public string Category;
            public string PartName;
            public double UnitPrice;
        }
    }
  4. Display the Part Editor form and double-click an empty area of its body
  5. Implement the event as follows:
    private void ResetPartEditor()
    {
        cbxYears.Items.Clear();
        cbxMakes.Items.Clear();
        cbxModels.Items.Clear();
        cbxCategories.Items.Clear();
        txtPartName.Text = "";
        txtUnitPrice.Text = "0.00";
        txtPartNumber.Text = "";
    
        // Put the years in the top combo box
        // Start with next year down to 1960
        for (int i = DateTime.Today.Year + 1; i >= 1960; i--)
            cbxYears.Items.Add(i.ToString());
    
        // Prepare a data set object for the parts
        DataSet dsParts = new DataSet("PartsSet");
    
        // Establish a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            // Create a command that will select the parts
            SqlCommand cmdParts =
                new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            // Open the connection
            cnnParts.Open();
    
            // Create a data adapter that will get the values from the table
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            // Store those values in the data set
            sdaParts.Fill(dsParts);
    
            // Create an auto part object
            AutoPart StoreItem = null;
            // Create an empty list of auto parts
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            // Check each record from the (only) table in the data set
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                // Use the auto part object ...
                StoreItem = new AutoPart();
                // ... to create a record object
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                // Once the record is ready, store it in the collection variable
                lstAutoParts.Add(StoreItem);
            }
    
            // To avoid duplicate values in the combo boxes, 
            // we will use collection classes
            List<string> lstMakes = new List<string>();
            List<string> lstCategories = new List<string>();
    
            // Check the list of makes
            foreach (AutoPart part in lstAutoParts)
            {
                // If the list doesn't yet contain the make, add it
                if (!lstMakes.Contains(part.Make))
                    lstMakes.Add(part.Make);
            }
    
            // Once we have the list of makes, 
            // put them in the Make combo box
            foreach (string strMake in lstMakes)
                cbxMakes.Items.Add(strMake);
    
            foreach (AutoPart part in lstAutoParts)
            {
                if (!lstCategories.Contains(part.Category))
                    lstCategories.Add(part.Category);
            }
    
            foreach (string strCategory in lstCategories)
                cbxCategories.Items.Add(strCategory);
        }
    }
    
    private void PartEditor_Load(object sender, EventArgs e)
    {
        ResetPartEditor();
    }
  6. Call the ResetPartEditor() method before the closing curly bracket of the Click event of the Submit button:
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        . . . No Change            
                 
        ResetPartEditor();
    }
  7. Return to the Part Editor form and double-click the Year combo box
  8. Implement the event as follows:
    private void cbxYears_SelectedIndexChanged(object sender, EventArgs e)
    {
        // If the user was working on a previous part, cancel it
        cbxModels.Items.Clear();
    }
  9. Return to the Part Editor form and double-click the Make combo box
  10. Implement its event as follows:
    private void cbxMakes_SelectedIndexChanged(object sender, EventArgs e)
    {
        // Create a data set of parts
        DataSet dsParts = new DataSet("PartsSet");
    
        // Establish a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
    	// Create a command that will select the parts
            SqlCommand cmdParts = 
    	    new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
    	// Open the connection
            cnnParts.Open();
    
    	// Create a data adapter that will get 
    	// the record from the command
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
    
    	// Store those records in the data set
            sdaParts.Fill(dsParts);
    
    	// Create an empty object that can receive one record
            AutoPart StoreItem = null;
    	// Create an empty list that can receive a list of records
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
    	// Check each record
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
    	    // Get the values of the current record
                StoreItem = new AutoPart();
    	    // Store the values in the object
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
    	    // Once the record is ready, store it in the collection
                lstAutoParts.Add(StoreItem);
            }
    
    	// This will hold the list of parts
            List<string> lstModels = new List<string>();
    
            foreach (AutoPart part in lstAutoParts)
            {
                if ((part.CarYear == int.Parse(cbxYears.Text)) &&
                    (part.Make == cbxMakes.Text))
                {
                    if (!lstModels.Contains(part.Model))
                        lstModels.Add(part.Model);
                }
            }
    
            foreach (string strModel in lstModels)
                cbxModels.Items.Add(strModel);
        }
    }
  11. Access the CollegeParkAutoParts form, right-click it and click View Code
  12. Make the following changes:
    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();
    
        // Create an empty data set
        DataSet dsParts = new DataSet("PartsSet");
    
        // Create a connection to the database
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            // Use a command to specify what action we want to take
            SqlCommand cmdParts = 
    		new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            // Open the connection
            cnnParts.Open();
    
            // Use a data adapter to retrieve the values from the command
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            // Fill the data set with the values from the data adapater
            sdaParts.Fill(dsParts);
    
            // Prepare a variable from the class we will use
            AutoPart StoreItem = null;
            // Create a collection
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            // Check each record from the table from the data set
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                // Identify each record as a value of our class
                StoreItem = new AutoPart();
                // Get the values of the record and put them in the class
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                // Store the record in the collection
                lstAutoParts.Add(StoreItem);
            }
    
            // Now that we have the records, 
            // we can use them as a normal collection
            foreach (TreeNode nodYear in nodRoot.Nodes)
            {
                List<string> lstMakes = new List<string>();
    
                foreach (AutoPart  part in lstAutoParts)
                {
                    if (nodYear.Text == part.CarYear.ToString())
                    {
                        if (!lstMakes.Contains(part.Make))
                            lstMakes.Add(part.Make);
                    }
                }
    
                foreach (string strMake in lstMakes)
                    nodYear.Nodes.Add(strMake, strMake, 4, 5);
            }
    
            foreach (TreeNode nodYear in nodRoot.Nodes)
            {
                foreach (TreeNode nodMake in nodYear.Nodes)
                {
                    List<string> lstModels = new List<string>();
    
                    foreach (AutoPart  part in lstAutoParts)
                    {
                        if ((nodYear.Text == part.CarYear.ToString()) &&
                            (nodMake.Text == part.Make))
                        {
                            if (!lstModels.Contains(part.Model))
                                lstModels.Add(part.Model);
                        }
                    }
    
                    foreach (string strModel in lstModels)
                        nodMake.Nodes.Add(strModel, strModel, 6, 7);
                }
            }
    
            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 (AutoPart  part in lstAutoParts)
                        {
                            if ((nodYear.Text == part.CarYear.ToString()) &&
                                (nodMake.Text == part.Make) &&
                                (nodModel.Text == part.Model))
                            {
                                if (!lstCategories.Contains(part.Category))
                                    lstCategories.Add(part.Category);
                            }
                        }
    
                        foreach (string strCategory in lstCategories)
                        nodModel.Nodes.Add(strCategory, strCategory, 8, 9);
                    }
                }
            }
        }
    }
    
    private void CollegeParkAutoParts_Load(object sender, EventArgs e)
    {
        // CreateDatabase();
        ShowAutoParts();
    }
    
    . . . No Change
    	
    
  13. Return to the CollegeParkAutoParts form and double-click the New Auto Part button
  14. Implement the event as follows:
    private void btnNewAutoPart_Click(object sender, EventArgs e)
    {
        PartEditor editor = new PartEditor();
    
        if (editor.ShowDialog() == DialogResult.Cancel)
            ShowAutoParts();
    }
  15. Execute the application
  16. Click the New Auto Part button and use the Part Editor to create a few parts
  17. Close the forms and return to your programming environment
  18. Access the  CollegeParkAutoParts form and click the tree view
  19. In the Properties window, click the Events button and, in the Events section, double-click NodeMouseClick
  20. Implement the event as follows:
    private void tvwAutoParts_NodeMouseClick(object sender, 
    		TreeNodeMouseClickEventArgs e)
    {
        TreeNode nodClicked = e.Node;
    
        if (nodClicked.Level == 4)
            lvwAutoParts.Items.Clear();
    
        DataSet dsParts = new DataSet("PartsSet");
    
        using (SqlConnection cnnParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts = 
    		new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            cnnParts.Open();
    
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            sdaParts.Fill(dsParts);
    
            AutoPart StoreItem = null;
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                StoreItem = new AutoPart();
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
               lstAutoParts.Add(StoreItem);
           }
    
           try
           {
                foreach (AutoPart part in lstAutoParts)
                {
                    if ((part.Category == nodClicked.Text) &&
                        (part.Model == nodClicked.Parent.Text) &&
                        (part.Make == nodClicked.Parent.Parent.Text) &&
                        (part.CarYear.ToString() == 
    			nodClicked.Parent.Parent.Parent.Text))
                    {
                        ListViewItem lviAutoPart =
                            new ListViewItem(part.PartNumber.ToString());
    
                        lviAutoPart.SubItems.Add(part.PartName);
                  lviAutoPart.SubItems.Add(part.UnitPrice.ToString("F"));
                        lvwAutoParts.Items.Add(lviAutoPart);
                    }
                }
            }
            catch (NullReferenceException)
            {
            }
        }
    }
  21. Return to the CollegeParkAutoParts form and double-click the Add/Select button
  22. 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;
        }
                
        DataSet dsParts = new DataSet("PartsSet");
    
        using (SqlConnection cnnParts =
              new SqlConnection("Data Source=(local);" +
                                "Database=CollegeParkAutoParts1;" +
                                "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts = 
    	    new SqlCommand("SELECT * FROM AutoParts;", cnnParts);
            cnnParts.Open();
    
            SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
            sdaParts.Fill(dsParts);
    
            AutoPart StoreItem = null;
            List<AutoPart> lstAutoParts = new List<AutoPart>();
    
            foreach (DataRow row in dsParts.Tables[0].Rows)
            {
                StoreItem = new AutoPart();
                StoreItem.PartNumber = int.Parse(row[0].ToString());
                StoreItem.CarYear = int.Parse(row[1].ToString());
                StoreItem.Make = row[2].ToString();
                StoreItem.Model = row[3].ToString();
                StoreItem.Category = row[4].ToString();
                StoreItem.PartName = row[5].ToString();
                StoreItem.UnitPrice = double.Parse(row[6].ToString());
                lstAutoParts.Add(StoreItem);
            }
    
            foreach (AutoPart part in lstAutoParts)
            {
                 if (part.PartNumber == int.Parse(txtPartNumber.Text))
                 {
                    ListViewItem lviSelectedPart =
                        new ListViewItem(part.PartNumber.ToString());
    
                    lviSelectedPart.SubItems.Add(part.PartName);
                    lviSelectedPart.SubItems.Add(part.UnitPrice.ToString());
                    lviSelectedPart.SubItems.Add(txtQuantity.Text);
                    lviSelectedPart.SubItems.Add(txtSubTotal.Text);
                    lvwSelectedParts.Items.Add(lviSelectedPart);
                }
            }
    
            CalculateOrder();
        }
    }
  23. Return to the CollegeParkAutoParts form and double-click the Save Customer Order button
  24. Implement the event as follows:
    private void btnSaveCustomerOrder_Click(object sender, EventArgs e)
    {
        if (lvwSelectedParts.Items.Count == 0)
            return;
        else
        {
            int ReceiptNumber = 0;
            string strReceiptNumber = "";
    
            // The following code allows us to get 
    	// the highest receipt number
            //1. Create a connection to the database
            using (SqlConnection cnnReceiptNumber =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts1;" +
                                     "Integrated Security=SSPI;"))
            {
                // Use the MAX() function to locate 
    	    // the highest receipt number
                string strSelect = 
    		"SELECT MAX(ReceiptNumber) FROM CustomersOrders;";
                SqlCommand cmdReceiptNumber = 
    		new SqlCommand(strSelect, cnnReceiptNumber);
    
                // 2. Open the connection
                cnnReceiptNumber.Open();
    
                // 3. Use a data reader to get 
    	    //  the values from the Customer Orders table
                SqlDataReader rdrReceiptNumber = 
    		cmdReceiptNumber.ExecuteReader();
    
                // 4. Read through the receipt numbers to get to the last
                while (rdrReceiptNumber.Read())
                    strReceiptNumber = rdrReceiptNumber[0].ToString();
    
                // If there is no receipt number (yet), 
    	    // then set the first to 1000
                if (strReceiptNumber == "")
                    strReceiptNumber = "1000";
    
                // Now that we have a receipt number, we will increase it by 1
                ReceiptNumber = int.Parse(strReceiptNumber) + 1;
            }
    
            // The following variable will hold the whole INSERT statement
            string strCustomerOrder = "";
    
            // Check each selected part and get it ready to add to the table
            // Each selected part of this order will have a common receipt #
            for (int i = 0; i < lvwSelectedParts.Items.Count; i++)
            {
                strCustomerOrder = 
    		strCustomerOrder + 
    		"INSERT INTO CustomersOrders(" +
                    "ReceiptNumber, PartNumber, PartName, " +
                    "UnitPrice, Quantity, SubTotal) VALUES('" +
                    ReceiptNumber.ToString() + "', '" +
                    lvwSelectedParts.Items[i].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[1].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[2].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[3].Text + "', '" +
                    lvwSelectedParts.Items[i].SubItems[4].Text + "'); ";
            }
    
            // We will also use the same common receipt number 
            // to save the values of the receipt
             strCustomerOrder = 
    		strCustomerOrder + 
    		"INSERT INTO CustomersOrders(" +
                    "ReceiptNumber, PartsTotal, TaxRate, " +
                    "TaxAmount, OrderTotal) VALUES('" +
                    ReceiptNumber.ToString() + "', '" +
                    txtPartsTotal.Text + "', '" +
                    txtTaxRate.Text + "', '" +
                    txtTaxAmount.Text + "', '" +
                    txtOrderTotal.Text + "');";
    
            using (SqlConnection cnnCustomerOrder =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts1;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomerOrder = 
    		new SqlCommand(strCustomerOrder, cnnCustomerOrder);
                cnnCustomerOrder.Open();
    
                cmdCustomerOrder.ExecuteNonQuery();
    
                // Let the user know that the order has been created
                MessageBox.Show("The customer order has been saved.");
                // Reset the form
                btnNewCustomerOrder_Click(sender, e);
            }
        }
    }
  25. Return to the CollegeParkAutoParts form and double-click the Open button
  26. Implement the event as follows:
    private void btnOpen_Click(object sender, EventArgs e)
    {
        lvwSelectedParts.Items.Clear();
    
        using (SqlConnection cnnCustomerOrder =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strSelect = "SELECT * FROM CustomersOrders " +
                               "WHERE (ReceiptNumber = '" +
                               txtReceiptNumber.Text + "') AND " +
                               "PartNumber IS NOT NULL;";
    
            SqlCommand cmdCustomerOrder =
            new SqlCommand(strSelect, cnnCustomerOrder);
            SqlDataAdapter sdaCustomerOrder =
    	        new SqlDataAdapter(cmdCustomerOrder);
    
            cnnCustomerOrder.Open();
    
            DataSet dsCustomerOrder = new DataSet("CustomersOrdersSet");
            sdaCustomerOrder.Fill(dsCustomerOrder);
    
            for (int i = 0; i < dsCustomerOrder.Tables[0].Rows.Count; i++)
            {
                DataRow rowOrder = dsCustomerOrder.Tables[0].Rows[i];
    
                ListViewItem lviOrder =
                    new ListViewItem(rowOrder["PartNumber"].ToString());
                lviOrder.SubItems.Add(rowOrder["PartName"].ToString());
                lviOrder.SubItems.Add(rowOrder["UnitPrice"].ToString());
                lviOrder.SubItems.Add(rowOrder["Quantity"].ToString());
                lviOrder.SubItems.Add(rowOrder["SubTotal"].ToString());
                lvwSelectedParts.Items.Add(lviOrder);
            }
        }
    
        using (SqlConnection cnnInvoice =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            string strInvoice = "SELECT ReceiptNumber, PartsTotal, TaxRate, " +
                                "TaxAmount, OrderTotal " +
                                "FROM CustomersOrders " +
                                "WHERE (ReceiptNumber = '" +
                                txtReceiptNumber.Text + "') AND " +
                                "      (PartNumber IS NULL) AND " +
                                "      (PartName IS NULL);";
    
            SqlCommand cmdInvoice = new SqlCommand(strInvoice, cnnInvoice);
    
            cnnInvoice.Open();
            SqlDataReader rdrInvoice = cmdInvoice.ExecuteReader();
    
            while (rdrInvoice.Read())
            {
                txtPartsTotal.Text = rdrInvoice[1].ToString();
                txtTaxRate.Text = rdrInvoice[2].ToString();
                txtTaxAmount.Text = rdrInvoice[3].ToString();
                txtOrderTotal.Text = rdrInvoice[4].ToString();
            }
        }
    }
  27. Return to the CollegeParkAutoParts form and double-click the New Customer button
  28. Implement the event as follows:
    private void btnNewCustomerOrder_Click(object sender, EventArgs e)
    {
        ShowAutoParts();
        lvwAutoParts.Items.Clear();
        txtPartNumber.Text = "";
        txtPartName.Text = "";
        txtUnitPrice.Text = "0.00";
        txtQuantity.Text = "0";
        txtSubTotal.Text = "0.00";
        lvwSelectedParts.Items.Clear();
        txtPartsTotal.Text = "0.00";
        txtTaxRate.Text = "5.75";
        txtTaxAmount.Text = "0.00";
        txtOrderTotal.Text = "0.00";
        txtReceiptNumber.Text = "";
    }
  29. Return to the CollegeParkAutoParts form and double-click the Close button
  30. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  31. Create a few customer orders and save them
  32. Close the forms and return to your programming environment
  33. Execute the application again and open a previously saved order
  34. Close the forms and return to your programming environment
 

Home Copyright © 2010-2011 FunctionX