LDAP Querying for Hierarchical results

[dpdougla]

I am using a database table to list managers and people that report to them -- a parent/child relationship I use to populate a Treeview control. The problem is that this table is not kept updated so the data is incomplete.

My boss has asked me to use LDAP to query the Active directory to get this list. The problem is that I don't think that I can create a query as complex as needed to create the hierarchical type lists that I need. Also I don't believe that Active Directory has a fields that are "reports to" fields, or does it?

This is the query that populates the root level:

select userid, (firstname + ' ' + middlename + ' ' + lastname + ' - ' + jobtitle) as EmpName, " & _
        "active, (select count(*) FROM employees " _
        & "WHERE reportsto=hr.user_login) childnodecount FROM employees hr where reportsto = '" & ReportsTo & "' " & _
        "AND (userid IS NOT NULL) AND (userid <> '') AND (hr.active = 1) order by firstname

This is the query that populates the Sublevel (on demand):

select userid, (firstname + ' ' + middlename + ' ' + lastname + ' - ' + jobtitle) as EmpName, " & _
        "active, (select count(*) FROM employees " _
        & "WHERE reportsto=hr.userid) childnodecount FROM employees hr where reportsto = '" & ReportsTo & "' " & _
        "AND (userid IS NOT NULL) AND (userid <> '') AND (hr.active = 1) order by firstname

Is there anyway that I could create queries against the Active Directory to get the same results?

 

[jbenson001]

Try posting here:
forum774

 

[jmeckley]

LDAP is not a relational database, its a hierarchical database, so queries look a lot different.

2nd querying ldap (or any database for that matter) has nothing to do with asp.net. asp.net services http requests. that's it.

as JB suggested, try the LDAP forum.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[dpdougla]

I realize that, but it is true that you can access AD through .net with LDAP. (The company's software is all done in asp.net)

What I'll end up doing (I think) is populate a table with the AD data and query it from there. The only problem with that approach is that when active directory gets a new record or is updated or has a record deleted, the database is not up-to-date any more.

The downside is that the table will have to be updated every time AD is updated -- double work. I'd like to avoid that if I can.

I may have to create some kind of stored procedure that runs maybe once a week and polls active directory for any changes and does updates on the database table.

Any ideas?

 

[jmeckley]

yes you can access AD through .net using System.DirectoryServices (you'll need to add a reference). this is .net question, not an asp.net question. this forum is for questions concerning web development (request, response, context, webforms, mvc) not .net in general. there are vb and c# forums for that.

I may have to create some kind of stored procedure that runs maybe once a week and polls active directory for any changes and does updates on the database table.

exactly, what you need to determine is how often the transfer should run. If you use some form of automation to add users/relationships to AD then you can plug this into that routine. if it's a manual process to update AD, then there will be period of time that AD and the database are out of sync. The key is to find out what period of time is acceptable for being out of sync.

Jason Meckley
Programmer

faq855-7190
faq732-7259