Features of the DataSet Object

var oAPI = null; function document.onreadystatechange() { if (document.readyState != "complete") return; oAPI = FindLMSAPI(); if (oAPI) oAPI.LMSInitialize(""); } function window.onunload() { if (oAPI) oAPI.LMSFinish(""); }

DataSet, DataTable, DataColumn, DataRow, UniqueConstraint,and ForeignKeyConstraint Object Reference

Now that you understand how to use the basic features of the DataSet and its related objects, let's look at each of the properties, events, and methods that those objects expose.

Properties of the DataSet Object

The commonly used properties of the DataSet object are shown in Table 6-5.

Table 6-5 Properties of the DataSet Object

Property

Data Type

Description

CaseSensitive

Boolean

Controls whether string comparisons are case sensitive

DataSetName

String

Indicates the name of the DataSet

DesignMode

Boolean

Indicates whether the DataSet is in design mode

EnforceConstraints

Boolean

Controls whether the DataSet will enforce the constraints that it contains

ExtendedProperties

PropertyCollection

Contains a collection of dynamic properties and values

HasErrors

Boolean

Indicates whether the DataSet contains errors

Locale

CultureInfo

Controls the locale that the DataSet will use to compare strings

Namespace

String

Contains the namespace that ADO.NET will use when writing the contents of your DataSet to XML or when loading XML data into your DataSet

Prefix

String

Contains the prefix for the namespace that ADO.NET will use when writing the contents of your DataSet to XML or when loading XML data into your DataSet

Relations

DataRelationCollection

Contains the collection of DataRelation objects for your DataSet

Tables

DataTableCollection

Contains the collection of DataTable objects for your DataSet

CaseSensitive Property

The CaseSensitive property of the DataSet controls whether string comparisons within the DataSet are case sensitive. The default value for this property is False.

Changing the value of the CaseSensitive property of the DataSet will change the value of the CaseSensitive property of DataTable objects within the DataSet whose CaseSensitive property has not been set.

The DataTable object also exposes a CaseSensitive property.

DataSetName Property

The DataSetName property contains the name of the DataSet. You can specify a value for this property in the DataSet object's constructor. If you do not specify a value in the constructor, this property will be set to "NewDataSet".

If you write the contents of your DataSet to an XML document, the DataSetName property controls the name of the root node for the XML document. The DataSetName property also controls the name of the class you'll generate if you use the XSD.exe utility to generate a class file based on the contents of an XML schema file.

DesignMode Property

The DesignMode property of the DataSet object returns a Boolean value that indicates whether the DataSet is in design mode. This property can be useful when you write code in a user control. If the DataSet is being used at design time within a component, DesignMode will return True. Otherwise, it will return False.

The DataTable object also exposes a DesignMode property. This property is read-only.

EnforceConstraints Property

You can use the EnforceConstraints property to control whether the DataSet will enforce the constraints that it contains. By default, this property is set to True. If you want to temporarily turn off constraints, you can set EnforceConstraints to False.

Setting the property to True will throw a ConstraintException if the current contents of the DataSet violate any of its constraints.

ExtendedProperties Property

You can use the DataSet object's ExtendedProperties property to store miscellaneous information. The property returns a PropertyCollection object, which is designed to store various objects. Even though you can store objects within the DataSet object's ExtendedProperties collection, you should probably stick with simple strings.

When you save the contents of a DataSet object's schema to a file or a stream, ADO.NET will write the contents of the ExtendedProperties collection as strings.

The DataTable, DataColumn, DataRelation, and Constraint objects also expose an ExtendedProperties property.

The following code shows how to add entries to a DataSet object's ExtendedProperties collection, as well as how to access the contents of the collection:

Visual Basic .NET

Dim ds As New DataSet() 'Add extended properties. ds.ExtendedProperties.Add("Prop1", "Value1") ds.ExtendedProperties.Add("Prop2", "Value2") ds.ExtendedProperties.Add("Prop3", "Value3") 'Retrieve the value of an extended property. Console.WriteLine(ds.ExtendedProperties("Prop2")) 'Retrieve and enumerate all extended properties. Dim objEnum As IDictionaryEnumerator objEnum = ds.ExtendedProperties.GetEnumerator Do While objEnum.MoveNext     Console.WriteLine(objEnum.Key & " = " & objEnum.Value) Loop

Visual C# .NET

//Requires "using System.Collections" DataSet ds = new DataSet(); //Add extended properties. ds.ExtendedProperties.Add("Prop1", "Value1"); ds.ExtendedProperties.Add("Prop2", "Value2"); ds.ExtendedProperties.Add("Prop3", "Value3"); //Retrieve the value of an extended property. Console.WriteLine(ds.ExtendedProperties["Prop2"]); //Retrieve and enumerate all extended properties. IDictionaryEnumerator objEnum; objEnum = ds.ExtendedProperties.GetEnumerator(); while (objEnum.MoveNext())     Console.WriteLine(objEnum.Key + " = " + objEnum.Value);

HasErrors Property

The HasErrors property returns a Boolean value that indicates whether any DataRow objects within the DataSet contain errors. If you're submitting batches of changes to your database and you've set the ContinueUpdateOnError property of your DataAdapter objects to True, you should check the HasErrors property of your DataSet after submitting changes to determine whether any of the update attempts failed.

The DataTable and DataRow objects also expose a HasErrors property.

For more information on handling failed update attempts, see Chapter 11.

Locale Property

Different languages and cultures employ different rules when comparing the contents of strings. By default, the DataSet will use the current culture information for your system to compare strings. You can change this behavior by setting the Locale property of your DataSet.

This property accepts a CultureInfo object, which resides in the System. Globalization namespace. For more information on the CultureInfo object, see the MSDN documentation.

Like the CaseSensitive property, the Locale property also exists on the DataTable object. Setting the Locale property of a DataSet will change the Locale property of all DataTable objects in the DataSet whose Locale property has not been set.

The DataTable object also exposes a Locale property.

The following code snippet shows how to set the Locale property of the DataSet to English (Australia):

Visual Basic .NET

Dim ds As New DataSet() ds.Locale = New System.Globalization.CultureInfo("en-AU") Console.WriteLine(ds.Locale.DisplayName)

Visual C# .NET

DataSet ds = new DataSet(); ds.Locale = new System.Globalization.CultureInfo("en-AU"); Console.WriteLine(ds.Locale.DisplayName);

Namespace and Prefix Properties

You can use the DataSet object's Namespace and Prefix properties to specify an XML namespace and prefix for your DataSet. ADO.NET will use these settings when it writes the contents of your DataSet to XML and when it loads data from an XML document into your DataSet.

The DataTable and DataColumn objects also expose Namespace and Prefix properties.

For more information on XML namespaces, see the MSDN documentation.

Relations Property

The Relations property returns a DataRelationCollection object, which contains the DataRelation objects that reside in the DataSet. You can use this property to examine existing DataRelation objects as well as to add, modify, or remove DataRelation objects.

Tables Property

You can use the Tables property to examine existing DataTable objects as well as to add, modify, or remove DataTable objects. This property returns a DataTableCollection object, which contains the DataTable objects that reside in the DataSet.

You can access a DataTable using the Tables property by supplying either the desired DataTable object's TableName property or its index within the collection. Accessing a DataTable based on its index will yield better performance.

Methods of the DataSet Object

The commonly used methods of the DataSet object are shown in Table 6-6.

Table 6-6 Methods of the DataSet Object

Method

Description

AcceptChanges

Accepts all pending changes within the DataSet

BeginInit

Used by the Visual Studio .NET designers before adding schema information to the DataSet

