ADO.NET, C#, and the .NET Framework

ADO.NET and C#

Introduction

ADO.NET is primarily a concept of how to create visual databases. It is neither a technology nor a programming environment. You must use a computer language, such as C#, to make a graphical application connect to an actual database in a server, such as Microsoft SQL Server. This also means that you will follow the rules of your programming language to make your application work. Besides the language, you can also use one or more library to get whatever behavior you want and is possible. An example library is the .NET Framework.

Windows Controls

Since we have reviewed different controls and their options, there is no reason to go through that again. Still, you should be familiar with the strengths, weaknesses, and options of each control. You should make sure you apply the appropriate restrictions where necessary such as making sure that user can only select a date instead of typing one,

ADO.NET and Collection Classes

Introduction

As you may imagine, a database is primarily a list of values. 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, you start by creating a connection to a database:

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 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:

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.

1. Start Microsoft Visual Studio
2. To create a new application, on the main menu, click FILE -> New -> Project...
3. Make sure Windows Forms Application. Set the Name to CollegeParkAutoParts2
4. Click OK
5. To create a new database, in the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
6. In the Server Name combo box, select your server or type (local)
7. Set the database name as CollegeParkAutoParts1 and click OK
8. In the Server Explorer, right-click the CollegeParkAutoParts1 connection and click New Query
9. Open the CollegeParkAutoParts1 file and select everything in it the document
10. Copy and paste it in the Query window
11. Right-click inside the document and click Execute
12. In the Solution Explorer, right-click Form1.cs and click Rename
13. Type CollegeParkAutoParts.cs and press Enter
14. On the message box that appears, click Yes
15. To create a dialog box, on the main menu, click PROJECT -> Add Windows Form...
16. Set the name to MakeEditor and click Add
17. Design the form as follows:
     

    Control Text Name Other Properties Label &Make:     TextBox   txtMake Modifiers: Public Button OK btnOK DialogResult: OK 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

18. To create a dialog box, on the main menu, click PROJECT -> Add Windows Form...
19. Set the name to ModelEditor and click Add
20. Design the form as follows:
     

    Control Text Name Other Properties Label &Model:     TextBox   txtModel Modifiers: Public Button OK btnOK DialogResult: OK 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

21. To create a dialog box, in the Solution Explorer, right-click CollegeParkAutoParts3 -> Add -> Windows Form...
22. Set the name to CategoryEditor and click Add
23. Design the form as follows:
     

    Control Text Name Other Properties Label &Category:     TextBox   txtCategory Modifiers: Public Button OK btnOK DialogResult: OK 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

24. On the main menu, click PROJECT -> Add Windows Form...
25. Set the Name to PartEditor and click Add
26. Design the form as follows:
     

    Control Text Name Other Properties Label &Year:     ComboBox   cbxYears   Label &Make:     ComboBox   cbxMakes   Button New C&ategory... btnNewMake   Label M&odel:     ComboBox   cbxModels   Button New Mo &del... btnNewModel   Label &Category:     ComboBox   cbxCategories   Button New Ca&tegory btnNewCategory   Label &Part Name:     TextBox   txtPartName   Label &Unit Price:     TextBox 0.00 txtUnitPrice TextAlign: Right Label Part #:     TextBox   txtPartNumber Enabled: False Button Submit btnSubmit   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

27. 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 CollegeParkAutoParts2
    {
        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.",
    	                                "College Park Auto-Parts",
            	                MessageBoxButtons.OK, MessageBoxIcon.Information);
            	    }
                        else
                        {
                            // Since this is a new category, add it to the combox box
                            cbxMakes.Items.Add(strMake);
                        }
    
                        cbxMakes.Text = strMake;
                    }
                }
            }        
        }
    }

28. Return to the Part Editor form and double-click the New Model button
29. 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.",
    	                        "College Park Auto-Parts",
            	                MessageBoxButtons.OK, MessageBoxIcon.Information);
          	    }
                else
                {
                    // Since this is a new category, add it to the combox box
                    cbxModels.Items.Add(strModel);
                }
    
                cbxModels.Text = strModel;
            }
        }
    }

30. Return to the Part Editor form and double-click the New Category button
31. 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.",
    	                        "College Park Auto-Parts",
            	                MessageBoxButtons.OK, MessageBoxIcon.Information);
            	}
                else
                {
                    // Since this is a new category, add it to the combo box
                    cbxCategories.Items.Add(strCategory);
                }
    
                cbxCategories.Text = strCategory;
            }
        }
    }

