Home

The Records of a Table

 

The Rows of a Table

 

Introduction to Records

In our description of tables, we saw that a table was made of one or various columns that represented some categories of data. Here is an example of a table with a few columns:

Shelf Number Title Director Length Year Rating
           
           

After creating such a table and its columns, you (actually the user) can enter values in the table to make it a valuable list. Filling up a table with values is referred to as data entry.

A Data Grid View for Data Entry

When creating an application, do deliver it to the user and make it ready for data entry, you can create one or more forms as we did in previous lessons. When it comes to a data set, the .NET Framework provides a control named data grid view.

The data grid view is a control that resembles a list view; that is, it is made of cells that hold values. One of the differences is that, when a data grid view is added to a form, it is empty and dark.

As opposed to a list view, instead of visually creating the columns and/or the items, you can specify the source of the records. This would equipped the data grid view with the necessary columns and the records in the data set.

To visually create a data grid view, from the Data section of the Toolbox, you can click DataGridView and click the form . To programmatically create a data grid view, declare a variable of type DataGridView, locate memory for it using the new operator, and add it to the Controls collection of its container. Here is an example

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;

public class Exercise : Form
{
    DataGridView dgvStudents;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        Controls.Add(dgvStudents);
    }
}

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

This would produce:

Data Grid View

A Data Source and a Data Member for the Data Grid View

After creating a data grid view, you can assign it a data source, such as a DataSet object. At design time, to specify the data source, of a data grid view, in the Properties window, select the object in the DataSource field. At run time, to specify a data source, assign the appropriate object to the DataSource property. Here is an example:

public class Exercise : Form
{
    DataGridView dgvStudents;

    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colGender;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";
        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        Controls.Add(dgvStudents);

        colFirstName = new DataColumn("First Name");
        colLastName = new DataColumn("Last Name");
        colGender = new DataColumn("Gender");

        tblRegistration = new DataTable("Student Registration");
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colGender);

        dsRedOakHighSchool = new DataSet("Red Oak High School");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        dgvStudents.DataSource = dsRedOakHighSchool;
    }
}

To find out what the data source of a data grid view is, get the value of its DataSource property.

After assigning a data source, you should assign a data member to the data grid view. To visually specify the data members, in the Properties window of the data grid view, after setting the data source, access the DataMember field and click it arrowed button. Then, in the list, select an object, such as a table of a data set.

To programmatically specify the data member, after assigning the appropriate variable to the DataSource property, assign the name of the list, such as the object name of a table, to the DataMember property of the DataGridView object. Here is an example:

void InitializeComponent()
{
    Text = "Students Records";
    dgvStudents = new DataGridView();
    dgvStudents.Location = new Point(12, 12);
    Controls.Add(dgvStudents);

    colFirstName = new DataColumn("First Name");
    colLastName = new DataColumn("Last Name");
    colGender = new DataColumn("Gender");

    tblRegistration = new DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    dsRedOakHighSchool = new DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    dgvStudents.DataSource = dsRedOakHighSchool;
    dgvStudents.DataMember = "Student Registration";
}

Once you have specified the data source and the data member, the columns would appear on the data grid view:

The Row of a Table

A record on a table is represented as a row (horizontal) of data. A row, or record, is an object based on the DataRow class.

To support the various records that belong to a table, the DataTable class is equipped with a property called Rows. The DataTable.Rows property is an object of the DataRowCollection class. The DataRowCollection class provides the necessary properties and methods you can use to create and manage the records of a table.

A record on a table is an object of type DataRow.

Introduction to Data Entry

When performing data entry and while doing it on a record, the record has a status that can be identified by the DataRow.RowState property which is a value based on the DataRowState enumeration.

Before adding a new record to a table, you must let the table know. This is done by calling the DataTable.NewRow() method. Its syntax is:

public DataRow NewRow();

The DataTable.NewRow() method returns a DataRow object. Here is an example of calling it:

void InitializeComponent()
{
      DataRow rowStudent = tblRegistration.NewRow();
}

Data Entry

 

Introduction

When you call the DataTable.NewRow() method, the record's status is DataRowState.Detached.