Clear

Removes all DataRow objects from the DataSet

Clone

Creates a new DataSet object with the same schema but with no DataRow objects

Copy

Creates a new DataSet object with the same schema and the same DataRow objects

EndInit

Used by the Visual Studio .NET designers after adding schema information to the DataSet

GetChanges

Returns a new DataSet with the same structure that contains modified rows from the original DataSet

GetXml

Returns the contents of the DataSet as an XML string

GetXmlSchema

Returns the schema for the DataSet as an XML string

HasChanges

Returns a Boolean value that indicates whether any DataRow objects in the DataSet contain pending changes

InferXmlSchema

Loads schema information from an XML schema and allows you to supply a list of namespaces whose elements you want to exclude from the DataSet object's schema

Merge

Merges data from another DataSet, DataTable, or array of DataRow objects into the existing DataSet

ReadXml

Reads XML data into your DataSet from a file, a Stream, a TextReader, or an XmlReader

ReadXmlSchema

Reads XML schema information into your DataSet from a file, a Stream, a TextReader, or an XmlReader

RejectChanges

Rejects all pending changes within the DataSet

Reset

Resets your DataSet to its original, uninitialized state

WriteXml

Writes the contents of your DataSet as XML to a file, a Stream, a TextReader, or an XmlReader

WriteXmlSchema

Writes the schema of your DataSet as XML to a file, a Stream, a TextReader, or an XmlReader

AcceptChanges and RejectChanges Methods

You can use the AcceptChanges and RejectChanges methods to accept or reject all pending changes within your DataSet.

When you modify the contents of a DataRow object, ADO.NET will mark the DataRow object as having a pending change and set the RowState property of the DataRow object to the appropriate value—Added, Modified, or Deleted. ADO.NET will also maintain both the original values and the current values for the contents of the DataRow.

If you call the AcceptChanges method on your DataSet, ADO.NET will accept all pending changes stored in the DataRow objects in your DataSet. Any rows whose RowState property is set to Added or Modified will have their RowState property set to Unchanged. Doing this will also reset the "original" values for the DataRow to the current contents of the DataRow. Any DataRow objects marked as Deleted will be removed from your DataSet when you call AcceptChanges.

When the DataAdapter object successfully submits pending changes stored in a DataRow object, it implicitly calls the AcceptChanges method on that DataRow.

Calling the RejectChanges method on your DataSet will cancel any pending changes within your DataSet. Any DataRow objects marked as Added will be removed from your DataSet. Other modified DataRow objects (RowState = Modified or Deleted) will return to their previous states.

The DataTable and DataRow objects also expose AcceptChanges and RejectChanges methods.

BeginInit and EndInit Methods

The BeginInit and EndInit methods are used by designers and aren't meant to be used directly in your code. If you create an untyped DataSet at design time using the Visual Studio .NET designers, you'll see that the designer generates code that uses these methods. The code calls the BeginInit method of the DataSet object, adds structure to the DataSet, and then calls the EndInit method.

I've tried using these methods in my code in the hope that I could offer some insight as to what they do. However, I was unable to envision any scenarios (such as adding an expression-based column before adding the column that it references) using these methods that weren't already possible.

The DataTable object also exposes BeginInit and EndInit methods.

Clear Method

You can use the DataSet object's Clear method to remove all DataRow objects from the DataSet. Using this method is faster than releasing a DataSet and then creating a new DataSet with the same structure.

The DataTable object also exposes a Clear method.

Clone and Copy Methods

You use the Copy method to create a new DataSet that contains the same structure and the same set of rows as the original DataSet. If you want to create a new DataSet object that contains the same structure but doesn't contain any rows, use the Clone method instead.

The DataTable object also exposes Clone and Copy methods.

GetChanges Method

The DataSet object's GetChanges method returns a new DataSet with the same structure as the original DataSet and also includes all rows from the original DataSet that contain pending changes. We'll discuss this feature in more depth in Chapter 11.

The DataTable object also exposes a GetChanges method.

note

The new DataSet object might also include some unmodified DataRow objects in order to conform to referential integrity constraints in the DataSet. If you have a modified child row but the parent row is unmodified, the parent row will be included in the new DataSet. If the new DataSet were to contain the child row but not the parent row, it would violate the referential integrity constraint.

GetXml and GetXmlSchema Methods

You can use the GetXml method to retrieve the contents of your DataSet, including its schema, into a string in XML format. If you want to retrieve just the schema information, use the GetXmlSchema method instead.

I'll discuss the ADO.NET XML features in more depth in Chapter 12.

HasChanges Method

The HasChanges method returns a Boolean value that indicates whether the DataSet has DataRow objects that contain pending changes.

If you're building an application that allows users to modify data in a DataSet and submit the changes to the database using a DataAdapter, you might want to check the HasChanges method. There's no reason to try to submit changes to your database if the DataSet does not contain any pending changes.

Merge Method

The DataSet object's Merge method allows you to load data from another DataSet or DataTable or an array of DataRow objects into your existing DataSet.

I'll discuss the Merge method in detail in Chapter 11.

ReadXml and WriteXml Methods

You can use the ReadXml method to load XML data into your DataSet from a file, a TextReader, a Stream, or an XmlReader. You can also control how the DataSet will read the XML using the mode parameter. This parameter accepts values from the XmlReadMode enumeration and allows you to specify options such as whether you want to read a full XML document or just an XML fragment and whether to read or ignore the XML schema.

The DataSet object also exposes a WriteXml method so that you can write the contents of your DataSet as XML. The WriteXml method offers all the same options as the ReadXml method.

I'll discuss the ADO.NET XML features in more depth in Chapter 12.

ReadXmlSchema, WriteXmlSchema, and InferXmlSchema Methods

The ReadXmlSchema and WriteXmlSchema methods are similar to their counterparts ReadXml and WriteXml, but they're designed to work with XML schemas. Like the ReadXml and WriteXml methods, these methods accept a TextReader, a Stream, an XmlReader, or a string that contains a filename for your XML data.

The InferXmlSchema method is similar to the ReadXmlSchema method, but it provides an added level of control. It lets you specify a list of namespaces whose elements you want to ignore. For more information on this difference between the features, see "Loading DataSet Schema Information from XML" in the MSDN documentation.

I'll discuss the ADO.NET XML features in more depth in Chapter 12.

Reset Method

The DataSet object's Reset method returns the DataSet to its original, uninitialized state. If you want to discard an existing DataSet and start working with a new DataSet, use the Reset method rather than create a new instance of a DataSet.

Events of the DataSet Object

The most commonly used event of the DataSet object is shown in Table 6-7.

Table 6-7 Event of the DataSet Object

Event

Description

MergeFailed

Fires if the Merge method of the DataSet generates an exception

MergeFailed Event

You can use the MergeFailed event to handle any failures that occur when you use the DataSet object's Merge method. Personally, I've been unable to cause the MergeFailed event to fire. You might have more success causing MergeFailed to fire.

Properties of the DataTable Object

The commonly used properties of the DataTable object are shown in Table 6-8.

Table 6-8 Properties of the DataTable Object

Property

Data Type

Description

CaseSensitive

Boolean

Controls whether string comparisons are case sensitive

ChildRelations

DataRelationCollection

Returns the DataRelation objects that contain child data for the DataTable

Columns

DataColumnCollection

Contains the collection of DataColumn objects for the DataTable

Constraints

ConstraintCollection

Contains the collection of Constraint objects for the DataTable

DataSet

DataSet

Returns the DataSet to which the DataTable belongs

DefaultView

DataView

Returns the DataView object that bound controls will receive for the DataTable

