About Rui Jarimba

I was born in Madeira island, Portugal and I currently live in Lisbon. I’m working as a .NET software developer since 2005.

Code Kata – Numbers to Words, using javascript and TDD

This code kata is based on the KataNumbersInWords kata – convert numbers into words:

  • 3: “three”
  • 159: “one hundred and fifty-nine”
  • 2400: “two thousand and four hundred”

Katas are a good way to learn/practice TDD and/or learn more about a particular technology – in my particular case I decided to to this kata to improve my javascript skills.

The purpose of the exercise is to write the code incrementally, one step at a time – starting with the simplest case scenario (1 digit numbers) and then adding the code to handle the other scenarios (2 digit numbers, 3 digit numbers, etc).

Assume that the input is valid (number).

Test cases:

  • 1 digit numbers
    • 0 should return “zero”
    • 3 should return “three”
    • 7 should return “seven”
  • 2 digit numbers
    • 10 should return “ten”
    • 14 should return “fourteen”
    • 20 should return “twenty”
    • 21 should return “twenty-one”
    • 56 should return “fifty-six”
  • 3 digit numbers
    • 209 should return “two hundred and nine”
    • 300 should return “three hundred”
    • 417 should return “four hundred and seventeen”
    • 560 should return “five hundred and sixty”
    • 698 should return “six hundred and ninety-eight”
  • 4 digit numbers
    • 3004 should return “three thousand and four”
    • 4000 should return “four thousand”
    • 5020 should return “five thousand and twenty”
    • 6300 should return “six thousand and three hundred”
    • 7111 should return “seven thousand and one hundred and eleven”
  • 5 digit numbers
    • 40000 should return “forty thousand”
    • 70393 should return “seventy thousand and three hundred and ninety-three”
    • 87654 should return “eighty-seven thousand and six hundred and fifty-four”
  • 6 digit numbers
    • 500000 should return “five hundred thousand”
    • 803308 should return “eight hundred and three thousand and three hundred and eight”
    • 999999 should return “nine hundred and ninety-nine thousand and nine hundred and ninety-nine”

You can see my implementation here (I’m using WebStorm v8 and JS Test Driver):

Download the code (WebStorm project): NumbersToWords.zip

Implementing a basic IoC container using C#

Implementing a basic IoC container using C#, step by step.