32. Return to the Part Editor form and double-click the Submit button
33. 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.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (cbxMakes.Text == "")
        {
            MessageBox.Show("You must specify the car manufacturer.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (cbxModels.Text == "")
        {
            MessageBox.Show("You must specify the car model.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (txtPartName.Text == "")
        {
            MessageBox.Show("You must enter the name of the part.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (txtUnitPrice.Text == "")
        {
            MessageBox.Show("You must enter the unit price of the part.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        try
        {
            UnitPrice = double.Parse(txtUnitPrice.Text);
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Unit Price.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        using (SqlConnection cnnNewPart =
               new SqlConnection("Data Source=(local);" +
                                 "Database=CollegeParkAutoParts1;" +
                                 "Integrated Security=SSPI;"))
        {
            SqlCommand cmdAutoPart =
            	new SqlCommand("INSERT INTO Inventory.AutoParts(" +
                                   "PartNumber, CarYear, Make, Model, Category, " +
                                   "PartName, UnitPrice) VALUES(" + txtPartNumber.Text + ", " +
                                   cbxYears.Text + ", N'" + cbxMakes.Text + "', N'" +
                                   cbxModels.Text + "', N'" + cbxCategories.Text + "', N'" +
                                   txtPartName.Text + "', " + double.Parse(txtUnitPrice.Text) + ");",
                                   cnnNewPart);
    
            cnnNewPart.Open();
            cmdAutoPart.ExecuteNonQuery();
    
            MessageBox.Show("The new part has been added.",
    	                "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

34. Display the College Park Auto-Parts form (the main form)
35. From the Components section of the Toolbox, click ImageList and click the form
36. In the Properties window, click (Name) and type imgAutoParts
37. Click the ellipsis button of the Images field
38. In the Image Collection Editor, click Add
39. Locate the folder that contains the resources for ated and display it in the Look In combo box
40. Select the following icons: Sign1, Sign2, Cliper1, Cliper2, Rulers1, Rulers2, Graph1, Graph2, Tool1, and Tool2
41. Click Open
     
    
42. Click OK
43. 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

44. Click the Available Parts list view
45. In the Properties window, click the Events button and, in the Events section, double-click DoubleClick
46. Implement the 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 CollegeParkAutoParts2
    {
        public partial class CollegeParkAutoParts : Form
        {
            public CollegeParkAutoParts()
            {
                InitializeComponent();
            }
    
            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();
            }
        }
    }

47. Return to the College Park Auto-Parts form
48. Click the Unit Price text box and, in the Events section of the Properties window, double-click Leave
49. 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!",
                            "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        try { Quantity = int.Parse(txtQuantity.Text); }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Quandtity!",
                            "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        SubTotal = UnitPrice * Quantity;
        txtSubTotal.Text = SubTotal.ToString("F");
    }
    
    internal void CalculateOrder()
    {
        if (lvwSelectedParts.Items.Count == 0)
        {
            txtPartsTotal.Text = "0.00";
            txtTaxAmount.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",
                            "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        TaxAmount = PartsTotal * TaxRate;
        OrderTotal = PartsTotal + TaxAmount;
    
        txtPartsTotal.Text = PartsTotal.ToString("F");
        txtTaxAmount.Text = TaxAmount.ToString("F");
        txtOrderTotal.Text = OrderTotal.ToString("F");
    }

50. Return to the College Park Auto-Parts form and click the Qty text box
51. In the Events section of the Properties, click Leave, then click the arrow of its combo box and select txtUnitPrice_Leave
52. On the College Park Auto-Parts form, click the Selected Part list view (the list view in the bottom-right section of the form)
53. In the Events section of the Properties window, double-click DoubleClick
54. 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.

Practical Learning: Using a Collection-Based Class

1. In the Solution Explorer, right-click CollegeParkAutoParts2 -> 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;
   using System.Threading.Tasks;
   
   namespace CollegeParkAutoParts2
   {
       public class AutoPart
       {
           public int PartNumber   { get; set; }
   	public int CarYear      { get; set; }
   	public string Make      { get; set; }
   	public string Model     { get; set; }
   	public string Category  { get; set; }
   	public string PartName  { get; set; }
   	public double UnitPrice { get; set; }
       }
   }

4. Display the Part Editor form and double-click an empty area of its body
5. Implement the event as follows:
   

   internal void ResetPartEditor()
   {
       cbxYears.Items.Clear();
       cbxMakes.Items.Clear();
       cbxModels.Items.Clear();
       cbxCategories.Items.Clear();
       cbxYears.Text = "";
       cbxMakes.Text = "";
       cbxModels.Text = "";
       cbxCategories.Text = "";
       txtPartName.Text = "";
       txtUnitPrice.Text = "0.00";
   
       Random rndNumber = new Random();
   
       txtPartNumber.Text = rndNumber.Next(100001, 999999).ToString();
   
       // 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 scCollegeParkAutoParts =
           new SqlConnection("Data Source=(local);" +
                             "Database=CollegeParkAutoParts1;" +
                             "Integrated Security=SSPI;"))
       {
           // Create a command that will select the parts
           SqlCommand cmdParts =
               new SqlCommand("SELECT PartNumber, " +
                              "       CarYear, " +
                              "       Make, " +
                              "       Model, " +
                              "       Category, " +
                              "       PartName, " +
                              "       UnitPrice " +
                              "FROM Inventory.AutoParts;", scCollegeParkAutoParts);
           cmdParts.CommandType = CommandType.Text;
           // Open the connection
           scCollegeParkAutoParts.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 scCollegeParkAutoParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            // Create a command that will select the parts
            SqlCommand cmdParts =
            new SqlCommand("SELECT PartNumber, " +
                           "       CarYear, " +
                           "       Make, " +
                           "       Model, " +
                           "       Category, " +
                           "       PartName, " +
                           "       UnitPrice " +
                           "FROM Inventory.AutoParts;", scCollegeParkAutoParts);
            // Open the connection
            scCollegeParkAutoParts.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 College Park Auto-Parts form and double-click anunoccupied area of its body
12. Implement the event as follows:
    

    internal 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 scCollegeParkAutoParts =
            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 PartNumber, " +
                           "       CarYear, " +
                           "       Make, " +
                           "       Model, " +
                           "       Category, " +
                           "       PartName, " +
                           "       UnitPrice " +
                           "FROM Inventory.AutoParts;", scCollegeParkAutoParts);
            // Open the connection
            scCollegeParkAutoParts.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)
    {
        ShowAutoParts();
    }	
    

13. Return to the College Park Auto-Parts 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. Return to the  College Park Auto-Parts form and click the tree view
16. In the Properties window, click the Events button and, in the Events section, double-click NodeMouseClick
17. 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 scCollegeParkAutoParts =
            new SqlConnection("Data Source=(local);" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts =
            new SqlCommand("SELECT PartNumber, " +
                           "       CarYear, " +
                           "       Make, " +
                           "       Model, " +
                           "       Category, " +
                           "       PartName, " +
                           "       UnitPrice " +
                           "FROM Inventory.AutoParts;", scCollegeParkAutoParts);
            scCollegeParkAutoParts.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)
            {
            }
        }
    }

18. Return to the College Park Auto-Parts form and double-click the Add/Select button
19. 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.",
                            "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        DataSet dsParts = new DataSet("PartsSet");
    
        using (SqlConnection scCollegeParkAutoParts =
              new SqlConnection("Data Source=(local);" +
                                "Database=CollegeParkAutoParts1;" +
                                "Integrated Security=SSPI;"))
        {
            SqlCommand cmdParts =
         new SqlCommand("SELECT * FROM Inventory.AutoParts;", scCollegeParkAutoParts);
            scCollegeParkAutoParts.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();
        }
    }

20. Return to the College Park Auto-Parts form and double-click the Save Customer Order button
21. Implement the event as follows:
    

    private void btnSave_Click(object sender, EventArgs e)
    {
        if (lvwSelectedParts.Items.Count == 0)
            return;
        else
        {
            using (SqlConnection scAltairRealtors =
                new SqlConnection("Data Source=(local);" +
                           "Database='CollegeParkAutoParts1';" +
                           "Integrated Security='SSPI';"))
            {
                SqlCommand cmdProperty =
                    new SqlCommand("INSERT INTO Transactions.CustomersOrders(ReceiptNumber, " +
                                   "PartsTotal, TaxRate, TaxAmount, OrderTotal) VALUES(" + txtSave.Text + ", " +
                                   txtPartsTotal.Text + ", " + txtTaxRate.Text + ", " +
                                   txtTaxAmount.Text + ", " + txtOrderTotal.Text + ");",
                                   scAltairRealtors);
    
                scAltairRealtors.Open();
                cmdProperty.ExecuteNonQuery();
            }
    
            // 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++)
            {
                using (SqlConnection cnnCustomerOrder =
                  new SqlConnection("Data Source=(local);" +
                                    "Database=CollegeParkAutoParts1;" +
                                    "Integrated Security=SSPI;"))
                {
                    SqlCommand cmdCustomerOrder =
                        new SqlCommand("INSERT INTO Transactions.PartsSold(ReceiptNumber, PartNumber, PartName, " +
                        "UnitPrice, Quantity, SubTotal) VALUES(" + txtSave.Text + ", N'" +
                        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 + "'); ", cnnCustomerOrder);
                    cnnCustomerOrder.Open();
    
                    cmdCustomerOrder.ExecuteNonQuery();
                }
            }
    
            MessageBox.Show("The customer order has been saved.",
                            "College Park Auto-Parts",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        CollegeParkAutoParts_Load(sender, e);
    }

22. Change the Load event as follows:

    private void SetReceiptNumber()
    {
        int iReceiptNumber = 0;
    
        using (SqlConnection scCustomersOrders =
           new SqlConnection(@"Data Source='CENTRAL\MSSQLSERVER2012';" +
                              "Database=CollegeParkAutoParts1;" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand cmdCustomersOrders =
                       new SqlCommand("SELECT ReceiptNumber " +
                             "FROM Transactions.CustomersOrders;", scCustomersOrders);
    
            scCustomersOrders.Open();
            SqlDataReader rdrCustomersOrders = cmdCustomersOrders.ExecuteReader();
    
            while (rdrCustomersOrders.Read())
                iReceiptNumber = int.Parse(rdrCustomersOrders[0].ToString());
        }
    
        txtSave.Text = (iReceiptNumber + 1).ToString();
    }
    
    private void CollegeParkAutoParts_Load(object sender, EventArgs e)
    {
        ShowAutoParts();
        SetReceiptNumber();
    
        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";
        txtOpen.Text = "";
    }

23. Return to the College Park Auto-Parts form and double-click the Open button
24. Implement the event as follows:
    

    private void btnOpen_Click(object sender, EventArgs e)
    {
        lvwSelectedParts.Items.Clear();
    
        if (string.IsNullOrEmpty(txtOpen.Text))
            return;
        else
        {
            using (SqlConnection cnnCustomerOrder =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts1;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdCustomerOrder =
                    new SqlCommand("SELECT * FROM Transactions.PartsSold " +
                                   "WHERE ReceiptNumber = '" + txtOpen.Text + "';",
                                   cnnCustomerOrder);
                SqlDataAdapter sdaCustomerOrder =
                    new SqlDataAdapter(cmdCustomerOrder);
    
                cnnCustomerOrder.Open();
    
                DataSet dsCustomerOrder = new DataSet("PartsSoldSet");
                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 Transactions.CustomersOrders " +
                                    "WHERE ReceiptNumber = " + txtOpen.Text + ";";
    
                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();
                }
            }
        }
    }

25. Return to the College Park Auto-Parts form and double-click the New Customer button
26. 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";
        txtOpen.Text = "";
    }

27. Return to the College Park Auto-Parts form and double-click the Close button
28. Implement the event as follows:
    

    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }

29. Execute the application
30. Click the New Auto Part button and use the Part Editor to create a few parts
31. Create a few customers 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

1. Open the CollegeParkAutoParts3 database from this lesson
2. Add a context menu for the Available Parts list view with the items: Select, Edit..., and Delete
    
   
3. Configure the context menu so that
   1. If the user clicks Select, the behavior would be the same as if the user had double-clicked the item
   2. If the user clicks Edit..., the Part Editor dialog box would display with the part in it. The user can then edit any part (year, make, model, category, part name, or unit price) except the part number. Then the user can save the changed part
   3. If the user clicks Delete, a message box would warn the user and ask for confirmation with Yes/No answers. If the user clicks Yes, the part would be deleted from the AutoParts table
4. Configure the application so that the user can open an order, add new parts to it, or delete parts from it, then save the order
5. Extend the application so that the store can also sell items that are, or are not, car-related, such as books, t-shirts, cleaning items, maintenance items (steering oils, brake oils, etc), license plates, etc. Every item in the store should have an item number. The user can enter that item number in the Part # text box and press Tab or Enter. The corresponding item would then be retrieved from the database and displayed on the form. If there is no item with that number, a message box should let the user know