VB

This morning, I had the chance to play around with another ASP.NET AJAX control:  the CascadingDropDown control.  Like the name implies, it's a nifty little AJAX-enabled control that dynamically enables and populates a DropDownList based on the value selected in a previous list.  Kinda like the coding version of those old skool "Choose Your Own Adventure" books.

You've seen this kind of thing in action plenty of times, I'm sure.  Remember the last time you filled out a registration form that had you pick a country, then you suddenly had a "State/Province" list that only included regions within that country?  Well, this is Microsoft's implementation.  Check it out:  http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx

As with most of the ASP.NET AJAX stuff, there's also a great "How Do I..." video (http://www.asp.net/learn/ajax-videos/video-77.aspx) from Joe Stagner (http://www.joeon.net).  Thing is, though, that the video uses an XML file for its data source.  Not real helpful for me, seeing that my data was coming from an actual SQL database. 

The sample website has another page that claims to show how to use the CascadingDropDown with a database backend (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx), but I frankly find it wanting.  First of all, it's not really a "tutorial" in the sense that it doesn't really walk you through how to do what I wanted to do step-by-step.  It assumes you know what you're doing to a certain extent, an assumption which we here at Nerdliness.com have proven time and time again can be fatal.

The second problem with it is that the sample uses C#, with no Visual Basic equivalent.  Now, I'm not a real developer.  I don't even play one on TV.  Hell, I'd be lucky to be the dude they bring in to double for the developer-on-TV when they need to adjust the lighting and all that.  Sure, maybe I could be the key grip on the developer show but...  Ok, you get the point.  I'm sticking with VB.NET.

So let's show the newbs how it's done.  We're going to create three DropDownLists and, using the CascadingDropDown control, populate the second list based on what was selected in the first, and the third based on what's selected in the second.

In this example, we're asking people to pick their favorite NBA player (for you hardcore nerdlings, the "NBA" is the National Basketball Association...  you know, that sport with all the really tall guys throwing a ball into a hoop).  In the first DropDownList, we'll ask them to pick the conference (Eastern or Western).  The second DropDownList will be populated with teams in that conference.  Once they pick a team, they'll be able to choose from a couple of players on that team using the third DropDownList.

The data will be stored in three tables called, amazingly enough, CONFERENCE, TEAM, and PLAYER.  This E-R diagram should give you an idea of what those relationships look like:

As for the data in each, well, I'm lazy, so I'm not going to include all the teams and all the players.  Just a representative example to show how it works (see attached for a dump of the tables).

(Sure, it's West-heavy.  But is it my fault that's where all the talent is?)

That's all stored in a local SQL Server 2005 Express instance, but that doesn't really matter.  You could be pulling it from SQL 2000, Oracle, MySQL, etc., just as long as you configure the connection in your web service appropriately.  Obviously, I can't really tell you how your connection will look, not knowing anything about your setup.

Finally, a last assumption...  I'm using Visual Studio 2008 for this example, but from what I've seen I don't think there's much difference between it and previous versions (as far as this example is concerned, that is).  If you're using a previous version, make sure you've gone through all the getting-ready-to-use-ASP.NET-AJAX stuff in videos 1 and 2 here:  http://www.asp.net/learn/ajax-videos/

Let's get to it.   First, I have a simple default.aspx file with the aforementioned DropDownLists.  Then, I've added one CascadingDropDown control for each of the DropDownLists in our little group.  Of course, all that's in addition to the stuff we need to make ASP.NET AJAX work at all (registering the AjaxControlTookit assembly at the top, and adding a ScriptManager control).

