#21 Employee Directory Sharepoint Webpart Pt 2 – Class Files

There are Data Access Layer classes for each collection Employee, Office & Department which can use ADO or the Enterprise library to call stored procedures to access data.

EmpDir.cs

Credit Due: for GridView Paging code - Powlo’s site (Paul Robinson’s SP Site)

using System;
using System.Collections.Generic;
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;
[assembly: System.Security.AllowPartiallyTrustedCallers]

namespace EmpDir

{

public class EmpDir: WebPart

{

private Label       lblMessage;
private TextBox     txtSearchFN;
private TextBox     txtSearchLN;
private TextBox     txtSearchPhone;
private TextBox     txtSearchEmployee;
private TextBox     txtSearchInitials;
private Button      btnSearch;
private Button      btnClear;
private SPGridView  oGrid;
private DataSet     oDataset;
private DataView    oView;
private DropDownList ddDepartment;
private DropDownList ddOffice;

protected override void Render(HtmlTextWriter writer)
{
base.Render(writer);
}

protected override void RenderContents(HtmlTextWriter writer)
{

writer.Write("<table border='0'  width='600px'>");
writer.Write("<tr><td align='right' nowrap='nowrap'>Intials:</td>");
writer.Write("<td align='left' >");
txtSearchInitials.RenderControl(writer);
writer.Write("</td><td align='right'  nowrap='nowrap'>Emp#:</td>");
writer.Write("<td align='left' >");
txtSearchEmployee.RenderControl(writer);
writer.Write("</td><td></td></tr>");
writer.Write("<tr><td align='right' nowrap='nowrap'>First Name:</td>");
writer.Write("<td align='left' >");
txtSearchFN.RenderControl(writer);
writer.Write("</td><td align='right'  nowrap='nowrap'>Last Name:</td>");
writer.Write("<td align='left' >");
txtSearchLN.RenderControl(writer);
writer.Write("</td><td></td></tr>");

writer.Write("<tr><td align='right' nowrap='nowrap'>Phone:</td>");
writer.Write("<td align='left' >");
txtSearchPhone.RenderControl(writer);
writer.Write("</td><td align='right'  nowrap='nowrap'>Department:</td>");
writer.Write("<td align='left' >");
ddDepartment.RenderControl(writer);
writer.Write("</td><td></td></tr>");

writer.Write("<tr><td align='right' nowrap='nowrap'>Office:</td>");
writer.Write("<td align='left' >");
ddOffice.RenderControl(writer);
writer.Write("</td><td align='right'  nowrap='nowrap'></td>");
writer.Write("<td align='left' >");
//ddDepartment.RenderControl(writer);
writer.Write("</td><td></td></tr>");

// BUTTONS
writer.Write("<tr><td colspan='3'>");
btnSearch.RenderControl(writer);
writer.Write("&amp;nbsp;&amp;nbsp;");
btnClear.RenderControl(writer);
writer.Write("</table>");

// GRID
writer.Write("<table border='0'>");
writer.Write("</td></tr>");
writer.Write("<tr><td colspan='3'>");
oGrid.RenderControl(writer);
writer.Write("</td><td></td></tr>");
writer.Write("</table>");

}

protected override void CreateChildControls()
{
base.CreateChildControls();

lblMessage          = new Label();
btnSearch           = new Button();
btnClear            = new Button();
txtSearchFN         = new TextBox();
txtSearchLN         = new TextBox();
txtSearchPhone      = new TextBox();
txtSearchEmployee   = new TextBox();
txtSearchInitials   = new TextBox();
ddDepartment        = new DropDownList();
ddOffice            = new DropDownList();

Department.loadDepartmentDropdown(this.ddDepartment, "--- All Departments --");
Office.loadOfficeDropdown(this.ddOffice, "--- All Offices ---");

this.lblMessage.Width = new Unit("800px");
this.btnSearch.Text = "Search";
this.btnSearch.Click += new EventHandler(btnSubmit_Click);
this.btnClear.Text = "Clear";
this.btnClear.Click += new EventHandler(btnClear_Click);

this.oDataset = new DataSet();
this.oView = new DataView();
this.oGrid = new SPGridView();
this.oGrid.DataSource = oView;
this.oGrid.AutoGenerateColumns = false;
this.oGrid.AllowSorting = true;

this.oGrid.Sorting += new GridViewSortEventHandler(oGrid_Sorting);

this.oGrid.AllowPaging = true;
oGrid.Columns.Add(Utilities.buildColumn("Employee", "Emp #"));

// Add Cols using a utility method
oGrid.Columns.Add(Utilities.buildColumn("FirstName", "First Name"));
oGrid.Columns.Add(Utilities.buildColumn("LastName", "Last Name"));
oGrid.Columns.Add(Utilities.buildColumn("WorkPhone", "Direct"));
oGrid.Columns.Add(Utilities.buildColumn("WorkPhoneExt", "Ext"));
oGrid.Columns.Add(Utilities.buildColumn("MobilePhone", "Cell"));
oGrid.Columns.Add(Utilities.buildColumn("Office", "Office"));
oGrid.Columns.Add(Utilities.buildColumn("PrimaryDept", "Primary Dept"));
oGrid.Columns.Add(Utilities.buildColumn("SecondaryDept", "Secondary Dept"));

this.Controls.Add(lblMessage);
this.Controls.Add(ddDepartment);
this.Controls.Add(ddOffice);
this.Controls.Add(txtSearchFN);
this.Controls.Add(txtSearchLN);
this.Controls.Add(txtSearchEmployee);
this.Controls.Add(txtSearchInitials);
this.Controls.Add(txtSearchPhone);
this.Controls.Add(btnSearch);
this.Controls.Add(btnClear);
this.Controls.Add(oGrid);

this.oGrid.PageSize = 10;
this.oGrid.AllowPaging = true;

this.oGrid.PageIndexChanging += new GridViewPageEventHandler(oGrid_PageIndexChanging);

this.oGrid.PagerTemplate = null;  // Must be called after Controls.Add(oGrid)

this.lblMessage.Width = new Unit("400px");
this.ddDepartment.Width = new Unit("180px");
this.ddOffice.Width = new Unit("180px");
this.btnSearch.Width = new Unit("80px");
this.btnClear.Width = new Unit("80px");
this.txtSearchFN.Width = new Unit("180px");
this.txtSearchLN.Width = new Unit("180px");
this.txtSearchEmployee.Width = new Unit("180px");
this.txtSearchInitials.Width = new Unit("180px");
this.txtSearchPhone.Width = new Unit("180px");

this.oGrid.Width = new Unit("800px");

}

void oGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{

oGrid.PageIndex = e.NewPageIndex;
//oGrid.DataBind();
bindGrid(String.Empty);

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
bindGrid(String.Empty);
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtSearchFN.Text        = string.Empty;
txtSearchLN.Text        = string.Empty;
txtSearchEmployee.Text  = string.Empty;
txtSearchPhone.Text     = string.Empty;
txtSearchInitials.Text  = string.Empty;
ddDepartment.SelectedIndex = 0;
ddOffice.SelectedIndex = 0;
Utilities.BindGridView(oGrid,true);
}

private void bindGrid(string sort)
{

string sFN = txtSearchFN.Text.Trim().Replace(" ", "%");
string sLN = txtSearchLN.Text.Trim().Replace(" ", "%");
string sEM = txtSearchEmployee.Text.Trim().Replace(" ", "%");
string sPH = txtSearchPhone.Text.Trim().Replace(" ", "%");
string sIN = txtSearchInitials.Text.Trim().Replace(" ", "%");
string sDept = string.Empty;

if (ddDepartment.SelectedIndex > 0)
sDept = "%" + ddDepartment.SelectedValue.ToString() + "%";

string sOffice = string.Empty;
if (ddOffice.SelectedIndex > 0)
sOffice = ddOffice.SelectedValue.ToString();

oDataset = Employee.searchEmployees(sFN, sLN, sIN, sPH, sOffice, sDept, sEM);
oView = new DataView(oDataset.Tables[0]);
oGrid.DataSource = oView;

if (Utilities.hasData(sort))
oView.Sort = sort;

//oGrid.DataBind();
Utilities.BindGridView(oGrid, oView, true);

}

void oGrid_Sorting(object sender, GridViewSortEventArgs e)
{

string lastExpression = "";

if (ViewState["SortExpression"] != null)
lastExpression = ViewState["SortExpression"].ToString();

string lastDirection = "asc";

if (ViewState["SortDirection"] != null)
lastDirection = ViewState["SortDirection"].ToString();

string newDirection = "asc";

if (e.SortExpression == lastExpression)
newDirection = (lastDirection == "asc") ? "desc" : "asc";

ViewState["SortExpression"] = e.SortExpression;
ViewState["SortDirection"] = newDirection;

string sSort = e.SortExpression + " " + newDirection;
bindGrid(sSort);

}

}

}

