Microsoft Visual C# Data Sets: Records Maintenance on a Data Set

	Records Maintenance on a Data Set

Fundamentals of Record Maintenance

Introduction

Record maintenance consists of locating a value or a record, changing a value or a record, deleting a value or a record, and changing a value in a record. To assist you with this, the various classes of the System.Data namespace are equipped with many useful methods. We will review the most regularly used ones.

Consider the following:

The Status of a Record

When a change has been made to a record, the compiler would like that record to hold a flag that indicates the type of change it has just received. This is referred to as the row state of a record. To support the flags, the System.Data namespace provides an enumeration named DataRowState. To apply this flag to a record, the DataRow class is equipped with a property named RowState.

Making Copies

Imagine you have a table in a data set and the table has the type of structure you want, including the values. You can copy the content of that table and replicate it into a table of your data set. To support this operation, you can call the Copy() method of the DataTable class. Its syntax is:

public DataTable Copy();

In the same way, you can copy various tables from a data set and replicate them in your data set. If you want to copy all tables from an existing data set into your data set, you can call the Copy() method from the DataSet class. Its syntax is:

public DataSet Copy();

Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataSet dsSchool = dsStudents.Copy();
    dgvStudents.DataSource = dsSchool;
    dgvStudents.DataMember = dsSchool.Tables[0].TableName;
}

Creating a Record

We have already learned different techniques of creating records for a table. Besides those, the DataRowCollection provides a method named InsertAt. Its syntax is:

public void InsertAt(DataRow row, int pos);

The first argument is the collection of values to be added as the new record of the table. The pos argument is the position that the new record should assume in the table. If the position is set to 0, the record would be added as the first. Here are examples:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = tblStudent.NewRow();

    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, 0);

    Record = tblStudent.NewRow();

    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 0);

    Record = tblStudent.NewRow();

    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 0);
    
    Record = tblStudent.NewRow();

    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 0);
}

This would produce:

Students

If the position is equal to or higher than the total number of records (DataRowCollection.Count), the new record would be added as the last record to the table (in both cases, the compiler would not throw an exception). Here are examples:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = tblStudent.NewRow();

    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, 0);

    Record = tblStudent.NewRow();

    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 0);

    Record = tblStudent.NewRow();

    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    // Add this as the last record
    tblStudent.Rows.InsertAt(Record, 100);

    Record = tblStudent.NewRow();

    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    // Add this as the last record
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
}

Otherwise, you can insert a record between two existing records. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;

    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, 0);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 1);

    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, 2);

    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    // Add the new record in the second position
    tblStudent.Rows.InsertAt(Record, 1);
}

This would produce:

Students

There are restrictions to this technique. If the table does not have a primary key whose value is incremental (where a DataColumn.AutoIncrement is set to true), if you specify a position between 0 and the maximum number of records, the new record can be inserted between two existing records. If the table has an DataColumn.AutoIncrement primary key as a column, the record would always be added as the last record.

When a new record has just been created, the compiler changes the status of that record using an appropriate member of the DataRowState enumeration. In this case, the record would be flagged as RowState.Added. This is done automatically, to find out whether a certain record currently has the RowState.Added value, you can check the value of its RowState property.

After creating a new record, to flag it as a new record, you can call the SetAdded() method of the DataRow class. Its syntax is:

public void SetAdded();

Editing a Record

As seen in Lesson 16, editing a record consists of locating the record, identifying the value that needs to be changed and then changing it. You can perform this operation on one record at a time or on many records. If there are many records to be edited and you do not want a record to be used for anything else while that record is being updated, you can ask the compiler to suspend the activities on that record while the updating operation is going on. Before a record is changed, its status is set to DataRowState.Unchanged value.

To signal to the compiler that you are about to edit a record, the DataRow class is equipped with a method named BeginEdit. Its syntax is:

public void BeginEdit();

When this method is called, the compiler puts everything on hold on the record on which this method is called. After calling it, you can perform the desired changes.