DesignMode

Boolean

Indicates whether the DataTable is in design mode

ExtendedProperties

PropertyCollection

Contains a collection of dynamic properties and values

HasErrors

Boolean

Indicates whether the DataTable contains errors

Locale

CultureInfo

Controls the locale that the DataTable will use to compare strings

MinimumCapacity

Integer

Controls how much memory, in rows, that the DataTable will reserve initially

Namespace

String

Contains the namespace that ADO.NET will use when it writes the contents of your DataTable to XML or when it loads XML data into your DataTable

ParentRelations

DataRelationCollection

Returns the DataRelation objects that contain parent data for the DataTable

Prefix

String

Contains the prefix for the namespace that ADO.NET will use when it writes the contents of your DataTable to XML or when it loads XML data into your DataTable

PrimaryKey

Array of DataColumn objects

Contains information about the primary key for the DataTable

Rows

DataRowCollection

Contains the collection of DataColumn objects for the DataTable

TableName

String

Contains the name of the DataTable

CaseSensitive Property

The CaseSensitive property of the DataTable controls whether string comparisons within the DataTable are case sensitive. The DataSet object also exposes a CaseSensitive property.

By default, the DataTable object's CaseSensitive property contains the same value as the CaseSensitive property of the parent DataSet object. If you set the CaseSensitive property of your DataTable, this value will override the setting for the parent DataSet object.

The default value of the CaseSensitive property for a DataTable that does not reside in a DataSet is False.

ChildRelations and ParentRelations Properties

The ChildRelations and ParentRelations properties let you examine the DataRelation objects that contain child or parent relations for the current DataTable.

Say you're working with a hierarchy of customers, orders, and order details data and you have a reference to the DataTable that contains order information. The ParentRelations collection will contain the DataRelation that relates the order DataTable to the customer DataTable. The ChildRelations collection will contain the DataRelation that relates the order DataTable to the order details DataTable.

Columns Property

You can use the Columns property to examine existing DataColumn objects as well as to add, modify, or remove DataColumn objects. This property returns a DataColumnCollection object, which contains the DataColumn objects that reside in the DataTable.

You can access a DataColumn using the Columns property by supplying the desired DataColumn object's Ordinal property or ColumnName property. As with most searches, accessing a DataColumn based on its Ordinal property will yield better performance.

Constraints Property

The Constraints property lets you examine the constraints defined for your DataTable. Like the Columns property, you can use the Constraints collection to add, modify, or remove constraints from the DataTable. This property returns a ConstraintCollection object.

You can access a Constraint using the Columns property by supplying either the desired Constraint object's ConstraintName property or its index within the collection. Performing the search using the desired Constraint object's index will yield better performance.

DataSet Property

The DataSet property returns the DataSet in which the DataTable resides. If the DataTable does not reside in a DataSet, the DataSet property returns an uninitialized object.

The DataSet property is read-only.

DefaultView Property

If you bind a control to your DataTable, the control will actually bind to the DataView object returned by the DataTable object's DefaultView property. For example, you can use the following code to apply a filter so that only the customers from Spain appear in the DataGrid bound to the DataTable. The DataTable will still contain all customers, regardless of the filter.

Visual Basic .NET

tblCustomers.DefaultView.RowFilter = "Country = 'Spain'" gridCustomers.DataSource = tblCustomers

Visual C# .NET

tblCustomers.DefaultView.RowFilter = "Country = 'Spain'"; gridCustomers.DataSource = tblCustomers;

I'll discuss the DataView object in more detail in Chapter 8.

DesignMode Property

The DesignMode property of the DataTable object returns a Boolean value that indicates whether the DataTable is in design mode. This property can be useful when you write code in a user control. If the DataTable is being used at design time within a component, DesignMode will return True. Otherwise, it will return False.

The DataSet object also exposes a DesignMode property.

The DesignMode property is read-only.

ExtendedProperties Property

The DataTable object's ExtendedProperties property returns a PropertyCollection object, which is designed to store various objects.

The DataSet, DataColumn, DataRelation, and Constraint objects also expose an ExtendedProperties property.

For more information, including a code example, see the information on the ExtendedProperties property under the earlier section titled "Properties of the DataSet Object."

HasErrors Property

The HasErrors property returns a Boolean value that indicates whether any DataRow objects within the DataTable contain errors. If you're submitting batches of changes to your database and you've set the ContinueUpdateOnError property of your DataAdapter objects to True, you should check the HasErrors property of your DataSet after you submit changes to determine whether any of the update attempts failed.

The DataSet and DataRow objects also expose a HasErrors property.

For more information on handling failed update attempts, see Chapter 11.

Locale Property

The Locale property controls how ADO.NET will compare strings within your DataTable.

The DataSet object also exposes a Locale property.

For more information, including a code example, see the information on the Locale property under the earlier section titled "Properties of the DataSet Object."

MinimumCapacity Property

If you know approximately how many rows your DataTable will contain, you can improve the performance of your code by setting the DataTable object's MinimumCapacity property prior to filling your DataTable with the results of a query.

By default, the MinimumCapacity property is set to 50, which means that ADO.NET will reserve enough memory for your DataTable to hold 50 rows of data. If you know approximately how many rows your DataTable will contain, you might be able to improve the performance of your code by setting the MinimumCapacity property to a more appropriate value. Setting this property to a lower value when you work with DataTable objects will also reduce the memory footprint of your application.

If you add more rows to the DataTable, you won't receive an out-of-memory error. ADO.NET will simply request more memory.

Namespace and Prefix Properties

You can use the DataTable object's Namespace and Prefix properties to specify an XML namespace and prefix for your DataTable. ADO.NET will use these settings when it writes the contents of your DataTable to XML and when it loads data from an XML document into your DataTable.

The DataSet and DataColumn objects also expose Namespace and Prefix properties.

For more information on XML namespaces, see the MSDN documentation.

PrimaryKey Property

The PrimaryKey property contains an array of DataColumn objects that constitute the primary key for your DataTable.

This primary key serves two purposes. First, it acts as a unique constraint. No two DataRow objects can have the same values in the primary key columns. For example, say you have a DataTable of customer information and you define the primary key based on the CustomerID DataColumn. If you add a new DataRow to your DataTable object's Rows collection and the new DataRow has a value for the CustomerID DataColumn that already exists in your DataTable, you'll receive an exception.

You can also locate a DataRow in a DataTable based on its primary key values using the Find method of the DataTable object's Rows collection. I'll discuss this feature in more detail when I discuss sorting, searching, and filtering in Chapter 8.

The following code snippet shows an example of setting the PrimaryKey property of a DataTable.

Visual Basic .NET

Dim tbl As New DataTable("Customers") tbl.Columns.Add("CustomerID", GetType(String)) tbl.Columns.Add("CompanyName", GetType(String)) tbl.PrimaryKey = New DataColumn() {tbl.Columns("CustomerID")}

Visual C# .NET

DataTable tbl = new DataTable("Customers"); tbl.Columns.Add("CustomerID", typeof(string)); tbl.Columns.Add("CompanyName", typeof(string)); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["CustomerID"]};

Rows Property

The DataTable object's Rows property returns a DataRowCollection object that contains the DataRow objects in the DataTable. You can use the Rows property to add a DataRow object to the DataTable, as well as to access any of the existing DataRow objects.

You can use only the DataRowCollection object to locate a DataRow based on its ordinal value within the DataTable. If you want to locate DataRow objects based on their primary key values or other search criteria, you can use the various methods described in Chapter 8.

TableName Property

The TableName property contains the name for the DataTable object. You can set this property in the DataTable object's constructor.

