C # SQLServer to get results and post in .csv format

Question

I looked at the site and on Google, but I could not find a suitable solution for what I was trying to do.

Basically, I have a client server application (C #) where I send the server SQL select statements (Connect to SQL Server 2008) and would like to return the results in a CSV image 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 # to SQL. I need tips on how to simply put the results in a string in csv format. Columns and fields can be different, so I can not use the method of something [something], as I saw on several sites. I'm not sure how clear tbh is!

I would really appreciate some tips / advice on how to do this, please!

Answer 1

Here is the method that I use to output any IDataReader to StreamWriter. I usually create a StreamSwriter as follows: new StreamWriter(Response.OutputStream) . I convert any double quote characters into input into single quote characters (maybe this is 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

Perhaps you can adapt the implementation of the CSV creator available here .

If you also need to parse the CSV files, implementation

Answer 3

You can get the table column names as follows:

 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 define column names with only the first row, here it does it on every row.

Now you can easily add your own code to the columns in the CSV row ...

thanks

Answer 4

As already mentioned, there are quite a few problems with delimiters, proper escaping of characters, and proper formatting of different types. But if you're just looking for an example of entering data into a string, here is another one. He does not check for the above 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); } } 

Answer 5

When working with a CSV file, I usually use FileHelpers : it has SqlServerStorage , which you can use to read records from the SQL server and write them to a CSV file.