public async Task UpdateExistingRows(int count, int rowsToChange, int iterations)
{
var rows = Enumerable.Range(1, count)
.Select(i => new SomeDataRow(i, i * 2.3m, $"This is row {i}"))
.ToList();
var upsertHelper = new UpsertHelper <SomeDataRow>(ConnectionString);
upsertHelper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
upsertHelper.CreateSchema();
await upsertHelper.UpsertAsync(rows);
var recordedExecutionTimes = new Queue <TimeSpan>();
for (var counter = 0; counter < 10; counter++)
{
var stopwatch = Stopwatch.StartNew();
var updatedRows = rows.InRandomOrder().Take(rowsToChange)
.Select(row => new SomeDataRow(row.Id, row.Number + 1, string.Concat(row.Text, "-HEJ")));
await upsertHelper.UpsertAsync(updatedRows);
var elapsed = stopwatch.Elapsed;
recordedExecutionTimes.Enqueue(elapsed);
}
var averageExecutionTimeSeconds = recordedExecutionTimes.Average(t => t.TotalSeconds);
Console.WriteLine($"Updating {rowsToChange} random rows in dataset of {count} took average of {averageExecutionTimeSeconds:0.0} s - that's {rowsToChange / averageExecutionTimeSeconds:0.0} rows/s");
}
public void Checkperson()
{
var helper = new UpsertHelper <Person>(ConnectionString);
helper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
helper.CreateSchema();
}
protected override void SetUp()
{
_helper = new UpsertHelper <CanContainNullValue>(ConnectionString);
_helper.DropSchema(dropProcedure: true, dropTable: true, dropType: true);
_helper.CreateSchema();
}
public async Task CanUseGuidsAsNames()
{
var tableName = Guid.NewGuid().ToString("N");
var typeName = Guid.NewGuid().ToString("N");
var procName = Guid.NewGuid().ToString("N");
var helper = new UpsertHelper <SomeModel>(ConnectionString, tableName: tableName, procName: procName, typeName: typeName);
helper.DropSchema(dropType: true, dropTable: true, dropProcedure: true);
helper.CreateSchema();
await helper.UpsertAsync(new[] { new SomeModel {
Id = 123, Text = "nummer 123"
} });
var rows = helper.LoadAll().ToList();
Assert.That(rows.Count, Is.EqualTo(1));
var row = rows.First();
Assert.That(row.Id, Is.EqualTo(123));
Assert.That(row.Text, Is.EqualTo("nummer 123"));
await using var connection = await OpenSqlConnection();
var schemas = connection.GetSchemas().ToList();
Assert.That(schemas, Contains.Item("dbo"));
Assert.That(connection.GetTableNames(schema: "dbo"), Contains.Item(tableName));
Assert.That(connection.GetTableDataTypeNames(schema: "dbo"), Contains.Item(typeName));
Assert.That(connection.GetSprocNames(schema: "dbo"), Contains.Item(procName));
}
protected override void SetUp()
{
_helper = new UpsertHelper <HasIgnoredProperty>(ConnectionString);
_helper.DropSchema(dropTable: true, dropType: true, dropProcedure: true);
_helper.CreateSchema();
}
public async Task ItWorks()
{
var helper = new UpsertHelper <SomeClassWithDateTimeOffset>(ConnectionString);
helper.DropSchema(dropType: true, dropTable: true, dropProcedure: true);
helper.CreateSchema();
var now = DateTimeOffset.Now;
await helper.UpsertAsync(new[]
{
new SomeClassWithDateTimeOffset {
Id = "1", Time = now
},
new SomeClassWithDateTimeOffset {
Id = "2", Time = now
},
new SomeClassWithDateTimeOffset {
Id = "3", Time = now
},
});
var all = helper.LoadAll();
Assert.That(all.Count(), Is.EqualTo(3));
}
protected override void SetUp()
{
_upsertHelper = new UpsertHelper <RowWithData>(ConnectionString);
_upsertHelper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
_upsertHelper.CreateSchema();
}
public async Task CanDoIt()
{
var upsertHelper = new UpsertHelper <TeamRow>(ConnectionString);
upsertHelper.DropSchema(dropType: true, dropTable: true, dropProcedure: true);
upsertHelper.CreateSchema();
await upsertHelper.UpsertAsync(new[]
{
new TeamRow(new Team("team1", "Team c0ffeebada55", new List <string> {
"t-team1-1", "t-team1-2"
})),
new TeamRow(new Team("team2", "Team c0ffeebada55", new List <string> {
"t-team2-1"
})),
new TeamRow(new Team("team3", "Team c0ffeebada55", new List <string> {
"t-team3-1", "t-team3-2", "t-team3-3"
})),
new TeamRow(new Team("team4", "Team c0ffeebada55", Enumerable.Range(1, 200).Select(n => $"t-team4-{n}").ToList())),
new TeamRow(new Team("team5", "Team c0ffeebada55", new List <string> {
"t-team5-1", "t-team5-2"
})),
});
var team = (await upsertHelper.LoadWhereAsync("[AccountIds] like '%;t-team4-200;%'")).FirstOrDefault();
Assert.That(team, Is.Not.Null);
Assert.That(team.Id, Is.EqualTo("team4"));
Assert.That(team.Entity.AccountIds.Count, Is.EqualTo(200));
}
public async Task CanGetCreationSchema()
{
var helper = new UpsertHelper <Something>(ConnectionString);
Console.WriteLine(helper.GetCreateSchemaScript());
Console.WriteLine(helper.GetDropSchemaScript(dropProcedure: true, dropTable: true, dropType: true));
}
public async Task ItWorks()
{
var helper = new UpsertHelper <SomeClassWithDecimal>(ConnectionString);
helper.DropSchema(dropType: true, dropTable: true, dropProcedure: true);
helper.CreateSchema();
await helper.UpsertAsync(new[]
{
new SomeClassWithDecimal {
Decimal = 1.1m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.12m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.123m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.1234m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.12345m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.123456m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.1234567m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.12345678m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.123456789m, Id = Guid.NewGuid().ToString()
},
new SomeClassWithDecimal {
Decimal = 1.1234567891m, Id = Guid.NewGuid().ToString()
},
});
var all = helper.LoadAll().OrderBy(d => d.Decimal).ToList();
Assert.That(all.Select(a => a.Decimal), Is.EqualTo(new[]
{
1.1m,
1.12m,
1.123m,
1.1234m,
1.12345m,
1.123456m,
1.1234567m,
1.12345678m,
1.123456789m,
1.1234567891m,
}));
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsStandardSectorCodes query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[] { nameof(UpsertLarsStandardSectorCodeRecord.StandardSectorCode) },
"Pttcd.StandardSectorCodes");
return(new None());
}
protected override void SetUp()
{
_upsertHelper = new UpsertHelper <SomeRowWithIntegerRevision>(ConnectionString);
_upsertHelper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
_upsertHelper.CreateSchema();
_upsertHelper2 = new UpsertHelper <SomeRowWithDateTimeRevision>(ConnectionString);
_upsertHelper2.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
_upsertHelper2.CreateSchema();
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsLearnAimRefTypes query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[] { nameof(UpsertLarsLearnAimRefTypesRecord.LearnAimRefType) },
"LARS.LearnAimRefType");
return(new None());
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsCategories query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[] { nameof(UpsertLarsCategoriesRecord.CategoryRef) },
"LARS.Category");
return(new None());
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsSectorSubjectAreaTier2s query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[] { nameof(UpsertLarsSectorSubjectAreaTier2sRecord.SectorSubjectAreaTier2) },
"LARS.SectorSubjectAreaTier2");
return(new None());
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsAwardOrgCodes query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[] { nameof(UpsertLarsAwardOrgCodesRecord.AwardOrgCode) },
"LARS.AwardOrgCode");
return(new None());
}
protected override void SetUp()
{
base.SetUp();
_helper = new UpsertHelper <SomethingToSave>(ConnectionString);
var helper = new UpsertHelper <SomethingToSave>(ConnectionString);
helper.DropSchema(dropProcedure: true, dropTable: true, dropType: true);
helper.CreateSchema();
}
public async Task <None> Execute(SqlTransaction transaction, UpsertLarsLearningDeliveryCategories query)
{
await UpsertHelper.Upsert(
transaction,
query.Records,
keyPropertyNames : new[]
{
nameof(UpsertLarsLearningDeliveryCategoriesRecord.CategoryRef),
nameof(UpsertLarsLearningDeliveryCategoriesRecord.LearnAimRef),
nameof(UpsertLarsLearningDeliveryCategoriesRecord.EffectiveFrom)
},
"LARS.LearningDeliveryCategory");
return(new None());
}
public async Task InsertRows(int count)
{
var rows = Enumerable.Range(1, count)
.Select(i => new SomeDataRow(i, i * 2.3m, $"This is row {i}"))
.ToList();
var upsertHelper = new UpsertHelper <SomeDataRow>(ConnectionString);
upsertHelper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
upsertHelper.CreateSchema();
var stopwatch = Stopwatch.StartNew();
await upsertHelper.UpsertAsync(rows);
var elapsedSeconds = stopwatch.Elapsed.TotalSeconds;
Console.WriteLine($"Upserting {rows.Count} rows took {elapsedSeconds} - that's {rows.Count / elapsedSeconds:0.0} rows/s");
}
public async Task CanRoundtripThisBadBoy()
{
var helper = new UpsertHelper <SomeKindOfLine>(ConnectionString);
helper.DropSchema(dropProcedure: true, dropTable: true, dropType: true);
helper.CreateSchema();
await helper.UpsertAsync(new[] { new SomeKindOfLine(123, 789, 505, TimeZoneInfo.Local) });
var roundtripped = helper.LoadAll().ToList();
Assert.That(roundtripped.Count, Is.EqualTo(1));
var line = roundtripped.First();
Assert.That(line.SecondId, Is.EqualTo(789));
Assert.That(line.FirstId, Is.EqualTo(123));
Assert.That(line.DecimalNumber, Is.EqualTo(505));
Assert.That(line.Timezone, Is.EqualTo(TimeZoneInfo.Local));
}
protected override void SetUp()
{
_upsertHelper = new UpsertHelper <CurrencyCrossRates>(ConnectionString);
_upsertHelper.DropSchema(dropTable: true, dropProcedure: true, dropType: true);
_upsertHelper.CreateSchema();
}
