travislowdermilk.com

This is a continuation of a series about creating a robust search U.I. for SharePoint.

We’ve created our U.I. elements and embedded them into the master page. We’ve also implemented JQuery and connected our U.I. to a web service to populate our auto completion.

The next questions is: “How do I populate it the auto completion with suggestions?”

Well, before we can answer that, we have to figure out how we are going to store the search queries in the first place. The challenge, as you might well imagine, is that we can’t just store every string value a user might type into the search box.

For instance, if several users type in the keywords “dress code”, we don’t want to store that phrase, multiple times, into the database.

After some lengthy discussions with my brother and a couple of napkin drafts, we came up with a rather simple solution.

Rather than store the entire phrase “dress code”, couldn’t we just parse the words “dress” and “code” and store them into a table with an identifier?

So, instead of this:

We get this:

I decided to create a stored procedure that would collect a search phrase, parse it into “tags”, and INSERT them into the “Tag” table. If the record already existed, then an error would be caught and I would move on.

Note: I won’t go into detail about how I connected the stored procedure to the user control. Suffice to say, I created my connections and parameters in my web service. The U.I. passes the phrase to the web service when the user executes a search. The web service then makes a connection to the database, collects the phrase into a parameter, and calls the stored procedure. This is all standard .NET data stuff and there are a plethora of articles on this subject.

Here is the SQL code for my stored procedure “spStoreSearch”:

-- Add the parameters for the stored procedure here
	@TagList nvarchar(255),
	@NetworkID nvarchar(10)
	AS
	BEGIN
 --Please refer to attached code.

Note:For some reason, my blog article wont post with the SQL code in it. Here’s a link to the script.

You might have noticed, I’m also storing a value of “NetworkID” into a table called “Context” and values of “ContextID”, “Ordinal”, and “TagID” in a table called “Context_Tag”. As you might’ve imagined at this point, I cannot just store the individual tags into the database. They would lose their context. In other words, I would only be able to say: “Guess what guys!!! The word ‘dress’ is the most popular search word!!!! WOOO HOOO!!! YEAH!” They’d look at me like I had lost my marbles. The word “dress” could have so many different contexts: “dress code”, “dress policy”, “informal dress”, “dress code violation”…You get the idea.

So I have to be able to store the contextual information in a meaningful way.

Also, I would like to know who’s completing the search.

Thankfully, SharePoint provides that information by way of collecting your network credentials. I wrote a blog post about how you can go about getting that kind of user information from SharePoint. I used the same methodology to get my “NetworkID” here.

Combined, the information would get stored like this:

Example: At 0750 on 03/16/2010, user “tlowderm (a.k.a Travis Lowdermilk) searched the phrase “dress code”
Tag Table

Context Table

Context_Tag

As you can see, this is as normalized as it’s gonna get (unless you can suggest otherwise?). You’ll also notice the column “Ordinal” in the Context_Tag table.
This is used to reference the order a sequence of tags were entered.

For example, if I were to query the phrase “dress code”, the result would be “code dress” if I were to ORDER BY tag alone.

The next challenge is putting together queries that present meaningful search data. Answering questions like: “What’s the most popular search phrase?” and “What searches has Travis requested?”.

In the next post, SharePoint Search on Steroids Part 4 – Retrieving Search Queries, I’ll detail how to retrieve this kind of information and how to enrich the user search experience by
injecting suggestions based on popular search phrases.

This is a continuation of a series about creating a robust search U.I. for SharePoint.

Connecting the U.I. to Search Results

We now have our user control embedded on the master page of our site. First we should have the U.I. forwarding the user to the search results upon clicking “Search” or submitting a query.

This is simple enough. In reality, we are just redirecting them to the appropriate search results page and placing their query in a query string parameter:

//This would be for a general query
Response.Redirect("/SearchCenter/Pages/results.aspx?k=" + txtSearch.Text.Replace(" ", "%20"));
//This would be for a people search
Response.Redirect("/SearchCenter/Pages/peopleresults.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=People");
//This would be for a custom scope
Response.Redirect("/SearchCenter/Pages/Results.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=This isMyCustomScope");

