SQL Table to CSV using LINQ

Question

I'm trying to get a table into a comma delimited CSV file. I am currently achieving it like this:

var csv = new StringBuilder("id,Name,Address,City,State\n");
var delimiter = ",";
var newline = "\n";

using (var db = MyDatabase())
{
     var query = from c in db.Customers select c;
     foreach(var q in query)
         csv.Append(q.id).Append(delimiter)
             .Append(q.name).Append(delimiter)
             .Append(q.address).Append(delimiter)
             .Append(q.city).Append(delimiter)
             .Append(q.state).Append(newline);
}

This creates a csv fine. But my question is, is there a more streamlined way I could achieve this without individually specifying each column? In some cases, I'd like to do a select all, but with the tables having lots of columns, it seems a bit clunky.

What happens to your CSV file when it hits an address like 123 Main St., Suite 200? There is more to making a CSV file than appending some raw data and commas. Don't reinvent the wheel. There are numerous CSV classes for .Net out there. — hatchet - done with SOverflow, Jul 09 '12 at 05:51
For example, CsvHelper and FileHelpers. See http://stackoverflow.com/questions/1941392/are-there-any-csv-readers-writer-libraries-in-c — Arithmomaniac, Jul 09 '12 at 05:53
Good point, but in this case, all i'm doing is automating the old manual way it was done with an sql script, so there are no commas in the fields. Any examples of these 'numerous' csv classes? thanks. — mnsr, Jul 09 '12 at 05:54
Here's a helper class I wrote. It might prove helpful for your tasks. [Reading and Writing CSV Files in C#](http://www.blackbeltcoder.com/Articles/files/reading-and-writing-csv-files-in-c). — Jonathan Wood, Jul 09 '12 at 05:59

score 3 · Accepted Answer · answered Jul 09 '12 at 05:56

In order to do this without specifying each property you want to write to the CSV, you would need use reflection. Here is a basic example of how this can be done:

List<Customer> query = from c in db.Customers select c;
        foreach (var q in query)
        {
            Type type = typeof(Customer);
            PropertyInfo[] properties = type.GetProperties();
            foreach (PropertyInfo property in properties)
            {
                string value = property.GetValue(type, null) != null
                    ? property.GetValue(type, null).ToString() 
                    : string.Empty; // or whatever you want the default text to be
                csv.Append(q.id).Append(delimiter);
            }
        }

This will loop through each Customer from the query, and then loop through each property in Customer and write the value. This is assuming that you want each property from Customer to be written to the CSV file. This should at least give you ideas about how to do what you want to accomplish.

you can use the .net csv helper classes, but still use the functionality of reflection — Chris Knight, Jul 09 '12 at 05:58
Umm.. a link would help- I get filehepers but I don't wnat to use that. I need to load csv into a linq2sql datacontext without making a new class, again. with 300 properties — Piotr Kula, Oct 10 '12 at 09:57

Julien Ch. · Answer 2 · 2012-07-09T06:21:54.720

The shortest path to what you wan to achieve could be :

var props = typeof(Customer).GetProperties(
          BindingFlags.Instance|BindingFlags.Public
      );

StringBuilder result = new StringBuilder(
         (
             from prop in props select prop.Name
         ).Join(',') + "\n"
     );

Function<string,Customer> project = ( 
   Customer c => (
           from prop in props 
           select prop.GetValue(c).ToString()
       ).Join(",")+"\n";
)

using (var db = MyDatabase())
{
    var query = from c in db.Customers select project(c);
    foreach (var res in query) {
        result.append(res);
    }
}

You can easily make it more generic, but then you will need to cache the reflected PropertyInfos to avoid massive use of reflection on the types.

Warning: you should thing of escaping the properties values properly because newlines, comas, quotes, etc. can break your CSV file. Even better, use a library that already address the issue of writing a correct CSV file.

score 1 · Answer 3 · answered Apr 15 '13 at 04:32

I use this code:

string delimiter = "|";
string newLine = Environment.NewLine;

using (mCustDataContext = new CustDataContext()) {
    var props = typeof(Customer).GetProperties();

    StringBuilder headers = new StringBuilder(string.Join(delimiter, props.Select(c => c.Name).ToArray()) + newLine);

    Func<Customer, string> myFunc = c => 
         string.Join(delimiter, props.Select(b => b.GetValue(c,null))) + newLine;

    var query = mCustDataContext.Customers.Select(myFunc);

    foreach(var q in query)
        headers.Append(q);

    File.WriteAllText(yourFileName, headers.ToString(), mEncoding);
}

SQL Table to CSV using LINQ

3 Answers3