The properties of the CascadingDropDown controls are all pretty straightforward and you can probably get the idea of what each does just by the name (see http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx for more detailed info).  Only things that really need to be pointed out here are the TargetControlId property (shows the relationship between our CascadingDropDown controls and their matching DropDownList controls), the ServiceMethod property (the method we'll soon create in our web service), and the ServicePath property (the path to our soon-to-be-created web service).  Also note the ParentControlId on the last two, pointing to the DropDownList above it in our little heirarchy.  Without them, there's no way for our little controls to know how they're related.

One other thing about our .aspx file...  According to the ASP.NET AJAX sample site, you need to turn off EventValidation for this whole thing to work:

Finally, in order for the values to be submitted, EventValidation needs to be disabled for the page. EventValidation ensures that the values in each control match the values that were present when the page was rendered, but since these drop downs are populating on the client side, this is never true. Were hoping to find a way to resolve this issue but please ensure that you understand the potential risks of this and validate the data appropriately in your post back when using this control.

You'll notice I did just that in the <%@ Page %> directive (EnableEventValidation="false"). 

This whole file is really just our presentation layer.  Nothing really interesting happening here.  The good stuff goes down inside our web service.  It's also where the ASP.NET AJAX sample page gets tricky.

I mentioned before that the sample page (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx) makes a couple of assumptions I didn't like.  First, they assume you're using C# and don't include a VB snippet.  Bad form, I say.  Worse, though, is the fact that they make the assumption that you've already created methods for pulling the data from your database, and they use arbitrary names for methods that don't usually exist through their example:

So imagine you've got a database with tables for the Make (manufacturer), Model, and Color information, and you're accessing that database through a DataSet to which you've added methods for getting each set of values.

But if you're a newb, or a strict VB dev guy (sure, maybe that's redundant), you might have a hard time making heads or tails out of their code.  Trying typing in Cars.ColorDataTable colorTable = adapter.GetColorsForModel(modelId); and see what IntelliSense does.  Trust me, you'll have lots of colorful squiggly lines and errors in your future.

So here's how we roll at Nerdliness.com.  In our web service, we're going to use a SqlDataReader to pull the info we want from the previously defined CONFERENCE, TEAM, and PLAYER tables instead of using a DataSet with methods defined behind the scenes.  Frankly, I think this is a bit more straight forward and easier to read, especially for someone new to coding.

And here's the full codebehind file:

nbaplayers.vb

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
_
_
_
_
Public Class nbaplayers
Inherits System.Web.Services.WebService

_
Public Function GetConferences(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strConfQuery As String = "SELECT * FROM CONFERENCE"
Dim cmdFetchConf As SqlCommand = New SqlCommand(strConfQuery, sqlConn)

Dim dtrConf As SqlDataReader
Dim myConferences As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrConf = cmdFetchConf.ExecuteReader

While dtrConf.Read()
Dim strConfName As String = dtrConf("conf_name").ToString
Dim strConfId As String = dtrConf("conf_id").ToString

myConferences.Add(New CascadingDropDownNameValue(strConfName, strConfId))
End While

Return myConferences.ToArray
End Function

_
Public Function GetTeams(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strTeamQuery As String = "SELECT * FROM TEAM WHERE conf_id = @confid"
Dim cmdFetchTeam As SqlCommand = New SqlCommand(strTeamQuery, sqlConn)

Dim dtrTeam As SqlDataReader
Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim intConfId As Integer

If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
Return Nothing
End If

cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)
Dim myTeams As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrTeam = cmdFetchTeam.ExecuteReader

While dtrTeam.Read()
Dim strTeamName As String = dtrTeam("team_name").ToString
Dim strTeamId As String = dtrTeam("team_id").ToString

myTeams.Add(New CascadingDropDownNameValue(strTeamName, strTeamId))
End While

Return myTeams.ToArray
End Function

_
Public Function GetPlayers(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strPlayerQuery As String = "SELECT * FROM PLAYER WHERE team_id = @teamid"
Dim cmdFetchPlayer As SqlCommand = New SqlCommand(strPlayerQuery, sqlConn)

Dim dtrPlayer As SqlDataReader
Dim kvPlayer As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim intTeamId As Integer

If Not kvPlayer.ContainsKey("Team") Or Not Int32.TryParse(kvPlayer("Team"), intTeamId) Then
Return Nothing
End If

cmdFetchPlayer.Parameters.AddWithValue("@teamid", intTeamId)

Dim myPlayers As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrPlayer = cmdFetchPlayer.ExecuteReader

While dtrPlayer.Read()
Dim strPlayerName As String = dtrPlayer("player_name").ToString
Dim strPlayerId As String = dtrPlayer("player_id").ToString

myPlayers.Add(New CascadingDropDownNameValue(strPlayerName, strPlayerId))
End While

Return myPlayers.ToArray
End Function

End Class
Ok, so first thing to note are the different namespaces we imported at the top of the file.  Make sure you do the same.

Second, notice that we've uncommented the "<System.Web.Script.Services.ScriptService()> _" line.  In Visual Studio 2008, that line's already sitting in the new web service when you first add it to your project, but in previous versions you have to add it yourself. 

Now the meat of the web service comes in the form of our three WebMethods:  GetConferences, GetTeams, and GetPlayers.  You might recall those names from the properties of our CascadingDropDown controls in our default.aspx file.  These are the methods we're using to gather the data from our tables and send it back to our controls. 

The first method, GetConferences, is a little simpler than the other two.  Since we're populating that right away and without caring what other DropDownLists contain, we don't need a lot of the logic.

Most of the logic in that method is dedicated to connecting to our database, creating our DataReader, and pulling data.  We loop through the results returned by our "SELECT * FROM CONFERENCE" query and, for each line returned, add a line to the myConferences collection.  We then convert myConferences to an array and send that result back to our CascadingDropDown control.

The only non-standard issue stuff here involves that CascadingDropDownNameValue bit.  Like the sample site says (typo and all):

Note that the values are returned as an array of CascadaingDropDownNameValue structures. This structure also includes an isDefaultValue boolean field that allows the specification of which value should be selected when the list is first displayed.

So we create a list of CascadingDropDownNameValue pairs in our WHILE loop, then pass that list back to the control.  Control takes it, populates our list, then feeds that information into the web method used by the next DropDownList in our chain whenever you select a value.  Pretty easy.

The other two methods are nearly identical, save for the fact that one takes CONFERENCE input and gives TEAM output, and the other takes TEAM input and gives PLAYER output.  Let's just look at the GetTeams method, then.

You'll notice that a good portion of this is the same as the first method.  That is, we create a SqlDataReader and populate it based on our parameterized "SELECT * FROM TEAM WHERE conf_id = @confid" query.  That @confid parameter will be supplied by the previous DropDownList.  So if someone selects "Eastern" from the first list, we only want TEAMS that are in the Eastern conference, right?

So what's the difference between this method and the previous?  This bit here:

Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

    Dim intConfId As Integer

    If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
        Return Nothing
    End If
Like the sample site says:

The knownCategoryValues parameter will return a string containing the currently selected category values, as well as the category to retrieve values for.

and...

The CascadingDropDown class has a helper method for unpacking the category values...  This method will return a StringDictionary containing the name/value pairs of the currently selected values.

 
In English?  We're going to look at knownCategoryValues, make sure it contains a key called Conference, and pull out of it the conf_id associated with the selected item.  We then use that conf_id finish dynamically creating our SQL SELECT query:

cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)
Execute that query and feed it into our DataReader, loop through the results, add the appropriate info into another list of CascadingDropDownNameValues, and send that back to the CascadingDropDown control to enable and populate our ddlTeam DropDownList.

And that's pretty much it.  Like I mentioned, the second and third methods are pretty much identical, so adding a fourth or fiftieth DropDownList to the chain is a snap.

Hope this clears up some things for any other poor saps out there looking to populate their CascadingDropDowns with a database backend and using Visual Basic.

Intro

From time to time, I get the chance to take off the "database guy" hat I usually wear around the office and pretend I'm a developer.  A good deal of that coding involves some sort of web-based tool or another so, being a Microsoft shop, I figured it was time for me to get up to speed on their implementation of AJAX.  For the last couple of weeks, I've been playing around with the AJAX Control Toolkit for ASP.NET (http://www.asp.net/ajax/) and today had the chance to put one of those controls (the AutoComplete extender) to work.

I needed to create a web-based company address/phone book to throw on our intranet site.  We've nearly doubled the number of employees on the payroll over the past several months, and with everybody spread across about a dozen or so field offices, it can be a pain finding somebody when you need them, especially when you don't know their full name.  If only there was some way to allow users to type in the parts they knew and dynamically generate a list of users with names that fit that criteria...  something that might automatically complete their search string...

In case you don't know (or know it by some other name, if you're a non-Microsoft person), the AutoComplete extender attaches to a TextBox control and, as the user begins to type, gives a list of suggestions he can click if he's too lazy to type in the whole phrase.  You've seen it in the Google toolbar, etc.  Pretty damn handy tool, if you ask me.  You can see a sample of it (along with a list of properties) at http://www.asp.net/AJAX/AjaxControlToolkit/Samples/AutoComplete/AutoComplete.aspx.

That ASP.net website is chock full of well done, bite-sized how-to videos to help you learn to use the various Microsoft AJAX controls.  In fact, that's where I started and it's the first place I go when I want to learn about a new piece of the puzzle.  The videos are typically about 10-30 minutes long, with just enough information to give point you in the right direction.  Perfect for those of us with short atten...  Oh, look!  Something shiny!

Sorry.  Anyway...  All the information people needed to access was in Active Directory (names, phone numbers, and email addresses), so really all we needed to do was give our users a way to search and access the data they wanted.  Unfortunately, the sample "How Do I..." video for that control doesn't really delve into populating your control with data from a simple database, much less from Active Directory.  Had to do a little digging for that.  It did, though, give me the basic foundation I needed to set up the control and create a web service to supply it with info of some kind.

Description and Caveats

Ok, so enough of the intro crap.  Let's get to the goods.  We're going to create a simple ASP.NET website that consists of one TextBox control with a linked AutoComplete extender, a button to kick off our email/phone search event, and a couple of labels to display the results. 

When someone types a few letters in the TextBox, our AutoComplete extender will send those characters to our web service.  That web service will perform the Active Directory lookup using the System.DirectoryServices namespace and return the collection of values back to that control.  After someone selects the user they want, they can hit our button to search Active Directory for that person's email address and phone number (we'll use the web service for that lookup, too).  And if they exist, they'll see them in our labels.