After calling the DataTable.NewRow() method, you can specify the value that the column would carry. To do this, you must specify the table's column whose value you want to provide. You can locate a column based on an index as we mentioned already that the columns of a table are stored in the DataTable.Columns property which is based on the DataColumnCollection class. An example would be rowStudent["First Name"], which specifies the column named First Name. After specifying the column, assign it the desired but appropriate value. Here are examples of assigning values to the columns of a table:

void InitializeComponent()
{
    DataRow rowStudent = tblRegistration.NewRow();

    rowStudent["First Name"] = "Pauline";
    rowStudent["Last Name"] = "Simms";
    rowStudent["Gender"] = "Female";
}

Each column can also be identified by its index in the table. 

Adding a Record to a Table

After specifying the value(s) of the column(s), you must add it (them) to the table. To do this, you must call the Add() method of the DataRowCollection class. This method is overloaded with two versions. One of the versions uses the following syntax:

public void Add(DataRow row);

This method expects the name of the record as argument, which would be the value returned by a previous call to the DataTable.NewRow() method. Here is an example:

public class Exercise : Form
{
    DataGridView dgvStudents;

    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colGender;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";
        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        Controls.Add(dgvStudents);

        colFirstName = new DataColumn("First Name");
        colLastName = new DataColumn("Last Name");
        colGender = new DataColumn("Gender");

        tblRegistration = new DataTable("Student Registration");
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colGender);

        dsRedOakHighSchool = new DataSet("Red Oak High School");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        DataRow rowStudent = tblRegistration.NewRow();
        rowStudent["First Name"] = "Pauline";
        rowStudent["Last Name"] = "Simms";
        rowStudent["Gender"] = "Female";

        tblRegistration.Rows.Add(rowStudent);

        dgvStudents.DataSource = dsRedOakHighSchool;
        dgvStudents.DataMember = "Student Registration";
    }
}

This would produce:

When the record has been added to the table, the record has a status of DataRowState.Added.

Adding an Array of Records

The above version of the DataRowCollection.Add() method means that you must identify each column before assigning a value to it. If you already know the sequence of columns and don't need to explicitly identify them, you can store all values in an array and simply add the array as a complete record. To support this, the DataRowCollection class provide another version of the .Add() method whose syntax is:

public virtual DataRow Add(object[] values);

Here is an example:

void InitializeComponent()
{
    . . . No Change

    DataRow rowStudent = tblRegistration.NewRow();
    rowStudent["First Name"] = "Pauline";
    rowStudent["Last Name"] = "Simms";
    rowStudent["Gender"] = "Female";
    tblRegistration.Rows.Add(rowStudent);

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);

    dgvStudents.DataSource = dsRedOakHighSchool;
    dgvStudents.DataMember = "Student Registration";
}

This would produce:

There is an alternative to this second version of the DataRowCollection.Add() method. As opposed to passing an array of values to the Add() method, you can first define an array, assign that array to a DataRow variable, then pass that DataRow object to the Add() method. To support this technique, the DataRow class is equipped with an ItemArray property that expects an array. Here is an example

void InitializeComponent()
{
    Text = "Students Records";
    dgvStudents = new DataGridView();
    dgvStudents.Location = new Point(12, 12);
    Controls.Add(dgvStudents);

    colFirstName = new DataColumn("First Name");
    colLastName = new DataColumn("Last Name");
    colGender = new DataColumn("Gender");

    tblRegistration = new DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    dsRedOakHighSchool = new DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    DataRow rowStudent = tblRegistration.NewRow();
    rowStudent["First Name"] = "Pauline";
    rowStudent["Last Name"] = "Simms";
    rowStudent["Gender"] = "Female";
    tblRegistration.Rows.Add(rowStudent);

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);

    object[] arrStudent = { "Geraldine", "Rodetsky", "Unknown" };

    rowStudent = tblRegistration.NewRow();
    rowStudent.ItemArray = arrStudent;

    tblRegistration.Rows.Add(rowStudent);

    dgvStudents.DataSource = dsRedOakHighSchool;
    dgvStudents.DataMember = "Student Registration";
}

This would produce:

After creating the records of a table, if a record contains invalid values, the DataRow.HasErrors property can help you identify them.

