Thursday, June 28, 2012

Use LightSwitch to search Employee Details in Active Directory

     I develop applications for the Global Security, Investigations and Legal Departments of a Fortune 100 company in Silicon Valley with offices all over the world. Lately almost all applications I develop involve looking up Employee information,  I'm always asked can you include the ability to look up the 1st and 2nd level managers?

Fortunately Visual Studio LightSwitch with the help of the LightSwitch Teams  "LightSwitch Active Directory Sample" makes this really simple.

Project Description
    A simple lookup example that allows a user to look up the 1st and 2nd level managers for a given EmployeeID.

    Note:  I'm using the EmployeeID field because that is a guaranteed unique identifier, your search term may be different.
A great free tool for looking up Active Directory information is LDAP Browser by Softerra,

*** Before we begin I have to point out that I'm using Visual Studio Professional 2012 RC for this tutorial. ***

This tutorial will also work for the previous version of Visual Studio LightSwitch either as an add in or the standalone version. I use Visual Studio Professional with the LightSwitch add in at work.

I left the default project type as Desktop but this will also work as a Web Project.

  1. Create a new LightSwitch project, I chose the C# version for this tutorial and I'm naming mine "ADEmployeeSearch"
  2. Add a table to this project called EmployeeData with these 3 fields
 EmployeeID (String) (Required), FirstLevelManager (String), SecondLevelManager (String).
  3. Download the "LightSwitch Active Directory Sample" again I chose to download the C# version, a VB version is available as well.
  4. Unzip the archive in WinZIP or your favorite Archive handling application.
  5. Add a "List and Details Screen" and for Screen Data choose the EmployData Table and name this new screen EmployeeDetails.
    Your screen should look similar to this:

    Notice I'm leaving the First Level and Second Level Manager fields in place, if you do not use Active Directory you are still free to enter this information manually. And if you do use Active Directory these values will be overwritten by the returned values from AD.
  6. We now need to switch to File View and add two files from the Active Directory Sample, ActiveDirectoryHelper.cs and ApplicationDataService.cs
    Right Click the Server folder and select Add-Existing Item then navigate to  ActiveDirectoryHelper.cs, this is located in LightSwitch Active Directory Sample\C#\LDAP_CS_Demo\Server, we will need to make a couple changes to this file shortly.
  7. If the folder UserCode has not been created yet, create a new folder under the Server folder and name it UserCode, Right Click UserCode and select Add-Existing Item, then navigate to ApplicationDataService.cs, this is located at LightSwitch Active Directory Sample\C#\LDAP_CS_Demo\Server\UserCode. We will need to make a couple changes to this file as well.
  8. Open ActiveDirectoryHelper.cs, this file contains the most common fields in Active Directory but if your company has custom fields you will need to add them to this file if you intend on using them as search parameters. Add the following fields to the String Constants Region.  Keep in mind these values may be different in your company's Active Directory, consult your administrator or if you have the appropriate privs use the LDAP Browser.

    Add the following:

    public const string EMPLOYEEID = "sAMAccountName";

    public const string HIREDATE = "whenCreated";

    public const string COUNTRY = "co";

    The last two entries are not really necessary for this tutorial but you may find them to be useful if you wish to expand on this tutorial. You can now close the ActiveDirectoryHelper.cs we are finished with this file for now.
  9. Add two references to your Server project one for System.DirectoryServices and another for System.Configuration (We will use ConfigurationManager to read in values from our Web.config file, as you'll see later on).
  10. Build the project then open the EmployeeData table in the designer and click the Write Code dropdown and select EmployeeDatas_Inserting, this will open the ApplicationDataService.cs file and you will now find the EmployeeDatas_Inserting method stub has been added for you. 

    Important TIP
    : You can't call the server code directly from the client, all interactions between the client and server happen within the Save, Inserting, and Updating pipelines with LightSwitch applications.
  11. Ok in the default ApplicationDataService.cs, the LDAP directory is currently hard coded, this is not ideal if you wish to sell or give away your application to other clients, this value will change from client to client, so to make it possible for the clients Administrator to modify this setting and allow our code to read in whatever the current value may be, we need to modify our Web.config file, you can find this file using Windows Explorer, ADEmployeeSearch\ADEmployeeSearch\Server.

    Add the following two entries after <appsettings> xml tag
<add key="LDAPAVAILABLE" value="true"/>
    <add key="LDAPDirectory" value="LDAP://dc=[your domain],dc=com"/>

***NOTE: You need to replace [your domain] consult your Network Administrator
  12. In ApplicationDataService.cs delete the line 

    string domain = @"LDAP://";
    and add the following two declarations:

    // We use ConfigurationManager to read in the values we added to our Web.config file

    domain = ConfigurationManager.AppSettings["LDAPDirectory"];

    Boolean ActiveDirectoryAvailable = Convert.ToBoolean(ConfigurationManager.AppSettings["LDAPAVAILABLE"]);
  13. Delete the methods DistributionLists_Inserting and CreateMembers
    We won't be using them in this example.
  14. Add the following properties after the ActiveDirectoryAvailable property
            string Name = string.Empty;
            string EmailAddress = string.Empty;
            string Phone = string.Empty;
            string Title = string.Empty;
            DateTime HireDate;
            string Country = string.Empty;
            string Department = string.Empty;
            string FirstLevelManager = string.Empty;
            string FirstLevelManagerEmail = string.Empty;
            string SecondLevelManager = string.Empty;
  15. Add the following method
    private void SearchEmployeeRecord(string EmployeeID)
             string[] props = { 

    var propResults = ActiveDirectoryInfo.UserPropertySearchByName(EmployeeID, domain, props);

    //Parse out the hiredate
    string hdate = propResults[ActiveDirectoryInfo.strings.HIREDATE];
    // Determine if this is an actual Employee
    if (hdate != ActiveDirectoryInfo.strings.VALUENOTFOUND)
        Name = propResults[ActiveDirectoryInfo.strings.DISPLAYNAME];
        EmailAddress = propResults[ActiveDirectoryInfo.strings.EMAIL];
       Phone = propResults[ActiveDirectoryInfo.strings.PHONE];
       Title = propResults[ActiveDirectoryInfo.strings.TITLE];
       Country = propResults[ActiveDirectoryInfo.strings.COUNTRY];
       Department = propResults[ActiveDirectoryInfo.strings.DEPARTMENT];

                    string[] split = hdate.Split(new Char[] { '/' });
                    int hmonth = Convert.ToInt16(split[0]);
                    int hday = Convert.ToInt16(split[1]);
                    string hyear = split[2];
                    string[] split2 = hyear.Split(new Char[] { ' ' });
                    int hyr = Convert.ToInt16(split2[0]);

                    HireDate = new DateTime(hyr, hmonth, hday);

       //Parse the manager name out of the ActiveDirectory path
     Tuple<string, string> managerKey = ActiveDirectoryInfo.ParseUserAndDomain(propResults[ActiveDirectoryInfo.strings.REPORTSTO]);
                    string firstManagerDisplayName = string.Empty;
                    string firstManagerReportsTo = string.Empty;

                    string[] managerProps = { ActiveDirectoryInfo.strings.DISPLAYNAME,

     if (managerKey.Item2 != "")
        var managerName = ActiveDirectoryInfo.UserPropertySearchByName(managerKey.Item1, domain, managerProps);
    firstManagerDisplayName = managerName[ActiveDirectoryInfo.strings.DISPLAYNAME];

    var managerpropResults = ActiveDirectoryInfo.UserPropertySearchByName(managerName[ActiveDirectoryInfo.strings.EMPLOYEEID],domain,managerProps);

    // Parse the 2nd level manager name out of the Active Directory Path

    Tuple<string, string> secondmanagerKey = ActiveDirectoryInfo.ParseUserAndDomain(managerpropResults[ActiveDirectoryInfo.strings.REPORTSTO]);
    if (secondmanagerKey.Item2 != "")
        var seconManagerName = ActiveDirectoryInfo.UserPropertySearchByName(secondmanagerKey.Item1, domain, managerProps);
    firstManagerReportsTo = seconManagerName[ActiveDirectoryInfo.strings.DISPLAYNAME];

                    // 1st Level Manager
                    FirstLevelManager = firstManagerDisplayName;
                    // 2nd Level Manager
                    SecondLevelManager = firstManagerReportsTo;
  16.  In the method EmployeeDatas_Inserting
    Add the following code:
    if (entity.EmployeeID != "" && entity.EmployeeID != null && ActiveDirectoryAvailable)
                    SearchEmployeeRecord(entity.EmployeeID );
                     if (this.FirstLevelManager != "")
                           entity.FirstLevelManager = this.FirstLevelManager;
                    if (this.Name != "")
                        entity.EmployeeID = this.Name;
  17. (Optional) Open EmployeeData table, select EmployeeDatas_Updating from the Write Code drop down and enter or copy the following code.
    partial void EmployeeDatas_Updating(EmployeeData entity)
                if (ActiveDirectoryAvailable)

                    if (this.FirstLevelManager !="")
                    entity.FirstLevelManager = this.FirstLevelManager;

                    if (this.Name != "")
                        entity.EmployeeID = this.Name;

  18. Build the project once again and correct any errors and if all is well launch the application, Enter an EmployeeID, click OK, click Save. You should now have access to the FirstLevelManager and SecondLevelManager information.

    From here you can expand on this tutorial to bring back additional data as required.
    Hope this helps.

My PowerApps Videos

Demo of using the PowerApps Office365Users Connector and Microsoft Graph to surface User information from Azure Active Directory with the Us...