Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DataReader to .CSV with column names

Tags:

c#

sql-server

I'm generating a csv file from an SqlDataReader, however it is not writing the column names, how can I make it write them? The code I'm using is as follows:

SqlConnection conn = new SqlConnection(myconn);
SqlCommand cmd = new SqlCommand("dbo.test", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();

StringBuilder sb = new StringBuilder();
StreamWriter sw = new StreamWriter(myfilePath + "testfile.csv"); 
while (reader.Read())
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
        string value = reader[i].ToString();
        if (value.Contains(","))
            value = "\"" + value + "\"";

        sb.Append(value.Replace(Environment.NewLine, " ") + ",");
    }
    sb.Length--; // Remove the last comma
    sb.AppendLine();
}
conn.Close();
sw.Write(sb.ToString());
sw.Close();
like image 419
User0 Avatar asked Apr 10 '15 12:04

User0


People also ask

How do I check if a DataReader has a column?

string ColumnValue; if (dr["ColumnName"] != null) ColumnValue = dr["ColumnName"].

What is ExecuteReader ()?

ExecuteReader() Sends the CommandText to the Connection and builds a SqlDataReader. ExecuteReader(CommandBehavior) Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.

How do I get data from ExecuteReader?

To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command. ExecuteReader to retrieve rows from a data source.

What does SqlDataReader return?

As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available.


1 Answers

Read all the column names and append it to sb then iterate reader.

SqlDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();

//Get All column 
var columnNames = Enumerable.Range(0, reader.FieldCount)
                        .Select(reader.GetName) //OR .Select("\""+  reader.GetName"\"") 
                        .ToList();

//Create headers
sb.Append(string.Join(",", columnNames));

//Append Line
sb.AppendLine();

while (reader.Read())
....
like image 161
Satpal Avatar answered Sep 24 '22 03:09

Satpal