SharePoint: July 2013

Searching users details via UPS has certain limitation.
We can't do property filtering using based on metadata.
Below code tries to get user data via Simple Search (can be achieved via UPS ) and Advanced Search ( using search service).
Please note the query formed with * character will take only when providing full text value, e.g. if you want to search "Kumar" as last name we can't use "Ku*". If you want search with partial text like that we can use Like instead of Contains.

Namespace used
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.Office.Server.Search;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using System.Data;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Server.Search.Administration;
using Microsoft.Office.Server.Search.Query;

Simple Search

protected void btnSearch_Click(object sender, EventArgs e)
        {
            GetUserDetails(txtSearchBox.Text.Trim());
        }

        private void GetUserDetails(string searchTxt)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    ProfileBase[] userProfiles = null;
                    SPServiceContext cntx = SPServiceContext.GetContext(site);
                    UserProfileManager userProfileMgr = new UserProfileManager(cntx);

                    userProfiles = userProfileMgr.Search(searchTxt);
                    //ServerContext cntx = ServerContext.GetContext(site);
                    SearchResultBind(userProfiles);
                }
            });

        }
Advanced Search

private void GetPeopleSearchResult()
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
           {
               using (SPSite site = new SPSite(SPContext.Current.Site.ID))
               {
                   SPServiceContext context = SPServiceContext.GetContext(site);
                   SearchServiceApplicationProxy searchProxy = context.GetDefaultProxy(typeof(SearchServiceApplicationProxy)) as SearchServiceApplicationProxy;
                   SearchServiceApplicationInfo ssai = searchProxy.GetSearchServiceApplicationInfo();
                   SearchServiceApplication application = Microsoft.Office.Server.Search.Administration.SearchService.Service.SearchApplications.
        GetValue<SearchServiceApplication>(ssai.SearchServiceApplicationId);

                   string qry = "SELECT Firstname, Lastname,PreferredName, AccountName, Department, workemail, WorkPhone,Title   ";
                   qry += " FROM SCOPE() WHERE \"scope\" = '" + "People" + "' ";
                   if (!string.IsNullOrEmpty(txtFirstName.Text))
                       qry += "AND CONTAINS (Firstname,'" + txtFirstName.Text.Trim() + "*')"; // working based on default metadata mapping
                   if (!string.IsNullOrEmpty(txtLastName.Text))
                       qry += " AND CONTAINS (Lastname,'" + txtLastName.Text.Trim() + "*') "; // working based on default metadata mapping
                   if (!string.IsNullOrEmpty(txtDepartment.Text))
                       qry += "AND CONTAINS (Department,'" + txtDepartment.Text.Trim() + "*')"; // working based on default metadata mapping
                   if (!string.IsNullOrEmpty(txtEmail.Text))
                       qry += "AND CONTAINS (workemail,'" + txtEmail.Text.Trim() + "*') "; // working based on default metadata mapping
                   if (!string.IsNullOrEmpty(txtPhone.Text))
                       qry += "AND CONTAINS (WorkPhone,'" + txtPhone.Text.Trim() + "*')";
                   if (!string.IsNullOrEmpty(txtJobTitle.Text))
                       qry += " AND CONTAINS (Title,'" + txtJobTitle.Text.Trim() + "*') ";
                   // qry += " AND FREETEXT(*, '" + lblLastName.Text +'*' +"')";

                   // KeywordQuery query = new KeywordQuery(searchProxy);

                   FullTextSqlQuery query = new FullTextSqlQuery(searchProxy);

                   query.QueryText = qry;
                   query.ResultTypes = ResultType.RelevantResults;
                   query.KeywordInclusion = KeywordInclusion.AllKeywords;
                   query.RowLimit = 1000;
                   query.ResultsProvider = Microsoft.Office.Server.Search.Query.SearchProvider.Default;
                   //query.KeywordInclusion = KeywordInclusion.AllKeywords;

                   ResultTableCollection results = query.Execute();
                   ResultTable result = results[ResultType.RelevantResults];
                   DataTable dt = new DataTable();
                   dt.Load(result, LoadOption.OverwriteChanges);
                   SearchResultBind(dt);
               }
           });

        }

     private void SearchResultBind(DataTable usersDetail)
        {
            DataTable tblUsersDetails = new DataTable();
            tblUsersDetails.Columns.Add("PreferredName", typeof(string));
            tblUsersDetails.Columns.Add("AccountName", typeof(string));
            tblUsersDetails.Columns.Add("Department", typeof(string));
            tblUsersDetails.Columns.Add("WorkPhone", typeof(string));
            tblUsersDetails.Columns.Add("Title", typeof(string));
            foreach (DataRow usrDetail in usersDetail.Rows)
            {
                DataRow dr = tblUsersDetails.NewRow();
                dr["PreferredName"] = usrDetail["PreferredName"] != null ? usrDetail["PreferredName"] : string.Empty; //
                dr["AccountName"] = usrDetail["ACCOUNTNAME"] != null ? usrDetail["ACCOUNTNAME"] : string.Empty; //usrDetail["AccountName"].Value;
                dr["Department"] = usrDetail["DEPARTMENT"] != null ? usrDetail["DEPARTMENT"] : string.Empty; //
                dr["WorkPhone"] = usrDetail["WorkPhone"] != null ? usrDetail["WorkPhone"] : string.Empty; //
                dr["Title"] = usrDetail["Title"] != null ? usrDetail["Title"] : string.Empty; //
                tblUsersDetails.Rows.Add(dr);
            }
            lstViewUsers.DataSource = tblUsersDetails;
            lstViewUsers.DataBind();
        }

