Archive

Posts Tagged ‘SqlBulkCopy’

Magiq: BulkCopy support

February 19, 2010 Leave a comment

With all the behavior stuff, I have normalized the design in favor of new Magiq providers. Now there’s a MagiqToDatabases abstract class that only needs a few methods to implement. Also, there is a normalization in the operations: Now Magiq have the classes DatabaseInsert, DatabaseUpdate, DatabaseQuery and DatabaseDelete, which contains properties like the columns to update, the expressions of the values, etc.

For implementing BuilkCopy, there was only two things needed to override:

  • The way Magiq-to-objects inserts into a SqlServer based destination: For example, for a Linq-to-Sql table, the way Magiq-to-objects inserts is using the Insert method of Table. The idea is to override that with a SqlBulkCopy. Note: this “way Magiq-to-objects inserts” is called IInsertDestination.
  • Use magiq-to-objects instead any SqlServer insert strategy defined: For using BulkCopy when the source belongs to the same provider, avoiding the regular insert…select… statement

So, following the behavior schema, Magiq provides two interfaces for all this: IObjectsInsertBehavior and IInsertBehavior.

  • IObjectsInsertBehavior provides the way for overriding the IInsertDestination.
  • IInsertBehavior provides the way for overriding the strategy to use, so we can avoid using the default strategy

Also, it needed a way to know if the destination is a SqlServer. Well… all the database operations and strategies implements an interface called IDatabaseBasedExecution that provides a IDatabaseAdapter. And this last interface give us a IDialect that could be a SqlServerDialect.

Ok, ok, but show me some code!!

public class BulkCopy : MagiqBehavior, IObjectsInsertBehavior, IInsertBehavior
{
	public int BatchSize { get; set; }
	public int Timeout { get; set; }

	public BulkCopy()
	{
		BatchSize = 0;
		Timeout = 30;
	}

	private static bool Applies(IDatabaseBasedExecution execution)
	{
		return execution != null && execution.DatabaseAdapter.Dialect is SqlServerDialect;
	}
	
	public IInsertDestination<TNew> GetInsertDestination<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert)
		where TDestination : class
		where TNew : class, TDestination, new()
	{
		var insertDestination = Next<IObjectsInsertBehavior>().Call(x => x.GetInsertDestination(insert));
		var databaseOperation = insertDestination as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new BulkCopyDestination<TSource, TDestination, TNew>(insert, databaseOperation.DatabaseAdapter)
					   {
						   BatchSize = BatchSize,
						   Timeout = Timeout
					   };

		return insertDestination;
	}

	public IInsertStrategy GetInsertStrategy<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert) where TDestination : class where TNew : class, TDestination, new()
	{
		var strategy = Next<IInsertBehavior>().Call(x => x.GetInsertStrategy(insert));
		var databaseOperation = strategy as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new InsertStrategy<TSource, TDestination, TNew>(insert);

		//overrides the strategy with magiq-to-objects
		return strategy;
	}

	public int Execute(IInsertStrategy strategy)
	{
		//default behavior
		return Next<IInsertBehavior>().Call(x => x.Execute(strategy));
	}
}

And the way to use it:


var sellsFromSomeRepository = ...;

records.From(sells)
       .Using<BulkCopy>()
       .Insert(x => new Record
                     {
                        Type = RecordType.Sell,
                        Description = "Sell from " + x.Date,
                        Amount = x.Total
                     });

And of course, if you use BulkCopy for inserting into a non SqlServer repository, the plugin continues without changes, so it could be used in a testing environment in which the repositories are setted to a List.