// Utilities.cs

using System;
using System.Collections.Generic;
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 Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint;
using System.IO;

namespace BWEmpDir
{
class Utilities
{

static private string _connR  = WebConfigurationManager.ConnectionStrings["IntranetUserR"].ConnectionString;
static private string _connRW = WebConfigurationManager.ConnectionStrings["IntranetUserRW"].ConnectionString;

static public SqlConnection getConnR()
{
return (new SqlConnection(_connR));
}
static public SqlConnection getConnRW()
{
return (new SqlConnection(_connRW));
}

static public bool hasData(string f)
{
bool bReturn = false;

if (f != null &amp;&amp; f.Trim().Length > 0)
bReturn = true;

return bReturn;
}

static public bool hasData(TextBox f)
{
bool bReturn = false;

if (f != null &amp;&amp; f.Text.Trim().Length > 0)
bReturn = true;

return bReturn;
}

static public bool hasNoData(string f)
{
bool bReturn = false;

if (f == null || f.Trim().Length == 0)
bReturn = true;

return bReturn;
}

static public bool hasNoData(TextBox f)
{
bool bReturn = false;

if (f == null || f.Text.Trim().Length == 0)
bReturn = true;

return bReturn;
}

static public void BindGridView(SPGridView gv, bool bRebind)
{
gv.DataSource = new string[] { }; //clear
if (bRebind)
gv.DataBind();
}

static public void BindGridView(SPGridView gv, DataSet ds, bool bRebind)
{
gv.DataSource = new string[] { }; //clear
gv.DataSource = ds;
if (bRebind)
gv.DataBind();
}
static public void BindGridView(SPGridView gv, DataView ds, bool bRebind)
{
gv.DataSource = new string[] { }; //clear
gv.DataSource = ds;
if (bRebind)
gv.DataBind();
}

static public BoundField buildColumn(string sData, string sHeader, string SortExp)
{
BoundField col = new BoundField();
col.DataField = sData;
col.HeaderText = sHeader;
col.SortExpression = SortExp;
return (col);
}

static public BoundField buildColumn(string sData, string sHeader)
{
return(buildColumn(sData, sHeader, sData));
}

}

}

#20 Some helpful links for tracking down & fixing SP errors

Event Viewer 10016 error (application log)

“The application-specific permission settings do not grant Local Activation permission…”

http://www.cleverworkarounds.com/2007/10/25/dcom-fun-with-sharepoint/

The blasted ‘An unexpected error has occurred’ error page with nothing in any log that you can find.

http://blog.thekid.me.uk/archive/2007/02/15/a-solution-to-quot-an-unexpected-error-has-occurred-quot-in-wss-v3.aspx

CAS errors on SqlClient (System.Data.SqlClient.SqlClientPermission). Use your c:\inetpub\wwwroot\wss\VirtualDirectories\<port number>\web.config to locate your active Security Policy config file rather than the following post’s suggestion.

http://www.mysharepointblog.com/2007/03/default.aspx

#19 Employee Directory Sharepoint Webpart Pt 1 – Stored Procedure

Starting with a new proc to select data of off the EM, Employee Custom Tab Fields and a union with a Auxilary data table to add in such things as printers, conference rooms, local police,  fire etc. I will build on this stored procedure defining a datagrid with filter controls in a Sharepoint webpart.

Virtualized Dev Environment: SQL 2005, Windows 2003 server, Visual Studio 2008, Sharepoint Extensions

This proc allows wildcard searches on fields and does not filter if a null is passed.  The union to the auxiliary table poses some challenges so I created a UDF dbo.IgnoreNullParm to return ‘1′ or ‘0′ to effectively turn off the union ‘leg’ when we are passed a filter parm that does not exist for that table (i.e. Initials).

