Creating Comma Delimited List (CSV) directly from SQL

This post serves as a self-reminder of how to perform SQL Records to CSV using Ad-Hoc SQL Query.

Getting data in CSV format is one of the features I keep using from time to time, and instead of searching for the same solution over Goggle over and over again, I created this post just to remind myself and serve as a note reference.

The following approach is derived from this discussion on stackoverflow.
http://stackoverflow.com/questions/1564980/building-a-comma-separated-list

Disclaimer: This approach is for MS SQL Server 2008 or later. The syntax probably won't work on other Relationsional Database Management System (RDBMS).

Let say we have a table like this:

If we want to retrieve the id column in comma delimited format.

SELECT STUFF((
    SELECT ', ' + cast(id as varchar(max))
    FROM [dbo].[username]
    FOR XML PATH('')
    ), 1, 2, '') AS CSV_Id;

If we want to retrieve the name column in comma delimited format, together with text quantifier of a single quote:

SELECT STUFF((

    SELECT ', ''' + cast(name as varchar(max)) + ''''

    FROM [dbo].[username]

    FOR XML PATH('')

    ), 1, 2, '') AS CSV_Name;

Beware of .NET Reflection

Recently I was working on a Proof of Concept (POC) project on Microsoft .NET Framework 4.5, which involves a brunch of testing buttons.
I tried to apply my design pattern knowledge on the Command Pattern to the project.

One of the command was a Quick Help Command that list all the available commands.

Trying to be smart, I utilized the .NET Reflection namespace to retrieve a list of all command class types under a specific namespace, excluding the base abstract class type.

var cmdList = (from obj in Assembly.GetExecutingAssembly().GetTypes()
               where obj.IsClass && obj.Namespace == "POC.Commands"
                    && obj.IsAbstract == false
               select obj).ToList();

After compile, it worked like a charm. I made some more changes and tried to compiled, then I got the "unable to copy file from obj/Debug to bin/Debug.  The process cannot access the file because it is being used by another process."

I tried a few times, same issue. I tried to manually delete the compiled executable file, and I could not. It seemed that there is a file lock on the executable file that preventing any changes.

After I restarted the Visual Studio, it worked again, but only for the first time.  I repeated the process a few times and same result. Then it just dawned on me that the it might be the Reflection class holding the file.

I commented out the code above, and replaced it with an empty list.  Voilà, this fixed the problem.

It turned out that the use of Reflector class was causing a circular reference on the executable. Taking it out fixed the issue.

Moral of the story, be careful when you work with .NET Reflection classes, especially when you running reflection on yourself instead of other reference DLLs. You may be surprised.