将大量数据加载到数据表中的有效方法 [英] Effective way to load huge data into datatable

问题描述

您好我正在将DBF文件加载到c＃console应用程序中的现有sql表中。首先，我的dbf文件有8,50,000 +条记录我已将整个DBF文件加载到datatable中，这会在处理400000条记录时导致内存不足异常。所以我一次读取一条记录并作为一行添加到数据表中。它工作得很好，直到6,00,000多条记录。在此记录之后，我面临System.OutofMemoryException。请任何人帮我将DBF文件加载到c＃console应用程序的sql表中。我的代码在这里，我已经尝试了很多代码优化但没有用

  public   void  ConvertDBFtoSQL（）{
  string  connStr =  @  Provider = VFPOLEDB.1; Data Source = dbf file path; Persist Security Info = False; User ID = Admin; Password =;; 
 使用（OleDbConnection oleDbConnection =  new  OleDbConnection（connStr））
 {
 oleDbConnection.Open（）; 
  string  query =   select * from dbftable ; 
 OleDbCommand oleDbCommand =  new  OleDbCommand（query，oleDbConnection）; 
 oleDbCommand.CommandTimeout =  600000 ; 
 OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader（）; 
  int  fieldCount = oleDbDataReader.FieldCount; 
 DataTable dataTable = CreateDataTable（）; 
  string  [] rows =  new   string < /跨度> [段计数]; 
  while （oleDbDataReader.Read（））
 {
 使用 （dataTable）
 {
 Array.Clear（rows， 0 ，rows.Length）; 
  for （ int  i =  0 ; i <  fieldCount; i ++）
 {
  try  
 {
 rows [i] = oleDbDataReader.GetFieldType（i）.Name.ToLower（）==  日期时间？ 
   \ + Convert.ToDateTime（oleDbDataReader.GetValue（i））。 ToString（  dd / MM / yyyy）。子串（ 0 ， 10 ）+   \\ \\：
 
（oleDbDataReader.GetValue（i）.ToString（）。包含（ ，）？ ：  \ + oleDbDataReader.GetValue（i）.ToString（）。Trim（）+   \）; 
} 
  catch （Exception ex）{} 
} 
  string  row =  string  .Join（ ， ，行）; 
 dataTable.Rows.Add（ new   object  [] {row}）; 
} 
} 
} 
} 

我尝试了什么：



首先我尝试了dataTable.Load（oledbcommand.executereader（））它因内存不足而失败然后我尝试读取一行添加到数据表中。

解决方案

阅读以下内容： foxpro - 如何在SQL Server中导入DBF文件 - Stack Overflow [ ^ ]

您可以使用数据读取器使用SqlBulkCopy：

  const   string  sourceConnectionString =   abc; 
  const   string  targetConnectionString =   xyz; 
 
 使用（ var  sourceConnection =  new  OleDbConnection（sourceConnectionString））
 使用（ var  targetConnection =  new  SqlConnection（targetConnectionString））
 使用（ var  sourceCommand =  new  OleDbCommand（  select * from dbftable，sourceConnection））
 {
 sourceConnection.Open（）; 
 targetConnection.Open（）; 
 
 使用（ var  reader = sourceCommand.ExecuteReader（））
 使用（ var  bulkCopy =  new  SqlBulkCopy （targetConnection））
 {
 bulkCopy.DestinationTableName =   aTableName; 
  bulkCopy.BatchSize =  1000 ;  
 bulkCopy.WriteToServer（reader）; 
} 
} 

看看这个：

VFP：将VFP数据导入SQL Server [ ^ ]

如何将Visual Foxpro数据库转换为SQL Server数据库 - Stack Overflow [ ^ ]

将DBF文件导入SQL Server [ ^ ]

Hi I am loading DBF file into existing sql table in c# console application. First of all my dbf file having 8 ,50,000+ records i had load the whole DBF file into datatable, It's lead to out of memory exception when processing 400000+ records. So i have read a record at a time and add into the datatable as a row. It's worked fine untill 6,00,000+ records. After this record i am facing System.OutofMemoryException. Please anyone help me to load DBF file into sql table in c# console application. My code is here, I have tried lots of code optimizations but no use

 public void ConvertDBFtoSQL() {
     string connStr = @"Provider=VFPOLEDB.1;Data Source=dbf file path;Persist              Security Info=False; User ID=Admin;Password=;";
     using (OleDbConnection oleDbConnection = new OleDbConnection(connStr))
     {
         oleDbConnection.Open();
         string query = "select * from dbftable";
         OleDbCommand oleDbCommand = new OleDbCommand(query, oleDbConnection);
         oleDbCommand.CommandTimeout = 600000;
         OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();
         int fieldCount = oleDbDataReader.FieldCount;
         DataTable dataTable= CreateDataTable();
         string[] rows = new string[fieldCount];
         while (oleDbDataReader.Read())
         {                     
             using (dataTable)
             {
                 Array.Clear(rows, 0, rows.Length);
                 for (int i = 0; i < fieldCount; i++)
                 {
                     try
                     {
      rows[i] = oleDbDataReader.GetFieldType(i).Name.ToLower() == "datetime"?
"\"" + Convert.ToDateTime(oleDbDataReader.GetValue(i)).ToString("dd/MM/yyyy").Substring(0, 10) + "\"":
                         
                             (oleDbDataReader.GetValue(i).ToString().Contains(",") ? "" :"\"" + oleDbDataReader.GetValue(i).ToString().Trim() + "\"");                                 
                     }
                     catch (Exception ex) { }
                 }
                 string row = string.Join(",", rows);
                 dataTable.Rows.Add(new object[] { row });
             }
         }
     }
}

What I have tried:

First i tried dataTable.Load(oledbcommand.executereader()) it's fail with out of memory exception then i have tried with reading a row and add into a datatable.

解决方案

Read the following : foxpro - How to import a DBF file in SQL Server - Stack Overflow[^]

You can use data reader with SqlBulkCopy:

const string sourceConnectionString = "abc";
const string targetConnectionString = "xyz";

using (var sourceConnection = new OleDbConnection(sourceConnectionString))
using (var targetConnection = new SqlConnection(targetConnectionString))
using (var sourceCommand = new OleDbCommand("select * from dbftable", sourceConnection))
{
	sourceConnection.Open();
	targetConnection.Open();
	
	using (var reader = sourceCommand.ExecuteReader())
	using (var bulkCopy = new SqlBulkCopy(targetConnection))
	{
		bulkCopy.DestinationTableName = "aTableName";
		bulkCopy.BatchSize = 1000;
		bulkCopy.WriteToServer(reader);
	}
}

Have a look at this:
VFP: Importing VFP data into SQL Server[^]
How to convert Visual Foxpro database into SQL Server database - Stack Overflow[^]
Importing DBF files into SQL Server[^]

这篇关于将大量数据加载到数据表中的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！