从Access插入数据到CSV文件的有效方法

我正在尝试将访问数据库中的数据插入C#中的.csv文件。我在Access数据库中有54744条记录。将记录插入CSV文件需要2个多小时。下面是我的代码:

 public void CreateCSVFile()
        {
            DataSet dsRecLoad = new DataSet();
            DataTable dt = new DataTable("RecsCDDt");
            StringBuilder sb = new StringBuilder();
            using (OleDbConnection connection = new OleDbConnection(_configuration.GetConnectionString("AccessConnection")))
            {
                using (OleDbCommand cmd = new OleDbCommand("select * from RecsCD", connection))
                {
                    connection.Open();
                    OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                    dt.Load(reader);

                }
            }

                foreach (DataRow row in dt.Rows)
                {
                   foreach (DataColumn dc in dt.Columns)
                    {
                        if (!String.IsNullOrEmpty(sb.ToString()))
                            sb.Append(",");
                       sb.Append(row[dc.ColumnName].ToString());

                   }

                    sb.Append("\n");
                }

                string CSVfilePath = _configuration.GetValue<string>("MySettings:CSVFile");
                if (!File.Exists(CSVfilePath))
                {

                    File.WriteAllText(CSVfilePath, sb.ToString()) ;
                }


        }

谁能建议我将记录插入.CSV文件的更有效方法。

谢谢。

评论
  • nfugit
    nfugit 回复

    这是一个小示例,说明如何通过使用读取器流式处理数据并将数据作为流写入(未调试)来实现相同的目的:

    await using var textWriter = new StreamWriter("path\\to\\file.csv");
    await using var writer = new CsvHelper.CsvWriter(textWriter, CultureInfo.InvariantCulture);
    
    await using var connection = new SqlConnection("connection_string");
    await using var cmd = new SqlCommand("select * from RecsCD", connection);
    await connection.OpenAsync();
    await using var reader = await cmd.ExecuteReaderAsync();
    
    while (await reader.ReadAsync()) {
        foreach (int i in Enumerable.Range(0, reader.FieldCount)) {
            writer.WriteField(await reader.GetFieldValueAsync<object>(i));
        }
    
        await writer.NextRecordAsync();
    }
    await writer.FlushAsync();
    

    Note that the code depends on the CsvHelper library.