Introduction

We can get direct access if using the Rows and Columns collections on a DataTable. The DataTable.Select method allows us to filter.

Better is to use a DataView. Filter, sort, find, and you get proper handling of deleted row values.

Example code is in project DataSet\DtView.

Setup

Here is the setup for the DataTable we will be using.

        static DataSet ds;
        static DataTable items;


        private static void CreateDataTable()
        {
            ds = new DataSet("fred");

            items = new DataTable("Items");
            ds.Tables.Add(items);

            DataColumn dc;

            dc = new DataColumn();
            dc.ColumnName = "id";
            dc.DataType = typeof(Int32);
            dc.ReadOnly = true;
            dc.AllowDBNull = false;
            dc.Unique = true;
            dc.AutoIncrement = true;
            dc.AutoIncrementSeed = 100;
            dc.AutoIncrementStep = 10;
            items.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "name";
            dc.DataType = typeof(string);
            dc.AllowDBNull = false;
            dc.Unique = true;
            items.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "color";
            dc.DataType = typeof(string);
            dc.AllowDBNull = true;
            items.Columns.Add(dc);

            items.PrimaryKey = new DataColumn[] { items.Columns["id"] };
        }

        static string[] itemNames = { "widget", "freeble", "thingie", "whatzit", "geegaw" };
        static string[] itemColors = { "blue", "blue", "blue", "blue", "red" };

        private static void EnterInitialData()
        {

            DataRow row;

            for (int i = 0; i < itemNames.Length; ++i)
            {
                row = items.NewRow();
                row["name"] = itemNames[i];
                row["color"] = itemColors[i];
                items.Rows.Add(row);
            }

            // Make the initial rows Unchanged
            items.AcceptChanges();
        }

        private static void EditDataTable()
        {
            DataRow row;

            row = items.Rows[0];
            row.Delete();

            row = items.Rows[1];
            row["color"] = "teal";

            row = items.NewRow();
            row["name"] = "thingamabob";
            row["color"] = "green";
            items.Rows.Add(row);
        }

Find

The DataTable.Rows value is a DataRowCollection. THat collection has a Find method to use when searching for records by primary key.

        private static void FindData()
        {
            DataRow foundRow = items.Rows.Find(110);
            if (foundRow == null)
                Console.WriteLine("Didn't find it");
            else
            {
                Console.WriteLine("Found a row");
                PrintRow(foundRow);  // see below
                Console.WriteLine("------");
            }
        }

The overload of Find used above takes a single key value. If the table has a multi-field key, there is an overload that takes an object array.

 DataRow foundRow = dataTable.Find( new object[] { key1, key2, key3 } );

Select

The Select method of the DataTable allows you to specify several criteria:

DataRow[] rows = dt.Select(filter,sort,state);

There are overloads taking 0, 1, or 2 arguments that default the trailing parameters.

• filter = a SQL WHERE expression
• sort = a SQL ORDERED BY expression
• state = a value of the DataViewRowState enumeration, possibly several values or'd.

Because of the possibility of seeing Deleted rows, some caution must be taken. I wrote the following method to display the values in a row:

        private static void PrintData(string p, DataRow[] rows)
        {
            Console.WriteLine("------");
            Console.WriteLine(p);
            foreach (DataRow row in rows)
                PrintRow(row);
            Console.WriteLine("------");

        }

        private static void PrintRow(DataRow row)
        {
            foreach (DataColumn col in row.Table.Columns)
                if (row.HasVersion(DataRowVersion.Default))
                    Console.Write("\t{0}", row[col]);
                else
                    Console.Write("\t{0}", row[col, DataRowVersion.Original]); // is deleted
            Console.WriteLine();
        }

Here are some calls to Select, with the output shown next to it.

items.Select()

110     freeble teal
120     thingie blue
130     whatzit blue
140     geegaw  red
150     thingamabob     green

items.Select("color='blue'")

120     thingie blue
130     whatzit blue

items.Select("color='blue'",
             "name DESC")

130     whatzit blue
120     thingie blue

items.Select("","",
   DataViewRowState.Added)

150     thingamabob     green

items.Select("","",
   DataViewRowState.CurrentRows)

110     freeble teal
120     thingie blue
130     whatzit blue
140     geegaw  red
150     thingamabob     green

items.Select("", "", 
   DataViewRowState.Deleted)

100     widget  blue

items.Select("", "", 
   DataViewRowState.ModifiedCurrent)

110     freeble teal

