Larry Steinle

February 28, 2011

Active Directory Data Access Layer

Filed under: Active Directory,VS.Net,Web — Larry Steinle @ 1:27 pm
Tags: , , ,

In the previous post, Reading Data in Active Directory, we learned how to query Active Directory using OLEDB and .Net Directory Services. We learned that the OLEDB option is simple but has two big restrictions: we are limited to 1,000 objects in a search and we are restricted to single-valued attributes. We also learned how to get past those limitations using Directory Services.

In today’s post we begin our journey to create a data access layer for Active Directory that allows us to query Active Directory using a SQL-like syntax without the paging limit and without the single-valued restriction. Best of all this new data access layer will make it easier to integrate Active Directory management into all of our VS.Net products. When we are done we will use our new Active Directory Data Access Provider to easily construct our own custom, Active Directory Query Analyzer web page.

Active Directory Data Access Layer Series

This is the first post of an eight part series about the Active Directory Data Access Layer. If you would like to download a working copy of the AD DAL please refer to the download on the Code Share page.

The Microsoft dot Net Data Access Layer

Since the early days of dot Net we had access to a common data access layer for SQL, Oracle, OLEDB and ODBC in the System.Data namespace. The data access layer was originally modeled on several interfaces.
Active Data Objects dot Net Interface Model

ADO.Net Interface Model

With CLR 2.0 several DB base classes were added to the System.Data.Common namespace. These classes implement the interfaces and provide much of the common code required to support the interfaces.
Active Data Objects dot Net DB Base Class Model

ADO.Net DB Base Class Model

These classes provide a common interface to manage data in various data sources. They can be bound to controls in Web Forms, Windows Forms and Silverlight. Because of the common interface they introduce tremendous power and flexibility to the .Net framework.

Standing on the Back of Giants

We want to leverage the power and flexibility of the data framework to access Active Directory. Additionally, we want to continue to utilize the SQL code style to manage the data in Active Directory.

The following diagram shows how we intend to implement the Data framework in our own system to simplify managing Active Directory.
Custom Active Directory Data Access Layer Model

AD Data Access Model

In order to support our requirements of using SQL statements to manage Active Directory we will also need several helper classes. These helper classes will assist with connection string management, SQL statement parsing, data conversion and quality control.

In our new AD DAL we will be using code from previous posts:


Our custom Data Access Layer will offer the following benefits:

  • Execute Select query with access to both single-valued and multi-valued attributes.
  • Paging so that all data can be retrieved.
  • Translation of SQL Where clause into an ADSI filter.
    ADSI filters can become very complex quickly. The more “and” and “or” conditions required the more difficult it is to correctly construct an ADSI filter by hand. Using a SQL Where clause format makes ADSI filter much easier to manage!
  • Support for the NOT operator.
  • Support the following SQL keywords:
    • EXISTS
    • IN
    • LIKE
  • Keywords that will make it easier to take advantage of the full power of ADSI filters:
    • Standard math operator comparisons (equal to, less than and greater than).
    • Bitwise operator comparisons (bitwise “or” and bitwise “and”).
    • Support finding users who are a member of a group that they are not directly assigned to via the CHAINDN operator.
    • Keywords to help with finding objects whose attributes:
      • Contains a value,
      • Starts with a value,
      • Ends with a value, or
      • Is similar to a value.
  • Execute Insert, Update and Delete actions against a domain in which you have the rights to perform the action.
  • Delete an OU tree with the DeleteTree statement which will be modeled after the SQL Delete statement.
  • Any special characters found in the ADSI filters will automatically be escaped.
  • Any special characters found in the path will automatically be escaped.

Putting it all Together

When we are finished we will create an Active Directory Query Analyzer web page. The page will provide similar capabilities as Microsoft’s SQL Query Analyzer. We will be able to write SQL statements, parse them and run them. Our AD Query Analyzer will support multiple statements separated by a semi-colon. We will be able to select, insert, update and delete objects in Active Directory using standard SQL statements.
AD Query Analyzer

AD Query Analyzer

Since the custom AD DAL implements a standardized data access model interface it will be extremely simple to plug it into our custom AD Query Analyzer web page. We will only need a handful of routines to enable AD Query to work with our new AD DAL!


I trust that today’s post has captured your interest. In future posts we will begin constructing our AD DAL until the final post where we will demonstrate its use with our custom AD Query Analyzer web page!


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: