Data Table Structure

for RuBoard

Certainly, the primary collection exposed by a DataSet is encapsulated in the DataTableCollection object and exposed through the Tables property. The DataTableCollection object in turn exposes DataTable objects that are used to cache the data within the DataSet . In this section, you'll learn how that data is exposed through rows and columns and how you can customize the properties of the table to assist in maintaining data integrity.

As you might imagine, the DataTable exposes some members that are similar to those found in the DataSet . The reason for this overlap is that the members of the DataSet , such as HasErrors , look at all tables within the DataSet , whereas the DataTable properties are particular to an instance. The major methods , properties, and events of the DataTable class can be seen in Table 4.1. You'll notice from Table 4.1 that the DataTable exposes more events to provide notification for changes to the data.

Table 4.1. Important DataTable Members
Member Description
  Properties
CaseSensitive Property that gets or sets whether string comparisons are case sensitive
ChildRelations Property that gets or sets a collection of relationships for the table in a DataRelationCollection object
Columns Property that gets or sets the collection of DataColumn objects associated with the table in a DataColumnCollection object
Constraints Property that gets or sets the collection of constraint objects for the table in a ConstraintCollection object
DataSet Property that gets the DataSet object this table belongs to
DefaultView Property that gets a custom view of the table to allow searching, sorting, and filtering
DisplayExpression Property that gets or sets a String used to represent the table in the user interface
ExtendedProperties A collection of custom information that resides with the DataTable in a PropertyCollection object
HasErrors Property that indicates whether there are errors in any rows of this table
MinimumCapacity Property that gets or sets the initial size of the table defaulted to 25 rows; used by the system to efficiently allocate resources
Namespace Property that gets or sets an XML namespace for the DataTable
ParentRelations Property that gets the collection of parent relationships for this table in a DataRelationCollection object
Prefix Property that gets or sets an XML prefix for the namespace for the DataTable
PrimaryKey Property that gets or sets an array of DataColumn objects that represent the primary key of the table
Rows Property that gets the collection of rows in the table exposed in a DataRowCollection object
TableName Property that gets or sets the name of the table
AcceptChanges Method that commits all changes to the DataTable
BeginLoadData Method that turns off all notifications, indexes, and constraints during the loading of data
Clear Method that removes all rows from this table
Clone Method that copies the structure but no data from the DataTable
Compute Method that computes the given expression on the current rows not excluded by filter criteria
Copy Method that copies both the structure and data of a DataTable
EndLoadData Method that turns on notifications, indexes, and constraints after loading data
GetChanges Overloaded method that returns a copy of the DataTable with only changed rows or rows that match a given DataRowState value
ImportRow Method that copies a given DataRow into the table preserving original and modified values as well as property settings
LoadDataRow Method that finds and updates a specific row if present, and creates a new one if not; used with BeginLoadData and EndLoadData
NewRow Method that returns a new DataRow with the same schema as a row in the table
RejectChanges Method that rolls back all changes made to a DataTable since it was loaded or since AcceptChanges was called ( opposite of AcceptChanges )
  Methods
Reset Method that reverts the DataTable to its original state
Select Overloaded method that returns an array of DataRow objects based on filter criteria or row state
  Events
ColumnChanged Event fired after a value in a DataColumn changes
ColumnChanging Event fired when a value in a DataColumn is being changed
RowChanged Event fired after a DataRow has been successfully changed
RowChanging Event fired when a DataRow is being changed
RowDeleted Event fired after a DataRow has been deleted from the table
RowDeleting Event fired before a row is deleted from the table

As with the DataSet , the DataTable class contains several collections, as shown in Figure 4.1. These collections are used to represent the columns, rows, constraints, relationships, and extended properties.

Figure 4.1. The DataTable object and its child collections. This diagram highlights the collections exposed by the DataTable that make up its structure.

graphics/04fig01.gif

To illustrate the use of several of the methods of the DataTable class, consider the LoadStores method shown in Listing 4.1. Here, the method accepts a DataTable as a parameter and loads it with store information found in a text file.

