BulkCopy mechanism

_BulkCopy.cs

using FastMember;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace db.Portal.Bom.Class.Dal
{
    public static class _BulkCopy
    {

        /// <summary>
        /// Toplu kayıt işlemi.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">IList</param>
        /// <param name="tableName">Optional</param>
        /// <param name="PK">Optional</param>
        /// <param name="removeColumns">Optional</param>
        /// <param name="CurrentUser">Optional</param>
        /// <returns></returns>
        public static bool Bulking<T>(this IList<T> list,
            string tableName = null,
            string PK = null,
            List<string> removeColumns = null,
            SessionUsers CurrentUser = null)
        {
            return DataTableBulking(list, tableName, PK, removeColumns, CurrentUser);
        }

        /// <summary>
        /// Toplu kayıt işlemi.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">IEnumerable</param>
        /// <param name="tableName">Optional</param>
        /// <param name="PK">Optional</param>
        /// <param name="removeColumns">Optional</param>
        /// <param name="CurrentUser">Optional</param>
        /// <returns></returns>
        public static bool Bulking<T>(this IEnumerable<T> list,
            string tableName = null,
            string PK = null,
            List<string> removeColumns = null,
            SessionUsers CurrentUser = null)
        {
            return DataTableBulking(list, tableName, PK, removeColumns, CurrentUser);
        }

        private static bool DataTableBulking<T>(this IEnumerable<T> list,
            string tableName = null,
            string PK = null,
            List<string> removeColumns = null,
            SessionUsers CurrentUser = null)
        {
            try
            {
                if (tableName == null) tableName = "[" + typeof(T).Name + "]";
                using (SqlBulkCopy bc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString))
                {
                    bc.BatchSize = 100;
                    bc.NotifyAfter = 1000;
                    bc.DestinationTableName = tableName;
                    var List = ToDataTable(IdGenerate(list, PK, tableName), removeColumns);
                    foreach (var item in List.Columns)
                    {
                        bc.ColumnMappings.Add(item.ToString(), item.ToString());
                    }
                    bc.WriteToServer(List);
                    return true;
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// Verilen tablo için, listeye yeni ID üretir.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lst">IList</param>
        /// <param name="PK"></param>
        /// <param name="tableName">Optional</param>
        /// <param name="CurrentUser">Optional</param>
        /// <returns></returns>
        public static IList<T> IdGenerate<T>(this IList<T> lst, string PK,
            string tableName = null,
            SessionUsers CurrentUser = null)
        {
            if (PK == null) return lst;

            if (tableName == null) tableName = "[" + typeof(T).Name + "]";

            using (dbEntities dbEntities = new dbEntities())
            {
                dbEntities.Connection.Open();
                string query, fulfillment = string.Empty;

                if (CurrentUser == null)
                    query = string.Format("SELECT TOP 1 {0} FROM {1} ORDER BY {0} DESC", PK, tableName);
                else
                    query = string.Format("SELECT TOP 1 {0} FROM {1} WHERE CUSTOMER_ID='{2}' ORDER BY {0} DESC", PK, tableName, CurrentUser.CustomerId);

                // Kayıta ait son ID alınır 
                int result = Convert.ToInt32(dbEntities.ExecuteStoreQuery<string>(query).First());

                // PK alanının max alabileceği karakter sayısı alınır.
                query = string.Format(@"SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS IC
                                        WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName.Replace("[", null).Replace("]", null), PK);
                int maxLen = dbEntities.ExecuteStoreQuery<int>(query).First();

                dbEntities.Connection.Close();

                fulfillment = fulfillment.PadLeft(maxLen, '0');

                int LastId = result + 1;
                foreach (T item in lst)
                {
                    item.GetType().GetProperty(PK).SetValue(item, LastId.ToString(fulfillment), null);
                    LastId++;
                }
                return lst;
            }
        }

        /// <summary>
        /// Verilen tablo için, listeye yeni ID üretir.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lst">IEnumerable</param>
        /// <param name="PK"></param>
        /// <param name="tableName">Optional</param>
        /// <param name="CurrentUser">Optional</param>
        /// <returns></returns>
        public static IEnumerable<T> IdGenerate<T>(this IEnumerable<T> lst, string PK,
            string tableName = null,
            SessionUsers CurrentUser = null)
        {
            if (PK == null) return lst;

            if (tableName == null) tableName = "[" + typeof(T).Name + "]";

            using (dbEntities dbEntities = new dbEntities())
            {
                dbEntities.Connection.Open();
                string query, fulfillment = string.Empty;

                if (CurrentUser == null)
                    query = string.Format("SELECT TOP 1 {0} FROM {1} ORDER BY {0} DESC", PK, tableName);
                else
                    query = string.Format("SELECT TOP 1 {0} FROM {1} WHERE CUSTOMER_ID='{2}' ORDER BY {0} DESC", PK, tableName, CurrentUser.CustomerId);

                // Kayıta ait son ID alınır 
                int result = Convert.ToInt32(dbEntities.ExecuteStoreQuery<string>(query).First());

                // PK alanının max alabileceği karakter sayısı alınır.
                query = string.Format(@"SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS IC
                                        WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName.Replace("[", null).Replace("]", null), PK);
                int maxLen = dbEntities.ExecuteStoreQuery<int>(query).First();

                dbEntities.Connection.Close();

                fulfillment = fulfillment.PadLeft(maxLen, '0');

                int LastId = result + 1;
                foreach (T item in lst)
                {
                    item.GetType().GetProperty(PK).SetValue(item, LastId.ToString(fulfillment), null);
                    LastId++;
                }
                return lst;
            }
        }

        /// <summary>
        /// IList tipini DataTable tipine çevirir. İstenmeyen Propertyleri dışlar.
        /// </summary>
        /// <typeparam name="T">IList</typeparam>
        /// <param name="data"></param>
        /// <param name="removeColumns">Optional</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IList<T> data, List<string> removeColumns = null)
        {
            return DataTableConverting(data, removeColumns);
        }

        /// <summary>
        /// IEnumerable tipini DataTable tipine çevirir. İstenmeyen Propertyleri dışlar.
        /// </summary>
        /// <typeparam name="T">IEnumerable</typeparam>
        /// <param name="data"></param>
        /// <param name="removeColumns">Optional</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> data, List<string> removeColumns = null)
        {
            return DataTableConverting(data, removeColumns);
        }

        private static DataTable DataTableConverting<T>(this IEnumerable<T> data, List<string> removeColumns = null)
        {
            DataTable table = new DataTable();
            using (var reader = ObjectReader.Create(data)) // nuget.org/packages/FastMember
            {
                table.Load(reader);
            }
            try
            {
                // Dahil edilmemesi istenilen statik alanlar çıkartılır.
                table.Columns.Remove("EntityState");
                table.Columns.Remove("EntityKey");
                // Dahil edilmemesi istenilen dinamik alanlar çıkartılır.
                if (removeColumns != null)
                    foreach (string item in removeColumns)
                    {
                        table.Columns.Remove(item);
                    }
            }
            catch (Exception)
            {
            }
            return table;
        }
    }
}