public void BulkCopyLinqTypes([IncludeDataSources(CurrentProvider)] string context)
{
foreach (var bulkCopyType in new[] { BulkCopyType.MultipleRows, BulkCopyType.ProviderSpecific })
{
using (var db = new DataConnection(context))
{
try
{
db.BulkCopy(
new BulkCopyOptions {
BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 10).Select(n =>
new LinqDataTypes
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
SmallIntValue = (short)n
}
));
}
finally
{
db.GetTable <LinqDataTypes>().Delete(p => p.ID >= 4000);
}
}
}
}
public void BulkCopyLinqTypesMultipleRows([IncludeDataSources(TestProvNameDb2i.All)] string context)
{
using (var db = new DataConnection(context))
{
try
{
db.BulkCopy(
new BulkCopyOptions {
BulkCopyType = BulkCopyType.MultipleRows
},
Enumerable.Range(0, 10).Select(n =>
new LinqDataTypes
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
SmallIntValue = (short)n
}
));
}
catch (Exception e)
{
Assert.Fail(e.ToString());
}
finally
{
db.GetTable <LinqDataTypes>().Delete(p => p.ID >= 4000);
}
}
}
public void BulkCopyLinqTypesProviderSpecific([IncludeDataSources(TestProvName.AllInformix)] string context)
{
using (var db = new DataConnection(context))
{
try
{
db.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
RowsCopiedCallback = copied => Debug.WriteLine(copied.RowsCopied)
},
Enumerable.Range(0, 10).Select(n =>
new DataTypes
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
DateTimeValue2 = new DateTime(2001, 1, 10, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
BinaryValue = new byte[] { (byte)n },
SmallIntValue = (short)n,
IntValue = n,
BigIntValue = n,
StringValue = n.ToString(),
}
));
}
finally
{
db.GetTable <DataTypes>().Delete(p => p.ID >= 4000);
}
}
}
void BulkCopyAllTypes(string context, BulkCopyType bulkCopyType)
{
using (var db = new DataConnection(context))
{
db.CommandTimeout = 60;
db.GetTable <AllType>().Delete(p => p.ID >= _allTypeses[0].ID);
var keepIdentity = bulkCopyType == BulkCopyType.ProviderSpecific &&
((InformixDataProvider)db.DataProvider).Adapter.IsIDSProvider;
db.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = bulkCopyType,
RowsCopiedCallback = copied => Debug.WriteLine(copied.RowsCopied),
KeepIdentity = keepIdentity
},
_allTypeses);
var ids = _allTypeses.Select(at => at.ID).ToArray();
var list = db.GetTable <AllType>().Where(t => ids.Contains(t.ID)).OrderBy(t => t.ID).ToList();
db.GetTable <AllType>().Delete(p => p.ID >= _allTypeses[0].ID);
Assert.That(list.Count, Is.EqualTo(_allTypeses.Length));
for (var i = 0; i < list.Count; i++)
{
CompareObject(db.MappingSchema, list[i], _allTypeses[i]);
}
}
}
public void BulkCopyLinqTypesProviderSpecific(string context)
{
using (var db = new DataConnection(context))
{
db.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
RowsCopiedCallback = copied => Debug.WriteLine(copied.RowsCopied)
},
Enumerable.Range(0, 10).Select(n =>
new DataTypes
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
SmallIntValue = (short)n
}
));
db.GetTable <DataTypes>().Delete(p => p.ID >= 4000);
}
}
public Task Handle(BusinessProcessList <IBusinessProcess> processlist, CancellationToken cancellationToken)
{
var processList = processlist.OfType <BusinessServiceModel>().ToArray();
if (!processList.Any())
{
return(Task.FromResult(0));
}
var toBulkInsert = processList.Where(x => !x.ShouldUpdate).ToList();
//var toUpdate = processList.Where(x => x.ShouldUpdate);
try
{
if (toBulkInsert.Count > 0)
{
if (toBulkInsert.Count > 1)
{
_db.BulkCopy(toBulkInsert);
}
else
{
_db.Insert(toBulkInsert.Single());
}
}
/* foreach (var businessProcess in toUpdate)
* {
* var updateLinq = _db.EASBusinessServices.Where(p =>
* p.StoreId == businessProcess.StoreId && p.TerminalId == businessProcess.TerminalId &&
* p.DataAreaId == businessProcess.DataAreaId &&
* p.TransactionId == businessProcess.TransactionId && p.ObjectId== businessProcess.ObjectId);
*
* var updatable = updateLinq.Set(x => x.LastUpdateTime, DateTime.Now);
*
* if (!string.IsNullOrWhiteSpace(businessProcess.CashVoucher))
* updatable = updatable.Set(x => x.CashVoucher, businessProcess.CashVoucher);
* if (!string.IsNullOrWhiteSpace(businessProcess.DispatchBarcode))
* updatable = updatable.Set(x => x.DispatchBarcode, businessProcess.DispatchBarcode);
* if (!string.IsNullOrWhiteSpace(businessProcess.OperatorId))
* updatable = updatable.Set(x => x.OperatorId, businessProcess.OperatorId);
* if (businessProcess.BeginDateTime != null)
* updatable = updatable.Set(x => x.BeginDateTime, businessProcess.BeginDateTime);
* if (businessProcess.EndDateTime != null)
* updatable = updatable.Set(x => x.EndDateTime, businessProcess.EndDateTime);
*
*
* updatable.Update();
* }
*/
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(Task.FromResult(0));
}
public static BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, IEnumerable <T> source, int maxBatchSize = 1000, Action <BulkCopyRowsCopied> rowsCopiedCallback = null)
{
return(dataConnection.BulkCopy(new BulkCopyOptions
{
BulkCopyType = BulkCopyType.MultipleRows,
MaxBatchSize = maxBatchSize,
RowsCopiedCallback = rowsCopiedCallback
}, source));
}
protected override void InsertMultipleTest(int count)
{
using (var transaction = new TransactionScope())
{
InstanceCount = (int)_db.BulkCopy(CreateNewSimplests(count)).RowsCopied;
transaction.Complete();
}
}
public void BulkCopyTest()
{
using (var db = new DataConnection(dbConnString))
{
db.BulkCopy(Enumerable
.Range(1, 100)
.Select(n => new TestTable {
Name = n.ToString()
}));
}
}
public static BulkCopyRowsCopied ProviderSpecificBulkCopy <T>(DataConnection dataConnection, IEnumerable <T> source, int bulkCopyTimeout = 0, bool keepIdentity = false, int notifyAfter = 0, Action <BulkCopyRowsCopied> rowsCopiedCallback = null)
{
return(dataConnection.BulkCopy(new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
BulkCopyTimeout = bulkCopyTimeout,
KeepIdentity = keepIdentity,
NotifyAfter = notifyAfter,
RowsCopiedCallback = rowsCopiedCallback
}, source));
}
public void AddProductList(IList <Product> products)
{
foreach (var product in products)
{
UpdateCategory(product);
UpdateSupplier(product);
}
_dataConnection.BulkCopy(new BulkCopyOptions {
BulkCopyType = BulkCopyType.MultipleRows
}, products);
}
void BulkCopyTest_AccessClient(string context, BulkCopyType bulkCopyType, int maxSize, int batchSize)
{
using (var conn = new DataConnection(context))
{
try
{
conn.BulkCopy(
new BulkCopyOptions
{
MaxBatchSize = maxSize,
BulkCopyType = bulkCopyType,
NotifyAfter = 10000,
RowsCopiedCallback = copied => Debug.WriteLine(copied.RowsCopied)
},
Enumerable.Range(0, batchSize).Select(n =>
new ALLTYPE
{
ID = 2000 + n,
BIGINTDATATYPE = 3000 + n,
INTDATATYPE = 4000 + n,
SMALLINTDATATYPE = (short)(5000 + n),
DECIMALDATATYPE = 6000 + n,
DECFLOAT16DATATYPE = 7000 + n,
DECFLOAT34DATATYPE = 7000 + n,
REALDATATYPE = 8000 + n,
DOUBLEDATATYPE = 9000 + n,
CHARDATATYPE = 'A',
VARCHARDATATYPE = "",
CLOBDATATYPE = "123",
DBCLOBDATATYPE = "αβγ",
BINARYDATATYPE = new byte[] { 1, 2, 3 },
VARBINARYDATATYPE = new byte[] { 1, 2, 3 },
BLOBDATATYPE = new byte[] { 1, 2, 3 },
GRAPHICDATATYPE = "αβγ",
VARGRAPHICDATATYPE = "βγδ",
DATEDATATYPE = DateTime.Now.Date,
TIMEDATATYPE = TimeSpan.FromSeconds(10),
TIMESTAMPDATATYPE = DateTime.Now,
XMLDATATYPE = "<root><element strattr=\"strvalue\" intattr=\"12345\"/></root>"
}));
}
catch (Exception e)
{
Assert.Fail(e.Message);
}
finally
{
conn.GetTable <ALLTYPE>().Delete(p => p.DECIMALDATATYPE >= 6000);
}
}
}
public static BulkCopyRowsCopied MultipleRowsCopy <T>(
DataConnection dataConnection,
IEnumerable <T> source,
int maxBatchSize = 1000,
Action <BulkCopyRowsCopied>?rowsCopiedCallback = null)
where T : class
{
return(dataConnection.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
MaxBatchSize = maxBatchSize,
RowsCopiedCallback = rowsCopiedCallback,
}, source));
}
public static BulkCopyRowsCopied ProviderSpecificBulkCopy <T>(
DataConnection dataConnection,
IEnumerable <T> source,
int?maxBatchSize = null,
int?bulkCopyTimeout = null,
int notifyAfter = 0,
Action <BulkCopyRowsCopied> rowsCopiedCallback = null)
{
return(dataConnection.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
BulkCopyTimeout = bulkCopyTimeout,
NotifyAfter = notifyAfter,
RowsCopiedCallback = rowsCopiedCallback,
}, source));
}
public void Replicate()
{
try
{
var sourceQueryable = _mapSpecification.Map(_source);
var options = new BulkCopyOptions {
BulkCopyTimeout = 1800
};
_target.GetTable <T>().Delete();
_target.BulkCopy(options, sourceQueryable);
}
catch (Exception ex)
{
var linq2DBSource = _source as LinqToDbQuery;
throw new Exception($"Can not process entity type {typeof(T).Name}\n{linq2DBSource?.LastQuery}", ex);
}
}
void BulkCopyTest(string context, BulkCopyType bulkCopyType)
{
using (var conn = new DataConnection(context))
{
conn.BeginTransaction();
conn.BulkCopy(new BulkCopyOptions {
MaxBatchSize = 50000, BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 100000).Select(n =>
new AllType
{
ID = 2000 + n,
bigintDataType = 3000 + n,
smallintDataType = (short)(4000 + n),
tinyintDataType = (sbyte)(5000 + n),
mediumintDataType = 6000 + n,
intDataType = 7000 + n,
numericDataType = 8000 + n,
decimalDataType = 9000 + n,
doubleDataType = 8800 + n,
floatDataType = 7700 + n,
dateDataType = DateTime.Now,
datetimeDataType = DateTime.Now,
timestampDataType = null,
timeDataType = null,
yearDataType = (1000 + n) % 100,
year2DataType = (1000 + n) % 100,
year4DataType = null,
charDataType = 'A',
varcharDataType = "",
textDataType = "",
binaryDataType = null,
varbinaryDataType = null,
blobDataType = new byte[] { 1, 2, 3 },
bitDataType = null,
enumDataType = "Green",
setDataType = "one",
intUnsignedDataType = (uint)(5000 + n),
}));
//var list = conn.GetTable<ALLTYPE>().ToList();
conn.GetTable <ALLTYPE>().Delete(p => p.SMALLINTDATATYPE >= 5000);
}
}
/// <summary>
/// Insert entities
/// </summary>
/// <param name="entities">Entities</param>
public virtual long Insert(IEnumerable <TEntity> entities)
{
if (entities == null)
{
throw new ArgumentNullException(nameof(entities));
}
var result = 0L;
using (var transaction = new TransactionScope())
{
var status = _dataConnection.BulkCopy(new BulkCopyOptions(), entities);
result = status.RowsCopied;
transaction.Complete();
}
return(result);
}
public Task Handle(BusinessProcessList <IBusinessProcess> processlist, CancellationToken cancellationToken)
{
var processList = processlist.OfType <PrintReportSaverModel>().ToArray();
if (!processList.Any())
{
return(Task.FromResult(0));
}
var reports = processList.Where(x => !x.ShouldUpdate).ToList();
_db.BulkCopy(reports);
return(Task.FromResult(0));
}
void BulkCopyTest(string context, BulkCopyType bulkCopyType)
{
using (var conn = new DataConnection(context))
{
conn.BeginTransaction();
conn.BulkCopy(new BulkCopyOptions {
MaxBatchSize = 50, BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 100).Select(n =>
new AllType
{
ID = 2000 + n,
bigintDataType = 3000 + n,
smallintDataType = (short)(4000 + n),
decimalDataType = 900000 + n,
smalldecimalDataType = 90000 + n,
intDataType = 7000 + n,
tinyintDataType = (byte)(5000 + n),
floatDataType = 7700 + n,
realDataType = 7600 + n,
dateDataType = DateTime.Now,
timeDataType = DateTime.Now - DateTime.Today,
seconddateDataType = DateTime.Now,
timestampDataType = DateTime.Now,
charDataType = 'A',
varcharDataType = "AA",
textDataType = "text",
shorttextDataType = "shorttext",
ncharDataType = '\u00fc',
nvarcharDataType = "A\u00fcfsdf\u00fc",
alphanumDataType = "abcQWE654",
binaryDataType = new byte[] { 1 },
varbinaryDataType = new byte[] { 1, 2, 3 },
blobDataType = new byte[] { 1, 2, 3, 4, 5, 6 },
clobDataType = "clobclobclob",
nclobDataType = "nclob\u00fcnclob\u00fcnclob\u00fc"
}));
conn.GetTable <AllType>().Delete(p => p.ID >= 2000);
}
}
void BulkCopyTest(string context, BulkCopyType bulkCopyType, int maxSize, int batchSize)
{
using (var conn = new DataConnection(context))
{
//conn.BeginTransaction();
conn.BulkCopy(
new BulkCopyOptions
{
MaxBatchSize = maxSize,
BulkCopyType = bulkCopyType,
NotifyAfter = 10000,
RowsCopiedCallback = copied => Debug.WriteLine(copied.RowsCopied)
},
Enumerable.Range(0, batchSize).Select(n =>
new ALLTYPE
{
ID = 2000 + n,
BIGINTDATATYPE = 3000 + n,
INTDATATYPE = 4000 + n,
SMALLINTDATATYPE = (short)(5000 + n),
DECIMALDATATYPE = 6000 + n,
DECFLOATDATATYPE = 7000 + n,
REALDATATYPE = 8000 + n,
DOUBLEDATATYPE = 9000 + n,
CHARDATATYPE = 'A',
VARCHARDATATYPE = "",
CLOBDATATYPE = null,
DBCLOBDATATYPE = null,
BINARYDATATYPE = null,
VARBINARYDATATYPE = null,
BLOBDATATYPE = new byte[] { 1, 2, 3 },
GRAPHICDATATYPE = null,
DATEDATATYPE = DateTime.Now,
TIMEDATATYPE = null,
TIMESTAMPDATATYPE = null,
XMLDATATYPE = null,
}));
//var list = conn.GetTable<ALLTYPE>().ToList();
conn.GetTable <ALLTYPE>().Delete(p => p.SMALLINTDATATYPE >= 5000);
}
}
private static void TestBulkCopy <TTable, TType>(DataConnection db, TTable[] data, TempTable <TTable> table, BulkCopyType bulkCopyType)
where TTable : TypeTable <TType>
where TType : struct
{
table.Delete();
db.BulkCopy(new BulkCopyOptions()
{
BulkCopyType = bulkCopyType
}, data);
var records = table.OrderBy(r => r.Id).ToArray();
Assert.AreEqual(2, records.Length);
Assert.AreEqual(data[0].Id, records[0].Id);
Assert.AreEqual(data[0].Column, records[0].Column);
Assert.AreEqual(data[0].ColumnNullable, records[0].ColumnNullable);
Assert.AreEqual(data[1].Id, records[1].Id);
Assert.AreEqual(data[1].Column, records[1].Column);
Assert.AreEqual(data[1].ColumnNullable, records[1].ColumnNullable);
}
public void BulkCopyTest([Values(
ProviderName.SqlServer, ProviderName.Access, ProviderName.DB2, ProviderName.Firebird,
ProviderName.Informix, ProviderName.MySql, ProviderName.Oracle, ProviderName.PostgreSQL,
ProviderName.SqlCe, ProviderName.SQLite, ProviderName.Sybase)] string configString)
{
using (var db = new DataConnection(configString))
{
db.BulkCopy(
new BulkCopyOptions {
BulkCopyTimeout = 60 * 10
},
Enumerable
.Range(1, 100)
.Select(n => new TestTable
{
Name = n.ToString()
})
);
}
}
public int Add(Employee employee)
{
var identity = _dataConnection.InsertWithIdentity(employee);
int id;
int.TryParse(identity.ToString(), out id);
var territories = employee.EmployeeTerritories;
foreach (var territory in territories)
{
territory.EmployeeId = id;
}
_dataConnection.BulkCopy(new BulkCopyOptions {
BulkCopyType = BulkCopyType.MultipleRows
}, territories);
return(id);
}
// public static int MultipleRowsCopy<T>(DataConnection dataConnection, IEnumerable<T> source, int maxBatchSize = 1000)
// {
// return dataConnection.BulkCopy(
// new BulkCopyOptions
// {
// BulkCopyType = BulkCopyType.MultipleRows,
// MaxBatchSize = maxBatchSize,
// }, source);
// }
public static BulkCopyRowsCopied ProviderSpecificBulkCopy <T>(
DataConnection dataConnection,
IEnumerable <T> source,
int?maxBatchSize = null,
int?bulkCopyTimeout = null,
bool keepIdentity = false,
bool checkConstraints = false,
int notifyAfter = 0,
Action <BulkCopyRowsCopied> rowsCopiedCallback = null)
{
return(dataConnection.BulkCopy(
new BulkCopyOptions
{
BulkCopyType = BulkCopyType.ProviderSpecific,
MaxBatchSize = maxBatchSize,
BulkCopyTimeout = bulkCopyTimeout,
KeepIdentity = keepIdentity,
CheckConstraints = checkConstraints,
NotifyAfter = notifyAfter,
RowsCopiedCallback = rowsCopiedCallback,
}, source));
}
public void BulkCopyLinqTypes(string context)
{
foreach (var bulkCopyType in new[] { BulkCopyType.MultipleRows, BulkCopyType.ProviderSpecific })
{
using (var db = new DataConnection(context))
{
if (bulkCopyType == BulkCopyType.ProviderSpecific)
{
var ms = new MappingSchema();
ms.GetFluentMappingBuilder()
.Entity <LinqDataTypes>()
.Property(e => e.GuidValue)
.IsNotColumn()
;
db.AddMappingSchema(ms);
}
db.BulkCopy(
new BulkCopyOptions {
BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 10).Select(n =>
new LinqDataTypes
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
SmallIntValue = (short)n
}
));
db.GetTable <LinqDataTypes>().Delete(p => p.ID >= 4000);
}
}
}
public void SetUp()
{
_connection = new DataConnection(ProviderName.SQLite, "Data Source=:memory:;");
_connection.CreateTable <LinqDataTypes2>();
var dt = DateTime.Today;
var guid = Guid.NewGuid();
_types = new[]
{
new LinqDataTypes2()
{
ID = 1, DateTimeValue = null, BigIntValue = null, BoolValue = null, DateTimeValue2 = null, GuidValue = null, IntValue = null, MoneyValue = 0, SmallIntValue = null
},
new LinqDataTypes2()
{
ID = 2, DateTimeValue = dt, BigIntValue = 1, BoolValue = false, DateTimeValue2 = dt, GuidValue = guid, IntValue = 1, MoneyValue = 0, SmallIntValue = 1
}
};
_connection.BulkCopy(_types);
}
public void BulkCopyProviderSpecificLowerCaseColumns(string context)
{
using (var db = new DataConnection(context))
{
var result = db.BulkCopy(
new BulkCopyOptions {
BulkCopyType = BulkCopyType.ProviderSpecific
},
Enumerable.Range(0, 10).Select(n =>
new BulkInsertLowerCaseColumns
{
ID = 4000 + n,
MoneyValue = 1000m + n,
DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100),
BoolValue = true,
GuidValue = Guid.NewGuid(),
SmallIntValue = (short)n
}
));
Assert.That(result.RowsCopied, Is.EqualTo(10));
var count = db.GetTable <BulkInsertLowerCaseColumns>().Delete(p => p.ID >= 4000);
Assert.That(count, Is.EqualTo(10));
}
}
public long InsertMultiple <T>(IEnumerable <T> rows)
{
var result = connection.BulkCopy(rows);
return(result.RowsCopied);
}
public static async Task <int> TestBulkCopy <TEntry>(this DataConnection l2db, TEntry entry) where TEntry : BTest, new()
=> (int)l2db.BulkCopy(_bulkCopyOptions, new TEntry[] { entry }).RowsCopied;
public BulkCopyRowsCopied BulkCopy <T>(BulkCopyOptions options, IEnumerable <T> source) where T : class
{
return(_dataConnection.BulkCopy(options, source));
}
