Tip: export to CSV using ADO.NET

A friend of mine was telling me about a bug he found in one of his applications caused by a simple lack of escaping quotes when producing CSV files. It immediately reminded me of an old trick in .NET.

If you really want, you can create CSV files using ADO.NET and OLE-DB (or ODBC.) I wouldn't necessarily recommend this approach but it is definitely one of those things that when you see for the first time you go "I never thought this could be done this way."

The idea is simple — open an OLE-DB connection using the Jet OLE-DB provider, create a table (which is really just a file) and insert rows in that table (or lines in that file.)

//create a temp directory for the CSV output
string tempFile = Path.GetTempFileName();
File.Delete(tempFile);
tempFile = Path.GetFileNameWithoutExtension(tempFile);
string dir = Path.Combine(Path.GetTempPath(),  tempFile );
Directory.CreateDirectory(dir);
string csvFile =  Path.Combine(dir, "data.csv");

string cnStr = 
    "Provider=Microsoft.Jet.OLEDB.4.0;" + 
    "Extended Properties='text;HDR=Yes;FMT=Delimited';" + 
    "Data Source=" + dir + ";";

using (var cn = new OleDbConnection(cnStr))
{
    cn.Open();

    //define the file layout (a.k.a. the table)
    var cmd = new OleDbCommand(
        "CREATE TABLE data.csv (CharColumn VARCHAR(30), IntColumn INT)", cn);
    cmd.ExecuteNonQuery();

    //start pumping data
    cmd = new OleDbCommand(
        "INSERT INTO data.csv (CharColumn, IntColumn) VALUES (?, ?)", cn);

    //in a more realistic example this part
    // would be inside some type of loop

    //1st record
    cmd.Parameters.AddWithValue("?", "11111111");
    cmd.Parameters.AddWithValue("?", 1234);
    cmd.ExecuteNonQuery();

    //2nd record
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("?", "22222\"22222");
    cmd.Parameters.AddWithValue("?", 6789);
    cmd.ExecuteNonQuery();

    //etc...
}

//read the csv formatted data
string csv = File.ReadAllText(csvFile);

//cleanup
Directory.Delete(dir, true);

Console.WriteLine("Result:");
Console.WriteLine("--------------------");
Console.WriteLine(csv);
Console.ReadLine();

The output of this will be as follows. Note the escaped double quote in the last line.

"CharColumn","IntColumn"
"11111111",1234
"22222""22222",6789

Of course, you can also read from a CSV file with simple SELECT statements against the file. You can let ADO.NET take care of all your typical CSV tasks.

Fabrice Marguerie wrote about this same topic a long time ago (check the comments and links too).

You may also want to check a more structured approach to the Export to CSV problem by way of the FileHelpers Library.