Getting Started with the DataTable Object

 

Getting Started with the DataTable Object

The DataTable object represents tabular data as an in-memory table of rows, columns, and constraints. You must use the DataTable object to perform any disconnected data access. You can create a DataTable object explicitly by creating an instance of the DataTable class, adding DataColumn objects that define the data to be held, and adding DataRow objects containing the data. The following code, which creates a table for storing car information, demonstrates the creation of a DataTable:

image from book

Visual Basic

'Create the DataTable named "Auto"
Dim auto As New DataTable("Auto")
 

image from book

image from book

C#

//Create the DataTable named "Auto"
DataTable auto = new DataTable ("Auto");
 

image from book

This code creates an empty DataTable for which the TableName property is Auto. You can use the TableName property to access this DataTable when this DataTable is in a DataTableCollection (as detailed later in this chapter).

Adding DataColumn Objects to Create a Schema

The DataTable object is of little use until it has a schema. You create the schema by adding DataColumn objects and setting the constraints of each column. Constraints help maintain data integrity by limiting the data that can be placed in the column. The following code adds DataColumn objects to the auto DataTable object:

image from book

Visual Basic

'Add the DataColumn object using all properties
Dim vin As New DataColumn("Vin")
vin.DataType = GetType(String)
vin.MaxLength = 23
vin.Unique = True
vin.AllowDBNull = False
vin.Caption = "VIN"
auto.Columns.Add(vin)
'Add the DataColumn using defaults
Dim make As New DataColumn("Make") 'default is String
make.MaxLength = 35 'default is -1
make.AllowDBNull = False 'default is True
auto.Columns.Add(make)
Dim year As New DataColumn("Year",GetType(Integer))

year.AllowDBNull = False
auto.Columns.Add(year)
'Derived column using expression
Dim yearMake As New DataColumn("Year and Make")
yearMake.MaxLength = 70
yearMake.Expression = "Year + ' ' + Make"
auto.Columns.Add(yearMake)
 

image from book

image from book

C#

//Add the DataColumn using all properties
DataColumn vin = new DataColumn("Vin");
vin.DataType = typeof(string);
vin.MaxLength = 23;
vin.Unique = true;
vin.AllowDBNull = false;
vin.Caption = "VIN";
auto.Columns.Add(vin);
//Add the DataColumn using defaults
DataColumn make = new DataColumn("Make");
make.MaxLength = 35;
make.AllowDBNull = false;
auto.Columns.Add(make);
DataColumn year = new DataColumn("Year", typeof(int));
year.AllowDBNull = false;
auto.Columns.Add(year);
//Derived column using expression
DataColumn yearMake = new DataColumn("Year and Make");
yearMake.DataType = typeof(string);
yearMake.MaxLength = 70;
yearMake.Expression = "Year + ' ' + Make";
auto.Columns.Add(yearMake);
 

image from book

With the addition of each DataColumn object, the column's name is specified in the constructor. The DataType property is set to string for all of the DataColumn objects except the year, which is set to an integer (int) to limit this column to numeric data. The MaxLength property limits the length of the string data. Setting the Unique property to true creates an index to prevent duplication of entries. The AllowDBNull property is set to false to ensure that the column is populated with data. The Caption property isn't really a constraint; it's a string that holds the column heading when this DataTable object is used with graphic data grid controls. The yearMake DataColumn object demonstrates the creation of a calculated column, in this case by assigning an expression. Adding a calculated column is especially beneficial when data is available but not in the correct format.

Notice that some of the DataColumn objects were created without specifying values for all of the properties. The default values for the common properties are as follows:

  • DataType Default is the string type.

  • MaxLength Default is -1, which means that no check for maximum length is performed.

  • Unique Default is false, which allows the existence of duplicate values.

  • AllowDBNull Default is true, which means the DataColumn does not need to have a value.

  • Caption Default is the DataColumn object's Name property value.

Creating Primary Key Columns

