去容易对我来说，这是我的第一个问题;）

Go easy on me, this is my first question ;)

我已经花了很多时间寻找，但我还没有找到我要找的。我有一个基于企业内部网的报告工具（VB.Net + ASP.Net集成Windows身份验证），看起来用户和管理人员从SQL Server 2005表卷起到经理级别的报告。

I've spent a lot of time looking, but I haven't found what I'm looking for. I've got an intranet based reporting tool (VB.Net + ASP.Net Integrated Windows Authentication) that looks up users and managers from a SQL Server 2005 table to roll up the reporting to manager level.

这台目前手动维护，我一直在问，使其更有活力，因为它会是按比例放大远远更多的用户。因此，我期待与Active Directory链接，以创建一个组织表的后端，这将取代目前的人工输入的。

This table is currently manually maintained and I've been asked to make it more dynamic as it is going to be up-scaled for far more users. Therefore I'm looking to link in with the Active Directory to create an Org table in the back end which will replace the current manually entered one.

我很舒服从AD获取用户数据，但不敢肯定，以最佳的方式来设置此，我想如下表：

I'm comfortable getting user data from the AD, but not so sure as to the best approach to set this up, I was thinking of the following table:

CREATE TABLE dbo.Employees
(
    EmpID       nvarchar(8) PRIMARY KEY,
    EmpName     nvarchar(30),
    EmpNo       nvarchar(15),
    EmpEmail    nvarChar(255),
    EmpTitle    nvarchar(255),
    MgrID       nvarchar(8) FOREIGN KEY REFERENCES Employees(EmpID)
)
GO

的EmpID 将成为 NetworkID （sAMAccountName赋）。

EmpID will be the NetworkID (sAMAccountName).

那么这将需要从AD填充，我通过递归调用猜测，填补了领域。

Then this will need populating from the AD, I'm guessing through recursive calls to fill in the fields.

我那么不知道如何组织code，这样它会捕捉每一个管理者在每个员工开始在给定级别。

I'm then not sure how to structure the code so that it will capture every employee under every manager starting at a given level.

我目前使用我的下面，以捕捉特定经理直接下属：

I'm currently using the below to capture the direct reports for a given manager:

 Public Function GetDirectReports(ByVal ADFullName As String) As ArrayList

            Dim adItems As ArrayList = New ArrayList

            Dim rootEntry As New DirectoryEntry("LDAP://" & ADFullName)

            Dim searcher As New DirectorySearcher(rootEntry)
            searcher.PropertiesToLoad.Add("directReports")
            searcher.PropertiesToLoad.Add("sAMAccountName")
            searcher.PropertiesToLoad.Add("cn")

            searcher.PageSize = 5
            searcher.ServerTimeLimit = New TimeSpan(0, 0, 30)
            searcher.ClientTimeout = New TimeSpan(0, 10, 0)

            Dim queryResults As SearchResultCollection
            queryResults = searcher.FindAll()

            Dim x As Integer

            For Each result As SearchResult In queryResults
                For x = 0 To result.Properties("directReports").Count - 1
                    adItems.Add(New ADReports(result.Properties("directReports")(x), _
                                ExtractUser(result.Properties("directReports")(x))))
                Next
            Next
            Return adItems
        End Function

        Private Function ExtractUser(ByVal username) As String
            Return Split(Split(username, "CN=")(1), ",")(0)
        End Function

有任何意见建议，并帮助将是非常美联社preciated :)

Any comments suggestions and help would be very much appreciated :)

推荐答案

基本上我会做的是简单地通过整个广告递归抢了所有的用户，提供所需的相关信息，并将其存储在SQL Server中。虽然列举，不用担心和关心的层次 - 你以后会处理这些

Basically what I would do is simply recurse through the entire AD and grab out all the users, with the relevant info needed, and store them in SQL Server. While enumerating, don't worry and care about the hierarchies - you'll handle those later.

一旦数据是SQL Server内部，然后你可以创建如递归CTE（公共表前pression），以获得员工的层次 - 谁向谁报告等。

Once the data is inside SQL Server, you could then create e.g. a recursive CTE (Common Table Expression) to get the hierarchy of the employees - who reports to whom etc.

作为一个侧面说明：在 sAMAccountName赋不保证能够保持稳定 - 这可能会改变（很少见，但有可能） - 所以也许你需要另一个项目作为唯一标识符为每个用户？贵公司是否使用了用户ID 属性，还是其他什么东西？或者，你能使用AD用户的GUID（这将保持不变永远），而不是的samAccountName ？

As a side-note: the sAMAccountName is not guaranteed to stay stable - it could change (rare, but possible) - so maybe you need another item as the unique identifier for each user? Does your company use the userID attribute, or something else? Or could you use the AD user GUID (which will stay the same forever) instead of the samAccountName?

另外：如果你选择坚持使用 sAMAccountName赋：注意它可能是最多20个字符 - NVARCHAR（8）是不够的。另外：你的真正的需要统一code字符串？这些只是始终两次只要不统一code VARCHAR（20）串 - 只是问，你可能需要它，那么这很好。

Also: if you choose to stick with the sAMAccountName: be aware it could be up to 20 characters long - NVARCHAR(8) won't be enough. Also: do you really need the Unicode strings? Those are just always twice as long as the non-Unicode VARCHAR(20) strings - just asking, you might need it, then that's fine.

我会雇员（ INT IDENTITY ）列可能添加的人工表来处理所有要求该表的好聚集键：独特的，稳定的，狭隘的。 A 为nvarchar（20）首先是可变长度这还不是特别好，它可能是最多40个字节长 - 这又是不是最佳的。

I would probably add an artificial EmployeeID (INT IDENTITY) column to the table to handle all the requirements for a good clustering key on that table: unique, stable, narrow. A nvarchar(20) is first of all variable length which isn't terribly good, and it could be up to 40 bytes long - which again is not optimal.