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();
string ColumnValue; if (dr["ColumnName"] != null) ColumnValue = dr["ColumnName"].
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.
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.
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.
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())
....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With