The primary key of a DataTable object consists of a column or columns that make up a unique identity for each data row. In the previous example, the vehicle identification might be considered to be a unique key from which data for a given auto can be retrieved. In other situations, getting a unique key might require combining two or more fields. For example, a sales order might contain sales order details. The primary key for each of the sales order detail rows might be the combination of the order number and the line number. The PrimaryKey property must be set to an array of DataColumn objects to accommodate composite (multiple) keys. The following code shows how to set the PrimaryKey property for the auto DataTable object:

image from book

Visual Basic

'Set the Primary Key
auto.PrimaryKey = new DataColumn (){vin}
 

image from book

image from book

C#

//Set the Primary Key
auto.PrimaryKey = new DataColumn [] {vin};
 

image from book

Creating DataRow Objects to Hold Data

After the DataTable object is created and contains DataColumn objects, you can populate the DataTable object by adding DataRow objects. A DataRow object can be created only in the context of a DataTable because the DataRow must conform to constraints of the DataTable object's columns.

Adding Data to the DataTable

The DataTable object contains a Rows collection, which contains a collection of DataRow objects. There are several ways to insert data into the Rows collection.

The Rows collection has an Add method that accepts a DataRow. The Add method is overloaded to accept an array of objects instead of a DataRow object. If an array of objects is passed to the Add method, the array object count must match the quantity of DataColumn objects that the DataTable has.

The DataTable object also contains a Load method, which can be used to update existing DataRow objects or load new DataRow objects. The DataTable requires the PrimaryKey property to be set so that the DataTable object can locate the DataRow that is to be updated. The Load method accepts an array of objects and a LoadOption enumeration value. The possible values for the LoadOption enumeration are shown in Table 1-1.

Table 1-1: LoadOption Enumeration Members

LoadOption Member

Description

OverwriteChanges

Overwrites the original DataRowVersion and the current DataRowVersion and changes the RowState to Unchanged. New rows have a RowState of Unchanged as well.

PreserveChanges (default)

Overwrites the original DataRowVersion but does not modify the current DataRowVersion. New rows have a RowState of Unchanged as well.

Upsert

Overwrites the current DataRowVersion but does not modify the original DataRowVersion. New rows have a RowState of Added. Rows that had a RowState of Unchanged have a RowState of Unchanged if the current DataRowVersion is the same as the original DataRowVersion, but if they are different, the RowState is Modified.

The following code snippet demonstrates the methods of creating and adding data into the auto DataTable:

image from book

Visual Basic

'Add new DataRow by creating the DataRow first
Dim newAuto As DataRow = auto.NewRow()
newAuto ("Vin") = "123456789ABCD "
newAuto ("Make") = "Ford"
newAuto ("Year") = 2002
auto.Rows.Add(newAuto)
'Add new DataRow by simply passing the values
auto.Rows.Add("987654321XYZ", "Buick", 2001)
'Load DataRow, replacing existing contents, if existing
auto.LoadDataRow(new object() _
   { "987654321XYZ", "Jeep", 2002 },LoadOption.OverwriteChanges)
 

image from book

image from book

C#

//Add New DataRow by creating the DataRow first
DataRow newAuto = auto.NewRow();
newAuto ["Vin"] = "123456789ABCD";
newAuto ["Make"] = "Ford";
newAuto ["Year"] = 2002;
auto.Rows.Add(newAuto);
//Add New DataRow by simply adding the values
auto.Rows.Add("987654321XYZ", "Buick", 2001);
//Load DataRow, replacing existing contents, if existing
auto.LoadDataRow(new object[]
   { "987654321XYZ", "Jeep", 2002 },LoadOption.OverwriteChanges);
 

image from book

This code adds new DataRow objects to the auto DataTable. The first example explicitly creates a new DataRow using the NewRow method on the auto DataTable. The next example adds a new DataRow by simply passing the values into the auto.Rows.Add method. Remember that nothing has been permanently stored to a database. We'll cover sending updates in Chapter 2.

Viewing the State of the DataRow Object Using DataRowState

The DataRow goes through a series of states that can be viewed and filtered at any time. You can retrieve the current state of the DataRow from the RowState property, which contains a DataRowState enumeration. The DataRowState values are described in Table 1-2.

Table 1-2: RowState Enumeration Members

RowState Value

