Example Application: Elementary Addition

	Records Maintenance

Editing or Updating Records

Introduction

Record maintenance includes modifying one or more records, or deleting one or more records. These operations can be performed visually or programmatically using a Data Definition Language (DDL) command.

The DDL command to update a record is UPDATE. The basic formula to use is:

UPDATE TableName SET ColumnName Operator Expression

You must specify the name of the involved table as the TableName factor of our formula. The SET statement allows you to specify a new value, Expression, for the field under the ColumnName column.

With this formula, you must specify the name of the involved table as the TableName factor of our formula. The SET statement allows you to specify a new value, Expression, for the field under the ColumnName column.

Imagine that, at one time, on a particular table, all records need to receive a new value under one particular column or certain columns. To update a record, the SQL provides the UPDATE keyword that is used to specify the table on which you want to maintain the record(s). The basic formula to use is:

UPDATE TableName
SET ColumnName = Expression

Here is an example:

Updating all Records

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnShowRecords;
    Button btnUpdateVideos;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShowRecords = new Button();
        btnShowRecords.Text = "Show Videos";
        btnShowRecords.Width = 120;
        btnShowRecords.Location = new Point(12, 12);
        btnShowRecords.Click += new EventHandler(btnShowRecordsClick);

        btnUpdateVideos = new Button();
        btnUpdateVideos.Text = "Update Videos";
        btnUpdateVideos.Location = new Point(150, 12);
        btnUpdateVideos.Width = 100;
        btnUpdateVideos.Click += new EventHandler(btnUpdateVideosClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 46);

        Text = "Video Collection";
        Controls.Add(btnUpdateVideos);
        Controls.Add(btnShowRecords);
        Controls.Add(dgvVideos);

        StartPosition = FormStartPosition.CenterScreen;
        dgvVideos.Width = this.Width - 30;
        dgvVideos.Height = this.Height - 80;
        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    private void btnShowRecordsClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos = new SqlConnection("Data Source=(local);" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                                new SqlCommand("SELECT ALL * FROM Collection.Videos;",
                                               cntVideos);
            cntVideos.Open();
            cmdVideos.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    void btnUpdateVideosClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                   new SqlCommand("UPDATE Collection.Videos SET Rating = N'R';",
                    cntVideos);
            cntVideos.Open();
            cmdVideos.ExecuteNonQuery();

            MessageBox.Show("All video records have been rated R.",
                   "Video Collection",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

With this code, all records of the Videos table will have their Rating fields set to a value of R:

Updating all Records

Practical Learning: Introducing Record Maintenance

Start Microsoft Visual Studio
Create a new Windows Forms Application named FunDS1 (which stands for Fun Department Store)
In the Solution Explorer, right-click Form1.cs and click Rename
Type FunDS and press Enter twice
Double-click the body of the form

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class FunDS : Form
    {
        public FunDS()
        {
            InitializeComponent();
        }

        private void CreateDatabase()
        {
            // Here We Go
        }

        private void FunDS_Load(object sender, EventArgs e)
        {
            CreateDatabase();
        }
    }
}

From the resources that accompany these lessons, open the FunDS HTML file and select everything in it
Paste it by replacing the // Here We Go line above
Execute the application and click OK on the message boxes
To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Manufacturer and click Add

Complete the design of the form as follows:

FunDS: New Manufacturer

Control	Text	Name	Other Properties
Label	Manufacturer:
TextBox		txtManufacturer	Modifiers: Public
Button	OK	btnOK	DialogResult: OK
Button	Cancel	btnCancel	DialogResult: Cancel

Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
AcceptButton: btnOK
CancelButton: btnCancel
FormBorderStyle: FixedDialog
MaximizeBox: False
MinimizeBox: False
ShowInTaskbar: False
StartPosition: CenterScreen
To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Manufacturers and click Add
Add a list view to the form and change its properties as follows:

Complete the design of the form as follows:

FunDS: Manufacturers

Control

Text

Name

Other Properties

ListView

(Name)	Text	TextAlign	Width
colManufacturer	Manufacturer		160

lvwEmployees

FullRowSelect: True
GridLines: True
View: Details

Button

New Manufacturer ...

btnNewManufacturer

Button

btnClose

Double-click an unoccupied area of the form
Return to the Manufacturers form and double-click the New Manufacturer button
Return to the Manufacturers form and double-click the Close button

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class Manufacturers : Form
    {
        public Manufacturers()
        {
            InitializeComponent();
        }

        private void ShowManufacturers()
        {
            lvwManufacturers.Items.Clear();

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                    "Database=FunDS1;" +
                                                                    "Integrated Security=Yes;"))
            {
                SqlCommand cmdManufacturers = new SqlCommand("SELECT * FROM Inventory.Manufacturers;",
                                                             scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaManufacturers = new SqlDataAdapter(cmdManufacturers);
                DataSet dsManufacturers = new DataSet("ManufacturersSet");
                sdaManufacturers.Fill(dsManufacturers);

                foreach (DataRow drManufacturer in dsManufacturers.Tables[0].Rows)
                {
                    ListViewItem lviManufacturer = new ListViewItem(drManufacturer["Manufacturer"].ToString());
                    lvwManufacturers.Items.Add(lviManufacturer);
                }
            }
        }

        private void Manufacturers_Load(object sender, EventArgs e)
        {
            ShowManufacturers();
        }

        private void btnNewManufacturer_Click(object sender, EventArgs e)
        {
            bool ManufacturerFound = false;
            Manufacturer man = new Manufacturer();

            if (man.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (string.IsNullOrEmpty(man.txtManufacturer.Text))
                {
                    MessageBox.Show("You must enter a manufacturer's name.",
                                    "FunDS - Fun Department Store",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
                else
                {
                    foreach (ListViewItem lviManufacturer in lvwManufacturers.Items)
                    {
                        if (man.txtManufacturer.Text == lviManufacturer.SubItems[0].Text)
                            ManufacturerFound = true;
                    }

                    if (ManufacturerFound == true)
                    {
                        MessageBox.Show("That manufacturer exists already.",
                                        "FunDS - Fun Department Store",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                        return;
                    }
                    else
                    {
                        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                        "Database=FunDS1;" +
                                                                        "Integrated Security=Yes;"))
                        {
                            SqlCommand cmdManufacturers = new SqlCommand("INSERT INTO Inventory.Manufacturers " +
                                                                         "VALUES(N'" + man.txtManufacturer.Text + "');",
                                                                         scFunDS);
                            scFunDS.Open();
                            cmdManufacturers.ExecuteNonQuery();
                        }
                    }
                }
            }

            ShowManufacturers();
        }

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

Return to the Employees form and double-click the New Employee button

Implement the event as follows:

private void btnNewEmployee_Click(object sender, EventArgs e)
{
    bool EmployeeFound = false;
    string strEmployeeNumber = "";
    Employee empl = new Employee();
    Random rndNumber = new Random();

    // Create a random employee number. The user can change it if necessary
    strEmployeeNumber = rndNumber.Next(10, 99).ToString() + "-" + rndNumber.Next(100, 999).ToString() + "-" + rndNumber.Next(100, 999).ToString();

    // Set some default values on the New Employee dialog box before the user opens it
    empl.txtEmployeeNumber.Text = strEmployeeNumber;
    empl.txtUserPassword.Text = "Password1";

    // Open the New Employee dialog box.
    // Find out if the user clicked OK after using it. If that's the case, get ready to create a new employee record
    if (empl.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (string.IsNullOrEmpty(empl.txtEmployeeNumber.Text))
        {
            MessageBox.Show("You must provide at least an employee number.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            foreach (ListViewItem lviEmployee in lvwEmployees.Items)
            {
                if (empl.txtEmployeeNumber.Text == lviEmployee.SubItems[0].Text)
                    EmployeeFound = true;
            }

            if (EmployeeFound == true)
            {
                MessageBox.Show("That employee number exists already.",
                                "FunDS - Fun Department Store",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
            else
            {
                using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                  "Database=FunDS17;" +
                                                                  "Integrated Security=Yes;"))
                {
                    SqlCommand cmdSubCategories =
                        new SqlCommand("INSERT INTO HumanResources.Employees " +
                                       "VALUES(N'" + empl.txtEmployeeNumber.Text + "', N'" +
                                       empl.txtFirstName.Text + "', N'" + empl.txtLastName.Text + "', N'" +
                                       empl.txtTitle.Text + "', " +
                                       double.Parse(empl.txtHourlySalary.Text) + ", N'" +
                                       empl.txtUsername.Text + "', N'" +
                                       empl.txtUserPassword.Text + "');",
                                       scFunDS);
                    scFunDS.Open();
                    cmdSubCategories.ExecuteNonQuery();
                }
            }
        }

        ShowEmployees();
    }
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Category and click Add

Complete the design of the form as follows:

FunDS: New Category

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

Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
AcceptButton: btnOK
CancelButton: btnCancel
FormBorderStyle: FixedDialog
MaximizeBox: False
MinimizeBox: False
ShowInTaskbar: False
StartPosition: CenterScreen
To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Categories and click Add
Add a list view to the form and create a column as follows:

(Name) Text TextAlign Width

colCategory Category 160

Complete the design of the form as follows:

FunDS: Categories

Control	Text	Name		Other Properties
ListView			lvwEmployees	FullRowSelect: True GridLines: True View: Details
Button	New Category ...	btnNewCategory
Button	Close	btnClose

Double-click an unoccupied area of the form
Return to the Categories form and double-click the New Category button
Return to the Categories form and double-click the Close button

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class Categories : Form
    {
        public Categories()
        {
            InitializeComponent();
        }

        private void ShowCategories()
        {
            lvwCategories.Items.Clear();

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                    "Database=FunDS1;" +
                                                                    "Integrated Security=Yes;"))
            {
                SqlCommand cmdCategories = new SqlCommand("SELECT * FROM Inventory.Categories;",
                                                             scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaCategories = new SqlDataAdapter(cmdCategories);
                DataSet dsCategories = new DataSet("CategoriesSet");
                sdaCategories.Fill(dsCategories);

                foreach (DataRow drCategory in dsCategories.Tables[0].Rows)
                {
                    ListViewItem lviCategory = new ListViewItem(drCategory["Category"].ToString());
                    lvwCategories.Items.Add(lviCategory);
                }
            }
        }

        private void Categories_Load(object sender, EventArgs e)
        {
            ShowCategories();
        }

        private void btnNewCategory_Click(object sender, EventArgs e)
        {
            bool CategoryFound = false;
            Category cat = new Category();

            if (cat.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (string.IsNullOrEmpty(cat.txtCategory.Text))
                {
                    MessageBox.Show("You must enter a category's name.",
                                    "FunDS - Fun Department Store",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
                else
                {
                    foreach (ListViewItem lviCategory in lvwCategories.Items)
                    {
                        if (cat.txtCategory.Text == lviCategory.SubItems[0].Text)
                            CategoryFound = true;
                    }

                    if (CategoryFound == true)
                    {
                        MessageBox.Show("That category exists already.",
                                        "FunDS - Fun Department Store",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                        return;
                    }
                    else
                    {
                        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                         "Database=FunDS1;" +
                                                                         "Integrated Security=Yes;"))
                        {
                            SqlCommand cmdCategories = new SqlCommand("INSERT INTO Inventory.Categories " +
                                                                      "VALUES(N'" + cat.txtCategory.Text + "');",
                                                                      scFunDS);
                            scFunDS.Open();
                            cmdCategories.ExecuteNonQuery();
                        }
                    }
                }
            }

            ShowCategories();
        }

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

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to SubCategory and click Add

Complete the design of the form as follows:

FunDS: New Category

Control	Text	Name	Other Properties
Label	Sub-Category:
TextBox		txtSubCategory	Modifiers: Public
Button	OK	btnOK	DialogResult: OK
Button	Cancel	btnCancel	DialogResult: Cancel

Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
AcceptButton: btnOK
CancelButton: btnCancel
FormBorderStyle: FixedDialog
MaximizeBox: False
MinimizeBox: False
ShowInTaskbar: False
StartPosition: CenterScreen
To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to SubCategories and click Add
Add a list view to the form and create a column as follows:

(Name) Text TextAlign Width

colSubCategory Sub-Category 160

Complete the design of the form as follows:

FunDS: Sub-Categories

Control	Text	Name		Other Properties
ListView			lvwEmployees	FullRowSelect: True GridLines: True View: Details
Button	New Sub-Category ...	btnNewSubCategory
Button	Close	btnClose

Double-click an unoccupied area of the form
Return to the SubCategories form and double-click the New Sub-Category button
Return to the SubCategories form and double-click the Close button

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class SubCategories : Form
    {
        public SubCategories()
        {
            InitializeComponent();
        }

        private void ShowSubCategories()
        {
            lvwSubCategories.Items.Clear();

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                    "Database=FunDS1;" +
                                                                    "Integrated Security=Yes;"))
            {
                SqlCommand cmdSubCategories = new SqlCommand("SELECT * FROM Inventory.SubCategories;",
                                                             scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaSubCategories = new SqlDataAdapter(cmdSubCategories);
                DataSet dsSubCategories = new DataSet("SubCategoriesSet");
                sdaSubCategories.Fill(dsSubCategories);

                foreach (DataRow drSubCategory in dsSubCategories.Tables[0].Rows)
                {
                    ListViewItem lviSubCategory = new ListViewItem(drSubCategory["SubCategory"].ToString());
                    lvwSubCategories.Items.Add(lviSubCategory);
                }
            }
        }

        private void SubCategories_Load(object sender, EventArgs e)
        {
            ShowSubCategories();
        }

        private void btnNewSubCategory_Click(object sender, EventArgs e)
        {
            bool SubCategoryFound = false;
            SubCategory scat = new SubCategory();

            if (scat.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (string.IsNullOrEmpty(scat.txtSubCategory.Text))
                {
                    MessageBox.Show("You must enter a sub-category's name.",
                                    "FunDS - Fun Department Store",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
                else
                {
                    foreach (ListViewItem lviSubCategory in lvwSubCategories.Items)
                    {
                        if (scat.txtSubCategory.Text == lviSubCategory.SubItems[0].Text)
                            SubCategoryFound = true;
                    }

                    if (SubCategoryFound == true)
                    {
                        MessageBox.Show("That sub-category exists already.",
                                        "FunDS - Fun Department Store",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                        return;
                    }
                    else
                    {
                        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                        "Database=FunDS1;" +
                                                                        "Integrated Security=Yes;"))
                        {
                            SqlCommand cmdSubCategories = new SqlCommand("INSERT INTO Inventory.SubCategories " +
                                                                         "VALUES(N'" + scat.txtSubCategory.Text + "');",
                                                                         scFunDS);
                            scFunDS.Open();
                            cmdSubCategories.ExecuteNonQuery();
                        }
                    }
                }
            }

            ShowSubCategories();
        }

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

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to StoreItem and click Add

Complete the design of the form as follows:

FunDS: New Store Item

Control	Text	Name	Other Properties
Label	Item Number:
TextBox		txtItemNumber	Modifiers: Public
Label	Date Entered:
DateTimePicker		dtpDateEntered	Modifiers: Public
Label	Manufacturer:
ComboBox		cbxManufacturers	DropDownList: DropDownList Modifiers: Public
Button	New ...	btnNewManufacturer
Label	Category:
ComboBox		cbxCategories	DropDownList: DropDownList Modifiers: Public
Button	New ...	btnNewCategory
Label	SubCategory:
ComboBox		cbxSubCategories	DropDownList: DropDownList Modifiers: Public
Button	New ...	btnNewSubCategory
Label	Item Name:
TextBox		txtItemName	Modifiers: Public
Label	Item Size:
TextBox		txtItemSize	Modifiers: Public
Label	Unit Price:
TextBox	0.00	txtUnitPrice	Modifiers: Public TextAlign: Right
Label	Discount Rate:
TextBox	0.00	txtDiscountRate	Modifiers: Public TextAlign: Right
Label	%
Button	Submit	btnSubmit
Button	Reset	btnReset
Button	Close	btnClose

Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
FormBorderStyle: FixedDialog
MaximizeBox: False
MinimizeBox: False
ShowInTaskbar: False
StartPosition: CenterScreen

Double-click an unoccupied area of the form and change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class StoreItem : Form
    {
        public StoreItem()
        {
            InitializeComponent();
        }

        private void PopulateStoreItem()
        {
            string strItemNumber = "";
            Random rndNumber = new Random();

            // Create a random item number. The user can change it if necessary
            strItemNumber = rndNumber.Next(100000, 999999).ToString();

            // Set some default values on the New Store Item dialog box before the user opens it
            txtItemNumber.Text = strItemNumber;
            dtpDateEntered.Value = DateTime.Today;

            cbxCategories.Items.Clear();
            cbxSubCategories.Items.Clear();

            cbxManufacturers.Items.Clear();
            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                             "Database=FunDS1;" +
                                                             "Integrated Security=Yes;"))
            {
                SqlCommand cmdManufacturers =
                    new SqlCommand("SELECT Manufacturer " +
                                   "FROM Inventory.Manufacturers;",
                                   scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaManufacturers = new SqlDataAdapter(cmdManufacturers);
                DataSet dsManufacturers = new DataSet("ManufacturersSet");
                sdaManufacturers.Fill(dsManufacturers);

                foreach (DataRow drManufacturer in dsManufacturers.Tables[0].Rows)
                    cbxManufacturers.Items.Add(drManufacturer["Manufacturer"].ToString());
            }

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                             "Database=FunDS1;" +
                                                             "Integrated Security=Yes;"))
            {
                SqlCommand cmdCategories =
                    new SqlCommand("SELECT Category " +
                                   "FROM Inventory.Categories;",
                                   scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaCategories = new SqlDataAdapter(cmdCategories);
                DataSet dsCategories = new DataSet("CategoriesSet");
                sdaCategories.Fill(dsCategories);

                foreach (DataRow drCategory in dsCategories.Tables[0].Rows)
                    cbxCategories.Items.Add(drCategory["Category"].ToString());
            }

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                             "Database=FunDS1;" +
                                                             "Integrated Security=Yes;"))
            {
                SqlCommand cmdSubCategories =
                    new SqlCommand("SELECT SubCategory " +
                                   "FROM Inventory.SubCategories;",
                                   scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaSubCategories = new SqlDataAdapter(cmdSubCategories);
                DataSet dsSubCategories = new DataSet("SubCategoriesSet");
                sdaSubCategories.Fill(dsSubCategories);

                foreach (DataRow drSubCategory in dsSubCategories.Tables[0].Rows)
                    cbxSubCategories.Items.Add(drSubCategory["SubCategory"].ToString());
            }

            cbxManufacturers.Text = "N/A";
            cbxCategories.Text = "N/A";
            cbxSubCategories.Text = "N/A";
            txtItemName.Text = "";
            txtItemSize.Text = "";
            txtUnitPrice.Text = "0.00";
            txtDiscountRate.Text = "0.00";
        }

        private void StoreItem_Load(object sender, EventArgs e)
        {
            PopulateStoreItem();
        }
    }
}

Return to the Store Item form and double-click the top New button

Implement the event as follows:

private void btnNewManufacturer_Click(object sender, EventArgs e)
{
    bool ManufacturerFound = false;
    Manufacturer man = new Manufacturer();

    // Show the New Manufacturer dialog box.
    // Find out if the user clicked OK after using the dialog box
    if (man.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        // If the user clicked OK...
        // ... if the Manufacturer text box was empty, do nothing
        if (string.IsNullOrEmpty(man.txtManufacturer.Text))
        {
            MessageBox.Show("You must enter a manufacturer's name.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            // ... check each item in the combo box
            foreach (string strManufacturer in cbxManufacturers.Items)
            {
                // If the manufacturer the user entered is already in the combo box, make a note.
                if (man.txtManufacturer.Text == strManufacturer)
                    ManufacturerFound = true;
            }

            // If the manufacturer the user entered is already in the combo box, there is no reason to add it
            if (ManufacturerFound == true)
            {
                MessageBox.Show("That manufacturer exists already.",
                                "FunDS - Fun Department Store",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
            else
            {
                // Since this is a new manufacturer, add its name to the Manufacturers table.
                using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                "Database=FunDS1;" +
                                                                "Integrated Security=Yes;"))
                {
                    SqlCommand cmdManufacturers = new SqlCommand("INSERT INTO Inventory.Manufacturers " +
                                                                 "VALUES(N'" + man.txtManufacturer.Text + "');",
                                                                 scFunDS);
                    scFunDS.Open();
                    cmdManufacturers.ExecuteNonQuery();
                }
            }
        }
    }

    // Before repopulating the combo box, remove all its items
    cbxManufacturers.Items.Clear();
    // Repopulate the combo box.
    using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                     "Database=FunDS1;" +
                                                     "Integrated Security=Yes;"))
    {
        SqlCommand cmdManufacturers =
            new SqlCommand("SELECT Manufacturer " +
                           "FROM Inventory.Manufacturers;",
                           scFunDS);
        scFunDS.Open();
        SqlDataAdapter sdaManufacturers = new SqlDataAdapter(cmdManufacturers);
        DataSet dsManufacturers = new DataSet("ManufacturersSet");
        sdaManufacturers.Fill(dsManufacturers);

        foreach (DataRow drManufacturer in dsManufacturers.Tables[0].Rows)
            cbxManufacturers.Items.Add(drManufacturer["Manufacturer"].ToString());
    }
}

Return to the Store Item form and double-click the second New button

Implement the event as follows:

private void btnNewCategory_Click(object sender, EventArgs e)
{
    bool CategoryFound = false;
    Category cat = new Category();

    if (cat.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (string.IsNullOrEmpty(cat.txtCategory.Text))
        {
            MessageBox.Show("You must enter a category's name.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            foreach (string strCategory in cbxCategories.Items)
            {
                if (cat.txtCategory.Text == strCategory)
                    CategoryFound = true;
            }

            if (CategoryFound == true)
            {
                MessageBox.Show("That category exists already.",
                                "FunDS - Fun Department Store",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
            else
            {
                using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                "Database=FunDS1;" +
                                                                "Integrated Security=Yes;"))
                {
                    SqlCommand cmdCategories = new SqlCommand("INSERT INTO Inventory.Categories " +
                                                                 "VALUES(N'" + cat.txtCategory.Text + "');",
                                                                 scFunDS);
                    scFunDS.Open();
                    cmdCategories.ExecuteNonQuery();

                    cbxCategories.Items.Add(cat.txtCategory.Text);
                }
            }
        }
    }
}

Return to the Store Item form and double-click the bottom New button

Implement the event as follows:

private void btnNewSubCategory_Click(object sender, EventArgs e)
{
    bool SubCategoryFound = false;
    SubCategory sc = new SubCategory();

    if (sc.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (string.IsNullOrEmpty(sc.txtSubCategory.Text))
        {
            MessageBox.Show("You must enter a sub-category's name.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            foreach (string strSubCategory in cbxSubCategories.Items)
            {
                if (sc.txtSubCategory.Text == strSubCategory)
                    SubCategoryFound = true;
            }

            if (SubCategoryFound == true)
            {
                MessageBox.Show("That sub-category exists already.",
                                "FunDS - Fun Department Store",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
            else
            {
                using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                "Database=FunDS1;" +
                                                                "Integrated Security=Yes;"))
                {
                    SqlCommand cmdSubCategories = new SqlCommand("INSERT INTO Inventory.SubCategories " +
                                                                 "VALUES(N'" + sc.txtSubCategory.Text + "');",
                                                                 scFunDS);
                    scFunDS.Open();
                    cmdSubCategories.ExecuteNonQuery();

                    cbxSubCategories.Items.Add(sc.txtSubCategory.Text);
                }
            }
        }
    }
}

Return to the Store Item form and double-click the Reset button

Implement the event as follows:

private void btnReset_Click(object sender, EventArgs e)
{
    PopulateStoreItem();
}

Return to the Store Item form and double-click the Submit button

Implement the event as follows:

private void btnSubmit_Click(object sender, EventArgs e)
{
    bool itemNumberFound = false;

    // Find out if the user clicked OK after using it. If that's the case, get ready to create a new store item
    if (string.IsNullOrEmpty(txtItemNumber.Text))
    {
        MessageBox.Show("You must provide at least an item number.",
                        "FunDS - Fun Department Store",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
        return;
    }
    else if (string.IsNullOrEmpty(txtItemName.Text))
    {
        MessageBox.Show("You must specify the name of the item or its description.",
                        "FunDS - Fun Department Store",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
        return;
    }
    else
    {
        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                     "Database=FunDS1;" +
                                                     "Integrated Security=Yes;"))
        {
            SqlCommand cmdStoreItems =
                new SqlCommand("SELECT ALL * FROM Inventory.StoreItems " +
                               "WHERE ItemNumber = N'" + txtItemNumber.Text + "';",
                               scFunDS);
            scFunDS.Open();
            SqlDataAdapter sdaStoreItems = new SqlDataAdapter(cmdStoreItems);
            DataSet dsStoreItems = new DataSet("StoreItemSet");
            sdaStoreItems.Fill(dsStoreItems);

            foreach (DataRow drStoreItem in dsStoreItems.Tables[0].Rows)
            {
                if (txtItemNumber.Text == drStoreItem["ItemNumber"].ToString())
                    itemNumberFound = true;
            }
        }

        if (itemNumberFound == true)
        {
            MessageBox.Show("That item number exists already.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                            "Database=FunDS1;" +
                                                            "Integrated Security=Yes;"))
            {
                SqlCommand cmdSubCategories =
                    new SqlCommand("INSERT INTO Inventory.StoreItems " +
                                   "VALUES(N'" + txtItemNumber.Text + "', N'" +
                                   dtpDateEntered.Value.ToShortDateString() + "', N'" +
                                   cbxManufacturers.Text + "', N'" + cbxCategories.Text + "', N'" +
                                   cbxSubCategories.Text + "', N'" + txtItemName.Text + "', N'" +
                                   txtItemSize.Text + "', " + double.Parse(txtUnitPrice.Text) + ", " +
                                   double.Parse(txtDiscountRate.Text) + ");",
                                   scFunDS);
                scFunDS.Open();
                cmdSubCategories.ExecuteNonQuery();
            }
        }
    }

    MessageBox.Show("The new item has been added to the inventory.",
                    "FunDS",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);

    PopulateStoreItem();
}

Return to the Store Item form and double-click the Close button

Implement the event as follows:

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

Display the FunDS form and add a button to it
Change the button's properties as follows:
(Name): btnNewStoreItem
Text: New Store Item ...

Double-click the New Store Item and implement the event as follows:

private void FunDS_Load(object sender, EventArgs e)
{
    // CreateDatabase();
}

private void btnNewStoreItem_Click(object sender, EventArgs e)
{
    StoreItem si = new StoreItem();
    si.Show();
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Employee and click Add

Complete the design of the form as follows:

FunDS: New employee

Control	Text	Name	DialogResult
Label	First Name:
TextBox		txtFirstName
Label	Last Name:
TextBox		txtLastName
Label	Title:
TextBox		txtTitle
Label	Hourly Salary:
TextBox		txtHourlySalary
Label	Employee #:
TextBox		txtEmployeeNumber
Label	Username:
TextBox		txtUsername
Label	Password:
TextBox		txtPassword
Button	OK	btnOK	OK
Button	Cancel	btnCancel	Cancel

Click an unoccupied area of the form and, in the Properties window, change the following characteristics:
AcceptButton: btnOK
CancelButton: btnCancel
FormBorderStyle: FixedDialog
MaximizeBox: False
MinimizeBox: False
ShowInTaskbar: False
StartPosition: CenterScreen
To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Employees and click Add

Complete the design of the form as follows:

FunDS: Employees

Control

Text

Name

Other Properties

ListView

(Name)	Text	TextAlign	Width
colReceiptNumber	Employee #		70
colFirstName	First Name		70
colLastName	Last Name		70
colTitle	Title		140
colHourlySalary	Salary	Right	70
colUsername	Username
colPassword	Password		65

lvwEmployees

FullRowSelect: True
GridLines: True
View: Details

Label

Number of employees

TextBox

txtNumberOfEmployees

TextAlign: Right

Button

New Employee ...

btnNewEmployee

Button

btnClose

Double-click an unoccupied area of the form

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class Employees : Form
    {
        public Employees()
        {
            InitializeComponent();
        }

        private void ShowEmployees()
        {
            lvwEmployees.Items.Clear();

            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                    "Database=FunDS17;" +
                                                                    "Integrated Security=Yes;"))
            {
                SqlCommand cmdEmployees = new SqlCommand("SELECT * FROM HumanResources.Employees;",
                                                             scFunDS);
                scFunDS.Open();
                SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
                DataSet dsCategories = new DataSet("EmployeesSet");
                sdaEmployees.Fill(dsCategories);

                foreach (DataRow drEmployee in dsCategories.Tables[0].Rows)
                {
                    ListViewItem lviEmployee = new ListViewItem(drEmployee["EmployeeNumber"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["FirstName"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["LastName"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["Title"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["HourlySalary"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["Username"].ToString());
                    lviEmployee.SubItems.Add(drEmployee["UserPassword"].ToString());

                    lvwEmployees.Items.Add(lviEmployee);
                }

                txtNumberOfEmployees.Text = dsCategories.Tables[0].Rows.Count.ToString();
            }
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            ShowEmployees();
        }
    }
}

Return to the Employees form and double-click the New Employee button

Implement the event as follows:

private void btnNewEmployee_Click(object sender, EventArgs e)
{
    bool EmployeeFound = false;
    string strEmployeeNumber = "";
    Employee empl = new Employee();
    Random rndNumber = new Random();

    // Create a random employee number. The user can change it if necessary
    strEmployeeNumber = rndNumber.Next(10, 99).ToString() + "-" + rndNumber.Next(100, 999).ToString() + "-" + rndNumber.Next(100, 999).ToString();

    // Set some default values on the New Employee dialog box before the user opens it
    empl.txtEmployeeNumber.Text = strEmployeeNumber;
    empl.txtUserPassword.Text = "Password1";

    // Open the New Employee dialog box.
    // Find out if the user clicked OK after using it. If that's the case, get ready to create a new employee record
    if (empl.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (string.IsNullOrEmpty(empl.txtEmployeeNumber.Text))
        {
            MessageBox.Show("You must provide at least an employee number.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }
        else
        {
            foreach (ListViewItem lviEmployee in lvwEmployees.Items)
            {
                if (empl.txtEmployeeNumber.Text == lviEmployee.SubItems[0].Text)
                    EmployeeFound = true;
            }

            if (EmployeeFound == true)
            {
                MessageBox.Show("That employee number exists already.",
                                "FunDS - Fun Department Store",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
            else
            {
                using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                  "Database=FunDS17;" +
                                                                  "Integrated Security=Yes;"))
                {
                    SqlCommand cmdSubCategories =
                        new SqlCommand("INSERT INTO HumanResources.Employees " +
                                       "VALUES(N'" + empl.txtEmployeeNumber.Text + "', N'" +
                                       empl.txtFirstName.Text + "', N'" + empl.txtLastName.Text + "', N'" +
                                       empl.txtTitle.Text + "', " +
                                       double.Parse(empl.txtHourlySalary.Text) + ", N'" +
                                       empl.txtUsername.Text + "', N'" +
                                       empl.txtUserPassword.Text + "');",
                                       scFunDS);
                    scFunDS.Open();
                    cmdSubCategories.ExecuteNonQuery();
                }
            }
        }

        ShowEmployees();
    }
}

Display the FunDS form and add a button to it
Change the properties of the button as follows:
(Name): btnEmployees
Text: Employees ...

Double-click the Store Inventory button and implement its event as follows:

private void btnEmployees_Click(object sender, EventArgs e)
{
    Employees empls = new Employees();
    empls.Show();
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to StoreInventory and click Add
Add a list view to the form and change its properties as follows:
(Name): lvwStoreItems
FullRowSelect: True
GridLines: True
View: Details

Create the columns as follows:

(Name)	Text	TextAlign	Width
colItemID	Item ID		50
colItemNumber	Item #	Center
colDateEntered	Date Entered	Center	75
colManufacturer	Manufacturer		130
colCategory	Category		70
colSubCategory	Sub-Category		70
colItemName	Item Name		300
colItemSize	Size		80
colUnitPrice	Unit Price	Right
colDaysInStore	Days in Store	Right	75
colDiscountRate	Discount Rate	Right	80

Complete the design of the form as follows:

Control	Text	Name	Other Properties
ListView
Label	Number of Records:
TextBox		txtNumberOfRecords	TextAlign: Right
Button	New Store Item ...	btnNewStoreItem
Button	Close	btnClose

Double-click an unoccupied area of the form

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class StoreInventory : Form
    {
        public StoreInventory()
        {
            InitializeComponent();
        }

        private void ShowInventory()
        {
            using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                               "Database='FunDS1';Integrated Security=True;"))
            {
                SqlCommand cmdStoreItems = new SqlCommand("SELECT sis.StoreItemID, " +
                                                          "       sis.ItemNumber, " +
                                                          "       sis.DateEntered, " +
                                                          "       sis.Manufacturer, " +
                                                          "       sis.Category, " +
                                                          "       sis.SubCategory, " +
                                                          "       sis.ItemName, " +
                                                          "       sis.ItemSize, " +
                                                          "       sis.DaysInStore, " +
                                                          "       sis.UnitPrice, " +
                                                          "       sis.DiscountRate, " +
                                                          "       sis.DiscountAmount, " +
                                                          "       sis.MarkedPrice " +
                                                          "FROM   Inventory.StoreItems sis " +
                                                          "ORDER BY sis.StoreItemID;",
                                                          cntFunDS);

                cntFunDS.Open();
                cmdStoreItems.ExecuteNonQuery();

                SqlDataAdapter sdaStoreItems = new SqlDataAdapter(cmdStoreItems);
                DataSet dsStoreItems = new DataSet("StoreItemsSet");

                sdaStoreItems.Fill(dsStoreItems);

                lvwStoreItems.Items.Clear();

                foreach (DataRow drStoreItem in dsStoreItems.Tables[0].Rows)
                {
                    ListViewItem lviStoreItem = new ListViewItem(drStoreItem["StoreItemID"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["ItemNumber"].ToString());
                    lviStoreItem.SubItems.Add(DateTime.Parse(drStoreItem["DateEntered"].ToString()).ToShortDateString());
                    lviStoreItem.SubItems.Add(drStoreItem["Manufacturer"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["Category"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["SubCategory"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["ItemName"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["ItemSize"].ToString());
                    lviStoreItem.SubItems.Add(drStoreItem["DaysInStore"].ToString());
                    lviStoreItem.SubItems.Add(double.Parse(drStoreItem["UnitPrice"].ToString()).ToString("F"));
                    if (!(string.IsNullOrEmpty(drStoreItem["DiscountRate"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drStoreItem["DiscountRate"].ToString()).ToString("P"));
                    if (!(string.IsNullOrEmpty(drStoreItem["DiscountAmount"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drStoreItem["DiscountAmount"].ToString()).ToString("F"));
                    if (!(string.IsNullOrEmpty(drStoreItem["MarkedPrice"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drStoreItem["MarkedPrice"].ToString()).ToString("F"));
                    lvwStoreItems.Items.Add(lviStoreItem);
                }

                txtNumberOfRecords.Text = dsStoreItems.Tables[0].Rows.Count.ToString();
            }
        }

        private void StoreInventory_Load(object sender, EventArgs e)
        {
            ShowInventory();
        }
    }
}

Return to the form and double-click the New Store Item

Implement the event as follows:

private void btnNewStoreItem_Click(object sender, EventArgs e)
{
    StoreItem si = new StoreItem();
    si.Show();
}

Display the FunDS form and add a button to it
Change the properties of the button as follows:
(Name): btnStoreInventory
Text: Store Inventory ...

Double-click the Store Inventory button and implement its event as follows:

private void btnStoreItems_Click(object sender, EventArgs e)
{
    StoreInventory sinv = new StoreInventory();
    sinv.Show();
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to SoldItems and click Add

Add a list view to the form and change its properties as follows:

(Name)	Text	TextAlign	Width
colSoldItemID	SI ID		40
colReceiptNumber	Receipt #	Center
colDateSold	Date Sold	Center	75
colItemNumber	Item #	Center
colManufacturer	Manufacturer		130
colCategory	Category		70
colSubCategory	Sub-Category		80
colItemName	Item Name		300
colItemSize	Size		80
colUnitPrice	Unit Price	Right
colDiscountRate	Disc Rate	Right	80
colDiscountAmount	Disc Amt	Right	80
colSalePrice	Sale Price	Right

Complete the design of the form as follows:

Control	Text	Name		Other Properties
ListView			lvwSoldItems	FullRowSelect: True GridLines: True View: Details
Label	Number of Records
TextBox		txtNumberOfRecords		TextAlign: Right
Button	Close	btnClose

Double-click an unoccupied area of the form
Return to the form and double-click the Close button

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class SoldItems : Form
    {
        public SoldItems()
        {
            InitializeComponent();
        }

        private void SoldItems_Load(object sender, EventArgs e)
        {
            using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                              "Database='FunDS1';Integrated Security=True;"))
            {
                SqlCommand cmdSoldItems = new SqlCommand("SELECT sis.SoldItemID, " +
                                                          "	 sis.ReceiptNumber, " +
                                                          "      sis.SaleDate, " +
                                                          "      sis.ItemNumber, " +
                                                          "      sis.Manufacturer, " +
                                                          "      sis.Category, " +
                                                          "      sis.SubCategory, " +
                                                          "      sis.ItemName, " +
                                                          "      sis.ItemSize, " +
                                                          "      sis.UnitPrice, " +
                                                          "      sis.DiscountRate, " +
                                                          "      sis.DiscountAmount, " +
                                                          "      sis.SalePrice " +
                                                          "FROM Inventory.SoldItems sis " +
                                                          "ORDER BY sis.SoldItemID;",
                                                          cntFunDS);

                cntFunDS.Open();
                cmdSoldItems.ExecuteNonQuery();

                SqlDataAdapter sdaSoldItems = new SqlDataAdapter(cmdSoldItems);
                DataSet dsSoldItems = new DataSet("SoldItemsSet");

                sdaSoldItems.Fill(dsSoldItems);

                foreach (DataRow drSoldItem in dsSoldItems.Tables[0].Rows)
                {
                    ListViewItem lviStoreItem = new ListViewItem(drSoldItem["SoldItemID"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["ReceiptNumber"].ToString());
                    lviStoreItem.SubItems.Add(DateTime.Parse(drSoldItem["SaleDate"].ToString()).ToShortDateString());
                    lviStoreItem.SubItems.Add(drSoldItem["ItemNumber"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["Manufacturer"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["Category"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["SubCategory"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["ItemName"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["ItemSize"].ToString());
                    lviStoreItem.SubItems.Add(drSoldItem["UnitPrice"].ToString());
                    if (!(string.IsNullOrEmpty(drSoldItem["DiscountRate"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drSoldItem["DiscountRate"].ToString()).ToString("P"));
                    if (!(string.IsNullOrEmpty(drSoldItem["DiscountAmount"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drSoldItem["DiscountAmount"].ToString()).ToString("P"));
                    if (!(string.IsNullOrEmpty(drSoldItem["SalePrice"].ToString())))
                        lviStoreItem.SubItems.Add(double.Parse(drSoldItem["SalePrice"].ToString()).ToString("P"));
                    lvwSoldItems.Items.Add(lviStoreItem);
                }

                txtNumberOfRecords.Text = dsSoldItems.Tables[0].Rows.Count.ToString();
            }
        }

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

Display the FunDS form and add a button to it
Change the properties of the button as follows:
(Name): btnSoldItems
Text: Sold Items ...

Double-click the Store Inventory button and implement its event as follows:

private void btnSoldItems_Click(object sender, EventArgs e)
{
    SoldItems sis = new SoldItems();
    sis.Show();
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to UnsoldItems and click Add
Add a list view to the form and change its properties as follows:

Complete the design of the form as follows:

Control

Text

Name

Other Properties

ListView

(Name)	Text	TextAlign	Width
colSoldItemID	SI ID		40
colReceiptNumber	Receipt #	Center
colDateSold	Date Sold	Center	75
colItemNumber	Item #	Center
colManufacturer	Manufacturer		130
colCategory	Category		70
colSubCategory	Sub-Category		80
colItemName	Item Name		300
colItemSize	Size		80
colUnitPrice	Unit Price	Right
colDaysInStore	Days in Store	Right	80
colDateRemovedFromStore	Date Removed From Store	Center	140

lvwSoldItems

FullRowSelect: True
GridLines: True
View: Details

Label

Number of Records

TextBox

txtNumberOfRecords

TextAlign: Right

Button

btnClose

Double-click an unoccupied area of the form
Return to the form and double-click the Close button

Change the document 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class UnsoldItems : Form
    {
        public UnsoldItems()
        {
            InitializeComponent();
        }

        private void UnsoldItems_Load(object sender, EventArgs e)
        {
            using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                              "Database='FunDS1';Integrated Security=True;"))
            {
                SqlCommand cmdUnsoldItems = new SqlCommand("SELECT sis.UnsoldItemID, " +
                                                          "        sis.ItemNumber, " +
                                                          "	   sis.OriginalDateEntered, " +
                                                          "        sis.Manufacturer, " +
                                                          "        sis.Category, " +
                                                          "        sis.SubCategory, " +
                                                          "        sis.ItemName, " +
                                                          "        sis.ItemSize, " +
                                                          "        sis.UnitPrice, " +
                                                          "        sis.DaysInStore, " +
                                                          "        sis.DateRemovedFromStore " +
                                                          "FROM Inventory.UnsoldItems sis " +
                                                          "ORDER BY sis.UnsoldItemID;",
                                                          cntFunDS);

                cntFunDS.Open();
                cmdUnsoldItems.ExecuteNonQuery();

                SqlDataAdapter sdaUnsoldItems = new SqlDataAdapter(cmdUnsoldItems);
                DataSet dsUnsoldItems = new DataSet("UnsoldItemsSet");

                sdaUnsoldItems.Fill(dsUnsoldItems);

                foreach (DataRow drUnsoldItem in dsUnsoldItems.Tables[0].Rows)
                {
                    ListViewItem lviStoreItem = new ListViewItem(drUnsoldItem["UnsoldItemID"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["ItemNumber"].ToString());
                    lviStoreItem.SubItems.Add(DateTime.Parse(drUnsoldItem["OriginalDateEntered"].ToString()).ToShortDateString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["Manufacturer"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["Category"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["SubCategory"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["ItemName"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["ItemSize"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["UnitPrice"].ToString());
                    lviStoreItem.SubItems.Add(drUnsoldItem["DaysInStore"].ToString());
                    lviStoreItem.SubItems.Add(DateTime.Parse(drUnsoldItem["DateRemovedFromStore"].ToString()).ToShortDateString());
                    lvwUnsoldItems.Items.Add(lviStoreItem);
                }

                txtNumberOfRecords.Text = dsUnsoldItems.Tables[0].Rows.Count.ToString();
            }
        }

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

Display the FunDS form and add a button to it
Change the properties of the button as follows:
(Name): btnUnsoldItems
Text: Unsold Items ...

Double-click the Store Inventory button and implement its event as follows:

private void btnUnsoldItems_Click(object sender, EventArgs e)
{
    UnsoldItems usis = new UnsoldItems();
    usis.Show();
}

To add a new form, on the main menu, click PROJECT -> Add Windows Form ...
Set the name to PointOfSale and click Add
Add a list view to the form

Create its columns as follows:

(Name)	Text	TextAlign	Width
colItemNumber	Item #	Center	100
colItemName	Item Name/Description		610
colItemSize	Size		150
colUnitPrice	Unit Price	Right	135
colDiscountRate	Dscnt Rate	Right	120
colDiscountAmount	Dscnt Amt	Right	140
colSalePrice	Sale Price	Right	130

Design the form as follows:

FunDS - Point of Sale

Control	Text	Name	Other Properties
Label	Item # to Add:
TextBox		txtItemNumberAdd
Label	Receipt #:
TextBox		txtReceiptNumber
ListView			FullRowSelect: True GridLines: True View: Details
Label	Item # to Remove:
TextBox		txtItemNumberRemove
Button	Remove	btnRemoveItem
Label	Order Total:
TextBox		txtOrderTotal	Text: 0.00 TextAlign: Right
Label	Tendered:
TextBox		txtAmountTendered	Text: 0.00 TextAlign: Right
Label	Processed By:
TextBox		txtEmployeeNumber
TextBox		txtEmployeeName
Label	Change:
TextBox		txtChange
DateTimePicker		dtpSaleDate
DateTimePicker		dtpSaleTime	Format: Time
Button	Reset	btnReset
Button	Submit	btnSubmit
Button	Close	btnClose

Click the Item # to Add text box
In the Properties window, click the Events button

Double-click KeyUp and 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FunDS1
{
    public partial class PointOfSale : Form
    {
        public PointOfSale()
        {
            InitializeComponent();
        }

        private void txtItemNumberAdd_KeyUp(object sender, KeyEventArgs e)
        {
            bool itemFound = false;
            double dblTotal = 0.00;

            if (e.KeyCode == Keys.Enter)
            {
                if (string.IsNullOrEmpty(txtItemNumberAdd.Text))
                {
                    /* MessageBox.Show("You must enter an item number.",
                                    "FunDS - Fun Department Store",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);*/
                    return;
                }
                else
                {
                    using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                     "Database='FunDS1';" +
                                                                     "Integrated Security=Yes;"))
                    {
                        SqlCommand cmdStoreItems = new SqlCommand("SELECT ALL * FROM Inventory.StoreItems " +
                                                                  "WHERE ItemNumber = N'" + txtItemNumberAdd.Text + "';",
                                                                  scFunDS);
                        SqlDataAdapter sdaStoreItems = new SqlDataAdapter(cmdStoreItems);
                        DataSet dsStoreItems = new DataSet("StoreItemsSet");
                        sdaStoreItems.Fill(dsStoreItems);

                        scFunDS.Open();

                        foreach (DataRow rowStoreItem in dsStoreItems.Tables[0].Rows)
                        {
                            if (txtItemNumberAdd.Text == rowStoreItem["ItemNumber"].ToString())
                            {
                                itemFound = true;

                                ListViewItem lviStoreItem = new ListViewItem(rowStoreItem["ItemNumber"].ToString());
                                lviStoreItem.SubItems.Add(rowStoreItem["ItemName"].ToString());
                                lviStoreItem.SubItems.Add(rowStoreItem["ItemSize"].ToString());
                                lviStoreItem.SubItems.Add(double.Parse(rowStoreItem["UnitPrice"].ToString()).ToString("F"));
                                lviStoreItem.SubItems.Add(double.Parse(rowStoreItem["DiscountRate"].ToString()).ToString("P"));
                                lviStoreItem.SubItems.Add(double.Parse(rowStoreItem["DiscountAmount"].ToString()).ToString("F"));
                                lviStoreItem.SubItems.Add(double.Parse(rowStoreItem["MarkedPrice"].ToString()).ToString("F"));
                                lvwSelectedItems.Items.Add(lviStoreItem);
                                txtItemNumberAdd.Text = "";
                            }
                        }

                        if (itemFound == false)
                        {
                            MessageBox.Show("There is no item with that item number.",
                                            "FunDS - Fun Department Store",
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Information);
                        }
                    }

                    if (lvwSelectedItems.Items.Count > 0)
                    {
                        foreach (ListViewItem lviStoreItem in lvwSelectedItems.Items)
                            dblTotal += double.Parse(lviStoreItem.SubItems[6].Text);

                        txtOrderTotal.Text = dblTotal.ToString("F");
                    }
                }
            }
        }
    }
}

Return to the Point of Sale form and double-click the Remove button

Implement the event as follows:

private void btnRemoveItem_Click(object sender, EventArgs e)
{
    bool itemFound = false;

    if (lvwSelectedItems.Items.Count == 0)
    {
        MessageBox.Show("The list view is empty.",
                        "FunDS", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }
    else if (string.IsNullOrEmpty(txtItemNumberRemove.Text))
    {
        MessageBox.Show("You must enter an item number and that exists in the list view.",
                        "FunDS", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }
    else
    {
        foreach (ListViewItem lviStoreItem in lvwSelectedItems.Items)
        {
            if (lviStoreItem.SubItems[0].Text.Equals(txtItemNumberRemove.Text))
            {
                itemFound = true;
                lvwSelectedItems.Items.Remove(lviStoreItem);
            }
        }

        if (itemFound == false)
        {
            MessageBox.Show("That item number is not in the list view.",
                            "FunDS", MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
            return;
        }

        txtItemNumberRemove.Text = "";
    }
}

Return to the Point of Sale form and click the Tendered text box
In the Events section of the Properties window, double-click KeyUp

Implement the event as follows:

private void txtAmountTendered_KeyUp(object sender, KeyEventArgs e)
{
    double orderTotal = 0.00;
    double amountTendered = 0.00;
    double change = 0.00;

    if (e.KeyCode == Keys.Enter)
    {
        if (string.IsNullOrEmpty(txtOrderTotal.Text))
        {
            return;
        }
        else if (string.IsNullOrEmpty(txtAmountTendered.Text))
        {
            return;
        }
        else
        {
            orderTotal = double.Parse(txtOrderTotal.Text);
            amountTendered = double.Parse(txtAmountTendered.Text);
            change = amountTendered - orderTotal;

            txtChange.Text = change.ToString("F");
        }
    }
}

Return to the Point of Sale form and click the Processed By text box
In the Events section of the Properties window, double-click KeyUp

Implement the event as follows:

private void txtEmployeeNumber_KeyUp(object sender, KeyEventArgs e)
{
    bool employeeFound = false;

    if (e.KeyCode == Keys.Enter)
    {
        if (string.IsNullOrEmpty(txtEmployeeNumber.Text))
        {
            return;
        }
        else
        {
            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                                "Database=FunDS17;" +
                                                                "Integrated Security=Yes;"))
            {
                SqlCommand cmdEmployees = new SqlCommand("SELECT ALL * FROM HumanResources.Employees " +
                                                          "WHERE EmployeeNumber = N'" + txtEmployeeNumber.Text + "';",
                                                             scFunDS);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
                DataSet dsEmployees = new DataSet("EmployeesSet");
                sdaEmployees.Fill(dsEmployees);

                scFunDS.Open();

                foreach (DataRow rowEmployee in dsEmployees.Tables[0].Rows)
                {
                    if (txtEmployeeNumber.Text == rowEmployee["EmployeeNumber"].ToString())
                    {
                        employeeFound = true;
                        txtEmployeeName.Text = string.Concat(rowEmployee["LastName"].ToString(), ", ", rowEmployee["FirstName"].ToString());
                    }

                }

                if (employeeFound == false)
                {
                    MessageBox.Show("There is no employee with that item number.",
                            "FunDS - Fun Department Store",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
                }
            }
        }
    }
}

Return to the Point of Sale form and double-click the Reset button

Implement the event as follows:

private void btnReset_Click(object sender, EventArgs e)
{
    int iReceiptNumber = 100001;

    using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                      "Database=FunDS17;" +
                                                      "Integrated Security=Yes;"))
    {
        SqlCommand cmdShoppingSessions =
            new SqlCommand("SELECT ALL * FROM Transactions.ShoppingSessions;",
                           scFunDS);
        SqlDataAdapter sdaShoppingSessions = new SqlDataAdapter(cmdShoppingSessions);
        DataSet dsShoppingSessions = new DataSet("ShoppingSessionsSet");
        sdaShoppingSessions.Fill(dsShoppingSessions);

        scFunDS.Open();

        if (dsShoppingSessions.Tables[0].Rows.Count == 0)
            iReceiptNumber = 100001;
        else
        {
            foreach (DataRow rowShoppingSession in dsShoppingSessions.Tables[0].Rows)
            {
                iReceiptNumber = int.Parse(rowShoppingSession["ReceiptNumber"].ToString()) + 1;
            }
        }
    }

    txtReceiptNumber.Text = iReceiptNumber.ToString();
    txtItemNumberAdd.Text = "";
    lvwSelectedItems.Items.Clear();
    txtItemNumberRemove.Text = "";
    dtpSaleDate.Value = DateTime.Now;
    dtpSaleTime.Value = DateTime.Now;
    // txtEmployeeNumber.Text = ""; ;
    // txtEmployeeName.Text = "";
    txtOrderTotal.Text = "0.00";
    txtAmountTendered.Text = "0.00";
    txtChange.Text = "0.00";
}

Return to the Point of Sale form and double-click an unoccupied area of its body

Implement the event as follows:

private void PointOfSale_Load(object sender, EventArgs e)
{
    btnReset_Click(sender, e);
}

Display the FunDS form and add a button to it
Change the properties of the button as follows:
(Name): btnPointOfSale
Text: Point of Sale ...

Double-click the Point of Sale button and implement its event as follows:

private void btnPointOfSale_Click(object sender, EventArgs e)
{
    PointOfSale pos = new PointOfSale();
    pos.Show();
}

Updating all Records

The SQL allows you change all records of a table with a single line of code. The formula to follow is:

UPDATE TableName
SET ColumnName = Expression

The WHERE operator allows you to specify how the particular record involved would be identified. It is very important, in most cases, that the criterion used be able to uniquely identify the record. In the above table, imagine that you ask the interpreter to change the released year to 1996 where the director of the video is Rob Reiner. The UPDATE statement would be written as follows:

UPDATE Videos
SET YearReleased = 1996
WHERE Director = 'Rob Reiner';

Practical Learning: Updating all Records

Our StoreItems table contains a field that shows for how many days an item has been in the store. The reason we did not specify the values of that field is because those values change on a daily basis: items are added items are deleted, data about items change (for example, an employee can change the date entered of an item, for any reason). Instead, we will allow the employee(s) to click a button and update the number of days the items (each item) have (has) been in the store.
Display the Store Inventory form and add a button to it
Change the characteristics of the button as follows:
(Name): btnDaysInStore
Text: Update Number of Days in Store

Double-click the Number of Days in Store button and implement its event as follows:

private void btnDaysInStore_Click(object sender, EventArgs e)
{
    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                   "Database='FunDS5';Integrated Security=True;"))
    {
        SqlCommand cmdStoreItems = new SqlCommand("UPDATE Inventory.StoreItems " +
                                                  "SET DaysInStore = DATEDIFF(d, DateEntered, SYSDATETIME());",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();

        ShowInventory();
    }
}

Execute the application and click the Show Inventory button
After seeing the existing records, click the Number of Days in Store button
Close the forms and return to your programming environment

Updating One or Some Records

Editing a record consists of changing a value in a field. It could be that the field is empty, such as the � Year of the the 'The Lady Killers' video of the following table. It could be that the value is wrong, such as the Director of the the 'The Distinguished Gentleman' video of this table:

Video Title	Director	� Year	Length	Rating
A Few Good Men	Rob Reiner	1992	138 Minutes	R
The Silence of the Lambs	Jonathan Demme	1991	118 Minutes
The Distinguished Gentleman	James Groeling		112 Minutes	R
The Lady Killers	Joel Coen & Ethan Coen		104 Minutes	R
Ghosts of Mississippi	Rob Reiner		130 Minutes

To visually edit a record, first open the table to view its records. Locate the record, the column on which you want to work, and locate the value you want to change, then change it.

In SQL, you must provide a way for the interpreter to locate the record. To do this, you would associate the WHERE operator in an UPDATE statement using the following formula:

UPDATE TableName
SET ColumnName = Expression
WHERE Condition(s)

UPDATE Videos
SET YearReleased = 1996
WHERE Director = 'Rob Reiner';

In the above table, there are at least two videos directed by Rob Reiner. When this statement is executed, all video records whose director is Rob Reiner would be changed, which would compromise existing records that did not need this change. Therefore, make sure your WHERE statement would isolate one particular record or only those that need to be updated. Here is an example used to change the name of the director of a particular video:

void btnChangeVideoDirectorClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
 	new SqlConnection("Data Source=(local);" +
            "Database='VideoCollection1';" +
            "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
              new SqlCommand("UPDATE Videos " +
                             "SET Director = 'Jonathan Lynn' " +
                             "WHERE [Video Title] = N'The Distinguished Gentleman';",
                             cntVideos);
        cntVideos.Open();
        cmdVideos.ExecuteNonQuery();

        MessageBox.Show("The director of 'The Distinguished Gentleman' " +
                        "video has been updated.",
                        "Video Collection",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

Practical Learning: Updating Some Records

Display the Store Inventory form and add a button to it
Change the characteristics of the button as follows:
(Name): btnDiscountRates
Text: Update the Discount Rates

Double-click the Update the Discount Rates button and implement the event as follows:

private void btnDiscountRates_Click(object sender, EventArgs e)
{
    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                       "Database='FunDS25';Integrated Security=True;"))
    {
        SqlCommand cmdStoreItems = new SqlCommand("UPDATE Inventory.StoreItems " +
                                                  "SET DiscountRate = 0.10 WHERE DATEDIFF(d, DateEntered, SYSDATETIME()) > 30; " +
                                                  "UPDATE Inventory.StoreItems " +
                                                  "SET DiscountRate = 0.15 WHERE DATEDIFF(d, DateEntered, SYSDATETIME()) > 45; " +
                                                  "UPDATE Inventory.StoreItems " +
                                                  "SET DiscountRate = 0.30 WHERE DATEDIFF(d, DateEntered, SYSDATETIME()) > 60; " +
                                                  "UPDATE Inventory.StoreItems " +
                                                  "SET DiscountRate = 0.50 WHERE DATEDIFF(d, DateEntered, SYSDATETIME()) > 75; " +
                                                  "UPDATE Inventory.StoreItems " +
                                                  "SET DiscountRate = 0.75 WHERE DATEDIFF(d, DateEntered, SYSDATETIME()) > 90",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();
    }

    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                       "Database='FunDS25';Integrated Security=True;"))
    {
        SqlCommand cmdStoreItems = new SqlCommand("UPDATE Inventory.StoreItems " +
                                                  "SET DiscountAmount = FORMAT(UnitPrice * DiscountRate, N'F'); " +
                                                  "UPDATE Inventory.StoreItems " +
                                                  "SET MarkedPrice = FORMAT(UnitPrice - DiscountAmount, N'F');",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();
    }
     
    ShowInventory();
}

Execute the application and click the Show Inventory button
After seeing the existing records, click the Update the Discount Rates button
Close the forms and return to your programming environment

Deleting Records

Deleting all Records

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure.

To visually delete all records from a table, open it in design view, first select all of them, and press Delete. You would receive a warning. If you still want to delete the records, click Yes. If you change your mind, click No.

Using SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE TableName;

When this statement is executed, all records from the TableName factor would be removed from the table. Be careful when doing this because once the records have been deleted, you cannot get them back.

Removing the First n Records

Instead of removing all records, to delete only the first n of a table, use the following formula:

DELETE TOP (Number) TableName;

In the parentheses, enter the desired number of records. When the statement executes, the first n records of the table would be deleted. Here is an example:

DELETE TOP (2) Employees;
GO

This statement asks the database engine to delete the first two records of the Employees table.

Removing a Specific Record

If you find out that a record is not necessary, not anymore, or is misplaced, you can remove it from a table.

To visually remove a record from a table, open the table in Table view, right-click the gray box of the record and click Delete. You can also first select the record and press Delete. You would receive a warning to confirm your intention.

To programmatically delete a record:

In the Object Explorer, right the table, position the mouse on Script Table As -> DELETE To -> New Query Editor Window
Open an empty Query Editor and type your code

In SQL, to delete a record, use the DELETE FROM statement associated with the WHERE operator. The formula to follow is:

DELETE FROM TableName
WHERE Condition(s)

The TableName factor is used to identify a table whose record(s) would be removed.

The Condition(s) factor allows you to identify a record or a group of records that carries a criterion. Once again, make sure you are precise in your criteria so you would not delete the wrong record(s). Here is an example used to remove a particular record from the table:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnShowRecords;
    Button btnDeleteVideo;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShowRecords = new Button();
        btnShowRecords.Text = "Show Videos";
        btnShowRecords.Width = 120;
        btnShowRecords.Location = new Point(12, 12);
        btnShowRecords.Click += new EventHandler(btnShowRecordsClick);

        btnDeleteVideo = new Button();
        btnDeleteVideo.Text = "Delete Video";
        btnDeleteVideo.Location = new Point(150, 12);
        btnDeleteVideo.Width = 100;
        btnDeleteVideo.Click += new EventHandler(btnDeleteVideoClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 46);

        Text = "Video Collection";
        Controls.Add(btnDeleteVideo);
        Controls.Add(btnShowRecords);
        Controls.Add(dgvVideos);

        StartPosition = FormStartPosition.CenterScreen;
        dgvVideos.Width = this.Width - 30;
        dgvVideos.Height = this.Height - 80;
        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    private void btnShowRecordsClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos = new SqlConnection("Data Source=(local);" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                                new SqlCommand("SELECT ALL * FROM Collection.Videos;",
                                               cntVideos);
            cntVideos.Open();
            cmdVideos.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    void btnDeleteVideoClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
     	new SqlConnection("Data Source=(local);" +
        	          "Database='VideoCollection1';" +
                	  "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                  new SqlCommand("DELETE FROM Collection.Videos " +
                                 "WHERE Title = N'The Lady Killers';",
                                 cntVideos);
            cntVideos.Open();
            cmdVideos.ExecuteNonQuery();

            MessageBox.Show("The video title The Lady Killers has been deleted.",
                            "Video Collection",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Practical Learning: Deleting Some Records

Display the Store Inventory form and add a button to it
Change the characteristics of the button as follows:
(Name): btnDeleteOldItems
Text: Delete Old Items (> 100 Days in Store)

Double-click the Delete Old Items (> 100 Days in Store) button and implement the event as follows:

private void btnDeleteOldItems_Click(object sender, EventArgs e)
{
    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                   "Database='FunDS25';Integrated Security=True;"))
    {
        // Every item that has been in the store for more than 100 days, it will be added (copied) to the Unsold Items table.
        SqlCommand cmdStoreItems = new SqlCommand("INSERT INTO Inventory.UnsoldItems(ItemNumber, DateEnteredInStore, DateRemovedFromStore, " +
                                                  "                                  DaysInStore, Manufacturer, Category, " +
                                                  "                                  SubCategory, ItemName, ItemSize, UnitPrice) " +
                                                  "SELECT ItemNumber, DateEntered, SYSDATETIME(), " +
                                                  "       DaysInStore, Manufacturer, Category, SubCategory, ItemName, ItemSize, UnitPrice " +
                                                  "FROM Inventory.StoreItems " +
                                                  "WHERE Inventory.StoreItems.DaysInStore > 100;",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();
    }

    // The following code species the current date as the date the "old" items were removed from the store
    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                                       "Database='FunDS25';Integrated Security=True;"))
    {
        SqlCommand cmdStoreItems = new SqlCommand("UPDATE Inventory.UnsoldItems " +
                                                  "SET DateRemovedFromStore = SYSDATETIME() " +
                                                  "WHERE DateRemovedFromStore IS NULL;",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();
    }

    using (SqlConnection cntFunDS = new SqlConnection("Data Source=(local);" +
                                   "Database='FunDS25';Integrated Security=True;"))
    {
        // Every item that has been in the store for more than 100 days will be deleted.
        SqlCommand cmdStoreItems = new SqlCommand("DELETE FROM Inventory.StoreItems " +
                                                  "WHERE DaysInStore > 100;",
                                                  cntFunDS);

        cntFunDS.Open();
        cmdStoreItems.ExecuteNonQuery();
    }
        
    ShowInventory();
}

Display the Point of Sale form and double-click the Submit button

Implement the event as follows:

private void btnSubmit_Click(object sender, EventArgs e)
{
    string strSaleItems = "";
    string strShoppingSessions = "";

    if (lvwSelectedItems.Items.Count == 0)
    {
        MessageBox.Show("There is no customer order to save.",
                        "FunDS", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }
    else if (string.IsNullOrEmpty(txtEmployeeNumber.Text))
    {
        MessageBox.Show("You must specify the employee (clerk) who processed the order.",
                        "FunDS", MessageBoxButtons.OK, MessageBoxIcon.Information);
        txtEmployeeNumber.Focus();
        return;
    }
    else
    {
        strShoppingSessions = "INSERT INTO Transactions.ShoppingSessions" +
                              "(ReceiptNumber, EmployeeNumber, ShoppingDate, ShoppingTime, SaleTotal)" +
                              "VALUES(" + txtReceiptNumber.Text + ", N'" + txtEmployeeNumber.Text + "', N'" +
                              dtpSaleDate.Value.ToShortDateString() + "', N'" +
                              dtpSaleTime.Value.ToShortTimeString() + "', " +
                              double.Parse(txtOrderTotal.Text) + ");";

        strSaleItems = "INSERT INTO Inventory.SaleItems" +
                       "(ReceiptNumber, ItemNumber, ItemName, ItemSize, UnitPrice, DiscountRate, DiscountAmount, SalePrice) VALUES";

        foreach (ListViewItem lviStoreItem in lvwSelectedItems.Items)
        {
            strSaleItems += "(" +
                            txtReceiptNumber.Text + ", N'" +
                            lviStoreItem.SubItems[0].Text + "', N'" +
                            lviStoreItem.SubItems[1].Text + "', N'" +
                            lviStoreItem.SubItems[2].Text + "', " +
                            lviStoreItem.SubItems[3].Text + ", " +
                            (double.Parse(lviStoreItem.SubItems[4].Text.Replace("%", "")) / 100).ToString("F") + ", " +
                            lviStoreItem.SubItems[5].Text + ", " +
                            lviStoreItem.SubItems[6].Text + ")";
        }
        strSaleItems = strSaleItems.Replace(")(", "),(") + ";";

        // Create a shopping session (customer order) using the information on the form except the items in the list view.
        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                         "Database='FunDS1';" +
                                                         "Integrated Security=Yes;"))
        {
            SqlCommand cmdShoppingSessions = new SqlCommand(strShoppingSessions, scFunDS);
            scFunDS.Open();
            cmdShoppingSessions.ExecuteNonQuery();
        }

        // Add the items of the list view in the SaleItems table
        using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                         "Database='FunDS1';" +
                                                         "Integrated Security=Yes;"))
        {
            SqlCommand cmdSaleItems = new SqlCommand(strSaleItems, scFunDS);
            scFunDS.Open();
            cmdSaleItems.ExecuteNonQuery();
        }

        // Copy each item of the list view to the SoldItems table
        foreach (ListViewItem lviStoreItem in lvwSelectedItems.Items)
        {
            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                              "Database='FunDS1';" +
                                                              "Integrated Security=Yes;"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("INSERT INTO Inventory.SoldItems(ReceiptNumber, ItemNumber, DateEnteredInStore, SaleDate, " +
                                   "                                Manufacturer, Category, SubCategory, ItemName, ItemSize, " +
                                   "                                DaysInStore, UnitPrice, DiscountRate, DiscountAmount, SalePrice) " +
                                   "SELECT " + txtReceiptNumber.Text + ", ItemNumber, DateEntered, N'" + dtpSaleDate.Value.ToShortDateString() + "', " +
                                   "       Manufacturer, Category, SubCategory, ItemName, ItemSize, DaysInStore, UnitPrice, " +
                                   "       DiscountRate, DiscountAmount, MarkedPrice " +
                                   "FROM Inventory.StoreItems " +
                                   "WHERE ItemNumber = N'" + lviStoreItem.SubItems[0].Text + "';",
                                   scFunDS);

                scFunDS.Open();
                cmdEmployees.ExecuteNonQuery();
            }
        }

        // Remove the items of the list view from the StoreItems table
        foreach (ListViewItem lviStoreItem in lvwSelectedItems.Items)
        {
            using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
                                                             "Database='FunDS1';" +
                                                             "Integrated Security=Yes;"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("DELETE FROM Inventory.StoreItems " +
                                   "WHERE ItemNumber = N'" + lviStoreItem.SubItems[0].Text + "';",
                                   scFunDS);
                scFunDS.Open();
                cmdEmployees.ExecuteNonQuery();
            }
        }

        MessageBox.Show("The customer's order has been saved.",
                        "FunDS", MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
        btnReset_Click(sender, e);
    }
}

Return to the Point of Sale form and double-click the Close button

Implement the event as follows:

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

Execute the application and click the Show Inventory button.
Scroll down in the list view to see more items and how long they have been in the store
After seeing the existing records, click the Delete Old Items (> 100 Days in Store) button.
Scroll down in the list and notice that there is no more item that has a Days in Store value over 100
Close the Store Inventory form
Open the Unsold Items form to see the items that have been added (it would be interesting to run the application again after one or two months and perform the operations, or you can change the time on your computer and perform the operations)
Close the forms and return to your programming environment

Conditionally Removing the First n Records

Consider the following table:

CREATE TABLE Employees
(
	EmployeeNumber int,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(283007, N'Megan',    N'Wright',    8.50),
      (480295, N'Horace',   N'Taylor',   20.25),
      (804805, N'Marc',     N'Knights',  10.85),
      (294117, N'Bryan',    N'Tenant',   30.25),
      (837479, N'Paul',     N'Motto',    18.02),
      (280304, N'Joyce',    N'Holliday', 11.66),
      (924802, N'Peter',    N'Mukoko',    8.68),
      (725381, N'Marianne', N'Brooks',   22.64);
GO

By default, the DELETE expression acts on all records of a table. As an alternative, you can ask the database engine to consider only the first n records of a table. The formula to do this is:

DELETE TOP (Number) FROM TableName
WHERE Condition(s)

In the parentheses after TOP, enter the desired number of records. When the statement executes, the WHERE condition would be applied on only the first Number of records. Any record that falls in that condition would be deleted. Here is an example:

DELETE TOP (4) FROM Employees
WHERE HourlySalary < 12.50;
GO

This code asks the database engine to delete any record in the first four records of the Employees table if that hourly salary of the employee in less than 12.50.

Conditionally Removing the First Percentage of Records

If you don't want to specify a fixed number of records, you can use a percentage instead. The formula to follow is:

DELETE TOP (Number) PERCENT FROM TableName
WHERE Condition(s)

In the parentheses, enter a number between 0.00 and 100.00 included. The number of records to consider is based on the total number of the records using a percentage. Here is an example:

DELETE TOP (40) PERCENT FROM Employees
WHERE HourlySalary < 12.50;
GO

This code delete any record whose salary is less than 12.50 but the record must be among the first 40% of the records.

Outputting the Deleted Results

When some record(s) has(have) been deleted, the operation is performed behind the scenes and you don't see the result. If you want to see a list of the records that were deleted, you can use the OUTPUT operator to display the result. To show the list of the records from a table that was completely emptied, you can use the following formula:

DELETE FROM TableName
OUTPUT DELETED.Columns

The OUTPUT INSERTED expression follows the description we have seen for the record update. Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
GO

To show the list of the records that were deleted based on a condition, use the following formula:

DELETE FROM TableName
OUTPUT DELETED.Columns
WHERE Condition(s)

Here is an example:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
WHERE YearReleased IS NULL;
GO

Deleting Many Records

Instead of one, you can delete more than one record at a time. To programmatically delete a group or records, apply the DELETE FROM table formula and use a WHERE condition that can identify each one of the records.


Home	Copyright © 2014-2022, FunctionX	Home