SQL Bulk Loading

AssetsByClientFactBulkInserter.cs

using Dimensional.Common;
using Dimensional.Gips.Core.Services;
using Dimensional.Gips.Core.Services.Cube;

namespace Dimensional.Gips.Database.Cube
{
    public class AssetsByClientFactBulkInserter : BaseSqlBulkInserter<AssetsByClientFact>
    {
        private readonly IDatabaseSettings _databaseSettings;

        public AssetsByClientFactBulkInserter(
            IDatabaseSettings databaseSettings,
            ISystemLog systemLog)
            : base(systemLog)
        {
            _databaseSettings = databaseSettings;
        }

        protected override string ConnectionString
        {
            get { return _databaseSettings.GipsConnectionString; }
        }

        protected override string TableName
        {
            get { return "Cube.AssetsByClientFact"; }
        }

        public override void SetupColumns(BulkColumnSettingsCollection<AssetsByClientFact> settingsCollection)
        {
            settingsCollection.Add(m => m.PeriodEndDate);
            settingsCollection.Add(m => m.ClientIdentifier);
            settingsCollection.Add(m => m.AccountIdentifier);
            settingsCollection.Add(m => m.AccountType);
            settingsCollection.Add(m => m.Domicile);
            settingsCollection.Add(m => m.State);
            settingsCollection.Add(m => m.TaxStatus);
            settingsCollection.Add(m => m.InvestmentVehicle);
            settingsCollection.Add(m => m.ProductName);
            settingsCollection.Add(m => m.Count);
            settingsCollection.Add(m => m.AssetClass);
            settingsCollection.Add(m => m.InvestmentRegion);
            settingsCollection.Add(m => m.RegionalDirectorNumber);
            settingsCollection.Add(m => m.LineOfBusiness);
            settingsCollection.Add(m => m.Strategy);
            settingsCollection.Add(m => m.CompositeCode);
            settingsCollection.Add(m => m.CompositeName);
            settingsCollection.Add(m => m.VehicleCode);
            settingsCollection.Add(m => m.CurrentAum);
            settingsCollection.Add(m => m.CurrentAumInUsd);
            settingsCollection.Add(m => m.PortfolioNumber);
            settingsCollection.Add(m => m.MonthYearId, "MonthYearID");
        }
    }
}

BaseSqlBulkInserter.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dimensional.Common;

namespace Dimensional.Gips.Database
{
    public abstract class BaseSqlBulkInserter<TType>
    {
        private readonly ISystemLog _systemLog;

        protected BaseSqlBulkInserter(
            ISystemLog systemLog)
        {
            _systemLog = systemLog;
        }

        protected abstract string ConnectionString { get; }

        protected abstract string TableName { get; }

        public abstract void SetupColumns(BulkColumnSettingsCollection<TType> settingsCollection);

        protected BulkColumnSettings<TType>[] GetColumnSettings()
        {
            var settingsCollection = new BulkColumnSettingsCollection<TType>();

            SetupColumns(settingsCollection);

            return settingsCollection.GetSettingsArray();
        }

        public void InsertData(
            TType[] records)
        {
            if (records.Length <= 0)
                return;

            const string logStartTempate = "Starting bulk insert into table {0} for {1} records.";
            _systemLog.Info(string.Format(logStartTempate, TableName, records.Length));

            if (records.Length <= 0)
            {
                _systemLog.Info("No records to insert.");
                return;
            }

            var columnSettings = GetColumnSettings();

            var dataTable = CreateDataTable(columnSettings, TableName);

            foreach (var record in records)
            {
                var dataRow = dataTable.NewRow();

                foreach (var setting in columnSettings)
                {
                    var value = setting.CompiledExpression(record);

                    if (value != null)
                        dataRow[setting.ColumnName] = value;
                }

                dataTable.Rows.Add(dataRow);

                if (dataTable.Rows.Count >= 20000)
                {
                    WriteRecordsToDatabase(dataTable, TableName, columnSettings);
                    dataTable = CreateDataTable(columnSettings, TableName);
                }
            }

            if (dataTable.Rows.Count > 0)
                WriteRecordsToDatabase(dataTable, TableName, columnSettings);

            const string logCompletedTempate = "Completed bulk insert into table {0}.";
            _systemLog.Info(string.Format(logCompletedTempate, TableName));
        }

