I was working on a SharePoint 2007 project a few days ago and I came across some problems that I resolved by doing research on Net.
This blog would help people implement same kind of solution on SharePoint 2007.
Objective: To create a Customized search web part for SharePoint site which provides the facility to users to search through different parameters and wildcard search. The result will be displayed in SPGrid and can be exported to MS Excel.
Solution:
1) Create a ShrePoint project in VS.NET 2005 as a WebPart template.2) Declare controls on a class level. 3) Create controls in CreateChildControl() method.4) Render webpart in RenderWebpart method 5) Write event handlers for Buttons.I am including the code of Search button which is important to mention here, rest everything u do is normal in any webpart.
This method shows how to write select query along with parameter search and it includes wildcard search as well.
For Example
a) Search for keyword
Patent , will search for title column containing word Patent
b) Search for
AB6789 in DocumentNumber field will search for exact document number.
c) Wildcard Search for
AB* in DocumentNumber field search for any documents that have
AB in its DocumentNumber column.
public void btnSearch_Click(object sender, EventArgs e)
{
using (SPSite mySite = new SPSite("http://myspssite/"))
{
//site = mySite;
FullTextSqlQuery query = new FullTextSqlQuery(mySite);
query.ResultTypes = ResultType.RelevantResults;
query.QueryText = this.GenerateSelect();
try
{
ResultTableCollection rtc = query.Execute();
rt = rtc[ResultType.RelevantResults];
if (rt.RowCount > 0)
{
SearchText = true;
}
}
catch (Exception ex)
{
rt = null;
}
}
}
private string GenerateSelect()
{
string strSQL = "";
string strQuery = "";
// NOTE: these columns in select query are the managed properties in Search Settings
//(Central Admin)
strQuery = "select title, created, DocumentNumber, SCI from scope()";
strQuery += " Where isDocument = 1";
if (txtKeyword.Text.Trim() != "")
{
strQuery += " and FREETEXT(title, '" + txtKeyword.Text.Trim() + "')";
}
if (txtDocNo.Text.Trim() != "")
{
if (txtDocNo.Text.Trim().IndexOf("*") != -1)
{
strQuery += " and CONTAINS(DocumentNumber, '\"" + txtDocNo.Text.Trim() + "\"')";
}
else
{
strQuery += " and DocumentNumber = '" + txtDocNo.Text.Trim() + "'";
}
}
if (txtSCI.Text.Trim() != "")
{
if (txtSCI.Text.Trim().IndexOf("*") != -1)
{
strQuery += " and CONTAINS(CSI, '\"" + txtSCI.Text.Trim() + "\"')";
}
else
{
strQuery += " and CSI = '" + txtSCI.Text.Trim() + "'";
}
}
}
Call this BindGrid() method in RenderWebPart() method.
private void BindGrid()
{
DataTable dtSearch = new DataTable();
if (rt != null)
dtSearch.Load(rt, LoadOption.OverwriteChanges);
BoundField field = new BoundField();
field.HeaderText = "Title";
field.DataField = "title";
field.ItemStyle.Font.Size = new FontUnit(10);
spGrid.Columns.Add(field);
field = new BoundField();
field.HeaderText = "Document Date";
field.DataField = "CREATED";
field.ItemStyle.Font.Size = new FontUnit(10);
spGrid.Columns.Add(field);
HyperLinkField link = new HyperLinkField();
string[] dataNavigateUrlFields = { "PATH" };
link.DataTextField = "DOCUMENTNUMBER";
link.DataNavigateUrlFields = dataNavigateUrlFields;
link.HeaderText = "Document Number";
link.Target = "_blank";
link.ItemStyle.Font.Size = new FontUnit(7);
spGrid.Columns.Add(link);
field = new BoundField();
field.HeaderText = "Document Type";
field.DataField = "DOCUMENTTYPE";
field.ItemStyle.Font.Size = new FontUnit(10);
spGrid.Columns.Add(field);
spGrid.DataSource = dtSearch;
spGrid.PagerTemplate = null;
spGrid.DataBind();
}
public void btnExport_Click(object sender, EventArgs e)
{
ExportGridViewToExcel();
}
private void ExportGridViewToExcel()
{
// SELECT_QUERY is private variable that holds the select query to be passed to // to ExportExcel.aspx page as a query string.
this.GenerateSelect();
string str = "
";
HttpContext.Current.Response.Write(str);
}
Exporting Grid To Excela) If you try to export results in a grid to Excel from the same webpage, it wont be achieved. So Its better to make a new .aspx page anywhere in your site through SharePoint designer. Name that page ExportExcel.aspx
No need to write any html code in that .aspx page.
b) Add new .aspx page in your visual studio project as well with the same name ExportExcel.aspx
c) add following code in ExportExcel.aspx.cs page
protected void Page_Load(object sender, EventArgs e)
{
ResultTable rt;
string strSQL = (string)Request.QueryString["SQL"];
using (SPSite mySite = new SPSite("http://mysite/"))
{
web = mySite.OpenWeb();
FullTextSqlQuery query = new FullTextSqlQuery(mySite);
query.ResultTypes = ResultType.RelevantResults;
query.QueryText = strSQL;
try
{
ResultTableCollection rtc = query.Execute();
rt = rtc[ResultType.RelevantResults];
this.Export(rt);
}
catch (Exception ex)
{
rt = null;
}
}
}
public void Export(ResultTable rt)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=FileName1.xls");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
//grid code
DataTable table = new DataTable();
table.Load(rt, LoadOption.OverwriteChanges);
GridView gridNew = new GridView();
gridNew.AutoGenerateColumns = false;
BoundField field = new BoundField();
field.HeaderText = "Title";
field.DataField = "title";
gridNew.Columns.Add(field);
field = new BoundField();
field.HeaderText = "Created Date";
field.DataField = "CREATED";
gridNew.Columns.Add(field);
field = new BoundField();
field.HeaderText = "Document Number";
field.DataField = "DOCUMENTNUMBER";
gridNew.Columns.Add(field);
field = new BoundField();
field.HeaderText = "Document Type";
field.DataField = "DOCUMENTTYPE";
gridNew.Columns.Add(field);
HyperLinkField link = new HyperLinkField();
string[] dataNavigateUrlFields = { "PATH" };
link.DataTextField = "PATH";
link.DataNavigateUrlFields = dataNavigateUrlFields;
link.HeaderText = "Document Path";
link.Target = "_blank";
gridNew.Columns.Add(link);
gridNew.DataSource = table;
gridNew.DataBind();
gridNew.RenderControl(htmlWrite);
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();
HttpContext.Current.Response.ClearContent();
gridNew.Dispose();
table.Dispose();
}
I hope this code has been comprehensible enough to implement Enterprise Search. :-)
If you have any queries, just drop me an email.
