public async Task UpdateAllColumns()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
using (var builder = GetLocalSpiderHostBuilder())
{
var provider = builder.Build();
var services = provider.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.InsertIgnoreDuplicate);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4> {
entity
};
dfc.AddParseData(typeName, items);
await storage.HandleAsync(dfc);
var dfc2 = new DataFlowContext(null, services);
dfc2.Add(typeName, entity.GetTableMetadata());
var now = DateTime.Now;
dfc2.AddParseData(typeName,
new ParseResult <CreateTableEntity4>
{
new CreateTableEntity4
{
Str1 = "TTT", DateTime = now, DateTimeOffset = now, Double = 888
}
});
var storage2 = CreateStorage(StorageType.Update);
await storage2.HandleAsync(dfc2);
var list = (await conn.QueryAsync <CreateTableEntity4>(
$"SELECT * FROM {Escape}test{Escape}.{Escape}createtableprimay{Escape}"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("TTT", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(888, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
}
}
}
public async Task Primary()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
using (var builder = GetLocalSpiderHostBuilder())
{
var provider = builder.Build();
var services = provider.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.Insert);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4> {
entity
};
dfc.AddParseData(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>(
$"SELECT * FROM {Escape}test{Escape}.{Escape}createtableprimay{Escape}"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
var primaries = (await conn.QueryAsync <PrimaryInfo>(
"SELECT t.CONSTRAINT_TYPE, c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = 'test' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' AND t.TABLE_NAME='createtableprimay';")
).ToList();
Assert.Single(primaries);
Assert.Equal("str2", primaries[0].COLUMN_NAME);
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
}
}
}
public async Task InsertAndUpdate()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.InsertAndUpdate);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4>
{
entity,
new CreateTableEntity4
{
Str1 = "zzz"
}
};
dfc.AddParseData(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>(
$"SELECT * FROM {Escape}test{Escape}.{Escape}createtableprimay{Escape}"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("zzz", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
}
}
public async Task CreateTablePrimary()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
using (var builder = GetLocalSpiderHostBuilder())
{
var provider = builder.Build();
var services = provider.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.Insert);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4> {
entity
};
dfc.AddParseData(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>("SELECT * FROM test.dbo.createtableprimay"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
var primaries = (await conn.QueryAsync <IndexInfo>
(@"USE test; EXEC sp_pkeys @table_name='createtableprimay'")
).ToList();
Assert.Single(primaries);
Assert.Equal("str2", primaries[0].COLUMN_NAME);
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
}
}
}
public async Task UseTransaction()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
using (var builder = GetLocalSpiderHostBuilder())
{
var provider = builder.Build();
var services = provider.CreateScopeServiceProvider();
var storage = (RelationalDatabaseEntityStorageBase)CreateStorage(StorageType.InsertIgnoreDuplicate);
storage.UseTransaction = true;
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4> {
entity, entity, entity
};
dfc.AddParseData(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>("SELECT * FROM test.dbo.createtableprimay"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
}
}
}
public async Task InsertIgnoreDuplicate()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists `test`.`createtableprimay`;");
var services = SpiderFactory.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.InsertIgnoreDuplicate);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4>
{
entity,
entity,
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>("SELECT * FROM `test`.`createtableprimay`"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync("drop table if exists `test`.`createtableprimay`;");
}
}