        public void InsertData(
            IEnumerable<TType> records)
        {
            const string logStartTempate = "Starting bulk insert into table {0}";
            _systemLog.Info(string.Format(logStartTempate, TableName));

            var columnSettings = GetColumnSettings();

            var dataTable = CreateDataTable(columnSettings, TableName);

            foreach (var record in records)
            {
                var dataRow = dataTable.NewRow();

                foreach (var setting in columnSettings)
                {
                    var value = setting.CompiledExpression(record);

                    if (value != null)
                        dataRow[setting.ColumnName] = value;
                }

                dataTable.Rows.Add(dataRow);

                if (dataTable.Rows.Count >= 20000)
                {
                    WriteRecordsToDatabase(dataTable, TableName, columnSettings);
                    dataTable = CreateDataTable(columnSettings, TableName);
                }
            }

            if (dataTable.Rows.Count > 0)
                WriteRecordsToDatabase(dataTable, TableName, columnSettings);

            const string logCompletedTempate = "Completed bulk insert into table {0}.";
            _systemLog.Info(string.Format(logCompletedTempate, TableName));
        }


        private void WriteRecordsToDatabase(
            DataTable dataTable,
            string databaseTableName,
            BulkColumnSettings<TType>[] columnSettings)
        {
            const string logProgressTempate = "Writing {0} records to the database.";
            _systemLog.Info(string.Format(logProgressTempate, dataTable.Rows.Count));

            using (var connection = new SqlConnection(ConnectionString))
            {
                SqlBulkCopy bulkCopy =
                    new SqlBulkCopy(connection);

                bulkCopy.BulkCopyTimeout = 120;

                foreach (var setting in columnSettings)
                    bulkCopy.ColumnMappings.Add(setting.ColumnName, setting.ColumnName);

                // set the destination table name
                bulkCopy.DestinationTableName = databaseTableName;
                connection.Open();

                // write the data in the "dataTable"
                bulkCopy.WriteToServer(dataTable);
                connection.Close();
            }
        }

        private DataTable CreateDataTable(
            BulkColumnSettings<TType>[] columnSettings,
            string databaseTableName)
        {
            var dataTable = new DataTable(databaseTableName);

            foreach (var setting in columnSettings)
            {
                var targetDataType = setting.DataType;

                if (targetDataType == typeof(int?))
                    targetDataType = typeof(int);
                if (targetDataType == typeof(decimal?))
                    targetDataType = typeof(decimal);
                if (targetDataType == typeof(DateTime?))
                    targetDataType = typeof(DateTime);
                if (targetDataType == typeof(bool?))
                    targetDataType = typeof(bool);

                dataTable.Columns.Add(setting.ColumnName, targetDataType);
            }
            return dataTable;
        }
    }
}

BulkColumnSettingsCollection,cs

using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using Dimensional.Gips.Core.Services;

namespace Dimensional.Gips.Database
{
    public class BulkColumnSettingsCollection<TType>
    {
        private readonly List<BulkColumnSettings<TType>> _settings;

        public BulkColumnSettingsCollection()
        {
            _settings = new List<BulkColumnSettings<TType>>();
        }

        public void Add(
            Expression<Func<TType, object>> property,
            string columnName,
            Type dataType)
        {
            var bulkColumnSettings = new BulkColumnSettings<TType>()
            {
                ColumnName = columnName,
                CompiledExpression = property.Compile(),
                DataType = dataType,
                Property = property
            };

            _settings.Add(bulkColumnSettings);
        }

        public void Add(
            Expression<Func<TType, object>> property,
            string columnName)
        {
            var bulkColumnSettings = new BulkColumnSettings<TType>()
            {
                ColumnName = columnName,
                CompiledExpression = property.Compile(),
                DataType = GetPropertyType(property),
                Property = property
            };

            _settings.Add(bulkColumnSettings);
        }

        public void Add(
            Expression<Func<TType, object>> property)
        {
            var bulkColumnSettings = new BulkColumnSettings<TType>()
            {
                ColumnName = GetPropertyName(property),
                CompiledExpression = property.Compile(),
                DataType = GetPropertyType(property),
                Property = property
            };

            _settings.Add(bulkColumnSettings);
        }

        public void Add(
            Expression<Func<TType, object>> property,
            Type dataType)
        {
            var bulkColumnSettings = new BulkColumnSettings<TType>()
            {
                ColumnName = GetPropertyName(property),
                CompiledExpression = property.Compile(),
                DataType = dataType,
                Property = property
            };

            _settings.Add(bulkColumnSettings);
        }

        public BulkColumnSettings<TType>[] GetSettingsArray()
        {
            return _settings.ToArray();
        }

        private Type GetPropertyType(Expression<Func<TType, object>> property)
        {
            var info = ReflectionHelper.FindProperty(property);

            return info.GetMemberType();
        }

        private string GetPropertyName(Expression<Func<TType, object>> property)
        {
            var info = ReflectionHelper.FindProperty(property);

            return info.Name;
        }
    }

    public class BulkColumnSettings<TType>
    {
        public string ColumnName { get; set; }
        public Func<TType, object> CompiledExpression { get; set; }
        public Expression<Func<TType, object>> Property { get; set; }
        public Type DataType { get; set; }
    }

}