You can create a U.I. that stores the selected search type into a session variable. I created tabs upon the top of the search. If the user clicks on “People”, for instance, I store a session variable of the value “people” and direct them to the appropriate page (peopleresults.aspx) upon the search click or search submit event:

Each tab stores a value in a session variable. The variable redirects the user to the appropriate search result page.

Enhancing search with JQuery and WebServices

We first need to integrate JQuery into our SharePoint site.  I imagine there are many ways to do this, however, this approach seemed the most straightforward to me:

1. Download the JQuery code.  It’s also available on the Microsoft CDN, but for an Intranet site, I prefer offering it from our own servers.

2. Open up SharePoint Designer and create a folder in the root called “_scripts” and place the jquery.js file there.

3. Open up your masterpage in SharePoint Designer and in the <head> section add:

<script type="text/javascript" src="/_scripts/jquery-1.3.2.js"></script>

4. You’ll also want to download the autocomplete plugin from JQuery.  This plugin allows us to present suggestions (very much like Google) as the user begins to type their query.

5. Follow steps 2 and 3 to add your autocomplete.js file

For brevity, I won’t go  into specifics about how the autocomplete js file works.  There are plenty of other sources on the Internet.  However, in order for our suggestions to be produced dynamically we do need to connect our user control to a web service.  In SharePoint this may not be as easy as it seems.  I chose to integrate the web service code into my existing site.  I’m sure there are many other methods, but this seemed the most straight-forward way:

1. Add a “New Item…” to your Visual Studio Web Project (the same project that contains your user control)

2. Choose “Web Service” to create a new .asmx file.

3. Write the necessary web method(s) to provide data to your autocomplete script (We’ll cover this in more detail in Part 3)

Now, when we compile our web project, the .dll will include code for our web service as well.  The .dll will go into our bin folder of our site, we will also need to create a space for our .asmx file.  I found that creating a folder in the location of your site’s file system is easiest (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>).

I created a folder called “_webservices” and placed the .asmx file in there.

I then used a ScriptManagerProxy control inside the user control to create a script proxy to be used by the autocomplete jquery plugin:

<asp:ScriptManagerProxy ID="SMP" runat="server">
<Services>
<asp:ServiceReference Path="/_webservices/<web service name>.asmx" InlineScript="false" />
</Services>
</asp:ScriptManagerProxy>

You will need to give “Everyone” or “Domain Users” access to this folder because the service actually provides a .js file to be embedded into your site at load time.

We now have the “plumbing” in order.  Let’s review:

1. We have a user control on the master page for all of our U.I. elements

2. We’ve added the jquery and autocomplete .js files, using SharePoint Designer, to a “_scripts” folder in the root.  We created references to those files in the <head> section of the master page.

3. We added a web service (.asmx) item to our Visual Studio project and added the necessary code to make our autocomplete plugin work.

4. The user control creates a proxy to connect to a web service that is stored in our site’s folder structure (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>\_webservices\webservice.asmx)

The next step will be to capture and store search queries so that we can provide things like tag clouds and suggestions.

Coming up… SharePoint Search on Steroids Part 3 – Storing Search Queries

SharePoint has pretty amazing “people” searching capabilities. However, if you are like our organization, your Active Directory data may not be as complete as you’d like it to be. Or maybe you manage identities using another system like PeopleSoft. So the question becomes: How do you get identity information into SharePoint?

I decided to develop a small console application that would insert user profile data, pulled from our PeopleSoft database, into the SharePoint User Profile Store:

What kind of dark magic must be peformed to get information in here!?

Also, we had employee photos, sitting in a web directory on the Central Admin, front-end web server, that I wanted to point to for the profile pictures.

All of this can be done using the SharePoint User Profile Manager class in the SharePoint API.

1. Start a Console Project and add references to System.Web, Microsoft.SharePoint, and Microsoft.Office.Server

