Larry Steinle

In the previous seven posts we learned how to construct an Active Directory Data Access Layer. In today’s post we will use the AD DAL to create an Active Directory Query tool. AD Query will allow us to select, insert, update and delete objects in Active Directory with a familiar intuitive interface. Best of all, AD Query will demonstrate just how easy it is to work with Active Directory now that we have implemented the DataBase Access Layer.

This is the last post of an eight part series about the Active Directory Data Access Layer. As each post builds on the previous it may be helpful to review older posts prior to reading this one. If you would like to download a working copy of the AD DAL please refer to the download on the Code Share page.

• First Post: Active Directory Data Access Layer
• Second Post: Active Directory Connection Strings
• Third Post: AdConnection: Enforcing Active Directory Communication Best Practices
• Fourth Post: AdCommandTextParser: Parsing SQL Statements
• Fifth Post: AdCommand: Running Active Directory Queries
• Sixth Post: AdDataReader: Providing Controlled Access to AD Values
• Seventh Post: AdDataAdapter: Managing Active Directory Data
• Eighth Post: AD Query: Putting it All Together

AD Query Web Page

The AD Query Web Page that we will have fields for entering a connection string so we know which domain to manage and another text field for our sql statements.

A menu will be used to create the tab menu effect and a multiview will be tied to the menu. When a menu item is selected the corresponding view will be enabled.

We’ll add three checkboxes to control when insert, update and delete actions are permitted. The user should excercise extreme caution as the Active Directory Data Access Layer makes it extremely easy to update objects. In my test environment I accidentally set the display name attribute for all user objects to “Test” while validating the code. As a result my administrator account displayed “Test”. So be sure to test queries that modify the data in a safe environment and prove them prior to running them in a production environment.

Finally there are three buttons that will enable us to parse the query for validation, execute the query and view the results in tabular format or execute the query and view the results as xml.

<%@ Page Title="ADQuery" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="LarrySteinle.ADQuery._Default" ValidateRequest="false" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"></asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
  <h2>ADSI Query</h2>

  <h3>Connection String <span style="font-variant:normal;font-size:.7em;">(<a href="ConnectionStrings.aspx" target="_blank">Help</a>)</span></h3>
  <asp:TextBox ID="adConnectionString" runat="server" Width="100%" />

  <h3>Query <span style="font-variant:normal;font-size:.7em;">(<a href="About.aspx" target="_blank">Help</a>)</span></h3>
  <asp:TextBox ID="adQuery" runat="server" TextMode="MultiLine" Rows="8" Width="100%" />
  <div style="display:block;text-align:right;">
    <div style="float:left;">
      Options:&nbsp;
      <asp:CheckBoxList ID="uiOptions" runat="server" RepeatLayout="Flow" RepeatDirection="Horizontal">
        <asp:ListItem Text="Insert" Value="Insert" />
        <asp:ListItem Text="Update" Value="Update" />
        <asp:ListItem Text="Delete" Value="Delete" />
      </asp:CheckBoxList>
    </div>

    <asp:Button ID="parseQuery" runat="server" Text="Parse Query" CssClass="button" />
    <asp:Button ID="runQuery" runat="server" Text="Run Query" CssClass="button" />
    <asp:Button ID="getXml" runat="server" Text="Get Xml" CssClass="button" />
  </div>

  <asp:Menu ID="tabMenu" runat="server" CssClass="tabMenu" IncludeStyleBlock="true" Orientation="Horizontal">
    <Items>
      <asp:MenuItem Text="Results" Value="resultsPane" />
      <asp:MenuItem Text="Analysis" Value="analysisPane" />
      <asp:MenuItem Text="Messages" Value="messagesPane" />
    </Items>
  </asp:Menu>
  <div style="border:inset 2px silver;margin:0px;padding:0px;height:250px;overflow:auto;">
    <asp:MultiView ID="tabPanes" runat="server">
      <asp:View ID="resultsPane" runat="server">
        <asp:Literal ID="uiResults" runat="server" />
      </asp:View>
      <asp:View ID="analysisPane" runat="server">
        <asp:Literal ID="uiAnalysis" runat="server" />
      </asp:View>
      <asp:View ID="messagesPane" runat="server">
        <asp:Literal ID="adMessages" runat="server" />
      </asp:View>
    </asp:MultiView>
  </div>
</asp:Content>

Consuming the AD DAL

In the code behind for the class add the following routines so that they can be associated with their corresponding asp controls.

In this solution I am manually creating the html tables in the code. We could have just as easily used a DataAdapter to translate the results into a DataTable and then used the GridView or DataRepeater or any other control of choice.

Imports LarrySteinle.Library.Data
Imports LarrySteinle.Library.Extensions.FullSplitStringExtension