When you store the contents of your DataSet as XML, ADO.NET uses the TableName property of each DataTable as the element tag for each row of data in the DataTable.

Methods of the DataTable Object

The commonly used methods of the DataTable object are shown in Table 6-9.

Table 6-9 Methods of the DataTable Object

Method

Description

AcceptChanges

Accepts all pending changes within the DataTable

BeginInit

Used by the Visual Studio .NET designers before adding schema information to the DataTable

BeginLoadData

Turns off constraints while loading data

Clear

Removes all DataRow objects from the DataTable

Clone

Creates a new DataTable object with the same schema but with no DataRow objects

Compute

Returns the value of an aggregate expression based on the contents of your DataTable

Copy

Creates a new DataTable object with the same schema and the same DataRow objects

EndInit

Used by the Visual Studio .NET designers after adding schema information to the DataSet

EndLoadData

Reenables constraints after you've loaded data

GetChanges

Returns a new DataTable with the same structure that contains modified rows from the original DataTable

GetErrors

Returns an array that contains the DataRow objects that contain errors

ImportRow

Imports an existing DataRow into your DataTable

LoadDataRow

Adds a new DataRow to your DataTable based on the contents of an array

NewRow

Returns a new DataRow object for your DataTable

RejectChanges

Rejects all pending changes within the DataTable

Reset

Resets your DataTable to its original, uninitialized state

Select

Returns an array of DataRow objects based on the specified search criteria

AcceptChanges and RejectChanges Methods

You can use the AcceptChanges and RejectChanges methods to accept or reject all pending changes within your DataTable, respectively.

The DataSet and DataRow objects also expose AcceptChanges and RejectChanges methods. For more information on these methods, see the earlier section titled "Methods of the DataSet Object."

BeginInit and EndInit Methods

These methods are used by designers and aren't meant to be used directly in your code.

The DataSet object also exposes BeginInit and EndInit methods. For more information on these methods, see the earlier section titled "Methods of the DataSet Object."

BeginLoadData and EndLoadData Methods

If you're adding a series of DataRow objects to your DataTable object, you might be able to improve the performance of your code by using the BeginLoadData and EndLoadData methods.

Calling the BeginLoadData method turns off constraints for the DataTable. You can reenable the constraints by calling EndLoadData. If the DataTable contains rows that violate the constraints, you'll receive a ConstraintException when you call EndLoadData. To determine which rows caused the exception, you can examine the rows returned by the GetErrors method.

Clear Method

You can use the DataTable object's Clear method to remove all DataRow objects from the DataTable. Using this method is faster than releasing a DataTable and then creating a new DataTable with the same structure.

The DataSet object also exposes a Clear method.

Clone and Copy Methods

You can use the Copy method to create a new DataTable that contains the same structure and the same set of rows. If you want to create a new DataTable object that contains the same structure but doesn't contain any rows, use the Clone method instead.

The DataSet object also exposes Clone and Copy methods.

Compute Method

You can use the Compute method to perform an aggregate query on a single column in your DataTable based on the search criteria you specify.

The following code snippet demonstrates using the Compute method to count the number of orders that include chai. The code snippet also computes the total number of chai units ordered.

Visual Basic .NET

Dim strSQL, strConn As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity FROM [Order Details]" Dim da As New OleDb.OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable("Order Details") da.Fill(tbl) Dim intNumChaiOrders As Integer Dim lngNumChaiUnits As Long intNumChaiOrders = CInt(tbl.Compute("COUNT(OrderID)", _                                     "ProductID = 1")) lngNumChaiUnits = CLng(tbl.Compute("SUM(Quantity)", _                                    "ProductID = 1")) Console.WriteLine("# of orders that include chai: " & _                   intNumChaiOrders) Console.WriteLine("Total number of units ordered: " & _                   lngNumChaiUnits)

Visual C# .NET

string strSQL, strConn; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, ProductID, Quantity FROM [Order Details]"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable("Order Details"); da.Fill(tbl); int intNumChaiOrders; Int64 intNumChaiUnits; intNumChaiOrders = (int) tbl.Compute("COUNT(OrderID)",                                       "ProductID = 1"); intNumChaiUnits = (Int64) tbl.Compute("SUM(Quantity)",                                        "ProductID = 1"); Console.WriteLine("# of orders that include chai: " +                    intNumChaiOrders); Console.WriteLine("Total number of units ordered: " +                    intNumChaiUnits);

You cannot use the Compute method to compute an aggregate that involves multiple columns, such as SUM(Quantity * UnitPrice). However, you can use an expression-based column to perform the calculation between the two columns and then use that expression-based column in the Count method: SUM(ItemTotal).

The Compute method returns its results using the generic Object data type. When you perform a calculation using the Compute method, the data type that the Compute method uses to store the results might surprise you. For example, the DataType property for the Quantity column is a 16-bit integer, but the call to the Compute method returns a 64-bit integer.

If you're unsure what data type to use to store the results of the Compute method, you can use code such as the following:

Visual Basic .NET

Dim objRetVal As Object = tbl.Compute("SUM(Quantity)", _                                       "ProductID = 1") Console.WriteLine(objRetVal.GetType.ToString)

Visual C# .NET

object objRetVal = tbl.Compute("SUM(Quantity)",                                 "ProductID = 1"); Console.WriteLine(objRetVal.GetType().ToString());

GetChanges Method

The DataSet object's GetChanges method returns a new DataTable with the same structure and also includes all rows from the original DataTable that contain pending changes. I'll discuss this feature in more depth in Chapter 11.

The DataSet object also exposes a GetChanges method.

GetErrors Method

You can use the GetErrors method to access the DataRow objects that contain errors, whether those errors constitute constraint violations or failed update attempts. The GetErrors method returns an array of DataRow objects.

ImportRow, LoadDataRow, and NewRow Methods

The ImportRow method accepts a DataRow object and adds that row of data to the DataTable.

The LoadDataRow method accepts an array as its first argument. Each item in the array corresponds to an item in the DataTable object's Columns collection. The second argument in the LoadDataRow method takes a Boolean value to control the RowState of the new DataRow object. You can supply False for this parameter if you want the new DataRow to have a RowState of Added, and you can supply True if you want a RowState of Unmodified. The LoadDataRow method also returns the newly created DataRow object.

The NewRow method returns a new DataRow object for the DataTable. The new DataRow will not reside in the DataTable object's Rows collection at this point. You'll need to add the item to the Rows collection after you've populated the desired columns on the DataRow.

So which of these three methods should you use? Here are some guidelines:

  • Use the ImportRow method if you want to import a row from a different DataTable.

  • Use the LoadDataRow method if you want to add a number of new rows at a time, perhaps based on the contents of a file. Adding a row to a DataTable using LoadDataRow requires fewer lines of code than using the NewRow method.

  • Otherwise, use the NewRow method.

Reset Method

The DataTable object's Reset method returns the DataTable object to its original, uninitialized state. If you want to discard an existing DataTable and start working with a new DataTable, use the Reset method rather than create a new instance of a DataTable.

Select Method

You can use the Select method to locate a row or multiple rows in a DataTable based on various search criteria. The Select method returns an array of DataRow objects that satisfy the specified criteria.

I'll take a closer look at the Select method in detail when I discuss sorting, searching, and filtering in Chapter 8.

Events of the DataTable Object

The commonly used events of the DataTable object are shown in Table 6-10.

Table 6-10 Events of the DataTable Object

Event

Description

ColumnChanged

Fires after the contents of a column have changed

ColumnChanging

Fires just before the contents of a column change

RowChanged

Fires after the contents of a row have changed