(NOTE: If you are deving on a box that doesn’t have WSS or MOSS installed, you won’t be able to debug, however, you can still add the .dll files to your project for compiling.  Just go to your MOSS or WSS server and pull the Microsoft.Office.Server.dll and the Microsoft.SharePoint.dll [%Program Files%Common Files\Microsoft Shared\web server extensions\12\ISAPI] into your project)

Below is the code I used.  Obviously, there are many variants to this to make it work and no two data structures are the same.  However, I think the general concept could work for many different environments.

Essentially, what I did was iterate through a DataTable that had all my employee data in it.  I then pulled up the user profile, using the SharePoint Profile Manager, and inserted the profile property values.

All of the values I’m referencing (PhoneNo, JobTitle, Supervisor_NetworkID, etc.) were available in our PeopleSoft database. If you don’t have access to that level of data, or it’s not being collected in your organization…well…this will be kinda hard.

Finally, I committed the changes in SharePoint and moved on to the next record:

using Microsoft.SharePoint;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SharePoint_Employee_Photos
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Get usernames
                //Retrieve E-Mail Addresses
                SqlConnection sqlconn1 = new SqlConnection("Data Source=Server;Initial Catalog=Database;User Id=dbuser;Password=dbpassword;");
                SqlCommand sqlcomm1 = new SqlCommand("Stored_Procedure", sqlconn1);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sda = new SqlDataAdapter(sqlcomm1);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                using (SPSite spSite = new SPSite(@"http://sharepoint.site.org"))
                {
                    ServerContext siteContext = ServerContext.GetContext(spSite);
                    UserProfileManager pManager = new UserProfileManager(siteContext);

                    foreach (DataRow dr in dt.Rows)
                    {
                        String strUsername = "Domain\\" + dr["NetworkID"].ToString();
                        UserProfile userProfile = null;
                        //Check to see if the profile exists in SharePoint
                        if (pManager.UserExists(strUsername))
                        {
                            userProfile = pManager.GetUserProfile(strUsername);
                            Console.WriteLine("User Success: Domain\\" + dr["NetworkID"].ToString());

                            //Update any of the user profile property values

                            //Photos are made available through a separate SSIS task that queries the PeopleSoft database and outputs
                            //the images to a web folder.  The web folder is served on my Central Admin, web front end server.
                            //Let's make sure the file is there first.
                            if (File.Exists(@"[C:\PhotoWebsite\photos\" + dr["EmpID"].ToString() + ".jpg"))
                            {
                                userProfile[PropertyConstants.PictureUrl].Value = "http://website.photos.org/photos/" + dr["EmpID"].ToString() + ".jpg";
                            }
                            if (!dr["PhoneNo"].Equals(DBNull.Value))
                            {
                                if (!dr["PhoneNo"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.WorkPhone].Value = dr["PhoneNo"].ToString();
                                }
                            }
                            if (!dr["DeptName"].Equals(DBNull.Value))
                            {
                                if (!dr["DeptName"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Department].Value = dr["DeptName"].ToString();
                                }
                            }
                            if (!dr["JobTitle"].Equals(DBNull.Value))
                            {
                                if (!dr["JobTitle"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Title].Value = dr["JobTitle"].ToString();
                                }
                            }
                            if (!dr["Supervisor_NetworkID"].Equals(DBNull.Value))
                            {
                                if (!dr["Supervisor_NetworkID"].Equals(String.Empty))
                                {
                                    String strSupervisorUsername = "Domain\\" + dr["Supervisor_NetworkID"].ToString();
                                    if (pManager.UserExists(strSupervisorUsername))
                                    {
                                        userProfile[PropertyConstants.Manager].Value = strSupervisorUsername;
                                        Console.WriteLine("Supervisor Success: Domain\\" + dr["Supervisor_NetworkID"].ToString());
                                    }
                                }
                            }

                            userProfile[PropertyConstants.WorkEmail].Value = dr["Email"].ToString();

                            //Commit the changes
                            userProfile.Commit();
                            Console.WriteLine("Import Success!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.ReadLine();
            }
        }
    }
}