需要通过SQL表行进行迭代,每次一个(表过大,使用adapter.Fill) [英] Need to iterate through SQL table rows, one at a time (table too big to use adapter.Fill)
问题描述
这是很容易让我通过一个小SQL Server 2005的表像这样写着:
It's easy enough for me to read through a small SQL Server 2005 table like this:
string cmdText = "select * from myTable";
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connection);
DataTable table = new DataTable();
adapter.Fill(table);
不幸的是,这种方法似乎整个表加载到内存中,这根本是行不通的与我一起工作的巨大的表。
Unfortunately, this method appears to load the entire table into memory, which simply isn't going to work with the gigantic tables I'm working with.
我希望能够在同一时间通过表迭代一行,这样,只有一行必须在内存中一次。沿着线的东西:
I would like to be able to iterate through the table one row at a time, such that only one row needs to be in memory at once. Something along the lines of:
foreach (DataRow row in rowIteratorObject)
{
// do something using the row
// current row goes out of scope and is no longer in memory
}
的类似,您可以使用StreamReader的处理,而不是看它的所有在用一次,一次一个文本文件一行,顺便类。有谁知道的一种方式与表中的行做到这一点(或者,如果我找错了树,一种替代解决方案)?
Kind of similar to the way you can use StreamReader to deal with a text file one line at a time, instead of reading it all in at once. Does anyone know of a way to do this with table rows (or, if I'm barking up the wrong tree, an alternative solution)?
推荐答案
您应该使用DataReader:
You should use a DataReader:
using( var connection = new SqlConnection( "my connection string" ) ) {
using( var command = connection.CreateCommand() ) {
command.CommandText = "SELECT Column1, Column2, Column3 FROM myTable";
connection.Open();
using( var reader = command.ExecuteReader() ) {
var indexOfColumn1 = reader.GetOrdinal( "Column1" );
var indexOfColumn2 = reader.GetOrdinal( "Column2" );
var indexOfColumn3 = reader.GetOrdinal( "Column3" );
while( reader.Read() ) {
var value1 = reader.GetValue( indexOfColumn1 );
var value2 = reader.GetValue( indexOfColumn2 );
var value3 = reader.GetValue( indexOfColumn3 );
// now, do something what you want
}
}
connection.Close();
}
}
这篇关于需要通过SQL表行进行迭代,每次一个(表过大,使用adapter.Fill)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!