.net DataTable to ADO Recordset to PivotCache Recordset

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.

http://support.microsoft.com/kb/316337/
http://www.codeproject.com/dotnet/ADOConversion.asp
http://codebetter.com/blogs/brendan.tompkins/archive/2004/04/27/12229.aspx

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

http://www.codeproject.com/cs/database/DataTableToRecordset.asp

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:
    'http://www.codeproject.com/cs/database/DataTableToRecordset.asp
    '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, _
          Nothing)
    Next
 
    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, _
                System.Reflection.Missing.Value)
 
      For columnIndex As Integer = 0 To inColumns.Count - 1
        resultFields(columnIndex).Value = dr(columnIndex)
      Next
    Next
 
    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:
      'http://www.codeproject.com/cs/database/DataTableToRecordset.asp
      '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
      Try
        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
      Try
        anchorCell.Offset(0, tableHeaderOffset).Value = loopHeaders.ColumnName
      Catch ex As Exception
      End Try
 
      tableHeaderOffset += 1
 
    Next
 
    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

Advertisements

5 thoughts on “.net DataTable to ADO Recordset to PivotCache Recordset

  1. Geco

    This is GREAT code!!!!!; I was looking for this long time ago
    I had a problem when, in some field; there are special caracters like “�”(This char come for other systems), but it was easy to solve

    Reply
  2. cmiles Post author

    Geco – Glad this was helpful and that you were able to easily solve your special characters problem. I have made some small changes over the years, but I am still using this basic solution!

    CM

    Reply
  3. Thomas

    This code looks really great, but I don´t know, how to create with the ADO Recordset a PivotCache or PivotTable. Can you give an example how to do this?
    Thanks in advanced!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s