The Number of Records of a Table

After you have created a table and its columns but before adding any row, the number of the table's records is set to 0. Every time you add a new record, the number of records is incremented by 1. To get the number of records that a table contains, access the Count property of its DataRowCollection collection. The Count property is inherited from the InternalDataCollectionBase class, which is the parent of many collection classes.

Saving the Records of a Table

 

Introduction

When the application closes, unfortunately, all the information created while the application was running is lost. While the first goal of an application is to create one or more lists used to organize information, probably the essence of an information-based or a data-based application is to preserve information created when using the application and be able to retrieve that information the next time the application runs, without re-creating it.

Of course, there are various ways you can save the information created in an application. As the DataSet class is equipped with all the necessary features used to create and manage one or more lists of an application, it also provides a very high level of saving the information stored in its lists.

Saving a Data Set

Once a new record has been created or when the lists of the data set have been populated with information, you can save the changes and store them to a computer file. By default, the DataSet class is equipped to save its lists as XML. To support this, it is equipped with the WriteXml() method that is overloaded with various versions. One of the versions of this method uses the following syntax:

public void WriteXml(string fileName);

This method takes as argument the name of the new file or its path. When providing this argument, make sure you add the .xml extension to the file name. This method does two things: it checks the existence of the file and it saves it. If the file you provided is not found in the path, this method creates it and writes the record(s) to it. If the file exists already, this method opens it, finds its end, and appends the new data at the end. This makes the method very useful and friendly.

Here is an example of saving a data set using this method:

void InitializeComponent()
{
    . . . No Change

    tblRegistration.Rows.Add(rowStudent);

    dsRedOakHighSchool.WriteXml("students.xml");
}

If you want to control whether the file should be created from scratch, instead of passing the name of the file to this method, first create a stream using a Stream-derived class such as FileStream. This allows specifying the necessary options using the FileMode, FileAccess, and FileShare properties. Once the stream is ready, pass it to the WriteXml() method because it is also overloaded with the following syntax:

public void WriteXml(Stream stream);

Here is an example:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;

public class Exercise : Form
{
    DataGridView dgvStudents;

    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colGender;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        Controls.Add(dgvStudents);

        colFirstName = new DataColumn("First Name");
        colLastName = new DataColumn("Last Name");
        colGender = new DataColumn("Gender");

        tblRegistration = new DataTable("Student Registration");
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colGender);

        dsRedOakHighSchool = new DataSet("Red Oak High School");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        DataRow rowStudent = tblRegistration.NewRow();
        rowStudent["First Name"] = "Pauline";
        rowStudent["Last Name"] = "Simms";
        rowStudent["Gender"] = "Female";
        tblRegistration.Rows.Add(rowStudent);

        object[] arrRecord = { "Edward", "Zaks", "Male" };
        tblRegistration.Rows.Add(arrRecord);

        object[] arrStudent = { "Geraldine", "Rodetsky", "Unknown" };

        rowStudent = tblRegistration.NewRow();
        rowStudent.ItemArray = arrStudent;

        tblRegistration.Rows.Add(rowStudent);

        FileStream stmStudents = new FileStream("registration.xml",
                                                 FileMode.Create,
                                                 FileAccess.Write);
        dsRedOakHighSchool.WriteXml(stmStudents);
    }
}

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

If you want the file to be formatted as text, you can use the following version of the method:

public void WriteXml(TextWriter writer);

If you prefer to use an XmlWriter variable to manage the file, use the following version of the method:

public void WriteXml(XmlWriter writer);

Obviously to use this method, you must first define an XmlWriter type of variable.

Opening a Data Set

To open the data saved from a list, the DataSet class provides the ReadXml() method that is overloaded with various versions. One of the versions of this method uses the following syntax:

public XmlReadMode ReadXml(string fileName);

This method takes as argument the name of an existing XML file or its path. The method opens the file and provides the XML formatting as it was done when the file was saved. Here is an example of calling this method:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;

public class Exercise : Form
{
    DataGridView dgvStudents;

    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colGender;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        Controls.Add(dgvStudents);

