Editing Records

Introduction

Consider the following application:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace RedOakHighSchool1
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnNewStudent_Click(object sender, EventArgs e)
        {
            StudentEditor editor = new StudentEditor();

            if (editor.ShowDialog() == DialogResult.OK)
            {
                DataRow row = tblStudent.NewRow();

                row["StudentNumber"] = editor.txtStudentNumber.Text;
                row["FirstName"] = editor.txtFirstName.Text;
                row["LastName"] = editor.txtLastName.Text;
                row["DateOfBirth"] = editor.dtpDateOfBirth.Value.ToString("d");
                row["Gender"] = editor.cbxGenders.Text;

                tblStudent.Rows.Add(row);
                dsStudentsRecords.WriteXml("students.xml");

                lvwStudents.Items.Clear();
                
                for (int i = 0; i < tblStudent.Rows.Count; i++)
                {
                    DataRow rowStudent = tblStudent.Rows[i];

                    ListViewItem lviStudent =
                    new ListViewItem(rowStudent[0].ToString());
                    lviStudent.SubItems.Add(rowStudent[1].ToString());
                    lviStudent.SubItems.Add(rowStudent[2].ToString());
                    lviStudent.SubItems.Add(rowStudent[3].ToString());
                    lviStudent.SubItems.Add(rowStudent[4].ToString());

                    lvwStudents.Items.Add(lviStudent);
                }
            }
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            lvwStudents.Items.Clear();
            string strFilename = "students.xml";

            if (File.Exists(strFilename))
            {
                dsStudentsRecords.ReadXml(strFilename);

                for (int i = 0; i < tblStudent.Rows.Count; i++)
                {
                    DataRow rowStudent = tblStudent.Rows[i];

                    ListViewItem lviStudent =
                    new ListViewItem(rowStudent[0].ToString());
                    lviStudent.SubItems.Add(rowStudent[1].ToString());
                    lviStudent.SubItems.Add(rowStudent[2].ToString());
                    lviStudent.SubItems.Add(rowStudent[3].ToString());
                    lviStudent.SubItems.Add(rowStudent[4].ToString());

                    lvwStudents.Items.Add(lviStudent);
                }
            }
        }

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

Once a table has been filled with records, you can perform maintenance operations on it such as changing some records or removing others. Editing a record consists of changing one of the values of the record under a particular column.

If you are using a data grid view, to select a record, the user can click the row header, that is, the gray box on the left side of a record

There are various ways you can programmatically select a record. The general steps you can follow are:

1. Make sure the table has at least one column that can be used to uniquely identify each record. For example, when creating a table for employees, you can assign a unique number to each employee. The same would go for students. If you are creating a table for a collection of items, such as a book or a video collection, a commercial store that sells items such as auto parts, make sure each item has a certain value that is unique to it, such as a shelf number or a store number
2. Before editing a record, make the user aware of the existing values. You can do this by displaying the records of the database
3. Let the user specify a value of the unique column. For a table that contains employees information, you can ask the user to enter the employee number of the record to edit. The same would be for a book or video collection, a commercial store that sells items, etc
4. Use that value to locate the record. Then locate the particular that needs to be changed

To perform these steps, you use a combination of the techniques we has reviewed so far: locate the table, display the records, locate the record, locate the column, assign the value to the column of a record, save the table. Here is an example:

private void lvwStudents_DoubleClick(object sender, EventArgs e)
{
    // If no student ain't selected
    // or more than one student is selected,
    // don't do nothing. Know'm Sayin'?
    if( (lvwStudents.SelectedItems.Count == 0) ||
        (lvwStudents.SelectedItems.Count > 1))
         return;

    // Get a reference to the Student Editor dialog box
    StudentEditor editor = new StudentEditor();

    // Since a student is selected,
    // get its values and transfer them to the dialog box
    editor.txtStudentNumber.Text = lvwStudents.SelectedItems[0].SubItems[0].Text;
    editor.txtFirstName.Text = lvwStudents.SelectedItems[0].SubItems[1].Text;
    editor.txtLastName.Text = lvwStudents.SelectedItems[0].SubItems[2].Text;
    editor.dtpDateOfBirth.Value = 
	DateTime.Parse(lvwStudents.SelectedItems[0].SubItems[3].Text);
    editor.cbxGenders.Text = lvwStudents.SelectedItems[0].SubItems[4].Text;

    // Display the dialog box
    editor.ShowDialog();
}

We know that you can locate a value based on the object name of a column. Once you have identified a column for a record, you can assign the desired value. If you are using a data grid view, to edit a value, click it and specify its new value.

To programmatically edit a value, first locate its record, then locate its column, and assign the desired value to the column. Here is an example:

private void lvwStudents_DoubleClick(object sender, EventArgs e)
{
    // If no student ain't selected
    // or more than one student is selected,
    // don't do nothing
    if ((lvwStudents.SelectedItems.Count == 0) ||
        (lvwStudents.SelectedItems.Count > 1))
        return;

    // Get a reference to the Student Editor dialog box
    StudentEditor editor = new StudentEditor();

    // Since a student is selected,
    // get its values and transfer them to the dialog box
    editor.txtStudentNumber.Text = lvwStudents.SelectedItems[0].SubItems[0].Text;
    editor.txtFirstName.Text = lvwStudents.SelectedItems[0].SubItems[1].Text;
    editor.txtLastName.Text = lvwStudents.SelectedItems[0].SubItems[2].Text;
    editor.dtpDateOfBirth.Value = 
	DateTime.Parse(lvwStudents.SelectedItems[0].SubItems[3].Text);
    editor.cbxGenders.Text = lvwStudents.SelectedItems[0].SubItems[4].Text;

    // Display the dialog box
    // If the user makes changes and clicks OK
    if (editor.ShowDialog() == DialogResult.OK)
    {
        // Check each record in the Student table
        foreach (DataRow rowStudent in tblStudent.Rows)
        {
            // Look for the record that has the student number that was selected
            // If you find such a record
            if (rowStudent["StudentNumber"].ToString() == 
		editor.txtStudentNumber.Text)
            {
                // Update its values
                rowStudent["FirstName"] = editor.txtFirstName.Text;
                rowStudent["LastName"] = editor.txtLastName.Text;
                rowStudent["DateOfBirth"] = 
			editor.dtpDateOfBirth.Value.ToString("d");
                rowStudent["Gender"] = editor.cbxGenders.Text;

                dsStudentsRecords.WriteXml("students.xml");
            }
            // This code assumes that each record has a unique student number
        }

        lvwStudents.Items.Clear();

        for (int i = 0; i < tblStudent.Rows.Count; i++)
        {
            DataRow rowStudent = tblStudent.Rows[i];

            ListViewItem lviStudent =
	            new ListViewItem(rowStudent[0].ToString());
            lviStudent.SubItems.Add(rowStudent[1].ToString());
            lviStudent.SubItems.Add(rowStudent[2].ToString());
            lviStudent.SubItems.Add(rowStudent[3].ToString());
            lviStudent.SubItems.Add(rowStudent[4].ToString());

            lvwStudents.Items.Add(lviStudent);
        }
    }
}

We saw that another technique of recognizing a record was by using the index of each column applied to the DataRow object of the record. You can apply this concept to identify each column. Once you do, you can then assign the desired value.

If you have a record you don't need, you can remove it from your table. If you are using the data grid view, to visually delete a record, first select it by clicking its row header, and then press Delete.

To support the ability to remove a record, the DataRow class is equipped with a method named Delete. Its syntax is:

public void Delete();

To programmatically delete a record, first locate it by its index, get a DataRow reference to the record to be removed, and then call the Delete() method on it. Once again, you would need a way to uniquely identify a record.

Here is an example:

private void lvwStudents_KeyUp(object sender, KeyEventArgs e)
{
    // If no student ain't selected
    // or more than one student is selected,
    // don't do nothing
    if ((lvwStudents.SelectedItems.Count == 0) ||
        (lvwStudents.SelectedItems.Count > 1))
        return;

    // Since/while a student is selected,
    // find out if the user had pressed Delete
    if(e.KeyCode  == Keys.Delete )
    {
        // Check each record in the Student table
        foreach (DataRow rowStudent in tblStudent.Rows)
        {
            // Look for the record that has the student number that was selected
            // If you find such a record
            if (rowStudent["StudentNumber"].ToString() ==
		lvwStudents.SelectedItems[0].SubItems[0].Text)
            {
                // Verify that that the user really wants to delete the record
                if (MessageBox.Show("Do you want to delete this student's record?",
                                    "Students Records",
                                    MessageBoxButtons.YesNo,
                                    MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    // If/since the user wants to delete the record, do it
                    rowStudent.Delete();
                    // Save the records                        
                    dsStudentsRecords.WriteXml("students.xml");
                    // Get out
                    break;
                }
                // This code assumes that each record has a unique student number
            }
        }

        lvwStudents.Items.Clear();

        for (int i = 0; i < tblStudent.Rows.Count; i++)
        {
            DataRow rowStudent = tblStudent.Rows[i];

            ListViewItem lviStudent =
            new ListViewItem(rowStudent[0].ToString());
            lviStudent.SubItems.Add(rowStudent[1].ToString());
            lviStudent.SubItems.Add(rowStudent[2].ToString());
            lviStudent.SubItems.Add(rowStudent[3].ToString());
            lviStudent.SubItems.Add(rowStudent[4].ToString());

            lvwStudents.Items.Add(lviStudent);
        }
    }
}

Besides the DataRow class, the DataRowCollection class provides its own means of deleting a record from a table. To delete a record, you can call the DataRowCollection.Remove() method. Its syntax is:

public void Remove(DataRow row);

This method takes as argument a DataRow object and checks whether the table contains it. If that record exists, it gets deleted, including all of its entries for each column. Here is an example:

private void lvwStudents_KeyUp(object sender, KeyEventArgs e)
{
    // If no student ain't selected
    // or more than one student is selected,
    // don't do nothing
    if ((lvwStudents.SelectedItems.Count == 0) ||
        (lvwStudents.SelectedItems.Count > 1))
        return;

    // Since/while a student is selected,
    // find out if the user had pressed Delete
    if (e.KeyCode == Keys.Delete)
    {
        // Check each record in the Student table
        foreach (DataRow rowStudent in tblStudent.Rows)
        {
            // Create a record that uses the values of the selected student
            rowStudent["StudentNumber"] = 
		lvwStudents.SelectedItems[0].SubItems[0].Text;
            rowStudent["FirstName"] = 
		lvwStudents.SelectedItems[0].SubItems[1].Text;
            rowStudent["LastName"] = 
		lvwStudents.SelectedItems[0].SubItems[2].Text;
            rowStudent["DateOfBirth"] = 
		lvwStudents.SelectedItems[0].SubItems[3].Text;
            rowStudent["Gender"] = lvwStudents.SelectedItems[0].SubItems[4].Text;

            // Verify that that the user really wants to delete the record
            if (MessageBox.Show("Do you want to delete this student's record?",
                                "Students Records",
                                MessageBoxButtons.YesNo,
                                MessageBoxIcon.Question) == DialogResult.Yes)
            {
                // If/since the user wants to delete the record, do it
                tblStudent.Rows.Remove(rowStudent);
                // Save the records                        
                dsStudentsRecords.WriteXml("students.xml");
                // Get out
                break;
            }
            // This code assumes that each record has a unique student number
        }

        lvwStudents.Items.Clear();

        for (int i = 0; i < tblStudent.Rows.Count; i++)
        {
            DataRow rowStudent = tblStudent.Rows[i];

            ListViewItem lviStudent =
            new ListViewItem(rowStudent[0].ToString());
            lviStudent.SubItems.Add(rowStudent[1].ToString());
            lviStudent.SubItems.Add(rowStudent[2].ToString());
            lviStudent.SubItems.Add(rowStudent[3].ToString());
            lviStudent.SubItems.Add(rowStudent[4].ToString());

            lvwStudents.Items.Add(lviStudent);
        }
    }
}

When calling the DataRowCollection.Remove() method, you must pass an exact identification of the record. If you don't have that identification, you can delete a record based on its index. To do this, you would call the DataRowCollection.RemoveAt() method. Its syntax is:

public void RemoveAt(int index);

This method takes as argument the index of the record you want to delete. If a record with that index exists, it would be deleted.

To delete all records of a table, call the DataRowCollection.Clear() method. Its syntax is:

public void Clear();

This method is used to clear the table of all records.