Archives

Archives / 2011 / May
  • Never, never, never do Office interop on the server

    I’ve been telling people this for years and years, and still I stepped right into the mudhole a month ago. I blame stress, but still I shouldn’t have. It’s so easy to just add the interop assemblies and off you go… until you try running your stuff on the 2008 r2 server, hosted somewhere else, with just enough user rights. And even though I managed to get the !”#!¤¤”% working after a while, I got mem-leaks and follow-up probs due to the workarounds I had to do to get the interop working in the first place.

    Then I sat down and relaxed for a minute and realized I need to do this the right way, which is the OpenXML way.

    The thing is, OpenXML is – compared to Office-interop - not easy, and the Microsoft OpenXML SDK 2.0 is just lipstick on top of the XML and some things which were so easy to do with the interop assemblies gives you nightmares to do with OpenXML. Try fill in formsfields in a protected word document for example. I ditched the SDK and ended up doing it directly against the XML directly instead.

    I also had to fill out a few Excel spreadsheets with data, which was somewhat easier to handle than the Word-story above. But I ran into problems here too, because I wanted to do such a simple thing as setting text wrap on a few cells! I don’t know, but I’m sure Microsoft could have done something better for us poor developers that need to create docs on the server! Are we supposed to spend 40% of the coding time to just create a few simple docs, just because we go the OpenXML way? There are a quite a few pages on MSDN with samples, as well as code snippets to download, but as soon as you need to do something outside the “demo-path” you’re banging your head. Maybe I’m just stupid and doesn’t get it, or maybe I’m getting grumpy, because OpenXML is not a developer-friendly framework! There, feels much better now…

    Now, for Excel spreadsheets there’s good hope because you got more than a few decent packages out there to help you out, and the best one I’ve found so far is called ClosedXML on Codeplex. It’s free and it did everything I needed to do right out of the box! It took me 5 minutes to create the 2 different spreadsheets I needed for my project, including cell formatting, text wrapping and so on. Cheers to MDeLeon for doing this!

  • Refactored Dapper Extensions

    Making extensions for Dapper-dot-net was so fun and I needed a few more simple ones so I refactored a bit and made a few Update() and Delete() extensions as well. I’m also looking for Id-properties with the [Key] attribute on them. The class grew a bit, but it now supports the functions below. Note that this will only work for very simple objects and tables, but it might be helpful for some of you Smile

    I’m trying to follow the overall Dapper syntax as much as I can. If you done specify the table name with the input parameter, I’m using the name of the class/entity as table name, except where anonymous types are used.

    The object I’m using for testing

        public class Test
        {
            [Key]
            public int Id { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
        }
    

    Insert typed object

                    var entity = new Test() { Name = "Johan", Age = 44 };
                    connection.Insert(entity);
    

    Insert anonymous object

                    connection.Insert("insert into test", new { Name = "Johan", Age = 20 });
    

    Update typed object (where-clause built up from Id-properties)

                    var entity = new Test() { Id = 16, Name = "Johan", Age = 50 };
                    connection.Update(entity);
    

    Delete typed object (where-clause built up from Id-properties)

                    var entity = new Test() { Id = 4, Name = "Johan", Age = 44 };
                    var deletedCount = connection.Delete(entity);
    

    Delete anonymous object (where-clause built up from anonymous object)

                    var deletedCount = connection.Delete("delete from test", new { Name = "Johan" });
    

    The Code:

    public static class DapperExtensions
    {
        public static void Insert(this IDbConnection connection, object entityToInsert)
        {
            Insert(connection, null, entityToInsert);
        }
    
        public static void Insert(this IDbConnection connection, string sql, object entityToInsert)
        {
            var name = entityToInsert.GetType().Name;
            var sb = new StringBuilder(sql);
            if (sql == null)
                sb.AppendFormat("insert into {0}", name);
            sb.Append(" (");
            BuildInsertParameters(entityToInsert, sb);
            sb.Append(") values (");
            BuildInsertValues(entityToInsert, sb);
            sb.Append(")");
            connection.Execute(sb.ToString(), entityToInsert);
        }
    
        public static int Update(this IDbConnection connection, object entityToUpdate)
        {
            return Update(connection, null, entityToUpdate);
        }
    
        public static int Update(this IDbConnection connection, string sql, object entityToUpdate)
        {
            var idProps = GetIdProperties(entityToUpdate);
            if (idProps.Count() == 0)
                throw new ArgumentException("Entity must have at least one [Key] property");
    
            var name = entityToUpdate.GetType().Name;
    
            var sb = new StringBuilder(sql);
            if (sql == null)
                sb.AppendFormat("update {0}", name);
    
            sb.AppendFormat(" set ");
            BuildUpdateSet(entityToUpdate, sb);
            sb.Append(" where ");
            BuildWhere(sb, idProps.ToArray());
    
            return connection.Execute(sb.ToString(), entityToUpdate);
        }
    
        public static int Delete<T>(this IDbConnection connection, T entityToDelete)
        {
            return Delete(connection, null, entityToDelete);
        }
    
        public static int Delete<T>(this IDbConnection connection, string sql, T entityToDelete)
        {
            var idProps = typeof (T).IsAnonymousType() ? 
                GetAllProperties(entityToDelete) : 
                GetIdProperties(entityToDelete);
    
            if (idProps.Count() == 0)
                throw new ArgumentException("Entity must have at least one [Key] property");
    
            var name = entityToDelete.GetType().Name;
    
            var sb = new StringBuilder(sql);
            if (sql == null)
                sb.AppendFormat("delete from {0}", name);
    
            sb.Append(" where ");
            BuildWhere(sb, idProps);
    
            return connection.Execute(sb.ToString(), entityToDelete);
        }
    
        private static void BuildUpdateSet(object entityToUpdate, StringBuilder sb)
        {
            var nonIdProps = GetNonIdProperties(entityToUpdate).ToArray();
    
            for (var i = 0; i < nonIdProps.Length; i++)
            {
                var property = nonIdProps[i];
    
                sb.AppendFormat("{0} = @{1}", property.Name, property.Name);
                if (i < nonIdProps.Length - 1)
                    sb.AppendFormat(", ");
            }
        }
    
        private static void BuildWhere(StringBuilder sb, IEnumerable<PropertyInfo> idProps)
        {
            for (var i = 0; i < idProps.Count(); i++)
            {
                sb.AppendFormat("{0} = @{1}", idProps.ElementAt(i).Name, idProps.ElementAt(i).Name);
                if (i < idProps.Count() - 1)
                    sb.AppendFormat(" and ");
            }
        }
    
        private static void BuildInsertValues(object entityToInsert, StringBuilder sb)
        {
            var props = GetAllProperties(entityToInsert);
    
            for (var i = 0; i < props.Count(); i++)
            {
                var property = props.ElementAt(i);
                if (property.GetCustomAttributes(true).Where(a => a is KeyAttribute).Any()) continue;
                sb.AppendFormat("@{0}", property.Name);
                if (i < props.Count() - 1)
                    sb.Append(", ");
            }
        }
    
        private static void BuildInsertParameters(object entityToInsert, StringBuilder sb)
        {
            var props = GetAllProperties(entityToInsert);
    
            for (var i = 0; i < props.Count(); i++)
            {
                var property = props.ElementAt(i);
                if (property.GetCustomAttributes(true).Where(a => a is KeyAttribute).Any()) continue;
                sb.Append(property.Name);
                if (i < props.Count() - 1)
                    sb.Append(", ");
            }
        }
    
        private static IEnumerable<PropertyInfo> GetAllProperties(object entity)
        {
            return entity.GetType().GetProperties();
        }
    
        private static IEnumerable<PropertyInfo> GetNonIdProperties(object entity)
        {
            return GetAllProperties(entity).Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute) == false);
        }
    
        private static IEnumerable<PropertyInfo> GetIdProperties(object entity)
        {
            return GetAllProperties(entity).Where( p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute));
        }
    }
    
    public static class TypeExtension
    {
        public static Boolean IsAnonymousType(this Type type)
        {
            if (type == null) return false;
    
            var hasCompilerGeneratedAttribute = type.GetCustomAttributes(typeof(CompilerGeneratedAttribute), false).Count() > 0;
            var nameContainsAnonymousType = type.FullName.Contains("AnonymousType");
            var isAnonymousType = hasCompilerGeneratedAttribute && nameContainsAnonymousType;
    
            return isAnonymousType;
        }
    }
    
    Feel free to fork the code as much as you can, I’m too tired to stay up longer. Dapper is getting really useful Smile
     
  • Simple Insert Extension for Dapper

    Last week I started using Dapper (dapper-dot-net on Google Code) for a project I’m working on. Dapper is a micro ORM, which extends IDbConnection. It’s very fast and works very well for queries where you want to return both typed and dynamic lists of objects. Have a quick look at the webpage and you’ll see what I mean.

    Today I needed to do some inserting, which you can do with the Execute() extension, but you’d have to type out all the “insert into xxx (col1,col2) values (@val1, @val2)” stuff. Some guys suggested to create a dapper.contrib and adding extensions of insert, update, delete and so on but it’s not there yet (at time of writing).

    Anyway, the extensions in dapper are already so well written I thought it should be quite easy to just add a dead simple (or stupid if you prefer) Insert() extensions on top of the existing Execute(), and I ended up with this raw unrefactored code. All it does really is building up the SQL for the insert using some property reflection, dapper itself does the heavy lifting:

    public static class DapperExtensions
    {
        public static void Insert(this IDbConnection connection, 
    object entityToInsert, string sql = "insert into " ) { var name = entityToInsert.GetType().Name; if (sql == "insert into ") { sql = "insert into " + name + " "; } sql += " ("; for (var i = 0; i < entityToInsert.GetType().GetProperties().Length; i++) { var propertyInfo = entityToInsert.GetType().GetProperties()[i]; sql += propertyInfo.Name; if (i < entityToInsert.GetType().GetProperties().Length - 1) sql += ","; } sql += ") values ("; for (var i = 0; i < entityToInsert.GetType().GetProperties().Length; i++) { var propertyInfo = entityToInsert.GetType().GetProperties()[i]; sql += "@" + propertyInfo.Name; if (i < entityToInsert.GetType().GetProperties().Length - 1) sql += ","; } sql += ")"; connection.Execute(sql, entityToInsert); } }
    I’m using it like this with a type:
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        var entity = new Test() { Name = "Johan", Age = 43 };
        connection.Insert(entity);
    }
    public class Test
    {
        public string Name { get; set; }
        public int Age { get; set; }
    }

    …or like this with an anonymous type:

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        connection.Insert(new { Name = "Johan", Age = 43 }, "insert into test");
    }

    Works for me ™ Winking smile