C#,GridView_DataList存储过程分页(效率高).doc

C# GridView DataList存储过程分页（效率高）2010-06-09 20:32:51 作者：佚名 来源： 浏览次数：0protected void bindlist() SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStringsNetconntiong.ConnectionString); SqlCommand command = new SqlCoprotected void bindlist() SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStringsNetconntiong.ConnectionString); SqlCommand command = new SqlCommand(); /获取数据库连接字符串,生成新的数据库连接对象. SqlCommand myCommand = new SqlCommand(Tech_SearchListzgbfwTion, myConnection); /标记数据库访问对象的命令类型为存储过程. myCommand.CommandType = CommandType.StoredProcedure; int pageindex = 0; if (Request.QueryStringselectis = 1) key = Server.HtmlDecode(Request.QueryStringkeys); else key = ; /string strWhere = ; if (AspNetPager1.CurrentPageIndex 1) pageindex = 1; else pageindex = AspNetPager1.CurrentPageIndex; SqlParameter parameters = new SqlParameter(tblname,SqlDbType.VarChar,255), new SqlParameter(strGetFields,SqlDbType.VarChar,1000), new SqlParameter(fldName,SqlDbType.VarChar,255), new SqlParameter(pagesize,SqlDbType.Int,4), new SqlParameter(pageindex,SqlDbType.Int,4), new SqlParameter(doCount,SqlDbType.Bit), new SqlParameter(ordertype,SqlDbType.Bit), new SqlParameter(strwhere,SqlDbType.VarChar,2000), new SqlParameter(key,SqlDbType.VarChar,2000); parameters0.Value = expertadd;/数据表 parameters1.Value = id,title,Company,Content,tel,date; parameters2.Value = date; parameters3.Value = AspNetPager1.PageSize;/多少条为一页 parameters4.Value = pageindex;/索引页 parameters5.Value = 0;/这里写死就行了 parameters6.Value = 1;/这里的是降序，为升序 parameters7.Value = and classid=1 and onhot=1;/查询条件组合 parameters8.Value = key;/查询条件组合 foreach (SqlParameter p in parameters) myCommand.Parameters.Add(p); SqlDataAdapter sda = new SqlDataAdapter(myCommand); DataSet ds = new DataSet(); sda.Fill(ds); /动态设置用户自定义文本内容 AspNetPager1.RecordCount = int.Parse(ds.Tables1.Rows00.ToString(); AspNetPager1.CustomInfoText = 共 + AspNetPager1.RecordCount.ToString() + 条,; AspNetPager1.CustomInfoText += 总页数 + AspNetPager1.PageCount.ToString() + 页,; AspNetPager1.CustomInfoText += 第 + AspNetPager1.CurrentPageIndex.ToString() + 页; if (Convert.ToInt32(AspNetPager1.RecordCount.ToString() = 0) Literal2.Visible = true; Panel1.Visible = false; DataList1.DataSource = ds.Tables0.DefaultView; DataList1.DataBind(); 存储过程CREATE PROCEDURE Tech_ListBtechPageTiontblName nvarchar(1000),strGetFields nvarchar(1000) = *, - 需要返回的列fldName varchar(255)=, - 排序的字段名PageSize int = 10, - 页尺寸PageIndex int = 1, - 页码doCount bit = 0, - 返回, 非0 值则返回记录总数OrderType bit = 0, - 设置排序类型, 非0 值则降序strWhere varchar(1500) = , - 查询条件(注意: 不要加where)key varchar(1000)ASdeclare strSQL varchar(5000) - 主语句declare strTmp varchar(110) - 临时变量declare strOrder varchar(400) - 排序类型if key!=beginset key= and SpName like %+key+% endif doCount != 0begin if strWhere != set strSQL = select count(*) as Total from + tblName + where 1=1 + strWhere + key else set strSQL = select count(*) as Total from + tblName + end -以上代码的意思是如果doCount传递过来的不是，就执行总数统计。以下的所有代码都是doCount为的情况：elsebegin if OrderType != 0-降序 begin set strTmp = (select max set strOrder = order by + fldName + asc end if PageIndex = 1 begin if strWhere != set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + where 1=1 + strWhere + key + + strOrder else set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + +strOrder-如果是第一页就执行以上代码，这样会加快执行速度 end else begin-以下代码赋予了strSQL以真正执行的SQL代码 set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + where + fldName + + strTmp + (+ fldName + ) from (select top + str(PageIndex-1)*PageSize) + +fldName + from + tblName + + strOrder + ) as tblTmp) + strOrder if strWhere != set strSQL = select top + str(PageSize) + +strGetFields+ from + tblName + where +fldName + + strTmp + ( + fldName + ) from (select top + str(PageIndex-1)*PageSize) + +fldName + from + tblName + where 1=1 + strWhere + key + + strOrder + ) as tblTmp) and1=1 + strWhere + key + + strOrder end i