Monday, January 10, 2011

An alternative approach to TreeView for displaying and selecting active directory OU structure using infopath form

I was trying to figure out a way to show AD tree structure inside Infopath form without much luck. The reason for doing this is to put it into our new staff form so we can designate the OU where the new staff will be created so we can automate it in our biztalk process.

Anyway, instead of trying to show the whole tree structure, I came up with a workaround which is much easier while still achieving the same result.

the core component here is a web service to get the list of sub OUs by supplying a parent OU DSN, here is the source code for the web service:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml;
using System.DirectoryServices;

/// <summary>
/// Summary description for GetADChildNodesWS
/// </summary>
[WebService(Namespace = "")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class GetADChildNodesWS : System.Web.Services.WebService

    public GetADChildNodesWS()

        //Uncomment the following line if using designed components 

    public System.Xml.XmlDocument getADChildNodes(string dsnpath)

        XmlDocument xmlnodes = new XmlDocument();
		if (dsnpath == "" || dsnpath ==null)
			dsnpath = "LDAP://DC=mycompany,DC=com";
        string updsnpath = dsnpath.Replace("LDAP://", "");
        if (updsnpath.Substring(0, 3).Equals("OU="))
            updsnpath = "LDAP://" + updsnpath.Substring(updsnpath.IndexOf(',')+1, updsnpath.Length - updsnpath.IndexOf(',')-1);
            updsnpath = "LDAP://DC=mycompany,DC=com";
        string xmlstring = "<OU><SUBOU><name>Up one Level</name><value>" + updsnpath + "</value></SUBOU>";
        DirectoryEntry entry = new DirectoryEntry(dsnpath);

        entry.Username = "username";
        entry.Password = "pass";

        DirectorySearcher searcher = new DirectorySearcher(entry);
        searcher.Filter = "(ObjectClass=organizationalUnit)";
        searcher.SearchScope = SearchScope.OneLevel;
        SearchResultCollection resultColl = searcher.FindAll();

        if (resultColl.Count > 0)
            foreach (SearchResult result in resultColl)
                xmlstring += "<SUBOU>" + "<name>" + result.Properties["OU"][0].ToString() + "</name><value>" + "LDAP://OU=" + result.Properties["OU"][0].ToString() + "," + dsnpath.Replace("LDAP://", "") +
            xmlstring += "</OU>";
            return xmlnodes;


        return null;

once the web service is deployed, you then simply add a dropdown list to the infopath form, configure the drodown to get choice from an external data source which is the web service call.

Add a rule to the drodown with condition as the field is present, add two actions within the rule, one to set the query field value, another one to "query for data".

the way it works here is biz different, you need to keep clicking the same dropdown list untill you reach the OU from your root node, you can also navigate back by clicking on 'Up one Level' item, also select the empty option brings you back to the root level directly.

Sunday, January 9, 2011

Using Missing Index Information to Write CREATE INDEX Statements

Use the following guidelines for ordering columns in the CREATE INDEX statements you write from the missing indexes feature component output:

*List the equality columns first (leftmost in the column list).
*List the inequality columns after the equality columns (to the right of equality columns listed).
*List the include columns in the INCLUDE clause of the CREATE INDEX statement.
*To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.