RowChanging

Fires just before the contents of a row change

RowDeleted

Fires after a row has been deleted

RowDeleting

Fires just before a row is deleted

ColumnChanged and ColumnChanging Events

The ColumnChanged and ColumnChanging events fire each time the contents of a column in a row change. You can use these events to validate your data, enable or disable controls, and so forth.

The events include an argument of type DataColumnChangeEventArgs, which has properties such as Row and Column that you can use to determine which row and column have been changed.

Remember that if you use the events to modify the contents of the row, you might cause an infinite loop.

RowChanged and RowChanging Events

The RowChanged and RowChanging events fire when a DataRow object's contents change or its RowState property changes.

You can determine why the event fired by checking the Action property of the DataRowChangeEventArgs argument of the event. You can also access the row that's being modified using the Row property of the same argument.

RowDeleted and RowDeleting Events

The RowDeleted and RowDeleting events expose the same arguments and properties as the RowChanged and RowChanging arguments do. The only difference is that these events fire when a row is deleted from the DataTable.

Properties of the DataColumn Object

The commonly used properties of the DataColumn object are shown in Table 6-11.

Table 6-11 Properties of the DataColumn Object

Property

Data Type

Description

AllowDBNull

Boolean

Controls whether the column will accept null values

AutoIncrement

Boolean

Controls whether ADO.NET will generate new autoincrement values for the column

AutoIncrementSeed

Integer

Controls what value ADO.NET will use for the first new autoincrement value

AutoIncrementStep

Integer

Controls the value ADO.NET will use to generate subsequent autoincrement values

Caption

String

Controls the caption of the column when displayed in a bound data grid

ColumnMapping

MappingType

Controls how ADO.NET will store the contents of the column in an XML document

ColumnName

String

Contains the name of the DataColumn object

DataType

Type

Controls the data type that ADO.NET will use to store the contents of the column

DefaultValue

Object

Controls the default value that ADO.NET will use to populate this column for new rows

Expression

String

Controls how ADO.NET will generate values for expression-based columns

ExtendedProperties

PropertyCollection

Contains a collection of dynamic properties and values

MaxLength

Integer

Specifies the maximum length of the string that the column can contain

Namespace

String

Contains the namespace that ADO.NET will use when it writes the contents of the DataSet to XML or when it loads XML data into your DataSet

Ordinal

Integer

Returns the index of the DataColumn within the DataTable object's Columns collection

Prefix

String

Contains the prefix for the namespace that ADO.NET will use when it writes the contents of your DataSet to XML or when it loads XML data into your DataSet

ReadOnly

Boolean

Controls whether the contents of the column are read-only

Table

DataTable

Returns the DataTable to which the DataColumn belongs

Unique

Boolean

Controls whether ADO.NET requires that the values for the column be unique within the DataTable

AllowDBNull Property

You can use the AllowDBNull property to control whether the DataColumn will accept null values. By default, this property is set to True when you create new DataColumn objects.

Using a DataAdapter object's Fill method to create new DataColumn objects will not set the AllowDBNull property to True even if the corresponding column in the database does not accept null values. The DataAdapter will not fetch this schema information from your database when you call the Fill method. Calling the FillSchema method instead will fetch this information and apply it to new columns in your DataTable.

AutoIncrement, AutoIncrementSeed, and AutoIncrementStep Properties

You can use these properties to control how or whether ADO.NET will generate new autoincrement values for the column.

Setting the AutoIncrement property to True will force ADO.NET to generate new autoincrement values for your column. By default, this property is set to False. As with the AllowDBNull property, you must call the DataAdapter object's FillSchema method to set the AutoIncrement property to True for DataColumn objects that correspond to autoincrement columns in your database.

If you set the AutoIncrement property to True, ADO.NET will use the values in the AutoIncrementSeed and AutoIncrementStep properties to generate new autoincrement values. By default, AutoIncrementSeed is set to 0 and AutoIncrementStep is set to 1. For reasons stated earlier in the chapter, I prefer to set both of these properties to -1 when I use ADO.NET to generate new autoincrement values. Using the FillSchema method of the DataAdapter will not set the AutoIncrementSeed or AutoIncrementStep properties on new autoincrement DataColumn objects.

Earlier in the chapter, I also explained that you should not use the ADO.NET autoincrement features to generate new values for your database. Let your database handle generating the actual values, and let ADO.NET generate autoincrement values as placeholders within your DataSet.

Caption Property

If you're showing the contents of your DataTable in a bound data grid, you can use the Caption property to control the caption for the column. By default, the Caption property will return the same value as the ColumnName property. However, once you set the Caption property to a value, it will return that value rather than the value stored in the ColumnName property.

ColumnMapping Property

You can use the ColumnMapping property to control how ADO.NET will write the contents of the column when returning the data in your DataSet as XML.

The ColumnMapping property accepts values from the MappingType enumeration in the System.Data namespace. By default, the ColumnMapping property is set to Element, which means that the value of each column within your DataRow will appear in an element tag. You can also set ColumnMapping to Attribute, Hidden, or SimpleContent.

The following examples show the difference between using elements and attributes for data within your DataSet.

Using Column.ColumnMapping = MappingType.Element

<MyDataSet>   <Customers>     <CustomerID>ABCDE</CustomerID>     <CompanyName>New Customer</CompanyName>     <ContactName>New Contact</ContactName>     <Phone>425 555-1212</Phone>   </Customers> </MyDataSet>

Using Column.ColumnMapping = MappingType.Attribute

<MyDataSet>   <Customers Customer CompanyName="New Customer"               ContactName="New Contact" Phone="425 555-1212" /> </MyDataSet>

For more information on using the ADO.NET XML features, see Chapter 12.

ColumnName Property

The ColumnName property contains the name of the DataColumn. You can set this property in the DataColumn object's constructors.

DataType Property

The DataType property controls the data type that ADO.NET will use to store the contents of the column. By default, this property is set to store a string.

ADO.NET stores the data using a .NET data type. Previous data access models such as ADO store the results of queries in a data type designed to mirror the one that the database uses.

For example, SQL Server has different data types for fixed-length strings and variable-length strings, and for strings that contain single-byte characters and those that contain double-byte characters. ADO treats all these data types differently. As far as the ADO.NET DataColumn is concerned, a string is a string is a string.

The DataType property accepts a value of type Type. The following code snippet shows how to set a DataColumn object's DataType property directly and use the Add method of the DataColumnCollection object:

Visual Basic .NET

Dim col As New DataColumn("NewColumn") col.DataType = GetType(Decimal) Dim tbl As New DataTable("Products") tbl.Columns.Add("ProductID", GetType(Integer)) tbl.Columns.Add("ProductName", GetType(String)) tbl.Columns.Add("UnitPrice", GetType(Decimal))

Visual C# .NET

DataColumn col = new DataColumn("NewColumn"); col.DataType = typeof(Decimal); DataTable tbl = new DataTable("Products"); tbl.Columns.Add("ProductID", typeof(int)); tbl.Columns.Add("ProductName", typeof(string)); tbl.Columns.Add("UnitPrice", typeof(Decimal));

DefaultValue Property

You can use the DefaultValue property to generate a default value for the column in each new DataRow object.

SQL Server lets you define default values for columns in your tables. However, the DefaultValue property of the DataColumn object doesn't work quite the same way as the SQL Server feature.

When you define a default value for a SQL Server column, you supply a string that contains an expression. SQL Server evaluates that expression each time it assigns a default value to a column.

The DefaultValue property accepts a static value via the generic Object data type. For example, say you're working with order dates. You can use the DefaultValue property to specify the default value for an OrderDate column. However, the default value is static. It will return the same value tomorrow as it does today.

