private static void InsertUsingBulkInsert(int numberOfRows, bool useLinq, bool omitTableName)
{
var watch = Stopwatch.StartNew();
using (var dbct = new DemoDbContext(_connectionString))
{
var rows = new List <Row>();
var compositeKeyRows = new List <CompositeKeyRow>();
for (int i = 0; i < numberOfRows; i++)
{
rows.Add(new Row
{
Column1 = i,
Column2 = "" + i,
Column3 = DateTime.Now
});
compositeKeyRows.Add(new CompositeKeyRow
{
Id1 = i,
Id2 = i,
Column1 = i,
Column2 = "" + i,
Column3 = DateTime.Now
});
}
if (useLinq)
{
if (omitTableName)
{
dbct.BulkInsert(rows,
row => new { row.Column1, row.Column2, row.Column3 },
row => row.Id);
dbct.BulkInsert(compositeKeyRows,
row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
}
else
{
dbct.BulkInsert(rows, "Rows",
row => new { row.Column1, row.Column2, row.Column3 },
row => row.Id);
dbct.BulkInsert(compositeKeyRows, "CompositeKeyRows",
row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
}
}
else
{
if (omitTableName)
{
dbct.BulkInsert(rows,
new string[] { "Column1", "Column2", "Column3" });
dbct.BulkInsert(rows.Take(1000),
typeof(Row).GetDbColumnNames("Id"));
dbct.BulkInsert(compositeKeyRows,
new string[] { "Id1", "Id2", "Column1", "Column2", "Column3" });
}
else
{
dbct.BulkInsert(rows, "Rows",
new string[] { "Column1", "Column2", "Column3" });
dbct.BulkInsert(rows.Take(1000), "Rows",
typeof(Row).GetDbColumnNames("Id"));
dbct.BulkInsert(compositeKeyRows, "CompositeKeyRows",
new string[] { "Id1", "Id2", "Column1", "Column2", "Column3" });
}
}
}
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
private static void UpdateUsingBulkUpdate(bool useLinq, bool omitTableName)
{
var watch = Stopwatch.StartNew();
using (var dbct = new DemoDbContext(_connectionString))
{
var rows = dbct.Rows.AsNoTracking().ToList();
var compositeKeyRows = dbct.CompositeKeyRows.AsNoTracking().ToList();
foreach (var row in rows)
{
row.Column2 = "abc";
row.Column3 = DateTime.Now;
}
foreach (var row in compositeKeyRows)
{
row.Column2 = "abc";
row.Column3 = DateTime.Now;
}
if (useLinq)
{
if (omitTableName)
{
dbct.BulkUpdate(rows,
row => row.Id,
row => new { row.Column3, row.Column2 });
dbct.BulkUpdate(compositeKeyRows,
row => new { row.Id1, row.Id2 },
row => new { row.Column3, row.Column2 });
}
else
{
dbct.BulkUpdate(rows, "Rows",
row => row.Id,
row => new { row.Column3, row.Column2 });
dbct.BulkUpdate(compositeKeyRows, "CompositeKeyRows",
row => new { row.Id1, row.Id2 },
row => new { row.Column3, row.Column2 });
}
var newId = rows.Max(x => x.Id) + 1;
rows.Add(new Row
{
Id = newId,
Column1 = newId,
Column2 = "Inserted using Merge" + newId,
Column3 = DateTime.Now,
});
var newId1 = compositeKeyRows.Max(x => x.Id1) + 1;
var newId2 = compositeKeyRows.Max(x => x.Id2) + 1;
compositeKeyRows.Add(new CompositeKeyRow
{
Id1 = newId1,
Id2 = newId2,
Column1 = newId2,
Column2 = "Inserted using Merge" + newId2,
Column3 = DateTime.Now,
});
if (omitTableName)
{
dbct.BulkMerge(rows,
row => row.Id,
row => new { row.Column1, row.Column2 },
row => new { row.Column1, row.Column2, row.Column3 });
dbct.BulkMerge(compositeKeyRows,
row => new { row.Id1, row.Id2 },
row => new { row.Column1, row.Column2, row.Column3 },
row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
}
else
{
dbct.BulkMerge(rows, "Rows",
row => row.Id,
row => new { row.Column1, row.Column2 },
row => new { row.Column1, row.Column2, row.Column3 });
dbct.BulkMerge(compositeKeyRows, "CompositeKeyRows",
row => new { row.Id1, row.Id2 },
row => new { row.Column1, row.Column2, row.Column3 },
row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
}
}
else
{
if (omitTableName)
{
dbct.BulkUpdate(rows,
"Id",
new string[] { "Column3", "Column2" });
dbct.BulkUpdate(compositeKeyRows,
new string[] { "Id1", "Id2" },
new string[] { "Column3", "Column2" });
}
else
{
dbct.BulkUpdate(rows, "Rows",
"Id",
new string[] { "Column3", "Column2" });
dbct.BulkUpdate(compositeKeyRows, "CompositeKeyRows",
new string[] { "Id1", "Id2" },
new string[] { "Column3", "Column2" });
}
var newId = rows.Max(x => x.Id) + 1;
rows.Add(new Row
{
Id = newId,
Column1 = newId,
Column2 = "Inserted using Merge" + newId,
Column3 = DateTime.Now,
});
var newId1 = compositeKeyRows.Max(x => x.Id1) + 1;
var newId2 = compositeKeyRows.Max(x => x.Id2) + 1;
compositeKeyRows.Add(new CompositeKeyRow
{
Id1 = newId1,
Id2 = newId2,
Column1 = newId2,
Column2 = "Inserted using Merge" + newId2,
Column3 = DateTime.Now,
});
if (omitTableName)
{
dbct.BulkMerge(rows,
"Id",
new string[] { "Column1", "Column2" },
new string[] { "Column1", "Column2", "Column3" });
dbct.BulkMerge(compositeKeyRows,
new string[] { "Id1", "Id2" },
new string[] { "Column1", "Column2", "Column3" },
new string[] { "Id1", "Id2", "Column1", "Column2", "Column3" });
}
else
{
dbct.BulkMerge(rows, "Rows",
"Id",
new string[] { "Column1", "Column2" },
new string[] { "Column1", "Column2", "Column3" });
dbct.BulkMerge(compositeKeyRows, "CompositeKeyRows",
new string[] { "Id1", "Id2" },
new string[] { "Column1", "Column2", "Column3" },
new string[] { "Id1", "Id2", "Column1", "Column2", "Column3" });
}
}
}
watch.Stop();
Console.WriteLine(watch.Elapsed);
}