        colFirstName = new DataColumn("First Name");
        colLastName = new DataColumn("Last Name");
        colGender = new DataColumn("Gender");

        tblRegistration = new DataTable("Student Registration");
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colGender);

        dsRedOakHighSchool = new DataSet("Red Oak High School");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        if( File.Exists("students.xml") )
        {
            dsRedOakHighSchool.ReadXml("students.xml");
            dgvStudents.DataSource = dsRedOakHighSchool;
            dgvStudents.DataMember = "Student Registration";
        }
    }
}

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

Although this method can read any XML file, if you use it to open a file that was saved by someone else or another application and you want to use it in your application, you should be familiar with the names of its nodes. If it contains names that are not "registered" or recognized by your DataSet object, the lists that compose your application may not be able to read it, not because the list was not formatted right, but because the lists of your application would be holding different names.

If the file was saved using a Stream-based class, you can pass a stream to the method based on the following syntax:

public XmlReadMode ReadXml(Stream stream);

In the same way, the method provides an equivalent version for the TextWriter and the XmlWriter versions:

public XmlReadMode ReadXml(TextReader reader);
public XmlReadMode ReadXml(XmlReader reader);

To use one of these versions, you must first define a TextWriter or an XmlReader type of variable.

When retrieving the content of the XML file, if you want it delivered as text, call the DataSet.GetXml() method. Its syntax is:

public string GetXml();

As you can see, this method returns a string.

Once a file has been opened, you can explore its content. The most obvious operation related to opening a data set consists of viewing its records.

Committing or Rejecting Changes to a List

When a user has created a record, the data set that holds the information is considered to have been modified because, obviously, it does not have the same information or the same records it had when the application was launched. You, as the programmer, have the option of accepting the changes or rejecting them. To accept the changes, call the DataSet.AcceptChanges() method. Its syntax is:

public void AcceptChanges();

If you don't want the changes to take effect, you can reject them by calling the DataSet.RejectChanges() method. Its syntax is:

public virtual void RejectChanges();

This method can be called to dismiss whatever changes were made on the records of the list(s).

Locating Records and Their Values

 

Locating a Record

Consider the following data set:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;

public class Exercise : Form
{
    ListView lvwStudents;

    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colStudentNumber;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colGender;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";

        lvwStudents = new ListView();
        lvwStudents.Location = new Point(12, 12);
        Controls.Add(lvwStudents);

        colStudentNumber = new DataColumn("StudentNumber");
        colFirstName = new DataColumn("FirstName");
        colLastName = new DataColumn("LastName");
        colGender = new DataColumn("Gender");

        tblRegistration = new DataTable("Student");
        tblRegistration.Columns.Add(colStudentNumber);
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colGender);

        dsRedOakHighSchool = new DataSet("SchoolRecords");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
        tblRegistration.Rows.Add(objStudents1);
        object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
        tblRegistration.Rows.Add(objStudents2);
        object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
        tblRegistration.Rows.Add(objStudents3);
        object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
        tblRegistration.Rows.Add(objStudents4);
        object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };
        tblRegistration.Rows.Add(objStudents5);
        dsRedOakHighSchool.WriteXml("students.xml");
    }
}

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

This would produce:

<?xml version="1.0" standalone="yes"?>
<SchoolRecords>
  <Student>
    <StudentNumber>920759</StudentNumber>
    <FirstName>Pauline</FirstName>
    <LastName>Simms</LastName>
    <Gender>Female</Gender>
  </Student>
  <Student>
    <StudentNumber>281174</StudentNumber>
    <FirstName>Geraldine</FirstName>
    <LastName>Rodetsky</LastName>
    <Gender>Unknown</Gender>
  </Student>
  <Student>
    <StudentNumber>400795</StudentNumber>
    <FirstName>Edward</FirstName>
    <LastName>Zaks</LastName>
    <Gender>Male</Gender>
  </Student>
  <Student>
    <StudentNumber>931579</StudentNumber>
    <FirstName>Jeannete</FirstName>
    <LastName>Palau</LastName>
    <Gender>Female</Gender>
  </Student>
  <Student>
    <StudentNumber>315825</StudentNumber>
    <FirstName>Kate</FirstName>
    <LastName>Hooks</LastName>
    <Gender>Unknown</Gender>
  </Student>