Description

Detached

The DataRow has been created but not added to a DataTable.

Added

The DataRow has been created and added to the DataTable.

Unchanged

The DataRow has not changed since the last call to the AcceptChanges method. When the AcceptChanges method is called, the DataRow changes to this state.

Modified

The DataRow has been modified since the last time the AcceptChanges method was called.

Deleted

The DataRow has been deleted using the Delete method of the DataRow.

You can view the RowState property of the DataRow at any time to determine the current state of the DataRow. Figure 1-2 shows the RowState transitions at different times in the DataRow object's life.

image from book
Figure 1-2: The RowState as it changes during the lifetime of a DataRow object

Notice that after the CustomerID is assigned a value of "AAAA", the RowState does not change to Modified. The RowState is still Added because RowState is an indicator of an action required to send an update of this data to the database. The fact that "AAAA" was placed into the CustomerID is not as important as the fact that the DataRow needs to be added to the database.

Managing Multiple Copies of Data Using the DataRowVersion

The DataRow can hold up to three versions of data: Original, Current, and Proposed. When the DataRow is loaded, it contains a single copy of the data. At that time, only the Current version exists. When the DataRow is placed into edit mode by the BeginEdit method, changes to the data are placed in a second instance of the data, called the Proposed version. When the EndEdit method is executed, the Current version becomes the Original version, the Proposed version becomes the Current version, and the Proposed version no longer exists. After EndEdit is called, there are two instances of the DataRow data, the Original and the Current versions. If the BeginEdit method is called again, the Current version of the data is copied to a third instance of the data, which is the Proposed version. Once again, calling the EndEdit method causes the Proposed version to become the Current version and the Proposed version to no longer exist.

When you retrieve data from the DataRow, the DataRowVersion can be specified as well. Table 1-3 describes the DataRowVersion enumeration members that you can specify.

Table 1-3: DataRowVersion Enumeration Members

DataRowVersion Value

Description

Current

The current value of the DataRow, even after changes have been made. This version exists in all situations, except when the DataRowState is Deleted. If the DataRowState is Deleted, an exception is thrown.

Default

If the DataRowState is Added or Modified, the default version is Current. If the DataRowState is Deleted, an exception is thrown. If the BeginEdit method has been executed, the version is Proposed.

Original

The value that was originally loaded into the DataRow, or the value at the time the last AcceptChanges method was executed. Note that this version is not populated until the DataRowState becomes Modified, Unchanged, or Deleted. If the DataRowState is Deleted, this information is retrievable. If the DataRowState is Added, a VersionNotFoundException is thrown.

Proposed

The value at the time of editing the DataRow. If the DataRowState is Deleted, an exception is thrown. If the BeginEdit method has not been explicitly executed or if BeginEdit was implicitly executed via editing a detached DataRow (an orphaned DataRow object that has not been added to a DataTable object), a VersionNotFoundException is thrown.

The DataRow contains the HasVersion method that can be used to query for the existence of a particular DataRowVersion. Using the HasVersion method means that you can check for the existence of a DataRowVersion before attempting to retrieve a version that does not exist. The following code snippet demonstrates how to retrieve a string using the RowState and the DataRowVersion. This sample uses the HasVersion method to retrieve the DataRow version information without throwing an exception.

image from book

Visual Basic

Private Function GetDataRowInfo( _
ByVal row As DataRow, ByVal columnName As String) _
   As String

   Dim retVal As String = String.Format( _
      "RowState: {0} " + VbCrLf, row.RowState)

   Dim versionString As String
   For Each versionString In [Enum].GetNames(GetType(DataRowVersion))
      Dim version As DataRowVersion = _
         CType([Enum].Parse(GetType(DataRowVersion), versionString), _
         DataRowVersion)

      If (row.HasVersion(version)) Then
         retVal += String.Format( _
            "Version: {0} Value: {1}" + vbCrLf, _
            version, row(columnName, version))
      Else
         retVal += String.Format( _
            "Version: {0} does not exist." + VbCrLf, _
            version)
      End If
   Next
   Return retVal
End Function
 

image from book

image from book

C#

