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 libraries to get whatever behavior you want and is possible. An example library is the .NET Framework.

Practical LearningPractical Learning: Introducing the Application

  1. In your computer, create a folder named College Park Auto-Parts. If you are using Microsoft Windows, create that folder in your C:\ drive
  2. Save the following image in that folder:

    College Park Auto-Parts

  3. Start Microsoft Visual Studio
  4. In the Visual Studio 2022 dialog box, click Create a New Project
  5. In the Create a New Project wizard page, make sure either C# or All Languages is set in the first combo box.
    In the list of projects templates, click Windows Forms App
  6. Click Next
  7. Change the Project Name to CollegeParkAutoParts2
  8. Click Next
  9. In the Framework combo box, select the highest version: .NET 7.0 (Standard Term Suppot).
    Click Create

A Database for Auto-Parts

Our application is to simulate a business that sells auto-parts. Such a business uses an application, and that application needs a database. For this application, we will use a very simple database. To keep it simple, we will use a database with just one table, a table for auto parts. Since this is a Windows Forms application that stores its records in a database, we will create that database in Microsoft SQL Server. To manage records, we will use ADO.NET. This means that we have to install the Microsft.Data.SqlClient.dll assembly.

Practical LearningPractical Learning: Preparing a Database

  1. To create a new database, on the main menu of Microsoft Visual Studio, click View and click Server Explorer
  2. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
  3. In the Server Name combo box, select your server or type (local)
  4. Set the database name as CollegeParkAutoParts1
  5. Click OK
  6. In the Server Explorer, right-click the CollegeParkAutoParts1 connection and click New Query
  7. Type the following code to create a table: 
    CREATE TABLE AutoParts
(
	AutoPartsId int identity(1, 1),
	PartNumber  int           not null,
	CarYear     int           not null, 
	Make        nvarchar(50),
	Model       nvarchar(150),
	Category    nvarchar(150),
	PartName    nvarchar(255) not null,
	PartImage   nvarchar(150),
	UnitPrice   money         not null,
	CONSTRAINT PK_AutoParts PRIMARY KEY (AutoPartsId)
);
GO
  8. Right-click inside the document and click Execute
  9. Close the Query window
  10. When asked whether you want to save, click Don't Save
  11. In the Solution Explorer, right-click the name of the project and click Manage NuGet Packages...
  12. In the NuGet tab, click Browse
  13. In the combo box, type System.Data.SqlClient
  14. In the list, click System.Data.SqlClient
  15. In the right list, click Install
  16. In the Preview Changes dialog box, click OK
  17. In the License Acceptance dialog box, click I Accept

The Vehicles Makes

A vehicle uses many parts. The primary way to identify a vehicle part is through a vehicle manufacturer, also know as the make. In our application, we will use a form to assist the user in creating names for vehicles manufacturers.

Practical LearningPractical Learning: Introducing Vehicles Makes

  1. To create a dialog box, on the main menu, click Project -> Add Form (Windows Forms)...
  2. Set the name to MakeEditor and click Add
  3. Design the form as follows:

    Make Editor

    Control Text Name Other Properties
    Label Label &Make:    
    TextBox Text Box   txtMake Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel

    Form Properties

    Form Property Value
    FormBorderStyle FixedDialog
    Text Make Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False

The Vehicles Models

A vehicle makes only identifies a manufacturer. A vehicle make has many models. Our applicaton will need a form to assist the user in creating names for vehicles models.

Practical LearningPractical Learning: Introducing Vehicles Models

  1. To create a dialog box, on the main menu, click Project -> Add Form (Windows Forms)...
  2. Set the name to ModelEditor
  3. Click Add
  4. Design the form as follows:

    College Park Auto Parts: Model Editor

    Control Text Name Other Properties
    Label Label &Model:    
    TextBox Text Box   txtModel Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel

    Form Properties

    Form Property Value
    FormBorderStyle FixedDialog
    Text Model Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False

Parts or Items Categories

A piece of hardware, whether for vehicles or other machines, must fit in a category. Our application will need a form so the user can create names for categories of parts.

Practical LearningPractical Learning: Introducing Parts Categories

  1. To create a dialog box, in the Solution Explorer, right-click CollegeParkAutoParts2 -> Add -> Form (Windows Forms)...
  2. Set the name to CategoryEditor and click Add
  3. Design the form as follows:

    College Park Auto Parts: Category Editor

    Control Text Name Other Properties
    Label Label &Category:    
    TextBox Text Box   txtCategory Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel

    Form Properties

    Form Property Value
    FormBorderStyle FixedDialog
    Text Category Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False

A Class for a Collection of Store Items

Many operations of our application require that we select records. Some of the subsequent operations may be better performed using a regular collection. Such a collection may need a class so the collection can hold a list of objects. For that reason, we will need and create a class for auto-parts.

Practical LearningPractical Learning: Creating a Class

  1. In the Solution Explorer, right-click the name of the project -> Add -> New Folder
  2. For the name of the folder, type Models
  3. In the Solution Explorer, right-click Models -> Add -> Class...
  4. For the name of the class, type AutoPart
  5. Click Add
  6. Change the document as follows: 
    namespace CollegeParkAutoParts2.Models
{
    internal class AutoPart
    {
        public int     AutoPartId { get; set; }
        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 string? PartImage  { get; set; }
        public double  UnitPrice  { get; set; }
    }
}

New Store Item

The main devices of our application are auto-parts. To get information about a part, we will provide a form that a user can use to provide the pieces of information about an auto-part.