Table of contents

  • References
  • Downloads
  • Continue reading

    Use extension methods to keep your code simple and DRY

    In this article I’ll show you why and how to use generic extension methods to keep your code simple and DRY.

    Table of contents

    The Problem

    Consider this simple model:

    public class Format
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }   
        
    public class Product 
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    
        public IEnumerable<Category> Categories { get; set; }
        public IEnumerable<Format> Formats { get; set; }
    
        public bool HasCategories
        {
            get
            {
                if(Categories == null)
                    return false;
    
                return Categories.Any();
            }
        }
    
        public bool HasFormats
        {
            get
            {
                if(Formats == null)
                    return false;
    
                return Formats.Any();
            }
        }
    }
    

    As you can see, I have created to properties HasCategories and HasFormats in the Product class to check if the collections Categories and Formats have any element, respectively.
    The following ASP.NET MVC view shows how these properties can be used:

    @model Product
    
    <h1>@Model.Name</h1>
    
    <p>@Html.DisplayFor(model => model.Description)</p>
    
    <h2>Product Categories</h2>
    
    <ul>
    @if(Model.HasCategories)
        {
            foreach(var category in Model.Categories)
            {
            <li>@category.Name</li>
            }
        }
        else
        {
            <li>This product has no categories</li>
        }
    </ul>
    
    <h2>Formats Available</h2>
    <ul>
    @if(Model.HasFormats)
        {
            foreach(var format in Model.Formats)
            {
            <li>@format.Name</li>
            }
        }
        else
        {
            <li>No formats available</li>
        }
    </ul>
    
    

    There is a problem with this code though – the properties improve the code readability and can be reused, but you may have dozens or hundreds of classes that may need properties like these. It is a verious tedious and time consuming task to create such properties in all the classes we need – a better option would be to create an extension method to do that work for us.

    Extension methods to the rescue

    If you take a good look into the HasCategories and HasFormats properties you can see that the code is similar, the only thing that changes is the type of the properties.

    We can then create a generic extension method to check if any object that implements IEnumerable (List, etc) is null or empty:

    namespace Extensions
    {
        public static class IEnumerableExtensions
        {
            /// <summary>
            /// Determines whether an enumerable is null or empty.
            /// </summary>
            /// <typeparam name="T">Type of the enumerable</typeparam>
            /// <param name="collection">The collection.</param>
            /// <returns>
            ///   <c>true</c> if the enumerable is null or empty; otherwise, <c>false</c>.
            /// </returns>
            public static bool IsNullOrEmpty<T>(this IEnumerable<T> collection)
            {
                if(collection == null)
                    return true;
    
                return !collection.Any();
            }
        }
    }
    

    There’s no more need to have the properties HasCategories and HasFormats so they can be removed from the Product class. The code now looks simpler and cleaner:

    public class Product 
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    
        public IEnumerable<Category> Categories { get; set; }
        public IEnumerable<Format> Formats { get; set; }
    }
    

    Finally, we can change the ASP.NET MVC view to use the extension method:

    @using Extensions
    @model Product
    
    <h1>@Model.Name</h1>
    <p>@Html.DisplayFor(model => model.Description)</p>
    
    <h2>Product Categories</h2>
    <ul>
    @if(Model.Categories.IsNullOrEmpty())
    {
        foreach(var category in Model.Categories)
        {
        <li>@category.Name</li>
        }
    }
    else
    {
        <li>This product has no categories</li>
    }
    </ul>
    
    <h2>Formats Available</h2>
    <ul>
    @if(Model.Formats.IsNullOrEmpty())
    {
        foreach(var format in Model.Formats)
        {
        <li>@format.Name</li>
        }
    }
    else
    {
        <li>No formats available</li>
    }
    </ul>
    
    

    Final thoughts

    In short, you can (and should) use extension methods to keep your code DRY.

    Basically you need to identity pieces of code that are generic enough and replace them with an extension method, if possible. You will have code that is easier to maintain and also more time to develop other funcionalities.

    References

    .NET Guidelines: Should I have one class per file?

    I had this discussion many times over the last few years:

    • Should we have a class per file?
    • What about other types such as interfaces, enums, …?

    Opinions on this subject vary a lot. This purpose of this article is to provide you some basic guidelines and why you should follow them.

    Table of contents

    Continue reading

    Improving LINQ code reusability: Select method

    Select method is used to project each element of a sequence into a new form, i.e. it can be used to map a collection of one type to a collection of another type. In this article I’ll show you a simple approach that will allow you to reuse the code used in the Select method.

    Table of contents

    The Problem

    Consider the following model:

    Let’s suppose that you have a services layer, so you don’t want to expose your domain objects directly to the client applications. Instead you create a set of data contracts (or DTOs, if you prefer):

    At some stage you’ll have to convert those Domain objects to data contracts. This is a common way of doing it:

    var details = repository.All<Album>().Select(album => new AlbumDetail {
        AlbumId = album.AlbumId,
        Price = album.Price,
        Title = album.Title,
    
        ArtistId = album.ArtistId,
        GenreId = album.GenreId,
        ArtistName = (album.Artist == null) ? null : album.Artist.Name,
        GenreName = (album.Genre == null) ? null : album.Genre.Name
    });
    

    There is a problem with this approach – if you need to query the same collection but using different criteria you have to duplicate the code inside the Select method.

    Solution 1 – Creating a method for the mapping

    In order to reuse the code, we can create a method that converts Album objects (Domain) to data contract objects:

    private static AlbumSummary CreateAlbumSummary(Album album)
    {
        return new AlbumSummary {
            AlbumId = album.AlbumId,
            Title = album.Title,
    
            ArtistName = (album.Artist == null) ? null : album.Artist.Name
        };
    }
    
    private static AlbumDetail CreateAlbumDetail(Album album)
    {
        return new AlbumDetail {
            AlbumId = album.AlbumId,
            Price = album.Price,
            Title = album.Title,
    
            ArtistId = album.ArtistId,
            GenreId = album.GenreId,
            ArtistName = (album.Artist == null) ? null : album.Artist.Name,
            GenreName = (album.Genre == null) ? null : album.Genre.Name
        };
    }
    

    Using the code:

    var albums = Albums.Select(CreateAlbumDetail);
    var albumsByGenre = Albums.Where(x => x.GenreId == genreId).Select(CreateAlbumDetail);
    
    // alternative way
    var albums2 = Albums.Select(x => CreateAlbumDetail(x));
    var albumsByGenre2 = Albums.Where(x => x.GenreId == genreId).Select(x => CreateAlbumDetail(x));
    

    Solution 2 – Creating a generic ObjectMapper object

    The previous solution solves the code reusability problem, but there’s still a tight coupling between components. Abstractions should be used to implement loose coupling between components – in this case, to abstract the mapping code.

    Step 1: define a contract (interface) with a method that converts one object of type TSource to an object of type TDestination:

    public interface IObjectMapper
    {
        TDestination Map<TSource, TDestination>(TSource source);
    }
    

    Step 2: create a class that implements IObjectMapper (click to expand):

    public class ObjectMapper : IObjectMapper
    {
        private Dictionary<Type, Func<object, object>> Mappers = new Dictionary<Type, Func<object, object>>
        {
            { typeof(Tuple<Album, AlbumDetail>), CreateAlbumDetail },
            { typeof(Tuple<Album, AlbumSummary>), CreateAlbumSummary }
    
            // more mappings here
            // ....
        };
    
    
        public TDestination Map<TSource, TDestination>(TSource source)
        {
            if(source == null)
                return default(TDestination);
    
            Func<object, object> mapper = null;
            Type key = typeof(Tuple<TSource, TDestination>);
    
            if(Mappers.TryGetValue(key, out mapper))
            {
                var newObject = mapper(source);
                return (TDestination) newObject;
            }
    
            string errorMessage = string.Format("Invalid mapping (Source: {0}, Destination: {1})";,
                                                typeof(TSource).FullName, 
                                                typeof(TDestination).FullName);
            
            throw new InvalidOperationException(errorMessage);
        }
    
    
        private static object CreateAlbumDetail(object source)
        {
            var album = source as Album;
    
            return new AlbumDetail {
                AlbumId = album.AlbumId,
                Price = album.Price,
                Title = album.Title,
    
                ArtistId = album.ArtistId,
                GenreId = album.GenreId,
                ArtistName = (album.Artist == null) ? null : album.Artist.Name,
                GenreName = (album.Genre == null) ? null : album.Genre.Name
            };
        }
    
        private static object CreateAlbumSummary(object source)
        {
            var album = source as Album;
    
            return new AlbumSummary {
                AlbumId = album.AlbumId,
                Title = album.Title,
                
                ArtistName = (album.Artist == null) ? null : album.Artist.Name
            };
        }
    }
    

    Example 1: Using LINQ

    Using the mapper in a LINQ expression – convert an Album collection to an AlbumSummary collection:

    IObjectMapper mapper = new ObjectMapper();
    
    IEnumerable<AlbumSummary> summaries = repository.All<Album>()
                                            .Select(mapper.Map<Album, AlbumSummary>);
    

    Example 1: Mapping a single object

    Using the mapper for a single object:

    var album = new Album {
        AlbumId = 1,
        Price = 10.0m,
        Title = "The Dreamer",
        Artist = new Artist { ArtistId = 1, Name = "José James" },
        Genre = new Genre { GenreId = 1, Name = "Jazz" }
    };
    
    IObjectMapper mapper = new ObjectMapper();
    
    AlbumDetail albumDetail = mapper.Map<Album, AlbumDetail>(album);
    

    Unit Testing

    Some NUnit tests:

    [Test]
    public void Given_a_non_existing_mapping_when_mapping_object_then_should_throw_InvalidOperationException()
    {
        // arrange
        IObjectMapper mapper = new ObjectMapper();
        var albumDetail = new AlbumDetail();
    
        // act/assert
        Assert.Throws<InvalidOperationException>(() => 
            // non-existing mapping
            mapper.Map<AlbumDetail, AlbumSummary>(albumDetail)
        );
    }
    
    [Test]
    public void Given_an_album_when_mapping_to_album_summary_should_equals_expected_album_summary()
    {
        // arrange
        IObjectMapper mapper = new ObjectMapper();
        
        var album = new Album {
            AlbumId = 4,
            Price = 10.0m,
            Title = "Heritage",
            Artist = new Artist { ArtistId = 4, Name = "Opeth" },
            Genre = new Genre { GenreId = 4, Name = "Metal" }
        };
    
        var expectedAlbumSummary = new AlbumSummary {
            AlbumId = 4,
            ArtistName = "Opeth",
            Title = "Heritage"
        };
        
        // act
        AlbumSummary albumSummary = mapper.Map<Album, AlbumSummary>(album);
        
        // assert
        Assert.AreEqual(albumSummary, expectedAlbumSummary);
    }
    

    Final thoughts

    In this article you learned how to reuse the code used in the Select method, and how you can use that code to map single objects. But writing mapping code is tedious and time consuming. There are mapping tools out there that can make your life easier – AutoMapper is one of them. I’ve used it in the past and I definitely recommend it. So, why use Automapper? Quoting their website:

    “What makes AutoMapper interesting is that it provides some interesting conventions to take the dirty work out of figuring out how to map type A to type B. As long as type B follows AutoMapper’s established convention, almost zero configuration is needed to map two types”

    “Mapping code is boring. Testing mapping code is even more boring. AutoMapper provides simple configuration of types, as well as simple testing of mappings”

    References

    Downloads

    Download the demo project (VS2010): LINQ-Select.zip

    Technorati Tags: , , ,

    Bulk Insert in .NET applications, part 1

    This is the first of a 2 series articles about how to perform bulk inserts in your .NET applications, using a SQL Server database.

    In this article I’ll show how to create a wrapper object for SqlBulkCopy that can do a bulk insert for a collection of objects. In the second article I’ll show how that wrapper can be easily integrated with Entity Framework (creating extension methods for DbContext and ObjectContext).

    Table of contents

    The problem

    I am working in an application that parses Excel files and creates an object for each line. After some validations and properties mapping the objects are then being inserted in a database table, one by one. At the beggining this wasn’t a big problem because the number of objects to insert in the database was small, but now there can be files with thousands of rows, so using Entity Framework isn’t the best way to do it (currently Entity Framework has no support for bulk insert operations).

    The solution

    I decided to use the object SqlBulkCopy because it seemed the best and easiest option for performing a bulk insert in a SQL Server database. I created a wrapper for SqlBulkCopy – BulkCopy.cs. This class works the same way as SqlBulkCopy but has some extra features.

    The following properties are available:

    • DestinationTableName: Name of the destination table on the server
    • BatchSize (optional): Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server
    • ConnectionString: Database connection string
    • ExpressionFilter: Filters the properties to be included

    And these are the methods available (see examples below):

    public void WriteToServer<T>(IEnumerable<T> items) where T : class;
    public void WriteToServer<T>(IEnumerable<T> items, SqlBulkCopyOptions options) where T : class;
    public void WriteToServer<T>(IEnumerable<T> items, SqlBulkCopyOptions options, IEnumerable<SqlBulkCopyColumnMapping> columnMappings) where T : class;
    

    BulkCopy.cs source code (click to expand):

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data.SqlClient;
    using System.Data;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Text;
    
    using BulkCopy.Extensions;
    
    namespace BulkCopy
    {
        /// <summary>
        /// Lets you efficiently bulk load a SQL Server table with data from another source.
        /// This is a wrapper class for <see cref="SqlBulkCopy"/>
        /// </summary>
        public class BulkCopy
        {
            /// <summary>
            /// Name of the destination table on the server
            /// </summary>
            public string DestinationTableName { get; set; }
    
            /// <summary>
            /// Number of rows in each batch. 
            /// At the end of each batch, the rows in the batch are sent to the server.
            /// </summary>
            public int? BatchSize { get; set; }
    
            /// <summary>
            /// Database connection string
            /// </summary>
            public string ConnectionString { get; set; }
    
            /// <summary>
            /// Filters the properties to be included
            /// </summary>
            public Func<PropertyDescriptor, bool> ExpressionFilter { get; set; }
    
    		
    		
            /// <summary>
            /// Initializes a new instance of the <see cref="BulkCopy&lt;T&gt;"/> class.
            /// </summary>
            public BulkCopy()
            {
            }
    
            /// <summary>
            /// Initializes a new instance of the <see cref="BulkCopy&lt;T&gt;"/> class.
            /// </summary>
            public BulkCopy(string connectionString)
            {
                this.ConnectionString = connectionString;
            }
    
    		
    		
            /// <summary>
            /// Copies all items in a collection to a destination table
            /// </summary>
            /// <param name="dataTable">The items that will be copied to the destination table</param>
            /// <param name="options">A combination of values from the System.Data.SqlClient.SqlBulkCopyOptions 
            /// enumeration that determines which data source rows are copied to the destination table. <see cref="SqlBulkCopyOptions"/></param>
            public virtual void WriteToServer<T>(IEnumerable<T> items) where T : class
            {
                WriteToServer(items, SqlBulkCopyOptions.Default);
            }
    
            /// <summary>
            /// Copies all items in a collection to a destination table
            /// </summary>
            /// <param name="dataTable">The items that will be copied to the destination table</param>
            /// <param name="options">A combination of values from the System.Data.SqlClient.SqlBulkCopyOptions 
            /// enumeration that determines which data source rows are copied to the destination table. <see cref="SqlBulkCopyOptions"/></param>
            public virtual void WriteToServer<T>(IEnumerable<T> items, SqlBulkCopyOptions options) where T : class
            {
                DataTable dataTable = (this.ExpressionFilter == null) ? items.ToDataTable() : items.ToDataTable(this.ExpressionFilter);
    
                WriteToServer(dataTable, options);
            }
    
            /// <summary>
            /// Copies all items in a collection to a destination table
            /// </summary>
            /// <param name="dataTable">The items that will be copied to the destination table</param>
            /// <param name="options">A combination of values from the System.Data.SqlClient.SqlBulkCopyOptions 
            /// enumeration that determines which data source rows are copied to the destination table. <see cref="SqlBulkCopyOptions"/></param>
            /// <param name="columnMappings">Returns a collection of System.Data.SqlClient.SqlBulkCopyColumnMapping items. 
            /// Column mappings define the relationships between columns in the data source and columns in the destination.</param>
            public virtual void WriteToServer<T>(IEnumerable<T> items, SqlBulkCopyOptions options, IEnumerable<SqlBulkCopyColumnMapping> columnMappings) where T : class
            {
                DataTable dataTable = (this.ExpressionFilter == null) ? items.ToDataTable() : items.ToDataTable(this.ExpressionFilter);
    
                WriteToServer(dataTable, options, columnMappings);
            }
    
            /// <summary>
            /// Copies all rows in the supplied System.Data.DataTable to a destination table
            /// </summary>
            /// <param name="dataTable">A System.Data.DataTable whose rows will be copied to the destination table</param>
            private void WriteToServer(DataTable dataTable)
            {
                WriteToServer(dataTable, SqlBulkCopyOptions.Default);
            }
    
            /// <summary>
            /// Copies all rows in the supplied System.Data.DataTable to a destination table
            /// </summary>
            /// <param name="dataTable">A System.Data.DataTable whose rows will be copied to the destination table</param>
            /// <param name="options">A combination of values from the System.Data.SqlClient.SqlBulkCopyOptions 
            /// enumeration that determines which data source rows are copied to the destination table. <see cref="SqlBulkCopyOptions"/></param>
            private void WriteToServer(DataTable dataTable, SqlBulkCopyOptions options)
            {
                var columnMappings = from x in dataTable.Columns.Cast<DataColumn>()
                                     select new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName);
    
                WriteToServer(dataTable, options, columnMappings);
            }
    
            /// <summary>
            /// Copies all rows in the supplied System.Data.DataTable to a destination table
            /// </summary>
            /// <param name="dataTable">A System.Data.DataTable whose rows will be copied to the destination table</param>
            /// <param name="options">A combination of values from the System.Data.SqlClient.SqlBulkCopyOptions 
            /// enumeration that determines which data source rows are copied to the destination table. <see cref="SqlBulkCopyOptions"/></param>
            /// <param name="columnMappings">Returns a collection of System.Data.SqlClient.SqlBulkCopyColumnMapping items. 
            /// Column mappings define the relationships between columns in the data source and columns in the destination.</param>
            private void WriteToServer(DataTable dataTable, SqlBulkCopyOptions options, IEnumerable<SqlBulkCopyColumnMapping> columnMappings)
            {
                // table name matching:
                // checks for DestinationTableName value
                // if null or empty, checks for dataTable.TableName
                string destinationTableName =
                    (string.IsNullOrWhiteSpace(DestinationTableName) ? null : DestinationTableName)
                    ?? (string.IsNullOrWhiteSpace(dataTable.TableName) ? null : dataTable.TableName);
    
                if(string.IsNullOrWhiteSpace(destinationTableName))
                    throw new ArgumentException("destinationTableName cannot be null or empty");
    
                using(var bulkCopy = new SqlBulkCopy(this.ConnectionString, options))
                {
                    bulkCopy.DestinationTableName = destinationTableName;
    
                    if(this.BatchSize.HasValue)
                        bulkCopy.BatchSize = this.BatchSize.Value;
    
                    foreach(var mapping in columnMappings)
                        bulkCopy.ColumnMappings.Add(mapping);
    
                    bulkCopy.WriteToServer(dataTable);
                }
            }
        }
    }
    

    The BulkCopy object is using internally some extension methods that converts a collection of objects to a DataTable. (taken from SO’s post Generic List to DataTable, with some small modifications).

    Click to expand the source code:

    using System;
    using System.Collections.Generic;
    using System.Collections.Specialized;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Text;
    using System.Data;
    using System.ComponentModel;
    
    namespace BulkCopy.Extensions
    {
        public static class DataExtensions
        {
            /// <summary>
            /// Basic data types 
            /// </summary>
            private static Type[] dataTypes = new[] {
                typeof(byte)
                ,typeof(sbyte)
                ,typeof(short)
                ,typeof(ushort)
                ,typeof(int)
                ,typeof(uint)
                ,typeof(long)
                ,typeof(ulong)
                ,typeof(float)
                ,typeof(double)
                ,typeof(decimal)
                ,typeof(bool)
                ,typeof(char)
                ,typeof(Guid)
                ,typeof(DateTime)
                ,typeof(DateTimeOffset)
                ,typeof(byte[])
                ,typeof(string)
            };
    
            /// <summary>
            /// Converts a generic List to a DataTable
            /// <see cref="http://stackoverflow.com/a/5805044"/>
            /// </summary>
            /// <typeparam name="T">Type of the object to convert to DataTable</typeparam>
            /// <param name="data">Data to be converted</param>
            /// <returns>The converted DataTable</returns>
            public static DataTable ToDataTable<T>(this IList<T> data)
            {
                IEnumerable<PropertyDescriptor> properties = from x in TypeDescriptor.GetProperties(typeof(T)).Cast<PropertyDescriptor>()
                                                             where IsBasicType(x.PropertyType)
                                                             select x;
    
                DataTable table = GetDataTable(data, properties);
                return table;
            }
    
            /// <summary>
            /// Converts a generic List to a DataTable
            /// <see cref="http://stackoverflow.com/a/5805044"/>
            /// </summary>
            /// <typeparam name="T">Type of the object to convert to DataTable</typeparam>
            /// <param name="data">Data to be converted</param>
            /// <returns>The converted DataTable</returns>
            public static DataTable ToDataTable<T>(this IList<T> data, Func<PropertyDescriptor, bool> expression)
            {
                var properties = TypeDescriptor.GetProperties(typeof(T))
                    .Cast<PropertyDescriptor>()
                    .Where(expression);
    
                DataTable table = GetDataTable(data, properties);
                return table;
            }
    
            /// <summary>
            /// Converts an IEnumerable to a DataTable
            /// <see cref="http://stackoverflow.com/a/5805044"/>
            /// </summary>
            /// <typeparam name="T">Type of the object to convert to DataTable</typeparam>
            /// <param name="data">Data to be converted</param>
            /// <returns>The DataTable</returns>
            public static DataTable ToDataTable<T>(this IEnumerable<T> data)
            {
                return data.ToList().ToDataTable();
            }
    
            /// <summary>
            /// Converts an IEnumerable to a DataTable
            /// <see cref="http://stackoverflow.com/a/5805044"/>
            /// </summary>
            /// <typeparam name="T">Type of the object to convert to DataTable</typeparam>
            /// <param name="data">Data to be converted</param>
            /// <param name="expression">Predicate to filter the properties of <typeparamref name="T"/> to be included to the DataTable</param>
            /// <returns>The DataTable</returns>
            public static DataTable ToDataTable<T>(this IEnumerable<T> data, Func<PropertyDescriptor, bool> expression)
            {
                return data.ToList().ToDataTable(expression);
            }
    
            #region Private methods
    
            private static bool IsBasicType(Type type)
            {
                type = Nullable.GetUnderlyingType(type) ?? type;
    
                return type.IsEnum || dataTypes.Contains(type);
            }
    
            private static DataTable GetDataTable<T>(this IList<T> data, IEnumerable<PropertyDescriptor> mappedProperties)
            {
                DataTable table = new DataTable();
    
                // columns
                foreach(PropertyDescriptor prop in mappedProperties)
                {
                    table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                }
    
                // row values
                foreach(T item in data)
                {
                    DataRow row = table.NewRow();
    
                    foreach(PropertyDescriptor prop in mappedProperties)
                    {
                        object value = prop.GetValue(item) ?? DBNull.Value;
                        row[prop.Name] = value;
                    }
    
                    table.Rows.Add(row);
                }
    
                return table;
            }
    
            #endregion
        }
    }
    
    

    Using the code

    Assuming a table named dbo.Albums (I’m using Mvc Music Store model):

    1. Basic usage

    This example shows how to use a model object whose property names match the database column names from the table above:

    public class Album
    {
        public virtual int AlbumId { get; set; }
        public virtual int GenreId { get; set; }
        public virtual int ArtistId { get; set; }
        public virtual string Title { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string AlbumArtUrl { get; set; }
    }
    

    All you need to do is to specify the connection string and the destination table:

    IEnumerable<Album> data = GetData();
    
    var bulkCopy = new BulkCopy() {
        ConnectionString = ConnectionString,
        DestinationTableName = "dbo.Albums"
    };
    
    bulkCopy.WriteToServer(data);
    

    2. Specifying batch size and bulk options

    Using the model from the previous example, you can specify some other options:

    IEnumerable<Album> data = GetData();
    
    var bulkCopy = new BulkCopy() {
        BatchSize = 200,
        ConnectionString = ConnectionString,
        DestinationTableName = "dbo.Albums"
    };
    
    // SqlBulkCopyOptions.CheckConstraints: Check constraints while data is being inserted. 
    // By default, constraints are not checked.
    bulkCopy.WriteToServer(data, SqlBulkCopyOptions.CheckConstraints);
    

    3. Using column mappings

    This example shows how to use a model object whose property names don’t match the database column names:

    public class Album
    {
        public virtual int Id { get; set; }
        public virtual int GenreId { get; set; }
        public virtual int ArtistId { get; set; }
        public virtual string AlbumTitle { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string AlbumArtImage { get; set; }
    }
    

    In this case we have to create the column mappings. It can be done like this:

    private static IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings()
    {
        yield return new SqlBulkCopyColumnMapping("Id", "AlbumId");
        yield return new SqlBulkCopyColumnMapping("GenreId", "GenreId");
        yield return new SqlBulkCopyColumnMapping("ArtistId", "ArtistId");
        yield return new SqlBulkCopyColumnMapping("AlbumTitle", "Title");
        yield return new SqlBulkCopyColumnMapping("Price", "Price");
        yield return new SqlBulkCopyColumnMapping("AlbumArtImage", "AlbumArtUrl");
    }
    

    Finally, the bulk insert can be done this way:

    IEnumerable<Album> data = GetData();
    IEnumerable<SqlBulkCopyColumnMapping> mappings = GetColumnMappings();
    
    var bulkCopy = new BulkCopy() {
        ConnectionString = ConnectionString,
        DestinationTableName = "dbo.Albums"
    };
    
    bulkCopy.WriteToServer(data,SqlBulkCopyOptions.Default, mappings);
    

    4. Filtering properties to be copied

    This example shows how to filter the properties of a model object to be used in the bulk insert:

    public partial class Album
    {
        public virtual int AlbumId { get; set; }
        public virtual int GenreId { get; set; }
        public virtual int ArtistId { get; set; }
        public virtual string Title { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string AlbumArtUrl { get; set; }
        public virtual string P1 { get; set; }
        public virtual string P2 { get; set; }
    }
    

    Properties P1 and P2 don’t match any column of the table above, so they cannot be used in the bulk insert operation. Creating a filter to exclude those properties and using it can be done like this:

    // properties to exclude from mapping
    var nonMappedProperties = new string[] { "P1", "P2" };
    
    Func<PropertyDescriptor, bool> expression = x => 
        !nonMappedProperties.Contains(x.Name);
    
    IEnumerable<Album> data = GetData();
    
    var bulkCopy = new BulkCopy() {
        BatchSize = 200,
        ConnectionString = ConnectionString,
        DestinationTableName = "dbo.Albums",
        ExpressionFilter = expression
    };
    
    bulkCopy.WriteToServer(data, SqlBulkCopyOptions.CheckConstraints);
    

    That’s it! In the next article I’ll show you how to integrate easily this wrapper class in Entity Framework (creating extension methods for DbContext and ObjectContext).

    References

    Downloads

    Download the demo project (VS2010): BulkCopy-part1.zip

    Technorati Tags: , , ,

    Entity Framework: Get mapped table name from an entity

    Extension methods for ObjectContext and DbContent to get the mapped table name from an entity.

    Table of contents

    The problem

    I am working on a set of extension methods to perform a bulk insert using Entity Framework, using internally the SqlBulkCopy object. One of the steps involved is to get the mapped table name from an entity. After some google searching, I found a post on StackOverflow that led me to the solution.

    The solution

    The trick is to use the method ObjectQuery.ToTraceString to generate a SQL Select statement for an entity, and then extract the table name from that statement.

    Let’s assume that you have an entity named Album corresponding to a table named dbo.Albums.

    // context is ObjectContext
    string sql = context.CreateObjectSet<T>().ToTraceString();
    
    ...
    

    The generated SQL for that entity can be something like this:

    SELECT 
    [Extent1].[AlbumId] AS [AlbumId], 
    [Extent1].[GenreId] AS [GenreId], 
    [Extent1].[ArtistId] AS [ArtistId], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[Price] AS [Price], 
    [Extent1].[AlbumArtUrl] AS [AlbumArtUrl]
    FROM [dbo].[Albums] AS [Extent1] 
    

    So, all we need to do is to parse the SELECT statement to get the table name. This is the approach used in the post above but it has some limitations – that code will work only for tables that are in the default SQL Server schema (dbo.{tableName}). I made some changes to that code and I’m extracting the full table name using regular expressions.

    The extension methods

    I have created one extension method for DbContext and other for ObjectContext:

    public static class ContextExtensions
    {
        public static string GetTableName<T>(this DbContext context) where T : class
        {
            ObjectContext objectContext = ((IObjectContextAdapter) context).ObjectContext;
    
            return objectContext.GetTableName<T>();
        }
    
        public static string GetTableName<T>(this ObjectContext context) where T : class
        {
            string sql = context.CreateObjectSet<T>().ToTraceString();
            Regex regex = new Regex("FROM (?<table>.*) AS");
            Match match = regex.Match(sql);
    
            string table = match.Groups["table"].Value;
            return table;
        }
    }
    

    Using the code

    Getting the mapped table name for an entity named Album, using a ObjectContext object:

    ObjectContext context = ....;
    string table = context.GetTableName<Album>();
    

    Or using a DbContext object:

    DbContext context = ....;
    string table = context.GetTableName<Album>();
    

    References

    Technorati Tags: ,