如何使用sql查询的输出创建csv文件

本文关键字：创建 csv 文件 输出 何使用 sql 查询 | 更新日期: 2023-09-27 18:24:14

如何在C#中使用SQL（SQLServer）查询的输出创建CSV文件？

这是我迄今为止尝试过的代码：

public static void CreateManifestFile(string SQLFileToExecute, string ConnectionString, StreamWriter logfile) {
  int success = 0;
  List<string> results = new List<string>();
  string script = File.ReadAllText(@SQLFileToExecute);
  Logging.WriteToLog(" ", logfile);
  Logging.WriteToLog(" ", logfile);
  Logging.WriteToLog("=== Processing file: [" + SQLFileToExecute + "] ===", logfile);
  // split script on GO command
  IEnumerable<string> commandStrings = Regex.Split(script, @ "^'s*GO's*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
  SqlConnection Connection = new SqlConnection(ConnectionString);
  Connection.Open();
  Connection.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e) {
    Logging.WriteToLog("Msg: " + e.Message, logfile);
    success = 1;
  };
  SqlCommand sqlcmd = new SqlCommand();
  sqlcmd.Connection = Connection;
  foreach(string commandString in commandStrings) {
    if (commandString.Trim() != "") {
      success = 0;
      Console.WriteLine(commandString.ToString());
      logfile.WriteLine(commandString.ToString());
      results.Add(sqlcmd.ExecuteReader(commandString));
      if (success == 0) {
        Logging.WriteToLog("Command executed successfully.", logfile);
      }
    }
  }
  Connection.Close();
  int length = results.Count;
  string delimter = ",";
  using(System.IO.TextWriter writer = File.CreateText("manifest.csv")) {
    Logging.WriteToLog("manifest count:" + length, logfile);
    for (int index = 0; index < length; index++) {
      writer.WriteLine(string.Join(delimter, results[index]));
    }
  }
}

但我在网上遇到了错误：

results.Add(sqlcmd.ExecuteReader(commandString));

错误：

错误1的最佳重载方法匹配"System.Collections.Generic.List.Add（字符串）"包含一些无效内容自变量

错误2参数1:无法从转换"System.Data.SqlClient.SqlDataReader"到"string"

错误3的最佳重载方法匹配"System.Data.SqlClient.SqlCommand.ExecuteReader（System.Data.CommandBehavior）"有一些无效的参数

错误4参数1:无法从"string"转换为"System.Data.CommandBehavior"

我跟随这篇帖子来做这件事。

如何使用sql查询的输出创建csv文件

public string ExportToCSVFile(DataTable dtTable)
{
    StringBuilder sbldr = new StringBuilder();
    if (dtTable.Columns.Count != 0)
    {
        foreach (DataColumn col in dtTable.Columns)
        {
            sbldr.Append(col.ColumnName.Replace(",", "") + ',');
        }
        sbldr.Append("'r'n");
        foreach (DataRow row in dtTable.Rows)
        {
            foreach (DataColumn column in dtTable.Columns)
            {
                sbldr.Append(row[column].ToString().Replace(",", "").Trim() + ',');
            }
            sbldr.Append("'r'n");
        }
    }
    return sbldr.ToString();
}

你还没有尝试过任何东西，所以这里有一点动力让你开始。

SqlServerStorage storage = new SqlServerStorage(typeof(Order));
string sqlConnectionString ="Server=SqlServer;Database=SqlDataBase;Trusted_onnection=True";
storage.ConnectionString = sqlConnectionString;
storage.SelectSql = "select * from Yourtable";
storage.FillRecordCallback = new FillRecordHandler(FillRecordOrder);
FileDataLink link = new FileDataLink(storage);
link.FileHelperEngine.HeaderText = headerLine;
link.ExtractToFile("file.csv");

这是为包含大数据的表提供超时，但嘿，我没有你那么懒。所以想清楚。希望这有助于好运。

相关文章：