private string GetDataRowInfo(DataRow row, string columnName)
{
   string retVal=string.Format(
      "RowState: {0} \r\n",
      row.RowState);

   foreach (string versionString in Enum.GetNames(typeof (DataRowVersion)))
   {
      DataRowVersion version = (
         DataRowVersion)Enum.Parse(
            typeof(DataRowVersion),versionString);

      if (row.HasVersion(version))
      {
         retVal += string.Format(
            "Version: {0} Value: {1} \r\n",
            version, row[columnName, version]);
      }
      else
      {
         retVal += string.Format(

            "Version: {0} does not exist.\r\n",
            version);

      }
   }
   return retVal;
}
 

image from book

Resetting the Slate Using the AcceptChanges and RejectChanges Methods

You can use the AcceptChanges method to reset the DataRow state to Unchanged. This method exists on the DataRow, DataTable, and DataSet objects. (I'll cover the DataSet object later in this chapter.) In a typical data environment (after data has been loaded), the DataRow state of the loaded rows is set to Added. Calling AcceptChanges on the DataTable resets the RowState of all of the DataRow objects to Unchanged. Next, if you modify the DataRow objects, their RowState changes to Modified. When it is time to save the data, you can easily query the DataTable object for its changes by using the DataTable object's GetChanges method. This method returns a DataTable that is populated only with the DataRow objects that have changed since the last time that AcceptChanges was executed. Only these changes need to be sent to the data store. After the changes have been successfully sent to the data store, you must change the state of the DataRow objects to Unchanged, which essentially indicates that the DataRow objects are in sync with the data store. You use the AcceptChanges method for this purpose. Note that executing the AcceptChanges method also causes the DataRow object's Current DataRowVersion to be copied to the DataRow object's Original version.

You use the RejectChanges method to roll back the DataRow to the point in time when you last called the AcceptChanges method. You cannot roll back to a point in time that is earlier than the last time AcceptChanges was called because the AcceptChanges method overwrites the Original DataRowVersion. In other words, you can call the RejectChanges method to roll back any changes you didn't accept. Note that both AcceptChanges and RejectChanges typically reset the RowState to Unchanged, but RejectChanges also copies the DataRow object's Original DataRowVersion to the DataRow object's Current DataRowVersion. Also, if the RowState was set to Added before you called RejectChanges, the RowState becomes Detached because the AcceptChanges method has never been executed on a DataRow that has a RowState of Added, so an Original DataRowVersion does not exist.

Using SetAdded and SetModified to Change the RowState

The DataRow contains the SetAdded and SetModified methods, which allow the DataRow object to be forcibly set to Added or Modified, respectively. These operations are useful when you want to force a DataRow to be stored in a data store that is different from the data store from which the DataRow was originally loaded. These methods can be executed only on DataRow objects whose RowState is Unchanged. An attempt to execute these methods on a DataRow object with a different RowState throws the exception called InvalidOperationException.

If the SetAdded method is executed, the DataRow object discards its Original DataRowVersion because DataRow objects that have a RowState of Added never contain an Original DataRowVersion.

If the SetModified method is executed, the DataRow object's RowState is simply changed to Modified without modifying the Original or Current DataRowVersion.

Deleting the DataRow, and What about Undeleting?

The DataRow contains a Delete method, which you can use to set the RowState of the DataRow to Deleted. DataRow objects that have a RowState of Deleted indicate rows that need to be deleted from the data store. When the DataRow object is deleted, the Current and Proposed DataRowVersion are discarded, but the Original DataRowVersion remains. The DataRow is not formally removed from the DataTable until the AcceptChanges method has been executed.

Sometimes you need to undelete a DataRow. The DataRow object doesn't have an UnDelete method, but you can use the RejectChanges method to perform an effective undelete in some situations. Be aware that executing the RejectChanges method copies the Original DataRowVersion to the Current DataRowVersion. This effectively restores the DataRow object to its state at the time the last AcceptChanges method was executed, but any subsequent changes that were made to the data prior to deleting are also discarded.

Enumerating the DataTable

It is possible to loop through the rows and columns of the DataTable by using a foreach statement. The following code shows how the rows and columns of a DataTable can be enumerated.

image from book

Visual Basic

Dim buffer As System.Text.StringBuilder
buffer = New System.Text.StringBuilder()
For Each dc As DataColumn In auto.Columns
    buffer.Append( _
        String.Format("{0,15} ", dc.ColumnName))
Next
buffer.Append(vbCrLf)
For Each dr As DataRow In auto.Rows
    For Each dc As DataColumn In auto.Columns
        buffer.Append( _
            String.Format("{0,15} ", dr(dc)))
    Next
    buffer.Append(vbCrLf)
Next
TextBox1.Text = buffer.ToString()
 

image from book

image from book

C#

System.Text.StringBuilder buffer;
buffer = new System.Text.StringBuilder();
foreach (DataColumn dc in auto.Columns)
{

   buffer.Append(
      string.Format("{0,15} ",dc.ColumnName));
}
buffer.Append("\r\n");
foreach (DataRow dr in auto.Rows)
{
   foreach(DataColumn dc in auto.Columns)
   {
      buffer.Append(
         string.Format("{0,15} ", dr[dc]));
   }
   buffer.Append("\r\n");
}
textBox1.Text = buffer.ToString();
 

image from book

The code begins by simply collecting the column names to use as a header and places this information into the StringBuilder called buffer. After the header is rendered into the buffer, the table rows and columns are enumerated, and all values are placed into the buffer. Code such as this can be used to generically walk a DataTable and perform an action on all of the data. Figure 1-3 shows the output of this code. (The TextBox font is set to Courier New to get the columns to line up.)

image from book
Figure 1-3: The output when enumerating the DataTable object's rows and columns

Copying and Cloning the DataTable

Sometimes you want to create a full copy of a DataTable. You can do this by using the DataTable object's Copy method, which copies the DataTable object's schema and data. The following code snippet shows how to invoke the Copy method.

image from book

Visual Basic

Dim copy as DataTable = auto.Copy( )
 

image from book

image from book

C#

DataTable copy = auto.Copy( );
 

image from book

On some occasions you might need a copy of the DataTable schema without data. To copy just the schema without data, you can invoke the DataTable object's Clone method. This method is commonly used when an empty copy of the DataTable is required; at a later time, DataRow objects can be added. The following code shows the Clone method.

image from book

Visual Basic

Dim clone as DataTable = auto.Clone( )
 

image from book

image from book

C#

DataTable clone = auto.Clone( );
 

image from book

Importing DataRow Objects into a DataTable

After cloning a DataTable, you might need to copy certain DataRow objects from one DataTable to another. The DataTable contains an ImportRow method, which you can use to copy a DataRow from a DataTable that has the same schema. The ImportRow method is useful in situations where the Current and Original DataRowVersion must be maintained. For example, after editing a DataTable, you might want to copy the changed DataRow objects to a different DataTable but maintain the Original and Current DataRowVersion. The ImportRow method on the DataTable object imports the DataRow objects as long as a DataRow with the same PrimaryKey does not exist. (If a duplicate DataRow exists, a ConstraintException is thrown.) The following code snippet shows the process for cloning the DataTable and then copying a single DataRow to the cloned copy.

image from book

Visual Basic

Dim clone as DataTable = auto.Clone( )
clone.ImportRow(auto.Rows(0))
 

image from book

image from book

C#

DataTable clone = auto.Clone();
clone.ImportRow(auto.Rows[0]);
 

image from book

Using the DataTable with XML Data

The contents of a DataTable can be written to an XML file or stream using the DataTable object's WriteXml method. If this method is executed with a simple filename, the auto.xml file looks like the following:

image from book

XML File

<?xml version="1.0" standalone="yes"?>
 <DocumentElement>
   <Auto>
     <Vin>123456789ABC</Vin>
     <Make>BMW</Make>
     <Year>2005</Year>
     <Year_x0020_and_x0020_Make>2005 BMW</Year_x0020_and_x0020_Make>
   </Auto>
   <Auto>
     <Vin>123456789DEF</Vin>
     <Make>BMW</Make>
     <Year>2004</Year>
     <Year_x0020_and_x0020_Make>2004 BMW</Year_x0020_and_x0020_Make>
   </Auto>
   <Auto>
     <Vin>999888777ABC</Vin>
     <Make>Ford</Make>
     <Year>2003</Year>

     <Year_x0020_and_x0020_Make>2003 Ford</Year_x0020_and_x0020_Make>
   </Auto>
   <Auto>
     <Vin>987654321XYZ</Vin>
     <Make>Jeep</Make>
     <Year>2002</Year>
     <Year_x0020_and_x0020_Make>2002 Jeep</Year_x0020_and_x0020_Make>
   </Auto>
 </DocumentElement>
 

image from book

This example uses a DocumentElement as the root element and uses repeating Auto elements for each DataRow. The data for each DataRow is nested as elements within each Auto element. Also notice that an XML element name cannot have spaces, so Year and Make were automatically converted to Year_x0020_and_x0020_Make.

You can tune the XML output by providing an XML schema or by setting properties on the DataTable and its objects. To change the name of the repeating element for the DataRow objects from Auto to Car, you can change the DataTable object's TableName. To change the Vin, Make, and Year to XML attributes, you can set each DataColumn object's ColumnMapping property to MappingType.Attribute. The "Year and Make" column is a calculated column, so its data does not need to be stored. To prevent the "Year and Make" column from storing its data, set its ColumnMapping property to MappingType.Hidden. Table 1-6 describes the MappingType enumeration members and the following snippets show the necessary code and the resulting XML file contents.

image from book

Visual Basic

auto.TableName = "Car"
auto.Columns("Vin").ColumnMapping = MappingType.Attribute
auto.Columns("Make").ColumnMapping = MappingType.Attribute
auto.Columns("Year").ColumnMapping = MappingType.Attribute
auto.Columns("Year and Make").ColumnMapping = MappingType.Hidden
 

image from book

image from book

C#

auto.TableName = "Car";
auto.Columns["Vin"].ColumnMapping = MappingType.Attribute;
auto.Columns["Make"].ColumnMapping = MappingType.Attribute;
auto.Columns["Year"].ColumnMapping = MappingType.Attribute;
auto.Columns["Year and Make"].ColumnMapping = MappingType.Hidden;
 

image from book

image from book

XML

<?xml version="1.0" standalone="yes"?>
 <DocumentElement>
   <Car Vin="123456789ABC" Make="BMW" Year="2005" />
   <Car Vin="123456789DEF" Make="BMW" Year="2004" />
   <Car Vin="999888777ABC" Make="Ford" Year="2003" />
   <Car Vin="987654321XYZ" Make="Jeep" Year="2002" />
 </DocumentElement>
 

image from book

The resulting XML file is quite compact, but one problem is that the data types aren't saved, so all data is considered to be string data. The solution is to store the XML schema with the data, which you can do by including the XmlWriteMode.WriteSchema enumeration when you are saving, as shown here:

image from book

Visual Basic

auto.WriteXml("C:\auto.xml", XmlWriteMode.WriteSchema)
 

image from book

image from book

C#

auto.WriteXml(@"C:\auto.xml", XmlWriteMode.WriteSchema);
 

image from book

image from book

XML File

<?xml version="1.0" standalone="yes"?>
 <DocumentElement>
   <xs:schema  xmlns=""
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
     <xs:element name="NewDataSet" msdata:IsDataSet="true"
    msdata:MainDataTable="Car" msdata:Locale="en-US">
       <xs:complexType>
         <xs:choice minOccurs="0" maxOccurs="unbounded">
           <xs:element name="Car">
             <xs:complexType>
               <xs:attribute name="Vin" msdata:Caption="VIN" use="required">
                 <xs:simpleType>
                   <xs:restriction base="xs:string">
                     <xs:maxLength value="23" />
                   </xs:restriction>
                 </xs:simpleType>
               </xs:attribute>
               <xs:attribute name="Make" use="required">
                 <xs:simpleType>
                   <xs:restriction base="xs:string">
                     <xs:maxLength value="35" />
                   </xs:restriction>
                 </xs:simpleType>
               </xs:attribute>
               <xs:attribute name="Year" type="xs:int" use="required" />
               <xs:attribute name="Year_x0020_and_x0020_Make"
                  msdata:ReadOnly="true"
                  msdata:Expression="Year + ' ' + Make" use="prohibited">
                 <xs:simpleType>
                   <xs:restriction base="xs:string">
                     <xs:maxLength value="70" />
                   </xs:restriction>
                 </xs:simpleType>
               </xs:attribute>
             </xs:complexType>
           </xs:element>
         </xs:choice>
       </xs:complexType>
       <xs:unique msdata:TableNamespace="" name="Constraint1"
          msdata:PrimaryKey="true">

         <xs:selector xpath=".//Car" />
         <xs:field xpath="@Vin" />
       </xs:unique>
     </xs:element>
   </xs:schema>
   <Car Vin="123456789ABC" Make="BMW" Year="2005" />
   <Car Vin="123456789DEF" Make="BMW" Year="2004" />
   <Car Vin="999888777ABC" Make="Ford" Year="2003" />
   <Car Vin="987654321XYZ" Make="Jeep" Year="2002" />
 </DocumentElement>
 

image from book

With the XML schema included in the file, the data types are defined. Notice that the XML schema also includes the maximum length settings for Vin and Make. A DataTable can be loaded with this XML file, and the resulting DataTable will be the same as the DataTable that was saved to the file. The following code snippet reads the XML file into a new DataTable object.

image from book

Visual Basic

Dim xmlTable as new DataTable()
xmlTable.ReadXml("C:\auto.xml")
 

image from book

image from book

C#

DataTable xmlTable = new DataTable();
xmlTable.ReadXml(@"C:\auto.xml");
 

image from book

Although the data for the "Year and Make" column was not saved, the column data is populated because this column is calculated and the schema contains the expression to re-create this column data.

Using the DataView as a Window into a DataTable

The DataView object provides a window into a DataTable. The DataView object can be sorted and filtered. A DataTable can have many DataView objects assigned to it, allowing the data to be viewed in many different ways without requiring it to be re-read from the database. The Sort, RowFilter, and RowStateFilter properties on the DataView object can be combined as needed. You can use the DataView object's AllowDelete, AllowEdit, and AllowNew properties to constrain user input.

Internally, the DataView object is essentially an index. You can provide a sort definition to sort the index in a certain order, and you can provide a filter to simply filter the index entries.

Ordering Data Using the Sort Property

The Sort property requires a sort expression. The default order for the sort is ascending, but you can specify ASC or DESC with a comma-separated list of columns to be sorted. The following code snippet shows how a DataView is created on the auto DataTable with a compound sort on the Make column in ascending order and on the Year column in descending order.

image from book

Visual Basic

Dim view as new DataView(auto)
view.Sort = "Make ASC, Year DESC"
 

image from book

image from book

C#

DataView view = new DataView(auto);
view.Sort = "Make ASC, Year DESC";
 

image from book

Narrowing the Search Using the RowFilter and RowStateFilter Properties

The filters comprise a RowFilter and a RowStateFilter. The RowFilter is set to a SQL WHERE clause without the word "WHERE". The following code shows a filter on the Make column for cars beginning with the letter B and on the Year column for cars newer than 2003.

image from book

Visual Basic

Dim view as new DataView(auto)
view.RowFilter = "Make like 'B%' and Year > 2003"
 

image from book

image from book

C#

DataView view = new DataView(auto);
view.RowFilter = "Make like 'B%' and Year > 2003";
 

image from book

The RowStateFilter provides a filter base on the DataRow object's RowState property. This filter provides an extremely easy method of retrieving specific version information within the DataTable. The RowStateFilter requires the use of DataViewRowState enumeration values, which are shown in Table 1-4. The DataViewRowState enumeration is a bit-flag enumeration, which means you can use the OR operator on the enumeration members. For example, the default RowState filter value is set to display multiple states by using the OR operator to combine the Unchanged, Added, and ModifiedCurrent enumeration values.

Table 1-4: DataViewRowState Enumeration Members

DataViewRowState Value

Description

Added

Retrieves the Current DataRowVersion of DataRow objects that have a RowState of Added.

CurrentRows

Retrieves all DataRow objects that have a Current DataRowVersion.

Deleted

Retrieves the Original DataRowVersion of DataRow objects that have a RowState of Deleted.

ModifiedCurrent

Retrieves the Current DataRowVersion of DataRow objects that have a RowState of Modified.

ModifiedOriginal

Retrieves the Original DataRowVersion of DataRow objects that have a RowState of Modified.

None

Clears the RowStateFilter property.

OriginalRows

Retrieves the DataRow objects that have an Original DataRowVersion.

Unchanged

Retrieves DataRow objects that have a RowState of Unchanged.

The following code sample shows the use of the RowStateFilter to retrieve the DataRow objects that have a RowState of Deleted.

image from book

Visual Basic

Dim view as new DataView(auto)
view.RowFilter = "Make like 'B%' and Year > 2003"
view.RowStateFilter = DataViewRowState.Deleted
 

image from book

image from book

C#

DataView view = new DataView(auto);
view.RowFilter = "Make like 'B%' and Year > 2003";
view.RowStateFilter = DataViewRowState.Deleted;
 

image from book

Enumerating the DataView

The procedure for walking the DataView is similar to that for enumerating the DataTable, except the objects are different. The following code can be used to enumerate the rows and columns of a DataView.

image from book

Visual Basic

Dim view as new DataView(auto)
view.RowFilter = "Make like 'B%' and Year > 2003"
view.RowStateFilter = DataViewRowState.Deleted
Dim buffer As New System.Text.StringBuilder()
For Each dc As DataColumn In auto.Columns
    buffer.Append( _
        String.Format("{0,15} ", dc.ColumnName))
Next
buffer.Append(vbCrLf)
For Each dr As DataRowView In view
    For Each dc As DataColumn In auto.Columns
        buffer.Append( _
            String.Format("{0,15} ", dr.Row(dc)))
    Next
    buffer.Append(vbCrLf)
Next
TextBox1.Text = buffer.ToString()
 

image from book

image from book

C#

DataView view = new DataView(auto);
view.Sort = "Make ASC, Year DESC";
view.RowFilter = "Make like 'B%' and Year > 2003";

System.Text.StringBuilder buffer;
buffer = new System.Text.StringBuilder();
foreach (DataColumn dc in auto.Columns)
{
   buffer.Append(
      string.Format("{0,15} ", dc.ColumnName));
}
buffer.Append("\r\n");
foreach (DataRowView dr in view)
{

   foreach (DataColumn dc in auto.Columns)
   {
      buffer.Append(
         string.Format("{0,15} ", dr.Row[dc]));
   }
   buffer.Append("\r\n");
}
textBox1.Text = buffer.ToString();
 

image from book

This code creates a DataView based on the Make, beginning with the letter "B" and the Year being greater than 2003, sorted by Make ascending and Year descending, and formats the enumeration in a text box.

Exporting a DataView to a New DataTable

A DataView object can be used to export data from one DataTable object to another. This can be especially useful when a user-defined set of filters is applied and the user wants to convert the view that is seen into a new DataTable. Exporting to a new DataTable is done with the DataView object's ToTable method, as shown here.

image from book

Visual Basic

'here is the method signature
'Public Function ToTable(tableName as String, distinct as Boolean, _
'         ParamArray columnNames() as String) as System.Data.DataTable
Dim export as DataTable = view.ToTable( _
"MyAutoTable", true, "Vin", "Make", "Year")
 

image from book

image from book

C#

//here is the method signature
//DataTable DataView.ToTable(string tableName,
//         bool distinct, params string[] columns)
DataTable export = view.ToTable(
"MyAutoTable", true, "Vin", "Make", "Year");
 

image from book

This code snippet exports the data that is seen in the DataView called "view" to a new DataTable object named "MyAutoTable". The boolean true is used to indicate whether the distinct values (which filter out duplicate values) or all values should be shown. The last parameters are an array of strings that represent the columns to include in the output.