Employee Directory for Sharepoint Column
This web part exposes props for an advance search URL, results table width and max record count. The results are displayed via bind to an ASP.Net Repeater.
properties exposed in Sharepoint
Credit Due for dynamic template code
BWEmpDirSmall.cs
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Text;
//add
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Common;
using System.Web.Configuration;
using System.Data.SqlTypes;
using System.Xml.Serialization;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint;
using System.IO;
namespace BWEmpDirSmall
{
[Guid("60c0ce25-603a-4c24-8d99-8b6046f013fe")]
public class BWEmployeeSearch2 : Microsoft.SharePoint.WebPartPages.WebPart
{
private bool _error = false;
private Label lblMessage;
private TextBox txtSearch;
private Button btnSearch;
private Button btnClear;
private Repeater rptList;
private HyperLink hlAdvSearch;
[Personalizable(PersonalizationScope.Shared)]
[WebBrowsable(true)]
[System.ComponentModel.Category("My Property Group")]
[WebDisplayName("Advance Search Page")]
[WebDescription("URL to Advance Search")]
public string AdvSearchURL { get; set; }
[Personalizable(PersonalizationScope.Shared)]
[WebBrowsable(true)]
[System.ComponentModel.Category("My Property Group")]
[WebDisplayName("Table Width")]
[WebDescription("Filter & Results Table Width")]
public string TableWidth { get; set; }
[Personalizable(PersonalizationScope.Shared)]
[WebBrowsable(true)]
[System.ComponentModel.Category("My Property Group")]
[WebDisplayName("Max Count")]
[WebDescription("Maximum Rows Returned")]
public string MaxCount { get; set; }
public BWEmployeeSearch2()
{
this.ExportMode = WebPartExportMode.All;
this.AdvSearchURL = @"<a href='http://portal'>******</a>";
this.TableWidth = "220px";
this.MaxCount = "25";
}
/// <summary>
/// Sets Display
/// </summary>
/// <param name="writer"></param>
protected override void RenderContents(HtmlTextWriter writer)
{
writer.Write("<table width='" + TableWidth + "' border='0' >");
spanRow(writer, "left", "2");
//writer.Write("<b>Search: </b>");
txtSearch.RenderControl(writer);
writer.Write(" ");
btnSearch.RenderControl(writer);
writer.Write(" ");
btnClear.RenderControl(writer);
rowEnd(writer);
spanRow(writer, "left", "2");
hlAdvSearch.RenderControl(writer);
rowEnd(writer);
spanRow(writer, "left", "2");
lblMessage.RenderControl(writer);
rowEnd(writer);
spanRow(writer, "left", "2");
rptList.RenderControl(writer);
rowEnd(writer);
writer.Write("</table>");
}
protected void rowStart(HtmlTextWriter writer, string sAlign) { writer.Write("<tr><td align=" + sAlign + ">"); }
protected void cellEnd(HtmlTextWriter writer) { writer.Write("</td>"); }
protected void cellEndStart(HtmlTextWriter writer, string sAlign) { writer.Write("</td><td align=" + sAlign + ">"); }
protected void rowEnd(HtmlTextWriter writer) { writer.Write("</td></tr>"); }
protected void spanRow(HtmlTextWriter writer, string sAlign, string colspan)
{
writer.Write("<tr><td align=" + sAlign + " colspan=" + colspan + " >");
}
/// <summary>
/// Create all your controls here for rendering.
/// Try to avoid using the RenderWebPart() method.
/// </summary>
protected override void CreateChildControls()
{
if (!_error)
{
try
{
base.CreateChildControls();
lblMessage = new Label();
btnSearch = new Button();
btnClear = new Button();
txtSearch = new TextBox();
hlAdvSearch = new HyperLink();
this.lblMessage.Width = new Unit("200px");
this.btnSearch.Text = "Search";
this.btnSearch.Click += new EventHandler(btnSubmit_Click);
this.btnClear.Text = "Clear";
this.btnClear.Click += new EventHandler(btnClear_Click);
this.rptList = new Repeater();
this.rptList.ItemTemplate = new MyTemplate(MaxCount);
this.hlAdvSearch.Text = "Advance Search";
this.hlAdvSearch.NavigateUrl = AdvSearchURL;
this.Controls.Add(lblMessage);
this.Controls.Add(txtSearch);
this.Controls.Add(rptList);
this.Controls.Add(btnSearch);
this.Controls.Add(btnClear);
this.Controls.Add(hlAdvSearch);
this.lblMessage.Width = new Unit("180px");
this.btnSearch.Width = new Unit("60px");
this.btnClear.Width = new Unit("60px");
this.txtSearch.Width = new Unit("150px");
}
catch (Exception ex)
{
HandleException(ex);
}
}
}
/// <summary>
/// Ensures that the CreateChildControls() is called before events.
/// Use CreateChildControls() to create your controls.
/// </summary>
/// <param name="e"></param>
protected override void OnLoad(EventArgs e)
{
if (!_error)
{
try
{
base.OnLoad(e);
this.EnsureChildControls();
// Your code here...
}
catch (Exception ex)
{
HandleException(ex);
}
}
}
/// <summary>
/// Clear all child controls and add an error message for display.
/// </summary>
/// <param name="ex"></param>
private void HandleException(Exception ex)
{
this._error = true;
this.Controls.Clear();
this.Controls.Add(new LiteralControl(ex.Message));
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
this.rptList.ItemTemplate = new MyTemplate(MaxCount);
if (Utilities.hasData(txtSearch))
bindRepeater();
else
{
lblMessage.Text = "No Search Criteria Entered";
//this.rptList.DataSource = new string[] { }; //clear
//this.rptList.DataBind();
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtSearch.Text = string.Empty;
this.rptList.DataSource = new string[] { }; //clear
this.rptList.DataBind();
lblMessage.Text = "";
}
private void bindRepeater()
{
DataSet ds = Employee.searchEmployees(txtSearch.Text.Trim());
this.rptList.DataSource = ds;
this.rptList.DataBind();
int iRecCount = 0;
Int32.TryParse(ds.Tables[0].Rows.Count.ToString(),out iRecCount);
if (iRecCount < (Int32.Parse(MaxCount)))
this.lblMessage.Text = iRecCount.ToString() + " record(s) found";
else
this.lblMessage.Text = iRecCount.ToString() + " Recs Exceeded " + MaxCount + " Max";
}
}
}
MyTemplate.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace BWEmpDirSmall
{
public class MyTemplate : ITemplate
{
static int itemcount = 0;
static int MAXCOUNT = 25;
ListItemType templateType;
public MyTemplate()
{
templateType = ListItemType.Item;
itemcount = 0;
}
public MyTemplate(string s)
{
templateType = ListItemType.Item;
MAXCOUNT = 25;
Int32.TryParse(s, out MAXCOUNT);
itemcount = 0;
}
public MyTemplate(ListItemType type)
{
templateType = type;
itemcount = 0;
}
public void InstantiateIn(System.Web.UI.Control container)
{
Literal lc = new Literal();
//switch (templateType)
//{
// case ListItemType.Header:
// lc.Text = "<TABLE border=1><TR><TH>Items</TH></TR>";
// break;
// case ListItemType.Item:
// //lc.Text = "<TR><TD>Item number: " + itemcount.ToString() +
// // "</TD></TR>";
// lc.Text = DataBinder.Eval(Container.DataItem, "FirstName");
// break;
// case ListItemType.AlternatingItem:
// lc.Text = "<TR><TD bgcolor=lightblue>Item number: " +
// itemcount.ToString() + "</TD></TR>";
// break;
// case ListItemType.Footer:
// lc.Text = "</TABLE>";
// break;
//}
if (itemcount <= MAXCOUNT)
{
lc.DataBinding += new EventHandler(TemplateControl_DataBinding);
container.Controls.Add(lc);
}
itemcount++;
}
private void TemplateControl_DataBinding(object sender, System.EventArgs e)
{
Literal lc;
lc = (Literal)sender;
RepeaterItem container = (RepeaterItem)lc.NamingContainer;
lc.Text += "&raquo; <font color='#99000'><b> ";
lc.Text += DataBinder.Eval(container.DataItem, "FirstName");
lc.Text += " " + DataBinder.Eval(container.DataItem, "LastName");
lc.Text += "</b></font> ";
if (Utilities.hasData(DataBinder.Eval(container.DataItem, "WorkPhoneExt").ToString()))
lc.Text += "<b> x/" + DataBinder.Eval(container.DataItem, "WorkPhoneExt") + "</b>";
lc.Text += "<br />";
if (Utilities.hasData(DataBinder.Eval(container.DataItem, "WorkPhone").ToString()))
lc.Text += " <i>" + DataBinder.Eval(container.DataItem, "WorkPhone") + "</i> ";
if (Utilities.hasData(DataBinder.Eval(container.DataItem, "MobilePhone").ToString()))
lc.Text += "<i> Cell: " + DataBinder.Eval(container.DataItem, "MobilePhone") + "</i> ";
lc.Text += "<br /> ";
}
}
}
Employee cs searchEmployees method:
static public DataSet searchEmployees(string sTarget)
{
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet ds = new DataSet("Employees");
using (SqlConnection conn = Utilities.getConnR())
{
try
{
cmd = new SqlCommand("searchEmployees", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Target", SqlDbType.Text));
cmd.Parameters["@Target"].Value = sTarget.Trim();
conn.Open();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch (Exception err)
{
throw new ApplicationException(err.Message.ToString());
//err.Message.ToString();
}
finally
{
if (cmd != null) { cmd.Dispose(); }
if (da != null) { da.Dispose(); }
}
}
return ds;
}
Stored Procedure:
Procedure [dbo].[searchEmployees]
(
@Target nvarchar(50)
)
as
Select
EM.Employee as EmpNum
, RTRIM(EM.Employee) + '-' + SUBSTRING(EM.FirstName,1,1)
+ RTRIM(Case When(DATALENGTH(EM.MiddleName) = 0) Then '' Else SUBSTRING(EM.MiddleName,1,1) End )
+ SUBSTRING(EM.LastName,1,1) as Employee
, isNull(EM.PreferredName,EM.FirstName) as FirstName
, (RTRIM(EM.LastName) + ' ' + isNull(Suffix,'')) as LastName
, EM.MiddleName
, EM.Org as PrimaryDept
, EM.Region
, EM.WorkPhone
, EM.WorkPhoneExt
, EM.MobilePhone
, ECTF.Cust5ULPhysical as Office
, ECTF.custSecondaryDepartment as SecondaryDept
From
EM
Join EmployeeCustomTabFields ECTF on ( ECTF.Employee = EM.Employee )
Where
EM.Status = 'A'
And ( EM.FirstName like Case When (@Target is null) Then '%' Else RTRIM(@Target) + '%' End
OR EM.PreferredName like Case When (@Target is null) Then '%' Else RTRIM(@Target) + '%' End
OR EM.LastName like Case When (@Target is null) Then '%' Else RTRIM(@Target) + '%' End
OR SUBSTRING(EM.FirstName,1,1) + RTRIM(Case When(DATALENGTH(EM.MiddleName) = 0) Then '' Else SUBSTRING(EM.MiddleName,1,1) End ) + SUBSTRING(EM.LastName,1,1) like Case When (@Target is null) Then '%' Else @Target End
OR EM.WorkPhone like '%' + isNull(@Target,'%') + '%'
OR EM.WorkPhoneExt like '%' + isNull(@Target,'%') + '%'
OR EM.MobilePhone like '%' + isNull(@Target,'%') + '%'
OR ECTF.Cust5ULPhysical = isNull(@Target, ECTF.Cust5ULPhysical)
OR EM.Employee like isNull(@Target,'%') + '%' )
