C# Tutorials - Data Sets: Locating Records and Their Values

	Locating Records and Their Values

Locating a Record

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. 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 records of a table are stored in a list (called the DataRowCollection). The first record has an index of 0. The second record has an index of 1, and so on. Here is an example of using it to retrieve the information stored in a record:

DataRow row = this.dtVideos.Rows[2];

When you pass an index to this property, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced.

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 apply the foreach loop to its collection of records to visit each 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:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            foreach (DataRow row in tblVideos.Rows)
            {
                foreach (DataColumn col in tblVideos.Columns)
                {
                    Console.WriteLine("{0}", row[col]);
                }
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

This would produce:

Video Collection
================================
GT-682
A Few Good Men
Rob Reiner
138 Minutes
1992
R
--------------------------------
MM-258
Fatal Attraction
Adrian Lyne
120 Minutes
1987
R
--------------------------------
FD-205
Her Alibi
Bruce Beresford
94 Minute
1989
PG-13
--------------------------------
Press any key to continue . . .

The DataRow class itself is equipped with an indexed property that allows you to access the value stored in a particular column. For example, you can use a for loop to visit each column by its index. Once you get to a column, you can then use the indexed property of a row to access the value stored under that column. Here are examples:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("================================");
            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            for (int i = 0; i < tblVideos.Rows.Count; i++)
            {
                DataRow row = tblVideos.Rows[i];
            Console.WriteLine("Shelf #:  {0}", tblVideos.Rows[i]["ShelfNumber"]);
                Console.WriteLine("Title:    {0}", tblVideos.Rows[i]["Title"]);
                Console.WriteLine("Director: {0}", tblVideos.Rows[i]["Director"]);
                Console.WriteLine("Length:   {0}", tblVideos.Rows[i]["Length"]);
                Console.WriteLine("Year:     {0}", tblVideos.Rows[i]["Year"]);
                Console.WriteLine("Rating:   {0}", tblVideos.Rows[i]["Rating"]);
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

This would produce:

================================
Video Collection
================================
Shelf #:  GT-682
Title:    A Few Good Men
Director: Rob Reiner
Length:   138 Minutes
Year:     1992
Rating:   R
--------------------------------
Shelf #:  MM-258
Title:    Fatal Attraction
Director: Adrian Lyne
Length:   120 Minutes
Year:     1987
Rating:   R
--------------------------------
Shelf #:  FD-205
Title:    Her Alibi
Director: Bruce Beresford
Length:   94 Minute
Year:     1989
Rating:   PG-13
--------------------------------
Press any key to continue . . .

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.

Locating a Value

As mentioned already, a record is in fact one or a group of values from each of the columns of the table. Consider the following table:

Title	Director	Length	© Year	Rating
A Few Good Men	Rob Reiner	138 Minutes	1992	R
The Distinguished Gentleman	Jonathan Lynn	112 Minutes		R
The Lady Killers	Joel Coen & Ethan Coen	104 Minutes		R
Fatal Attraction	Adrian Lyne	120 Minutes	1987	R
Her Alibi	Bruce Beresford	94 Minutes	1989	PG-13
The Manchurian Candidate	Jonathan Demme	129 Minutes	2004	R

The "A Few Good Men" string is a value of the Title column. In the same way, 1992 is a value of the Year column. In some circumstances, you will need to locate a particular value in order to perform an operation on it. You can start by locating the record you need and return its DataRow object. To know the table that the record belongs to, access its DataRow.Table property. This property is declared as follows:

public DataTable Table {get;}

To locate the value that a record holds under a particular column, the DataRow class had an indexed property that is overloaded with various versions (actually six, but at this time we are interested in the first three only). One of the versions of this property uses the following syntax:

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

To use this property, pass the object name of the column in the square brackets. Here are examples:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("================================");
            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            for (int i = 0; i < tblVideos.Rows.Count; i++)
            {
                DataRow row = tblVideos.Rows[i];
            Console.WriteLine("Shelf #:  {0}", tblVideos.Rows[i][colShelfNumber]);
                Console.WriteLine("Title:    {0}", tblVideos.Rows[i][colTitle]);
                Console.WriteLine("Director: {0}", tblVideos.Rows[i][colDirector]);
                Console.WriteLine("Length:   {0}", tblVideos.Rows[i][colLength]);
                Console.WriteLine("Year:     {0}", tblVideos.Rows[i][colYear]);
                Console.WriteLine("Rating:   {0}", tblVideos.Rows[i][colRating]);
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

Instead of using the index 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. We saw earlier how to use this version of the property. Here are examples, using foreach:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("================================");
            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            foreach (DataRow row in tblVideos.Rows)
            {
                Console.WriteLine("Shelf #:  {0}", row["ShelfNumber"]);
                Console.WriteLine("Title:    {0}", row["Title"]);
                Console.WriteLine("Director: {0}", row["Director"]);
                Console.WriteLine("Length:   {0}", row["Length"]);
                Console.WriteLine("Year:     {0}", row["Year"]);
                Console.WriteLine("Rating:   {0}", row["Rating"]);
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

The third option you have is to identify the column by its index. To do this, use the following syntax of the DataRow indexed property:

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

This property expects the index of the column.

Here are examples using the for loop:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("================================");
            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            for (int i = 0; i < tblVideos.Rows.Count; i++)
            {
                DataRow row = tblVideos.Rows[i];
                Console.WriteLine("Shelf #:  {0}", tblVideos.Rows[i][0]);
                Console.WriteLine("Title:    {0}", tblVideos.Rows[i][1]);
                Console.WriteLine("Director: {0}", tblVideos.Rows[i][2]);
                Console.WriteLine("Length:   {0}", tblVideos.Rows[i][3]);
                Console.WriteLine("Year:     {0}", tblVideos.Rows[i][4]);
                Console.WriteLine("Rating:   {0}", tblVideos.Rows[i][5]);
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

Or, here are examples using foreach:

using System;
using System.IO;
using System.Data;

namespace VideoCollection
{
    public static  class Program
    {
        . . . No Change

        static void ShowVideos()
        {
            dsVideos.ReadXml(strDirectory + @"\videos.xml");

            Console.WriteLine("================================");
            Console.WriteLine("Video Collection");
            Console.WriteLine("================================");
            foreach (DataRow row in tblVideos.Rows)
            {
                Console.WriteLine("Shelf #:  {0}", row[0]);
                Console.WriteLine("Title:    {0}", row[1]);
                Console.WriteLine("Director: {0}", row[2]);
                Console.WriteLine("Length:   {0}", row[3]);
                Console.WriteLine("Year:     {0}", row[4]);
                Console.WriteLine("Rating:   {0}", row[5]);
                Console.WriteLine("--------------------------------");
            }
        }

        static int Main(string[] args)
        {
            CreateCollection();
            ShowVideos();

            return 0;
        }
    }
}

Practical Learning: Getting the Values of a Data Set

Access the Inventory.cs file

To allow the user to display the store inventory, change the file as follows:

using System;
using System.IO;
using System.Xml;
using System.Data;

namespace CollegeParkAutoParts1
{
    public class Inventory
    {
        // These are the columns of the AutoParts table
        private DataColumn colPartNumber;
        private DataColumn colYear;
        private DataColumn colMake;
        private DataColumn colModel;
        private DataColumn colPartName;
        private DataColumn colPartPrice;

        // These are the columns of the StoreItems table
        private DataColumn colItemNumber;
        private DataColumn colItemName;
        private DataColumn colItemPrice;

        // These are the table of the CollegeParkAutoParts database
        private DataTable tblAutoParts;
        private DataTable tblStoreItems;

        // This is the database
        private DataSet dsStoreItems;

        // These are accessory strings
        string strDirectory;
        string strFilename;

        // This default constructor is used to create
        // the structures of the tables
        public Inventory()
        {
            colPartNumber = new DataColumn("PartNumber",
                Type.GetType("System.Int32"));
            colYear = new DataColumn("Year",
                Type.GetType("System.Int32"));
            colMake = new DataColumn("Make",
                Type.GetType("System.String"));
            colModel = new DataColumn("Model",
                Type.GetType("System.String"));
            colPartName = new DataColumn("PartName",
                Type.GetType("System.String"));
            colPartPrice = new DataColumn("PartPrice",
                Type.GetType("System.Double"));

            tblAutoParts = new DataTable("AutoPart");
            tblAutoParts.Columns.Add(colPartNumber);
            tblAutoParts.Columns.Add(colYear);
            tblAutoParts.Columns.Add(colMake);
            tblAutoParts.Columns.Add(colModel);
            tblAutoParts.Columns.Add(colPartName);
            tblAutoParts.Columns.Add(colPartPrice);

            tblStoreItems = new DataTable("StoreItem");
            colItemNumber = new DataColumn("ItemNumber",
                Type.GetType("System.Int32"));
            colItemName = new DataColumn("ItemName",
                Type.GetType("System.String"));
            colItemPrice = new DataColumn("ItemPrice",
                Type.GetType("System.Double"));
           tblStoreItems .Columns.Add(colItemNumber);
           tblStoreItems.Columns.Add(colItemName);
           tblStoreItems.Columns.Add(colItemPrice);

            dsStoreItems = new DataSet("StoreItems");
            dsStoreItems.Tables.Add(tblAutoParts);
            dsStoreItems.Tables.Add(tblStoreItems);

            // This database will use a folder
            // named College Park Auto Parts
            // and located on the C: drive
            strDirectory = @"C:\College Park Auto Parts";
            strFilename = strDirectory + "\\" + "StoreItems.xml";

            DirectoryInfo dirInfo = new DirectoryInfo(strDirectory);

            // If the folder doesn't exist already, create it
            if (!dirInfo.Exists)
                dirInfo.Create();
        }

        // This method guides the user in creating a new store item
        public void CreateStoreItem()
        {
            // Accessory variables
            int year = 1960;
            int iPartNumber = 0, iItemNumber = 0;
            double unitPrice = 0.00D;
            string strMake = "Unknown", strModel = "Unknown",
                   strPartName = "N/A", strItemName = "N/A";
            int typeOfItem = 0;
            char ansAdd = 'n';

            // If the StoreItems.xml file exists already, then open it
            if (File.Exists(strFilename))
                dsStoreItems.ReadXml(strFilename);

            // This do...while is used in case the user wants to 
            // repeatedly perform these actions
            do
            {
                try
                {
                    // Find out what the user wants to do
                    Console.WriteLine("What type of item do you want to add");
                    Console.WriteLine("1. An auto part (for a car or an engine)");
                Console.WriteLine("2. Another type of item, not for a specific car");
                    Console.WriteLine("0. Stop");
                    Console.WriteLine("Enter the following pieces of information");
                    Console.Write("Your Choice: ");
                    typeOfItem = int.Parse(Console.ReadLine());

                    // The user wants to add a new car part
                    if (typeOfItem == 1)
                    {
                        // Create a random number of 6 digits
                        Random rndPartNumber = new Random();
                        iPartNumber = rndPartNumber.Next(100000, 999999);

                        // Request the information about the car and the part
                        try
                        {
                            Console.Write("Car Year: ");
                            year = int.Parse(Console.ReadLine());
                        }
                        catch (FormatException)
                        {
                            Console.WriteLine("Invalid year");
                        }
                        Console.Write("Make (or None if N/A):  ");
                        strMake = Console.ReadLine();
                        Console.Write("Model (or None if N/A): ");
                        strModel = Console.ReadLine();
                        Console.Write("Part Name:              ");
                        strPartName = Console.ReadLine();

                        try
                        {
                            Console.Write("Unit Price:             ");
                            unitPrice = double.Parse(Console.ReadLine());
                        }
                        catch (FormatException)
                        {
                            Console.WriteLine("Invalid unit price");
                        }

                        // Present the user with a summary of the part to be added
                        // This is a safe guard for a console application so that
                        // if the user made a mistake, he or she can dismiss it
                        // instead of adding a part with wrong information
                    Console.WriteLine("\nHere is a summary of the part to be added");
                        Console.WriteLine("--------------------------------");
                        Console.WriteLine("Part Number: {0}", iPartNumber);
                        Console.WriteLine("Year:        {0}", year);
                        Console.WriteLine("Make:        {0}", strMake);
                        Console.WriteLine("Model:       {0}", strModel);
                        Console.WriteLine("Part Name:   {0}", strPartName);
                        Console.WriteLine("Unit Price:  {0:C}", unitPrice);
                        Console.WriteLine("--------------------------------");

                    Console.Write("Are you ready to add it to the database (y/n)? ");
                        ansAdd = char.Parse(Console.ReadLine());

                        // If the user has decided to add the part to the database,
                        // then add it
                        if ((ansAdd == 'y') || (ansAdd == 'Y'))
                        {
                            DataRow part = tblAutoParts.NewRow();
                            part["PartNumber"] = iPartNumber;
                            part["Year"] = year;
                            part["Make"] = strMake;
                            part["Model"] = strModel;
                            part["PartName"] = strPartName;
                            part["PartPrice"] = unitPrice;
                            tblAutoParts.Rows.Add(part);

                            dsStoreItems.WriteXml(strFilename);
                        }
                        else
                    Console.WriteLine("The part will not be added to the database");
                    }
                    // For the same logic for other store items
                    // The items in this section can be anything
                    else if (typeOfItem == 2)
                    {
                        Random rndPartNumber = new Random();
                        iItemNumber = rndPartNumber.Next(100000, 999999);

                        Console.Write("Item/Description:  ");
                        strItemName = Console.ReadLine();

                        try
                        {
                            Console.Write("Unit Price:             ");
                            unitPrice = double.Parse(Console.ReadLine());
                        }
                        catch (FormatException)
                        {
                            Console.WriteLine("Invalid unit price");
                        }
                        
                        Console.WriteLine("\nHere is a summary of the part to be added");
                        Console.WriteLine("--------------------------------");
                        Console.WriteLine("Item Number: {0}", iItemNumber);
                        Console.WriteLine("Name/Descr:  {0}", strItemName);
                        Console.WriteLine("Unit Price:  {0:C}", unitPrice);
                        Console.WriteLine("--------------------------------");

                        Console.Write("Are you ready to add it to the database (y/n)? ");
                        ansAdd = char.Parse(Console.ReadLine());

                        if ((ansAdd == 'y') || (ansAdd == 'Y'))
                        {
                            DataRow item = tblStoreItems.NewRow();
                            item["ItemNumber"] = iItemNumber;
                            item["ItemName"] = strItemName;
                            item["ItemPrice"] = unitPrice;
                            tblStoreItems.Rows.Add(item);

                            dsStoreItems.WriteXml(strFilename);
                        }
                        else
                    Console.WriteLine("The part will not be added to the database");
                    }
                }
                catch (FormatException)
                {
                    Console.WriteLine("Invalid Menu Selection");
                }
            } while (typeOfItem == 1 || typeOfItem == 2);
        }

        // This method is used to display the store inventory of all items
        public void ShowInventory()
        {
            if (File.Exists(strFilename))
            {
                dsStoreItems.ReadXml(strFilename);

                Console.WriteLine("================================");
                Console.WriteLine("   College Park Auto Parts");
                Console.WriteLine("      Store Inventory");
                Console.WriteLine("        Car Parts");
                Console.WriteLine("================================");
                foreach (DataRow part in tblAutoParts.Rows)
                {
                    Console.WriteLine("Part #:     {0}", part["PartNumber"]);
                    Console.WriteLine("Car Year:   {0}", part["Year"]);
                    Console.WriteLine("Make:       {0}", part["Make"]);
                    Console.WriteLine("Model:      {0}", part["Model"]);
                    Console.WriteLine("Part Name:  {0}", part["PartName"]);
                    Console.WriteLine("Unit Price: {0:C}", part["PartPrice"]);
                    Console.WriteLine("--------------------------------");
                }

                Console.WriteLine("================================");
                Console.WriteLine("     Other Store Items");
                Console.WriteLine("================================");
                foreach (DataRow item in tblStoreItems.Rows)
                {
                    Console.WriteLine("Item #:           {0}", item["ItemNumber"]);
                    Console.WriteLine("Name/Description: {0}", item["ItemName"]);
                    Console.WriteLine("Unit Price:       {0:C}", item["ItemPrice"]);
                    Console.WriteLine("--------------------------------");
                }
            }
        }
    }
}

Access the Program.cs file

To allow the user to decide about the action to take when the application comes up, change the file as follows:

using System;

namespace CollegeParkAutoParts1
{
    class Program
    {
        static int Main(string[] args)
        {
            char choice = '0';
            Inventory inv = new Inventory();
            
            Console.WriteLine("================================");
            Console.WriteLine("   College Park Auto Parts");
            Console.WriteLine("================================");
            do
            {
                try
                {
                    Console.WriteLine("What do you want to do?");
                    Console.WriteLine("1. Add New Store Item");
                    Console.WriteLine("2. View Inventory");
                    Console.WriteLine("0. Quit");
                    Console.Write("Your Selection? ");
                    choice = char.Parse(Console.ReadLine());

                    if (choice == '1')
                        inv.CreateStoreItem();
                    else if (choice == '2')
                        inv.ShowInventory();
                }
                catch (FormatException)
                {
                    Console.WriteLine("Unrecognizable Menu Selection");
                }
            } while ((choice == '1') || (choice == '2'));

            Console.WriteLine();
            return 0;
        }
    }
}

Execute the application to see the result:

=====================================
   College Park Auto Parts
=====================================
What do you want to do?
1. Add New Store Item
2. View Inventory
0. Quit
Your Selection? 2
==========================================
   College Park Auto Parts
      Store Inventory
        Car Parts
==========================================
Part #:     299693
Car Year:   2005
Make:       Acura
Model:      NSX 3.0L V6
Part Name:  Oil Filter
Unit Price: $8.85
------------------------------------------
Part #:     398747
Car Year:   2002
Make:       Audi
Model:      Quattro 1.8L Turbo
Part Name:  Clash Bear
Unit Price: $55.50
------------------------------------------
Part #:     174724
Car Year:   2002
Make:       BMW
Model:      325i 2.5L L6
Part Name:  Ignition Coil
Unit Price: $60.85
------------------------------------------
==========================================
     Other Store Items
==========================================
Item #:           319027
Name/Description: Soda 2L Bottle
Unit Price:       $1.75
------------------------------------------
Item #:           865745
Name/Description: STP Gas Treatment 3-Pack
Unit Price:       $2.99
------------------------------------------
What do you want to do?
1. Add New Store Item
2. View Inventory
0. Quit
Your Selection? 0

Press any key to continue . . .

Close the DOS window


Previous	Copyright © 2006-2016, FunctionX, Inc.	Next