Couple of caveats here.  I'm using Visual Studio 2005 with the AJAX Control Toolkit installed seperately.  I'm assuming you already have a similar setup on your rig, otherwise you'll need to follow the instructions on the ASP.net site to get your ducks in a row.  Watch the first two videos here if you need a hand:  http://www.asp.net/learn/ajax-videos/

Second caveat is that, to look up Active Directory info, well, you'll need access to an Active Directory domain.  Definitely beyond the scope of this article.  I'm also assuming you'll be able to figure out your own LDAP path.  My example is going to be pretty basic and assumes you'll want to search your entire Active Directory domain from the root on down.

Finally, I'm going to assume that you have basic knowledge of Visual Studio in general.  That is, you know how to create a new website, how to add controls from the Toolbox, where the Solution Explorer is, etc.  I'll throw in a link to my project files at the end if you're lazy.

Default.aspx

Ok, let's get on with it.  First of all, let's create our site, our blank web service, and add our various references. 

In my case, since I was starting from scratch, I just chose to create a new web site using the AJAX Control Toolkit Web Site template added to Visual Studio when I installed the AJAX Control Toolkit.  Piece of cake. 

In my Default.aspx page, I added the controls I mentioned above.  Here's the source for that page, if you just want to copy and paste:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Phone and Email Looker Upper</title>
</head>
<body>
    <form id="form1" runat="server">
        <ajaxToolkit:ToolkitScriptManager ID="ScriptManager1" runat="server" />
        <div>
            <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
            <asp:Button ID="btnGetInfo" runat="server" Text="Get Info" />
            <ajaxToolkit:AutoCompleteExtender
                ID="AutoCompleteExtender1"
                runat="server"
                ServiceMethod="findUser"
                ServicePath="activeDirectorySearch.asmx"
                TargetControlID="txtUserName"
        MinimumPrefixLength="3"
            >
            </ajaxToolkit:AutoCompleteExtender>
            <br />
            <asp:Label ID="lblEmail" runat="server" Text="Email Address:"></asp:Label>
            <asp:Label ID="lblEmailResult" runat="server"></asp:Label><br />
            <asp:Label ID="lblPhone" runat="server" Text="Phone Number:"></asp:Label>
            <asp:Label ID="lblPhoneResult" runat="server"></asp:Label></div>
    </form>