Practical LearningPractical Learning: Creating a New Auto-Part

  1. On the main menu, click Project -> Add Form (Windows Forms)...
  2. Set the Name to StoreItemNew
  3. Click Add
  4. Design the form as follows:

    College Park Auto Parts - New Store Item

    Control (Name) Text Additional Properties
    Label Label   &Part #:  
    TextBox Text Box txtPartNumber    
    Button Button btnSelectPicture &Select Picture...  
    Label Label   &Year:  
    Combo Box Combo Box cbxYears   DropDownStyle: DropDownList
    Picture Box Picture Box pbxPartImage   BorderStyle: FixedSingle
    SizeMode: AutoSize
    Label Label   &Make:  
    Combo Box Combo Box cbxMakes   DropDownStyle: DropDownList
    Button Button btnNewMake New M&ke...  
    Label Label   M&odel  
    Combo Box Combo Box cbxModels   DropDownStyle: DropDownList
    Button Button btnNewModel New Mo&del...  
    Label Label   Ca&tegory:  
    Combo Box Combo Box cbxCategories   DropDownStyle: DropDownList
    Button Button btnNewCategory New Cat&egory...  
    Label Label   Part Na&me:  
    Text Box Text Box txtPartName   ScrollBars: Vertical
    Multiline: True
    Label Label   &Unit Price:  
    Text Box Text Box txtUnitPrice   TextAlign: Right
    Label Label   ____________________  
    Button Button btnSaveAutoPart Sa&ve Auto-Part  
    Button Button Close btnClose  
    Open File Dialog Open File Dialog ofdPictureFile    
  5. Double-click an unoccupied area of the form to generate its Load event
  6. Return to the form and double-click the &Select Picture... button
  7. Return to the form and double-click the Make combo box to generate its SelectedIndexChanged event
  8. Return to the form and double-click the New M&ake... button
  9. Return to the form and double-click the New Mo&del... button
  10. Return to the form and double-click the New Cat&egory... button
  11. Return to the form and double-click the Sa&ve Auto-Part button
  12. Return to the form and double-click the Close button
  13. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;
using CollegeParkAutoParts2.Models;

namespace CollegeParkAutoParts2
{
    public partial class StoreItemNew : Form
    {
        public StoreItemNew()
        {
            InitializeComponent();
        }

        /* This function is used to reset the form.
         * It can be called when necessary. */
        private void InitializeAutoParts()
        {
            // When this function is called, we want to reset all windows controls on the form.
            cbxYears.Items.Clear();
            cbxMakes.Items.Clear();
            cbxModels.Items.Clear();
            cbxCategories.Items.Clear();
            txtPartName.Text = string.Empty;
            txtUnitPrice.Text = string.Empty;

            cbxYears.Text = "";
            cbxMakes.Text = "";
            cbxModels.Text = "";
            cbxCategories.Text = "";
            txtPartName.Text = "";
            txtUnitPrice.Text = "0.00";

            /* Show the years in the Years combo box.
             * We will consider the cars made in the last 20 years. */
            for (int year = DateTime.Today.Year + 1; year >= DateTime.Today.Year - 20; year--)
                cbxYears.Items.Add(year);

            /* Normally, we will specify to the user what number to put in the part number.
             * As an option, we will provide a default random number for the auto-part.*/
            Random rndNumber = new();

            txtPartNumber.Text = rndNumber.Next(100000, 999999).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=CollegeParkAutoParts2;" +
                                  "Integrated Security=SSPI;"))
            {
                /* Although the main table of our database contains many parts, when the 
                 * New Store Item form opens, we will populate the Makes combo box with 
                 * all the names of vehicles manufacturers that currently exist in the 
                 * database. We will also populate the Categories combo box with the 
                 * Categories that the user might have created (assuming the user had 
                 * already created a few records). 
                 * As a result, for this operation, from the AutoParts table, we will 
                 * need just the Make, the Model, and the Category fields. */
                SqlCommand cmdParts =
                    new SqlCommand("SELECT Make, " +
                                   "       Model, " +
                                   "       Category " +
                                   "FROM   AutoParts;", scCollegeParkAutoParts);
                cmdParts.CommandType = CommandType.Text;

                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)
                {
                    StoreItem = new();
                    StoreItem.PartNumber = 0;
                    StoreItem.CarYear = 1000;
                    StoreItem.Make = row[0].ToString();
                    StoreItem.Model = row[1].ToString();
                    StoreItem.Category = row[2].ToString();
                    StoreItem.PartName = string.Empty;
                    StoreItem.PartImage = string.Empty;
                    StoreItem.UnitPrice = 0.00;
                    // 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);
            }

