SubSonic Collection to CSV

November 14th, 2007

I had a requirement for a search page that allowed for CSV download.  My web site is built using SubSonic, so I had to recreate that age old code to turn a domain collection into a flat file export.  

First note: I wrote this in a couple of hours.  Most of the coding took 30 minutes, then about three to four hours to test and tweak.  Also, after you see the main code, I’m then going to rewrite it with .Net 3.5 in mind — using an Extension Method.

The hardest part of the project was figuring out what SubSonic object to use, then how to pass a generic reference to the method.  Because of SubSonic’s heavy use of generics, that did get interesting.  In fact, the primary method looks like this:

public string CreateCsvData<ItemType, ListType>(

           ActiveList<ItemType, ListType> list)

           where ItemType : ActiveRecord<ItemType>, new()

           where ListType : AbstractList<ItemType, ListType>, new()

To decipher for the non-generically inclined: the method name is CreateCsvData.  You can pass in any SubSonic generated collection object as they all inherit from ActiveList.  What makes the code a bit ugly is the fact that ActiveList is a generic object that takes two other objects types in the declaration.  This is where having the source code handy really helps.

Understandably, there is a bit of code to doing this, so you can download it:

SubSonic Export To CSV for .Net 2.0

And if you are using .Net 3.5, here is an extension method version:

SubSonic Export To CSV for .Net 3.5

Using the code

First step is to hook up SubSonic to you database (of course).  I use the AdventureWorks LT database myself for most samples, which has a Customer table.  One of the generated SubSonic objects you will get is a CustomerCollection object. 

public string CreateCsv()

{

    CustomerCollection customerList = GetCustomers();

    SubSonicToCsv csv = new SubSonicToCsv();

    return csv.CreateCsvData(customerList);

}
 

Here is the code again, but this time using the .Net 3.5 extension method

public string CreateCsv35()

{

    CustomerCollection customerList = GetCustomers();

    return customerList.CreateCsvData();

}

You can debate which version you like better yourself but I like the .Net 3.5 better myself.  It is cleaner, and integrates into the SubSonic API beautifully.

Now I just have to replicate this for LINQ For SQL.

kick it on DotNetKicks.com

  • Ariel B. Vargas

    Hi Chris! Thank you for the excellent job. I’ll like to ask you if you have a new version of the SubSonicToCsv class since the one that I downloaded is not compiling. Thank you in advance!

  • http://damieng.com Damien Guard

    A quick look at the code would seem to indicate that it will put quotes around every field. Quotes should only be used for text not numeric values – in fact doing it for numeric will cause some applications/locales to try interpreting this as a date.

    [)amien

  • Dan

    Hi Chris,
    First of all thanks for your nicely written code.
    Been thru the code for 2.0.It compiles well with subsonic 2.0.3 version but the code for calling the CreateCSVData needs one more arguement and giving error ,it compiles well with 1.04 version of Subsonic.
    It will be great help if you can send me your CreateCSVData calling code with appropriate arguements.
    Thanks in advance.

    Dan