</body>
</html>

Really straight-forward if you've done any ASP.NET.  Take a second to look closer at the AutoCompleteExtender if this is your first time playing with it.  Couple of those properties you'll want to keep in mind:

ServiceMethod="findUser"
This is the name of the WebMethod we'll create in our web service that will ultimately perform our Active Directory lookup and return the results.  If you name your webmethod something else, make sure you change this property to match.

ServicePath="activeDirectorySearch.asmx"
The URL for our web service.  Mine's hosted locally, so no problem using that relative path.  Of course, if you put it somewhere else you'll need to modify this URL, too.

TargetControlID="txtUserName"
The name of the control we're binding this extender to.  When someone types in txtUserName, this AutoComplete extender will work its mojo.

MinimumPrefixLength="3"
The minimum number of characters your user has to type before the web service is called.  I've found that my Active Directory searches can be fairly slow, so I don't want to look for a ridiculously short string.  At the same time, there are plenty of people with three letter names, so I want to make sure they get returned.

There are a bunch of other properties that might be worth your time.  Check out  http://www.asp.net/AJAX/AjaxControlToolkit/Samples/AutoComplete/AutoComplete.aspx for more info.

Default.aspx.vb

Our code behind file will be pretty simple, too.  After all, there's just the one button to deal with (the rest of our logic is in the web service).  Mine looks like this:

Imports System.Collections.Generic
Imports System.Web.Services

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub btnGetInfo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGetInfo.Click
        Dim dictContact As New Dictionary(Of String, String)
        Dim srvSearch As activeDirectorySearch = New activeDirectorySearch
        dictContact = srvSearch.findContactInfo(txtUserName.Text)
        Dim strEmail As String
        Dim strPhone As String

        For Each line As KeyValuePair(Of String, String) In dictContact
            If line.Key = "mail" Then
                strEmail &= line.Value.ToString
            ElseIf line.Key = "telephonenumber" Then
                strPhone &= line.Value.ToString

            End If

        Next

        lblPhoneResult.Text = strPhone
        lblEmailResult.Text = strEmail
    End Sub
End Class

Not much to it, but a couple of things we need.  First, make sure you import System.Collections.Generic and System.Web.Services.  The former is needed for the dictionary objects I'm creating to hold the user info, and the latter is needed to make the call to our web service (activeDirectorySearch).

All we're doing here is taking the string in the txtUsername.Text property and feeding that to the findContactInfo() webmethod we'll be creating.  That webmethod returns a dictionary (hash table, key/value pairs, whatever you wanna call it) with the email address and phone number (if any) in Active Directory for the person whose name appears in the text box.  It then iterates through the dictionary and creates a string to  hold our results.  Those are then assigned to the Text properties of our result labels.  Admittedly, the formatting is kinda hinky, but we're going more for functionality here.

activeDirectorySearch.vb

Ok, let's create our web service.  If you haven't done this before, it's pretty simple:  just right-click your project in Solution Explorer, choose Add New Item..., and select Web Service.  Give it a name, click Add, and you're set.  In my case, the name is activeDirectorySearch, but if you name yours differently make sure you edit your code behind file above to match.

This is probably the most complex part of the project, and even it isn't that hard.  Here's the code:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.DirectoryServices
Imports System.Collections.Generic