Proc Def:

Create Procedure [dbo].[searchEmployees]
(
@FirstName        nvarchar(50)
,@LastName        nvarchar(50)
,@Initials              nvarchar(03)
,@Phone               nvarchar(20)
,@Office                nvarchar(50)
,@Dept                  nvarchar(50)
,@EmpNum        nvarchar(04)
)
as
Select
EM.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 (@FirstName is null)    Then '%' Else RTRIM(@FirstName)    + '%' End
OR    EM.PreferredName      like  Case When (@FirstName is null)    Then '%' Else RTRIM(@FirstName)    + '%' End
)
And    EM.LastName         like  Case When (@LastName is null) Then '%' Else RTRIM(@LastName)    + '%' End
And    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 (@Initials is null) Then '%' Else  @Initials  End
And    (        EM.WorkPhone    like '%' + isNull(@Phone,'%') + '%'
OR    EM.WorkPhoneExt like '%' + isNull(@Phone,'%') + '%'
OR    EM.MobilePhone    like '%' + isNull(@Phone,'%') + '%' )
And ECTF.Cust5ULPhysical = isNull(@Office, ECTF.Cust5ULPhysical)
And ( EM.Org like '%' + isNull(@Dept,'%') + '%'
OR ECTF.custSecondaryDepartment like '%' + isNull(@Dept,'%') + '%'  )
And EM.Employee like  isNull(@EmpNum,'%') + '%'
 UNION ALL
Select  EmpNum                     as Employee
, FirstName
, LastName
, ''                            as MiddleName
, ''                            as PrimaryDept
, ''                            as Region
, Phone                        as WorkPhone
, PhoneExt                        as WorkPhoneExt
, ''                             as MobilePhone
, ECTF_Cust5ULPhysical            as Office
, ''                            as SecondaryDept
From OfficeAux OA
Where
OA.FirstName        like  Case When (@FirstName is null) Then '%' Else RTRIM(@FirstName)    + '%' End
And    OA.LastName         like  Case When (@LastName is null)     Then '%' Else RTRIM(@LastName)        + '%' End
And '1'    = dbo.IgnoreNullParm(@Initials)        <em> -- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And '1'    = dbo.IgnoreNullParm(@Dept)           <em>-- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And    (        OA.Phone        like '%' + isNull(@Phone,'%')  + '%'
OR    OA.PhoneExt    like '%' + isNull(@Phone,'%')  + '%')
And ECTF_Cust5ULPhysical = isNull(@Office, ECTF_Cust5ULPhysical)
And EmpNum  like  isNull(@EmpNum,'%') + '%'

UDF

Create function [dbo].[IgnoreNullParm]
(
@Field  nvarchar(50)
)
Returns bit
-- Sends back True '1' if Parm is null
-- Setup in where clause as Where '1' = IgnoreNullParm(@parm)
-- Allows you to effectively turn off a union 'leg' if a parm is not null and ignore it if not null
as

Begin

Declare @bReturn as  bit

Select  @bReturn = Case When (@Field is null) Then '1' Else '0' End

Return @bReturn

End

Auxilary Table:

CREATE TABLE [dbo].[OfficeAux](
[ID] [uniqueidentifier] NOT NULL,
[ECTF_Cust5ULPhysical] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNum] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhoneExt] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MobilePhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isActive] [bit] NULL,
[lastUpdated] [datetime] NULL,
[lastUpdater] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
End