We had a requirement to do SQL type queries on the data in a SharePoint library. Out of the box, SharePoint supports keyword search, but you can't specify the column in which to search. I created managed properties to allow searching in specific columns, but the advanced search screen doesn't support wildcard searches. In order to allow Boolean searches on specific columns with wildcards, I created a webpart to use the SharePoint's FullTextSQLQuery search capability.
The users wanted to be able to use a query-builder user interface - where they could select the search terms and operators with buttons. They also wanted to be able to enter search criteria for any column in the library.
I created a scope with a rule that only included the documents in a specific document library.
I created managed properties for each of the columns in the document library.
In the metadata property mappings view, Central Administration > Shared Services Administration > Search Administration > Metadata Properties , click on Crawled Properties
Make sure the included In Index column is set to Yes for each crawled property that is mapped to a managed property.
After the managed properties are set up, a full crawl of the index is required before they are searchable.
There are two ways to search. You can use the quick search at the top, or the query builder at the bottom.
The search results are displayed in an SPGridView with paging:
The paging was tricky. I found this little
nugget on MSDN:
"If the GridView control is bound to a data source control that does not support the paging capability directly, or if the GridView control is bound to a data structure in code through the DataSource property, the GridView control will perform paging by getting all of the data records from the source, displaying only the records for the current page, and discarding the rest. This is supported only when the data source for the GridView control returns a collection that implements the ICollection interface (including datasets)."
So every time the page index changes, the query has to be re-executed because the gridview control discards the records that aren't displayed on the current page.
Now for some code. First I CreateChildControls. I am also using RenderContents to add the HTML Buttons and TextArea at the bottom of the webpart. The buttons are executing Javascript so I can add text to the search critera text area without doing a postback. I registered the javascript. You can see more details about
using javascript in a web part on MSDN.
public class ICSSearch : System.Web.UI.WebControls.WebParts.WebPart
{
// strings
public string DestinationPage;
private const string LeftParenScriptKey = "myLeftParenScriptKey";
//add the left paren to the text box - where the cursor is
//get textbox name
/*
* courtesy of http://alexking.org/blog/2003/06/02/inserting-at-the-cursor-using-javascript
* function insertAtCursor(myField, myValue) {
//IE support
if (document.selection) {
myField.focus();
sel = document.selection.createRange();
sel.text = myValue;
}
else if (myField.selectionStart || myField.selectionStart == '0') {
var startPos = myField.selectionStart;
var endPos = myField.selectionEnd;
myField.value = myField.value.substring(0, startPos)
+ myValue
+ myField.value.substring(endPos, myField.value.length);
} else {
myField.value += myValue;
}
*/
private string EmbeddedScriptFormat = " ";
//labels
Label lblID;
// CompareValidator checkID;
Label lblName;
Label lblProduct;
Label lblModel;
Label lblResults;
// Text boxes
TextBox cmpyName;
TextBox EquipID;
TextBox ProductID;
TextBox ModelID;
TextBox queryText;
// Buttons
Button cmdSearch;
Table table;
SPGridView gridView;
DataTable resultsDataTable;
DataTable dt;
int gridViewPageIndex =0;
// store the dataset between pages
DataTable dtPaging;
string strQuery;
public ICSSearch()
{
this.PreRender += new EventHandler(ICSSearch_PreRender);
}
//Client Script registration event
private void ICSSearch_PreRender(object sender, System.EventArgs e)
{
RegisterCommonScript();
}
// this function will register the embedded script
private void RegisterCommonScript()
{
// string location = null;
// Make sure that the script was not already added to the
//page.
EmbeddedScriptFormat = EmbeddedScriptFormat.Replace("[queryTextClientId]", queryText.ClientID);
ClientScriptManager csm = Page.ClientScript;
if (!csm.IsClientScriptBlockRegistered(LeftParenScriptKey))
csm.RegisterClientScriptBlock(GetType(), LeftParenScriptKey,
EmbeddedScriptFormat);
}
Here is the CreateChildControls
protected override void CreateChildControls()
{
//Create a table control to use for positioning the controls
table = new Table();
table.Width = Unit.Percentage(100);
for(int i =0; i<10; i++) { TableRow row= new TableRow(); TableCell cell = new TableCell(); row.Cells.Add(cell); table.Rows.Add(row); } //Create the controls for EquipmentID input lblID = new Label(); lblID.Text = "Equipment ID is:"; lblID.Width = Unit.Pixel(150); EquipID = new TextBox(); EquipID.ID = "EquipID"; //Add the controls for the EquipmentID to the table table.Rows[0].Cells[0].Controls.Add(lblID); table.Rows[0].Cells[0].Controls.Add(EquipID); table.Rows[0].Cells[0].Height = Unit.Pixel(30); //Create the controls for Company Name input. lblName = new Label(); lblName.Text = "Company Name contains:"; cmpyName = new TextBox(); //Add the controls for the Company Name to the table table.Rows[1].Cells[0].Controls.Add(lblName); table.Rows[1].Cells[0].Controls.Add(cmpyName); table.Rows[1].Cells[0].Height = Unit.Pixel(30); // add controls for Model lblModel = new Label(); lblModel.Text = "Model is: "; ModelID = new TextBox(); ModelID.ID = "Model"; // add the controls for Model to the table table.Rows[2].Cells[0].Controls.Add(lblModel); table.Rows[2].Cells[0].Controls.Add(ModelID); table.Rows[2].Cells[0].Height = Unit.Pixel(30); // add controls for Product lblProduct = new Label(); lblProduct.Text = "Product is: "; ProductID = new TextBox(); ProductID.ID = "Product"; // add the controls for Product to the table table.Rows[3].Cells[0].Controls.Add(lblProduct); table.Rows[3].Cells[0].Controls.Add(ProductID); table.Rows[3].Cells[0].Height = Unit.Pixel(30); //Create the search button and add to the table control cmdSearch = new Button(); cmdSearch.ID = "cmdSearch"; cmdSearch.Click += new EventHandler(cmdSearch_Click); cmdSearch.Text = "Search Incidents"; table.Rows[4].Cells[0].Controls.Add(cmdSearch); table.Rows[4].Cells[0].Height = Unit.Pixel(30); //Create a label to display the search message lblResults = new Label(); table.Rows[5].Cells[0].Controls.Add(lblResults); table.Rows[5].Cells[0].Height = Unit.Pixel(30); gridView = new SPGridView(); gridView.AutoGenerateColumns = false; gridView.ID = "GRIDID"; gridView.AllowPaging = true; gridView.AllowSorting = true; gridView.PagerTemplate = null; gridView.PageIndexChanging += new GridViewPageEventHandler(gridView_PageIndexChanging); // table.Rows[6].Cells[0].Controls.Add(gridView); Label queryTextLbl = new Label(); queryTextLbl.Text = "Search Criteria:"; queryText = new TextBox(); queryText.ID = "queryText"; queryText.Columns = 80; queryText.Rows = 3; // table.Rows[6].Cells[0].Controls.Add(queryTextLbl); // table.Rows[7].Cells[0].Controls.Add(queryText); BoundField colTitle = new BoundField(); colTitle.DataField = "CompanyName"; colTitle.HeaderText = "Company"; gridView.Columns.Add(colTitle); BoundField colEquip = new BoundField(); colEquip.DataField = "EquipmentID"; colEquip.HeaderText = "Equipment ID"; gridView.Columns.Add(colEquip); BoundField colModel = new BoundField(); colModel.DataField = "ModelNum"; colModel.HeaderText = "Model"; gridView.Columns.Add(colModel); BoundField colProduct = new BoundField(); colProduct.DataField = "Product"; colProduct.HeaderText = "Product"; gridView.Columns.Add(colProduct); HyperLinkField colLink = new HyperLinkField(); colLink.HeaderText = "Path"; string[] pathFields = { "Path" }; colLink.DataNavigateUrlFields = pathFields ; colLink.DataTextField = "LinkText"; gridView.Columns.Add(colLink); //Add the table to the controls collection this.Controls.Add(table); this.Controls.Add(gridView); this.Controls.Add(queryTextLbl); this.Controls.Add(queryText); gridView.PagerTemplate = null; base.CreateChildControls(); }
Here is the RenderContents:
Here are the actions for the Search button and the paging buttons:
Search button:
void cmdSearch_Click(object sender, EventArgs e)
{
string strName = cmpyName.Text;
string strID = EquipID.Text;
string strProductID = ProductID.Text ;
string strModelID = ModelID.Text;
string strCriteria = queryText.Text;
/*
Validate that the user entered something.
If not, prompt the user to enter an ID or search term.
*/
if (strName == "" && strID == "" && strProductID == "" && strModelID =="" && strCriteria=="")
{
lblResults.Text = "You must enter a criteria for the Search.";
}
else
{
if (strCriteria == "")
{
// if no criteria string was entered, use this :
returnResults(buildSQL(strName, strID, strModelID, strProductID));
}
else
{
returnResults(parseCriteria(strCriteria));
}
}
}
Paging button:
void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string strName = cmpyName.Text;
string strID = EquipID.Text;
string strProductID = ProductID.Text;
string strModelID = ModelID.Text;
string strCriteria = queryText.Text;
/*
Validate that the user entered something.
If not, prompt the user to enter an ID or search term.
*/
if (strName == "" & strID == "" & strProductID == "" & strModelID == "")
{
lblResults.Text = "You must enter a criteria for the Search.";
}
else
{
if (strCriteria == "")
{
// if no criteria string was entered, use this :
returnResults(buildSQL(strName, strID, strModelID, strProductID));
}
else
{
returnResults(parseCriteria(strCriteria));
}
}
gridViewPageIndex = e.NewPageIndex;
gridView.DataSource = dt;
gridView.PagerTemplate = null;
gridView.DataBind();
gridView.PageIndex = e.NewPageIndex;
}
Here is the code that builds the SQL:
I noticed that the wildcard characters didn't work with the CONTAINS predicate unless the term was enclosed in double quotes.
CONTAINS(CompanyName, 'First*') did not work.
CONTAINS(CompanyName, '"First*"') did work.
private string buildSQL(string stName, string stID, string stModel, string stProduct)
{
//TODO add all fields on form
//TODO Check to see why CONTAINS isn't working for Equipment
string BDCscopeID = "Company Client Support";
//Use the StringBuilder class for the syntax string
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT CompanyName,EquipmentID,ModelNum,Product,Path FROM Scope() WHERE (\"Scope\"='");
sbSQL.Append(BDCscopeID);
sbSQL.Append("')");
if (stName != "")
{
//wildcard search doesn't work unless the search term is enclosed in double quotes!
sbSQL.Append(" AND CONTAINS(CompanyName,'\"");
sbSQL.Append(stName);
sbSQL.Append("\"')");
}
if (stID != "")
{
sbSQL.Append(" AND CONTAINS(EquipmentID,'\"");
sbSQL.Append(stID);
sbSQL.Append("\"')");
}
if (stModel != "")
{
sbSQL.Append(" AND CONTAINS(ModelNum,'\"");
sbSQL.Append(stModel);
sbSQL.Append("\"')");
}
if (stProduct != "")
{
sbSQL.Append(" AND CONTAINS(Product,'\"");
sbSQL.Append(stProduct);
sbSQL.Append("\"')");
}
return sbSQL.ToString();
}
Here is the code that returns the results and populates the gridview. The library contains InfoPath forms, so I changed the URL in the search results so that it would open the form in the browser. I would like to put the search results at the bottom of the web part below the criteria builder, but the RenderContents puts everything in the bottom. I'm still working on that.
private void returnResults(string strSQL)
{
FullTextSqlQuery sqlQuery = new FullTextSqlQuery(SPContext.Current.Site);
try
{
strQuery = strSQL;
//Specify result type to return
sqlQuery.ResultTypes = ResultType.RelevantResults;
//Specify the full text search query string
sqlQuery.QueryText = strSQL;
sqlQuery.TrimDuplicates = true;
//Return the search results to a ResultTableCollection
ResultTableCollection results = sqlQuery.Execute();
//Create a ResultTable for the relevant results table
ResultTable relResults = results[ResultType.RelevantResults];
int x = relResults.RowCount;
if (x != 0)
{
resultsDataTable = new DataTable();
resultsDataTable.TableName = "Result";
resultsDataTable.Load(relResults, LoadOption.OverwriteChanges);
// walk through results table and look at the rows
string[] cName = new String[1];
string equip;
string path;
string[] product = new String[1];
string[] modelNum = new String[1];
dt = new DataTable();
// read results into a datatable
dt.Columns.Add("CompanyName");
dt.Columns.Add("EquipmentID");
dt.Columns.Add("ModelNum");
dt.Columns.Add("Product");
dt.Columns.Add("Path");
dt.Columns.Add("LinkText");
foreach (DataRow row in resultsDataTable.Rows)
{
if (row["CompanyName"] != null && row["CompanyName"] != DBNull.Value )
{
// this column returns a string array
cName = (string[])row["CompanyName"];
}
else
{
cName[0] = String.Empty;
}
if (row["EquipmentID"] == null)
{
equip = String.Empty;
}
else
{
equip = row["EquipmentID"].ToString();
}
if (row["Path"] == null)
{
path = String.Empty;
}
else
{
path = row["Path"].ToString();
}
// this column returns a string array
if (row["ModelNum"] != null && row["ModelNum"] != DBNull.Value)
{
modelNum = (string[])row["ModelNum"];
}
else
{
modelNum[0] = String.Empty;
}
// this column returns a string array
if (row["Product"] != null && row["Product"] != DBNull.Value)
{
product = (string[])row["Product"];
}
else
{
product[0] = String.Empty;
}
string currentUrl = SPContext.Current.Site.Url;
dt.Rows.Add(cName[0], equip, modelNum[0], product[0], currentUrl + "/_layouts/FormServer.aspx?XmlLocation=" + path + "&DefaultItemOpen=1", path);
}
gridView.DataSource = dt.DefaultView;
dtPaging = dt;
//Count the number of rows in the table; 0 = no search results
lblResults.Text = x.ToString() + " item(s) found";
}
}
catch (Exception ex1)
{
lblResults.Text = ex1.ToString();
}
finally
{
// sqlQuery.Dispose();
}
}