Listing 4.1 Loading a DataTable . This method loads a table from a text file.
 Private Sub LoadStores(ByRef stores As DataTable)   Dim storeFile As FileStream   Dim reader As StreamReader   Dim strLine As String   Dim storeValues() As String   Dim sep As String = ","   Try     storeFile = New FileStream("stores.txt", FileMode.Open)     reader = New StreamReader(storeFile)   Catch e As IOException     ' Handle file errors here   End Try   Try     stores.MinimumCapacity = 125     stores.BeginLoadData()     Do While True       strLine = reader.ReadLine()       If strLine Is Nothing Then         Exit Do       End If       storeValues = strLine.Split(sep.ToCharArray)       Dim dr As DataRow       dr = stores.LoadDataRow(storeValues, False)       dr.Item("StoreID") = System.Guid.NewGuid     Loop     stores.EndLoadData()   Catch e As DataException     ' Handle data exception (violated constraints)   Catch e As IOException     ' Handle file IO problems   Catch e As Exception     ' Handle other exceptions     Throw e   Finally     storeFile.Close()   End Try End Sub 
graphics/analysis.gif

Although Listing 3.1 in the previous chapter showed two methods that could be used to populate a DataSet programmatically, Listing 4.1 shows how you can also use the LoadDataRow method of the DataTable object directly. This is especially effective when you want to batch load a significant amount of data, as in this example.

Note

This method shows the basic use of the FileStream and StreamReader objects from the System.IO namespace to open and read the text file. As a result, this code would need the Imports System.IO statement at the top of the source file.


You'll notice in the second Try block that the MinimumCapacity property is first set to 125 to allow the common language runtime to preallocate resources for at least 125 rows. Setting this property appropriately can speed up the process of inserting data. The BeginLoadData method is then called to disable all notifications and constraints on the table to ensure that the data can be loaded without interruption.

Note

The MinimumCapacity property defaults to 25 and therefore should be used when you know that the DataTable will contain a good deal more than 25 rows because it can optimize performance. That said, worrying about setting this property ”to the extent that you incur an extra roundtrip to the data store ”doesn't make sense. The cost of the roundtrip will far outweigh the savings in memory allocation for the DataTable .


The data is then read from the text file and parsed into an array of strings using the Split method of the String class. If the array contains valid values for the columns in the DataTable that match the positional order of the columns, it can then be loaded with the LoadDataRow method. The second argument specifies whether to immediately call AcceptChanges on the row, thereby marking the row as having no pending changes in the DataTable .

Immediately after loading the row, the StoreID column is populated with a GUID using the NewGuid method of the Guid structure. This ensures that the store has a unique identifier used as the primary key. Note that if BeginLoadData hadn't been previously called, the LoadDataRow method would throw a DataException and not accept the row because the text file contains only an empty string for each StoreID .

After the data has been loaded, the EndLoadData method is called to enable the constraints. At this point, if the data doesn't adhere to the constraints, a DataException is thrown and can be handled in a Catch block. In either case, the input file is closed in the Finally block. Note that the stores object is passed into the method by reference ( ByRef ), so when the method returns, the calling code will have a fully loaded DataTable that can then be synchronized with a database using a data adapter.

Dealing with Rows

In previous listings and code snippets, you have no doubt noticed that the rows in a DataTable are accessed via the Rows property, which returns a DataRowCollection object that exposes a collection of DataRow objects. Each DataRow exposes its data as a collection of values that adhere to the data types of the columns for the table and are accessible via the Item and ItemArray properties. The members of the DataRow class can be seen in Table 4.2.

Table 4.2. Important DataRow Members
Member Description
  Properties
