I have a function that searches AD for members of various groups. If I remove the loop, I don't get the error "A column named 'cn' already belongs to this datatable", however, I need to iterate through each OU.
Function getCOMDLNames(ByVal searchStr As String) As DataTable
Dim MySearchRoot As DirectoryEntry = New DirectoryEntry("path", "usr", "pwd")
Dim MyDirectorySearcher As New DirectorySearcher(MySearchRoot)
Dim con As New SqlConnection
Dim cmdSelect As SqlCommand
Dim da As New SqlDataAdapter
Dim cdt As New DataTable
Dim row As DataRow
Dim campusGroupArray() As String
Dim j As Integer = 0
Try
con.ConnectionString = ConfigurationManager.AppSettings("sql_dir")
con.Open()
cmdSelect = New SqlCommand("...")
cmdSelect.Connection = con
da.SelectCommand = cmdSelect
da.Fill(cdt)
ReDim campusGroupArray(0 To cdt.Rows.Count)
'Fill array
For i As Integer = 0 To cdt.Rows.Count - 1
row = cdt.Rows(i)
campusGroupArray(i) = row(0)
Next
'Remove empty elements from array
Dim tempStr As String = String.Join("'", campusGroupArray)
campusGroupArray = Nothing
campusGroupArray = tempStr.Split(New Char() {"'"c}, StringSplitOptions.RemoveEmptyEntries)
Dim MySearchResult As SearchResultCollection
'Loop through OU's until members are found
Do
MyDirectorySearcher.Filter = ("(&(objectCategory=person)(memberof=cn=" & searchStr & ", ou=groups, ou=" & campusGroupArray(j) & ", dc=.., dc=.., dc=.., dc=..))")
MyDirectorySearcher.SearchScope = SearchScope.Subtree
MyDirectorySearcher.PropertiesToLoad.Add("cn")
MyDirectorySearcher.Sort.Direction = System.DirectoryServices.SortDirection.Ascending
MyDirectorySearcher.Sort.PropertyName = "cn"
MySearchResult = MyDirectorySearcher.FindAll()
j += 1
Loop Until MySearchResult.Count > 0 Or j > campusGroupArray.Length - 1
Dim myTable As New DataTable
Dim colName As String
'Create columns in table
For Each colName In MyDirectorySearcher.PropertiesToLoad
myTable.Columns.Add(colName, GetType(System.String))
Next
Dim result As SearchResult
'Fill table
For Each result In MySearchResult
Dim dr As DataRow = myTable.NewRow()
For Each colName In MyDirectorySearcher.PropertiesToLoad
If result.Properties.Contains(colName) Then
dr(colName) = CStr(result.Properties(colName)(0))
Else
dr(colName) = ""
End If
Next
myTable.Rows.Add(dr)
Next
Return myTable
Catch ex As Exception
Return Nothing
End Try
End Function
You can loop through any number of OU you want - but you need to define the list of properties to load (the .PropertiesToLoad
property) once before your loop starts (this really applies to all the properties that aren't going to change between calls to the Directory Searcher - like search scope, the sort direction etc - there's absolutely no point in setting those for each iteration - set them once and be done with it.
' define the list of properties to load for each search result ONCE before the loop
MyDirectorySearcher.PropertiesToLoad.Add("cn")
MyDirectorySearcher.SearchScope = SearchScope.Subtree
MyDirectorySearcher.Sort.Direction = SortDirection.Ascending
MyDirectorySearcher.Sort.PropertyName = "cn"
' *THEN* Loop through OU's until members are found
Do
MyDirectorySearcher.Filter = ("(&(objectCategory=person)(memberof=cn=" & searchStr & ", ou=groups, ou=" & campusGroupArray(j) & ", dc=.., dc=.., dc=.., dc=..))")
MySearchResult = MyDirectorySearcher.FindAll()
j += 1
Loop Until MySearchResult.Count > 0 Or j > campusGroupArray.Length - 1
The PropertiesToLoad
just defines the list of properties that the search results will contain - this is the same list for each of the searches, and can only contain the property name (e.g. cn
) once.