Skip to content

Instantly share code, notes, and snippets.

@bbilginn

bbilginn/_BulkCopy.cs

Created April 1, 2014 05:11
Show Gist options
  • Save bbilginn/9908076 to your computer and use it in GitHub Desktop.
Save bbilginn/9908076 to your computer and use it in GitHub Desktop.
Download ZIP
BulkCopy mechanism
Raw
_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;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment