CsvWrite from CsvHelper does not export small tables

Question

I am using CsvWriter to export data from a table in SQL Server, and have two problems:

1. when the table is small, i.e. has a few rows (I tested 1 and 5 rows), CsvWriter does not export anything (empty file)

2. when the table has sufficient rows to trigger export, it truncates rows. In my test, I got 21 rows when exporting a 30-row table, and got 44 rows when exporting a 50-row table ( that the last line is incomplete in both cases).

SQL Server Code to load test data

DECLARE @cnt INT = 0;
DECLARE @rows INT = 1; /* I tested 5, 30 and 50 */

WHILE @cnt < @rows
BEGIN
  INSERT INTO csvtest(id, a, b) VALUES(NEWID(), @cnt, 'a very very1 very2 very3 very4 long random string');
  SET @cnt = @cnt + 1;
END;

C# code to export data (more or less a copy of the code at https://joshclose.github.io/CsvHelper/)

// open a database connection
SqlConnection con = new SqlConnection(cs);
con.Open();

string sqlstring = "SELECT * FROM csvTest";
SqlCommand cmd = new SqlCommand(sqlstring, con);
SqlDataReader dr = cmd.ExecuteReader();

var csv = new CsvWriter(new StreamWriter("c:\\tmp\\csvtest_1row.csv"));

while (dr.Read())
{
    for (var i = 0; i < dr.FieldCount; i++)
    {
            csv.WriteField(dr[i]);
    }
    csv.NextRecord();
}
con.Close();

My CsvHelper version is 2.15.0.2.

Two notes:

1. I debugged the code line by line and can confirm the data were read correctly from the database. Actually, I peek the CSV object, it seems that the data is there. Just somehow they were not written to the file correctly.

2. My code is supposed to be generic to export any tables.

Can anyone tell me what I did wrong?

Thanks in advance for the help.

Answer 1

You must close the StreamWriter, either explicitly or with "using {...}". Otherwise it won't flush the buffer, so the tail of your file will be lost.