将SQL Server数据导出到CSV文件

问题描述

我正在尝试将数据从sql server导出到csv文件中.我一直在寻求在线帮助和其他支持论坛的帮助，但是我不知道如何做到这一点?我已经编写了自己的代码，但是它不起作用-它只是继续加载...而失败.

I am trying to export data into a csv file from sql server. I have looked for help online and other support forums, but I can't find out how to do this? I have written my own code, but it doesn't work - it just keeps on loading... and fails.

请帮助.这是我写的代码.

Please help. Here is the code I wrote.

  SqlConnection sqlCon = new SqlConnection("REMOVED");
  string fileName = "test.csv";
  SqlCommand sqlCmd = new SqlCommand();
  sqlCmd.CommandText = "Select * from products.products";
  sqlCmd.Connection = sqlCon;
  sqlCon.Open();

    using (var CommandText = new SqlCommand("select * from products.products"))
    using (var reader = sqlCmd.ExecuteReader())
    using (var outFile = File.CreateText(fileName))
    {
        string[] columnNames = GetColumnNames(reader).ToArray();
        int numFields = columnNames.Length;
        outFile.WriteLine(string.Join(",", columnNames));
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                string[] columnValues =
                    Enumerable.Range(0, numFields)
                              .Select(i => reader.GetValue(i).ToString())
                              .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                              .ToArray();
                outFile.WriteLine(string.Join(",", columnValues));
            }
        }
    }
}
private IEnumerable<string> GetColumnNames(IDataReader reader)
{
    foreach (DataRow row in reader.GetSchemaTable().Rows)
    {
        yield return (string)row["ColumnName"];
    }
}

推荐答案

对于它的价值，如果您想要做的只是查询并将内容转储到某个地方，则看起来您正在做的工作比您多必须.复杂性可能会增加调试的难度.

For what it's worth, if all you want is to take a query and dump the contents somewhere, it looks like you're doing a bit more work than you have to. The complexity may add to the challenge in debugging.

读取查询并将输出定向到文件的一个非常简单的示例可能看起来像这样:

A really bare bones example of reading a query and directing output to a file might look like this:

        SqlConnection sqlCon = new SqlConnection("REMOVED");
        sqlCon.Open();

        SqlCommand sqlCmd = new SqlCommand(
            "Select * from products.products", sqlCon);
        SqlDataReader reader = sqlCmd.ExecuteReader();

        string fileName = "test.csv";
        StreamWriter sw = new StreamWriter(fileName);
        object[] output = new object[reader.FieldCount];

        for (int i = 0; i < reader.FieldCount; i++)
            output[i] = reader.GetName(i);

        sw.WriteLine(string.Join(",", output));

        while (reader.Read())
        {
            reader.GetValues(output);
            sw.WriteLine(string.Join(",", output));
        }

        sw.Close();
        reader.Close();
        sqlCon.Close();

虽然看起来可能不会比您列出的代码短很多，但我确实认为它更简单，而且开箱即用，调试起来也更容易.我还没有测试过，所以我不能肯定地说它可行，尽管我认为它已经很接近了.

While it may not look dramatically shorter than the code you listed, I do think it's simpler and will be easier to debug, out of the box. I haven't tested this, so I can't say for certain it works, although I would think it's pretty close.

另一件事值得一提……这两个都不是真正的CSV输出.您需要确保处理嵌入式逗号，返回字符等(如果它们出现在任何输出中).不过，这很容易做到.

Another thing worth mentioning... neither of these is true CSV output. You need to be sure you handle embedded commas, return characters, etc, should they be in any of the output. That's easy enough to do, though.

这篇关于将SQL Server数据导出到CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！