The DefaultValue property is handy but not nearly as flexible as the corresponding SQL Server feature.

Expression Property

You can store an expression in the Expression property, and ADO.NET will evaluate that expression any time you request the contents of the column. Setting the Expression property of a DataColumn to anything other than the default empty string will automatically set the ReadOnly property of the DataColumn to True.

The following code snippet demonstrates how to set the Expression property of a DataColumn to return the product of two other columns in the DataTable—Quantity and UnitPrice. The code also adds a new DataRow to the DataTable and displays the contents of the expression-based column in the Console window.

Visual Basic .NET

Dim tbl As New DataTable("Order Details") tbl.Columns.Add("OrderID", GetType(Integer)) tbl.Columns.Add("ProductID", GetType(Integer)) tbl.Columns.Add("Quantity", GetType(Integer)) tbl.Columns.Add("UnitPrice", GetType(Decimal)) Dim col As New DataColumn("ItemTotal", GetType(Decimal)) col.Expression = "Quantity * UnitPrice" tbl.Columns.Add(col) Dim row As DataRow = tbl.NewRow() row("OrderID") = 1 row("ProductID") = 1 row("Quantity") = 6 row("UnitPrice") = 18 tbl.Rows.Add(row) Console.WriteLine(row("ItemTotal"))

Visual C# .NET

DataTable tbl = new DataTable("Order Details"); tbl.Columns.Add("OrderID", typeof(int)); tbl.Columns.Add("ProductID", typeof(int)); tbl.Columns.Add("Quantity", typeof(int)); tbl.Columns.Add("UnitPrice", typeof(Decimal)); DataColumn col = new DataColumn("ItemTotal", typeof(Decimal)); col.Expression = "Quantity * UnitPrice" tbl.Columns.Add(col); DataRow row = tbl.NewRow(); row["OrderID"] = 1; row["ProductID"] = 1; row["Quantity"] = 6; row["UnitPrice"] = 18; tbl.Rows.Add(row); Console.WriteLine(row["ItemTotal"]);

In Chapter 7, you'll learn how to reference the contents of other DataTable objects in an expression-based column. For more information on the functions you can use in the Expression property, see the MSDN documentation.

ExtendedProperties Property

The DataColumn object's ExtendedProperties property returns a PropertyCollection object, which is designed to store various objects.

The DataSet, DataTable, DataRelation, and Constraint objects also expose an ExtendedProperties property.

For more information, including a code sample, see the information on the ExtendedProperties property in the earlier section titled "Properties of the DataSet Object."

MaxLength Property

You can use the MaxLength property to make sure that a user does not enter a longer string into your DataColumn than the database will allow.

By default, the MaxLength property is set to -1, which means that there is no maximum length for the column. As with the AllowDBNull and AutoIncrement properties, the DataAdapter will not set the MaxLength property of DataColumn objects that it creates via the Fill method. However, you can use the DataAdapter object's FillSchema method to set this property.

Namespace and Prefix Properties

You can use the DataSet object's Namespace and Prefix properties to specify an XML namespace and prefix for your DataSet. ADO.NET will use these settings when it writes the contents of your DataSet to XML and when it loads data from an XML document into your DataSet.

The DataSet and DataTable objects also expose Namespace and Prefix properties.

For more information on XML namespaces, see the MSDN documentation.

Ordinal Property

The Ordinal property returns the position of the DataColumn within the DataTable object's Columns collection. This property is read-only and will return -1 if the DataColumn object is not part of a DataTable object's Columns collection.

ReadOnly Property

You can use the ReadOnly property to control whether the contents of the column are read-only. By default, this property is set to False.

If you set the Expression property of a DataColumn, the ReadOnly property will be automatically set to True. At that point, the ReadOnly property becomes read-only.

If you attempt to change the value of a column whose ReadOnly property is set to True, ADO.NET will throw a ReadOnlyException. But even though the ReadOnly property is set to True, you can still modify the contents of the column before adding it to a DataTable object's Rows collection.

Like the AllowDBNull and AutoIncrement properties, the ReadOnly property is one of the properties that the DataAdapter will set via the FillSchema method but not via the Fill method.

Table Property

The Table property returns the DataTable to which the DataColumn object belongs. This property is read-only and returns an uninitialized DataTable if the DataColumn does not reside in a DataTable object's Columns collection.

Unique Property

You can use the Unique property to ensure that all values for a column within a DataTable are unique. By default, the Unique property is set to False.

Setting the Unique property to True will implicitly create a UniqueConstraint object for the DataTable in which the column resides. Similarly, adding a UniqueConstraint based on a single column will set the Unique property of that DataColumn to True.

If you create a unique constraint or a primary key on a collection of columns, the Unique property of each DataColumn will not be set to True because the values within the column are not necessarily unique. For example, the Order Details table in the Northwind database has a primary key based on the combination of the OrderID and ProductID columns. Neither column on its own is unique because there can be multiple entries in the table for an order and multiple orders can include the same product.

Like the AllowDBNull and AutoIncrement properties, the Unique property is one of the properties that the DataAdapter will set via the FillSchema method but not via the Fill method.

Properties of the DataRow Object

The commonly used properties of the DataRow object are shown in Table 6-12.

Table 6-12 Properties of the DataRow Object

Property

Data Type

Description

HasErrors

Boolean

Indicates whether the current row contains errors

Item

Object

Returns or sets the contents of a column

ItemArray

Array of Object

Returns or sets the contents of the row

RowError

String

Returns or sets error information for the row

RowState

DataRowState

Returns the state of the row

Table

DataTable

Returns the DataTable to which the row belongs

HasErrors Property

You can use the HasErrors property to determine whether the row contains errors. The HasErrors property returns a Boolean value and is read-only.

Item Property

The Item property allows you to examine or modify the contents of a column of information in the row. You can access the contents of the column by specifying the ordinal for the column, its name, or the DataColumn object itself.

The Item property also lets you supply a value from the DataRowVersion enumeration so that you can choose which version of the column you want to see. For example, you might want to view the original contents of a column for a row that has changed.

ItemArray Property

You can retrieve or set values for all columns in your row by using the ItemArray property. This property returns or accepts an array of type Object, where each item in the array corresponds to a column in the DataTable.

When you use the ItemArray property to change the contents of a row, you can use the appropriate keyword for your language of choice to keep from modifying certain fields. For example, Visual Basic .NET programmers would use Nothing and Visual C# .NET programmers would use null.

The following line of code modifies the contents of the second, third, and fourth columns in the row but does not modify the contents of the first or last columns. Such code is necessary when you work with DataTable objects that contain read-only columns.

Visual Basic .NET

row.ItemArray = New Object() {Nothing, 2, 3, 4, Nothing}

Visual C# .NET

row.ItemArray = new object[] {null, 2, 3, 4, null};

RowError Property

The RowError property returns a string that contains error information for the row. You can set the RowError property to a string to indicate that the row has an error.

The DataRow object's HasErrors property might return True even if the RowError property is empty. See the documentation for SetColumnError for more information.

RowState Property

The RowState property returns a value from the DataRowState enumeration to indicate the current state of the row. This property is read-only.

I discussed the various values that the RowState property can return earlier in the chapter.

Table Property

The Table property returns the DataTable to which the DataRow object belongs. This property is read-only.

There are times when a DataRow object does not reside in a DataTable object's Rows collection—for example, after the DataRow is created using DataTable.NewRow but before it is added to the DataTable object's Rows collection. However, the DataRow object's Table property will always return the DataTable to which the DataRow belongs.