</SchoolRecords>

Before performing any operation on a record, you must be able to locate it. That is, you must be able to identify a record among the various records of a table. The records of a table are stored in a list based on the DataRowCollection class. To locate a record in the DataTable.Rows collection, the DataRowCollection class has an indexed property that is defined as follows:

public DataRow this[int index] {get;}

The first record has an index of 0. The second record has an index of 1, and so on. Here is an example of identify each column:

void InitializeComponent()
{
    Text = "Students Records";
    Size = new Size(320, 160);

    lvwStudents = new ListView();
    lvwStudents.Location = new Point(12, 12);
    lvwStudents.Size = new Size(290, 110);
    lvwStudents.View = View.Details;
    lvwStudents.GridLines = true;
    lvwStudents.FullRowSelect = true;

    Controls.Add(lvwStudents);

    colStudentNumber = new DataColumn("StudentNumber");
    colFirstName = new DataColumn("FirstName");
    colLastName = new DataColumn("LastName");
    colGender = new DataColumn("Gender");

    tblRegistration = new DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    dsRedOakHighSchool = new DataSet("SchoolRecords");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    dsRedOakHighSchool.ReadXml("students.xml");

    lvwStudents.Columns.Add("Student #");
    lvwStudents.Columns.Add("First Name", 75);
    lvwStudents.Columns.Add("Last Name", 75);
    lvwStudents.Columns.Add("Gender");

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

Each record of a table is an object of type DataRow. When you access a record, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced.

Locating a Value by a Column's Index

Once you have located a record, you can find a particular value you are interested in, and you have tremendous options (The DataSet class is one of the .NET Framework's masterpieces). Consider the following table of records:

To locate a value in a data set, you need two pieces of information: the record and the column. We have seen how to located a record. Each value of a table is created under a particular column. Therefore, you must be able to specify the column under which the value exists. To identify the columns of a table, the DataRow class is equipped the overloaded indexed property which comes in 6 versions.

As seen in previous lessons, the columns of a table are indexed with the first column at 0, the second at 1, and so on. To allow you to identify a column by its index, one of the versions of the DataRow's indexed property uses the following syntax:

public object this[int columnIndex] {get; set;}

This property expects the index of the column. Here are examples:

void InitializeComponent()
{
    . . . No Change

    for (int i = 0; i < tblRegistration.Rows.Count; i++)
    {
        DataRow rowStudent = tblRegistration.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());

        lvwStudents.Items.Add(lviStudent);
    }
}

To access a record directly without first declaring a DataRow variable, the above code can also be written as follows:

void InitializeComponent()
{
    . . . No Change

    for (int i = 0; i < tblRegistration.Rows.Count; i++)
    {
        ListViewItem lviStudent =
		new ListViewItem(tblRegistration.Rows[i][0].ToString());
        lviStudent.SubItems.Add(tblRegistration.Rows[i][1].ToString());
        lviStudent.SubItems.Add(tblRegistration.Rows[i][2].ToString());
        lviStudent.SubItems.Add(tblRegistration.Rows[i][3].ToString());

        lvwStudents.Items.Add(lviStudent);
    }
}

In the previous lessons, we learned how to locate a column using the foreach loop to visit the members of a DataColumnCollection collection. Like the DataColumnCollection class, the DataRowCollection class implements the GetEnumerator() method of the IEnumerable interface. This means that you can use the foreach loop on a collection of records to visit each member. Here is an example:

void InitializeComponent()
{
    . . . No Change

    foreach(DataRow rowStudent in tblRegistration.Rows)
    {
        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());

        lvwStudents.Items.Add(lviStudent);
    }
}

Locating a Value by the Column Object Name

Instead of using the index of a column, you can locate a value using the object name of its column. To do this, you can use the following syntax of the DataRow indexed property:

public object this[string ColumnName] {get; set;}

This property expects the object name of the column passed in its square brackets. Here are examples:

void InitializeComponent()
{
    . . . No Change

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

        ListViewItem lviStudent =
	    new ListViewItem(rowStudent["StudentNumber"].ToString());
        lviStudent.SubItems.Add(rowStudent["FirstName"].ToString());
        lviStudent.SubItems.Add(rowStudent["LastName"].ToString());
        lviStudent.SubItems.Add(rowStudent["Gender"].ToString());

        lvwStudents.Items.Add(lviStudent);
    }
}

Locating a Value by the Column Variable Name

Instead of using the index or the object name of a column, you can also locate a value using the variable name of its column. To do this, you can use the following syntax of the DataRow indexed property:

public object this[DataColumn column] {get; set;}

This property expects the object name of the column passed in its square brackets. Here are examples:

void InitializeComponent()
{
    . . . No Change

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

        ListViewItem lviStudent =
		new ListViewItem(rowStudent[colStudentNumber].ToString());
        lviStudent.SubItems.Add(rowStudent[colFirstName].ToString());
        lviStudent.SubItems.Add(rowStudent[colLastName].ToString());
        lviStudent.SubItems.Add(rowStudent[colGender].ToString());

        lvwStudents.Items.Add(lviStudent);
    }
}

Locating a Value From the Column Collection

As mentioned already, to access a record, you can pass its index to the indexed property of the DataRowCollection, which produces a DataRow object. Using these concepts, you can access the values of a table. Here is an example:

void InitializeComponent()
{
    . . . No Change

    foreach(DataRow rowStudent in tblRegistration.Rows)
    {
        foreach (DataColumn col in tblRegistration.Columns)
        {
            MessageBox.Show(rowStudent[col].ToString());
        }
        break;
    }
}

This code allows you to access a record using a row of a table and to locate a value based on the name of its column, but the above code does not allow you to clearly identify the column whose value you want to access. To clearly locate a value, you should name its column and to do this, you can pass the column name to the indexed property of the record. Here are examples:

