分页存储过程代码如下：
Create PROCEDURE [dbo].[Task_SelectPagedAndSorted]
(
@ProjectID uniqueidentifier,
@ProjectAreaID uniqueidentifier,
@DepartmentID uniqueidentifier,
@ChiefID uniqueidentifier,
@State nvarchar(32),
@Priority int,
@Triage nvarchar(32),
@PlanStartDateF datetime,
@PlanStartDateL datetime,
@PlanEndDateF datetime,
@PlanEndDateL datetime,
@CompletedDateF datetime,
@CompletedDateL datetime,
@SortExpression nvarchar(256),
@StartRowIndex int,
@MaximumRows int
)
AS
DECLARE @sql nvarchar(4000)
DECLARE @ViewSql nvarchar(4000)
DECLARE @WhereClause nvarchar(2000)
DeCLARE @FEndRowIndex int
DeCLARE @FStartRowIndex int
DeCLARE @FMaximumRows int
DeCLARE @FSortExpression nvarchar(256)
-- Make sure a @sortExpression is specified
IF LEN(@SortExpression) > 0
SET @FSortExpression = @SortExpression
ELSE
SET @FSortExpression = 'ChangedDate DESC'
if (@StartRowIndex is null)
SET @FStartRowIndex = 0;
else
SET @FStartRowIndex = @StartRowIndex
if (@MaximumRows is null) or (@MaximumRows <= 0)
SET @FMaximumRows = 1000;
else
SET @FMaximumRows = @MaximumRows
SET @FEndRowIndex = @FStartRowIndex + @FMaximumRows
SET @WhereClause = 'Where --'
if not ((@ProjectID is null) or (@ProjectID = '00000000-0000-0000-0000-000000000000'))
SET @WhereClause = @WhereClause + 'AND
([ProjectID] = ''' + CAST(@ProjectID as nvarchar(64)) + ''')'
if not ((@ProjectAreaID is null) or (@ProjectAreaID = '00000000-0000-0000-0000-000000000000'))
SET @WhereClause = @WhereClause + 'AND
([ProjectAreaID] = ''' + CAST(@ProjectAreaID as nvarchar(64)) + ''')'
if not ((@DepartmentID is null) or (@DepartmentID = '00000000-0000-0000-0000-000000000000'))
SET @WhereClause = @WhereClause + 'AND
([DepartmentID] = ''' + CAST(@DepartmentID as nvarchar(64)) + ''')'
if not ((@ChiefID is null) or (@ChiefID = '00000000-0000-0000-0000-000000000000'))
SET @WhereClause = @WhereClause + 'AND
([ChiefID] = ''' + CAST(@ChiefID as nvarchar(64)) + ''')'
if LEN(@State) > 0
SET @WhereClause = @WhereClause + 'AND
([State] = ''' + @State + ''')'
if not ((@Priority is null) or (@Priority < 0))
SET @WhereClause = @WhereClause + 'AND
([Priority] = ' + CONVERT(nvarchar(10), @Priority) + ')'
if LEN(@Triage) > 0
SET @WhereClause = @WhereClause + 'AND
([Triage] = ''' + @Triage + ''')'
if not (@PlanStartDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanStartDate] is null) or ([PlanStartDate] >= CAST(''' + CAST(@PlanStartDateF as nvarchar) + ''' AS datetime)))'
if not (@PlanStartDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanStartDate] is null) or ([PlanStartDate] <= CAST(''' + CAST(@PlanStartDateL as nvarchar) + ''' AS datetime)))'
if not (@PlanEndDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanEndDate] is null) or ([PlanEndDate] >= CAST(''' + CAST(@PlanEndDateF as nvarchar) + ''' AS datetime)))'
if not (@PlanEndDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanEndDate] is null) or ([PlanEndDate] <= CAST(''' + CAST(@PlanEndDateL as nvarchar) + ''' AS datetime)))'
if not (@CompletedDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([CompletedDate] is null) or ([CompletedDate] >= CAST(''' + CAST(@CompletedDateF as nvarchar) + ''' AS datetime)))'
if not (@CompletedDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([CompletedDate] is null) or ([CompletedDate] <= CAST(''' + CAST(@CompletedDateL as nvarchar) + ''' AS datetime)))'
if (@WhereClause = 'Where --')
SET @WhereClause = ''
SET @sql = '
Select
Task.[TaskID],
[TaskSQN],
[TaskName],
[DepartmentID],
[ChangerID],
[CreatedDate],
(Select FullName FROM dbo.UserInfo AS CreatorUser Where (dbo.Task.CreatorID = UserID)) AS
Creator,
[CreatorID],
[Triage],
(Select DepartmentName FROM dbo.Department Where (dbo.Task.DepartmentID = DepartmentID)) AS
Department,
[ChiefID],
(Select FullName FROM dbo.UserInfo AS ChiefUser Where (dbo.Task.ChiefID = UserID)) AS
Chief,
[ProjectID],
(Select ProjectName FROM dbo.Project Where (dbo.Task.ProjectID = ProjectID)) AS
Project,
[PlanEndDate],
[PlanStartDate],
[CompletedDate],
[Priority],
[State],
[WorkLoad],
(Select TaskName FROM dbo.Task AS ParentTask Where (dbo.Task.ParentID = TaskID)) AS
ParentTask,
[ParentID],
(Select ProjectAreaName FROM dbo.ProjectArea Where (dbo.Task.ProjectAreaID = ProjectAreaID)) AS
ProjectArea,
[ProjectAreaID],
[Description],
[Rev],
[ChangedDate],
(Select FullName FROM dbo.UserInfo AS ChangerUser Where (dbo.Task.ChangerID = UserID)) AS
Changer
FROM Task,
(Select
[TaskID],
ROW_NUMBER() OVER (ORDER BY ' + @FSortExpression + ') AS RowRank
FROM [Task]
' + @WhereClause + '
) AS RankTask
Where (Task.TaskID = RankTask.TaskID)
AND (RankTask.RowRank >= ' + CONVERT(nvarchar(10), @FStartRowIndex) + ')
AND (RankTask.RowRank < ' + CONVERT(nvarchar(10), @FEndRowIndex) + ')
'
SET @ViewSql = '
Select
ViewTask.[TaskID],
[TaskSQN],
[TaskName],
[DepartmentID],
[ChangerID],
[CreatedDate],
[Creator],
[CreatorID],
[Triage],
[Department],
[ChiefID],
[Chief],
[ProjectID],
[Project],
[PlanEndDate],
[PlanStartDate],
[CompletedDate],
[Priority],
[State],
[WorkLoad],
[ParentTask],
[ParentID],
[ProjectArea],
[ProjectAreaID],
[Description],
[Rev],
[ChangedDate],
[Changer]
FROM ViewTask,
(Select
[TaskID],
ROW_NUMBER() OVER (ORDER BY ' + @FSortExpression + ') AS RowRank
FROM [Task]
' + @WhereClause + '
) AS RankTask
Where (ViewTask.TaskID = RankTask.TaskID)
AND (RankTask.RowRank >= ' + CONVERT(nvarchar(10), @FStartRowIndex) + ')
AND (RankTask.RowRank < ' + CONVERT(nvarchar(10), @FEndRowIndex) + ')
'
EXEC sp_executesql @sql
RETURN
计算Count代码如下：
Alter PROCEDURE dbo.Task_SelectPagedAndSortedCount
(
@ProjectID uniqueidentifier,
@ProjectAreaID uniqueidentifier,
@DepartmentID uniqueidentifier,
@ChiefID uniqueidentifier,
@State nvarchar(32),
@Priority int,
@Triage nvarchar(32),
@PlanStartDateF datetime,
@PlanStartDateL datetime,
@PlanEndDateF datetime,
@PlanEndDateL datetime,
@CompletedDateF datetime,
@CompletedDateL datetime,
@Count int output
)
AS
DECLARE @sql nvarchar(4000)
DECLARE @WhereClause nvarchar(2000)
SET @WhereClause = 'Where --'
if not (@ProjectID is null)
SET @WhereClause = @WhereClause + 'AND
([ProjectID] = CAST(''' + CAST(@ProjectID as nvarchar) + ''') AS uniqueidentifier)'
if not (@ProjectAreaID is null)
SET @WhereClause = @WhereClause + 'AND
([ProjectAreaID] = CAST(''' + CAST(@ProjectAreaID as nvarchar) + ''') AS uniqueidentifier)'
if not (@DepartmentID is null)
SET @WhereClause = @WhereClause + 'AND
([DepartmentID] = CAST(''' + CAST(@DepartmentID as nvarchar) + ''') AS uniqueidentifier)'
if not (@ChiefID is null)
SET @WhereClause = @WhereClause + 'AND
([ChiefID] = CAST(''' + CAST(@ChiefID as nvarchar) + ''') AS uniqueidentifier)'
if LEN(@State) > 0
SET @WhereClause = @WhereClause + 'AND
([State] = ''' + @State + ''')'
if not ((@Priority is null) or (@Priority < 0))
SET @WhereClause = @WhereClause + 'AND
([Priority] = ' + CONVERT(nvarchar(10), @Priority) + ')'
if LEN(@Triage) > 0
SET @WhereClause = @WhereClause + 'AND
([Triage] = ''' + @Triage + ''')'
if not (@PlanStartDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanStartDate] is null) or ([PlanStartDate] >= CAST(''' + CAST(@PlanStartDateF as nvarchar) + ''' AS datetime)))'
if not (@PlanStartDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanStartDate] is null) or ([PlanStartDate] <= CAST(''' + CAST(@PlanStartDateL as nvarchar) + ''' AS datetime)))'
if not (@PlanEndDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanEndDate] is null) or ([PlanEndDate] >= CAST(''' + CAST(@PlanEndDateF as nvarchar) + ''' AS datetime)))'
if not (@PlanEndDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([PlanEndDate] is null) or ([PlanEndDate] <= CAST(''' + CAST(@PlanEndDateL as nvarchar) + ''' AS datetime)))'
if not (@CompletedDateF is null)
SET @WhereClause = @WhereClause + 'AND
(([CompletedDate] is null) or ([CompletedDate] >= CAST(''' + CAST(@CompletedDateF as nvarchar) + ''' AS datetime)))'
if not (@CompletedDateL is null)
SET @WhereClause = @WhereClause + 'AND
(([CompletedDate] is null) or ([CompletedDate] <= CAST(''' + CAST(@CompletedDateL as nvarchar) + ''' AS datetime)))'
if (@WhereClause = 'Where --')
SET @WhereClause = ''
SET @sql = '(
Select
' + @Count + ' = Count(*)
FROM [Task]
' + @WhereClause + ')'
-- Execute the SQL query
EXEC sp_executesql @sql
RETURN
DataList代码如下：
<atlas:UpdatePanel ID="TaskListUpdatePanel" runat="server" Mode="Conditional">
<Triggers>
<atlas:ControlEventTrigger ControlID="TaskFiltButton" EventName="Click" />
<atlas:ControlEventTrigger ControlID="NewTaskFormView" EventName="ItemInserted" />
</Triggers>
<ContentTemplate>
<asp:DataList ID="TaskListDataList" runat="server" Width="100%" DataSourceID="TaskListDataSource">
<ItemTemplate>
<%--<div class="DataListDate">
</div>--%>
<div class="DataListItem">
<div class="DataListTitle">
<asp:HyperLink ID="TaskListDetailLink" runat="server" NavigateUrl='<%# Eval("TaskID", "~/ControlPanel/WorkItem/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("TaskName") %>'>
</asp:HyperLink>
</div>
<div class="DataListStatus">
<asp:Label ID="PriorityLabel" runat="server" Text='<%# Eval("Priority") %>'></asp:Label>
|
<asp:Label ID="TaskListCompletedDateLabel" runat="server" Text='<%# Eval("CompletedDate", "{0:yyyy-MM-dd}") %>'></asp:Label>
|
<asp:Label ID="TaskListStateLabel" runat="server" Text='<%# Eval("State") %>'></asp:Label>
|
<asp:Label ID="TriageLabel" runat="server" Text='<%# Eval("Triage") %>'></asp:Label>
</div>
<div class="DataListBody">
<asp:Literal ID="TaskListDescriptionLiteral" runat="server" Text='<%# Eval("Description") %>'></asp:Literal>
</div>
<div class="DataListFoot">
<asp:HyperLink ID="TaskListDepartmentIDLink" runat="server" NavigateUrl='<%# Eval("DepartmentID", "~/ControlPanel/DepartmentManage.aspx?DepartmentID={0}") %>' Text='<%# Eval("Department") %>'>
</asp:HyperLink>
|
<asp:HyperLink ID="TaskListChiefIDLink" runat="server" NavigateUrl='<%# Eval("ChiefID", "~/ControlPanel/DepartmentManage.aspx?UserID={0}") %>' Text='<%# Eval("Chief") %>'>
</asp:HyperLink>
|
<asp:HyperLink ID="TaskListProjectIDLink" runat="server" NavigateUrl='<%# Eval("ProjectID", "~/ControlPanel/ProjectManage.aspx?ProjectID={0}") %>' Text='<%# Eval("Project") %>'>
</asp:HyperLink>
|
<asp:HyperLink ID="TaskListProjectAreaIDLink" runat="server" NavigateUrl='<%# Eval("ProjectAreaID", "~/ControlPanel/ProjectManage.aspx?ProjectAreaID={0}") %>' Text='<%# Eval("ProjectArea") %>'>
</asp:HyperLink>
<%-- |
<asp:HyperLink ID="TaskListParentIDLink" runat="server" NavigateUrl='<%# Eval("ParentID", "~/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("Parent.TaskName") %>'>
</asp:HyperLink>--%>
|
<asp:Label ID="TaskListPlanStartDatePlanEndDateLabel" runat="server" Text='<%# "(" + Eval("PlanStartDate", "{0:yyyy-MM-dd}") + "~" + Eval("PlanEndDate", "{0:yyyy-MM-dd}") + ")" %>'></asp:Label>
</div>
<div class="DataListVersion">
<%= Resources.Resource.Creator + "："%>
<asp:Label ID="CreatorLabel" runat="server" Text='<%# Eval("Creator") %>'></asp:Label>
<asp:Label ID="CreatedDateLabel" runat="server" Text='<%# Eval("CreatedDate", "{0:yyyy-MM-dd hh:mm:ss}") %>'></asp:Label>
|
<%= Resources.Resource.Changer + "："%>
<asp:Label ID="ChangerLabel" runat="server" Text='<%# Eval("Changer") %>'></asp:Label>
<asp:Label ID="ChangedDateLabel" runat="server" Text='<%# Eval("ChangedDate", "{0:yyyy-MM-dd hh:mm:ss}") %>'></asp:Label>
</div>
</div>
</ItemTemplate>
</asp:DataList>
<asp:ObjectDataSource ID="TaskListDataSource" runat="server" DataObjectTypeName="AIO.WITDB.WITDataObject" SelectMethod="ReadPagedAndSorted" TypeName="AIO.WITDB.WITDataObject">
<SelectParameters>
<asp:ControlParameter ControlID="TaskFiltProjectList" Name="projectID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TaskFiltProjectAreaTextBox" Name="projectAreaID" PropertyName="Value" />
<asp:ControlParameter ControlID="TaskFiltDepartmentTextbox" Name="departmentID" PropertyName="Value" />
<asp:ControlParameter ControlID="TaskFiltChiefList" Name="ChiefID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TaskFiltStateSelectOptionDropDownList" Name="state" PropertyName="SelectOptionItem" Type="String" />
<asp:ControlParameter ControlID="TaskFiltPrioritySelectOptionDropDownList" Name="priority" PropertyName="SelectOptionItem" Type="String" />
<asp:ControlParameter ControlID="TaskFiltTriageSelectOptionDropDownList" Name="triage" PropertyName="SelectOptionItem" Type="String" />
<asp:ControlParameter ControlID="TaskFiltPlanStartDateFDateTextBox" Name="planStartDateF" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TaskFiltPlanStartDateLDateTextBox" Name="planStartDateL" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TaskFiltPlanEndDateFDateTextBox" Name="planEndDateF" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TaskFiltPlanEndDateLDateTextBox" Name="planEndDateL" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TaskFiltCompletedDateFDateTextBox" Name="completedDateF" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TaskFiltCompletedDateLDateTextBox" Name="completedDateL" PropertyName="Text" Type="DateTime" />
<asp:Parameter ConvertEmptyStringToNull="True" DefaultValue="" Name="sortExpression" Type="String" />
<asp:Parameter ConvertEmptyStringToNull="True" DefaultValue="0" Name="startRowIndex" />
<asp:Parameter ConvertEmptyStringToNull="True" DefaultValue="20" Name="maximumRows" />
</SelectParameters>
</asp:ObjectDataSource>
</ContentTemplate>
</atlas:UpdatePanel>
本来系统采用BLinq实现、因为有复杂的逻辑关系、在业务层联合会产生大量的查询语句（大概200~200个）、现在采用存储过程调用动态SQL效率大大提高了。希望会对朋友有所帮助和借鉴