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.
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.
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 valueAdded, 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.
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.
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.
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.
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.
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.
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 DataTableQuantity 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.
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 collectionfor 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.
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 informationeither the name of the DataRelation or the object itselfas 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 maintainscurrent, 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.
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.
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.