After performing the changes, to resume, you should call the EndEdit() method of the DataRow class. Its syntax is:

public void EndEdit();

Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;

    // Create the first record, its index is 0
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    // Create the second record, its index is 1
    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
            
    // Create the third record, its index is 2
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    // Create the fourth record, its index is 3
    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    // Edit the second record, whose index is 2
    // Let the compiler know that you are starting to edit the record
    tblStudent.Rows[2].BeginEdit();
    tblStudent.Rows[2][1] = "Georgette";
    tblStudent.Rows[2][3] = "Female";
    tblStudent.Rows[2].EndEdit();
}

This would produce:

Students

Notice that the third record has values different than the original. While the changes are going on, if you find out that there is a problem and the record should not be updated, you can cancel the change. To support this, the DataRow class is equipped with a method named CancelEdit. Its syntax is:

public void CancelEdit();

When this method is called, the compiler would dismiss the current change that is being performed on the record. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = tblStudent.NewRow();

    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();

    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
            
    Record = tblStudent.NewRow();

    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();

    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    tblStudent.Rows[2].BeginEdit();
    tblStudent.Rows[2][1] = "Georgette";
    tblStudent.Rows[2][3] = "Female";
    tblStudent.Rows[2].BeginEdit();

    tblStudent.Rows[1].BeginEdit();
    tblStudent.Rows[1][1] = "Orlando";
    tblStudent.Rows[1].CancelEdit();
    tblStudent.Rows[1][2] = "Khan";
    tblStudent.Rows[1].BeginEdit();
}

This would produce:

Students

When a record has been changed, the compiler changes its flag. In this case, it would be flagged as DataRowState.Modified. If you want to manually set this flag, you can call the SetModified() method of the DataRow class. Its syntax is:

public void SetModified();

This method should be called only if the record was previously set to DataRowState.Added or DataRowState.Unchanged.

Accepting or Rejecting Record Changes

The operations you perform on records, such as adding a new record, adding a series of records, deleting a record, deleting a group or records, or deleting all records, are referred to as changes. It is not unusual to find out that a change that is about to be made is invalid. After preparing a change but before committing it, if you know the change is right, you can ask the compiler to accept it. To support this, the DataRow class is equipped with a method named AcceptChanges. Its syntax is:

public void AcceptChanges()

This method allows you to ask the compiler to validate the changes that are about to be made on a record. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;
            
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.Rows[1].AcceptChanges();
            
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

}

On the other hand, if you find out that the change that is about to occur on a record should not be validated, you can cancel it. To support this, the DataRow class is equipped with a method named RejectChanges. Its syntax is:

public void RejectChanges();

Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;
            
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    // Dismiss the changes made to the second record
    tblStudent.Rows[1].RejectChanges();
           
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
}

This would produce:

Accept or Reject Changes

Notice that, although 4 records were created, one was rejected. After editing a record and indicating that you have accepted the changes, the record's status can receive a new status such as DataRowState.Added or DataRowState.Modified. This means that you can then call either the DataRow.SetAdded() or the DataRow.Setmodified() method. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;
            
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
            
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.Rows[2].AcceptChanges();

    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    tblStudent.Rows[2].BeginEdit();
    tblStudent.Rows[2][1] = "Georgette";
    tblStudent.Rows[2][3] = "Female";
    tblStudent.Rows[2].BeginEdit();
    tblStudent.Rows[2].SetModified();
}

To validate changes at the table level, the DataTable class is equipped with a method named AcceptChanges. Its syntax is:

public void AcceptChanges();

This method is used to validate changes that are about to be made on a table. To let you dismiss changes on a table level, the DataTable class is equipped with a method named RejectChanges. Its syntax is:

public void RejectChanges();

Here are examples of calling the DataTable.AcceptChange() and the DataTable.RejectChanges() methods:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = tblStudent.NewRow();

    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.AcceptChanges();

    Record = tblStudent.NewRow();

    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.AcceptChanges();

    Record = tblStudent.NewRow();

    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.RejectChanges();

    Record = tblStudent.NewRow();

    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    tblStudent.AcceptChanges();
}

