更改列名而不是从模式更改

本文关键字：模式 | 更新日期: 2023-09-27 17:54:37

我使用下面的代码，我发现创建一个csv，而它的工作原理，我想命名的列名会有人有任何想法，我将如何去。

public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
    {
        using (var command = new SqlCommand("select * from " + tableName, connection))
        using (var reader = command.ExecuteReader())
        using (var outFile = File.CreateText(destinationFile))
        {
            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"];
        }
    }

我想我可以在这里写一个if语句并对它们进行转置。但我想知道是否有人有更好的主意。

更改列名而不是从模式更改

我建议提取方法首先为纯字符串到csv，其次为sql查询:

private static String ToCsv(String value) {
  if (String.IsNullOrEmpty(value))
    return "";
  StringBuilder sb = new StringBuilder(value.Length);
  bool hasSpecial = false; 
  foreach (var ch in value) {
    if ((ch == ',') || (ch == '''') || (ch == '"')) {
      hasSpecial = true;
      sb.Append(''''); 
    }
    sb.Append(ch);
  }
  if (hasSpecial) {
    sb.Insert(0, '"');
    sb.Append('"');
  } 
  return sb.ToString();
}
private static IEnumerable<String> TableToCsv(SqlConnection connection, 
                                              string tableName, 
                                              bool colummNames = true) {
  //TODO: simplest, but prone to SQL injection solution; tableName should be validated 
  String sql = String.Format("select * from {0}", tableName);
  using (var command = new SqlCommand(sql, connection))
    using (var reader = command.ExecuteReader()) {
      // Let's take columns' names from query
      if (colummNames)
        yield return String.Join(",", Enumerable
          .Range(0, reader.FieldCount)
          .Select(i => ToCsv(reader.GetName(i))));
      while (reader.Read())
        yield return String.Join(",", Enumerable
          .Range(0, reader.FieldCount)
          .Select(i => ToCsv(reader.GetFieldValue<String>(i))));
  }
}

然后使用它:

File.WriteAllLines(@"C:'MyFile.csv",
  TableToCsv(myConnection, "MyTable", true));
相关文章：