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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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: