关于ADO.net的效率问题 OleDbDataReader各有千秋把,关键你要结合你的实际使用! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 注意加上:if (!Page.IsPostBack) 一是你可以作查询的优化,这是数据库层的问题。二是如果你分页的话,不用把几万条记录都fill到dataset里面吧。只要取一页的数据出来就可以。要的时候再取下一页数据。 非常感谢各位,不过回答都不说明问题,最后我自己在.net的帮助文件里找到了答案。呵呵,有兴趣的往下看:以下摘自VS.NET2003 具体的连接是ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.2052/cpguide/html/cpconpagingthroughqueryresult.htm查询结果分页是以较小数据子集(即页)的形式返回查询结果的过程。它通常用于以易于管理的小块区的形式向用户显示结果。DataAdapter 提供了通过 Fill 方法的重载来仅返回一页数据的功能。但是,对于大量的查询结果,它可能并不是首选的分页方法,因为 DataAdapter 虽然仅使用所请求的记录来填充目标 DataTable 或 DataSet,但仍会使用返回整个查询的资源。若要在从数据源中返回一页数据时不使用返回整个查询所需的资源,请为查询指定附加条件,使返回的行数减少到只返回所需的行。若要使用 Fill 方法返回一页数据,请指定 startRecord(它指定该数据页的第一个记录),并指定 maxRecords(它指定该数据页中的记录数)。以下代码示例显示如何使用 Fill 方法来返回查询结果(页大小为 5 个记录)的第一页。int currentIndex = 0;int pageSize = 5;string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();myDA.Fill(myDS, currentIndex, pageSize, "Orders");在上例中,DataSet 只填充了 5 个记录,但却返回了整个 Orders 表。若要用相同的 5 个记录填充 DataSet 但仅返回这 5 个记录,请在 SQL 语句中使用 TOP 和 WHERE 语句,如以下代码示例所示。int pageSize = 5;string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID";SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();myDA.Fill(myDS, "Orders");请注意,当以这种方式进行查询结果分页时,将需要保留用作行排序依据的唯一标识符,以便将唯一的 ID 传递给用于返回下一页记录的命令,如以下代码示例所示。string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();若要使用附带 startRecord 和 maxRecords 参数的 Fill 方法的重载来返回下一页记录,请使当前记录索引按页大小递增,并填充该表。请记住,即使仅在 DataSet 中添加一页记录,数据库服务器仍会返回全部查询结果。在以下代码示例中,先清除表行,然后再用下一页数据填充这些表行。您可能需要在本地缓存中保留一定数量的返回行,以减少到数据库服务器的往返过程。currentIndex += pageSize;myDS.Tables["Orders"].Rows.Clear();myDA.Fill(myDS, currentIndex, pageSize, "Orders");若要返回下一页记录而不让数据库服务器返回整个查询,请指定对 SQL SELECT 语句的限制条件。由于上例保留了返回的最后一个记录,因此可以在 WHERE 子句中使用它来指定查询的起点,如以下代码示例所示。orderSQL = "SELECT TOP " + pageSize + " * FROM Orders WHERE OrderID > " + lastRecord + " ORDER BY OrderID";myDA.SelectCommand.CommandText = orderSQL;myDS.Tables["Orders"].Rows.Clear();myDA.Fill(myDS, "Orders");下面是一个查询结果分页的示例,它在 SQL 语句中指定特定条件,以便一次仅从数据库中返回一页记录。using System;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Windows.Forms;public class PagingSample: Form{ // Form controls. Button prevBtn = new Button(); Button nextBtn = new Button(); static DataGrid myGrid = new DataGrid(); static Label pageLbl = new Label(); // Paging variables. static int pageSize = 10; // Size of viewed page. static int totalPages = 0; // Total pages. static int currentPage = 0; // Current page. static string firstVisibleCustomer = ""; // First customer on page to determine location for move previous. static string lastVisibleCustomer = ""; // Last customer on page to determine location for move next. // DataSet to bind to DataGrid. static DataTable custTable; // Initialize connection to database and DataAdapter. static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn); static SqlCommand selCmd = custDA.SelectCommand; public static void GetData(string direction) { // Create SQL statement to return a page of records. selCmd.Parameters.Clear(); switch (direction) { case "Next": selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " + "WHERE CustomerID > @CustomerId ORDER BY CustomerID"; selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer; break; case "Previous": selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " + "WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC"; selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer; break; default: selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID"; // Determine total pages. SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn); nwindConn.Open(); int totalRecords = (int)totCMD.ExecuteScalar(); nwindConn.Close(); totalPages = (int)Math.Ceiling((double)totalRecords / pageSize); break; } // Fill a temporary table with query results. DataTable tmpTable = new DataTable("Customers"); int recordsAffected = custDA.Fill(tmpTable); // If table does not exist, create it. if (custTable == null) custTable = tmpTable.Clone(); // Refresh table if at least one record returned. if (recordsAffected > 0) { switch (direction) { case "Next": currentPage++; break; case "Previous": currentPage--; break; default: currentPage = 1; break; } pageLbl.Text = "Page " + currentPage + " of " + totalPages; // Clear rows and add new results. custTable.Rows.Clear(); foreach (DataRow myRow in tmpTable.Rows) custTable.ImportRow(myRow); // Preserve first and last primary key values. DataRow[] ordRows = custTable.Select("", "CustomerID ASC"); firstVisibleCustomer = ordRows[0][0].ToString(); lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString(); } } public PagingSample() { // Initialize controls and add to form. this.ClientSize = new Size(360, 274); this.Text = "NorthWind Data"; myGrid.Location = new Point(10,10); myGrid.Size = new Size(340, 220); myGrid.AllowSorting = true; myGrid.CaptionText = "NorthWind Customers"; myGrid.ReadOnly = true; myGrid.AllowNavigation = false; myGrid.PreferredColumnWidth = 150; prevBtn.Text = "<<"; prevBtn.Size = new Size(48, 24); prevBtn.Location = new Point(92, 240); prevBtn.Click += new EventHandler(Prev_OnClick); nextBtn.Text = ">>"; nextBtn.Size = new Size(48, 24); nextBtn.Location = new Point(160, 240); pageLbl.Text = "No Records Returned."; pageLbl.Size = new Size(130, 16); pageLbl.Location = new Point(218, 244); this.Controls.Add(myGrid); this.Controls.Add(prevBtn); this.Controls.Add(nextBtn); this.Controls.Add(pageLbl); nextBtn.Click += new EventHandler(Next_OnClick); // Populate DataSet with first page of records and bind to grid. GetData("Default"); DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows); myGrid.SetDataBinding(custDV, ""); } public static void Prev_OnClick(object sender, EventArgs args) { GetData("Previous"); } public static void Next_OnClick(object sender, EventArgs args) { GetData("Next"); }}public class Sample{ static void Main() { Application.Run(new PagingSample()); }} asp.net登陆框数据比对,请高手解答! 常量中有换行符 帮下忙啊 我们打算用.NET2008开发,想找一个源代码管理程序,不知各位现在用的是谁家的,版本是什么? 关于运行环境url路径的问题 不小心给工具栏里面的 控件删了 asp.net2.0母版页问题 关于ajaxpro应用模板页的问题 使用HTML的alert跳出提示栏时,为什么网页背景变成成空白?按确定后重新显示页面? 怎么可以让datagrid固定显示条数,即每页必须显示多少条 如何将一个DataTable中存有的前5行信息取出来存到另一个DataTable中???????????? 只有找思归了。 如何在客户端调用类里的方法
二是如果你分页的话,不用把几万条记录都fill到dataset里面吧。只要取一页的数据出来就可以。要的时候再取下一页数据。
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.2052/cpguide/html/cpconpagingthroughqueryresult.htm查询结果分页是以较小数据子集(即页)的形式返回查询结果的过程。它通常用于以易于管理的小块区的形式向用户显示结果。DataAdapter 提供了通过 Fill 方法的重载来仅返回一页数据的功能。但是,对于大量的查询结果,它可能并不是首选的分页方法,因为 DataAdapter 虽然仅使用所请求的记录来填充目标 DataTable 或 DataSet,但仍会使用返回整个查询的资源。若要在从数据源中返回一页数据时不使用返回整个查询所需的资源,请为查询指定附加条件,使返回的行数减少到只返回所需的行。若要使用 Fill 方法返回一页数据,请指定 startRecord(它指定该数据页的第一个记录),并指定 maxRecords(它指定该数据页中的记录数)。以下代码示例显示如何使用 Fill 方法来返回查询结果(页大小为 5 个记录)的第一页。int currentIndex = 0;
int pageSize = 5;string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();
myDA.Fill(myDS, currentIndex, pageSize, "Orders");在上例中,DataSet 只填充了 5 个记录,但却返回了整个 Orders 表。若要用相同的 5 个记录填充 DataSet 但仅返回这 5 个记录,请在 SQL 语句中使用 TOP 和 WHERE 语句,如以下代码示例所示。int pageSize = 5;string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();
myDA.Fill(myDS, "Orders");请注意,当以这种方式进行查询结果分页时,将需要保留用作行排序依据的唯一标识符,以便将唯一的 ID 传递给用于返回下一页记录的命令,如以下代码示例所示。string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();若要使用附带 startRecord 和 maxRecords 参数的 Fill 方法的重载来返回下一页记录,请使当前记录索引按页大小递增,并填充该表。请记住,即使仅在 DataSet 中添加一页记录,数据库服务器仍会返回全部查询结果。在以下代码示例中,先清除表行,然后再用下一页数据填充这些表行。您可能需要在本地缓存中保留一定数量的返回行,以减少到数据库服务器的往返过程。currentIndex += pageSize;myDS.Tables["Orders"].Rows.Clear();myDA.Fill(myDS, currentIndex, pageSize, "Orders");若要返回下一页记录而不让数据库服务器返回整个查询,请指定对 SQL SELECT 语句的限制条件。由于上例保留了返回的最后一个记录,因此可以在 WHERE 子句中使用它来指定查询的起点,如以下代码示例所示。orderSQL = "SELECT TOP " + pageSize + " * FROM Orders WHERE OrderID > " + lastRecord + " ORDER BY OrderID";
myDA.SelectCommand.CommandText = orderSQL;myDS.Tables["Orders"].Rows.Clear();myDA.Fill(myDS, "Orders");下面是一个查询结果分页的示例,它在 SQL 语句中指定特定条件,以便一次仅从数据库中返回一页记录。using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;public class PagingSample: Form
{
// Form controls.
Button prevBtn = new Button();
Button nextBtn = new Button(); static DataGrid myGrid = new DataGrid();
static Label pageLbl = new Label(); // Paging variables.
static int pageSize = 10; // Size of viewed page.
static int totalPages = 0; // Total pages.
static int currentPage = 0; // Current page.
static string firstVisibleCustomer = ""; // First customer on page to determine location for move previous.
static string lastVisibleCustomer = ""; // Last customer on page to determine location for move next. // DataSet to bind to DataGrid.
static DataTable custTable; // Initialize connection to database and DataAdapter.
static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
static SqlCommand selCmd = custDA.SelectCommand; public static void GetData(string direction)
{
// Create SQL statement to return a page of records.
selCmd.Parameters.Clear(); switch (direction)
{
case "Next":
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
"WHERE CustomerID > @CustomerId ORDER BY CustomerID";
selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
break;
case "Previous":
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
"WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC";
selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
break;
default:
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
// Determine total pages.
SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn);
nwindConn.Open();
int totalRecords = (int)totCMD.ExecuteScalar();
nwindConn.Close();
totalPages = (int)Math.Ceiling((double)totalRecords / pageSize); break;
} // Fill a temporary table with query results.
DataTable tmpTable = new DataTable("Customers");
int recordsAffected = custDA.Fill(tmpTable); // If table does not exist, create it.
if (custTable == null)
custTable = tmpTable.Clone(); // Refresh table if at least one record returned.
if (recordsAffected > 0)
{
switch (direction)
{
case "Next":
currentPage++;
break;
case "Previous":
currentPage--;
break;
default:
currentPage = 1;
break;
} pageLbl.Text = "Page " + currentPage + " of " + totalPages; // Clear rows and add new results.
custTable.Rows.Clear(); foreach (DataRow myRow in tmpTable.Rows)
custTable.ImportRow(myRow); // Preserve first and last primary key values.
DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
firstVisibleCustomer = ordRows[0][0].ToString();
lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
}
} public PagingSample()
{
// Initialize controls and add to form.
this.ClientSize = new Size(360, 274);
this.Text = "NorthWind Data"; myGrid.Location = new Point(10,10);
myGrid.Size = new Size(340, 220);
myGrid.AllowSorting = true;
myGrid.CaptionText = "NorthWind Customers";
myGrid.ReadOnly = true;
myGrid.AllowNavigation = false;
myGrid.PreferredColumnWidth = 150; prevBtn.Text = "<<";
prevBtn.Size = new Size(48, 24);
prevBtn.Location = new Point(92, 240);
prevBtn.Click += new EventHandler(Prev_OnClick); nextBtn.Text = ">>";
nextBtn.Size = new Size(48, 24);
nextBtn.Location = new Point(160, 240); pageLbl.Text = "No Records Returned.";
pageLbl.Size = new Size(130, 16);
pageLbl.Location = new Point(218, 244); this.Controls.Add(myGrid);
this.Controls.Add(prevBtn);
this.Controls.Add(nextBtn);
this.Controls.Add(pageLbl);
nextBtn.Click += new EventHandler(Next_OnClick);
// Populate DataSet with first page of records and bind to grid.
GetData("Default");
DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
myGrid.SetDataBinding(custDV, "");
} public static void Prev_OnClick(object sender, EventArgs args)
{
GetData("Previous");
} public static void Next_OnClick(object sender, EventArgs args)
{
GetData("Next");
}
}public class Sample
{
static void Main()
{
Application.Run(new PagingSample());
}
}