Link to home
Create AccountLog in
Avatar of plq
plqFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VB.net 2.0 - OleDB.Command does not have a Page Size property

First some background. When you extract data from active directory, you can use an OLEDB connection with the ADSDSOObject Provider. Here's a typical VBS Script

    Dim con
    set con = CreateObject("ADODB.Connection")
    con.ConnectionString = "Provider=adsdsoobject;Encrypt Password=true;user id=testing\administrator;password=xxxxxxx;"
    if con.Properties("User ID") <> "" then
       con.Properties("User ID") = con.Properties("User ID")         ' get strange error if you dont do this !
    end if
    con.Open "Active Directory Provider"
    Dim objCommand
    set objcommand = CreateObject("ADODB.Command")
    set objCommand.ActiveConnection = con
     sqltext = " select  Name,ADsPath,PersonalTitle,Displayname,UserPrincipalName,sAMAccountName, MAIL, sn, givenName, department, description, homePhone, initials,"
     SQLText = SQLText & "desktopProfile, whenCreated, whenChanged "
     SQLText = SQLText & " FROM 'LDAP://03TEST01/DC=TESTING,DC=MYCO,DC=NET' "                  'OU=HEADQUARTERS,
     SQLText = SQLText & " WHERE objectClass='User' "

    objCommand.CommandText = SQLTEXT
    Dim rs
    set rs = CreateObject("ADODB.Recordset")
    rs.Open objCommand
    Dim sFile
    sfile = "c:\adotemp.xml"
    msgbox "about to save"
    rs.Save sFile, 1
    rs.Close
    con.Close

Active Directory has a wonderful feature where you only get the first 1000 rows back. Setting "Page Size" in the above query seems to solve that problem. Its discussed here:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx


Now. In .NET we don't really want to use ADODB classes, but I can't see anyway of setting "page size" in an OLEDB command object. Any ideas? I've attached the .net equivalent code, showing the non-ado code that I want to modify to use an OLEDB command object (else side of the IF) and showing hte ado code in .net that works.

Thanks

mdsSource = New DataSet
 
                    If InStr(sConnect, "provider=adsdsoobject") > 0 Then
 
                        Dim con As Object
                        con = CreateObject("ADODB.Connection")
                        con.ConnectionString = sConnect
                        con.Properties("User ID") = con.Properties("User ID").Value
                        con.Open("Active Directory Provider")
                        Dim objCommand As Object
                        objCommand = CreateObject("ADODB.Command")
                        objCommand.ActiveConnection = con
 
                        objCommand.Properties("Page Size") = 1000
                        objCommand.Properties("Searchscope") = 2
 
                        objCommand.CommandText = sSql
                        Dim rs As Object
                        rs = CreateObject("ADODB.Recordset")
                        rs.Open(objCommand)
                        Dim x As New XMLToXML
                        Dim sXML As String = x.ADOToXML(rs, "root")
                        rs.Close()
                        con.Close()
 
                        Dim s As New System.IO.StringReader(sXML)
                        mdsSource.ReadXml(s)
 
                    Else
                        mcnSource.ConnectionString = sConnect
                        mcnSource.Open()
                        mdaSource = New OleDb.OleDbDataAdapter(sSql, mcnSource)
                        mdaSource.Fill(mdsSource)
                    End If

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of plq

ASKER

Yes, but this is a generic product where the connection string and sql are set at runtime. So I was hoping for a solution tweak that lets me use ADSDSOObject with OleDb

But I will certainly explore that route if there is no other way

thanks
The only pagesize I discovered for so far, is by setting it on the level of datagrid, ... (so on controls, not on the oleDbCommand)
Avatar of plq

ASKER

Yes. I wonder how it works with ADODB. The impression I get from all the articles on this suggests that setting page size  = 1000, where the AD limit is 1000, would actually work on up to 1,000,000 AD records. So I wonder if behind the scenes its retrieving 1 page at a time, and setting pagesize to 1000 allows it to do that 1000 times - perhaps something like the old "multiple recordsets" thing in ADO (thats going back a few years).

Avatar of Sancler
Sancler

I'm not sure if this would fill the bill, but check it out -
DbDataAdapter.Fill Method (Int32, Int32, DataTable[]) - at

http://msdn2.microsoft.com/en-us/library/0z5wy74x(VS.80).aspx

As DbDataAdapter is the base class for OleDbDataAdapter, it applies for OleDb.  You'd need to keep re-running, changing the startrecord argument.

Roger
Avatar of plq

ASKER

Thanks for that. I was really hopeful on that one.. this is what I tried, but its still only returning 1000 rows

                        mcnSource.ConnectionString = sConnect
                        mcnSource.Open()
                        mdaSource = New OleDb.OleDbDataAdapter(sSql, mcnSource)
                        mdsSource.Tables.Add("root")
                        mdaSource.Fill(0, 10000, mdsSource.Tables(0))

I also tried with a 10, it works to cut the records down, but it never goes above 1000, which obviously means its something in the data source.
Any joy with

                        mdaSource.Fill(0, 1000, mdsSource.Tables(0))
                        mdaSource.Fill(1000, 1000, mdsSource.Tables(0))
                        mdaSource.Fill(2000, 1000, mdsSource.Tables(0))
                        'etc

in a loop with a count - derived from .GetScalar - of the source size?  That seems to be the sort of "con" that's working on the ADODB side.  But it would depend on the records having, and the various components recognising, some sort of primary key or each new fill would just overwrite the previous one.

And I wonder - this is all just brain-storming - if it's the OelDb driver, rather than the datasource itself.  What is that?

Roger
Avatar of plq

ASKER

Tried this, but the second+ iteration just returns 0, so same problem. And of course mdsSource.Tables(0).Rows.Count = 1000

                        mcnSource.ConnectionString = sConnect
                        mcnSource.Open()
                        Dim cmd As New OleDb.OleDbCommand(sSql, mcnSource)
                        mdaSource = New OleDb.OleDbDataAdapter(cmd)
                        mdsSource.Tables.Add("root")
                        Dim lRows As Integer = 0
                        Dim lIndex As Integer = 0
                        Do
                            lRows = mdaSource.Fill(lIndex * 1000, 1000, mdsSource.Tables(0))
                            lIndex += 1
                        Loop Until lRows = 0
Avatar of plq

ASKER

Its the AD itself which has a setting of 1000 rows, but ADODB seems to be able to work around it just by setting that Page Size property. There seems to be no equivalent "properties" collection in OleDB
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of plq

ASKER

Thanks again- tried it and it didnt work.

I will try some other forums and post back here if I find a solution.
Avatar of plq

ASKER

thanks
Avatar of plq

ASKER

There doesnt seem to be a solution in OLEDB, so dhaest's suggestion of using ActiveDirectory objects (DirectoryEntry class) in .net is the way to go

thanks for helping