Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
331 views
in Technique[技术] by (71.8m points)

c# - Export SQL Server Data into CSV file

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"];
    }
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...