SP 分页
1
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4

5

6

7
ALTER PROCEDURE [dbo].[GetPageCount]8
@tblName varchar(255), -- table name9
@fidlelist varchar(2000), -- fields10
@fldName varchar(255), -- sort name11
@PageSize int, -- page count12
@PageIndex int, -- page index13
@IsReCount bit, --total Recount if 0 do not return14
@OrderType bit, -- order type if o do not order15
@strWhere varchar(1000) --Search where (NOTICE: DO NOT HAVE "where")16
AS17
declare @strSQL varchar(6000) 18
declare @strTmp varchar(100),@tmpwhere varchar(200) 19
declare @strOrder varchar(400) 20

21
if @OrderType != 022
begin23
set @strTmp = '<=(select min'24
set @strOrder = ' order by [' + @fldName +'] desc'25
end26
else27
begin28
set @strTmp = '>=(select max'29
set @strOrder = ' order by [' + @fldName +'] asc'30
end31
set @tmpwhere='';32
if(@strWhere!='')33
begin34
set @tmpwhere=' where '+@strWhere;35
end36
if @PageIndex = 137
begin38
set @strSQL = 'select top ' + str(@PageSize) +' '+@fidlelist+' '+'from ['39
+ @tblName + '] ' + @tmpwhere + ' ' + @strOrder40
end41
else42
begin43

44
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fidlelist+' '+'from ['45
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['46
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['47
+ @fldName + '] from [' + @tblName + '] ' + @tmpwhere + ' '48
+ @strOrder + ') as tblTmp) ' + @tmpwhere + ' ' + @strOrder49
end50
exec(@strSQL)51
print @strSQL52
if @IsReCount != 053
begin54
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+ @tmpwhere55
exec (@strSQL)56
end1

/**//// <summary>2
/// bind data3
/// </summary>4
/// <param name="tblName">table name</param>5
/// <param name="fidlelist">fields name</param>6
/// <param name="fldName">sort name</param>7
/// <param name="PageSize">page count</param>8
/// <param name="PageIndex">page index</param>9
/// <param name="IsReCount">return total Recount if false do not return</param>10
/// <param name="OrderType"> order type if ture desc</param>11
/// <param name="strWhere">Search where (NOTICE: DO NOT HAVE "where")</param>12
private void BindGridViewContact(string tblName,string fidlelist,string fldName,int PageSize,int PageIndex,bool IsReCount,bool OrderType,string strWhere)13

{14
//this.GridViewContacts.Sort("contactNumber", SortDirection.Descending);15
16
SqlConnection cn = new SqlConnection(WebConfigurationManager.AppSettings["SQLServer"]);17
string strSQL = string.Empty;18
try19

{20
cn.Open();21
SqlCommand cmd = new SqlCommand("GetPageCount", cn);22
cmd.CommandType = CommandType.StoredProcedure;23
cmd.Connection = cn;24

25
SqlParameter param = new SqlParameter("@tblName", SqlDbType.VarChar, 200);26
param.Value = tblName;27
param.Direction = ParameterDirection.Input;28
cmd.Parameters.Add(param);29

30
SqlParameter paramfields = new SqlParameter("@fidlelist", SqlDbType.VarChar, 2000);31
paramfields.Value = fidlelist;32
paramfields.Direction = ParameterDirection.Input;33
cmd.Parameters.Add(paramfields);34

35
SqlParameter paramSortName = new SqlParameter("@fldName", SqlDbType.VarChar, 255);36
paramSortName.Value = fldName;37
paramSortName.Direction = ParameterDirection.Input;38
cmd.Parameters.Add(paramSortName);39

40

41
SqlParameter paramPageSize = new SqlParameter("@PageSize", SqlDbType.Int);42
paramPageSize.Value = PageSize;43
paramPageSize.Direction = ParameterDirection.Input;44
cmd.Parameters.Add(paramPageSize);45

46

47

48
SqlParameter paramPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);49
paramPageIndex.Value = PageIndex;50
paramPageIndex.Direction = ParameterDirection.Input;51
cmd.Parameters.Add(paramPageIndex);52

53

54

55
SqlParameter paramIsReCount = new SqlParameter("@IsReCount", SqlDbType.Bit);56
paramIsReCount.Value = IsReCount;57
paramIsReCount.Direction = ParameterDirection.Input;58
cmd.Parameters.Add(paramIsReCount);59

60

61
SqlParameter paramOrderType = new SqlParameter("@OrderType", SqlDbType.Bit);62
paramOrderType.Value = OrderType;63
paramOrderType.Direction = ParameterDirection.Input;64
cmd.Parameters.Add(paramOrderType);65

66

67
SqlParameter paramstrWhere = new SqlParameter("@strWhere", SqlDbType.VarChar,1000);68
paramstrWhere.Value = strWhere;69
paramstrWhere.Direction = ParameterDirection.Input;70
cmd.Parameters.Add(paramstrWhere);71

72

73
SqlDataAdapter da = new SqlDataAdapter(cmd);74
DataSet ds = new DataSet();75
da.Fill(ds, "contact");76
77
GridViewContacts.DataSource = ds;78
GridViewContacts.DataBind();79
if (ds.Tables.Count > 1)80

{81
PageCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);82
} 83

84
}85
catch (Exception exc)86

{87
LabelMessage.Text = exc.Message;88
89
}90
finally91

{92
cn.Close();93
}94
}95

浙公网安备 33010602011771号