Get Active Directory Name From SQL User ID

Question

I have a SQL table that contains active directory IDs, but no names. So, the core of the issue is that I need to find the associated names. I have tried to just use SQL to query the active directory, but I have run into issues with that, so my next attempt is to use C#.NET and display those IDs and their associated active directory "givenname" on the page.

I am currently trying to do this using a Gridview.

The effort below represents my attempt to create a gridview column next to the ID column and populate it with the associated name of the user.

What is currently being returned now is the name of the user in the first column, repeated over and over.

DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("spIDsSelect", dbConnection);
                adapter.Fill(dt);
                gvLookup.DataSource = dt;
                gvLookup.DataBind();

            }
            string connection = "LDAP://....";
            using (DirectoryEntry DE = new DirectoryEntry(connection))
            {
                DirectorySearcher dssearch = new DirectorySearcher(connection);
                foreach (DataRow dr in dt.Rows)
                {
                    var name = dt.Rows[0][0].ToString();

                    dssearch.Filter = String.Format("(&(objectCategory=user)(samaccountname={0}))", name);

                    foreach (SearchResult sresult in dssearch.FindAll())
                    {
                        DirectoryEntry de = sresult.GetDirectoryEntry();
                        {
                            foreach (GridViewRow row in gvLookup.Rows)
                            {
                                ((Label)row.FindControl("lbName")).Text = de.Properties["givenname"][0].ToString();
                            }
                        }

                    }
                }
            }



<asp:GridView ID="gvLookup" runat="server" AllowSorting="True"  AutoGenerateColumns="False" 
DataSourceID="Lookup" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Left" 
DataKeyNames="recID" >

<AlternatingRowStyle BackColor="White"/>     
<Columns>

    <asp:BoundField DataField="ID" HeaderText="ID" 
        HeaderStyle-HorizontalAlign="Left">
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </asp:BoundField>

    <asp:TemplateField HeaderText="Name" >
        <ItemTemplate>
            <asp:Label runat="server" ID="lbName"></asp:Label>
        </ItemTemplate>
    </asp:TemplateField>

</Columns>
</asp:GridView>

Answer 1

This works. No need to loop through all data rows, only the Gridview. I am expecting only one result per ID, and can populate the label in my Gridview with the Directory Entry name. Important to check to be sure sresult is not null, as some users within the table may no longer be valid, which would prompt an object reference error.

string connection = "LDAP...";
            using (DirectoryEntry DE = new DirectoryEntry(connection))
            {
                DirectorySearcher dssearch = new DirectorySearcher(connection);
                {
                    var sgID = ((Label)gr.FindControl("lbID")).Text;
                    dssearch.Filter = String.Format("(&(objectCategory=user)(samaccountname={0}))", ID);

                    SearchResult sresult = dssearch.FindOne();

                    if (sresult != null)
                    {
                        DirectoryEntry de = sresult.GetDirectoryEntry();
                        ((Label)gr.FindControl("lbName")).Text = de.Name;
                    }
                }
            }

Answer 2

Just add a column to your datatable after you retrieve the data from the database:

dt.Columns.Add("ActiveDirectoryName", typeof(string));