HasErrors Property that indicates whether there are errors in any rows of this table
Item Property that gets or sets the value of a column in the row accessible via the ordinal number or the name (in C#, this property is the indexer and so it doesn't show up in the member list)
ItemArray Property that gets or sets the entire row of values through an array
RowError Property that gets or sets the error description for the row
RowState Property that gets the current DataRowState
Table Property that gets the DataTable this row belongs to
AcceptChanges Method that commits all changes to the DataRow since the last time AcceptChanges was called
BeginEdit Method that suspends event notifications so that changes can be made without triggering validation rules ”calling on a deleted row throws an exception
CancelEdit Method that cancels the edit on the current row and reverts to the original values and re-enables event notifications
ClearErrors Method that clears the errors on the row including the RowError property and errors set with SetError
Delete Method that deletes this DataRow
EndEdit Method that ends the edit of the current row ”will throw an exception if a constraint was violated or the ReadOnly or AllowDBNull properties of a column are violated
GetChildRows Overloaded method that returns an array of DataRow object by navigating the relationships between tables and optionally looking at a DataRowVersion
GetColumnError Overloaded method that returns the error description for a given column on the current row
GetColumnsInError Method that gets an array of DataColumn objects that contain errors on the current row
GetParentRow , GetParentRows Overloaded methods that return the parent DataRow or an array of parent DataRow objects for the current row based on the relation and an optional DataRowVersion
HasVersion Method that returns a Boolean indicating whether the current row contains a given DataRowVersion
IsNull Overloaded method that returns a Boolean indicating whether the given DataColumn in the current row contains a null value
RejectChanges Method that rejects all changes on the current row since AcceptChanges was last called
SetColumnError Overloaded method that sets the error description for the given DataColumn
SetParentRow Overloaded method that associates a parent DataRow with the current row

As shown in Table 4.2, you can use the members of the DataRow object to access error information in the event that an exception is thrown after calling the EndEdit method. For example, the TraceRowError method shown in Listing 4.2 accepts an array of DataRow objects and an error message, and writes each DataRow 's error information to the trace listeners collection using the WriteLine method of the Trace class.

Listing 4.2 Manipulating row information. This method prints error information to the active trace listeners.
 Private Sub TraceRowError(ByRef dr() As DataRow, ByVal message As String)   Dim dc As DataColumn   Dim strError As String   Dim strPk As String = "{"   Dim row As DataRow   For Each row In dr     If row.HasErrors Then       ' Get the primary key       For Each dc In row.Table.PrimaryKey         strPk &= ":" & dc.ColumnName & " = " & row.Item(dc).ToString       Next       strPk &= "} "       ' Log the error       Trace.WriteLine(Now & " " & message & ": DataRow  error occurred " & _      strPk & row.RowState.ToString & row.RowError)       ' Log errors for each column       For Each dc In row.GetColumnsInError         strError = row.GetColumnError(dc)         Trace.WriteLine(dc.ColumnName & " = " & strError)       Next     End If   Next End Sub 
graphics/analysis.gif

In Listing 4.2, the first For loop iterates the array of DataRow objects that presumably contain errors by checking the HasErrors property. Within the loop, the Table property is first used to access the primary key of the table to which the passed-in row belongs. Each primary key column and its value are then concatenated to the strPk String . Next, a timestamp, the custom message, the primary key information, the current state of the row, and the error message are all written to the trace listeners. Finally, the GetColumnsInError method is used to retrieve an array of DataColumn objects that contain errors, and each column name and its error description are also written to the listeners.

The TraceRowError method can then be called from a Catch block and passed the row that contains the error and any custom message, as in the following code snippet where dtTitles is a DataTable object:

 TraceRowError(dtTitles.GetErrors(), "Error saving Titles") 

As evidenced by Table 4.2, in addition to simply finding errors, you can write code that creates error conditions using the SetColumnError and RowError properties. For example, if several columns in a particular row must be correlated, you can inspect their values in the RowChanging event for the DataTable and then set the error appropriately. Listing 4.3 shows the StoresRowChanging method that is used to hook the RowChanging event of a DataTable that encapsulates information about stores. The method uses the DataRowChangeEventArgs object to inspect the reason the event was fired, and if a change occurred, enforces the rule that either the postal code or the city and state must be present.

Listing 4.3 Creating errors. This event handler creates an error if the row doesn't have the correct columns populated.
 Private Sub StoresRowChanging(ByVal sender As Object, _   ByVal e As DataRowChangeEventArgs)   ' Make sure we have a valid address   If e.Action = DataRowAction.Change Then     If e.Row.Item("PostalCode") Is DBNull.Value Then       If e.Row.Item("StateProv") Is DBNull.Value _         OrElse e.Row.Item("City") Is DBNull.Value Then         e.Row.RowError = "Must have a Postal Code or City and State"       End If     End If   End If End Sub 

By setting the RowError property to a String value, the HasErrors property of the row, the table, and the DataSet will all be automatically set to True .

Manipulating Columns

Of course, at the base level, the data in a DataTable is represented by a collection of DataColumn objects in a DataColumnCollection object exposed through the Columns property, each of which exposes a set of properties used to define the column, as shown in Table 4.3.

Table 4.3. Important DataColumn Members
Member Description
AllowDBNull Property that gets or sets a value indicating whether null values are allowed in this column
AutoIncrement Property that gets or sets a value indicating whether the value for the column is automatically generated by incrementing
AutoIncrementSeed , AutoIncrementStep Properties that get and set the starting value and increment used when auto-incrementing values
Caption Property that gets or sets the caption for the column that can be used by controls to which the column is bound
ColumnMapping Property that gets or sets the MappingType for the column controlling how it's displayed as XML
ColumnName Property that gets or sets the name of the column as it appears in the DataColumnCollection
DataType Property that gets or sets the type of data stored in the column
DefaultValue Property that gets or sets an expression used as default value when new rows are created
Expression Property that gets or sets an expression used to create a calculation or an aggregate value for the column
ExtendedProperties Property that gets or sets a collection of custom information in a PropertyCollection object
MaxLength Property that gets or sets the maximum length for a text column
Namespace Property that gets or sets the XML namespace used for the column
Ordinal Property that returns the position of the column in the DataColumnCollection
Prefix Property that gets or sets the XML prefix used for the Namespace of the column
ReadOnly Property that gets or sets a value indicating whether changes are allowed to the column once a new row has been added
Table Property that returns the DataTable to which this column belongs
Unique Property that gets or sets a value indicating that the values within the column must be unique across all rows

In the remainder of this section, you'll learn some common ways the properties of the DataColumn object are used to ensure better data integrity.

Using Column Properties

As you can see from Table 4.3, the properties of the DataColumn class mimic to some degree the kinds of information you'd find when viewing a table definition in a relational database such as SQL Server. Particularly, the AllowDBNull , ColumnName , DataType , and MaxLength properties provide the core information about a column. Typically, all these properties will be populated from the underlying data store when using a data adapter if the MissingSchemaAction property is set to the AddWithKey value of the MissingSchemaAction enumeration. If you don't use AddWithKey , only the ColumnName and DataType will be populated.

Tip

Even though you might assume that it does, creating a unique constraint or a unique index on a column in a SQL Server 2000 database and using the AddWithKey value does not automatically set the Unique property of the DataColumn to True . You must set this yourself if you want to ensure uniqueness.


The DataType property is particularly interesting because it allows data to be stored in the DataColumn based on any type in the Common Type System (CTS), including custom types you create. However, the following base set of simple types from the CTS is implicitly understood by the DataType property, and therefore will be typically used by data adapters when populating a DataSet . For example, the SqlDataAdapter will translate the SQL Server data types nvarchar , smalldatetime , and money to the CTS types String , DateTime , and Decimal , respectively.

CTS types supported by the DataType property are

  • Boolean

  • Byte

  • Char

  • DateTime

  • Decimal

  • Int16 , Int32 , Int64

  • SByte

  • Single

  • String

  • TimeSpan

  • UInt16 , UInt32 , UInt64

The primary reason these types are used is that they can be easily translated to types used in the XML Schema Definition (XSD) upon which the DataSet is based. (You'll learn more about this on Day 7, " DataSet s and XML.") The use of common types allows DataSet s to be passed to and returned from XML Web Services more easily, as you'll learn on Day 18, "ADO.NET and XML Web Services."

Regardless of which properties are automatically populated, you can then set additional properties to further conform the data. For example, the code in Listing 4.4 sets some of the properties for the columns in a DataTable that caches information from the Titles table.

Listing 4.4 Setting column properties. This code sets the Caption and other properties of the columns in a table that stores rows from the Titles table.
 With titles   .Columns("PubDate").Caption = "Publication Date"   .Columns("BulkDiscount").Caption = "Bulk Discount"   .Columns("BulkAmount").Caption = "Bulk Amount"   .Columns("ISBN").ReadOnly = True   .Columns("Title").Unique = True   .Columns("CatID").DefaultValue = _     New Guid("21B60927-5659-4AD4-A036-AB478D73E754") End With 
graphics/analysis.gif

In Listing 4.4, the Caption properties of the PubDate , BulkDiscount , and BulkAmount columns are set because the column names themselves aren't formatted properly for display. In addition, the ISBN column is set to ReadOnly because it's the primary key and therefore shouldn't be changed ( assuming this simple structure stores only a single version of each title). The Unique property of the Title column is used to ensure that the same title isn't added twice under different ISBNs. In addition, the DefaultValue property of the CatID column is set to a general programming category so that the book can be categorized at a later time.

Using Auto-Incrementing Columns

The other interesting properties exposed by the DataColumn class are those that deal with creating auto-incrementing columns: AutoIncrement , AutoIncrementSeed , and AutoIncrementStep . Together these properties mark the column as being one whose value is automatically generated each time a row is added to the DataTable and whose data type is System.Int32 , the value for the first inserted row (defaulted to ), and the increment to use for each successive row (defaulted to 1 ), respectively.

Although you might not expect it, setting the AutoIncrement property to True on a column not defined as an Integer ( Int32 ) automatically coerces the column into that data type. As mentioned previously, setting the value to True on a column that has its Expression property set throws an exception.

Typically, you'd use these properties when you want to ensure that you can uniquely identify a row in the case where a naturally occurring unique value isn't present. This often occurs with detail rows that track multiple occurrences of similar events, such as the reviews of books on the ComputeBooks Web site. You would then use the auto- incrementing column in a relationship to link two data tables in a DataSet , as you'll learn shortly. However, to ensure uniqueness, you'd need to set the Unique property of the column to True and the ReadOnly property to True for good measure so that changes can't even be attempted. As an example, consider the definition of a DataTable that stores book reviews shown in Listing 4.5.

Listing 4.5 Auto-incrementing columns. This code defines a table to hold book reviews using an auto-incrementing column.
 Dim reviews As New DataTable() reviews.TableName = "Reviews" With reviews   .Columns.Add("ISBN", GetType(String))   .Columns.Add("ReviewText", GetType(String))   .Columns.Add("Stars", GetType(Int16))   .Columns.Add("ReviewNo", GetType(Int32))   .Columns("ReviewNo").AutoIncrement = True   .Columns("ReviewNo").AutoIncrementSeed = 1   .Columns("ReviewNo").AutoIncrementStep = 1   .Columns("ReviewNo").Unique = True   .Columns("ReviewNo").ReadOnly = True   .Columns("ReviewNo").AllowDBNull = False End With 
graphics/analysis.gif

In Listing 4.5, the ReviewNo column is used to mark each row as unique. As data is inserted into the table, the first row will start with 1 and increment by 1 thereafter. Keep in mind that setting the ReadOnly property to True disallows changes to the column only after a row has been inserted into the table.

To insert a new row into the table programmatically, you can either omit the ReviewNo column altogether by not setting its value using the Item property, or, if loading from an array, you can simply place a null ( Nothing ) value in its position corresponding to the position of the column in the array. For example, to insert a row into the Reviews table, you could use the following syntax:

 reviews.Rows.Add(New Object() {"06720043X", "Good book", 3, Nothing} ) 

In the special case where the auto-increment column is the last column in the DataTable , you can simply omit the null value when loading with an array and the elements of the array will be mapped to the other columns.

Columns marked with auto-increment also work in conjunction with auto-incrementing columns generated at the database server. For example, SQL Server allows one IDENTITY column to be placed in each table that auto- increments values on the server in exactly the same way as is done in the DataTable . In fact, if you populate a DataTable from SQL Server that contains an IDENTITY column, the SqlDataAdapter object will correctly set the AutoIncrement , AutoIncrementSeed , and AutoIncrementStep properties if the AddWithKey value of the MissingSchemaAction property is set before calling the Fill method. Further, when you then insert new rows into the table, the new values will pick up where the last value left off. You'll learn more about IDENTITY values in SQL Server on Day 13, "Working with SQL Server."

Caution

As a rule, you shouldn't attempt to generate keys on both the client and the server because they can get out of sync and lead to data corruption. This can easily happen in the case of SQL Server because IDENTITY values can "get lost" or go unused due to transactions that are rolled back ”a fact that the DataTable won't be aware of. Although it's possible to set the AutoIncrementStep property to -1 in order to create negative values that won't conflict with the positive values generated at the database server, it's not recommended. For this reason ”and because auto-incrementing columns are much more difficult to use in distributed database scenarios ”you should use auto-incrementing columns in a DataTable only when they are transient, such as when used to temporarily link data tables.


Although auto-incrementing columns can be useful, a better solution for generating keys that are persisted to a database, and the one implemented in this book, is to use GUIDs as the system-assigned keys. They have the advantage of being able to be generated on either the client or the server and on multiple databases and still always retaining their uniqueness. The only downside is that you must use explicit code on the client to populate the Guid column using the NewGuid method of the System.Guid structure, as shown in Listing 3.1.

Using Expressions

One of the most powerful properties of the DataColumn is the Expression property. This property can be used to create computed columns based on the current row and child or parent rows as well as to create computed columns based on aggregate values across rows.

In the simplest case, the Expression property is used to calculate a value for a column based on other values in the row. This is typically used for numeric calculations ”for example, to compute the purchase price for an item ”or to create a more properly formatted string that combines a number of columns to, for example, create a URL that will be used in an ASP.NET Web Form. In both cases, the expression must evaluate to a String that can be cast to the type appropriate for the DataColumn . As an example, if a DataTable were used to represent customer information from the Customers table in the ComputeBooks database, a new column could be created that concatenates the first name and last name columns. This would allow the name to be easily accessible when binding the table to a control such as a grid. To create the column, you can simply use the Add method of the DataColumnCollection class like so:

 customers.Columns.Add("Name", GetType(String), "FName + ' ' + LName") 

In this case, the overloaded Add method accepts not only the name of the new column and its data type, but also the expression used to populate the Expression property. Note that the expression syntax follows the same rules as those found in Table 3.2. Another example of using the Expression property on a single row would be to create a column that holds the price of a title for customers ordering the book over the Web using the Discount column of the Titles table as follows:

 titles.Columns.Add("WebPrice", GetType(Decimal)) titles.Columns("WebPrice").Expression = "Price - ISNULL(Discount,0)" 

Here, the Expression property is set explicitly using the ISNULL function to ensure that a numeric value rather than a null is returned in the event the Discount column contains a null value (which is allowed in the database).

Note

Exceptions may be thrown either when the Expression property is initially set or when it's evaluated during execution. For example, an ArgumentException will be thrown immediately if you attempt to set an expression on a column that has its AutoIncrement or Unique properties set to True . Of course, a SyntaxErrorException will be thrown if the expression can't be parsed. Exceptions thrown during evaluation typically involve the use of functions. The CONVERT function may cause a FormatException or InvalidCastException to be thrown if the CONVERT function doesn't return a String that can be cast into the data type of the column, or if the requested cast isn't possible, respectively. In addition, the SUBSTRING , LEN , and TRIM functions can cause an ArgumentOutOfRangeException or simply an Exception if invalid arguments are passed to them or if they don't return a String .


A more sophisticated use of the Expression property involves using aggregate functions and navigating the relationships between tables in a DataSet . For example, consider the case where a DataSet contains Orders and OrderDetails tables. As you'll see shortly, these tables can be related within the DataSet through a foreign key in the same way that they were related within the relational database. In cases like this, it's often useful to summarize data from the child rows into single columns of the parent row. The Expression property can be used to do this easily as follows:

 orders.Columns.Add("ItemCount", GetType(Integer), "COUNT(Child.ISBN)") 

In this case, assume that orders is the DataTable that contains Orders . This statement then adds a new column to the DataTable to reflect the number of OrderDetails rows it's the parent for. The COUNT aggregate function is used here and is passed a column from the child table denoted with the Child identifier. In a slightly more sophisticated example, you could use computed columns to calculate the order total for an entire order by first creating a compute column on the OrderDetails table and calculating the item total by multiplying the Quantity and the UnitPrice . A column on the Orders table could then be created to sum the individual item totals and add any shipping costs that are charged as shown in the following snippet.

Note

Keep in mind that the Integer data type in Visual Basic .NET maps to the System.Int32 data type in the CTS. As a result, the GetType statement shown earlier could also have been written as GetType(Int32) .


 orders.Columns.Add("OrderTotal", GetType(Decimal)) orderDet.Columns.Add("ItemTotal", GetType(Decimal)) orderDet.Columns("ItemTotal").Expression = "Quantity * UnitPrice" orders.Columns("OrderTotal").Expression = _   "SUM(Child.ItemTotal) + ISNULL(Shipping,0)" 

Although you might think that you could combine the two expressions into a single expression that calculates the aggregate and does the multiplication on the child rows, the syntax for the Expression property doesn't allow it.

However, just as you can use aggregates with relationships, they can also be used on a single table like so:

 orders.Columns.Add("MaxDate", GetType(DateTime), "MAX(OrderDate)") 

Here, each row in the DataTable will contain a column called MaxDate with the most recent (maximum) value from the OrderDate column. Although you could create another table in the DataSet with a single row to act as the parent for all rows (using the SetParentRow method), and then use syntax as in the COUNT example above, a far easier way to efficiently calculate single aggregates is to use the Compute method of the DataTable as shown in Table 4.1. The previous snippet could then be rewritten as

 Dim maxDate As DateTime maxDate = CType(orders.Compute("MAX(OrderDate)", Nothing), DateTime) 

Note that the Compute method simply returns a value of type Object that can be cast to the appropriate data type. Also, the second argument to the Compute method, here simply set to Nothing , can be used to specify a filter expression in order to compute the aggregate over only a subset of the rows.

Where to Aggregate?

The use of aggregate functions in the Expression property raises an issue analogous to the use of the Select method of the DataTable class versus the WHERE clause in a SQL statement discussed yesterday .

Using the aggregates functions as shown in this section means that the aggregations are calculated on the client (likely the middle- tier server) where the DataSet is being populated. However, you can also calculate aggregates on the relational database server using the same basic functions ( SUM , MIN , MAX , AVG , COUNT ) in SQL statements that typically use a GROUP BY clause. As a general rule, performing calculations on the server is more efficient because with the use of the GROUP BY clause, the detail rows needn't be fully retrieved and sent to the client. Therefore, you should use the Expression property and the aggregate syntax shown here only if you already must retrieve all the detail rows and if the aggregate value can't be pre- calculated on the server.

for RuBoard


Sams Teach Yourself Ado. Net in 21 Days
Sams Teach Yourself ADO.NET in 21 Days
ISBN: 0672323869
EAN: 2147483647
Year: 2002
Pages: 158
Authors: Dan Fox

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