如何将大型数据表拆分为多个在VS2005上工作的数据表 [英] How to split a large datatable into multiple datatables working on VS2005
问题描述
我创建了一个上传文本文件的模块，然后我突然遇到内存不足的错误。我意识到我达到了要上传的文件的最大大小。
现在我在我的模块上创建一个条件，如果rows.count大于100,000，它将会循环我的条件。
我将rows.count分为30000因为我假设我上传的最大文件将有150,000个计数。
c 应该是整数，因此如果 c 不是一个整体我总是加+ 1的原因数字。
我在下面的解决方案中更新了我的代码库，但仍然出错。我知道代码中缺少的东西。
感谢您的帮助。
什么我试过了：
I create a module which upload text file, then suddenly I encounter error of out of memory. I realize that I reach the maximum size of file to be uploaded.
Now I create a condition on my module that if the rows.count is greater than 100,000 it will loop on my condition.
I divided rows.count to 30000 cause I assume that the largest file that I will upload will have 150,000 count.
c should be whole number so that the reason I always put + 1 if c is not a whole number.
I updated my code base on the solution below, but still got error. I know something missing in my code.
Thank you for the help.
What I have tried:
If (dt.Rows.Count > 100000) Then
Dim Columns As DataRow = dt.NewRow
Dim c = dt.Rows.Count / 30000
Dim SNumber = Convert.ToInt64(System.Math.Ceiling(c))
dt.Columns.Add("Key_Id", GetType(String))
If c = CDbl(c) Then
c = System.Math.Floor(c + 1)
Dim dtArr(c) As DataTable
For j As Integer = 0 To c
dtArr(j) = dt.Select("Key_Id <= 1 And Key_Id >= 30000").Clone()
dtArr(j) = dt.Select("Key_Id <= 30001 And Key_Id >= 60000").Clone()
dtArr(j) = dt.Select("Key_Id <= 60001 And Key_Id >= 90000").Clone()
dtArr(j) = dt.Select("Key_Id <= 90001 And Key_Id >= 120000").Clone()
dtArr(j) = dt.Select("Key_Id <= 120001 And Key_Id >= 150000").Clone()
Next
End If
End If
推荐答案
通过这个链接 -
c＃ - 将数据表拆分为多个固定大小的表 [ ^ ]
好像，你的逻辑错了......拿看看你的代码，尤其是下划线部分：
Seems, your logic is wrong... Take a look at your code, especially at underlined parts:
dtArr() = dt.Select("key_id <= 1 And key_id >= 30000").Clone()
dtArr() = dt.Select("key_id <= 31000 And key_id >= 60000").Clone()
dtArr() = dt.Select("key_id <= 61000 And key_id >= 90000").Clone()
dtArr() = dt.Select("key_id <= 91000 And key_id >= 120000").Clone()
dtArr() = dt.Select("key_id <= 121000 And key_id >= 150000").Clone()
您混合小于和大于运算符！另一个问题是你在每个选择之间丢失1000条记录（31000-3000 = 1000）！
如果你想将数据拆分成30000条记录的部分，可以使用Linq。看看例子......
方法＃1 - 按 key_id复制数据：
You mixed less than and greater than operators! Another issue is that you lose 1000 records (31000-3000=1000) between each select!
If you would like to split data into parts with 30000 records, you can use Linq. Take a look at example...
Method #1 - copying data by key_id:
//source table
DataTable src = new DataTable();
src.Columns.Add(new DataColumn("key_id", typeof(int)));
//add example data
src = Enumerable.Range(1,100).Select(x=>src.LoadDataRow(new object[]{x}, false)).CopyToDataTable();
int increasement = 30;
int curVal = 1;
//temporary object type of datarow
DataRow[] result = null;
//destination dataset, here we'll store smaller datatables
DataSet ds = new DataSet();
do
{
result = src.AsEnumerable()
.Where(x=>x.Field<int>("key_id")>=curVal && x.Field<int>("key_id")<curVal+increasement)
.ToArray();
if(result.Length>0)
{
DataTable tmp = src.Clone();
tmp = result.CopyToDataTable();
ds.Tables.Add(tmp);
}
curVal+=increasement;
} while(result.Length>0);
Dim src As DataTable = New DataTable()
src.Columns.Add(New DataColumn("key_id", Type.GetType("System.Int32")))
'add example data
src = Enumerable.Range(1,100).Select(Function(x) src.LoadDataRow(New Object(){x}, False)).CopyToDataTable()
Dim increasement As Integer = 30
Dim curVal As Integer = 1
Dim result As DataRow()
Dim ds As DataSet = New DataSet()
Do
result = src.AsEnumerable() _
.Where(Function(x) x.Field(Of Integer)("key_id")>=curVal And x.Field(Of Integer)("key_id")<curVal+increasement) _
.ToArray()
If result.Length>0 Then
Dim tmp As DataTable = src.Clone()
tmp = result.CopyToDataTable()
ds.Tables.Add(tmp)
End If
curVal+=increasement
Loop While(result.Length>0)
方法＃2 - 复制相同数量的行：
Method #2 - copying the same number of rows:
int increasement = 30;
int curVal = 0; //start with 0!
DataRow[] result = null;
DataSet ds = new DataSet();
do
{
result = src.AsEnumerable()
.Skip(curVal) //
.Take(increasement)
.ToArray();
if(result.Length>0)
{
DataTable tmp = src.Clone();
tmp = result.CopyToDataTable();
ds.Tables.Add(tmp);
}
curVal+=increasement;
} while(result.Length>0);
Dim increasement As Integer = 30
Dim curVal As Integer = 0
Dim result As DataRow()= Nothing
Dim ds As DataSet = New DataSet()
Do
result = src.AsEnumerable() _
.Skip(curVal) _
.Take(increasement) _
.ToArray()
If result.Length>0 Then
Dim tmp As DataTable = src.Clone()
tmp = result.CopyToDataTable()
ds.Tables.Add(tmp)
End If
curVal+=increasement
Loop While(result.Length>0)
非Linq解决方案 - 方法＃1：
Non-Linq solution - method #1:
'needed:
'Imports System.Data
'Imports System.Text
Dim src As DataTable = New DataTable()
src.Columns.Add(New DataColumn("key_id", Type.GetType("System.Int32")))
'add example data
For i As Integer = 0 To 100
src.Rows.Add(New Object() {i})
Next
Dim increasement As Integer = 30
Dim curVal As Integer = 1
Dim result As DataRow()
Dim ds As DataSet = New DataSet()
Do
result = src.Select(String.Format("key_id >={0} And key_id<{1}", curVal, curVal + increasement)).Clone()
If result.Length > 0 Then
Dim tmp As DataTable = src.Clone()
Dim sb As StringBuilder = New StringBuilder()
For Each dr As DataRow In result
tmp.Rows.Add(New Object() {dr("key_id")})
sb.Append(String.Format("{0};", dr("key_id")))
Next
ds.Tables.Add(tmp)
Console.WriteLine("{0}", sb.ToString())
sb = Nothing
End If
curVal += increasement
Loop While (result.Length > 0)
Console.ReadKey()
结果（4个表）：
Result (4 tables):
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;
31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;
61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;
91;92;93;94;95;96;97;98;99;100;
[结束编辑]
祝你好运！
[END EDIT]
Good luck!
这篇关于如何将大型数据表拆分为多个在VS2005上工作的数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
