CODEWITHSUNDEEP
linqpad
Elsimer
Elsimer

Reputation: 1898

how do I pipe large resultsets from SQL query directly to CSV in LINQpad?

We are using Linqpad for our data stewards as a reporting tool alternative to SSMS. Most of our stewards are still stuck using SQL (we're slowly transitioning some of them to LINQ but one step at a time). However, we've come across a limitation in LINQPad that I'm not sure how to deal with. For large resultsets, since LINQPad first pulls the query into memory before pushing to the screen we run out of memory on large resultsets. Is there some way to push a SQL query in LINQPad directly to a CSV?

Upvotes: 1

Views: 696

Answers (1)

Joe Albahari
Joe Albahari

Reputation: 30934

LINQPad has no built-in method for this (I should add it), but it's easy enough to write. Put the following into 'My Extensions':

public static class MyExtensions
{
    public static void WriteCsv<T> (this IEnumerable<T> elements, string filePath)
    {
        var fields = typeof (T).GetFields();

        var props = typeof (T).GetProperties()
            .Where (p => IsSimpleType (p.PropertyType))
            .ToArray();

        using (var writer = new StreamWriter (filePath))
        {
            string header = string.Join (",", 
                fields.Select (f => f.Name).Concat (props.Select (p => p.Name)))

            // Work around bug in Excel
            if (header.StartsWith ("ID")) header = " " + header;

            writer.WriteLine (header);
            foreach (var element in elements)
            {
                var values =
                    fields.Select (f => Format (f.GetValue (element))).Concat (
                    props.Select (p => Format (p.GetValue (element, null))));

                writer.WriteLine (string.Join (",", values));
            }
        }
    }

    static string Format (object value)
    {
        if (value == null) return "";

        // With DateTimes, it's safest to force a culture insensitive format:
        if (value is DateTime) return ((DateTime)value).ToString ("s");
        if (value is DateTimeOffset) return ((DateTimeOffset)value).ToString ("s");

        string result = value.ToString();
        result = result.Replace ("\"", "\"\"");
        if (result.Contains (",") || result.Contains ("\"") || result.Any (c => c < 0x20))
            result = "\"" + result + "\"";

        return result;
    }

    public static bool IsSimpleType (Type t)
    {
        if (t.IsGenericType && t.GetGenericTypeDefinition () == typeof (Nullable<>))
            t = t.GetGenericArguments() [0];

        return t.IsPrimitive || Type.GetTypeCode (t) != TypeCode.Object ||
               typeof (IFormattable).IsAssignableFrom (t);
    }
}

Note that this formats values using the current culture (with the exception of dates). If you want use invariant culture, this is easy enough to change.

Before calling it, you must disable object tracking to avoid caching the objects in memory by setting ObjectTrackingEnabled to false:

ObjectTrackingEnabled = false;
Customers.WriteCsv (@"c:\\temp\\customers.csv");

Upvotes: 2

Related Questions