Public Class _Default
  Inherits System.Web.UI.Page

  Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
      adConnectionString.Text = System.Configuration.ConfigurationManager.ConnectionStrings("LDAP").ConnectionString
    End If
  End Sub

  Private Sub SetMenuPane(ByVal value As String)
    For Each item As MenuItem In tabMenu.Items
      If item.Value = value Then item.Selected = True
    Next

    For Each pane As View In tabPanes.Views
      If pane.ID = value Then
        tabPanes.SetActiveView(pane)
        Exit For
      End If
    Next
  End Sub

  Protected Sub tabMenu_MenuItemClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.MenuEventArgs) Handles tabMenu.MenuItemClick
    SetMenuPane(e.Item.Value)
  End Sub

  Protected Sub parseQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles parseQuery.Click
    SetMenuPane(tabMenu.Items(1).Value)
    uiResults.Text = String.Empty
    uiAnalysis.Text = String.Empty
    adMessages.Text = String.Empty

    'Parse Statement
    Try
      Dim commandBatch As New ActiveDirectory.AdCommandTextSets(adQuery.Text)
      Dim statementIndex As Integer = 0

      For Each commandSet As ActiveDirectory.AdCommandTextParser In commandBatch
        statementIndex += 1
        uiAnalysis.Text &= "<h3 style=""color:navy"">Statement " & statementIndex.ToString & "</h3>"

        uiAnalysis.Text &= "<div style=""color:blue"">SEARCH SCOPE</div>"
        uiAnalysis.Text &= String.Format("Search for {0}objects will begin in &lt;{1}&gt;", (commandSet.ObjectCategory & " ").Trim, commandSet.SearchRoot) & "<br />"

        uiAnalysis.Text &= "<div style=""color:blue"">ATTRIBUTES</div><ul>"
        For Each parm As ActiveDirectory.AdParameter In commandSet.Parameters
          uiAnalysis.Text &= "<li>" & Server.HtmlEncode(parm.ParameterName)

          If parm.Value IsNot Nothing AndAlso Not String.IsNullOrEmpty(parm.Value) Then
            uiAnalysis.Text &= " = " & Server.HtmlEncode(parm.Value)
          End If

          uiAnalysis.Text &= "</li>"
        Next
        uiAnalysis.Text &= "</ul>"

        uiAnalysis.Text &= "<div style=""color:blue"">FILTER</div>"
        uiAnalysis.Text &= commandSet.Filter & "<br />"
      Next
    Catch ex As Exception
      adMessages.Text &= GetErrors(ex)
      SetMenuPane(tabMenu.Items(2).Value)
    End Try
  End Sub

  Protected Sub runQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles runQuery.Click
    parseQuery_Click(sender, e)
    SetMenuPane(tabMenu.Items(0).Value)

    'Get Results
    Dim commandIndex As Integer = 0
    Dim recordsAffected As Integer
    Dim results As New System.Text.StringBuilder
    Dim adConnection As ActiveDirectory.AdConnection = Nothing
    Dim adCommand As ActiveDirectory.AdCommand = Nothing

    Try
      NotSupportedCheck()

      adConnection = New ActiveDirectory.AdConnection(adConnectionString.Text)
      adConnection.Open()

      Dim adParserSet As New ActiveDirectory.AdCommandTextSets(adQuery.Text)
      For Each adParser As ActiveDirectory.AdCommandTextParser In adParserSet
        commandIndex += 1
        adMessages.Text &= String.Format("<h3 style=""color:navy"">Statement {0} Results</h3>", commandIndex)

        adCommand = New ActiveDirectory.AdCommand(adParser.CommandText, adConnection)

        If adParser.CommandType <> StatementTypes.Select Then
          recordsAffected = adCommand.ExecuteNonQuery()
        Else
          Dim adReader As ActiveDirectory.AdDataReader = adCommand.ExecuteReader()

          If adReader.HasRows Then
            results.Append(String.Format("<h3 style=""color:navy"">Statement {0} Results</h3>", adReader.DatasetIndex + 1))
            results.Append("<table border='1' cellpadding='0' cellspacing='0'>")

            Try
              Dim createHeading As Boolean = True

              Do While adReader.Read
                If createHeading Then
                  results.Append("<tr>")
                  For fieldIndex As Integer = 0 To adReader.FieldCount - 1
                    results.Append("<th>")
                    results.Append(Server.HtmlEncode(adReader.GetName(fieldIndex)))
                    results.Append("</th>")
                  Next
                  results.Append("</tr>")

                  createHeading = False
                End If

                results.Append("<tr>")
                For fieldIndex As Integer = 0 To adReader.FieldCount - 1
                  results.Append("<td>")
                  If adReader.IsFieldMultiValued(fieldIndex) Then
                    results.Append("<ul>")
                    For Each value As Object In adReader.Item(fieldIndex)
                      results.Append("<li>" & Server.HtmlEncode(value.ToString) & "</li>")
                    Next
                    results.Append("</ul>")
                  Else
                    results.Append(Server.HtmlEncode(adReader.GetString(fieldIndex)))
                  End If
                  results.Append("</td>")
                Next
                results.Append("</tr>")
              Loop
            Catch ex As Exception
              adMessages.Text &= "<div style=""color:red"">ERROR</div>"
              adMessages.Text &= "<div style=""color:red"">" & Server.HtmlEncode(ex.Message) & "</div>"
              SetMenuPane(tabMenu.Items(2).Value)
            End Try

            results.Append("</table>")
            recordsAffected = adReader.RecordsAffected
          Else
            recordsAffected = 0
            SetMenuPane(tabMenu.Items(2).Value)
          End If
        End If

        adCommand.Dispose()
        adMessages.Text &= recordsAffected.ToString & " Records Affected"
      Next

      uiResults.Text = results.ToString
    Catch ex As Exception
      adMessages.Text &= GetErrors(ex)
      SetMenuPane(tabMenu.Items(2).Value)
    Finally
      If adCommand IsNot Nothing Then adCommand.Dispose()
      If adConnection IsNot Nothing Then adConnection.Dispose()
    End Try
  End Sub

  Protected Sub getXml_Click(ByVal sender As Object, ByVal e As EventArgs) Handles getXml.Click
    parseQuery_Click(sender, e)
    SetMenuPane(tabMenu.Items(0).Value)

    Dim adCommand As ActiveDirectory.AdCommand = Nothing
    Try
      NotSupportedCheck()

      adCommand = New ActiveDirectory.AdCommand(adQuery.Text, adConnectionString.Text)
      adCommand.Connection.Open()
      adCommand.CommandTimeout = 2

      Dim xmlText As String = adCommand.ExecuteXml(CommandBehavior.CloseConnection)
      xmlText = xmlText.Replace("encoding=""utf-16""", "encoding=""utf-8""")
      System.IO.File.WriteAllText(Server.MapPath("~/Data/XMLResults.xml"), xmlText)

      uiResults.Text = "<a target='_blank' href='./Data/XMLResults.xml'>Click to View Xml Results</a>"
    Catch ex As Exception
      adMessages.Text &= GetErrors(ex)
      SetMenuPane(tabMenu.Items(2).Value)
    Finally
      If adCommand IsNot Nothing Then
        If adCommand.Connection IsNot Nothing Then adCommand.Connection.Dispose()
        adCommand.Dispose()
      End If
    End Try
  End Sub

  Private Function GetErrors(ByVal ex As System.Exception) As String
    Dim msg As String = "<div style=""color:red"">ERROR</div><div style=""color:red"">"

    If TypeOf ex Is ActiveDirectory.AdException Then
      With DirectCast(ex, ActiveDirectory.AdException)
        msg &= ex.GetBaseException.Message
        msg &= "<ul>"
        For Each iEx As System.Exception In .Errors
          msg &= "<li>" & iEx.GetBaseException.Message & "</li>"
        Next
        msg &= "</ul>"
      End With
    Else
      msg &= ex.GetBaseException.Message
    End If

    msg &= "</div>"

    Return msg
  End Function

  Private Sub NotSupportedCheck()
    Dim parserSet As New ActiveDirectory.AdCommandTextSets(adQuery.Text)
    For Each parser As ActiveDirectory.AdCommandTextParser In parserSet
      Select Case parser.CommandType
        Case StatementTypes.Insert
          If uiOptions.Items(0).Value <> "Insert" Then
            Throw New ArgumentOutOfRangeException("Item 0 expected to be insert option.")
          ElseIf Not uiOptions.Items(0).Selected Then
            Throw New InvalidOperationException("Insert statements are disabled.")
          End If
        Case StatementTypes.Update
          If uiOptions.Items(1).Value <> "Update" Then
            Throw New ArgumentOutOfRangeException("Item 0 expected to be update option.")
          ElseIf Not uiOptions.Items(1).Selected Then
            Throw New InvalidOperationException("Update statements are disabled.")
          End If
        Case StatementTypes.Delete
          If uiOptions.Items(2).Value <> "Delete" Then
            Throw New ArgumentOutOfRangeException("Item 0 expected to be delete option.")
          ElseIf Not uiOptions.Items(2).Selected Then
            Throw New InvalidOperationException("Delete statements are disabled.")
          End If
      End Select
    Next
  End Sub
End Class

The AD DAL in Action

The screen shot below demonstrates the final product. As an added convenience I have made the code from all eight sessions available on my Code Share page. I hope that you will find the modules as useful as I have!

AD Query Analyzer