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.

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


Tags:
Posts Before/After: