Bolik‘s AIO Blog
All In One Team Blog
分页存储过程代码如下:
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 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 nullor (@MaximumRows <= 0)
  
SET @FMaximumRows = 1000;
else
  
SET @FMaximumRows = @MaximumRows 

SET @FEndRowIndex = @FStartRowIndex + @FMaximumRows

SET @WhereClause = 'WHERE --'
if not ((@ProjectID is nullor (@ProjectID = '00000000-0000-0000-0000-000000000000'))
  
SET @WhereClause = @WhereClause + 'AND
    ([ProjectID] = 
''' + CAST(@ProjectID as nvarchar(64)) + ''')'
if not ((@ProjectAreaID is nullor (@ProjectAreaID = '00000000-0000-0000-0000-000000000000'))
  
SET @WhereClause = @WhereClause + 'AND
    ([ProjectAreaID] = 
''' + CAST(@ProjectAreaID as nvarchar(64)) + ''')'
if not ((@DepartmentID is nullor (@DepartmentID = '00000000-0000-0000-0000-000000000000'))
  
SET @WhereClause = @WhereClause + 'AND
    ([DepartmentID] = 
''' + CAST(@DepartmentID as nvarchar(64)) + ''')'
if not ((@ChiefID is nullor (@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 nullor (@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 nullor (@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>
                
&nbsp;|&nbsp;
                
<asp:Label ID="TaskListCompletedDateLabel" runat="server" Text='<%# Eval("CompletedDate", "{0:yyyy-MM-dd}") %>'></asp:Label>
                
&nbsp;|&nbsp;
                
<asp:Label ID="TaskListStateLabel" runat="server" Text='<%# Eval("State") %>'></asp:Label>
                
&nbsp;|&nbsp;
                
<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>
                
&nbsp;|&nbsp;
                
<asp:HyperLink ID="TaskListChiefIDLink" runat="server" NavigateUrl='<%# Eval("ChiefID", "~/ControlPanel/DepartmentManage.aspx?UserID={0}") %>' Text='<%Eval("Chief"%>'>
                
</asp:HyperLink>
                
&nbsp;|&nbsp;
                
<asp:HyperLink ID="TaskListProjectIDLink" runat="server" NavigateUrl='<%# Eval("ProjectID", "~/ControlPanel/ProjectManage.aspx?ProjectID={0}") %>' Text='<%Eval("Project"%>'>
                
</asp:HyperLink>
                
&nbsp;|&nbsp;
                
<asp:HyperLink ID="TaskListProjectAreaIDLink" runat="server" NavigateUrl='<%# Eval("ProjectAreaID", "~/ControlPanel/ProjectManage.aspx?ProjectAreaID={0}") %>' Text='<%Eval("ProjectArea"%>'>
                
</asp:HyperLink>
                
<%--&nbsp;|&nbsp;
                
<asp:HyperLink ID="TaskListParentIDLink" runat="server" NavigateUrl='<%# Eval("ParentID", "~/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("Parent.TaskName") %>'>
                </asp:HyperLink>--
%>
                
&nbsp;|&nbsp;
                
<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>
                
&nbsp;|&nbsp;
                
<%= 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效率大大提高了。希望会对朋友有所帮助和借鉴
posted on 2006-08-24 20:30  Bolik  阅读(1980)  评论(0编辑  收藏  举报