To validate changes made on a DataSet object, you can call its own AcceptChanges() method. To dismiss changes made on a DataSet object, you can call its RejectChanges() method.

The Nullity of a Value

As we know already, a record can contain one or more values. Each value is identified by the column it belongs to. A column is said to be null if it does not have a value. Either you or the user of your database can set the value of a column to be null. The easiest way for the user is to skip a column when performing data entry. Here is an example:

Students Records

Notice that the First Name column of the 5th record is null (left empty). Instead of the user, you too can leave a column empty during data entry. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;
            
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
//  Record[2] = "Kouma";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
            
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[1] = "Alain";
    Record[2] = "Paulson";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
    
    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
}

This would produce:

Students Records

Notice that the Last Name column of the second record was left empty.

On an existing record, to find out whether the value of a column is null, you can call the IsNull() method of the DataRow class. It comes in various versions. To specify the column whose value you want to check, you can pass the object name, the index, or the variable name of the column as argument. Here are examples:

private void Exercise_Load(object sender, EventArgs e)
{
    DataRow Record = null;
            
    Record = tblStudent.NewRow();
    Record[0] = "255084";
    Record[1] = "Gertrude";
    Record[2] = "Monay";
    Record[3] = "Female";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "947225";
    Record[1] = "Raymond";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);
            
    Record = tblStudent.NewRow();
    Record[0] = "735395";
    Record[2] = "Paulson";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    Record = tblStudent.NewRow();
    Record[0] = "293744";
    Record[1] = "Robert";
    Record[2] = "Bidoula";
    Record[3] = "Male";
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count);

    if (tblStudent.Rows[0].IsNull("Last Name"))
        MessageBox.Show("The last name of the first record is null.");
    else
        MessageBox.Show("The last name of the first record is " +
                        "\"" + tblStudent.Rows[0][2].ToString() + "\".");

    if (tblStudent.Rows[1].IsNull(2))
        MessageBox.Show("The last name of the second record is null.");
    else
        MessageBox.Show("The last name of the second record is " +
                        "\"" + tblStudent.Rows[1]["Last Name"].ToString() + ".");
}

This would produce:

Students

Students Records

Locating a Record

Introduction

Most of the operations you want to perform on a record require that you identify the record you want to work on. In Lesson 16, we saw different techniques of locating a record. Additional techniques allow you to find a record or to check the existence of a certain record in a table. Consider an application with the following forms:

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

namespace Exercise4
{
    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 Record = tblStudent.NewRow();

                Record[0] = Editor.txtStudentNumber.Text;
                Record[1] = Editor.txtFirstName.Text;
                Record[2] = Editor.txtLastName.Text;
                Record[3] = Editor.dtpDateOfBirth.Value.ToString("d");
                Record[4] = Editor.cbxGenders.Text;

                tblStudent.Rows.Add(Record);
                dsStudents.WriteXml("students.xml");

                lvwStudents.Items.Clear();

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

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

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

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

