This blog covers one of the alternative solutions for dealing with tasks that require fetching and manipulating large amounts of data from the database.

Of course, you can always rely on standard SQL scripts, but I feel that this approach allows for more flexibility.

Recently I had to do multiple DB-related tasks using LLBLGen ORM, for example password encryption, performing image optimization etc. Every task had similar base requirements - to fetch a collection from the database and perform some action on each of its entities. I didn’t have to implement all the logic inside the method that iterates through entities; delegates allow me to easily call all the methods I need regardless of their implementation details.

Implementation

The main idea is to declare two delegates. As we all know, delegates in .NET are basically function pointers. The first one is used to reference a repository method, and the second references an action performed on each entity.

public delegate void Action(IEntity2 entity, UnitOfWork2 uow);
public delegate IEntityCollection2 EntityProvider(int currentPageIndex, int pageSize, out int recordCount);

The next step is to make one centralized method which will page through the collection and perform an action on it through Action and EntityProvider delegates. Note that you don’t have to specify the action’s details here: you can easily point your delegate to any of the methods that have the right signature.

public static void DoWorkWithPaging(EntityProvider col, Action action)
{
    if (!(col == null || action == null))
    {
        int recordCount = 0;
        int pageSize = 30;
        int currentPageIndex = 0;
        int processedItemsCount = 0;

        UnitOfWork2 uow = new UnitOfWork2(GenericRepository.GetInstance().GetDefaultCommitOrder());

        do
        {
            currentPageIndex++;
            IEntityCollection2 collection = col(currentPageIndex, pageSize, out recordCount);

            if (collection != null && collection.Count > 0)
            {
                foreach (IEntity2 item in collection)
                {
                    action(item, uow);
                    processedItemsCount++;
                }

                //write some simple progress to console
                int progress = (int)(((float)processedItemsCount / (float)recordCount) * 100);
                Console.Write(progress + "% ");
            }
        }
        while ((currentPageIndex * pageSize) < recordCount);

        Console.Write("Action performing");
        GenericRepository.GetInstance().CommitUnitOfWork(uow);
        Console.Write("Action finished!.");
    }
    else
    {
        Console.Write("Wrong parameters forwarded to method!");
    }
}

Please note that you can use this method on every entity which implements LLBLGen’s IEntity(ex: UserProfile, AspnetMembership, Calendar MonoX entites). In the next blog post I’ll show more elaborate usage examples of this approach.