Tag Archives: LinqPad Extension

LinqPad: DumpAsInsert extension

I use LinqPad a LOT and love this tool.

Sometimes we need to export data to stage to another computer/database or just for safekeeping and I wrote a simple DumpAsInsert extension to dump the result of a Linq query as insert statements.

To install the extension add the code to MyExtensions and press F5 or select the Execute button. This will make LinqPad compile the extensions to a custom assembly and automatically load the methods. Beware that you must select C# Statements as language. C# Expression automatically adds .Dump()  to your statement.

Usage of the extension:

var names = new[]
  {
     new {Name= "Tom"}, 
     new {Name= "Dick"}, 
     new {Name= "Harry"}, 
   }.AsEnumerable();
names.DumpAsInsert("test");

gives this output:

INSERT INTO [test] ([Name])
VALUES ('Tom');
INSERT INTO [test] ([Name])
VALUES ('Dick');
INSERT INTO [test] ([Name])
VALUES ('Harry');

Using the Northwind sample database we can query tables like this:

Categories.Take(2).DumpAsInsert("Category");

and get the following result:

INSERT INTO [Category] ([CategoryID], [CategoryName], [Description], [Picture])
VALUES (1,'Beverages','Soft drinks, coffees, teas, beers, and ales',NULL);
INSERT INTO [Category] ([CategoryID], [CategoryName], [Description], [Picture])
VALUES (2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings',NULL);

Note that this includes the identity field so we need to specify fields to exclude:

Categories.Take(2).DumpAsInsert("Category", "CategoryID");

These extensions will also support projections to anonymous types like this

Categories.Take(2).Select(p => new {p.CategoryName, p.Description}).DumpAsInsert("Category");

and the result is

INSERT INTO [Category] ([CategoryName], [Description])
VALUES ('Beverages','Soft drinks, coffees, teas, beers, and ales');
INSERT INTO [Category] ([CategoryName], [Description])
VALUES ('Condiments','Sweet and savory sauces, relishes, spreads, and seasonings');

The SQL generator does not support Image or Xml data types. These will only be generated as NULL.

You should be able to add support for more datatypes into the SQL – I didn’t need it for my usage and didn’t have time to implement these.

Play around with the extension and feel free to update the code.

Here is the code to add in My Extensions:

void Main()
{
        // Write code to test your extensions here. Press F5 to compile and run.
        var names = new[] {new {Name= "Tom"}, new {Name= "Dick"}, new {Name= "Harry"}, new {Name= "Mary"}, new {Name= "Jay"} }.AsEnumerable();
        names.DumpAsInsert("test");
}

// Define other methods and classes here
public static class MyExtensions
{
   // Write custom extension methods here. They will be available to all queries.
   public static void DumpAsInsert<T>(this IEnumerable<T> data) where T:class
   {
       DumpAsInsert(data, null);
   }

   public static void DumpAsInsert<T>(this IEnumerable<T> data, string tableName) where T:class
   {
        DumpAsInsert(data, tableName, string.Empty);      
   }

   public static void DumpAsInsert<T>(this IEnumerable<T> data, string tableName, string hideColumn) where T:class
   {
        DumpAsInsert(data, tableName, new string[] { hideColumn});
   }

   public static void  DumpAsInsert<T>(this IEnumerable<T> data, string tableName, string[] hideColumns) where T:class
   {
        var firstItem = data.FirstOrDefault();
        if (firstItem == null) string.Empty.Dump();
        if (hideColumns == null) hideColumns = new [] { string.Empty };

        if (tableName == null)
            tableName = firstItem.GetType().Name;

        var formatProvider = GetSqlTextFormatInfo();
        var result = new StringBuilder();
        var members = new List<MemberInfo>();
        if (CheckIfAnonymousType(firstItem.GetType()))
            members.AddRange(firstItem.GetType().GetProperties().Where(p => !hideColumns.Contains(p.Name)));
        else
            members.AddRange(firstItem.GetType().GetFields().Where(p => !hideColumns.Contains(p.Name)));

        var stmt = string.Format("INSERT INTO [{0}] ({1})\nVALUES (", tableName, string.Join(", ", members.Select(p => string.Format("[{0}]", p.Name)).ToArray()));

        foreach (var item in data)
        {
            result.Append(stmt);

            var first = true;
            foreach (var col in members)
            {
                if (!first) result.Append(",");
                first = false;
                result.Append(GetFieldValue(formatProvider, col, item));
            }
            result.AppendLine(");");
        }

        result.ToString().Dump();
   }

   public static string GetFieldValue(IFormatProvider formatProvider, MemberInfo field, object row)
   {
        object value;
        Type fieldType;
        if (field is FieldInfo)
        {
            value = ((FieldInfo)field).GetValue(row);
            fieldType = ((FieldInfo) field).FieldType;
        }
        else
        {
            value = ((PropertyInfo)field).GetValue(row, null);
            fieldType = ((PropertyInfo)field).PropertyType;
        }
        if (value == null) return "NULL";

        if (fieldType == typeof(bool))
        return (bool) value ? "1" : "0";

        if (fieldType == typeof(System.String))
            return "'" + value.ToString().Replace("'", "''") + "'";
        else if (fieldType == typeof(DateTime) || fieldType == typeof(DateTime?))
            return "convert(datetime, '" + ((DateTime) value).ToString("yyyy-MM-dd HH:mm:ssss.fffffff") + "', 120)";
        else if (fieldType == typeof(System.Data.Linq.Binary))
            return "NULL";
        else if (fieldType == typeof(XElement))
            return "'" + ((XElement)value).Value.Replace("'", "''") + "'";
        else
            return string.Format(formatProvider, "{0}", value);
   }

    private static System.Globalization.NumberFormatInfo GetSqlTextFormatInfo() 
   {
        return new System.Globalization.NumberFormatInfo()
    {
        CurrencyDecimalSeparator = ".",
        CurrencyGroupSeparator = string.Empty,
        NumberDecimalSeparator = ".",
        NumberGroupSeparator = string.Empty,
        PercentDecimalSeparator = ".",
        PercentGroupSeparator = string.Empty,
    };
    }

    private static bool CheckIfAnonymousType(Type type)
    {
        if (type == null)
            throw new ArgumentNullException("type");

        // HACK: The only way to detect anonymous types right now.
        return Attribute.IsDefined(type, typeof(System.Runtime.CompilerServices.CompilerGeneratedAttribute), false)
            && type.IsGenericType && type.Name.Contains("AnonymousType")
            && (type.Name.StartsWith("<>") || type.Name.StartsWith("VB$"))
            && (type.Attributes & TypeAttributes.NotPublic) == TypeAttributes.NotPublic;
    }
}
Advertisements