Found my code.
Stored Procedures-
CREATE PROCEDURE dbo.ProjectsSearch
(
@pageindex int,
@pagesize int,
@costcentreid int,
@employeeno int,
@stageid int,
@departmentid int,
@onstratplan int,
@title varchar(50),
@startdate datetime,
@enddate datetime
)
AS
SET NOCOUNT ON
SELECT * FROM
(
SELECT ProjectID, Title, CostCentreID, OnStratPlan, EmployeeNo, StageID, PercentComplete, DepartmentID, EntryDate, ManDaysRequired,
ROW_NUMBER() OVER (ORDER BY ProjectID) AS RowNum FROM dbo.ProjectDetails
) AS Projects
WHERE Projects.RowNum BETWEEN (@pageindex*@pagesize+1) AND ((@pageindex+1)*@pagesize)
AND (Projects.CostCentreID = @costcentreid OR @costcentreid = -1)
AND (Projects.EmployeeNo = @employeeno OR @employeeno=-1)
AND (Projects.StageID = @stageid OR @stageid = -1)
AND (Projects.DepartmentID = @departmentid OR @departmentid = -1)
AND (Projects.OnStratPlan = @onstratplan OR @onstratplan = -1)
AND (Projects.Title LIKE '%' + @title + '%' OR @title = '')
AND (Projects.EntryDate BETWEEN @startdate AND @enddate)
ALTER PROCEDURE dbo.ProjectsSearchCount
(
@costcentreid int,
@employeeno int,
@stageid int,
@departmentid int,
@onstratplan int,
@title varchar(50),
@startdate datetime,
@enddate datetime
)
AS
SET NOCOUNT ON
SELECT COUNT(*) FROM dbo.ProjectDetails
WHERE (ProjectDetails.CostCentreID = @costcentreid OR @costcentreid = -1)
AND (ProjectDetails.EmployeeNo = @employeeno OR @employeeno=-1)
AND (ProjectDetails.StageID = @stageid OR @stageid = -1)
AND (ProjectDetails.DepartmentID = @departmentid OR @departmentid = -1)
AND (ProjectDetails.OnStratPlan = @onstratplan OR @onstratplan = -1)
AND (ProjectDetails.Title LIKE '%' + @title + '%' OR @title = '')
AND (ProjectDetails.EntryDate BETWEEN @startdate AND @enddate)
DAL-
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for SQLProjectsProvider
/// </summary>
namespace Projects.DAL.SQLClient
{
public class SQLProjectsProvider : ProjectsProvider
{
public override List<ProjectDetails> GetProjectsBySearch(int pageIndex, int pageSize, int costCentreID, int employeeNo,
int stageID, int departmentID, int onStratPlan, string title, DateTime startDate, DateTime endDate)
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("dbo.ProjectsSearch", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pageindex", SqlDbType.Int).Value = pageIndex;
cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = pageSize;
cmd.Parameters.Add("@costcentreid", SqlDbType.Int).Value = costCentreID;
cmd.Parameters.Add("@employeeno", SqlDbType.Int).Value = employeeNo;
cmd.Parameters.Add("@stageid", SqlDbType.Int).Value = stageID;
cmd.Parameters.Add("@departmentid", SqlDbType.Int).Value = departmentID;
cmd.Parameters.Add("@onstratplan", SqlDbType.Int).Value = onStratPlan;
cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = title;
cmd.Parameters.Add("@startdate", SqlDbType.DateTime).Value = startDate;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime).Value = endDate;
conn.Open();
return GetProjectDetailsListFromReader(ExecuteReader(cmd), false);
}
}
public override int GetProjectsBySearchCount(int costCentreID, int employeeNo, int stageID, int departmentID, int onStratPlan,
string title, DateTime startDate, DateTime endDate)
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("dbo.ProjectsSearchCount", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@costcentreid", SqlDbType.Int).Value = costCentreID;
cmd.Parameters.Add("@employeeno", SqlDbType.Int).Value = employeeNo;
cmd.Parameters.Add("@stageid", SqlDbType.Int).Value = stageID;
cmd.Parameters.Add("@departmentid", SqlDbType.Int).Value = departmentID;
cmd.Parameters.Add("@onstratplan", SqlDbType.Int).Value = onStratPlan;
cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = title;
cmd.Parameters.Add("@startdate", SqlDbType.DateTime).Value = startDate;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime).Value = endDate;
conn.Open();
return (int)ExecuteScalar(cmd);
}
}
}
n.b. The ProjectDetails class is a simple, lightweight wrapper mapped to the Projects table. The SQLProjectsProvider class inherits from the abstract, generic ProjectsProvider class and is specific to SQL Server.
Part of the BLL-
using System;
using System.Collections.Generic;
using System.ComponentModel;
using Projects.DAL;
using Projects.BLL.LookUps;
/// <summary>
/// Summary description for Project
/// </summary>
namespace Projects.BLL.Projects
{
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<ProjectList> ProjectsSearch(int costCentreID, int employeeNo, int stageID, int departmentID, int onStratPlan,
int startRowIndex, int maximumRows, string title, string start, string end)
{
start = NullToEmpty(start);
end = NullToEmpty(end);
DateTime startDate;
DateTime endDate;
bool hasStartDate = DateTime.TryParse(start, out startDate);
bool hasEndDate = DateTime.TryParse(end, out endDate);
if (!hasEndDate)
endDate = DateTime.MaxValue;
if (!hasStartDate)
startDate = new DateTime(1753, 1, 1);
title = NullToEmpty(title);
List<ProjectList> projects = null;
string key = "projects_list_" + costCentreID + "_" + employeeNo + "_" + stageID + "_" + departmentID + "_" + onStratPlan + "_" +
startRowIndex + "_" + maximumRows + "_" + title + "_" + start + "_" + end;
if (Settings.EnableCaching && Cache[key] != null)
{
projects = (List<ProjectList>)Cache[key];
}
else
{
List<ProjectDetails> records = SiteProvider.Projects.GetProjectsBySearch(startRowIndex, maximumRows, costCentreID, employeeNo,
stageID, departmentID, onStratPlan, title, startDate, endDate);
projects = GetProjectListFromProjectDetailsList(records);
CacheData(key, projects);
}
return projects;
}
public static int ProjectsSearchCount(int costCentreID, int employeeNo, int stageID, int departmentID, int onStratPlan,
int startRowIndex, int maximumRows, string title, string start, string end)
{
int count = 0;
start = NullToEmpty(start);
end = NullToEmpty(end);
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MaxValue;
bool hasStartDate = DateTime.TryParse(start, out startDate);
bool hasEndDate = DateTime.TryParse(end, out endDate);
if (!hasEndDate)
endDate = DateTime.MaxValue;
if (!hasStartDate)
startDate = new DateTime(1753, 1, 1);
title = NullToEmpty(title);
string key = "projects_list_count_" + costCentreID + "_" + employeeNo + "_" + stageID + "_" + departmentID + "_" + onStratPlan + "_" +
startRowIndex + "_" + maximumRows + "_" + title + "_" + start + "_" + end;
if (Settings.EnableCaching && Cache[key] != null)
{
count = (int)Cache[key];
}
else
{
count = SiteProvider.Projects.GetProjectsBySearchCount(costCentreID, employeeNo, stageID, departmentID, onStratPlan, title,
startDate, endDate);
CacheData(key, count);
}
return count;
}
}
From the Web page-
<div id="homepagemaindiv" style="font-size: 0.8em; border-bottom: none; background-color: Transparent;
margin: 10px 1% 10px 1%; width: 98%;">
<div class="homepageheader">
Filter and Sort Options</div>
<div class="homepagecontent">
<table style="width: 100%;">
<tr class="contentrow">
<td style="width: 25%">
Projects per page:
</td>
<td style="width: 25%">
Champion:
</td>
<td style="width: 25%">
Current Status:
</td>
</tr>
<tr>
<td>
<asp:DropDownList ID="ddlRecordsPerPage" runat="server" AppendDataBoundItems="True">
<asp:ListItem Selected="True">10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlEmployees" runat="server" DataSourceID="odsEmployees" DataTextField="Name"
DataValueField="ID" AppendDataBoundItems="True">
<asp:ListItem Selected="True" Value="-1">(All)</asp:ListItem>
</asp:DropDownList>
<asp:ObjectDataSource ID="odsEmployees" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetEmployeesByDepartment"
TypeName="Projects.BLL.LookUps.Employee">
<SelectParameters>
<asp:Parameter DefaultValue="3" Name="departmentID" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
</td>
<td>
<asp:DropDownList ID="ddlStages" runat="server" DataSourceID="odsStages"
AppendDataBoundItems="True" DataTextField="Name" DataValueField="ID">
<asp:ListItem Selected="True" Value="-1">(Any)</asp:ListItem>
</asp:DropDownList>
<asp:ObjectDataSource ID="odsStages" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetStages"
TypeName="Projects.BLL.LookUps.Stage"></asp:ObjectDataSource>
</td>
</tr>
<tr class="contentrow">
<td>
Allocated to cost centre:</td>
<td>
Allocated to Department Sub Group:</td>
<td>
On Strategic Plan?</td>
</tr>
<tr>
<td>
<asp:DropDownList ID="ddlCostCentres" runat="server"
AppendDataBoundItems="True" DataSourceID="odsCostCentres" DataTextField="Name"
DataValueField="ID">
<asp:ListItem Selected="True" Value="-1">(All)</asp:ListItem>
</asp:DropDownList>
<asp:ObjectDataSource ID="odsCostCentres" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetCostCentres"
TypeName="Projects.BLL.LookUps.CostCentre"></asp:ObjectDataSource>
</td>
<td>
<asp:DropDownList ID="ddlDepartmentSubGroups" runat="server"
AppendDataBoundItems="True" DataSourceID="odsDepartmentSubGroups"
DataTextField="Name" DataValueField="ID">
<asp:ListItem Selected="True" Value="-1">(All)</asp:ListItem>
</asp:DropDownList>
<asp:ObjectDataSource ID="odsDepartmentSubGroups" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetDepartmentSubGroups"
TypeName="Projects.BLL.LookUps.DepartmentSubGroup">
</asp:ObjectDataSource>
</td>
<td>
<asp:DropDownList ID="ddlOnStratPlan" runat="server"
AppendDataBoundItems="True">
<asp:ListItem Selected="True" Value="-1">(All)</asp:ListItem>
<asp:ListItem Value="1">Yes</asp:ListItem>
<asp:ListItem Value="0">No</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr class="contentrow">
<td>
Title contains keyword or phrase:</td>
<td>
Between:
</td>
<td>
And:</td>
</tr>
<tr>
<td style="height: 25px">
<asp:TextBox ID="txtTitle" runat="server" Width="90%"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtStart" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="calEnd" runat="server" Format="dd/MM/yyyy" TargetControlID="txtEnd">
</cc1:CalendarExtender>
</td>
<td>
<asp:TextBox ID="txtEnd" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="calStart" runat="server" Format="dd/MM/yyyy" TargetControlID="txtStart">
</cc1:CalendarExtender>
</td>
</tr>
<tr class="contentrow">
<td style="vertical-align: bottom" rowspan="2">
<asp:Button ID="btnShow" runat="server" Text="Show Records" />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnReset" runat="server" Text="Reset Filter" />
</td>
</tr>
</table>
</div>
<div class="separator" style="margin-bottom: 20px; border: none;">
</div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" DataSourceID="odsProjects">
<Columns>
<asp:BoundField DataField="EntryDate" DataFormatString="{0:d}"
HeaderText="Date" HtmlEncode="False" SortExpression="EntryDate" />
<asp:BoundField DataField="EmployeeName" HeaderText="Champion" ReadOnly="True"
SortExpression="EmployeeName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title">
<ItemStyle Width="20%" />
</asp:BoundField>
<asp:BoundField DataField="CostCentre" HeaderText="Cost Centre" ReadOnly="True"
SortExpression="CostCentre" />
<asp:CheckBoxField DataField="OnStratPlan" HeaderText="On Strat Plan?"
SortExpression="OnStratPlan" />
<asp:BoundField DataField="Stage" HeaderText="Stage" ReadOnly="True"
SortExpression="Stage" />
<asp:BoundField DataField="PercentComplete" HeaderText="% Complete"
SortExpression="PercentComplete" />
<asp:BoundField DataField="Department" HeaderText="Department Sub Group"
ReadOnly="True" SortExpression="Department" />
<asp:BoundField DataField="ManDaysRequired" HeaderText="Man Days Required"
SortExpression="ManDaysRequired" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID"
Visible="False" />
<asp:HyperLinkField DataNavigateUrlFields="id"
DataNavigateUrlFormatString="projectdetails.aspx?id={0}"
NavigateUrl="~/ProjectDetails.aspx" Target="_blank" Text="Details" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="odsProjects" runat="server" EnablePaging="True"
OldValuesParameterFormatString="original_{0}"
SelectCountMethod="ProjectsSearchCount" SelectMethod="ProjectsSearch"
TypeName="Projects.BLL.Projects.Project">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCostCentres" Name="costCentreID"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ddlEmployees" Name="employeeNo"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ddlStages" Name="stageID"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ddlDepartmentSubGroups" Name="departmentID"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ddlOnStratPlan" Name="onStratPlan"
PropertyName="SelectedValue" Type="Int32" />
<asp:Parameter Name="startRowIndex" Type="Int32" />
<asp:ControlParameter ControlID="ddlRecordsPerPage" Name="maximumRows"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="txtTitle" Name="title" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtStart" Name="start" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtEnd" Name="end" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:ObjectDataSource>