Home

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 LearningPractical Learning: Introducing Record Maintenance

  1. Start Microsoft Visual Studio
  2. Create a new Windows Forms Application named FunDS1 (which stands for Fun Department Store)
  3. In the Solution Explorer, right-click Form1.cs and click Rename
  4. Type FunDS and press Enter twice
  5. Double-click the body of the form
  6. 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();
            }
        }
    }
  7. From the resources that accompany these lessons, open the FunDS HTML file and select everything in it
  8. Paste it by replacing the // Here We Go line above
  9. Execute the application and click OK on the message boxes
  10. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  11. Set the Name to Manufacturer and click Add
  12. Complete the design of the form as follows:
     

    FunDS: New Manufacturer

    Control Text Name Other Properties
    Label Label Manufacturer:    
    TextBox Text Box   txtManufacturer Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
  13. 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
  14. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  15. Set the Name to Manufacturers and click Add
  16. Add a list view to the form and change its properties as follows:
  17. Complete the design of the form as follows:
     

    FunDS: Manufacturers

    Control Text Name Other Properties
    ListView List View
    (Name) Text TextAlign Width
    colManufacturer Manufacturer   160
     lvwEmployees
    FullRowSelect: True
    GridLines: True
    View: Details
    Button Button New Manufacturer ... btnNewManufacturer  
    Button Button Close btnClose  
  18. Double-click an unoccupied area of the form
  19. Return to the Manufacturers form and double-click the New Manufacturer button
  20. Return to the Manufacturers form and double-click the Close button
  21. 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();
            }
        }
    }
  22. Return to the Employees form and double-click the New Employee button
  23. 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();
        }
    }
  24. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  25. Set the Name to Category and click Add
  26. Complete the design of the form as follows:
     

    FunDS: New Category

    Control Text Name Other Properties
    Label Label Category:    
    TextBox Text Box   txtCategory Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
  27. 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
  28. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  29. Set the Name to Categories and click Add
  30. Add a list view to the form and create a column as follows:
    (Name) Text TextAlign Width
    colCategory Category   160

  31. Complete the design of the form as follows:
     

    FunDS: Categories

    Control Text Name Other Properties
    ListView List View    lvwEmployees
    FullRowSelect: True
    GridLines: True
    View: Details
    Button Button New Category ... btnNewCategory  
    Button Button Close btnClose  
  32. Double-click an unoccupied area of the form
  33. Return to the Categories form and double-click the New Category button
  34. Return to the Categories form and double-click the Close button
  35. 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();
            }
        }
    }
  36. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  37. Set the Name to SubCategory and click Add
  38. Complete the design of the form as follows:
     

    FunDS: New Category

    Control Text Name Other Properties
    Label Label Sub-Category:    
    TextBox Text Box   txtSubCategory Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
  39. 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
  40. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  41. Set the Name to SubCategories and click Add
  42. Add a list view to the form and create a column as follows:
    (Name) Text TextAlign Width
    colSubCategory Sub-Category   160
  43. Complete the design of the form as follows:
     

    FunDS: Sub-Categories

    Control Text Name Other Properties
    ListView List View    lvwEmployees
    FullRowSelect: True
    GridLines: True
    View: Details
    Button Button New Sub-Category ... btnNewSubCategory  
    Button Button Close btnClose  
  44. Double-click an unoccupied area of the form
  45. Return to the SubCategories form and double-click the New Sub-Category button
  46. Return to the SubCategories form and double-click the Close button
  47. 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();
            }
        }
    }
  48. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  49. Set the Name to StoreItem and click Add
  50. Complete the design of the form as follows:
     

    FunDS: New Store Item

    Control Text Name Other Properties
    Label Label Item Number:    
    TextBox Text Box   txtItemNumber Modifiers: Public
    Label Label Date Entered:    
    DateTimePicker Date Time Picker   dtpDateEntered Modifiers: Public
    Label Label Manufacturer:    
    ComboBox Combo Box   cbxManufacturers DropDownList: DropDownList
    Modifiers: Public
    Button Button New ... btnNewManufacturer  
    Label Label Category:    
    ComboBox Combo Box   cbxCategories DropDownList: DropDownList
    Modifiers: Public
    Button Button New ... btnNewCategory  
    Label Label SubCategory:    
    ComboBox Combo Box   cbxSubCategories DropDownList: DropDownList
    Modifiers: Public
    Button Button New ... btnNewSubCategory  
    Label Label Item Name:    
    TextBox Text Box   txtItemName Modifiers: Public
    Label Label Item Size:    
    TextBox Text Box   txtItemSize Modifiers: Public
    Label Label Unit Price:    
    TextBox Text Box 0.00 txtUnitPrice Modifiers: Public
    TextAlign: Right
    Label Label Discount Rate:    
    TextBox Text Box 0.00 txtDiscountRate Modifiers: Public
    TextAlign: Right
    Label Label %    
    Button Button Submit btnSubmit  
    Button Button Reset btnReset  
    Button Button Close btnClose  
  51. 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
  52. 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();
            }
        }
    }
  53. Return to the Store Item form and double-click the top New button
  54. 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());
        }
    }
  55. Return to the Store Item form and double-click the second New button
  56. 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);
                    }
                }
            }
        }
    }
  57. Return to the Store Item form and double-click the bottom New button
  58. 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);
                    }
                }
            }
        }
    }
  59. Return to the Store Item form and double-click the Reset button
  60. Implement the event as follows:
    private void btnReset_Click(object sender, EventArgs e)
    {
        PopulateStoreItem();
    }
  61. Return to the Store Item form and double-click the Submit button
  62. 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();
    }
  63. Return to the Store Item form and double-click the Close button
  64. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  65. Display the FunDS form and add a button to it
  66. Change the button's properties as follows:
    (Name): btnNewStoreItem
    Text:      New Store Item ...
  67. 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();
    }
  68. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  69. Set the Name to Employee and click Add
  70. Complete the design of the form as follows:
     

    FunDS: New employee

    Control Text Name DialogResult
    Label Label First Name:    
    TextBox Text Box   txtFirstName  
    Label Label Last Name:    
    TextBox Text Box   txtLastName  
    Label Label Title:    
    TextBox Text Box   txtTitle  
    Label Label Hourly Salary:    
    TextBox Text Box   txtHourlySalary  
    Label Label Employee #:    
    TextBox Text Box   txtEmployeeNumber  
    Label Label Username:    
    TextBox Text Box   txtUsername  
    Label Label Password:    
    TextBox Text Box   txtPassword  
    Button Button OK btnOK OK
    Button Button Cancel btnCancel Cancel
  71. 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
  72. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  73. Set the Name to Employees and click Add
  74. Complete the design of the form as follows:
     

    FunDS: Employees

    Control Text Name Other Properties
    ListView List View
    (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 Label Number of employees    
    TextBox Text Box   txtNumberOfEmployees TextAlign: Right
    Button Button New Employee ... btnNewEmployee  
    Button Button Close btnClose  
  75. Double-click an unoccupied area of the form
  76. 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();
            }
        }
    }
  77. Return to the Employees form and double-click the New Employee button
  78. 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();
        }
    }
  79. Display the FunDS form and add a button to it
  80. Change the properties of the button as follows:
    (Name): btnEmployees
    Text: Employees ...
  81. 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();
    }
  82. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  83. Set the Name to StoreInventory and click Add
  84. Add a list view to the form and change its properties as follows:
    (Name): lvwStoreItems
    FullRowSelect: True
    GridLines: True
    View: Details
  85. 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
  86. Complete the design of the form as follows:
     

    FunDS: Store Items

    Control Text Name Other Properties
    ListView List View  
    Label Label Number of Records:    
    TextBox Text Box   txtNumberOfRecords TextAlign: Right
    Button Button New Store Item ... btnNewStoreItem  
    Button Button Close btnClose  
  87. Double-click an unoccupied area of the form
  88. 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();
            }
        }
    }
  89. Return to the form and double-click the New Store Item
  90. Implement the event as follows:
    private void btnNewStoreItem_Click(object sender, EventArgs e)
    {
        StoreItem si = new StoreItem();
        si.Show();
    }
  91. Display the FunDS form and add a button to it
  92. Change the properties of the button as follows:
    (Name): btnStoreInventory
    Text: Store Inventory ...
  93. 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();
    }
  94. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  95. Set the Name to SoldItems and click Add
  96. 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  
  97. Complete the design of the form as follows:
     

    FunDS: Store Items

    Control Text Name Other Properties
    ListView List View    lvwSoldItems
    FullRowSelect: True
    GridLines: True
    View: Details
    Label Label Number of Records    
    TextBox Text Box   txtNumberOfRecords TextAlign: Right
    Button Button Close btnClose  
  98. Double-click an unoccupied area of the form
  99. Return to the form and double-click the Close button
  100. 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();
            }
        }
    }
  101. Display the FunDS form and add a button to it
  102. Change the properties of the button as follows:
    (Name): btnSoldItems
    Text: Sold Items ...
  103. 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();
    }
  104. To add a new form, on the main menu, click PROJECT -> Add Windows Form...
  105. Set the Name to UnsoldItems and click Add
  106. Add a list view to the form and change its properties as follows:
  107. Complete the design of the form as follows:
     

    FunDS: Store Items

    Control Text Name Other Properties
    ListView List View
    (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 Label Number of Records    
    TextBox Text Box   txtNumberOfRecords TextAlign: Right
    Button Button Close btnClose  
  108. Double-click an unoccupied area of the form
  109. Return to the form and double-click the Close button
  110. 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();
            }
        }
    }
  111. Display the FunDS form and add a button to it
  112. Change the properties of the button as follows:
    (Name): btnUnsoldItems
    Text: Unsold Items ...
  113. 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();
    }
  114. To add a new form, on the main menu, click PROJECT -> Add Windows Form ...
  115. Set the name to PointOfSale and click Add
  116. Add a list view to the form
  117. 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
  118. Design the form as follows:
     

    FunDS - Point of Sale

    Control Text Name Other Properties
    Label Label Item # to Add:    
    TextBox Text Box   txtItemNumberAdd  
    Label Label Receipt #:    
    TextBox Text Box   txtReceiptNumber  
    ListView List View     FullRowSelect: True
    GridLines: True
    View: Details
    Label Label Item # to Remove:    
    TextBox Text Box   txtItemNumberRemove  
    Button Button Remove btnRemoveItem  
    Label Label Order Total:    
    TextBox Text Box   txtOrderTotal Text: 0.00
    TextAlign: Right
    Label Label Tendered:    
    TextBox Text Box   txtAmountTendered Text: 0.00
    TextAlign: Right
    Label Label Processed By:    
    TextBox Text Box   txtEmployeeNumber  
    TextBox Text Box   txtEmployeeName  
    Label Label Change:    
    TextBox Text Box   txtChange  
    DateTimePicker Date Time Picker   dtpSaleDate  
    DateTimePicker Date Time Picker   dtpSaleTime Format: Time
    Button Button Reset btnReset  
    Button Button Submit btnSubmit  
    Button Button Close btnClose  
  119. Click the Item # to Add text box
  120. In the Properties window, click the Events button Events
  121. 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");
                        }
                    }
                }
            }
        }
    }
  122. Return to the Point of Sale form and double-click the Remove button
  123. 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 = "";
        }
    }
  124. Return to the Point of Sale form and click the Tendered text box
  125. In the Events section of the Properties window, double-click KeyUp
  126. 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");
            }
        }
    }
  127. Return to the Point of Sale form and click the Processed By text box
  128. In the Events section of the Properties window, double-click KeyUp
  129. 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);
                    }
                }
            }
        }
    }
  130. Return to the Point of Sale form and double-click the Reset button
  131. 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";
    }
  132. Return to the Point of Sale form and double-click an unoccupied area of its body
  133. Implement the event as follows:
    private void PointOfSale_Load(object sender, EventArgs e)
    {
        btnReset_Click(sender, e);
    }
  134. Display the FunDS form and add a button to it
  135. Change the properties of the button as follows:
    (Name): btnPointOfSale
    Text: Point of Sale  ...

    FunDS

  136. 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 LearningPractical Learning: Updating all Records

  1. 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
  2. Change the characteristics of the button as follows:
    (Name): btnDaysInStore
    Text: Update Number of Days in Store
  3. 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();
        }
    }
  4. Execute the application and click the Show Inventory button
  5. After seeing the existing records, click the Number of Days in Store button
  6. 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)

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

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 LearningPractical Learning: Updating Some Records

  1. Display the Store Inventory form and add a button to it
  2. Change the characteristics of the button as follows:
    (Name): btnDiscountRates
    Text: Update the Discount Rates
  3. 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();
    }
  4. Execute the application and click the Show Inventory button
  5. After seeing the existing records, click the Update the Discount Rates button
  6. 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 LearningPractical Learning: Deleting Some Records

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

    FunDS: Store Inventory

  3. 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();
    }
  4. Display the Point of Sale form and double-click the Submit button
  5. 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);
        }
    }
  6. Return to the Point of Sale form and double-click the Close button
  7. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  8. 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
  9. 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
  10. Close the Store Inventory form
  11. 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)
  12. 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, FunctionX Home