分页存储过程代码如下：ALTER 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 intDeCLARE @FStartRowIndex intDeCLARE @FMaximumRows intDeCLARE @FSortExpression nvarchar(256)
-- Make sure a @sortExpression is specifiedIF LEN(@SortExpression) > 0 SET @FSortExpression = @SortExpressionELSE SET @FSortExpression = 'ChangedDate DESC'
if (@StartRowIndex is null) SET @FStartRowIndex = 0;else SET @FStartRowIndex = @StartRowIndexif (@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 RankTaskWHERE (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 RankTaskWHERE (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 queryEXEC sp_executesql @sql
RETURN
DataList代码如下：<atlas:UpdatePanel runat='server' Mode='Conditional'> <Triggers> <atlas:ControlEventTrigger ControlID='TaskFiltButton' EventName='Click' /> <atlas:ControlEventTrigger ControlID='NewTaskFormView' EventName='ItemInserted' /> </Triggers> <ContentTemplate> <asp:DataList runat='server' DataSourceID='TaskListDataSource'> <ItemTemplate> <%--<div class='DataListDate'> </div>--%> <div class='DataListItem'> <div class='DataListTitle'> <asp:HyperLink runat='server' NavigateUrl='<%# Eval('TaskID', '~/ControlPanel/WorkItem/TaskDetail.aspx?TaskID={0}') %>' Text='<%# Eval('TaskName') %>'> </asp:HyperLink> </div> <div class='DataListStatus'> <asp:Label runat='server' Text='<%# Eval('Priority') %>'></asp:Label> | <asp:Label runat='server' Text='<%# Eval('CompletedDate', '{0:yyyy-MM-dd}') %>'></asp:Label> | <asp:Label runat='server' Text='<%# Eval('State') %>'></asp:Label> | <asp:Label runat='server' Text='<%# Eval('Triage') %>'></asp:Label> </div> <div class='DataListBody'> <asp:Literal runat='server' Text='<%# Eval('Description') %>'></asp:Literal> </div> <div class='DataListFoot'> <asp:HyperLink runat='server' NavigateUrl='<%# Eval('DepartmentID', '~/ControlPanel/DepartmentManage.aspx?DepartmentID={0}') %>' Text='<%# Eval('Department') %>'> </asp:HyperLink> | <asp:HyperLink runat='server' NavigateUrl='<%# Eval('ChiefID', '~/ControlPanel/DepartmentManage.aspx?UserID={0}') %>' Text='<%# Eval('Chief') %>'> </asp:HyperLink> | <asp:HyperLink runat='server' NavigateUrl='<%# Eval('ProjectID', '~/ControlPanel/ProjectManage.aspx?ProjectID={0}') %>' Text='<%# Eval('Project') %>'> </asp:HyperLink> | <asp:HyperLink runat='server' NavigateUrl='<%# Eval('ProjectAreaID', '~/ControlPanel/ProjectManage.aspx?ProjectAreaID={0}') %>' Text='<%# Eval('ProjectArea') %>'> </asp:HyperLink> <%-- | <asp:HyperLink runat='server' NavigateUrl='<%# Eval('ParentID', '~/TaskDetail.aspx?TaskID={0}') %>' Text='<%# Eval('Parent.TaskName') %>'> </asp:HyperLink>--%> | <asp:Label 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 runat='server' Text='<%# Eval('Creator') %>'></asp:Label> <asp:Label runat='server' Text='<%# Eval('CreatedDate', '{0:yyyy-MM-dd hh:mm:ss}') %>'></asp:Label> | <%= Resources.Resource.Changer + '：'%> <asp:Label runat='server' Text='<%# Eval('Changer') %>'></asp:Label> <asp:Label runat='server' Text='<%# Eval('ChangedDate', '{0:yyyy-MM-dd hh:mm:ss}') %>'></asp:Label> </div> </div> </ItemTemplate> </asp:DataList> <asp:ObjectDataSource 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效率大大提高了。希望会对朋友有所帮助和借鉴http://www.cnblogs.com/Bolik/archive/2006/08/24/485647.html