void InitializeComponent()
{
    . . . No Change

    foreach (DataRow rowStudent in tblRegistration.Rows)
    {
        foreach (DataColumn col in tblRegistration.Columns)
        {
            ListViewItem lviStudent =
                new ListViewItem(rowStudent["StudentNumber"].ToString());
            lviStudent.SubItems.Add(rowStudent["FirstName"].ToString());
            lviStudent.SubItems.Add(rowStudent["LastName"].ToString());
            lviStudent.SubItems.Add(rowStudent["Gender"].ToString());

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

When using any of these previous techniques (whether using for or foreach), if you specify an index that is either less than 0 or beyond the number of records in the table, the compiler would throw an IndexOutOfRangeException exception.

Assistance With Data Entry

 

Introduction

If you create an application that allows the user to enter some values, you would wish the user enter the right type of data under each column. To assist you with, the DataColumn class allows you to specify an appropriate or desired data type for each column. The data type of a column allows it to accept or reject an inappropriate value. Although we saw that the name was the most important aspect of a column, in reality, a data type is also required.

Specifying a Column's Data Type

To specify the data type of a column, if you are visually creating the table, in the Columns Collection Editor, under Members, create or select the name of a column. In the Properties list, click the arrow of the DataType field and select from the list:

To supports data types for a column, the DataColumn class relies on the following .NET Framework structures: Boolean, Byte, Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, String, TimeSpan, UInt16, UInt32, and UInt64. The DataColumn class can also support an array of Byte values, as in Byte[], for a column.

When creating a new column, if you don't specify its data type, it is assumed to be a string and the string data type is automatically applied to it.

To programmatically specify the data type of a column, you have two main alternatives. When declaring a column, to specify its data type, you can initialize the DataColumn variable using the third constructor of the class. Its syntax is:

public DataColumn(string columnName, Type dataType);

To specify a column's data type, select one from the Type class of the System namespace by calling the Type.GetType() method. The GetType() method is overloaded with three versions. The first version has the following syntax:

public static Type GetType(string typeName);

This method expects as argument a valid data type defined in the .NET Framework. The data type must be retrieved from the Type class of the System namespace. The name of the data type must be qualified with a period operator. Here is an example:

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

public class Exercise : Form
{
    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colStudentNumber;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";
        Size = new Size(320, 160);

        colStudentNumber = new DataColumn("StudentNumber",
					  Type.GetType("System.Int32"));

        tblRegistration = new DataTable("Student");
        tblRegistration.Columns.Add(colStudentNumber);

        dsRedOakHighSchool = new DataSet("SchoolRecords");
        dsRedOakHighSchool.Tables.Add(tblRegistration);
    }
}

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

If you used the default constructor to create a DataColumn, to specify its data type, assign its qualified type to the DataColumn.DataType property. Here is an example:

public class Exercise : Form
{
    DataColumn colStudentNumber;
    DataColumn colFirstName;
    DataColumn colLivesInASingleParentHome;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";
        Size = new Size(320, 160);

        colStudentNumber = new DataColumn("StudentNumber",
            Type.GetType("System.Int32"));
        
        colFirstName = new DataColumn("FullName");
        colFirstName .DataType = Type.GetType("System.String");

        colLivesInASingleParentHome = new DataColumn("LSPH");
        colLivesInASingleParentHome.DataType = Type.GetType("System.Boolean");
    }
}

Remember that there are various techniques you can use to create a column by specifying its name and its data type.

The Expression of a Column

 

Introduction

So far, to perform data entry, we created the data fields and expected the user to enter values in them. In some case, instead of the user typing data, you may want to specify your own constant value or you may want to combine some values. An expression can be:

  • A constant value such as 288, "Aaron Watts", or 48550.95
  • A combination of two or more constants such 50 + 428, "HourlySalary" + 25.85, or "John" & " " & "Santini", 
  • The name of a column such as Filename, CountryCode or DateOfBirth
  • The combination of a constant and one or more column names such as Username & "@gmail.com"
  • Or a combination of two or more columns such as FirstName & LastName

Besides the items in this list, you can also use some functions and/or combine them with the items in the above list. The expression then creates or represents a value. To create an expression, there are various rules you must follow:

  • If the expression is algebraic, you can use the normal math operations (+, -, *, and /) applied to one or more constants combined to one or more column names
  • To create a combination of strings, you can use the + operator

Once you have decided about this expression, you can use it as the value assigned to a column.

Creating an Expression

If you are visually creating a column, under the Members list of the Column Collection Editor, select a column. To specify an expression for it, in the Properties list, click Expression and type the desired expression. Here is an example: 

To programmatically specify the expression used on a column, assign the expression, as a string, to its variable name. Here is an example:

public class Exercise : Form
{
    DataSet dsRedOakHighSchool;
    DataTable tblRegistration;
    DataColumn colFirstName;
    DataColumn colLastName;
    DataColumn colFullName;

    DataGridView dgvStudents;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Students Records";
        Size = new Size(550, 160);

        colFirstName = new DataColumn("FirstName");
        colFirstName.DataType = Type.GetType("System.String");

        colLastName = new DataColumn("LastName");
        colLastName.DataType = Type.GetType("System.String");

        colFullName = new DataColumn("FullName");
        colFullName.DataType = Type.GetType("System.String");
        colFullName.Expression = "FirstName + ' ' + LastName";

        tblRegistration = new DataTable("Student");
        tblRegistration.Columns.Add(colFirstName);
        tblRegistration.Columns.Add(colLastName);
        tblRegistration.Columns.Add(colFullName);

        dsRedOakHighSchool = new DataSet("StudentsRecords");
        dsRedOakHighSchool.Tables.Add(tblRegistration);

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 12);
        dgvStudents.Size = new Size(400, 100);
        dgvStudents.DataSource = dsRedOakHighSchool;
        dgvStudents.DataMember = "Student";

        Controls.Add(dgvStudents);
    }
}

Thanks to this code, the user can type both the first and the last names. Then two things:

  1. When the user moves to the next record, the expression is used to create the value of the full name column
     
  2. The user cannot enter a value in the column that has an expression

Records Maintenance

 

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.

Selecting a Value

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();
}

Editing a Record by the Columns' Object Names

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);
        }
    }
}

Editing a Record by the Columns' Indices

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.

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 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);
        }
    }
}

Removing a Row From the 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 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);
        }
    }
}

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 of 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.

 

Previous Copyright © 2007-2009 FunctionX, Inc. Next