mirror of
https://github.com/DapperLib/Dapper.git
synced 2026-10-17 05:12:13 +08:00
Nick Craver
ae7a29a9f1
Due to the way VS test works (by injecting an executable entry point), the performance tests needed to be factored out. I also organized all of our existing tests better along the way. Actual code changes to Dapper itself are very minor, only formatting and documentation fixes (which we need many more of). The build.ps1 script is upated to work, but note that <frameworkAssemblies> is not working in .nuspec inside the packages in the new .csproj system. I consider this to be a blocker. Issue is here: https://github.com/NuGet/Home/issues/4853
797 lines
33 KiB
C#
797 lines
33 KiB
C#
using System.Linq;
|
|
using System.Data;
|
|
using System.Diagnostics;
|
|
using System;
|
|
using System.Threading.Tasks;
|
|
using System.Threading;
|
|
using System.Data.SqlClient;
|
|
using Xunit;
|
|
|
|
namespace Dapper.Tests
|
|
{
|
|
public class Tests : TestBase
|
|
{
|
|
private SqlConnection _marsConnection;
|
|
private SqlConnection marsConnection => _marsConnection ?? (_marsConnection = GetOpenConnection(true));
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageAsync()
|
|
{
|
|
var query = await connection.QueryAsync<string>("select 'abc' as [Value] union all select @txt", new { txt = "def" }).ConfigureAwait(false);
|
|
var arr = query.ToArray();
|
|
arr.IsSequenceEqualTo(new[] { "abc", "def" });
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageQueryFirstAsync()
|
|
{
|
|
var str = await connection.QueryFirstAsync<string>(new CommandDefinition("select 'abc' as [Value] union all select @txt", new {txt = "def"})).ConfigureAwait(false);
|
|
str.IsEqualTo("abc");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageQueryFirstOrDefaultAsync()
|
|
{
|
|
var str = await connection.QueryFirstOrDefaultAsync<string>(new CommandDefinition("select null as [Value] union all select @txt", new {txt = "def"})).ConfigureAwait(false);
|
|
str.IsNull();
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageQuerySingleAsync()
|
|
{
|
|
var str = await connection.QuerySingleAsync<string>(new CommandDefinition("select 'abc' as [Value]")).ConfigureAwait(false);
|
|
str.IsEqualTo("abc");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageQuerySingleOrDefaultAsync()
|
|
{
|
|
var str = await connection.QuerySingleAsync<string>(new CommandDefinition("select null as [Value]")).ConfigureAwait(false);
|
|
str.IsNull();
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageAsyncNonBuffered()
|
|
{
|
|
var query = await connection.QueryAsync<string>(new CommandDefinition("select 'abc' as [Value] union all select @txt", new { txt = "def" }, flags: CommandFlags.None)).ConfigureAwait(false);
|
|
var arr = query.ToArray();
|
|
arr.IsSequenceEqualTo(new[] { "abc", "def" });
|
|
}
|
|
|
|
[Fact]
|
|
public void TestLongOperationWithCancellation()
|
|
{
|
|
CancellationTokenSource cancel = new CancellationTokenSource(TimeSpan.FromSeconds(5));
|
|
var task = connection.QueryAsync<int>(new CommandDefinition("waitfor delay '00:00:10';select 1", cancellationToken: cancel.Token));
|
|
try
|
|
{
|
|
if (!task.Wait(TimeSpan.FromSeconds(7)))
|
|
{
|
|
throw new TimeoutException(); // should have cancelled
|
|
}
|
|
}
|
|
catch (AggregateException agg)
|
|
{
|
|
(agg.InnerException is SqlException).IsTrue();
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestBasicStringUsageClosedAsync()
|
|
{
|
|
var query = await connection.QueryAsync<string>("select 'abc' as [Value] union all select @txt", new { txt = "def" }).ConfigureAwait(false);
|
|
var arr = query.ToArray();
|
|
arr.IsSequenceEqualTo(new[] { "abc", "def" });
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestQueryDynamicAsync()
|
|
{
|
|
var row = (await connection.QueryAsync("select 'abc' as [Value]").ConfigureAwait(false)).Single();
|
|
string value = row.Value;
|
|
value.IsEqualTo("abc");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestClassWithStringUsageAsync()
|
|
{
|
|
var query = await connection.QueryAsync<BasicType>("select 'abc' as [Value] union all select @txt", new { txt = "def" }).ConfigureAwait(false);
|
|
var arr = query.ToArray();
|
|
arr.Select(x => x.Value).IsSequenceEqualTo(new[] { "abc", "def" });
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestExecuteAsync()
|
|
{
|
|
var val = await connection.ExecuteAsync("declare @foo table(id int not null); insert @foo values(@id);", new { id = 1 }).ConfigureAwait(false);
|
|
val.Equals(1);
|
|
}
|
|
|
|
[Fact]
|
|
public void TestExecuteClosedConnAsync()
|
|
{
|
|
var query = connection.ExecuteAsync("declare @foo table(id int not null); insert @foo values(@id);", new { id = 1 });
|
|
var val = query.Result;
|
|
val.Equals(1);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiMapWithSplitAsync()
|
|
{
|
|
const string sql = @"select 1 as id, 'abc' as name, 2 as id, 'def' as name";
|
|
var productQuery = await connection.QueryAsync<Product, Category, Product>(sql, (prod, cat) =>
|
|
{
|
|
prod.Category = cat;
|
|
return prod;
|
|
}).ConfigureAwait(false);
|
|
|
|
var product = productQuery.First();
|
|
// assertions
|
|
product.Id.IsEqualTo(1);
|
|
product.Name.IsEqualTo("abc");
|
|
product.Category.Id.IsEqualTo(2);
|
|
product.Category.Name.IsEqualTo("def");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiMapArbitraryWithSplitAsync()
|
|
{
|
|
const string sql = @"select 1 as id, 'abc' as name, 2 as id, 'def' as name";
|
|
var productQuery = await connection.QueryAsync<Product>(sql, new[] { typeof(Product), typeof(Category) }, (objects) => {
|
|
var prod = (Product)objects[0];
|
|
prod.Category = (Category)objects[1];
|
|
return prod;
|
|
}).ConfigureAwait(false);
|
|
|
|
var product = productQuery.First();
|
|
// assertions
|
|
product.Id.IsEqualTo(1);
|
|
product.Name.IsEqualTo("abc");
|
|
product.Category.Id.IsEqualTo(2);
|
|
product.Category.Name.IsEqualTo("def");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiMapWithSplitClosedConnAsync()
|
|
{
|
|
const string sql = @"select 1 as id, 'abc' as name, 2 as id, 'def' as name";
|
|
using (var conn = GetClosedConnection())
|
|
{
|
|
var productQuery = await conn.QueryAsync<Product, Category, Product>(sql, (prod, cat) =>
|
|
{
|
|
prod.Category = cat;
|
|
return prod;
|
|
}).ConfigureAwait(false);
|
|
|
|
var product = productQuery.First();
|
|
// assertions
|
|
product.Id.IsEqualTo(1);
|
|
product.Name.IsEqualTo("abc");
|
|
product.Category.Id.IsEqualTo(2);
|
|
product.Category.Name.IsEqualTo("def");
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiAsync()
|
|
{
|
|
using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2").ConfigureAwait(false))
|
|
{
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(1);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(2);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiAsyncViaFirstOrDefault()
|
|
{
|
|
using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2; select 3; select 4; select 5").ConfigureAwait(false))
|
|
{
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(1);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(2);
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(3);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(4);
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(5);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiClosedConnAsync()
|
|
{
|
|
using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2").ConfigureAwait(false))
|
|
{
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(1);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(2);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiClosedConnAsyncViaFirstOrDefault()
|
|
{
|
|
using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2; select 3; select 4; select 5;").ConfigureAwait(false))
|
|
{
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(1);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(2);
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(3);
|
|
multi.ReadAsync<int>().Result.Single().IsEqualTo(4);
|
|
multi.ReadFirstOrDefaultAsync<int>().Result.IsEqualTo(5);
|
|
}
|
|
}
|
|
|
|
#if !COREFX
|
|
[Fact]
|
|
public async Task ExecuteReaderOpenAsync()
|
|
{
|
|
var dt = new DataTable();
|
|
dt.Load(await connection.ExecuteReaderAsync("select 3 as [three], 4 as [four]").ConfigureAwait(false));
|
|
dt.Columns.Count.IsEqualTo(2);
|
|
dt.Columns[0].ColumnName.IsEqualTo("three");
|
|
dt.Columns[1].ColumnName.IsEqualTo("four");
|
|
dt.Rows.Count.IsEqualTo(1);
|
|
((int)dt.Rows[0][0]).IsEqualTo(3);
|
|
((int)dt.Rows[0][1]).IsEqualTo(4);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task ExecuteReaderClosedAsync()
|
|
{
|
|
using (var conn = GetClosedConnection())
|
|
{
|
|
var dt = new DataTable();
|
|
dt.Load(await conn.ExecuteReaderAsync("select 3 as [three], 4 as [four]").ConfigureAwait(false));
|
|
dt.Columns.Count.IsEqualTo(2);
|
|
dt.Columns[0].ColumnName.IsEqualTo("three");
|
|
dt.Columns[1].ColumnName.IsEqualTo("four");
|
|
dt.Rows.Count.IsEqualTo(1);
|
|
((int)dt.Rows[0][0]).IsEqualTo(3);
|
|
((int)dt.Rows[0][1]).IsEqualTo(4);
|
|
}
|
|
}
|
|
#endif
|
|
|
|
[Fact]
|
|
public async Task LiteralReplacementOpen()
|
|
{
|
|
await LiteralReplacement(connection).ConfigureAwait(false);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LiteralReplacementClosed()
|
|
{
|
|
using (var conn = GetClosedConnection()) await LiteralReplacement(conn).ConfigureAwait(false);
|
|
}
|
|
|
|
private async Task LiteralReplacement(IDbConnection conn)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync("drop table literal1").ConfigureAwait(false);
|
|
}
|
|
catch { /* don't care */ }
|
|
await conn.ExecuteAsync("create table literal1 (id int not null, foo int not null)").ConfigureAwait(false);
|
|
await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", new { id = 123, foo = 456 }).ConfigureAwait(false);
|
|
var rows = new[] { new { id = 1, foo = 2 }, new { id = 3, foo = 4 } };
|
|
await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", rows).ConfigureAwait(false);
|
|
var count = (await conn.QueryAsync<int>("select count(1) from literal1 where id={=foo}", new { foo = 123 }).ConfigureAwait(false)).Single();
|
|
count.IsEqualTo(1);
|
|
int sum = (await conn.QueryAsync<int>("select sum(id) + sum(foo) from literal1").ConfigureAwait(false)).Single();
|
|
sum.IsEqualTo(123 + 456 + 1 + 2 + 3 + 4);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LiteralReplacementDynamicOpen()
|
|
{
|
|
await LiteralReplacementDynamic(connection).ConfigureAwait(false);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LiteralReplacementDynamicClosed()
|
|
{
|
|
using (var conn = GetClosedConnection()) await LiteralReplacementDynamic(conn).ConfigureAwait(false);
|
|
}
|
|
|
|
private async Task LiteralReplacementDynamic(IDbConnection conn)
|
|
{
|
|
var args = new DynamicParameters();
|
|
args.Add("id", 123);
|
|
try { await conn.ExecuteAsync("drop table literal2").ConfigureAwait(false); }
|
|
catch { /* don't care */ }
|
|
await conn.ExecuteAsync("create table literal2 (id int not null)").ConfigureAwait(false);
|
|
await conn.ExecuteAsync("insert literal2 (id) values ({=id})", args).ConfigureAwait(false);
|
|
|
|
args = new DynamicParameters();
|
|
args.Add("foo", 123);
|
|
var count = (await conn.QueryAsync<int>("select count(1) from literal2 where id={=foo}", args).ConfigureAwait(false)).Single();
|
|
count.IsEqualTo(1);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LiteralInAsync()
|
|
{
|
|
await connection.ExecuteAsync("create table #literalin(id int not null);").ConfigureAwait(false);
|
|
await connection.ExecuteAsync("insert #literalin (id) values (@id)", new[] {
|
|
new { id = 1 },
|
|
new { id = 2 },
|
|
new { id = 3 },
|
|
}).ConfigureAwait(false);
|
|
var count = (await connection.QueryAsync<int>("select count(1) from #literalin where id in {=ids}",
|
|
new { ids = new[] { 1, 3, 4 } }).ConfigureAwait(false)).Single();
|
|
count.IsEqualTo(2);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task RunSequentialVersusParallelAsync()
|
|
{
|
|
var ids = Enumerable.Range(1, 20000).Select(id => new { id }).ToArray();
|
|
await marsConnection.ExecuteAsync(new CommandDefinition("select @id", ids.Take(5), flags: CommandFlags.None)).ConfigureAwait(false);
|
|
|
|
var watch = Stopwatch.StartNew();
|
|
await marsConnection.ExecuteAsync(new CommandDefinition("select @id", ids, flags: CommandFlags.None)).ConfigureAwait(false);
|
|
watch.Stop();
|
|
Console.WriteLine("No pipeline: {0}ms", watch.ElapsedMilliseconds);
|
|
|
|
watch = Stopwatch.StartNew();
|
|
await marsConnection.ExecuteAsync(new CommandDefinition("select @id", ids, flags: CommandFlags.Pipelined)).ConfigureAwait(false);
|
|
watch.Stop();
|
|
Console.WriteLine("Pipeline: {0}ms", watch.ElapsedMilliseconds);
|
|
}
|
|
|
|
[Fact]
|
|
public void RunSequentialVersusParallelSync()
|
|
{
|
|
var ids = Enumerable.Range(1, 20000).Select(id => new { id }).ToArray();
|
|
marsConnection.Execute(new CommandDefinition("select @id", ids.Take(5), flags: CommandFlags.None));
|
|
|
|
var watch = Stopwatch.StartNew();
|
|
marsConnection.Execute(new CommandDefinition("select @id", ids, flags: CommandFlags.None));
|
|
watch.Stop();
|
|
Console.WriteLine("No pipeline: {0}ms", watch.ElapsedMilliseconds);
|
|
|
|
watch = Stopwatch.StartNew();
|
|
marsConnection.Execute(new CommandDefinition("select @id", ids, flags: CommandFlags.Pipelined));
|
|
watch.Stop();
|
|
Console.WriteLine("Pipeline: {0}ms", watch.ElapsedMilliseconds);
|
|
}
|
|
|
|
[Fact]
|
|
public void AssertNoCacheWorksForQueryMultiple()
|
|
{
|
|
const int a = 123, b = 456;
|
|
var cmdDef = new CommandDefinition(@"select @a; select @b;", new
|
|
{
|
|
a, b
|
|
}, commandType: CommandType.Text, flags: CommandFlags.NoCache);
|
|
|
|
int c, d;
|
|
SqlMapper.PurgeQueryCache();
|
|
int before = SqlMapper.GetCachedSQLCount();
|
|
using (var multi = marsConnection.QueryMultiple(cmdDef))
|
|
{
|
|
c = multi.Read<int>().Single();
|
|
d = multi.Read<int>().Single();
|
|
}
|
|
int after = SqlMapper.GetCachedSQLCount();
|
|
before.IsEqualTo(0);
|
|
after.IsEqualTo(0);
|
|
c.IsEqualTo(123);
|
|
d.IsEqualTo(456);
|
|
}
|
|
|
|
private class BasicType
|
|
{
|
|
public string Value { get; set; }
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TypeBasedViaTypeAsync()
|
|
{
|
|
Type type = Common.GetSomeType();
|
|
|
|
dynamic actual = (await marsConnection.QueryAsync(type, "select @A as [A], @B as [B]", new { A = 123, B = "abc" }).ConfigureAwait(false)).FirstOrDefault();
|
|
((object)actual).GetType().IsEqualTo(type);
|
|
int a = actual.A;
|
|
string b = actual.B;
|
|
a.IsEqualTo(123);
|
|
b.IsEqualTo("abc");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TypeBasedViaTypeAsyncFirstOrDefault()
|
|
{
|
|
Type type = Common.GetSomeType();
|
|
|
|
dynamic actual = await marsConnection.QueryFirstOrDefaultAsync(type, "select @A as [A], @B as [B]", new { A = 123, B = "abc" }).ConfigureAwait(false);
|
|
((object)actual).GetType().IsEqualTo(type);
|
|
int a = actual.A;
|
|
string b = actual.B;
|
|
a.IsEqualTo(123);
|
|
b.IsEqualTo("abc");
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Issue22_ExecuteScalarAsync()
|
|
{
|
|
int i = await connection.ExecuteScalarAsync<int>("select 123").ConfigureAwait(false);
|
|
i.IsEqualTo(123);
|
|
|
|
i = await connection.ExecuteScalarAsync<int>("select cast(123 as bigint)").ConfigureAwait(false);
|
|
i.IsEqualTo(123);
|
|
|
|
long j = await connection.ExecuteScalarAsync<long>("select 123").ConfigureAwait(false);
|
|
j.IsEqualTo(123L);
|
|
|
|
j = await connection.ExecuteScalarAsync<long>("select cast(123 as bigint)").ConfigureAwait(false);
|
|
j.IsEqualTo(123L);
|
|
|
|
int? k = await connection.ExecuteScalarAsync<int?>("select @i", new { i = default(int?) }).ConfigureAwait(false);
|
|
k.IsNull();
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Issue346_QueryAsyncConvert()
|
|
{
|
|
int i = (await connection.QueryAsync<int>("Select Cast(123 as bigint)").ConfigureAwait(false)).First();
|
|
i.IsEqualTo(123);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressionsAsync()
|
|
{
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
await connection.ExecuteAsync(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
SET @AddressPersonId = @PersonId", p).ConfigureAwait(false);
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressions_ScalarAsync()
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
var result = (int)(await connection.ExecuteScalarAsync(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
SET @AddressPersonId = @PersonId
|
|
select 42", p).ConfigureAwait(false));
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
result.IsEqualTo(42);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressions_Query_Default()
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
var result = (await connection.QueryAsync<int>(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
SET @AddressPersonId = @PersonId
|
|
select 42", p).ConfigureAwait(false)).Single();
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
result.IsEqualTo(42);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressions_Query_BufferedAsync()
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
var result = (await connection.QueryAsync<int>(new CommandDefinition(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
SET @AddressPersonId = @PersonId
|
|
select 42", p, flags: CommandFlags.Buffered)).ConfigureAwait(false)).Single();
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
result.IsEqualTo(42);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressions_Query_NonBufferedAsync()
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
var result = (await connection.QueryAsync<int>(new CommandDefinition(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
SET @AddressPersonId = @PersonId
|
|
select 42", p, flags: CommandFlags.None)).ConfigureAwait(false)).Single();
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
result.IsEqualTo(42);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSupportForDynamicParametersOutputExpressions_QueryMultipleAsync()
|
|
{
|
|
var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };
|
|
|
|
var p = new DynamicParameters(bob);
|
|
p.Output(bob, b => b.PersonId);
|
|
p.Output(bob, b => b.Occupation);
|
|
p.Output(bob, b => b.NumberOfLegs);
|
|
p.Output(bob, b => b.Address.Name);
|
|
p.Output(bob, b => b.Address.PersonId);
|
|
|
|
int x, y;
|
|
using (var multi = await connection.QueryMultipleAsync(@"
|
|
SET @Occupation = 'grillmaster'
|
|
SET @PersonId = @PersonId + 1
|
|
SET @NumberOfLegs = @NumberOfLegs - 1
|
|
SET @AddressName = 'bobs burgers'
|
|
select 42
|
|
select 17
|
|
SET @AddressPersonId = @PersonId", p).ConfigureAwait(false))
|
|
{
|
|
x = multi.ReadAsync<int>().Result.Single();
|
|
y = multi.ReadAsync<int>().Result.Single();
|
|
}
|
|
|
|
bob.Occupation.IsEqualTo("grillmaster");
|
|
bob.PersonId.IsEqualTo(2);
|
|
bob.NumberOfLegs.IsEqualTo(1);
|
|
bob.Address.Name.IsEqualTo("bobs burgers");
|
|
bob.Address.PersonId.IsEqualTo(2);
|
|
x.IsEqualTo(42);
|
|
y.IsEqualTo(17);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestSubsequentQueriesSuccessAsync()
|
|
{
|
|
var data0 = (await connection.QueryAsync<AsyncFoo0>("select 1 as [Id] where 1 = 0").ConfigureAwait(false)).ToList();
|
|
data0.Count.IsEqualTo(0);
|
|
|
|
var data1 = (await connection.QueryAsync<AsyncFoo1>(new CommandDefinition("select 1 as [Id] where 1 = 0", flags: CommandFlags.Buffered)).ConfigureAwait(false)).ToList();
|
|
data1.Count.IsEqualTo(0);
|
|
|
|
var data2 = (await connection.QueryAsync<AsyncFoo2>(new CommandDefinition("select 1 as [Id] where 1 = 0", flags: CommandFlags.None)).ConfigureAwait(false)).ToList();
|
|
data2.Count.IsEqualTo(0);
|
|
|
|
data0 = (await connection.QueryAsync<AsyncFoo0>("select 1 as [Id] where 1 = 0").ConfigureAwait(false)).ToList();
|
|
data0.Count.IsEqualTo(0);
|
|
|
|
data1 = (await connection.QueryAsync<AsyncFoo1>(new CommandDefinition("select 1 as [Id] where 1 = 0", flags: CommandFlags.Buffered)).ConfigureAwait(false)).ToList();
|
|
data1.Count.IsEqualTo(0);
|
|
|
|
data2 = (await connection.QueryAsync<AsyncFoo2>(new CommandDefinition("select 1 as [Id] where 1 = 0", flags: CommandFlags.None)).ConfigureAwait(false)).ToList();
|
|
data2.Count.IsEqualTo(0);
|
|
}
|
|
|
|
private class AsyncFoo0 { public int Id { get; set; } }
|
|
|
|
private class AsyncFoo1 { public int Id { get; set; } }
|
|
|
|
private class AsyncFoo2 { public int Id { get; set; } }
|
|
|
|
[Fact]
|
|
public async Task TestSchemaChangedViaFirstOrDefaultAsync()
|
|
{
|
|
await connection.ExecuteAsync("create table #dog(Age int, Name nvarchar(max)) insert #dog values(1, 'Alf')").ConfigureAwait(false);
|
|
try
|
|
{
|
|
var d = await connection.QueryFirstOrDefaultAsync<Dog>("select * from #dog").ConfigureAwait(false);
|
|
d.Name.IsEqualTo("Alf");
|
|
d.Age.IsEqualTo(1);
|
|
connection.Execute("alter table #dog drop column Name");
|
|
d = await connection.QueryFirstOrDefaultAsync<Dog>("select * from #dog").ConfigureAwait(false);
|
|
d.Name.IsNull();
|
|
d.Age.IsEqualTo(1);
|
|
}
|
|
finally
|
|
{
|
|
await connection.ExecuteAsync("drop table #dog").ConfigureAwait(false);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestMultiMapArbitraryMapsAsync()
|
|
{
|
|
// please excuse the trite example, but it is easier to follow than a more real-world one
|
|
const string createSql = @"
|
|
create table #ReviewBoards (Id int, Name varchar(20), User1Id int, User2Id int, User3Id int, User4Id int, User5Id int, User6Id int, User7Id int, User8Id int, User9Id int)
|
|
create table #Users (Id int, Name varchar(20))
|
|
|
|
insert #Users values(1, 'User 1')
|
|
insert #Users values(2, 'User 2')
|
|
insert #Users values(3, 'User 3')
|
|
insert #Users values(4, 'User 4')
|
|
insert #Users values(5, 'User 5')
|
|
insert #Users values(6, 'User 6')
|
|
insert #Users values(7, 'User 7')
|
|
insert #Users values(8, 'User 8')
|
|
insert #Users values(9, 'User 9')
|
|
|
|
insert #ReviewBoards values(1, 'Review Board 1', 1, 2, 3, 4, 5, 6, 7, 8, 9)
|
|
";
|
|
await connection.ExecuteAsync(createSql).ConfigureAwait(false);
|
|
try
|
|
{
|
|
const string sql = @"
|
|
select
|
|
rb.Id, rb.Name,
|
|
u1.*, u2.*, u3.*, u4.*, u5.*, u6.*, u7.*, u8.*, u9.*
|
|
from #ReviewBoards rb
|
|
inner join #Users u1 on u1.Id = rb.User1Id
|
|
inner join #Users u2 on u2.Id = rb.User2Id
|
|
inner join #Users u3 on u3.Id = rb.User3Id
|
|
inner join #Users u4 on u4.Id = rb.User4Id
|
|
inner join #Users u5 on u5.Id = rb.User5Id
|
|
inner join #Users u6 on u6.Id = rb.User6Id
|
|
inner join #Users u7 on u7.Id = rb.User7Id
|
|
inner join #Users u8 on u8.Id = rb.User8Id
|
|
inner join #Users u9 on u9.Id = rb.User9Id
|
|
";
|
|
|
|
var types = new[] { typeof(ReviewBoard), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User) };
|
|
|
|
Func<object[], ReviewBoard> mapper = (objects) =>
|
|
{
|
|
var board = (ReviewBoard)objects[0];
|
|
board.User1 = (User)objects[1];
|
|
board.User2 = (User)objects[2];
|
|
board.User3 = (User)objects[3];
|
|
board.User4 = (User)objects[4];
|
|
board.User5 = (User)objects[5];
|
|
board.User6 = (User)objects[6];
|
|
board.User7 = (User)objects[7];
|
|
board.User8 = (User)objects[8];
|
|
board.User9 = (User)objects[9];
|
|
return board;
|
|
};
|
|
|
|
var data = (await connection.QueryAsync<ReviewBoard>(sql, types, mapper).ConfigureAwait(false)).ToList();
|
|
|
|
var p = data.First();
|
|
p.Id.IsEqualTo(1);
|
|
p.Name.IsEqualTo("Review Board 1");
|
|
p.User1.Id.IsEqualTo(1);
|
|
p.User2.Id.IsEqualTo(2);
|
|
p.User3.Id.IsEqualTo(3);
|
|
p.User4.Id.IsEqualTo(4);
|
|
p.User5.Id.IsEqualTo(5);
|
|
p.User6.Id.IsEqualTo(6);
|
|
p.User7.Id.IsEqualTo(7);
|
|
p.User8.Id.IsEqualTo(8);
|
|
p.User9.Id.IsEqualTo(9);
|
|
p.User1.Name.IsEqualTo("User 1");
|
|
p.User2.Name.IsEqualTo("User 2");
|
|
p.User3.Name.IsEqualTo("User 3");
|
|
p.User4.Name.IsEqualTo("User 4");
|
|
p.User5.Name.IsEqualTo("User 5");
|
|
p.User6.Name.IsEqualTo("User 6");
|
|
p.User7.Name.IsEqualTo("User 7");
|
|
p.User8.Name.IsEqualTo("User 8");
|
|
p.User9.Name.IsEqualTo("User 9");
|
|
}
|
|
finally
|
|
{
|
|
connection.Execute("drop table #Users drop table #ReviewBoards");
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Issue157_ClosedReaderAsync()
|
|
{
|
|
var args = new { x = 42 };
|
|
const string sql = @"select 123 as [A], 'abc' as [B] where @x=42";
|
|
var row = (await connection.QueryAsync<SomeType>(new CommandDefinition(
|
|
sql, args, flags:CommandFlags.None)).ConfigureAwait(false)).Single();
|
|
row.IsNotNull();
|
|
row.A.IsEqualTo(123);
|
|
row.B.IsEqualTo("abc");
|
|
|
|
args = new { x = 5 };
|
|
(await connection.QueryAsync<SomeType>(new CommandDefinition(
|
|
sql, args, flags: CommandFlags.None)).ConfigureAwait(false)).Any().IsFalse();
|
|
}
|
|
|
|
[Fact]
|
|
public async Task TestAtEscaping()
|
|
{
|
|
var id = (await connection.QueryAsync<int>(@"
|
|
declare @@Name int
|
|
select @@Name = @Id+1
|
|
select @@Name
|
|
", new Product { Id = 1 }).ConfigureAwait(false)).Single();
|
|
id.IsEqualTo(2);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Issue1281_DataReaderOutOfOrderAsync()
|
|
{
|
|
using (var reader = await connection.ExecuteReaderAsync("Select 0, 1, 2").ConfigureAwait(false))
|
|
{
|
|
reader.Read().IsTrue();
|
|
reader.GetInt32(2).IsEqualTo(2);
|
|
reader.GetInt32(0).IsEqualTo(0);
|
|
reader.GetInt32(1).IsEqualTo(1);
|
|
reader.Read().IsFalse();
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Issue563_QueryAsyncShouldThrowException()
|
|
{
|
|
try
|
|
{
|
|
var data = (await connection.QueryAsync<int>("select 1 union all select 2; RAISERROR('after select', 16, 1);").ConfigureAwait(false)).ToList();
|
|
Assert.Fail();
|
|
}
|
|
catch (SqlException ex) when (ex.Message == "after select") { }
|
|
}
|
|
}
|
|
} |