如何使用C＃将数百万条记录存入数据表 [英] How to get millions of records into datatable using C#

问题描述

In my application i have two datatables and two stored procedures while retrieving the data from stored procedure to datatable i am getting below error An exception of type 'System.OutOfMemoryException' occurred in System.Data.dll but was not handled in user code

我尝试过：



i能够在数据库中执行我的查询和存储过程但是在从数据库检索到c＃代码时我得到的错误

What I have tried:

i am able to execute my query and stored procedure in database but while retrieving from database to c# code i am getting above error

推荐答案

< a href =https://msdn.microsoft.com/en-us/library/system.outofmemoryexception(v=vs.110).aspx> OutOfMemoryException类（系统） [ ^ ]：

OutOfMemoryException Class (System)[^]:

Quote：

当没有足够的内存来继续执行程序时抛出的异常。

The exception that is thrown when there is not enough memory to continue the execution of a program.

您正在尝试将过多信息加载到内存中。你需要重新考虑你在做什么以及你是如何做的。



更多信息：异常故障排除：System.OutOfMemoryException [ ^ ]

简单：你输的数据太多，内存不足。

解决方案同样简单：不要一次拉这么多行：或者不要拉所有数据 - 很可能是你拉了整行并且它包含了非常大的东西，所以试着看看你是什么返回，看看你是否每次都需要每一栏。



你可能还需要看看你在做什么：如果你正在处理你的数据代码，然后将其拉入块中：一次可以输入1000行，或者更改为DataReader并逐行处理它们。

或者，如果你要将它们全部显示给用户，那么删除所有源代码并开始在快餐行业工作：你正在编写一个糟糕的用户界面，用户会热情地讨厌。永远不要显示超过二十个左右的行：page'em，search'em，filter'em，但不要只是转储它们并希望最好。

Simple: you are pulling too much data, and you have run out of memory.
The solution is equally simple: don;t pull so many rows at one time: or don't pull all the data - it's likely that you are pulling the whole row and it contains something very big, so try looking at what you are returning and see if you actually need every column every time.

You may also need to look at what you are doing with it: if you are processing the data in your code, then pull it in chunks: 1000 rows at a time maybe, or change to a DataReader and process them line by line.
Or if you are displaying them all to the user then delete all your source code and go work in the fast food industry: you are writing a horrible UI that your users will hate with a passion. Never display more than twenty or so rows: page 'em, search 'em, filter 'em, but never just dump them and hope for the best.

尝试使用分页和显示信息件

请找到自定义分页概念



DataRow [] temp = dtversion.Select（RowNumber> =+ first +and RowNumber< =+ last）;

localdatatable = temp.CopyToDataTable（）;

dataGridView1.Rows.Clear（）;

if （localdatatable.Rows.Count == 0）

返回;

foreach（localdatatable.Rows中的DataRow行）

{

dataGridView1.Rows.Add（row [1] .ToString（），row [SaveComments]。ToString（），row [DeveloperName]。ToString（），row [SaveDate]。ToString（ ），row [open]。ToString（），row [RowNumber]。ToString（））;

}

lbltotrecrds.Text = dtversion.Rows。计数+找到的记录。将+ first +显示为+ last +记录;

Try using Pagination and display info in pieces
please find Custom pagination concept

DataRow[] temp = dtversion.Select("RowNumber >= " + first + " and RowNumber <= " + last);
localdatatable = temp.CopyToDataTable();
dataGridView1.Rows.Clear();
if (localdatatable.Rows.Count == 0)
return;
foreach (DataRow row in localdatatable.Rows)
{
dataGridView1.Rows.Add(row[1].ToString(), row["SaveComments"].ToString(), row["DeveloperName"].ToString(), row["SaveDate"].ToString(), row["open"].ToString(), row["RowNumber"].ToString());
}
lbltotrecrds.Text = dtversion.Rows.Count + "records found. Displaying " + first + " to " + last + " records";

这篇关于如何使用C＃将数百万条记录存入数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！