items.Select("", "", 
   DataViewRowState.ModifiedOriginal)

110     freeble teal

items.Select("", "", 
   DataViewRowState.OriginalRows)

100     widget  blue
110     freeble teal
120     thingie blue
130     whatzit blue
140     geegaw  red

items.Select("", "", 
   DataViewRowState.Unchanged)

120     thingie blue
130     whatzit blue
140     geegaw  red

You see no difference between ModifiedCurrent and ModifiedOriginal here; both select all Modified rows. You will see difference when these enum values are used in a DataView. See below.

DataViews

Basics

• A DataView allows you to create different views of the data stored in DataTable.
• Allows filter, sorting.
• Somewhat analogous to views in a database, but computed client-side.
• Does not allow joins; single table only.
• Cannot exclude columns nor add columns.
• Cannot be used a like a table.
• Dynamic view of the data, so different than the result of DataTable.Select, which gives only a snapshot.
• Creates indexes on columns in the sort expression for quick searches.
• Each table has a DefaultView property.
• You can create your own. Several overloads for the c-tor:

  DataView dv1 = new DataView();   // not attached to any table
  DataView dv2 = new DataView(items);  // attached to DataTable items, default filter, sort, CurrentRows
  DataView dv3 = new DataView(items,"color='blue'","name DESC",DataViewRowState.CurrentRows);
                                       // specifying filter, sort, rowstate
  

Properties

Viewing data

• A DataView exposes an enumerable collection of DataRowView objects (not DataRow objects).
• You can use the DataRowView.Row property to get the underlying DataRow. However, see next.
• Each DataRowView can be indexed by column name or ordinal to get the underlying field values. When you do this, it will automatically pick the correct row version depending on the DataViewRowState -- see table below. For this reason, you really should be accessing rows through a view.

Modifying data

• A DataView can be used to add, delete or modify rows of data in the underlying table.
  • Add: The AllowNew property must be true. Call the AddNew method to get a row to modify. Row is not added until EndEdit on the DataRowView is called. However, it will really mess up your sorts in the meantime. If CancelEdit is called, the new row is discarded.
  • Edit: The AllowEdit property must be true. The DataRowView has BeginEdit, EndEdit, CancelEdit.
  • Delete: The AllowDelete property must be true. Call Delete on the DataView or the DataRowView.
• All changes can later by dealt with via AcceptChanges / RejectChanges.

Finding data

• Use Find or FindRows to search for rows matching certain values. You need a Sort to be defined, or at least ApplyDefaultSort.
• Input is array of values, one per each column in the sort order. If only one column, you can just pass the value directly.

Example

And away we go. First, a few helper to write out things of interest.

        private static void PrintViewInfo(DataView view)
        {
            Console.WriteLine("View settings:");
            Console.WriteLine("  AllowDelete: {0}", view.AllowDelete);
            Console.WriteLine("  AllowEdit:   {0}", view.AllowEdit);
            Console.WriteLine("  AllowNew:    {0}", view.AllowNew);
            Console.WriteLine("  Filter: {0}", view.RowFilter);
            Console.WriteLine("  State:  {0}", view.RowStateFilter);
            Console.WriteLine("  Sort:   {0}, Default: {1}", view.Sort, view.ApplyDefaultSort);
        }

        private static void PrintData(DataView view)
        {
            Console.WriteLine("------");
            Console.WriteLine("Filter: {0}",view.RowFilter);
            Console.WriteLine("State:  {0}",view.RowStateFilter);
            Console.WriteLine("Sort:   {0}, Default: {1}",view.Sort,view.ApplyDefaultSort);
            foreach (DataRowView drv in view)
                PrintRow(drv);
            Console.WriteLine("------");
        }


        private static void PrintRow(DataRowView row)
        {
            Console.Write("{0}\t",row.RowVersion);
            foreach (DataColumn col in row.DataView.Table.Columns)
                Console.Write("{0}\t", row[col.ColumnName]);
            Console.WriteLine();
        }

Notice that we do not have to worry about the row version when using the [] indexing on a DataRowView.

Some code and some output:

Console.WriteLine("Default view from the table itself:");
PrintViewInfo(items.DefaultView);

Default view from the table itself:
View settings:
  AllowDelete: True
  AllowEdit:   True
  AllowNew:    True
  Filter:
  State:  CurrentRows
  Sort:   , Default: False

DataView view;

view = new DataView(items);

Console.WriteLine("Our view, default settings:");
PrintViewInfo(view);      
PrintData(view);

