public override bool BulkCopy <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
{
try
{
CreateConnection();
SqlConnection connection = Connection as SqlConnection;
if (connection == null)
{
throw new NullReferenceException(nameof(Connection));
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)
{
DestinationTableName = bulkMapper.DstTableName[0]
};
foreach (string map in bulkMapper.MapList)
{
string[] cols = map.Split(',');
bulkCopy.ColumnMappings.Add(cols[0], cols[1]);
}
using (var er = new EntityReader <TEntity>(list))
{
Stopwatch sw = new Stopwatch();
sw.Start();
bulkCopy.WriteToServer(er);
sw.Stop();
bulkCopy.Close();
Trace.WriteLine($"bulkCopy took {sw.ElapsedMilliseconds} ms to execute");
}
}
catch (Exception e)
{
LastError.Code = e.HResult;
LastError.Message = e.Message;
return(false);
}
return(true);
}
public virtual bool BulkUpdate <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper) where TEntity : class, new()
{
throw new NotImplementedException();
}
/*
* Example 1
* Merges the DailySales table into GlobalSales:
*
* merge into GlobalSales
* (Item_number, Description, Quantity)as G
* using DailySales as D
* ON D.Item_number = G.Item_number
* when not matched then
* insert (Item_number, Description, Quantity )
* values (D.Item_number, D.Description, D.Quantity)
* when matched then
* update set G.Quantity = G.Quantity + D.Quantity
*
* Example 2
* Uses a derived table as the source table with dynamic parameter markers:
*
* merge into GlobalSales (Item_number, Description, Quantity)as G
* using select (?, ?, ?) as D (Item_number, Description, Quantity)
* ON D.Item_number = G.Item_number
* when not matched then
* insert (Item_number, Description, Quantity )
* values (D.Item_number, D.Description, D.Quantity)
* when matched then
* update set G.Quantity = G.Quantity + D.Quantity
*/
//-----------------------------------------------------------------------------------------
public override bool BulkUpdate <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
{
Stopwatch sw = new Stopwatch();
try
{
if (bulkMapper.SqlCreateTmpTable.IsNotNullOrEmpty())
{
CreateCommand(bulkMapper.SqlCreateTmpTable);
Cmd.ExecuteNonQuery();
}
// this is the connection for the bulkupdate
SqlConnection connection = Connection as SqlConnection;
if (connection == null)
{
throw new NullReferenceException(nameof(connection));
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)
{
DestinationTableName = $"#tmp_{bulkMapper.TmpTableName}"
};
foreach (string colPair in bulkMapper.MapList)
{
string[] fields = colPair.Split(',');
if (fields.Length == 2)
{
bulkCopy.ColumnMappings.Add(fields[0], fields[0]);
}
}
#if false
using (var reader = ObjectReader.Create(list))
{
// write records to temp table.
sw.Restart();
bulkCopy.WriteToServer(reader);
sw.Stop();
bulkCopy.Close();
Trace.WriteLine($"BulkUpdate bulkCopy.WriteToServer took {sw.ElapsedMilliseconds} ms to execute");
// merge temp table into specified tables, one at a time.
foreach (var sqlMerge in bulkMapper.SqlMerge)
{
var resultList = ExecuteQuery <TEntity>($"select * from #tmp_{bulkMapper.TmpTableName}");
if (resultList != null && resultList.Any())
{
if (sqlMerge.IsNotNullOrEmpty())
{
sw.Restart();
CreateCommand(sqlMerge);
Cmd.ExecuteNonQuery();
sw.Stop();
Trace.WriteLine($"BulkUpdate Merge statement took {sw.ElapsedMilliseconds} ms to execute");
}
}
}
}
#else
using (var reader = new EntityReader <TEntity>(list))
{
// write records to temp table.
sw.Restart();
bulkCopy.WriteToServer(reader);
sw.Stop();
bulkCopy.Close();
Trace.WriteLine($"BulkUpdate bulkCopy.WriteToServer took {sw.ElapsedMilliseconds} ms to execute");
// merge temp table into specified tables, one at a time.
foreach (var sqlMerge in bulkMapper.SqlMerge)
{
var resultList = ExecuteQuery <TEntity>($"select top 1 * from #tmp_{bulkMapper.TmpTableName}");
if (resultList != null && resultList.Any())
{
if (sqlMerge.IsNotNullOrEmpty())
{
CreateCommand(sqlMerge);
sw.Restart();
Cmd.ExecuteNonQuery();
sw.Stop();
Trace.WriteLine($"BulkUpdate Merge statement took {sw.ElapsedMilliseconds} ms to execute");
}
}
}
}
#endif
}
catch (Exception e)
{
LastError.Code = e.HResult;
LastError.Message = e.Message;
Trace.WriteLine($"Error Message: {e.Message}");
return(false);
}
return(true);
}
public override bool BulkCopy <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
{
return(base.BulkCopy(list, bulkMapper));
}