这几天一直在寻找着如何在能够让GridView加载的时候,速度更快点,但是没有找到。当时只是想利用这种方式:就是利用存储过程在Oracle服务器端进行分页,然后在客户端进行请求的时候,存储过程返回查询的数据集,包括总条数。这样挺好,但是麻烦点,能不能在服务器端就进行处理呢?在CodeProject上面还真的发现了一篇文章,现在让我给“拐卖”了过来,以作备忘吧,希望国外的那位老兄不要用如来神掌来招呼我:
具体的文章链接:http://www.codeproject.com/KB/aspnet/Custom_Paging_AnyDatabase.aspx
主要的操作逻辑如下:
1、执行DataReader
2、从我们规定的开始的位置执行DataReader
3、执行并得到数据记录
4、将数据记录放到DataTable数据集中
5、结束DataReader
6、返回DataTable数据集。
下面的代码就是针对上面的操作逻辑而来的,主要是“DataReaderToDataTable”方法,具体代码如下:
internal
static
DataTable DataReaderToDataTable(
string
sQuery,
int
iStart,
int
iEnd)
{
DataTable schematable = null ;
DataTable dt = null ;
SqlCommand cmdsql;
SqlDataReader dr = null ;
SqlConnection conn = null ;
long icount = 0 ;
try
{
// 打开数据库连接,执行DataReader
conn = new SqlConnection(ConnString);
conn.Open();
cmdsql = new SqlCommand(sQuery, conn);
dr = cmdsql.ExecuteReader(CommandBehavior.CloseConnection);
schematable = dr.GetSchemaTable();
dt = new DataTable();
// Get the Schema of Tables Columns and its types, and load the same into DataTable.
for ( int i = 0 ; i <= schematable.Rows.Count - 1 ; i ++ )
{
DataRow dRow = schematable.Rows[i];
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType(dRow[ " DataType " ].ToString());
column.AllowDBNull = (dRow[ " AllowDBNull " ].ToString() == " True " ? true : false );
column.ColumnName = dRow[ " ColumnName " ].ToString();
column.Caption = dRow[ " ColumnName " ].ToString();
dt.Columns.Add(column);
// More DataTable property can be added as required.
}
if (iStart == 0 ) iStart = 1 ;
if (iEnd == 0 ) iEnd = 1 ;
icount = 1 ;
// Loop the Reader which is executed till the Start and Variable,
// Fetch and add the rows one by one to Data Table Till the End Count is reached.
// Exit the loop and Return Datable.
while (dr.Read())
{
if (icount >= iStart && icount <= iEnd)
{
DataRow dRow = dt.NewRow();
for ( int i = 0 ; i <= dr.FieldCount - 1 ; i ++ )
{
dRow[i] = dr.GetValue(i);
}
dt.Rows.Add(dRow);
}
else if (icount > iEnd)
{
break ;
}
icount = icount + 1 ;
}
}
catch (SystemException ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
schematable.Dispose();
dr.Close();
dr.Dispose();
}
return dt;
}
{
DataTable schematable = null ;
DataTable dt = null ;
SqlCommand cmdsql;
SqlDataReader dr = null ;
SqlConnection conn = null ;
long icount = 0 ;
try
{
// 打开数据库连接,执行DataReader
conn = new SqlConnection(ConnString);
conn.Open();
cmdsql = new SqlCommand(sQuery, conn);
dr = cmdsql.ExecuteReader(CommandBehavior.CloseConnection);
schematable = dr.GetSchemaTable();
dt = new DataTable();
// Get the Schema of Tables Columns and its types, and load the same into DataTable.
for ( int i = 0 ; i <= schematable.Rows.Count - 1 ; i ++ )
{
DataRow dRow = schematable.Rows[i];
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType(dRow[ " DataType " ].ToString());
column.AllowDBNull = (dRow[ " AllowDBNull " ].ToString() == " True " ? true : false );
column.ColumnName = dRow[ " ColumnName " ].ToString();
column.Caption = dRow[ " ColumnName " ].ToString();
dt.Columns.Add(column);
// More DataTable property can be added as required.
}
if (iStart == 0 ) iStart = 1 ;
if (iEnd == 0 ) iEnd = 1 ;
icount = 1 ;
// Loop the Reader which is executed till the Start and Variable,
// Fetch and add the rows one by one to Data Table Till the End Count is reached.
// Exit the loop and Return Datable.
while (dr.Read())
{
if (icount >= iStart && icount <= iEnd)
{
DataRow dRow = dt.NewRow();
for ( int i = 0 ; i <= dr.FieldCount - 1 ; i ++ )
{
dRow[i] = dr.GetValue(i);
}
dt.Rows.Add(dRow);
}
else if (icount > iEnd)
{
break ;
}
icount = icount + 1 ;
}
}
catch (SystemException ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
schematable.Dispose();
dr.Close();
dr.Dispose();
}
return dt;
}
在后台绑定到GridView的方法如下:
private
void
BindData(
int
pageIndex)
{
int startRow;
int endRow;
startRow = (pageIndex * grdEmployee.PageSize) + 1 ;
endRow = startRow + grdEmployee.PageSize - 1 ;
{
int startRow;
int endRow;
startRow = (pageIndex * grdEmployee.PageSize) + 1 ;
endRow = startRow + grdEmployee.PageSize - 1 ;
grdEmployee.DataSource
=
CustomPaging.Class.Common.DataReaderToDataTable(SelectQuery,startRow,endRow);
grdEmployee.DataBind();
grdEmployee.DataBind();
}
以上就是这些方法,感觉还是比较好用的。