C# 将数据集保存到 CSV 文件的最佳方法

Best way to save DataSet to a CSV file

提问by user500741

I have some SQL queries that I need to write out the results to a CSV file.

我有一些 SQL 查询需要将结果写入 CSV 文件。

I'm currently storing the results in a DataSet.

我目前将结果存储在 DataSet 中。

What is the best way to to save each table (using a user-defined filename) as its own CSV file?

将每个表（使用用户定义的文件名）保存为自己的 CSV 文件的最佳方法是什么？

I'm using C#.

我正在使用 C#。

回答by Jason Meckley

I use http://www.filehelpers.comanytime I need to export to CSV.

我在需要导出到 CSV 的任何时候都使用http://www.filehelpers.com。

回答by Joachim Isaksson

SQL Server can export to csv on its own, have a look at

SQL Server 可以自己导出到 csv，看看

http://sqlserverpedia.com/blog/sql-server-2005/how-can-i-export-data-to-csv-format-in-sql-server-2005/

http://sqlserverpedia.com/blog/sql-server-2005/how-can-i-export-data-to-csv-format-in-sql-server-2005/

Edit: This solution isn't very tolerant of characters that need escaping in a csv, took a couple of minutes to write a breaking test. In other words, use with caution.

编辑：这个解决方案对需要在 csv 中转义的字符不太宽容，花了几分钟来编写一个破坏性测试。换句话说，请谨慎使用。

回答by YashG99

You can convert a dataset to a CSV file as follows:

您可以将数据集转换为 CSV 文件，如下所示：

Code snippet:

代码片段：

StringBuilder str = new StringBuilder();
foreach (DataRow dr in this.NorthwindDataSet.Customers) 
{
 foreach (object field in dr.ItemArray) 
 {
   str.Append(field.ToString + ",");
 }
 str.Replace(",", vbNewLine, str.Length - 1, 1);
}

try 
{
 My.Computer.FileSystem.WriteAllText("C:\temp\testcsv.csv", str.ToString, false);
} 
catch (Exception ex) 
{
 MessageBox.Show("Write Error");
}

Hope this helps! Cheers!

希望这可以帮助！干杯!

回答by user797717

You can loop through the DataSet (Tables, Rows, Fields)

您可以遍历数据集（表、行、字段）

C# Example:

C# 示例：

        StringBuilder str = new StringBuilder();
        foreach (DataTable dt in tempDataDS.Tables)
        {
            foreach (DataRow item in dt.Rows)
            {
                foreach (object field in item.ItemArray)
                {
                    str.Append(field.ToString() + ",");
                }
                str.Replace(",", Environment.NewLine, str.Length - 1, 1);
            }
        }
        try
        {
            File.AppendAllText("C:\temp\tempData.csv", str.ToString(), Encoding.UTF8);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error while writing content to csv file. \nException details: {0}", ex.ToString());
        }