Methods of the DataRow Object

The commonly used methods of the DataRow object are shown in Table 6-13.

Table 6-13 Methods of the DataRow Object

Method

Description

AcceptChanges

Accepts the pending changes stored in the DataRow

BeginEdit

Starts the editing process for the DataRow

CancelEdit

Cancels the changes made since the BeginEdit method was called

ClearErrors

Clears the errors for the DataRow

Delete

Marks the DataRow as deleted

EndEdit

Commits the changes made since the BeginEdit method was called

GetChildRows

Returns an array of child DataRow objects for the current DataRow based on a DataRelation

GetColumnError

Retrieves error information for a particular column

GetColumnsInError

Returns an array of DataColumn objects that contain errors for the current row

GetParentRow

Returns the parent DataRow for the current DataRow based on a DataRelation

GetParentRows

Returns an array of parent DataRow objects for the current DataRow based on a DataRelation

HasVersion

Returns a Boolean value to indicate whether the DataRow can return that version of data

IsNull

Indicates whether a particular column in the row contains a Null value

RejectChanges

Discards the pending changes stored in the DataRow

SetColumnError

Sets error information for a particular column in the row

SetParentRow

Changes the parent DataRow for the current DataRow based on a DataRelation

AcceptChanges and RejectChanges Methods

The DataRow object stores pending changes so that you can later submit those changes to the database. The AcceptChanges and RejectChanges methods let you accept or discard those changes, respectively.

By default, when you successfully submit pending changes to your database using a DataAdapter, the DataAdapter will implicitly call the AcceptChanges method on the DataRow object. The DataRow will then have a RowState of Unmodified.

You can discard the pending changes stored in a DataRow by calling its RejectChanges method. As with the AcceptChanges method, the DataRow object's RowState will then return Unmodified.

Say you have a row of customer data that contains a pending change. The CompanyName column originally contained Initial CompanyName but now contains New CompanyName.

If you call the AcceptChanges method, the DataRow will no longer maintain the old original value of Initial CompanyName. The DataRow will return New CompanyName regardless of whether you request the current or original value using the DataRow object's Item method.

If, instead, you call the RejectChanges method, the DataRow will no longer maintain the New CompanyName value. The DataRow will return Initial CompanyName regardless of whether you request the current or original value using the DataRow object's Item method.

To gain a better understanding of how ADO.NET uses the original values for a DataRow to submit changes to your database, see Chapter 10 and Chapter 11.

BeginEdit, CancelEdit, and EndEdit Methods

The BeginEdit, CancelEdit, and EndEdit methods allow you to store or cancel a series of changes to the DataRow. For example, you might want to let the user modify the contents of a row and then display a dialog box that gives the user the chance to accept or cancel those changes.

CancelEdit and EndEdit behave differently than AcceptChanges and RejectChanges do. The best way to explain the difference between the sets of methods is to show some sample code. The following code snippet creates a new DataRow and modifies its contents. It then calls BeginEdit, modifies the contents of the row again, and displays the various versions of the row.

Visual Basic .NET

Dim tbl As New DataTable("Customers") tbl.Columns.Add("CustomerID", GetType(String)) tbl.Columns.Add("CompanyName", GetType(String)) Dim row As DataRow 'Create a new row using the LoadDataRow method. row = tbl.LoadDataRow(New Object() {"ABCDE", _                                     "Initial CompanyName"}, True) 'Modify the contents of the DataRow. 'row.RowState will now return Modified. 'The 'Original' value for the column is "Initial CompanyName." row("CompanyName") = "New CompanyName" 'Call BeginEdit and modify the CompanyName column again. row.BeginEdit() row("CompanyName") = "Even Newer CompanyName!" 'Display the different versions of the column. Console.WriteLine("Proposed: " & _                   row("CompanyName", DataRowVersion.Proposed)) Console.WriteLine("Current:  " & _                   row("CompanyName", DataRowVersion.Current)) Console.WriteLine("Original: " & _                   row("CompanyName", DataRowVersion.Original))

Visual C# .NET

DataTable tbl = new DataTable("Customers"); tbl.Columns.Add("CustomerID", typeof(string)); tbl.Columns.Add("CompanyName", typeof(string)); DataRow row; //Create a new row using the LoadDataRow method. row = tbl.LoadDataRow(new object[] {"ABCDE",                                      "Initial CompanyName"}, true); //Modify the contents of the DataRow. //row.RowState will now return Modified. //The 'Original' value for the column is "Initial CompanyName." row["CompanyName"] = "New CompanyName"; //Call BeginEnit and modify the CompanyName column again. row.BeginEdit(); row["CompanyName"] = "Even Newer CompanyName!"; //Display the different versions of the column. Console.WriteLine("Proposed: " +                   row["CompanyName", DataRowVersion.Proposed]); Console.WriteLine("Current:  " +                   row["CompanyName", DataRowVersion.Current]); Console.WriteLine("Original: " +                   row["CompanyName", DataRowVersion.Original]);

Run the code, and you'll see that the proposed value for the column is Even Newer CompanyName!, the current value is New CompanyName, and the original value is Initial CompanyName.

You can call the EndEdit method to accept the edit. The current value of the column will be set to the proposed value. The original value for the column will remain the same.

You can call the CancelEdit method to discard the edit. The current and original values of the column will remain the same.

Keep in mind that while you're editing the contents of a row after using the BeginEdit method, the Item method will return the proposed values for the columns by default. For more information on this behavior, see the section titled "Examining the Pending Changes in a DataRow" earlier in this chapter.

ClearErrors Method

To clear all errors in a DataRow, call the object's ClearErrors method. The method clears the error information for the DataRow object as a whole as well as for each column in the row.

Delete Method

The Delete method does not actually remove a DataRow from its table's Rows collection. When you call a DataRow object's Delete method, ADO.NET marks the row as deleted so that you can later remove the corresponding row in your database by calling the DataAdapter object's Update method.

If you want to completely remove the DataRow, you can call its Delete method and then call its AcceptChanges method. You can also use the Remove method of the DataRowCollection object to accomplish the same task in a single line of code.

GetChildRows Method

You can use the GetChildRows method to access the child rows for the current DataRow. In order to use the GetChildRows method, you must supply a DataRelation or the name of a DataRelation. You can also supply a value from the DataRowVersion enumeration to control the version of the child data that you retrieve.

The GetChildRows method returns child data in an array of DataRow objects.

GetColumnError and SetColumnError Methods

To set or examine error information for a particular column in a row, you can use the GetColumnError and SetColumnError methods. You can supply a column name, its ordinal position within the DataTable, or the DataColumn object itself with either method.

You can also use SetColumnError to clear the error information for a particular column by passing an empty string as the second parameter.

GetColumnsInError Method

If the DataRow object's HasErrors property returns True, you can use the GetColumnsInError method to determine which column (or columns) in the DataRow contains error information.

The following code snippet demonstrates how you can use the GetColumnsInError method in conjunction with the GetColumnError method to return error information for a particular DataRow:

Visual Basic .NET

Dim row As DataRow  If row.HasErrors Then     Console.WriteLine("The row contains the following errors:")     Console.WriteLine("RowError: " & row.RowError)     Dim colError As DataColumn     For Each colError In row.GetColumnsInError     Console.WriteLine("Error in " & colError.ColumnName & ": " & _                       row.GetColumnError(colError))     Next colError Else     Console.WriteLine("The row does not contain errors") End If

Visual C# .NET

