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: