How to iterate over a recordset?

How to iterate over a recordset?

• Michael Stapleton

  http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

   

  
  
  

• BigFive

  Maybe I'm missing something, but, why not use the ForEach loop

  K

  
  
  

• waltc

  Incidentally, this method would be useful in solving the problem I have outlined here - http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx feedbackid=79c74cef-3a4e-4dfe-8adc-784338ea4d7e where a raw file source and a datareader destination cannot co-exist.

   

  -Jamie

   

  
  
  

• frW

  I found an easier way to map the recordset object to a .Net object.
  
          Dim olead As New Data.OleDb.OleDbDataAdapter
          Dim dt As New Data.DataTable
  
          olead.Fill(dt, Dts.Variables("rstDates").Value)
  
          MsgBox("DataTable rowcount: " & dt.Rows.Count)
  
  You can then iterate through DataTable version of the Recordset object.
  
  Larry
  
  

• Sadagopan Rajaram

  KirkHaselden wrote:

  Oh, you want to process the recordset, not iterate over it. Yeah, gotcha. K

   

  Well...iterate over it in the script component in order to process it!. So, yes.

  
  
  

• Peter Högström

  Jamie,
  
  I'm assuming you are trying to iterate over a recordset object created from the recordset destination.  The object type of the recordset ends up being a COM object, so it is in fact using ADO and not ADO.NET. 
  
  I haven't tried casting the recordset to a ADO.Net object and I wouldn't expect the v2.0 framework to include any references to COM objects. 
  
  I did come up with a method to create/use an Interop Assembly of the ADO library (msado15.dll) to loop through the recordset.  I have a suspicion that the ADODB.dll was really an Interop Assembly, so this method just recreates ADODB.dll.  The nice thing about this method, is that you should be able to use the same methodology to import other COM libraries.
  

  • Create Public/Private key pair.
  • c:\Program Files\Microsoft Visual Studio 8\VC>sn -k keyPair.snk
  • Create Interop DLL based on Strong Name pair
    
  • c:\Program Files\Microsoft Visual Studio 8\VC>tlbimp "c:\Program Files\Common Files\System\ado\msado15.dll" /out: c:\windows\Microsoft.NET\Framework\v2.0.50727\
    Interop.ADODB.dll /keyfile: keypair.snk /namespace:ADODB
  • Gac the Interop Assembly
  • c:\Program Files\Microsoft Visual Studio 8\VC>gacutil /i "c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Interop.ADODB.dll
  • In Script Task, add a reference to the Interop DLL
  • Script Task Code
  •         Dim rs As ADODB.Recordset
    
            rs = CType(Dts.Variables("rstDates").Value, ADODB.Recordset)
    
            While Not rs.EOF
    
                'MsgBox(rs("DateKey").Value)
    
                rs.MoveNext()
            End While
    
    

  It may still be possible to cast the recordset object to a .Net object, which I will still research.  This method does require MDAC, but most versions of Windows come with it standard.
  
  Larry
  
  

• Musa-1

  Blimey. I didn't think I'd  have to jump thru those sorts of hoops.

  Keep me posted if you find anything in .net that will do the job.

  Thanks Larry!

  -Jamie

  
  
  

• marcooq

  I don't typically think of consuming data in the dataflow as iterating, but yeah, semantics. Sorry.

  It's funny you bring this up cuz I was just thinking about how to do the same thing actually.

  We're in mind sync. :)

  K

   

  
  
  

• SJWhiteley

  I don't mind.  I hope it helps others trying to do the same. 
  
  I got the basis of the conversion from the following pdf from microsoft.
  http://msdn.microsoft.com/vbrun/staythepath/additionalresources/upgradingvb6/chapter20.pdf
  
  Larry
  
  
  
  

• Teddydino

  Larry,

  You are THE MAN. That works a treat.

  Would you mind if I posted this up on my blog I'll credit you of course!

  -Jamie

   

  
  
  

• Geert Verhoeven

  KirkHaselden wrote:

  I don't typically think of consuming data in the dataflow as iterating, but yeah, semantics. Sorry.

  You say toe-maydo, I say tamarto!!!!  :)

   

   

  
  
  

• Akhil Jindal 007

  KirkHaselden wrote:

  Maybe I'm missing something, but, why not use the ForEach loop K

  Wait till you see the blog post - all will become clear ;)

   

  
  
  

• Azzer

  Oh, you want to process the recordset, not iterate over it. Yeah, gotcha.

  K

  
  
  
• How to iterate over a recordset?