            if (File.Exists(Filename))
            {
                dsStudents.ReadXml(Filename);

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

                    ListViewItem lviStudent =
                    new ListViewItem(StudentRecord[0].ToString());
                    lviStudent.SubItems.Add(StudentRecord[1].ToString());
                    lviStudent.SubItems.Add(StudentRecord[2].ToString());
                    lviStudent.SubItems.Add(StudentRecord[3].ToString());
                    lviStudent.SubItems.Add(StudentRecord[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. Here is an example:

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

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
Before editing a record, make the user aware of the existing values. You can do this by displaying the records of the database
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
Use that value to locate the particular record that needs to be changed

To perform these steps, you use a combination of the techniques we have 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();
}

Finding a Record

The techniques we studied in Lesson 16 to locate a record should work in any table but they can be complex on a large table with many columns and various records. For example, you must make sure you can uniquely identify each record. This can be difficult because one column would not be enough and you may need to use a combination of columns just to isolate one particular record. In the previous lesson, we saw that the use of a primary key in a table makes it possible to have a unique value that can be used to identify each particular record.

Consider the following:

Unique Primary Key

On a table that has a primary key, to assist you with finding a record, the DataRowCollection class provides a method named Find that is overloaded with two versions. One of the versions uses the following syntax:

public DataRow Find(Object key);

This method expects a mechanism to find a record. The argument should be the value of a primary key. Here are two examples used to find a record using a primary key:

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,
    // find the record that uses the selected student number as 
    // its primary key.
    // Show the values of that record in the dialog box
    DataRow SelectedStudent = tblStudent.Rows.Find(lvwStudents.SelectedItems[0].SubItems[0].Text);
    Editor.txtStudentNumber.Text = SelectedStudent[0].ToString();
    Editor.txtFirstName.Text = SelectedStudent[1].ToString();
    Editor.txtLastName.Text = SelectedStudent[2].ToString();
    Editor.dtpDateOfBirth.Value = DateTime.Parse(SelectedStudent[3].ToString());
    Editor.cbxGenders.Text = SelectedStudent[4].ToString();
    Editor.txtStudentNumber.Enabled = false;

    // Display the dialog box
    // If the user makes changes and clicks OK
    if (Editor.ShowDialog() == DialogResult.OK)
    {
        // Find the record that uses that student number
        // as its primary key and update it (the record)
        SelectedStudent = tblStudent.Rows.Find(Editor.txtStudentNumber.Text);

        SelectedStudent[1] = Editor.txtFirstName.Text;
        SelectedStudent[2] = Editor.txtLastName.Text;
        SelectedStudent[3] = Editor.dtpDateOfBirth.Value.ToString("d");
        SelectedStudent[4] = Editor.cbxGenders.Text;

        dsStudents.WriteXml("students.xml");

        lvwStudents.Items.Clear();

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

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

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

Checking the Existence of a Record

On a typical table, you may want to find out whether it contains a certain record. To assist you with this, the DataRowCollection class is equipped with a method named Contains and that is overloaded with two versions. One of the versions uses the following syntax:

public bool Contains(Object key);

This method expects a value that should be a primary key of the table that holds the records. 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;

    if (tblStudent.Rows.Contains(lvwStudents.SelectedItems[0].SubItems[0].Text))
        MessageBox.Show("This record exists in the data table.");
}

Deleting Records

Deleting the Current Row

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 StudentRecord in tblStudent.Rows)
        {
            // Look for the record that has the student number that was selected
            // If you find such a record
            if (StudentRecord["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
                    StudentRecord.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 StudentRecord = tblStudent.Rows[i];

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

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

Removing a Row From a Collection of Records

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 StudentRecord in tblStudent.Rows)
        {
            // Create a record that uses the values of the selected student
            StudentRecord["StudentNumber"] = lvwStudents.SelectedItems[0].SubItems[0].Text;
            StudentRecord["FirstName"]     = lvwStudents.SelectedItems[0].SubItems[1].Text;
            StudentRecord["LastName"]      = lvwStudents.SelectedItems[0].SubItems[2].Text;
            StudentRecord["DateOfBirth"]   = lvwStudents.SelectedItems[0].SubItems[3].Text;
            StudentRecord["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(StudentRecord);
                // 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 StudentRecord = tblStudent.Rows[i];

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

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

Deleting a Record by its Index

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.

Deleting all Records From a Table

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. Here is an example:

private void btnClear_Click(object sender, EventArgs e)
{
    tblStudent.Rows.Clear();
}

Deleting all Records From all Tables

If you have many tables in a data set and you want to delete all records in all tables, you can call the Clear() method of the DataSet class. Its syntax is:

public void Clear();

Here is an example:

private void btnClear_Click(object sender, EventArgs e)
{
    dsStudents.Clear();
}