public static bool MereBulkInsert <T>(this IEnumerable <T> toInsertEn, int batchSize, bool truncateLength) where T : new()
{
var toInsertList = toInsertEn.ToList();
if (toInsertList.Count <= 0)
{
return(true);
}
var mereTableMin = MereUtils.CacheCheck <T>();
var mereDataReader = new MereDataReader <T>(toInsertList, truncateLength);
using (var conn = MereUtils.GetConnection <T>())
{
conn.Open();
using (var cpy = new SqlBulkCopy(conn))
{
cpy.EnableStreaming = true;
cpy.BulkCopyTimeout = 0;
cpy.BatchSize = batchSize;
mereTableMin.SelectMereColumns.ForEach(
x => cpy.ColumnMappings.Add(x.ColumnName, x.ColumnName));
cpy.DestinationTableName = mereTableMin.TableName;
//cpy.NotifyAfter = 1;
//cpy.SqlRowsCopied += (o, e) => Console.WriteLine(e.RowsCopied);
cpy.WriteToServer(mereDataReader);
}
}
return(true);
}
public async static Task <bool> MereBulkInsertAsync <T>(this IEnumerable <T> toInsertEn, int batchSize, bool truncateLength, MereDataSource mereDataSource) where T : new()
{
var toInsertList = toInsertEn.ToList();
if (toInsertList.Count <= 0)
{
return(true);
}
var mereTableMin = MereUtils.CacheCheck <T>();
var mereDataReader = new MereDataReader <T>(toInsertList, truncateLength);
SqlConnection conn = mereDataSource == null?MereUtils.GetConnection <T>() : MereUtils.GetConnection(mereDataSource);
using (conn)
{
await conn.OpenAsync();
using (var cpy = new SqlBulkCopy(conn))
{
cpy.EnableStreaming = true;
cpy.BulkCopyTimeout = 0;
cpy.BatchSize = batchSize;
mereTableMin.SelectMereColumns.ForEach(
x => cpy.ColumnMappings.Add(x.ColumnName, x.ColumnName));
cpy.DestinationTableName = mereTableMin.TableName;
await cpy.WriteToServerAsync(mereDataReader);
}
}
return(true);
}
public async static Task <bool> MereBulkCopyAsync <T>(this IEnumerable <T> toCopyFrom, MereDataSource copyToMereDataSource, int batchSize = 1000, bool truncateLength = false)
where T : new()
{
var toInsertList = toCopyFrom.ToList();
if (toInsertList.Count <= 0)
{
return(true);
}
var mereTableMin = MereUtils.CacheCheck <T>();
var mereDataReader = new MereDataReader <T>(toInsertList, truncateLength);
using (var conn = MereUtils.GetConnection(copyToMereDataSource))
{
await conn.OpenAsync();
using (var cpy = new SqlBulkCopy(conn))
{
cpy.EnableStreaming = true;
cpy.BulkCopyTimeout = 0;
cpy.BatchSize = batchSize;
foreach (var mereColumn in mereTableMin.SelectMereColumns)
{
var col = mereColumn.ColumnName;
cpy.ColumnMappings.Add(col, col);
}
cpy.DestinationTableName = copyToMereDataSource.TableName ?? mereTableMin.TableName;
//cpy.NotifyAfter = 1;
//cpy.SqlRowsCopied += (o, e) => Console.WriteLine(e.RowsCopied);
await cpy.WriteToServerAsync(mereDataReader);
}
}
return(true);
}
public static async Task <bool> ExecuteAsync <T>(T src, MereDataSource mereDataSource) where T : new()
{
var mereTable = MereUtils.CacheCheck <T>();
using (var myCn = MereUtils.GetConnection <T>(mereDataSource))
{
await myCn.OpenAsync();
using (var tx = myCn.BeginTransaction())
{
using (var cmd = myCn.CreateCommand())
{
cmd.Transaction = tx;
foreach (var sel in mereTable.SelectMereColumnsNoIdentity().Select((prop, index) => new { index, prop }))
{
var v = sel.prop.GetBase(src);
if (v is DateTime && DateTime.Parse(v.ToString()).Year < 1753)
{
v = new DateTime(1753, 1, 1);
}
cmd.Parameters.AddWithValue("@" + sel.prop.ColumnName, v ?? DBNull.Value); // Changed .PropertyName to .ColumnName
}
var key = mereTable.SelectMereColumns.FirstOrDefault(w => w.IsKey);
var identity = mereTable.SelectMereColumns.FirstOrDefault(w => w.IsIdentity);
if (key != null)
{
var val = key.GetBase(src);
cmd.Parameters.AddWithValue("@key", val);
cmd.CommandText = mereTable.GetUpsertSqlWithKey();
if (identity != null)
{
var inserted = await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "SELECT @@IDENTITY";
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var id = reader[0];
if (id != DBNull.Value)
{
identity.SetBase(src, id);
}
}
reader.Close();
tx.Commit();
return(inserted > 0);
}
}
//defaults to insert if no key available
cmd.CommandText = mereTable.SqlInsert;
var inserted1 = await cmd.ExecuteNonQueryAsync();
tx.Commit();
if (inserted1 <= 0)
{
}
return(inserted1 > 0);
}
}
}
}
//=========================================================================================
public static bool MereBulkUpdate <T>(
this IEnumerable <T> toUpdateEn, IEnumerable <String> keyFields,
IEnumerable <String> setFields, int batchSize, bool truncateLength
) where T : new()
{
var toUpdateEnList = toUpdateEn.ToList();
if (toUpdateEnList.Count <= 0)
{
return(true);
}
var setFieldList = setFields.ToList();
if (setFieldList.Count <= 0)
{
return(true);
}
var keyFieldsList = keyFields.ToList();
if (keyFieldsList.Count <= 0)
{
return(true);
}
var mereTableMin = MereUtils.CacheCheck <T>();
var mereDataReader = new MereDataReader <T>(toUpdateEnList, truncateLength);
using (var conn = MereUtils.GetConnection <T>())
{
conn.Open();
using (var cpy = new SqlBulkCopy(conn))
{
cpy.EnableStreaming = true;
cpy.BulkCopyTimeout = 0;
cpy.BatchSize = batchSize;
//-----------------------------------------------------------
var tempSetSb = new StringBuilder();
var identityColumns = new List <String>();
foreach (var column in mereTableMin.SelectMereColumns)
{
if (setFieldList.Contains(column.ColumnName) ||
keyFieldsList.Contains(column.ColumnName))
{
if (column.IsIdentity)
{
identityColumns.Add("[" + column.ColumnName + "]");
}
cpy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
tempSetSb.Append("[" + column.ColumnName + "],");
}
}
if (tempSetSb.Length > 0)
{
tempSetSb.Length--;
}
//-----------------------------------------------------------
var tempTable = "##" + mereTableMin.TableName + "_" +
Guid.NewGuid().ToString().Replace("-", "_");
var command = new SqlCommand("", conn);
command.CommandText = String.Format(
"Select top 0 {1} Into {2} from {3}{0}",
"\r\n",
tempSetSb,
tempTable,
mereTableMin.TableName);
command.ExecuteNonQuery();
foreach (var identity in identityColumns)
{
command.CommandText = String.Format(
"Alter Table {1}{0}Drop Column {2}{0}",
"\r\n",
tempTable,
identity);
command.ExecuteNonQuery();
command.CommandText = String.Format(
"Alter Table {1}{0}Add {2} int{0}",
"\r\n",
tempTable,
identity);
command.ExecuteNonQuery();
}
cpy.DestinationTableName = tempTable;
cpy.WriteToServer(mereDataReader);
//-----------------------------------------------------------
var setSb = new StringBuilder();
setFieldList.ForEach(x =>
setSb.Append(String.Format(
"t1.{1} = t2.{1},{0} ",
"\r\n", x)));
if (setSb.Length > 9)
{
setSb.Length = setSb.Length - 9;
}
var keySb = new StringBuilder();
keyFieldsList.ForEach(x =>
keySb.Append(String.Format(
"t1.{1} = t2.{1}{0} AND ",
"\r\n", x)));
if (keySb.Length > 8)
{
keySb.Length = keySb.Length - 8;
}
//----------------------------------------------------------
command.CommandText = String.Format(
"Update t1{0}" +
" Set {3}{0}" +
" From {1} t1{0}" +
"Inner Join {2} t2{0}" +
" On {4}",
"\r\n",
mereTableMin.TableName,
tempTable, setSb, keySb);
command.ExecuteNonQuery();
}
}
return(true);
}