C# SQLServer 检索结果并以 .csv 格式放置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1963719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# SQLServer retrieving results and place in a .csv format
提问by Erika
I had a look on the site and on Google, but I couldn't seem to find a good solution to what I'm trying to do.
我在网站和谷歌上看了看，但我似乎无法找到一个很好的解决方案来解决我想要做的事情。
Basically, I have a client server application (C#) where I send the server an SQL select statement (Connecting to SQL Server 2008) and would like to return results in a CSV manner back to the client.
基本上，我有一个客户端服务器应用程序 (C#)，我在其中向服务器发送 SQL 选择语句（连接到 SQL Server 2008），并希望以 CSV 方式将结果返回给客户端。
So far I have the following:
到目前为止，我有以下几点：
if (sqlDataReader.HasRows)
{
while(sqlDataReader.Read())
{
//not really sure what to put here and if the while should be there!
}
} `
}`
Unfortunately, I'm really new to connecting C# with SQL. I need any tips on how to simply put the results in a string in a csv format. The columns and fields are likely to be different so I cannot use the method of something[something] as I've seen in a few sites. I'm not sure if I'm being comprehensible tbh!
不幸的是，我对将 C# 与 SQL 连接起来真的很陌生。我需要有关如何简单地将结果放入 csv 格式的字符串中的任何提示。列和字段可能会有所不同，因此我无法使用我在一些站点中看到的 something[something] 方法。我不确定我是否可以理解 tbh！
I would really appreciate any tips / points on how to go about this please!
我真的很感激任何关于如何解决这个问题的提示/要点！
采纳答案by Ray
Here is a method I use to dump any IDataReader out to a StreamWriter. I generally create the StreamSwriter like this: new StreamWriter(Response.OutputStream). I convert any double-quote characters in the input into single-quote characters (maybe not the best way to handle this, but it works for me).
这是我用来将任何 IDataReader 转储到 StreamWriter 的方法。我一般创建这样的StreamSwriter： new StreamWriter(Response.OutputStream)。我将输入中的任何双引号字符转换为单引号字符（可能不是处理此问题的最佳方法，但它对我有用）。
public static void createCsvFile(IDataReader reader, StreamWriter writer) {
string Delimiter = "\"";
string Separator = ",";
// write header row
for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++) {
if (columnCounter > 0) {
writer.Write(Separator);
}
writer.Write(Delimiter + reader.GetName(columnCounter) + Delimiter);
}
writer.WriteLine(string.Empty);
// data loop
while (reader.Read()) {
// column loop
for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++) {
if (columnCounter > 0) {
writer.Write(Separator);
}
writer.Write(Delimiter + reader.GetValue(columnCounter).ToString().Replace('"', '\'') + Delimiter);
} // end of column loop
writer.WriteLine(string.Empty);
} // data loop
writer.Flush();
}
回答by LBushkin
You may be able to adapt the implementation of a CSV writer available here.
您可以调整此处提供的 CSV 编写器的实现。
If you also need to parse CSV files, the implementation hereis relatively good.
The CSV format is more complicated than it looks - particularly if you're going to deal with arbitrary data coming back from a query. You would need to be able to handle escaping of special characters (like quotes and commas), dealing with line breaks, and the like. You are better off finding and using a proven implementation - especially if you're new to C#.
CSV 格式比看起来更复杂 - 特别是如果您要处理从查询返回的任意数据。您需要能够处理特殊字符（如引号和逗号）的转义、处理换行符等。您最好找到并使用经过验证的实现 - 特别是如果您不熟悉 C#。
回答by Mike Gleason jr Couturier
You can get the table column names like this:
您可以像这样获取表列名称：
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader();
DataTable schema = rdr.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
foreach (DataColumn col in schema.Columns)
Console.WriteLine(col.ColumnName + " = " + row[col]);
}
rdr.Close()
conn.Close();
Of course you can determine the columns names with the first row only, here it does it on every rows.
当然你可以只用第一行来确定列名，这里它在每一行上都这样做。
You can now put your own code to join the columns into a CSV line pretty easily...
您现在可以很容易地将自己的代码加入到 CSV 行中...
Thanks
谢谢
回答by Mark Wilkins
As mentioned, there are quite a few issues with delimiters, escaping characters correctly, and formatting different types correctly. But if you are just looking for an example of putting data into a string, here is yet another one. It does not do any checking for the aforementioned complications.
如前所述，分隔符、正确转义字符和正确格式化不同类型存在很多问题。但是，如果您只是在寻找将数据放入字符串的示例，那么这里还有一个示例。它不会对上述并发症进行任何检查。
public static void ReaderToString( IDataReader Reader )
{
while ( Reader.Read() )
{
StringBuilder str = new StringBuilder();
for ( int i = 0; i < Reader.FieldCount; i++ )
{
if ( Reader.IsDBNull( i ) )
str.Append( "null" );
else
str.Append( Reader.GetValue( i ).ToString() );
if ( i < Reader.FieldCount - 1 )
str.Append( ", " );
}
// do something with the string here
Console.WriteLine(str);
}
}
回答by edosoft
When dealing with CSV file I usually go for the FileHelperslibrary: it has a SqlServerStorageclass which you can use to read records from a SQL server and write them to a CSV file.
在处理 CSV 文件时，我通常使用 FileHelpers库：它有一个SqlServerStorage类，您可以使用它从 SQL 服务器读取记录并将它们写入 CSV 文件。