DataRow row;  if (row.HasErrors) {     Console.WriteLine("The row contains the following errors:");     Console.WriteLine("RowError: " + row.RowError);     foreach (DataColumn colError in row.GetColumnsInError())     Console.WriteLine("Error in " + colError.ColumnName + ": " +                       row.GetColumnError(colError)); } else     Console.WriteLine("The row does not contain errors");

GetParentRow, GetParentRows, and SetParentRow Methods

The GetParentRow and SetParentRow methods provide an easy way for you to examine or set the parent row of the current row in a DataRelation object, respectively.

Like the GetChildRows method, the GetParentRow method accepts relation information—either the name of the DataRelation or the object itself—as well as a value from the DataRowVersion enumeration to control the version of the row that the method returns. The GetParentRow method returns a DataRow object.

If the current DataRow can have multiple parent rows via a relation, you can use the GetParentRows method to access those rows. This method accepts the same parameters as the GetParentRow method, except that it returns an array of DataRow objects.

The SetParentRow method allows you to change a row's parent row in a relation. To use the method, you simply pass the row's new parent. If the current row's DataTable is the child table in multiple relations within the DataSet, you should use the overloaded method that allows you to pass the DataRelation object as the second parameter so the SetParentRow method will know which relation you want to reference.

HasVersion Method

We've discussed some of the versions of data that a DataRow object maintains—current, original, and proposed. The DataRow object does not maintain values for all these versions all the time.

For example, a DataRow whose RowState is Added will have a current version but not an original version. A DataRow whose RowState is Deleted will have an original version but not a current version.

You can use the HasVersion method to determine whether a DataRow has data for that particular version. The HasVersion method accepts a value from the DataRowVersion enumeration and returns a Boolean value that indicates whether the DataRow currently maintains that version of data.

IsNull Method

Say you're working with a DataRow that contains customer information and you want to retrieve the contents of the ContactName column into a string variable. If you use the following code, you might run into problems if the ContactName column contains a null value:

Visual Basic .NET

Dim row As DataRow  Dim strContactName As String strContactName = CStr(row("ContactName"))

Visual C# .NET

DataRow row;  string strContactName; strContactName = (string) row["ContactName"];

To avoid such problems, you can do one of two things: set up your database and DataSet so that the column does not support null values, or check the contents of the column for null values before accessing its contents.

The IsNull method can simplify the second of these options. This method accepts the name of a column, its ordinal position, or the DataColumn object itself and returns a Boolean value to indicate whether the column contains a null value.

We can change our previous code snippet to use the IsNull method, as shown here:

Visual Basic .NET

Dim row As DataRow  Dim strContactName As String If row.IsNull("ContactName") Then     strContactName = "<Null>" Else     strContactName = CStr(row("ContactName")) End If

Visual C# .NET

DataRow row;  string strContactName; if (row.IsNull("ContactName"))     strContactName = "<Null>"; else     strContactName = (string) row["ContactName"];

The IsNull method also supports a fourth overloaded method that accepts a DataColumn object and a value from the DataRowVersion enumeration. You can use this method to determine whether a particular version of a column contains a null value.

Properties of the UniqueConstraint Object

The commonly used properties of the UniqueConstraint object are shown in Table 6-14.

Table 6-14 Properties of the UniqueConstraint Object

Property

Data Type

Description

Columns

Array of DataColumn objects

Returns the columns that are part of the constraint

ConstraintName

String

Contains the name of the constraint

ExtendedProperties

PropertyCollection

Contains a collection of dynamic properties and values

IsPrimaryKey

Boolean

Indicates whether the constraint constitutes the primary key for the DataTable

Table

DataTable

Returns the DataTable to which the constraint belongs

Columns Property

The Columns property returns an array of DataColumn objects that contains the columns that make up the constraint. This property is read-only.

ConstraintName Property

You can use the ConstraintName property to examine or set the name of the UniqueConstraint.

ExtendedProperties Property

The UniqueConstraint object's ExtendedProperties property returns a PropertyCollection object, which is designed to store various objects.

The DataSet, DataColumn, DataRelation, and ForeignKeyConstraint objects also expose an ExtendedProperties property.

For more information, including a code sample, see the information on the ExtendedProperties property under the earlier section titled "Properties of the DataSet Object."

IsPrimaryKey Property

The IsPrimaryKey property returns a Boolean value that indicates whether the UniqueConstraint object is the primary key for the DataTable.

The IsPrimaryKey property is read-only. The UniqueConstraint object lets you specify whether the constraint is a DataTable object's primary key only through its constructors.

You can also set a DataTable object's primary key through its PrimaryKey property.

Table Property

The Table property returns the DataTable to which the UniqueConstraint belongs. This property is read-only.

Properties of the ForeignKeyConstraint Object

The commonly used properties of the ForeignKeyConstraint object are shown in Table 6-15.

Table 6-15 Properties of the ForeignKeyConstraint Object

Property

Data Type

Description

AcceptRejectRule

AcceptRejectRule

Controls whether the effects of a call to a parent row's AcceptChanges or RejectChanges method cascade to the child rows

Columns

Array of DataColumn objects

Returns the columns in the child table that make up the constraint

ConstraintName

String

Contains the name of the constraint

DeleteRule

Rule

Controls how or whether a deletion of a parent row cascades to the child rows

ExtendedProperties

PropertyCollection

Contains a collection of dynamic properties and values

RelatedColumns

Array of DataColumns

Returns the columns in the parent table that make up the constraint

RelatedTable

DataTable

Returns the parent table for the constraint

Table

DataTable

Returns the child table for the constraint

UpdateRule

Rule

Controls how or whether changes to the parent row cascade to the child rows

AcceptRejectRule, DeleteRule, and UpdateRule Properties

The AcceptRejectRule, DeleteRule, and UpdateRule properties control how or whether changes to a parent row cascade to the child rows.

The AcceptRejectRule property accepts values from the AcceptRejectRule enumeration. By default, the AcceptRejectRule property is set to None, which means that if you call the AcceptChanges or RejectChanges method on a row, its child rows will not be affected. If you set the AcceptRejectRule property to Cascade, the action will cascade down to the child rows defined by the ForeignKeyConstraint object.

The DeleteRule and UpdateRule properties behave in a similar fashion, but they accept values from the Rule enumeration. By default, both properties are set to Cascade, which means that the changes you make to a parent row will automatically cascade down to the child rows.

For example, if you call the Delete method on a DataRow, you're implicitly calling the Delete method on its child rows as well. Similarly, if you change the value of a key column in a DataRow, you'll implicitly change the contents of the corresponding column in the child rows.

You can set the DeleteRule and UpdateRule properties to None if you don't want to cascade changes. You can also set the properties to SetDefault or SetNull. Setting the properties to SetNull will assign null values to the related columns in child rows if the parent row is deleted or if the contents of its related columns change. The SetDefault value causes similar behavior, except that the contents of the related columns in child rows will be set to their default values.

Columns and RelatedColumns Properties

The Columns property returns an array of DataColumn objects that contain the columns in the child table that are part of the constraint. The RelatedColumns property returns the same information for the parent table.

Both properties are read-only.

ConstraintName Property

You can use the ConstraintName property to examine or set the name of the ForeignKeyConstraint.

ExtendedProperties Property

The ForeignKeyConstraint object's ExtendedProperties property returns a PropertyCollection object, which is designed to store various objects.

The DataSet, DataColumn, DataRelation, and UniqueConstraint objects also expose an ExtendedProperties property.

For more information, including a code sample, see the information on the ExtendedProperties property under the earlier section titled "Properties of the DataSet Object."

RelatedTable and Table Properties

The Table property returns the child DataTable for the constraint. The RelatedTable property returns the parent DataTable for the constraint. Both properties are read-only.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net