            lblPictureFile.Text = "Generic.png";
            Width               = pbxPartImage.Right + 40;
            Height              = pbxPartImage.Bottom + 75;
            pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");
        }

        private void StoreItemNew_Load(object sender, EventArgs e)
        {
            InitializeAutoParts();
        }

        private void btnSelectPicture_Click(object sender, EventArgs e)
        {
            FileInfo fiFilePath;

            if (ofdPictureFile.ShowDialog() == DialogResult.OK)
            {
                fiFilePath         = new(ofdPictureFile.FileName);
                pbxPartImage.Image = Image.FromFile(ofdPictureFile.FileName);
            }
            else
            {
                fiFilePath         = new(@"E:\College Park Auto-Parts\Generic.png");
                pbxPartImage.Image = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");
            }

            lblPictureFile.Text    = fiFilePath.Name;
            Width                  = pbxPartImage.Right + 40;
            Height                 = pbxPartImage.Bottom + 75;
        }

        private void cbxMakes_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbxModels.Items.Clear();
            DataSet dsParts = new("PartsSet");

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection("Data Source=(local);" +
                                  "Database=CollegeParkAutoParts2;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdParts = new SqlCommand("SELECT CarYear, " +
                                                     "       Make, " +
                                                     "       Model " +
                                                     "FROM   AutoParts;", scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);

                sdaParts.Fill(dsParts);

                AutoPart? StoreItem = null;
                List<AutoPart> lstAutoParts = new();

                foreach (DataRow row in dsParts.Tables[0].Rows)
                {
                    StoreItem = new AutoPart();
                    StoreItem.PartNumber = 0;
                    StoreItem.CarYear = int.Parse(row[0].ToString()!);
                    StoreItem.Make = row[1].ToString();
                    StoreItem.Model = row[2].ToString();
                    StoreItem.Category = string.Empty;
                    StoreItem.PartName = string.Empty;
                    StoreItem.PartImage = string.Empty;
                    StoreItem.UnitPrice = 0;
                    lstAutoParts.Add(StoreItem);
                }

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

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

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

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

        private void btnSaveAutoPart_Click(object sender, EventArgs e)
        {
            double unitPrice = 0.00;

            if(string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show("You must enter the part number of the store item.",
                                "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if(string.IsNullOrEmpty(txtPartName.Text) )
            {
                MessageBox.Show("You must enter the name of the store item.",
                                "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if(string.IsNullOrEmpty(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=CollegeParkAutoParts2;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdAutoPart =
                    new SqlCommand("INSERT INTO AutoParts(" +
                                   "PartNumber, CarYear, Make, Model, Category, " +
                                   "PartName, PartImage, UnitPrice) VALUES(" + txtPartNumber.Text + ", " +
                                   cbxYears.Text + ", N'" + cbxMakes.Text + "', N'" +
                                   cbxModels.Text + "', N'" + cbxCategories.Text + "', N'" +
                                   txtPartName.Text + "', N'" + lblPictureFile.Text + "', " + unitPrice + ");",
                                   cnnNewPart);

                cnnNewPart.Open();
                cmdAutoPart.ExecuteNonQuery();

            InitializeAutoParts();

                MessageBox.Show("The new part has been added.", "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

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

Editing/Updating an Auto-Part

A database requires maintenance, which consists of locating records, then editing or updaing them. We will use a form to assist the user in taking care of that.

Practical LearningPractical Learning: Editing/Updating a Record

  1. To create a form, on the main menu of Microsoft Visual Studio, click Project and click Add Form (Windows Forms...)
  2. Change the file Name to StoreItemEditor
  3. Click Add
  4. Resize the form to have the same size as the New Store Item form
  5. Display the New Store Item form
  6. Copy everything on it (you can press Ctrl + A and then press Ctrl + C)
  7. Display the Store Item Editor form
  8. Paste everything (you can press Ctrl + V)
  9. Change the design of the form as follows:

    Add New Item

    Control (Name) Text
    Button Button btnFind &Find
    Text Box Text Box txtMake  
    Text Box Text Box txtModel  
    Text Box Label txtCategory  
    Button Button btnUpdateAutoPart Up&date Auto-Part
  10. Double-click an unoccupied area of the form to generate its Load event
  11. Return to the form and double-click the Find button
  12. Return to the form and double-click the &Select Picture... button
  13. Return to the form and double-click the Up&date Auto-Part button
  14. Return to the form and double-click the Close button
  15. Change the document as follows: 
    using CollegeParkAutoParts21.Models;
using System.Data.SqlClient;
using System.Data;

namespace CollegeParkAutoParts21
{
    public partial class StoreItemEditor : Form
    {
        public StoreItemEditor()
        {
            InitializeComponent();
        }

        private void InitializeAutoPart()
        {
            txtMake.Text       = string.Empty;
            txtModel.Text      = string.Empty;
            txtCategory.Text   = string.Empty;
            txtPartName.Text   = string.Empty;
            txtUnitPrice.Text  = string.Empty;
            txtPartNumber.Text = string.Empty;

            for (int year = DateTime.Today.Year + 1; year >= DateTime.Today.Year - 20; year--)
                cbxYears.Items.Add(year);

            lblPictureFile.Text = "Generic.png";
            pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");

            Width  = pbxPartImage.Right  + 40;
            Height = pbxPartImage.Bottom + 75;
        }

        private void StoreItemEditor_Load(object sender, EventArgs e)
        {
            InitializeAutoPart();
        }

        private void btnFind_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show("You must enter a (valid) number for an auto-part.",
                                "College Park Auto-Parts", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            bool foundAutoPart = false;

            DataSet dsParts = new("AutoPartsSet");

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection("Data Source=(local);" +
                                  "Database=CollegeParkAutoParts2;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdParts = new SqlCommand("SELECT PartNumber, " +
                                                     "       CarYear,    " +
                                                     "       Make,       " +
                                                     "       Model,      " +
                                                     "       Category,   " +
                                                     "       PartName,   " +
                                                     "       PartImage,  " +
                                                     "       UnitPrice   " +
                                                     "FROM   AutoParts;", scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);

                sdaParts.Fill(dsParts);

                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    if(drPart[0].ToString()!.Equals(txtPartNumber.Text))
                    {
                        foundAutoPart = true;
                        
                        cbxYears.Text       = drPart[1].ToString();
                        txtMake.Text        = drPart[2].ToString();
                        txtModel.Text       = drPart[3].ToString();
                        txtCategory.Text    = drPart[4].ToString();
                        txtPartName.Text    = drPart[5].ToString();
                        lblPictureFile.Text = drPart[6].ToString();
                        pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\" + drPart[6].ToString());
                        txtUnitPrice.Text   = double.Parse(drPart[7].ToString()!).ToString("F");
                        
                        Width               = pbxPartImage.Right  + 40;
                        Height              = pbxPartImage.Bottom + 75;
                    }
                }
            }

            if (foundAutoPart == false)
            {
                MessageBox.Show("There is no auto-part with that number in our records.",
                                "College Park Auto-Parts", MessageBoxButtons.OK, MessageBoxIcon.Information);

                lblPictureFile.Text = @"E:\College Park Auto-Parts\Generic.png";
                pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");
            }

            Width  = pbxPartImage.Right  + 40;
            Height = pbxPartImage.Bottom + 75;
        }

        private void btnSelectPicture_Click(object sender, EventArgs e)
        {
            FileInfo fiFilePath;

            if (ofdPictureFile.ShowDialog() == DialogResult.OK)
            {
                fiFilePath         = new(ofdPictureFile.FileName);
                pbxPartImage.Image = Image.FromFile(ofdPictureFile.FileName);
            }
            else
            {
                fiFilePath         = new(@"E:\College Park Auto-Parts\Generic.png");
                pbxPartImage.Image = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");
            }

            lblPictureFile.Text = fiFilePath.Name;
            Width               = pbxPartImage.Right  + 40;
            Height              = pbxPartImage.Bottom + 75;
        }

        private void btnUpdateAutoPart_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show("You must enter the part number of the store item.",
                                "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            using (SqlConnection cnnNewPart =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts2;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdAutoPart =
                    new SqlCommand("UPDATE AutoParts SET CarYear   =   " + cbxYears.Text        + "  WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET Make      = N'" + txtMake.Text         + "' WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET Model     = N'" + txtModel.Text        + "' WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET Category  = N'" + txtCategory.Text     + "' WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET PartName  = N'" + txtPartName.Text     + "' WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET PartImage = N'" + lblPictureFile.Text  + "' WHERE PartNumber = " + txtPartNumber.Text + ";" +
                                   "UPDATE AutoParts SET UnitPrice =   " + txtUnitPrice.Text    + "  WHERE PartNumber = " + txtPartNumber.Text + ";",
                                   cnnNewPart);

                cnnNewPart.Open();
                cmdAutoPart.ExecuteNonQuery();
                
                InitializeAutoPart();

                MessageBox.Show("The information about the auto part has been updated in the database.",
                                "College Park Auto-Parts", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

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

Deleting Records

A valuable operation of data management is to delete uncessairy records. We will use a form to assist the user with this type of operation.

Practical LearningPractical Learning: Removing Records

  1. To create a new form, on the main menu of Microsoft Visual Studio, click Project -> Add Form (Windows Forms...)
  2. Type the file Name as StoreItemDelete
  3. Click Add
  4. Resize the form to have the same size as the Store Item Editor form
  5. Display the Store Item Editor form and copy everything on it (you can press Ctrl + A and then press Ctrl + C)
  6. Display the Store Item Delete form and paste everything in it (you can press Ctrl + V
  7. Chang the design of the form as follows:

    College Park Auto-Parts - Store Item Delete

    Control (Name) Text Additional Properties
    Text Box Text Box txtYear   TextAlign: Right
    Button Button btnDeleteAutoPart Delete Auto-Part  
  8. Double-click an unoccupied area of the form
  9. Return to the form and double-click the Find button
  10. Return to the form and double-click the &Delete Auto-Part button
  11. Return to the form and double-click the Close button
  12. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;

namespace CollegeParkAutoParts2
{
    public partial class StoreItemDelete : Form
    {
        public StoreItemDelete()
        {
            InitializeComponent();
        }

        private void InitializeAutoPart()
        {
            txtYear.Text        = string.Empty;
            txtMake.Text        = string.Empty;
            txtModel.Text       = string.Empty;
            txtCategory.Text    = string.Empty;
            txtPartName.Text    = string.Empty;
            txtUnitPrice.Text   = string.Empty;
            txtPartNumber.Text  = string.Empty;

            lblPictureFile.Text = "Generic.png";
            pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");

            Width               = pbxPartImage.Right  + 40;
            Height              = pbxPartImage.Bottom + 75;
        }

        private void StoreItemDelete_Load(object sender, EventArgs e)
        {
            InitializeAutoPart();
        }

        private void btnFind_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show("You must enter a (valid) number for an auto-part.",
                                "College Park Auto-Parts", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            bool foundAutoPart = false;
            DataSet dsParts    = new("AutoPartsSet");

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection("Data Source=(local);" +
                                  "Database=CollegeParkAutoParts2;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdParts = new SqlCommand("SELECT PartNumber, " +
                                                     "       CarYear,    " +
                                                     "       Make,       " +
                                                     "       Model,      " +
                                                     "       Category,   " +
                                                     "       PartName,   " +
                                                     "       PartImage,  " +
                                                     "       UnitPrice   " +
                                                     "FROM   AutoParts;", scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);

                sdaParts.Fill(dsParts);

                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    if (drPart[0].ToString()!.Equals(txtPartNumber.Text))
                    {
                        foundAutoPart = true;

                        txtYear.Text        = drPart[1].ToString();
                        txtMake.Text        = drPart[2].ToString();
                        txtModel.Text       = drPart[3].ToString();
                        txtCategory.Text    = drPart[4].ToString();
                        txtPartName.Text    = drPart[5].ToString();
                        lblPictureFile.Text = drPart[6].ToString();
                        pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\" + drPart[6].ToString());
                        txtUnitPrice.Text   = double.Parse(drPart[7].ToString()!).ToString("F");

                        Width               = pbxPartImage.Right  + 40;
                        Height              = pbxPartImage.Bottom + 75;
                    }
                }
            }

            if (foundAutoPart == false)
            {
                MessageBox.Show("There is no auto-part with that number in our records.",
                                "College Park Auto-Parts", MessageBoxButtons.OK, MessageBoxIcon.Information);

                lblPictureFile.Text = @"E:\College Park Auto-Parts\Generic.png";
                pbxPartImage.Image  = Image.FromFile(@"E:\College Park Auto-Parts\Generic.png");
            }

            Width = pbxPartImage.Right + 40;
            Height = pbxPartImage.Bottom + 75;
        }

        private void btnDeleteAutoPart_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show("You must enter the part number of the store item.",
                                "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            using (SqlConnection cnnNewPart =
                   new SqlConnection("Data Source=(local);" +
                                     "Database=CollegeParkAutoParts2;" +
                                     "Integrated Security=SSPI;"))
            {
                SqlCommand cmdAutoPart =
                    new SqlCommand("DELETE FROM AutoParts " +
                                   "WHERE PartNumber = " + txtPartNumber.Text + ";",
                                   cnnNewPart);

                cnnNewPart.Open();
                cmdAutoPart.ExecuteNonQuery();

                InitializeAutoPart();

                MessageBox.Show("The part has been deleted from the database.", "College Park Auto-Parts",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

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

A Central Form

A typical application uses a central object from which people can access the objects. For that object, we will the first form that was created in the project.

Practical LearningPractical Learning: Finalizing the Application

  1. In the Solution Explorer, right-click Form1.cs and click Rename
  2. Type CollegeParkAutoParts (to have CollegeParkAutoParts.cs) and press Enter three times to accept the name and display the form
  3. From the Components section of the Toolbox, click ImageList and click the form
  4. In the Properties window, click (Name) and type AutoPartsImages
  5. Click the ellipsis button of the Images field
  6. In the Image Collection Editor, click Add
  7. Locate the folder that contains the resources for these lessons and display it in the Look In combo box
  8. Select the following icons: Sign1, Sign2, Cliper1, Cliper2, Rulers1, Rulers2, Graph1, Graph2, Tool1, and Tool2
  9. Click Open

    Image Collection Editor

  10. Click OK
  11. Click OK
  12. Design the form as follows:

    College Park Auto Parts - Form Design

    Control (Name) Text Other Properties
    Label Label   College Park Auto-Parts Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Panel Panel     BackColor: Black
    Height: 2
    GroupBox Group Box   Part Identification  
    TreeView Tree View tvwAutoParts   ImageList: AutoPartsImages
    GroupBox Group Box   Available Parts  
    ListView List View lvwAutoParts   FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colAvailablePartNumber Part #    
    colAvailablePartName Part Name/Description   300
    colAvailableUnitPrice Unit Price Right 80
    GroupBox Group Box   Customer Order - Selected Parts  
    Label Label   Part #  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    TextBox Text Box txtPartNumber    
    TextBox Text Box txtPartName    
    TextBox Text Box txtUnitPrice   TextAlign: Right
    TextBox Text Box txtQuantity   TextAlign: Right
    TextBox Text Box txtSubTotal   TextAlign: Right
    Button Button btnAdd Add/Select  
    ListView List View lvwSelectedParts   FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colSelectedPartNumber Part #   45
    colSelectedPartName Part Name/Description   274
    colSelectedUnitPrice Unit Price Right 58
    colSelectedQuantity Qty Right 28
    colSelectedSubTotal Sub-Total Right 58
    GroupBox Group Box Order Summary    
    Button Button btnNewAutoPart New Au&to Part...  
    Button Button btnUpdateAutoPart Update Auto Part...  
    Label Label   Selected Parts Total:  
    TextBox Text Box txtSelectedPartsTotal 0.00 TextAlign: Right
    Label Label Tax Rate:    
    TextBox Text Box txtTaxRate 7.75 TextAlign: Right
    Label Label   %  
    Label Label   Tax Amount:  
    TextBox Text Box txtTaxAmount   TextAlign: Right
    Label Label   Order Total:  
    TextBox Text Box txtOrderTotal 0.00 TextAlign: Right
    Button Button btnDeleteAutoPart Delete Auto-Part ...  
    Button Button btnClose Close  
  13. Double-click an unoccupied area of the form to generate its load event
  14. Return to the form and, in the Properties window, click the Events button Events
  15. On the form, click the Part Identification tree view
  16. In the Events section of the Properties window, double-click NodeMouseClick
  17. Return to the form and double-click the New Au&to Part button
  18. On the form, click the Available Parts list view to select it and, in the Events section Properties window, double-click ItemSelectionChanged
  19. Return to the form and make sure the Available Parts list view is still selected.
    In the Events section of the Properties, double-click DoubleClick
  20. Return to the form and click the Part # text box to select it
  21. In the Events section of the Properties window, double-click Leave
  22. Return to the form and click the Unit Price text box to select it
  23. In the Events section of the Properties window, double-click Leave
  24. Return to the form and click the Qty text box to select it
  25. In the Events section of the Properties window, click the arrow of the Leave combo box and select txtUnitPrice_Leave
  26. Return to the form and double-click the Add/Select
  27. Return to the form and click the Selected Parts list view to select it
  28. In the Events section of the Properties window, double-click DoubleClick
  29. Return to the form and double-click the Update Auto-Part button
  30. Return to the form and double-click the Delete Auto-Part button
  31. Return to the form and double-click the Close button
  32. Change the document as follows: 
    using System.Data;
using System.Data.SqlClient;
using CollegeParkAutoParts2.Models;

namespace CollegeParkAutoParts2
{
    public partial class CollegeParkAutoParts : Form
    {
        public CollegeParkAutoParts()
        {
            InitializeComponent();
        }

        // This function is used to reset the form
        void InitializeAutoParts()
        {
            // When the form must be reset, removes all nodes from the tree view
            tvwAutoParts.Nodes.Clear();
            // Create the root node of the tree view
            TreeNode nodRoot = tvwAutoParts.Nodes.Add(&quot;College Park Auto-Parts&quot;,
                                                      &quot;College Park Auto-Parts&quot;, 0, 1);
            /* Add the cars years to the tree view.
             * Our application will deal only with the cars in the last 21 years. */
            for (int years = DateTime.Today.Year + 1; years >= DateTime.Today.Year - 20; years--)
                nodRoot.Nodes.Add(years.ToString(), years.ToString(), 2, 3);

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

            // Create an empty data set
            DataSet dsParts = new DataSet(&quot;PartsSet&quot;);

            // Create a connection to the database
            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection(&quot;Data Source=(local);&quot; +
                                  &quot;Database=CollegeParkAutoParts2;&quot; +
                                  &quot;Integrated Security=SSPI;&quot;))
            {
                // Use a command to specify what action we want to take
                SqlCommand cmdParts =
                new SqlCommand(&quot;SELECT CarYear, &quot; +
                               &quot;       Make, &quot; +
                               &quot;       Model, &quot; +
                               &quot;       Category &quot; +
                               &quot;FROM AutoParts;&quot;, scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new(cmdParts);

                sdaParts.Fill(dsParts);

                AutoPart? StoreItem = null;
                List<AutoPart> lstAutoParts = new();

                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    StoreItem = new AutoPart();

                    StoreItem.PartNumber = 0;
                    StoreItem.CarYear    = int.Parse(drPart[0].ToString()!);
                    StoreItem.Make       =           drPart[1].ToString();
                    StoreItem.Model      =           drPart[2].ToString();
                    StoreItem.Category   =           drPart[3].ToString();
                    StoreItem.PartName   = string.Empty;
                    StoreItem.UnitPrice  = 0;
                    lstAutoParts.Add(StoreItem);
                }

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

                        foreach (AutoPart part in lstAutoParts)
                        {
                            if ((nodYear.Text == part.CarYear.ToString()) &amp;&amp;
                                (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)
                        {
                            var lstCategories = new List<string>();

                            foreach (AutoPart part in lstAutoParts)
                            {
                                if ((nodYear.Text == part.CarYear.ToString()) &amp;&amp;
                                    (nodMake.Text == part.Make) &amp;&amp;
                                    (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);
                        }
                    }
                }
            }

            lvwSelectedParts.Items.Clear();
            lvwAvailableParts.Items.Clear();
            txtPartName.Text           = string.Empty;
            txtQuantity.Text           = string.Empty;
            txtSubTotal.Text           = string.Empty;
            txtUnitPrice.Text          = string.Empty;
            txtTaxAmount.Text          = string.Empty;
            txtOrderTotal.Text         = string.Empty;
            txtPartNumber.Text         = string.Empty;
            txtSelectedPartsTotal.Text = string.Empty;
            pbxPartImage.Image         = Image.FromFile(@&quot;E:\College Park Auto-Parts\Generic.png&quot;);
        }
        
        private void CollegeParkAutoParts_Load(object sender, EventArgs e)
        {
            InitializeAutoParts();
        }

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

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

            DataSet dsParts = new DataSet(&quot;PartsSet&quot;);

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection(&quot;Data Source=(local);&quot; +
                                  &quot;Database=CollegeParkAutoParts2;&quot; +
                                  &quot;Integrated Security=SSPI;&quot;))
            {
                SqlCommand cmdParts =
                    new SqlCommand(&quot;SELECT PartNumber, &quot; +
                                   &quot;       CarYear,    &quot; +
                                   &quot;       Make,       &quot; +
                                   &quot;       Model,      &quot; +
                                   &quot;       Category,   &quot; +
                                   &quot;       PartName,   &quot; +
                                   &quot;       PartImage,  &quot; +
                                   &quot;       UnitPrice   &quot; +
                                   &quot;FROM   AutoParts;  &quot;, scCollegeParkAutoParts);
                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);
                sdaParts.Fill(dsParts);

                AutoPart? storeItem = null;
                List<AutoPart> lstAutoParts = new List<AutoPart>();

                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    storeItem            = new AutoPart();
                    storeItem.PartNumber =    int.Parse(drPart[0].ToString()!);
                    storeItem.CarYear    =    int.Parse(drPart[1].ToString()!);
                    storeItem.Make       =              drPart[2].ToString();
                    storeItem.Model      =              drPart[3].ToString();
                    storeItem.Category   =              drPart[4].ToString();
                    storeItem.PartName   =              drPart[5].ToString();
                    storeItem.PartImage  =              drPart[6].ToString();
                    storeItem.UnitPrice  = double.Parse(drPart[7].ToString()!);
                    lstAutoParts.Add(storeItem);
                }

                try
                {
                    foreach (AutoPart part in lstAutoParts)
                    {
                        if ((part.Category           == nodClicked.Text) &amp;&amp;
                            (part.Model              == nodClicked.Parent.Text) &amp;&amp;
                            (part.Make               == nodClicked.Parent.Parent.Text) &amp;&amp;
                            (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(&quot;F&quot;));
                            lvwAvailableParts.Items.Add(lviAutoPart);
                        }
                    }
                }
                catch (NullReferenceException)
                {
                }
            }
        }

        private void lvwAvailableParts_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
        {
            bool pictureFound = false;
            DataSet dsParts = new(&quot;PartsSet&quot;);

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection(&quot;Data Source=(local);&quot; +
                                  &quot;Database=CollegeParkAutoParts2;&quot; +
                                  &quot;Integrated Security=SSPI;&quot;))
            {
                SqlCommand cmdParts =
                    new SqlCommand(&quot;SELECT PartNumber, &quot; +
                                   &quot;       PartImage &quot; +
                                   &quot;FROM   AutoParts;&quot;, scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);

                sdaParts.Fill(dsParts);

                AutoPart? storeItem = null;
                List<AutoPart> lstAutoParts = new();

                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    storeItem = new AutoPart();
                    storeItem.PartNumber = int.Parse(drPart[0].ToString()!);
                    storeItem.CarYear    = 0;
                    storeItem.Make       = string.Empty;
                    storeItem.Model      = string.Empty;
                    storeItem.Category   = string.Empty;
                    storeItem.PartName   = string.Empty;
                    storeItem.PartImage  = drPart[1].ToString();
                    storeItem.UnitPrice  = 0.00;
                    lstAutoParts.Add(storeItem);
                }

                foreach (AutoPart part in lstAutoParts)
                {
                    if (part.PartNumber == long.Parse(e.Item!.SubItems[0].Text))
                    {
                        pictureFound       = true;
                        pbxPartImage.Image = Image.FromFile(@&quot;E:\College Park Auto-Parts\&quot; + part.PartImage!);
                        break;
                    }
                }
            }

            if (pictureFound == false)
            {
                pbxPartImage.Image = Image.FromFile(@&quot;E:\College Park Auto-Parts\Generic.png&quot;);
            }

            Width  = pbxPartImage.Right  + 40;
            Height = pbxPartImage.Bottom + 75;
        }

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

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

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

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

            txtQuantity.Focus();
        }

        private void txtUnitPrice_Leave(object sender, EventArgs e)
        {
            double subTotal;
            double unitPrice = 0D;
            double quantity  = 0.00d;

            try
            {
                unitPrice = double.Parse(txtUnitPrice.Text);
            }
            catch (FormatException)
            {
                MessageBox.Show(&quot;Invalid Unit Price!&quot;,
                                &quot;College Park Auto-Parts&quot;,
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            try
            {
                quantity = int.Parse(txtQuantity.Text);
            }
            catch (FormatException)
            {
                MessageBox.Show(&quot;Invalid Quandtity!&quot;,
                                &quot;College Park Auto-Parts&quot;,
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            subTotal = unitPrice * quantity;
            txtSubTotal.Text = subTotal.ToString(&quot;F&quot;);
        }

        internal void CalculateOrder()
        {
            // Calculate the current total order and update the order
            double partsTotal = 0.00D;
            double taxRate    = 0.00D;
            double taxAmount, orderTotal;

            if (string.IsNullOrEmpty(txtTaxRate.Text))
                txtTaxRate.Text = &quot;7.25&quot;;

            foreach (ListViewItem lviPart in lvwSelectedParts.Items)
            {
                ListViewItem.ListViewSubItem SubItem = lviPart.SubItems[4];
                partsTotal += double.Parse(SubItem.Text);
            }

            try
            {
                taxRate = double.Parse(txtTaxRate.Text) / 100;
            }
            catch (FormatException)
            {
                MessageBox.Show(&quot;Invalid Tax Rate&quot;,
                                &quot;College Park Auto-Parts&quot;,
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            taxAmount  = partsTotal * taxRate;
            orderTotal = partsTotal + taxAmount;

            txtSelectedPartsTotal.Text = partsTotal.ToString(&quot;F&quot;);
            txtTaxAmount.Text          = taxAmount.ToString(&quot;F&quot;);
            txtOrderTotal.Text         = orderTotal.ToString(&quot;F&quot;);
        }

        private void txtPartNumber_Leave(object sender, EventArgs e)
        {
            bool    found   = false;
            DataSet dsParts = new(&quot;PartsSet&quot;);

            using (SqlConnection scCollegeParkAutoParts =
                new SqlConnection(&quot;Data Source=(local);&quot; +
                                  &quot;Database=CollegeParkAutoParts2;&quot; +
                                  &quot;Integrated Security=SSPI;&quot;))
            {
                SqlCommand cmdParts = new SqlCommand(&quot;SELECT PartNumber, &quot; +
                                                     &quot;       PartName, &quot; +
                                                     &quot;       UnitPrice &quot; +
                                                     &quot;FROM   AutoParts;&quot;, scCollegeParkAutoParts);

                scCollegeParkAutoParts.Open();

                SqlDataAdapter sdaParts = new SqlDataAdapter(cmdParts);

                sdaParts.Fill(dsParts);

                /* After the user had entered a part number,
                 * check the whole list of parts */
                foreach (DataRow drPart in dsParts.Tables[0].Rows)
                {
                    // If you find a part that holds the number the user had entered
                    if (drPart[0].ToString()!.Equals(txtPartNumber.Text))
                    {
                        // Show the corresponding part name and unit price
                        txtPartName.Text = drPart[1].ToString()!;
                        txtUnitPrice.Text = double.Parse(drPart[2].ToString()!).ToString(&quot;F&quot;);
                        
                        if (string.IsNullOrEmpty(txtQuantity.Text))
                            txtQuantity.Text = &quot;1&quot;;
                        
                        txtSubTotal.Text = double.Parse(drPart[2].ToString()!).ToString(&quot;F&quot;);
                        // Give focus to the quantity in case the user was to increase it
                        txtQuantity.Focus();
                        
                        // And update the flag that specifies that the part has been found
                        found = true;
                        break;
                    }
                    // If the part number was not found, check the next
                } // If no part has that number, the found flag is marked as false
            }

            // If no part with that number was found...
            if (found == false)
            {
                // Since no part with that number was found,
                // reset the text boxes
                txtPartName.Text  = &quot;&quot;;
                txtUnitPrice.Text = &quot;0.00&quot;;
                txtQuantity.Text  = &quot;0&quot;;
                txtSubTotal.Text  = &quot;0.00&quot;;

                // Let the user know that the part number that 
                // was entered is not in the list
                MessageBox.Show(&quot;There is no part with that number.&quot;,
                            &quot;College Park Auto-Parts&quot;,
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPartNumber.Text))
            {
                MessageBox.Show(&quot;There is no part to be added to the order.&quot;,
                                &quot;College Park Auto-Parts&quot;,
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            ListViewItem lviSelectedPart = new ListViewItem(txtPartNumber.Text);
            
            lviSelectedPart.SubItems.Add(txtPartName.Text);
            lviSelectedPart.SubItems.Add(txtUnitPrice.Text);
            lviSelectedPart.SubItems.Add(txtQuantity.Text);
            lviSelectedPart.SubItems.Add(txtSubTotal.Text);
            
            lvwSelectedParts.Items.Add(lviSelectedPart);

            txtPartNumber.Text = txtPartName.Text = txtUnitPrice.Text = txtQuantity.Text = txtSubTotal.Text = string.Empty;
            CalculateOrder();
        }

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

        private void btnNewAutoPart_Click(object sender, EventArgs e)
        {
            StoreItemNew sin = new StoreItemNew();

            sin.ShowDialog();

            InitializeAutoParts();
        }

        private void btnUpdateAutoPart_Click(object sender, EventArgs e)
        {
            StoreItemEditor sie = new StoreItemEditor();

            sie.ShowDialog();

            InitializeAutoParts();
        }

        private void btnDeleteAutoPart_Click(object sender, EventArgs e)
        {
            StoreItemDelete sid = new StoreItemDelete();

            sid.ShowDialog();

            InitializeAutoParts();
        }

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

Executing and Testing the Application

After creating and application, you can execute it. You can then test it with sample values.

Practical LearningPractical Learning: Executing and Testing the Application

  1. To execute the application, on the main menu, click Debug -> Start Without Debugging:

  2. Click the New Auto Part button

  3. Click the New Make button
  4. Click Cancel
  5. Create some auto parts as follows:
    Part # Year Make Model Category Part Name Unit Price Part Image
    393795 2015 Buick Regal Alternators & Generators DB Electrical Alternator 218.74 928037
    928374 2018 Chevrolet Express 3500 Shocks, Struts & Suspension Suspension Kit (Front; with 3 Groove Pitman Arm) 142.44 304031
    730283 2020 Jeep Wrangler Unlimited Sahara Oil Filters Hydraulic Cylinder Timing Belt Tensioner 14.15 730283
    290741 2015 Ford F-150 XL 3.5L V6 Flex Regular Cab 2 Full-Size Doors Shocks, Struts & SuspensionFront Strut and Coil Spring Assembly - Set of 2 245.68 290741
    740248 2013 Chevrolet EquinoxBearings & Seals Wheel hub bearing Assembly 99.95 740248
    283759 2012 Dodge Charger 3.6L Starters DB Electrical SND0787 Starter 212.58 283759
    799428 2012 Cadillac XTS Bearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    648203 2018 Honda CRV Alternators Alternator 202.47 593804
    502853 2014 GMC TerrainBearings & SealsWheel Hub Bearing Assembly48.85 927944
    520384 2020 Jeep Wrangler Unlimited SaharaDrum BrakeRear Dynamic Friction Company True-Arc Brake Shoes 42.22 520384
    727394 2018 Toyota Corolla SE 1.8L L4 Gas Alternators DB Electrical 400-40169 Alternator Compatible With/Replacement For 125 Internal Fan Type Decoupler Pulley Type Internal Regulator CW Rotation 215.84 727394
    927944 2017 Chevrolet Equinox Bearings & Seals Wheel Hub Bearing Assembly 48.85 927944
    749471 2019 Toyota Prius Shocks, Struts & Suspension 2-Piece Suspension Strut and Coil Spring Kit 299.97 593024
    927307 2014 Buick Regal Alternators & Generators DB Electrical Alternator 218.74 928037
    304031 2017 Chevrolet Express 2500 Shocks, Struts & Suspension Suspension Kit (Front; with 3 Groove Pitman Arm) 142.44 304031
    497249 2013 GMC Sierra 1500 Drum BrakeACDelco Gold 17960BF1 Bonded Rear Drum Brake Shoe Set 58.92 497249
    973947 2012 Honda Accord Brake Kits R1 Concepts Front Rear Brakes and Rotors Kit |Front Rear Brake Pads| Brake Rotors and Pads| Ceramic Brake Pads and Rotors 292.84 973947
    182694 2016 Chevrolet Impala Bearings & SealsWheel Hub Bearing Assembly 48.85 927944
    497249 2013 Chevrolet Silverado 1500 Drum Brake ACDelco Gold 17960BF1 Bonded Rear Drum Brake Shoe Set 58.92 497249
    297149 2020 Jeep Wrangler Air Filters ACDelco Gold A3408C Air Filter 22.83 297149
    927397 2016 Chevrolet ImpalaBearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    392972 2020 ToyotaPrius AWD-e Shocks, Struts & Suspension 2-Piece Suspension Strut and Coil Spring Kit (593024) 299.97 593024
    928037 2017 Buick RegalAlternators & Generators DB Electrical Alternator 218.74 928037
    502481 2016 Chevrolet EquinoxBearings & Seals Wheel hub bearing Assembly 99.95 740248
    593804 2019 HondaAccord LX 1.5L L4 Gas Alternator Alternator 202.47 593804
    293748 2014 Toyota Corolla SE 1.8L L4 Gas Alternators DB Electrical 400-40169 Alternator Compatible With/Replacement For 125 Internal Fan Type Decoupler Pulley Type Internal Regulator CW Rotation 215.84 727394
    639704 2021 Kia Sorento Brake Kits Rear Brakes and Rotors Kit |Rear Brake Pads| Brake Rotors and Pads| Optimum OEp Brake Pads and Rotors125.15 639704
    829385 2020 JeepWrangler Unlimited Sahara Drum Brake Centric Brake Shoe 22.05 829385
    484695 2014 GMC Terrain Bearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    807204 2016 Chevrolet Camaro Alternators & Generators DB Electrical Alternator 218.74 928037
    939283 2015 Chevrolet Equinox Bearings & Seals Wheel Hub Bearing Assembly 48.85 927944
    738628 2021 Toyota Prius AWD-e Shocks, Struts & Suspension 2-Piece Suspension Strut and Coil Spring Kit 299.97 593024
    186950 2017 Honda CRV Alternator Alternator 202.47 593804
    329573 2012 Chevrolet Equinox Bearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    594085 2015 Buick Regal Bearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    928405 2018 Chevrolet Camaro Alternators & Generators DB Electrical Alternator 218.74 928037
    927937 2012 Ford Focus SE StartersDuralast Starter 19481 188.88 927937
    283948 2018 GMC Savana 3500 Shocks, Struts & Suspension Suspension Kit (Front; with 3 Groove Pitman Arm) 142.44 304031
    495116 2020 Chrysler Voyager Brake Kits Power Stop K7845 Rear Z23 Carbon Fiber Brake Pads with Drilled & Slotted Brake Rotors Kit 269.75 293748
    180400 2012 Cadillac CTS FWDBearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    593024 2021 Toyota Prius Shocks, Struts & Suspension 2-Piece Suspension Strut and Coil Spring Kit (593024) 299.97 593024
    302839 2014 Chevrolet Equinox Bearings & Seals Wheel Hub Bearing Assembly 48.85 927944
    649394 2020 Jeep Wrangler Unlimited Sahara Brake Kits Power Stop K7940 Front Z23 Evolution Sport Brake Upgrade Kit 354.46 495116
    820684 2015 Buick LaCrosse Bearings & Seals Front/Rear Wheel Hub Bearing Assembly 5 Lugs w/ABS 79.97 799428
    602839 2017 GMC Savana 2500 Shocks, Struts & Suspension Suspension Kit (Front; with 3 Groove Pitman Arm) 142.44 304031

  6. After creating some records, make selections on the tree view and list view

  7. Close the forms and return to your programming environment
  8. Close your programming environment
Home Copyright © 2003-2023, FunctionX Sunday 01 April 2023