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
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.
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"
%>
<style type="text/css">
.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>
<div class="team_details_heading">
<div class="breadcrumbs">
<h1>
<asp:Label ID="lblLN" runat="server" Text="People Search"></asp:Label>
</h1>
<h1 style="padding-right:5px; text-align:right;padding-left:200px">
<a href="OCPerson.aspx">Org Chart Person View</a> | <a href="MemberHierarchy.aspx">Organizational
Chart</a><%--| <a
href="">Edit My Information</a>--%>
</h1>
</div>
</div>
<table width="100%" cellpadding="10" cellspacing="10">
<tr>
<td colspan="3">
<div class="SearchHeader">
<asp:Label ID="lblSimpleSeach" runat="server" Text="Simple Search"></asp:Label></div>
</td></tr>
<tr class="TblBasicSearch">
<td style="font-weight: bold">
</td>
<td height="30px" >
<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>
<td height="30px" valign="bottom">
<asp:Button ID="btnSearch" Text="Search" runat="server" Width="120px"
OnClick="btnSearch_Click"
/>
</td>
</tr>
</table>
<%--Advance search
section--%>
<table cellpadding="10" cellspacing="10">
<tr>
<td colspan="6">
<div class="SearchHeader">
<asp:Label ID="Label1" runat="server" Text="Advanced Search Options"></asp:Label></div>
</td></tr>
<tr>
<td width="3px">
</td>
<td>
<asp:Label ID="lblLastName" Font-Bold="true" Text="Last Name" runat="server"></asp:Label>
</td>
<td style="font-weight: bold">
<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 width="3px">
</td>
<td>
<asp:Label ID="lblIntranetId" Font-Bold="true" Text="Intranet Id" runat="server"></asp:Label>
</td>
<td style="font-weight: bold">
<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 width="3px">
</td>
<td>
<asp:Label ID="lblEMail" Font-Bold="true" Text="Email" runat="server"></asp:Label>
</td>
<td style="font-weight: bold">
<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 width="3px">
</td>
<td style="font-weight: bold">
<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
width="3px">
</td>
<td >
<asp:Label
ID="Label5" Font-Bold="true" Text="Last Name"
runat="server"></asp:Label>
</td>
<td
style="font-weight:bold">
<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 width="3px">
</td>
<td style="text-align: right" colspan="2">
<asp:Button ID="btnAdvSearch" Text="Advance Search" Width="120px" runat="server"
onclick="btnAdvSearch_Click" />
</td>
<td style="text-align: left" colspan="2">
<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"--%>
<LayoutTemplate>
<table border="0" cellpadding="5" width="100%">
<tr class="ListHeader">
<th align="left">
<asp:Label ID="lblName"
runat="server"
Text="Name"></asp:Label>
<%--<asp:LinkButton ID="lnkName"
runat="server"></asp:LinkButton>--%>
</th>
<th align="left">
<asp:Label ID="lblAcctId"
runat="server"
Text=" Intranet
Id"></asp:Label>
<%--<asp:LinkButton ID="lnkID"
runat="server"></asp:LinkButton>--%>
</th>
<th align="left">
<asp:Label ID="lblTitle"
runat="server"
Text="Title"></asp:Label>
<%--<asp:LinkButton ID="lnkTitle"
runat="server"></asp:LinkButton>--%>
</th>
<th align="left">
<asp:Label ID="lblDept"
runat="server"
Text="Department"></asp:Label>
<%--<asp:LinkButton ID="lnkDept"
runat="server"></asp:LinkButton>--%>
</th>
<th align="left">
<asp:Label ID="lblPh" runat="server"
Text="Phone"></asp:Label>
<%--<asp:LinkButton ID="lnkType"
runat="server"></asp:LinkButton>--%>
</th>
<th>
</th>
</tr>
<tr id="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
<%--<GroupTemplate>
<div
id="itemPlaceholder1" runat="server">
</div>
</GroupTemplate>--%>
<ItemTemplate>
<tr class="ListItem">
<td class="User_details">
<a href='<%# "OCPerson.aspx?UserId=" +
Eval("AccountName").ToString()%>'>
<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>
<AlternatingItemTemplate>
<tr class="ListAlternate">
<td class="User_details">
<a href='<%# "OCPerson.aspx?UserId=" +
Eval("AccountName").ToString()%>'>
<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
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 += " )";
No comments:
Post a Comment