<WebService(Namespace:="http://localhost/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class activeDirectorySearch
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function findUser(ByVal prefixText As String) As String()
        Dim directory As DirectoryEntry = New DirectoryEntry("LDAP://DC=nerdliness,DC=com")
        Dim filter As String = "(&(cn=*" & prefixText & "*)(!objectClass=computer)(!objectClass=nTFRSMember))"

        Dim strCats() As String = {"cn"}
        Dim items As New List(Of String)
        Dim dirUser As DirectorySearcher = New DirectorySearcher(directory, filter, strCats, SearchScope.Subtree)
        Dim results As SearchResultCollection = dirUser.FindAll
        Dim strOut As String
        For Each result As SearchResult In results
            For Each prop As DictionaryEntry In result.Properties
                If prop.Key = "cn" Then
                    For Each individualValue As Object In prop.Value
                        items.Add(individualValue.ToString)
                    Next
                End If
            Next
        Next

        Return items.ToArray()

    End Function

    Public Function findContactInfo(ByVal strUser As String) As Dictionary(Of String, String)
        Dim directory As DirectoryEntry = New DirectoryEntry("LDAP://DC=nerdliness,DC=com")
        Dim filter As String = "(cn=*" & strUser & "*)"

        Dim strCats() As String = {"mail", "telephonenumber"}
        Dim items As New Dictionary(Of String, String)
        Dim dirUser As DirectorySearcher = New DirectorySearcher(directory, filter, strCats, SearchScope.Subtree)
        Dim results As SearchResultCollection = dirUser.FindAll
        Dim strOut As String
        For Each result As SearchResult In results
            For Each prop As DictionaryEntry In result.Properties
                If prop.Key = "mail" Or prop.Key = "telephonenumber" Then
                    For Each individualValue As Object In prop.Value
                        items.Add(prop.Key.ToString, individualValue.ToString)
                    Next
                End If
            Next
        Next

        Return items

    End Function

End Class

First, notice that we added two other namespaces at the top:  System.DirectoryServices and System.Collections.Generic.  As before, System.Collections.Generic is needed for our item and dictionary objects.  System.DirectoryServices, on the other hand, gives us the ability to pull our info out of Active Directory and iterate through it.

NOTE:  You can't just add that "Imports System.DirectoryServices" line and call it good.  If you try it, you'll get a wonderful little warning:

"Namespace or type specified in the Imports 'System.DirectoryServices' doesn't contain any public member or cannot be found.  Make sure the namespace or the type is defined and contains at least one public member.  Make sure the imported element name doesn't use any aliases."

To get around that, you need to add a reference to System.DirectoryServices.  Just right-click your project in Solution Explorer and choose Add Reference...  Scroll down the list in the .NET tab and look for that component name.  Select it, click Add, and carry on.

Another gotcha...    make sure you add that line that's immediately above "Public Class activeDirectorySearch", the one that says:

<System.Web.Script.Services.ScriptService()> _

Without it, this whole thing breaks down. 

Finally, you'll need to customize the Namespace:="http://localhost/" line and the two "Dim directory as DirectoryEntry("LDAP://DC=nerdliness,DC=com" lines to match your situation.  As mentioned, my simple example is looking at the top of the mythical nerdliness.com Active Directory domain and searching from there.  You'll need to figure out your own LDAP path for your situation.

Ok, other than that, we're really just got two webmethods named findUser and findContactInfo.  The first is the one we told our AutoComplete extender to use when we created our Default.aspx page.  The second is the one we referenced in our code behind file to find the contact info for a specific person when someone clicks our button.

They're both pretty similar, with a couple of minor differences.  They both take a string as input, for instance, but while the first returns an array of strings, the second returns a dictionary.  While you could modify the second one to return the data however you like (provided you make the appropriate changes to your code behind file, of course), you're stuck with the first one.  The inputs and outputs for the web method used by the AutoComplete extender are VERY specific, down to the name of the input string.  Change that from prefixText to something else and you'll find things not working very quickly.

Pretty much all the code in both methods is simply drilling down through the various collections returned from Active Directory until you get to the data you actually want.  Experiment around with that and you'll find you have access to a great deal of info kept in Active Directory. 

And that's really it...  You should be able to fire up that application let rock the house.  You might want to play with that filter part a little bit if you find that the query returns too many non-user results, but other than that everything should be solid. 
While I'm thinking about it, I need to give some love to RayV.  I bastardized the code he posted in this MSDN forum thread nearly two years ago to get my code working:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=372979&SiteID=1

Couple of weeks ago, I was doing some work on a personal Nerdly project on one of my laptops. That particular machine has a copy of SQL Server 2005 Express Edition already installed, and I often use that for my development needs. Has a good number of the same features that the full-blown SQL Server 2005 editions carry, but it's free to use, distribute, etc. Hard to beat that.

Anyway, using Microsoft's SQL is all well and good for most of my work projects, but this personal project is going to live on my webhost's boxes, and my cheap webhosting account doesn't include MS SQL databases. Needed a way to get the data from my MS SQL 2005 Express Edition database into my MySQL database.

The Express Edition can only create MSSQL backup files out of the box. You can download a watered down version of the Import/Export Wizard for some simple importing/exporting, and that might have worked if I could connect to the host's MySQL database remotely using an ODBC driver. Unfortunately, their server wouldn't allow connections from outside their subnet.

Way I figured, that gave me three options:

1. I could download and install MySQL locally, move my data from MSSQL to MySQL with the aforementioned watered down Import/Export Wizard, dump the contents from the local MySQL, and import into the hosted one with PHPMyAdmin or from the command line.
2. I could use the SQL Import/Export Wizard to export each table into a CSV, get my Find-and-Replace on, modify that output to create an INSERT statement for each line, then use PHPMyAdmin to Import the data.
3. I could script something to do Option 2 for me.

I went with Option 3. Already had Visual Studio 2003 installed on my workstation, so I figured writing some VB.NET code to do what I needed would be pretty easy. Take into account that I didn't feel like installing yet another database on this workstation and couple it with the fact that the kind of tedium in Option 2 would have likely caused me to stab out my own eyes and, well, it's a no-brainer.

So here's the setup... I have a simple Windows form with a single Text Field called txtTableName and a button called btnExport. The procedure that handles btnExport's click event is attached, if you want it, but it's really pretty simple. You give it the name of the table, it loops through the names of the columns in that table, and in constructs a list of SQL INSERT statements for each record in that table.

To me, the coolest part about this is that you don't need to know the names of all the columns in the table in order for it to create the output file. Instead, it uses Visual Basic's GetSchemaTable method to find that info for you.

With GetSchemaTable, you can find out all sorts of good info about a table's schema, from the column names and ordinals to whether it accepts NULLs or if it's an identity column. Each column in the table you're curious about ends up being a single record in the DataTable returned by GetSchemaTable, so it's pretty easy to just loop through all those rows to find out the information you need.

In this snippet, I loop through all the rows in the DataTable just to find the column name. I then put that column name into an array that I use later to both query the table I'm interested in and generate the INSERT lines.

For example, let's say I have a NERD table with three columns: NAME, AGE, and NERDLINESS. The DataTable returned by the GetSchemaTable method on that table would have three rows, one for each column. As I loop through the rows, I find the ColumnName column and insert it's value into my array (around line 19 below).

Finding the column names is the hardest part. After that, it's just a matter of querying the original table, putting the results from each row into another array, then spitting all of it out with the appropriate SQL keywords. Line 82 shows you how the INSERT statements are created.

Just a couple of things to keep in mind if you want to use this. First, you need to change your connection string to match your own database. That's on line 21. You'll also want to change line 5 to a path that actually exists on your machine.

Finally, if I really wanted to make this cool, I'd make it a little more user-friendly. For instance, maybe having a dropdown list of all the table names instead of having the user enter one manually. Or having the option to have it pull all data from all tables into one file at once. Both those would be pretty trivial at this point, but this did what I needed it to do. Still, let me know if you found this useful at all.

   1:      Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

   2:  

   3:          'Declaring a couple of strings we'll use later.

   4:  

   5:          Dim strOutputPath As String = "c:\nerds\"

   6:          Dim strSelectQuery As String = "SELECT * FROM "

   7:  

   8:  

   9:          'Require the user to enter a name of some kind.

  10:  

  11:          If txtTableName.Text = "" Then

  12:              MessageBox.Show("Please enter table name.")

  13:              Exit Sub

  14:          Else

  15:              strSelectQuery &= txtTableName.Text 'Finish our SELECT statement to use the tablename specified.

  16:              strOutputPath &= txtTableName.Text & ".sql"

  17:          End If

  18:  

  19:          'Create our SQLConnection.  I used a locally hosted SQL 2005 Express DB.  Your results will vary.

  20:          Dim sqlConn As New SqlConnection

  21:          sqlConn.ConnectionString = "workstation id=(local);packet size=4096;integrated security=SSPI;data source=nerdcomputer;" & _

  22:          "persist security info=False;initial catalog=nerddb"

  23:  

  24:          'Create our SQLCommand now that we have the query built.

  25:          Dim sqlSelectCommand As New SqlCommand(strSelectQuery, sqlConn)

  26:  

  27:          sqlConn.Open()

  28:  

  29:          Try

  30:              'First need to find out all the column names.  Executing this reader to get

  31:              'that info and put the resulting data into a DataTable.

  32:              Dim dtrReader As SqlDataReader = sqlSelectCommand.ExecuteReader(CommandBehavior.KeyInfo)

  33:              Dim tblSchema As DataTable = dtrReader.GetSchemaTable

  34:              Dim rowSchema As DataRow

  35:  

  36:  

  37:              'Each column in our table is represented as a row returned by GetSchemaTable.

  38:              'The array holding the column names is the same length as the number of rows in the table.

  39:              Dim intRowCount As Integer = tblSchema.Rows.Count

  40:              Dim strColumnNamesArray(intRowCount - 1) As String

  41:              Dim strColumnNames As String

  42:              Dim intColumnCounter As Integer

  43:              Dim intRowsWritten As Integer

  44:  

  45:  

  46:              'Putting the names of all the columns into an array for future use.

  47:              For Each rowSchema In tblSchema.Rows

  48:                  strColumnNamesArray(intColumnCounter) = rowSchema("ColumnName").ToString

  49:                  intColumnCounter += 1

  50:              Next

  51:              dtrReader.Close() 'Closing the reader used to get the schema info.

  52:  

  53:              'Joining the array into one string to construct our output line.

  54:              strColumnNames = Join(strColumnNamesArray, ",")

  55:  

  56:              'Now that we have the names of the columns, need the values for each row.

  57:              Dim strColumnNameValue As String

  58:              Dim strColumnOutput As String

  59:  

  60:              'Opening our reader, this time looking for the data in the table rather than the

  61:              'table schema.

  62:              dtrReader = sqlSelectCommand.ExecuteReader

  63:  

  64:              'Getting ready to write to our output file.

  65:              Dim wtrOutput As StreamWriter = New StreamWriter(strOutputPath)

  66:              While dtrReader.Read

  67:  

  68:                  'Building another part of our output string.

  69:                  'In each returned row, we loop through the column names and add the associated

  70:                  'value to our string.

  71:                  For Each strColumnNameValue In strColumnNamesArray

  72:                      strColumnOutput &= "'" & dtrReader.Item(strColumnNameValue).ToString.Trim & "',"

  73:                  Next

  74:  

  75:                  'The above loop adds a final comma.  Stripping that off.

  76:                  strColumnOutput = strColumnOutput.Substring(0, strColumnOutput.Length - 1)

  77:  

  78:                  'Constructing the actual INSERT query that will be written to the output file.

  79:                  'strColumnNames is the comma separated column names we made earlier.

  80:                  'strColumOutput is the string with the values contained in those columns.

  81:  

  82:                  Dim strOutputLine As String = "INSERT INTO " & txtTableName.Text & " " _

  83:                  & "(" & strColumnNames & ") " _

  84:                  & "VALUES (" & strColumnOutput & ");"

  85:  

  86:                  'Write that INSERT statement to our text file.

  87:                  wtrOutput.WriteLine(strOutputLine)

  88:  

  89:                  intRowsWritten += 1

  90:  

  91:                  'Clear the string names.  If skipped, additional lines are appended to

  92:                  'the previous line instead of being a line of their own.

  93:                  strColumnOutput = ""

  94:                  strOutputLine = ""

  95:  

  96:              End While

  97:  

  98:              'Close our file and our SQL connections.

  99:              wtrOutput.Close()

 100:              sqlConn.Close()

 101:  

 102:              'I like to know when it's done.

 103:              MessageBox.Show(intRowsWritten.ToString & " rows were written.")

 104:  

 105:  

 106:          Catch ex As Exception

 107:              MessageBox.Show(ex.Message)

 108:  

 109:          End Try

 110:  

 111:      End Sub