private void GetUserProfiledInTbl(UserProfileManager upm)
        {

            DataTable tblUsersDetails = new DataTable();
            tblUsersDetails.Columns.Add("PreferredName", typeof(string));
            tblUsersDetails.Columns.Add("AccountName", typeof(string));
            tblUsersDetails.Columns.Add("Department", typeof(string));
            tblUsersDetails.Columns.Add("WorkPhone", typeof(string));
            tblUsersDetails.Columns.Add("Title", typeof(string));
            foreach (UserProfile usrDetail in upm)
            {
                DataRow dr = tblUsersDetails.NewRow();
                dr["PreferredName"] = usrDetail[PropertyConstants.PreferredName].Value != null ? usrDetail[PropertyConstants.PreferredName].Value.ToString() : string.Empty;
                dr["AccountName"] = usrDetail[PropertyConstants.AccountName].Value != null ? usrDetail[PropertyConstants.AccountName].Value.ToString() : string.Empty; //usrDetail["AccountName"].Value;
                dr["Department"] = usrDetail[PropertyConstants.Department].Value != null ? usrDetail[PropertyConstants.Department].Value.ToString() : string.Empty;
                dr["WorkPhone"] = usrDetail[PropertyConstants.WorkPhone].Value != null ? usrDetail[PropertyConstants.WorkPhone].Value.ToString() : string.Empty;
                dr["Title"] = usrDetail[PropertyConstants.Title].Value != null ? usrDetail[PropertyConstants.Title].Value.ToString() : string.Empty;
                tblUsersDetails.Rows.Add(dr);
            }

        }

ASCX

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"

Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"

Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

Namespace="System.Web.UI.WebControls" TagPrefix="asp" %>

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="PeopleSearchUC.ascx.cs"

Inherits="OrgChart.PeopleSearch.PeopleSearchUserControl" %>

.User_details

{

color: Highlight;

font-size: 14px;

font-family: Arial,Helvetica,sans-serif;

}

.User_details td

{

color: Gray;

font-size: 14px;

margin-top: 2px;

margin-bottom: 2px;

font-family: Arial,Helvetica,sans-serif;

}

.ListHeader td

{

background-color: White;

font-size: 15px;

margin-top: 5px;

margin-bottom: 3px;

font-family: Arial,Helvetica,sans-serif;

}

.ListItem td

{

background-color: White;

font-size: 12px;

margin-top: 5px;

margin-bottom: 3px;

font-family: Arial,Helvetica,sans-serif;

}

.ListAlternate td

{

background-color: #EFEFEF;

font-size: 12px;

margin-top: 5px;

margin-bottom: 3px;

font-family: Arial,Helvetica,sans-serif;

}

.TblBasicSearch

{

border-bottom: 2px solid blue;

}

.TblBasicSearch td

{

margin-top: 5px;

margin-bottom: 5px;

font-family: Arial,Helvetica,sans-serif;

text-indent: 2px;

word-spacing:normal;

}

.SearchHeader

{

height: 20px;

text-align: left;

text-indent:5px;

color: White;

font-family: Arial,Helvetica,sans-serif;

font-size: 16px;

font-weight:bold;

margin-top: 2px;

padding-top: 2px;

padding-bottom: 5px;

background: url("../_layouts/images/ProjectName/panel_bg.jpg") repeat-x scroll 0 0 transparent;

}

table

{

width: 100%;

height: auto;

}

</style>

<h1>

<asp:Label ID="lblLN" runat="server" Text="People Search"></asp:Label>

</h1>

<a href="OCPerson.aspx">Org Chart Person View</a>  |   <a href="MemberHierarchy.aspx">Organizational Chart</a><%--| <a href="">Edit My Information</a>--%>

</h1>

</div>

<tr>

<asp:Label ID="lblSimpleSeach" runat="server" Text="Simple Search"></asp:Label></div>

</td></tr>

</td>

<asp:Label ID="lblSearchText" Font-Bold="true" runat="server" Text="Search Person by any Keyword"></asp:Label> <br>

<asp:TextBox ID="txtSearchBox" runat="server" Width="400px"></asp:TextBox>

</td>

<asp:Button ID="btnSearch" Text="Search" runat="server" Width="120px" OnClick="btnSearch_Click" />

</td>

</tr>

</table>

<%--Advance search section--%>

<tr>

<asp:Label ID="Label1" runat="server" Text="Advanced Search Options"></asp:Label></div>

</td></tr>

<tr>

</td>

<td>

<asp:Label ID="lblLastName" Font-Bold="true" Text="Last Name" runat="server"></asp:Label>

</td>

<asp:TextBox ID="txtLastName" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblFirstName" Font-Bold="true" Text="First Name" runat="server"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtFirstName" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

</td>

</tr>

<tr>

</td>

<td>

<asp:Label ID="lblIntranetId" Font-Bold="true" Text="Intranet Id" runat="server"></asp:Label>

</td>

<asp:TextBox ID="txtIntarnetId" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblLocationName" Font-Bold="true" Text="Location Name" runat="server"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtLocName" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

</td>

</tr>

<tr>

</td>

<td>

<asp:Label ID="lblEMail" Font-Bold="true" Text="Email" runat="server"></asp:Label>

</td>

<asp:TextBox ID="txtEmail" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblPhone" Font-Bold="true" Text="Phone" runat="server"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtPhone" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

</td>

</tr>

<tr>

</td>

<asp:Label ID="lbldepartment" Font-Bold="true" Text="Department" runat="server"></asp:Label>

</td>

<td >

<asp:TextBox ID="txtDepartment" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblTitle" Font-Bold="true" Text="Job Title" runat="server"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtJobTitle" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

</td>

</tr>

<%-- <tr >

</td>

<td >

<asp:Label ID="Label5" Font-Bold="true" Text="Last Name" runat="server"></asp:Label>

</td>

<asp:TextBox ID="TextBox5" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

<asp:Label ID="Label6" Font-Bold="true" Text="First Name" runat="server"></asp:Label>

</td>

<td>

<asp:TextBox ID="TextBox6" runat="server" Width="200px"></asp:TextBox>

</td>

<td>

</td>

</tr>--%>

<tr>

</td>

<asp:Button ID="btnAdvSearch" Text="Advance Search" Width="120px" runat="server"

onclick="btnAdvSearch_Click" />

</td>

<asp:Button ID="btnReset" Text="Reset" runat="server" Width="120px"

onclick="btnReset_Click" />

</td>

<td>

</td>

</tr>

</table>

<%--Advance search section--%>

<table>

<tr>

<td>

<asp:ListView ID="lstViewUsers" runat="server" ItemPlaceholderID="itemPlaceholder">

<%--GroupPlaceholderID="groupPlaceholder1" ItemPlaceholderID="itemPlaceholder"--%>

<asp:Label ID="lblName" runat="server" Text="Name"></asp:Label>

<%--<asp:LinkButton ID="lnkName" runat="server"></asp:LinkButton>--%>

</th>

<asp:Label ID="lblAcctId" runat="server" Text=" Intranet Id"></asp:Label>

<%--<asp:LinkButton ID="lnkID" runat="server"></asp:LinkButton>--%>

</th>

<asp:Label ID="lblTitle" runat="server" Text="Title"></asp:Label>

<%--<asp:LinkButton ID="lnkTitle" runat="server"></asp:LinkButton>--%>

</th>

<asp:Label ID="lblDept" runat="server" Text="Department"></asp:Label>

<%--<asp:LinkButton ID="lnkDept" runat="server"></asp:LinkButton>--%>

</th>

<asp:Label ID="lblPh" runat="server" Text="Phone"></asp:Label>

<%--<asp:LinkButton ID="lnkType" runat="server"></asp:LinkButton>--%>

</th>

<th>

</th>

</tr>

</tr>

</table>

