cmiles - info

Life, Tech and Unimportant Minutiae

.net DataTable to ADO Recordset to PivotCache Recordset

Created by cmiles on 8/5/2006.

PivotTables are a powerful tool in Excel – one interesting feature is the ability to use large sets of data without having the raw data present on an Excel Worksheet or available through the connection types that Excel/Microsoft provide. This expands the number of possible datasources and frees the PivotTable from the data storage limits of a single worksheet.

The PivotCache.Recordset property is the key to this functionality, it takes an ADO recordset and uses it as the data for the PivotTable (from your language of choice!). Good stuff – but this poses an immediate problem in .net code if you are restricted to/committed to getting your data via ADO.NET – how to create the ADO recordset?

There are two possible solutions that I have found -

XML Conversion One approach to the problem is to use XML and transform the data. I think the links below provide good information and code for this method.

‘Direct’ Conversion The XML solutions above are fascinating – but in my own code I liked the approach that is covered in this article:

My coding skills are NOT good enough to easily follow the xml conversion examples – likely part of the reason that I am partial to the ‘Direct’ approach. However, even if your xml coding knowledge is vastly superior to mine, it is interesting to notice the difference in code length and complexity between the XML solutions (longer/more complex) and direct conversion method (shorter/simple!).

I have used the code below for several months in an application that: creates a datatable from a query to a user-instance of SQL Express 2005 (using ADO.NET), converts the datatable (with the code below) into an ADO recordset and pushes it into an Excel PivotTable via the RecordSet property. Performance may be an issue in some situations – in my application there is certainly a pause during the conversion process, but at this point it seems acceptable (the conversion does not seem to take any longer than getting the queried data from the DB).

							Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop

Public Class ConvertDataTableToAdoRs

Public Shared Function ConvertToRecordset(ByVal inTable As DataTable) _
As ADODB.Recordset

'This is a VB conversion of the code found here:
'Please see the original link for a C# version and to read the
'original article.

Dim result As ADODB.Recordset = New ADODB.Recordset()
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient

Dim resultFields As ADODB.Fields = result.Fields
Dim inColumns As System.Data.DataColumnCollection = inTable.Columns

For Each inColumn As DataColumn In inColumns
resultFields.Append(inColumn.ColumnName, _
TranslateType(inColumn.DataType), _
inColumn.MaxLength, _
ADODB.FieldAttributeEnum.adFldIsNullable, _

result.Open(System.Reflection.Missing.Value _
, System.Reflection.Missing.Value _
, ADODB.CursorTypeEnum.adOpenStatic _
, ADODB.LockTypeEnum.adLockOptimistic)

For Each dr As DataRow In inTable.Rows
result.AddNew(System.Reflection.Missing.Value, _

For columnIndex As Integer = 0 To inColumns.Count - 1
resultFields(columnIndex).Value = dr(columnIndex)

Return result
End Function

Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
Select Case columnType.UnderlyingSystemType.ToString()

'This is a VB conversion of the code found here:
'Please see the original link for a C# version and to read the
'original article.

Case "System.Boolean"
Return ADODB.DataTypeEnum.adBoolean

Case "System.Byte"
Return ADODB.DataTypeEnum.adUnsignedTinyInt

Case "System.Char"
Return ADODB.DataTypeEnum.adChar

Case "System.DateTime"
Return ADODB.DataTypeEnum.adDate

Case "System.Decimal"
Return ADODB.DataTypeEnum.adCurrency

Case "System.Double"
Return ADODB.DataTypeEnum.adDouble

Case "System.Int16"
Return ADODB.DataTypeEnum.adSmallInt

Case "System.Int32"
Return ADODB.DataTypeEnum.adInteger

Case "System.Int64"
Return ADODB.DataTypeEnum.adBigInt

Case "System.SByte"
Return ADODB.DataTypeEnum.adTinyInt

Case "System.Single"
Return ADODB.DataTypeEnum.adSingle

Case "System.UInt16"
Return ADODB.DataTypeEnum.adUnsignedSmallInt

Case "System.UInt32"
Return ADODB.DataTypeEnum.adUnsignedInt

Case "System.UInt64"
Return ADODB.DataTypeEnum.adUnsignedBigInt

End Select

'Note Strings are not cased and will return here:
Return ADODB.DataTypeEnum.adVarChar

End Function

Public Shared Sub DataTableToRange(ByVal anchorCell As Excel.Range, _
ByVal tableToCopy As DataTable, _
Optional ByVal tableHeader As String = "")

If tableHeader <> "" Then
anchorCell.Value = tableHeader
anchorCell = anchorCell.Offset(1, 0)
Catch ex As Exception
End Try
End If

Dim tableHeaderOffset As Integer = 0

For Each loopHeaders As DataColumn In tableToCopy.Columns
anchorCell.Offset(0, tableHeaderOffset).Value = loopHeaders.ColumnName
Catch ex As Exception
End Try

tableHeaderOffset += 1


anchorCell.Offset(1, 0).CopyFromRecordset(ConvertToRecordset(tableToCopy))

End Sub

End Class

The last function in this class is an interesting way to get information in Excel - certainly there are many other ways to get data into Excel from a DataTable without conversion to an ADO recordset. However, DataTableToRange is quick and easy with the conversion code written and ready-to-use.

Comments welcome! CM

Posts Before/After: