I recently received a request to join data from an external ODBC data source to the internal results of a SharePoint search. To be specific, the client wished to outer join SQL data on SharePoint’s search results. Ideally, one would create a custom SQL data source and modify SharePoint’s SQL query to do the join. This solution would scale much better than the one I ended up implementing and describe below. In this example, I’ll use the hypothetical situation of incorporating HR information with the People information stored in SharePoint.
To join across the disparate sources I’ll use the users’ SAMAccountName, which is stored in the database as ‘urn:schemas-microsoft-com:sharepoint:portal:profile:AccountName’. You could have just as easily used an employee ID that has been pushed to AD, but you must remember to include it in the indexing. My approach assumes that for each employee you also have their login stored in the database. I began by overriding the Search Results web part:
public class MySearch : Microsoft.SharePoint.Portal.WebControls.SearchResults
{
protected Xml xmlResults = new Xml();
protected DataSet dsResults;
protected string strOut;
private const string strConn = "data source=XYZ; initial catalog= XYZ; user id= XYZ; password= XYZ; persist security info=True";
Then we must modify the select statement of the query to include our joining property. It should look something like:
protected override string GenerateQueryString(string strKeyword,
System.Collections.ArrayList rgScopeList, string
strWhereAndPart, out string strSavedQuery)
{
string strSelect = base.QueryTemplateSelectPart;
strSelect = "SELECT \"urn:schemas-microsoft-com:sharepoint:portal:profile:AccountName\", " + strSelect.Substring(6);
base.QueryTemplateSelectPart = strSelect;
return base.GenerateQueryString (strKeyword, rgScopeList,
strWhereAndPart, out strSavedQuery);
}
The next step is to intercept the result and return it as a dataset:
protected override object IssueQuery(string strQuery,
int startRowIndex, int endRowIndex)
{
dsResults = (DataSet) base.IssueQuery(strQuery,
startRowIndex, endRowIndex);
return dsResults;
}
Once the base dataset is created we’ll have to add the desired columns for which we will populate with our HR database information. Then we can retrieve our HR database information, which we’ll do by executing a stored procedure and return those results into a separate dataset. All of the code that follows is implemented in the overridden CreateChildControls function.
dsResults.Tables["Table"].Columns.Add("EmpID");
dsResults.Tables["Table"].Columns.Add("BillRate");
dsResults.Tables["Table"].Columns.Add("MoreHRInfo");
dsResults.Tables["Table"].Columns.Add("IconURL");
SPWeb currWeb = SPControl.GetContextWeb(Context);
SqlConnection objConn = new SqlConnection(strConn);
SqlCommand cmdSQL = new SqlCommand("proc_EmployeeData", objConn);
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.Parameters.Add("@SAMAccountName",
currWeb.CurrentUser.LoginName.Trim());
objConn.Open();
SqlDataAdapter daSQL = new SqlDataAdapter();
daSQL.SelectCommand = cmdSQL;
DataSet dsSQL = new DataSet();
daSQL.Fill(dsSQL, "myTable");
objConn.Close();
Since the two datasets likely won’t have a parent child relationship, we won’t be able to join them using dataset/datatable relations. Instead, you can do a manual join by filtering the SharePoint dataset down to the desired class of objects, such as SPSPeople for people, and then doing a find on the dataset’s PrimaryKey field, which we set to be the Username. This has the effect of joining on their joining field (SAMAccountName to Username). Where they match you can insert the data from the desired SQL field into the created field on the SharePoint dataset.
dsSQL.Tables["myTable"].PrimaryKey = new DataColumn[]
{dsSQL.Tables["myTable"].Columns["Username"] };
string tmpLogin;
string tmpVal;
for(int i=0; i
{
tmpVal = dsResults.Tables["Table"].Rows[i]["DAV:displayname"].ToString().Trim();
if(dsResults.Tables["Table"].Rows[i]["DAV:contentclass"].ToString().Trim() == "urn:content-class:SPSPeople")
{
//ItemArray[1] is the index of the AccountName in the
//select statement we created.
//Substring(x) is used to eliminate the ‘Domain\’
//prefix of the AccountName
tmpLogin = dsResults.Tables["Table"].Rows[i].ItemArray[1].ToString().Substring(7);
DataRow myRow = dsSQL.Tables["myTable"].Rows.Find(tmpLogin.Trim());
if (myRow != null)
{
dsResults.Tables["Table"].Rows[i]["EmpID"] = myRow["HREmpID"].ToString().Trim();
dsResults.Tables["Table"].Rows[i]["BillRate"] = myRow["HRBillRate"].ToString().Trim();
dsResults.Tables["Table"].Rows[i]["MoreHRInfo"] = myRow["MoreData"].ToString().Trim();
}
}
else
{
if(tmpVal.LastIndexOf(".") != -1)
{
dsResults.Tables["Table"].Rows[i]["IconURL"] = SPUtility.MapToIcon(currWeb, tmpVal, "");
}
}
}
Once the datasets are built, we must rebuild the presentation layer. I accomplished this by obtaining the XML data from the dataset and applying an XSL transformation. Note the else condition in the previous code block for when the class is not ‘SPSPeople’. This is necessary to determine the icon to display.
xmlResults.TransformSource = "_layouts/custom/xsl/Transform.xsl";
xmlResults.DocumentContent = dsResults.GetXml();
In my next post, I will focus on the XSL file to build the presentation layer and will also make the CS file available for the above example.
Before trying to run. Isn't that how the saying goes? Unfortunately, the impetus for SharePoint projects tend to be either very specific or extremely broad, neither of which extremes are conducive to building the necessary understanding for a successful implementation. The former forces one to look at SharePoint solely through the lens of the particular issue at hand. This is quite a common approach for assessing the value of any product... Answering the question, “How well does it meet our needs?” So, it seems like a positive, right? Well, that is until you sit down and actually attempt to build the solution. Oh, how frustrating those first few steps can be! It’s been my experience that firms going down this path actually tend to underestimate the overall value of SharePoint. You’re not alone if you feel as though SharePoint does 80% of everything and 100% of nothing. Enter one of SharePoint’s greatest assets, its extensibility.
By contrast, looking at SharePoint from too broad of a perspective can result in an overestimation of its “out of the box” capabilities and an underestimation of the additional resources required. After all, SharePoint looks like the holy grail of products at first glance (and some will legitimately argue that in many cases it is, especially if you’re primarily a Microsoft shop)! Between Windows SharePoint Services and SharePoint Portal Server you have a relatively inexpensive portal with personalization, a fully fledged collaborative environment, a knowledge management solution, etc. However, despite its extensive functionality, there is still much to be done.
My first experience with SharePoint had a very specific driver when a request came through for a document management solution. I immediately found myself running into one roadblock after the next. How does one programmatically upload documents, create document libraries, and folders? Can the upload file size limit be circumvented? SharePoint's search looks great, but how do I create a custom search? How do I associate metadata (not to mention large amounts of data that require text data types or data that may exist in a different SQL database) with an uploaded file? Then there are those tasks that one would think should be downright simple, and in fact they may very well be, but are agonizingly difficult due to the foreign nature of SharePoint. So, with the product still in beta and very little documentation available, off I went trying to reverse engineer SharePoint. Oh wait, shouldn’t I figure out what SharePoint is first? I’ll discuss this project (and many of its mistakes) along with some of the other issues I mention here over my next couple posts.
One last thought… Tackling SharePoint, a product which represents a paradigm shift on so many fronts, necessitates an open mind from everyone involved. This ranges from the stakeholders and project managers to IT and the developers. Yes, a few simple tasks appear to be more difficult and cryptic then they were in the past (also to be discussed later). However, more times than not it merely requires a different way of thinking. In all its robustness SharePoint can change the way a firm does business, and even in its most limited form, it will at least make current processes more efficient.
Kyle