C# SQLServer retrieving results and place in a .csv format

Question

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.

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!

I would really appreciate any tips / points on how to go about this please!

Answer 1

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).

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();
}

Answer 2

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);
        }
     }