Our view, default settings:
View settings:
  AllowDelete: True
  AllowEdit:   True
  AllowNew:    True
  Filter:
  State:  CurrentRows
  Sort:   , Default: False
------
Filter:
State:  CurrentRows
Sort:   , Default: False
Current 110     freeble teal
Current 120     thingie blue
Current 130     whatzit blue
Current 140     geegaw  red
Current 150     thingamabob     green
------

Console.WriteLine("Setting filter and sort:");
view.RowFilter = "color='blue'";
view.RowStateFilter = DataViewRowState.CurrentRows;
view.Sort = "name DESC";
PrintData(view);

Setting filter and sort:
------
Filter: color='blue'
State:  CurrentRows
Sort:   name DESC, Default: False
Current 130     whatzit blue
Current 120     thingie blue
------

Console.WriteLine("You will see a difference between " +
   "ModifiedCurrent and ModifiedOriginal");
view.RowFilter = "";
view.Sort = "";
view.RowStateFilter = DataViewRowState.ModifiedCurrent;
PrintData(view);

view.RowStateFilter = DataViewRowState.ModifiedOriginal;
PrintData(view);

You will see a difference between 
   ModifiedCurrent and ModifiedOriginal
------
Filter:
State:  ModifiedCurrent
Sort:   , Default: False
Current 110     freeble teal
------
------
Filter:
State:  ModifiedOriginal
Sort:   , Default: False
Original        110     freeble blue
------

Console.WriteLine("Look at deleted");
view.RowFilter = string.Empty;
view.RowStateFilter = DataViewRowState.Deleted;
PrintData(view);

Look at deleted
------
Filter:
State:  Deleted
Sort:   , Default: False
Original        100     widget  blue
------

Console.WriteLine("Adding something via the view,"+
  " then viewing only added");
DataRowView newRow = view.AddNew();
newRow["color"] = "red";
newRow["name"] = "gadget";
newRow.EndEdit();
view.RowFilter = string.Empty;
view.RowStateFilter = DataViewRowState.Added;
PrintData(view);

Adding something via the view, then viewing only added
------
Filter:
State:  Added
Sort:   , Default: False
Current 150     thingamabob     green
Current 160     gadget  red
------

Console.WriteLine("Setting ApplyDefaultSort to true "+
  "does nothing if sort is set.");
view.RowFilter = string.Empty;
view.RowStateFilter = DataViewRowState.CurrentRows;
view.Sort = "color";
view.ApplyDefaultSort = true;
PrintData(view);

Setting ApplyDefaultSort to true 
   does nothing if sort is set.
------
Filter:
State:  CurrentRows
Sort:   color, Default: True
Current 120     thingie blue
Current 130     whatzit blue
Current 150     thingamabob     green
Current 140     geegaw  red
Current 160     gadget  red
Current 110     freeble teal
------

Console.WriteLine("Resetting Sort to empty");
view.ApplyDefaultSort = false;
view.Sort = string.Empty;
PrintData(view);

Resetting Sort to empty
------
Filter:
State:  CurrentRows
Sort:   , Default: False
Current 110     freeble teal
Current 120     thingie blue
Current 130     whatzit blue
Current 140     geegaw  red
Current 150     thingamabob     green
Current 160     gadget  red
------

Console.WriteLine("Applying default sort");
view.ApplyDefaultSort = true;
PrintData(view);

Applying default sort
------
Filter:
State:  CurrentRows
Sort:   id, Default: True
Current 110     freeble teal
Current 120     thingie blue
Current 130     whatzit blue
Current 140     geegaw  red
Current 150     thingamabob     green
Current 160     gadget  red
------

Console.WriteLine("All current rows, by color");
view.RowFilter = string.Empty;
view.RowStateFilter = DataViewRowState.CurrentRows;
view.ApplyDefaultSort = false;
view.Sort = "color";

PrintData(view);

All current rows, by color
------
Filter:
State:  CurrentRows
Sort:   color, Default: False
Current 120     thingie blue
Current 130     whatzit blue
Current 150     thingamabob     green
Current 140     geegaw  red
Current 160     gadget  red
Current 110     freeble teal
------

int index = view.Find("red");
Console.WriteLine("Found first red object at {0}", index);

Found first red object at 3

DataRowView[] rows = view.FindRows("blue");
Console.WriteLine("Blue rows");
foreach (DataRowView drv in rows)
    PrintRow(drv.Row); 

Blue rows
        120     thingie blue
        130     whatzit blue