</LayoutTemplate>

<%--<GroupTemplate>

</div>

</GroupTemplate>--%>

<asp:Label ID="lblName" runat="server" Text='<%# Eval("PreferredName") %>'></asp:Label>

</a>

</td>

<td>

<asp:Label ID="lblAcctName" runat="server" Text='<%# Eval("AccountName") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblTitle" runat="server" Text='<%# Eval("Title") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblDepartment" runat="server" Text='<%# Eval("Department") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblPhone" runat="server" Text='<%# Eval("WorkPhone") %>'></asp:Label>

</td>

</tr>

</ItemTemplate>

<asp:Label ID="lblName" runat="server" Text='<%# Eval("PreferredName") %>'></asp:Label>

</a>

</td>

<td>

<asp:Label ID="lblAcctName" runat="server" Text='<%# Eval("AccountName") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblTitle" runat="server" Text='<%# Eval("Title") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblDepartment" runat="server" Text='<%# Eval("Department") %>'></asp:Label>

</td>

<td>

<asp:Label ID="lblPhone" runat="server" Text='<%# Eval("WorkPhone") %>'></asp:Label>

</td>

</tr>

</AlternatingItemTemplate>

</asp:ListView>

</td>

</tr>

</table>

If we want to extent our FullTextSqlQuery to beyond default metadata mapping provided by SharePoint (other than FirstName, LastName, Department, Email) . We can do so by creating a new managed property under search and running a full/incremental crawl.
Below is the tested query syntax with returning results.

string qry = "SELECT Firstname, Lastname,PreferredName, AccountName, Department, workemail,BaseOfficeLocation, WorkPhone,JobTitle ";

qry += " FROM SCOPE() WHERE \"scope\" = '" + "People" + "' ";

if (!string.IsNullOrEmpty(txtFirstName.Text.Trim()))

qry += "AND CONTAINS (Firstname,'" + txtFirstName.Text.Trim() + "*')"; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtLastName.Text.Trim()))

qry += " AND CONTAINS (Lastname,'" + txtLastName.Text.Trim() + "*') "; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtDepartment.Text.Trim()))

qry += "AND CONTAINS (Department,'" + txtDepartment.Text.Trim() + "*')"; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtEmail.Text.Trim()))

qry += "AND CONTAINS (workemail,'" + txtEmail.Text.Trim() + "*') "; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtPhone.Text.Trim()))

qry += "AND CONTAINS (workphone,'" + txtPhone.Text.Trim() + "*')";

if (!string.IsNullOrEmpty(txtIntarnetId.Text.Trim()))

qry += "AND CONTAINS (accountname,'" + txtIntarnetId.Text.Trim() + "*')"; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtLocName.Text.Trim()))

qry += "AND CONTAINS (baseofficelocation,'" + txtLocName.Text.Trim() + "*')";

if (!string.IsNullOrEmpty(txtJobTitle.Text))

qry += " AND CONTAINS (jobtitle,'" + txtJobTitle.Text.Trim() + "*') "; // working based on default metadata mapping

Trying with OR logic instead of AND

string qry = "SELECT Firstname, Lastname,PreferredName, AccountName, Department, workemail,BaseOfficeLocation, WorkPhone,JobTitle ";

qry += " FROM SCOPE() WHERE \"scope\" = '" + "People" + "' ";

qry += "AND (";

if (!string.IsNullOrEmpty(txtFirstName.Text.Trim()))

qry += " CONTAINS (Firstname,'" + txtFirstName.Text.Trim() + "*') OR "; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtLastName.Text.Trim()))

qry += " CONTAINS (Lastname,'" + txtLastName.Text.Trim() + "*') OR"; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtDepartment.Text.Trim()))

qry += " CONTAINS (Department,'" + txtDepartment.Text.Trim() + "*') OR"; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtEmail.Text.Trim()))

qry += " CONTAINS (workemail,'" + txtEmail.Text.Trim() + "*') OR "; // working based on default metadata mapping

if (!string.IsNullOrEmpty(txtJobTitle.Text))

qry += " CONTAINS (jobtitle,'" + txtJobTitle.Text.Trim() + "*') OR "; // working based on default metadata mapping

qry += " CONTAINS (accountname,'" + txtIntarnetId.Text.Trim() + "*')"; // working based on default metadata mapping

qry += " )";

SharePoint

Wednesday, 24 July 2013

Customizing the OOB Note Board

Problem with the Ratings controls on items having special character

Sunday, 21 July 2013

Finding Users detail from Search and UPS

About Me