/Tests/Tests.cs
C# | 2315 lines | 1944 code | 328 blank | 43 comment | 14 complexity | 3afce8868624876fe516b0ea2def52c9 MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- //#define POSTGRESQL // uncomment to run postgres tests
- using System;
- using System.Collections.Generic;
- using System.Data.SqlClient;
- using System.Linq;
- using Dapper;
- using System.Data.SqlServerCe;
- using System.IO;
- using System.Data;
- using System.Collections;
- using System.Reflection;
- using System.Dynamic;
- using System.ComponentModel;
- using Microsoft.CSharp.RuntimeBinder;
- #if POSTGRESQL
- using Npgsql;
- #endif
-
- namespace SqlMapper
- {
-
- class Tests
- {
- SqlConnection connection = Program.GetOpenConnection();
-
- public class AbstractInheritance
- {
- public abstract class Order
- {
- internal int Internal { get; set; }
- protected int Protected { get; set; }
- public int Public { get; set; }
-
- public int ProtectedVal { get { return Protected; } }
- }
-
- public class ConcreteOrder : Order
- {
- public int Concrete { get; set; }
- }
- }
-
- class UserWithConstructor
- {
- public UserWithConstructor(int id, string name)
- {
- Ident = id;
- FullName = name;
- }
- public int Ident { get; set; }
- public string FullName { get; set; }
- }
-
- class PostWithConstructor
- {
- public PostWithConstructor(int id, int ownerid, string content)
- {
- Ident = id;
- FullContent = content;
- }
-
- public int Ident { get; set; }
- public UserWithConstructor Owner { get; set; }
- public string FullContent { get; set; }
- public Comment Comment { get; set; }
- }
-
- public void TestMultiMapWithConstructor()
- {
- var createSql = @"
- create table #Users (Id int, Name varchar(20))
- create table #Posts (Id int, OwnerId int, Content varchar(20))
-
- insert #Users values(99, 'Sam')
- insert #Users values(2, 'I am')
-
- insert #Posts values(1, 99, 'Sams Post1')
- insert #Posts values(2, 99, 'Sams Post2')
- insert #Posts values(3, null, 'no ones post')";
- connection.Execute(createSql);
- string sql = @"select * from #Posts p
- left join #Users u on u.Id = p.OwnerId
- Order by p.Id";
- PostWithConstructor[] data = connection.Query<PostWithConstructor, UserWithConstructor, PostWithConstructor>(sql, (post, user) => { post.Owner = user; return post;}).ToArray();
- var p = data.First();
-
- p.FullContent.IsEqualTo("Sams Post1");
- p.Ident.IsEqualTo(1);
- p.Owner.FullName.IsEqualTo("Sam");
- p.Owner.Ident.IsEqualTo(99);
-
- data[2].Owner.IsNull();
-
- connection.Execute("drop table #Users drop table #Posts");
- }
-
-
- class MultipleConstructors
- {
- public MultipleConstructors()
- {
-
- }
- public MultipleConstructors(int a, string b)
- {
- A = a + 1;
- B = b + "!";
- }
- public int A { get; set; }
- public string B { get; set; }
- }
-
- public void TestMultipleConstructors()
- {
- MultipleConstructors mult = connection.Query<MultipleConstructors>("select 0 A, 'Dapper' b").First();
- mult.A.IsEqualTo(0);
- mult.B.IsEqualTo("Dapper");
- }
-
- class ConstructorsWithAccessModifiers
- {
- private ConstructorsWithAccessModifiers()
- {
- }
- public ConstructorsWithAccessModifiers(int a, string b)
- {
- A = a + 1;
- B = b + "!";
- }
- public int A { get; set; }
- public string B { get; set; }
- }
-
- public void TestConstructorsWithAccessModifiers()
- {
- ConstructorsWithAccessModifiers value = connection.Query<ConstructorsWithAccessModifiers>("select 0 A, 'Dapper' b").First();
- value.A.IsEqualTo(1);
- value.B.IsEqualTo("Dapper!");
- }
-
- class NoDefaultConstructor
- {
- public NoDefaultConstructor(int a1, int? b1, float f1, string s1, Guid G1)
- {
- A = a1;
- B = b1;
- F = f1;
- S = s1;
- G = G1;
- }
- public int A { get; set; }
- public int? B { get; set; }
- public float F { get; set; }
- public string S { get; set; }
- public Guid G { get; set; }
- }
-
- public void TestNoDefaultConstructor()
- {
- var guid = Guid.NewGuid();
- NoDefaultConstructor nodef = connection.Query<NoDefaultConstructor>("select CAST(NULL AS integer) A1, CAST(NULL AS integer) b1, CAST(NULL AS real) f1, 'Dapper' s1, G1 = @id", new { Id = guid }).First();
- nodef.A.IsEqualTo(0);
- nodef.B.IsEqualTo(null);
- nodef.F.IsEqualTo(0);
- nodef.S.IsEqualTo("Dapper");
- nodef.G.IsEqualTo(guid);
- }
-
- class NoDefaultConstructorWithChar
- {
- public NoDefaultConstructorWithChar(char c1, char? c2, char? c3)
- {
- Char1 = c1;
- Char2 = c2;
- Char3 = c3;
- }
- public char Char1 { get; set; }
- public char? Char2 { get; set; }
- public char? Char3 { get; set; }
- }
-
- public void TestNoDefaultConstructorWithChar()
- {
- const char c1 = 'ą';
- const char c3 = 'ó';
- NoDefaultConstructorWithChar nodef = connection.Query<NoDefaultConstructorWithChar>("select @c1 c1, @c2 c2, @c3 c3", new { c1 = c1, c2 = (char?)null, c3 = c3 }).First();
- nodef.Char1.IsEqualTo(c1);
- nodef.Char2.IsEqualTo(null);
- nodef.Char3.IsEqualTo(c3);
- }
-
- class NoDefaultConstructorWithEnum
- {
- public NoDefaultConstructorWithEnum(ShortEnum e1, ShortEnum? n1, ShortEnum? n2)
- {
- E = e1;
- NE1 = n1;
- NE2 = n2;
- }
- public ShortEnum E { get; set; }
- public ShortEnum? NE1 { get; set; }
- public ShortEnum? NE2 { get; set; }
- }
-
- public void TestNoDefaultConstructorWithEnum()
- {
- NoDefaultConstructorWithEnum nodef = connection.Query<NoDefaultConstructorWithEnum>("select cast(2 as smallint) E1, cast(5 as smallint) n1, cast(null as smallint) n2").First();
- nodef.E.IsEqualTo(ShortEnum.Two);
- nodef.NE1.IsEqualTo(ShortEnum.Five);
- nodef.NE2.IsEqualTo(null);
- }
-
- class NoDefaultConstructorWithBinary
- {
- public System.Data.Linq.Binary Value { get; set; }
- public int Ynt { get; set; }
- public NoDefaultConstructorWithBinary(System.Data.Linq.Binary val)
- {
- Value = val;
- }
- }
-
- public void TestNoDefaultConstructorBinary()
- {
- byte[] orig = new byte[20];
- new Random(123456).NextBytes(orig);
- var input = new System.Data.Linq.Binary(orig);
- var output = connection.Query<NoDefaultConstructorWithBinary>("select @input as val", new { input }).First().Value;
- output.ToArray().IsSequenceEqualTo(orig);
- }
-
- // http://stackoverflow.com/q/8593871
- public void TestAbstractInheritance()
- {
- var order = connection.Query<AbstractInheritance.ConcreteOrder>("select 1 Internal,2 Protected,3 [Public],4 Concrete").First();
-
- order.Internal.IsEqualTo(1);
- order.ProtectedVal.IsEqualTo(2);
- order.Public.IsEqualTo(3);
- order.Concrete.IsEqualTo(4);
- }
-
- public void TestListOfAnsiStrings()
- {
- var results = connection.Query<string>("select * from (select 'a' str union select 'b' union select 'c') X where str in @strings",
- new { strings = new[] { new DbString { IsAnsi = true, Value = "a" }, new DbString { IsAnsi = true, Value = "b" } } }).ToList();
-
- results[0].IsEqualTo("a");
- results[1].IsEqualTo("b");
- }
-
- public void TestNullableGuidSupport()
- {
- var guid = connection.Query<Guid?>("select null").First();
- guid.IsNull();
-
- guid = Guid.NewGuid();
- var guid2 = connection.Query<Guid?>("select @guid", new { guid }).First();
- guid.IsEqualTo(guid2);
- }
-
- public void TestNonNullableGuidSupport()
- {
- var guid = Guid.NewGuid();
- var guid2 = connection.Query<Guid?>("select @guid", new { guid }).First();
- Assert.IsTrue(guid == guid2);
- }
-
- struct Car
- {
- public enum TrapEnum : int
- {
- A = 1,
- B = 2
- }
- #pragma warning disable 0649
- public string Name;
- #pragma warning restore 0649
- public int Age { get; set; }
- public TrapEnum Trap { get; set; }
-
- }
-
- public void TestStructs()
- {
- var car = connection.Query<Car>("select 'Ford' Name, 21 Age, 2 Trap").First();
-
- car.Age.IsEqualTo(21);
- car.Name.IsEqualTo("Ford");
- ((int)car.Trap).IsEqualTo(2);
- }
-
- public void SelectListInt()
- {
- connection.Query<int>("select 1 union all select 2 union all select 3")
- .IsSequenceEqualTo(new[] { 1, 2, 3 });
- }
- public void SelectBinary()
- {
- connection.Query<byte[]>("select cast(1 as varbinary(4))").First().SequenceEqual(new byte[] { 1 });
- }
- public void PassInIntArray()
- {
- connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 }.AsEnumerable() })
- .IsSequenceEqualTo(new[] { 1, 2, 3 });
- }
-
- public void PassInEmptyIntArray()
- {
- connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[0] })
- .IsSequenceEqualTo(new int[0]);
- }
-
- public void TestSchemaChanged()
- {
- connection.Execute("create table #dog(Age int, Name nvarchar(max)) insert #dog values(1, 'Alf')");
- var d = connection.Query<Dog>("select * from #dog").Single();
- d.Name.IsEqualTo("Alf");
- d.Age.IsEqualTo(1);
- connection.Execute("alter table #dog drop column Name");
- d = connection.Query<Dog>("select * from #dog").Single();
- d.Name.IsNull();
- d.Age.IsEqualTo(1);
- connection.Execute("drop table #dog");
- }
-
- public void TestSchemaChangedMultiMap()
- {
- connection.Execute("create table #dog(Age int, Name nvarchar(max)) insert #dog values(1, 'Alf')");
- var tuple = connection.Query<Dog, Dog, Tuple<Dog, Dog>>("select * from #dog d1 join #dog d2 on 1=1", (d1, d2) => Tuple.Create(d1, d2), splitOn: "Age").Single();
-
- tuple.Item1.Name.IsEqualTo("Alf");
- tuple.Item1.Age.IsEqualTo(1);
- tuple.Item2.Name.IsEqualTo("Alf");
- tuple.Item2.Age.IsEqualTo(1);
-
- connection.Execute("alter table #dog drop column Name");
- tuple = connection.Query<Dog, Dog, Tuple<Dog, Dog>>("select * from #dog d1 join #dog d2 on 1=1", (d1, d2) => Tuple.Create(d1, d2), splitOn: "Age").Single();
-
- tuple.Item1.Name.IsNull();
- tuple.Item1.Age.IsEqualTo(1);
- tuple.Item2.Name.IsNull();
- tuple.Item2.Age.IsEqualTo(1);
-
- connection.Execute("drop table #dog");
- }
-
- public void TestReadMultipleIntegersWithSplitOnAny()
- {
- connection.Query<int, int, int, Tuple<int, int, int>>(
- "select 1,2,3 union all select 4,5,6", Tuple.Create, splitOn: "*")
- .IsSequenceEqualTo(new[] { Tuple.Create(1, 2, 3), Tuple.Create(4, 5, 6) });
- }
-
- public void TestDoubleParam()
- {
- connection.Query<double>("select @d", new { d = 0.1d }).First()
- .IsEqualTo(0.1d);
- }
-
- public void TestBoolParam()
- {
- connection.Query<bool>("select @b", new { b = false }).First()
- .IsFalse();
- }
-
- // http://code.google.com/p/dapper-dot-net/issues/detail?id=70
- // https://connect.microsoft.com/VisualStudio/feedback/details/381934/sqlparameter-dbtype-dbtype-time-sets-the-parameter-to-sqldbtype-datetime-instead-of-sqldbtype-time
- public void TestTimeSpanParam()
- {
- connection.Query<TimeSpan>("select @ts", new { ts = TimeSpan.FromMinutes(42) }).First()
- .IsEqualTo(TimeSpan.FromMinutes(42));
- }
-
- public void TestStrings()
- {
- connection.Query<string>(@"select 'a' a union select 'b'")
- .IsSequenceEqualTo(new[] { "a", "b" });
- }
-
- enum EnumParam : short
- {
- None, A, B
- }
- class EnumParamObject
- {
- public EnumParam A { get; set; }
- public EnumParam? B { get; set; }
- public EnumParam? C { get; set; }
- }
- class EnumParamObjectNonNullable
- {
- public EnumParam A { get; set; }
- public EnumParam? B { get; set; }
- public EnumParam? C { get; set; }
- }
- public void TestEnumParamsWithNullable()
- {
- EnumParam a = EnumParam.A;
- EnumParam? b = EnumParam.B, c = null;
- var obj = connection.Query<EnumParamObject>("select @a as A, @b as B, @c as C",
- new { a, b, c }).Single();
- obj.A.IsEqualTo(EnumParam.A);
- obj.B.IsEqualTo(EnumParam.B);
- obj.C.IsEqualTo(null);
- }
- public void TestEnumParamsWithoutNullable()
- {
- EnumParam a = EnumParam.A;
- EnumParam b = EnumParam.B, c = 0;
- var obj = connection.Query<EnumParamObjectNonNullable>("select @a as A, @b as B, @c as C",
- new { a, b, c }).Single();
- obj.A.IsEqualTo(EnumParam.A);
- obj.B.IsEqualTo(EnumParam.B);
- obj.C.IsEqualTo((EnumParam)0);
- }
- public class Dog
- {
- public int? Age { get; set; }
- public Guid Id { get; set; }
- public string Name { get; set; }
- public float? Weight { get; set; }
-
- public int IgnoredProperty { get { return 1; } }
- }
-
- public void TestExtraFields()
- {
- var guid = Guid.NewGuid();
- var dog = connection.Query<Dog>("select '' as Extra, 1 as Age, 0.1 as Name1 , Id = @id", new { Id = guid });
-
- dog.Count()
- .IsEqualTo(1);
-
- dog.First().Age
- .IsEqualTo(1);
-
- dog.First().Id
- .IsEqualTo(guid);
- }
-
-
- public void TestStrongType()
- {
- var guid = Guid.NewGuid();
- var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
-
- dog.Count()
- .IsEqualTo(1);
-
- dog.First().Age
- .IsNull();
-
- dog.First().Id
- .IsEqualTo(guid);
- }
-
- public void TestSimpleNull()
- {
- connection.Query<DateTime?>("select null").First().IsNull();
- }
-
- public void TestExpando()
- {
- var rows = connection.Query("select 1 A, 2 B union all select 3, 4").ToList();
-
- ((int)rows[0].A)
- .IsEqualTo(1);
-
- ((int)rows[0].B)
- .IsEqualTo(2);
-
- ((int)rows[1].A)
- .IsEqualTo(3);
-
- ((int)rows[1].B)
- .IsEqualTo(4);
- }
-
- public void TestStringList()
- {
- connection.Query<string>("select * from (select 'a' as x union all select 'b' union all select 'c') as T where x in @strings", new { strings = new[] { "a", "b", "c" } })
- .IsSequenceEqualTo(new[] { "a", "b", "c" });
-
- connection.Query<string>("select * from (select 'a' as x union all select 'b' union all select 'c') as T where x in @strings", new { strings = new string[0] })
- .IsSequenceEqualTo(new string[0]);
- }
-
- public void TestExecuteCommand()
- {
- connection.Execute(@"
- set nocount on
- create table #t(i int)
- set nocount off
- insert #t
- select @a a union all select @b
- set nocount on
- drop table #t", new { a = 1, b = 2 }).IsEqualTo(2);
- }
- public void TestExecuteCommandWithHybridParameters()
- {
- var p = new DynamicParameters(new { a = 1, b = 2 });
- p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.Output);
- connection.Execute(@"set @c = @a + @b", p);
- p.Get<int>("@c").IsEqualTo(3);
- }
- public void TestExecuteMultipleCommand()
- {
- connection.Execute("create table #t(i int)");
- int tally = connection.Execute(@"insert #t (i) values(@a)", new[] { new { a = 1 }, new { a = 2 }, new { a = 3 }, new { a = 4 } });
- int sum = connection.Query<int>("select sum(i) from #t drop table #t").First();
- tally.IsEqualTo(4);
- sum.IsEqualTo(10);
- }
-
- class Student
- {
- public string Name { get; set; }
- public int Age { get; set; }
- }
-
- public void TestExecuteMultipleCommandStrongType()
- {
- connection.Execute("create table #t(Name nvarchar(max), Age int)");
- int tally = connection.Execute(@"insert #t (Name,Age) values(@Name, @Age)", new List<Student>
- {
- new Student{Age = 1, Name = "sam"},
- new Student{Age = 2, Name = "bob"}
- });
- int sum = connection.Query<int>("select sum(Age) from #t drop table #t").First();
- tally.IsEqualTo(2);
- sum.IsEqualTo(3);
- }
-
- public void TestExecuteMultipleCommandObjectArray()
- {
- connection.Execute("create table #t(i int)");
- int tally = connection.Execute(@"insert #t (i) values(@a)", new object[] { new { a = 1 }, new { a = 2 }, new { a = 3 }, new { a = 4 } });
- int sum = connection.Query<int>("select sum(i) from #t drop table #t").First();
- tally.IsEqualTo(4);
- sum.IsEqualTo(10);
- }
-
- public void TestMassiveStrings()
- {
- var str = new string('X', 20000);
- connection.Query<string>("select @a", new { a = str }).First()
- .IsEqualTo(str);
- }
-
- class TestObj
- {
- public int _internal;
- internal int Internal { set { _internal = value; } }
-
- public int _priv;
- private int Priv { set { _priv = value; } }
- }
-
- public void TestSetInternal()
- {
- connection.Query<TestObj>("select 10 as [Internal]").First()._internal.IsEqualTo(10);
- }
-
- public void TestSetPrivate()
- {
- connection.Query<TestObj>("select 10 as [Priv]").First()._priv.IsEqualTo(10);
- }
-
- public void TestEnumeration()
- {
- var en = connection.Query<int>("select 1 as one union all select 2 as one", buffered: false);
- var i = en.GetEnumerator();
- i.MoveNext();
-
- bool gotException = false;
- try
- {
- var x = connection.Query<int>("select 1 as one", buffered: false).First();
- }
- catch (Exception)
- {
- gotException = true;
- }
-
- while (i.MoveNext())
- { }
-
- // should not exception, since enumertated
- en = connection.Query<int>("select 1 as one", buffered: false);
-
- gotException.IsTrue();
- }
-
- public void TestEnumerationDynamic()
- {
- var en = connection.Query("select 1 as one union all select 2 as one", buffered: false);
- var i = en.GetEnumerator();
- i.MoveNext();
-
- bool gotException = false;
- try
- {
- var x = connection.Query("select 1 as one", buffered: false).First();
- }
- catch (Exception)
- {
- gotException = true;
- }
-
- while (i.MoveNext())
- { }
-
- // should not exception, since enumertated
- en = connection.Query("select 1 as one", buffered: false);
-
- gotException.IsTrue();
- }
-
- public void TestNakedBigInt()
- {
- long foo = 12345;
- var result = connection.Query<long>("select @foo", new { foo }).Single();
- foo.IsEqualTo(result);
- }
-
- public void TestBigIntMember()
- {
- long foo = 12345;
- var result = connection.Query<WithBigInt>(@"
- declare @bar table(Value bigint)
- insert @bar values (@foo)
- select * from @bar", new { foo }).Single();
- result.Value.IsEqualTo(foo);
- }
- class WithBigInt
- {
- public long Value { get; set; }
- }
-
- class User
- {
- public int Id { get; set; }
- public string Name { get; set; }
- }
- class Post
- {
- public int Id { get; set; }
- public User Owner { get; set; }
- public string Content { get; set; }
- public Comment Comment { get; set; }
- }
- public void TestMultiMap()
- {
- var createSql = @"
- create table #Users (Id int, Name varchar(20))
- create table #Posts (Id int, OwnerId int, Content varchar(20))
-
- insert #Users values(99, 'Sam')
- insert #Users values(2, 'I am')
-
- insert #Posts values(1, 99, 'Sams Post1')
- insert #Posts values(2, 99, 'Sams Post2')
- insert #Posts values(3, null, 'no ones post')
- ";
- connection.Execute(createSql);
-
- var sql =
- @"select * from #Posts p
- left join #Users u on u.Id = p.OwnerId
- Order by p.Id";
-
- var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
- var p = data.First();
-
- p.Content.IsEqualTo("Sams Post1");
- p.Id.IsEqualTo(1);
- p.Owner.Name.IsEqualTo("Sam");
- p.Owner.Id.IsEqualTo(99);
-
- data[2].Owner.IsNull();
-
- connection.Execute("drop table #Users drop table #Posts");
- }
-
-
-
- public void TestMultiMapGridReader()
- {
- var createSql = @"
- create table #Users (Id int, Name varchar(20))
- create table #Posts (Id int, OwnerId int, Content varchar(20))
-
- insert #Users values(99, 'Sam')
- insert #Users values(2, 'I am')
-
- insert #Posts values(1, 99, 'Sams Post1')
- insert #Posts values(2, 99, 'Sams Post2')
- insert #Posts values(3, null, 'no ones post')
- ";
- connection.Execute(createSql);
-
- var sql =
- @"select p.*, u.Id, u.Name + '0' Name from #Posts p
- left join #Users u on u.Id = p.OwnerId
- Order by p.Id
-
- select p.*, u.Id, u.Name + '1' Name from #Posts p
- left join #Users u on u.Id = p.OwnerId
- Order by p.Id
- ";
-
- var grid = connection.QueryMultiple(sql);
-
- for (int i = 0; i < 2; i++)
- {
- var data = grid.Read<Post, User, Post>((post, user) => { post.Owner = user; return post; }).ToList();
- var p = data.First();
-
- p.Content.IsEqualTo("Sams Post1");
- p.Id.IsEqualTo(1);
- p.Owner.Name.IsEqualTo("Sam" + i);
- p.Owner.Id.IsEqualTo(99);
-
- data[2].Owner.IsNull();
- }
-
- connection.Execute("drop table #Users drop table #Posts");
-
- }
-
- public void TestQueryMultipleBuffered()
- {
- using (var grid = connection.QueryMultiple("select 1; select 2; select @x; select 4", new { x = 3 }))
- {
- var a = grid.Read<int>();
- var b = grid.Read<int>();
- var c = grid.Read<int>();
- var d = grid.Read<int>();
-
- a.Single().Equals(1);
- b.Single().Equals(2);
- c.Single().Equals(3);
- d.Single().Equals(4);
- }
- }
-
- public void TestQueryMultipleNonBufferedIncorrectOrder()
- {
- using (var grid = connection.QueryMultiple("select 1; select 2; select @x; select 4", new { x = 3 }))
- {
- var a = grid.Read<int>(false);
- try
- {
- var b = grid.Read<int>(false);
- throw new InvalidOperationException(); // should have thrown
- }
- catch (InvalidOperationException)
- {
- // that's expected
- }
-
- }
- }
- public void TestQueryMultipleNonBufferedCcorrectOrder()
- {
- using (var grid = connection.QueryMultiple("select 1; select 2; select @x; select 4", new { x = 3 }))
- {
- var a = grid.Read<int>(false).Single();
- var b = grid.Read<int>(false).Single();
- var c = grid.Read<int>(false).Single();
- var d = grid.Read<int>(false).Single();
-
- a.Equals(1);
- b.Equals(2);
- c.Equals(3);
- d.Equals(4);
- }
- }
- public void TestMultiMapDynamic()
- {
- var createSql = @"
- create table #Users (Id int, Name varchar(20))
- create table #Posts (Id int, OwnerId int, Content varchar(20))
-
- insert #Users values(99, 'Sam')
- insert #Users values(2, 'I am')
-
- insert #Posts values(1, 99, 'Sams Post1')
- insert #Posts values(2, 99, 'Sams Post2')
- insert #Posts values(3, null, 'no ones post')
- ";
- connection.Execute(createSql);
-
- var sql =
- @"select * from #Posts p
- left join #Users u on u.Id = p.OwnerId
- Order by p.Id";
-
- var data = connection.Query<dynamic, dynamic, dynamic>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
- var p = data.First();
-
- // hairy extension method support for dynamics
- ((string)p.Content).IsEqualTo("Sams Post1");
- ((int)p.Id).IsEqualTo(1);
- ((string)p.Owner.Name).IsEqualTo("Sam");
- ((int)p.Owner.Id).IsEqualTo(99);
-
- ((object)data[2].Owner).IsNull();
-
- connection.Execute("drop table #Users drop table #Posts");
- }
-
- class Product
- {
- public int Id { get; set; }
- public string Name { get; set; }
- public Category Category { get; set; }
- }
- class Category
- {
- public int Id { get; set; }
- public string Name { get; set; }
- public string Description { get; set; }
- }
- public void TestMultiMapWithSplit() // http://stackoverflow.com/q/6056778/23354
- {
- var sql = @"select 1 as id, 'abc' as name, 2 as id, 'def' as name";
- var product = connection.Query<Product, Category, Product>(sql, (prod, cat) =>
- {
- prod.Category = cat;
- return prod;
- }).First();
- // assertions
- product.Id.IsEqualTo(1);
- product.Name.IsEqualTo("abc");
- product.Category.Id.IsEqualTo(2);
- product.Category.Name.IsEqualTo("def");
- }
- public void TestMultiMapWithSplitWithNullValue() // http://stackoverflow.com/q/10744728/449906
- {
- var sql = @"select 1 as id, 'abc' as name, NULL as description, 'def' as name";
- var product = connection.Query<Product, Category, Product>(sql, (prod, cat) =>
- {
- prod.Category = cat;
- return prod;
- }, splitOn: "description").First();
- // assertions
- product.Id.IsEqualTo(1);
- product.Name.IsEqualTo("abc");
- product.Category.IsNull();
- }
- public void TestMultiMapWithSplitWithNullValueAndSpoofColumn() // http://stackoverflow.com/q/10744728/449906
- {
- var sql = @"select 1 as id, 'abc' as name, 1 as spoof, NULL as description, 'def' as name";
- var product = connection.Query<Product, Category, Product>(sql, (prod, cat) =>
- {
- prod.Category = cat;
- return prod;
- }, splitOn: "spoof").First();
- // assertions
- product.Id.IsEqualTo(1);
- product.Name.IsEqualTo("abc");
- product.Category.IsNotNull();
- product.Category.Id.IsEqualTo(0);
- product.Category.Name.IsEqualTo("def");
- product.Category.Description.IsNull();
- }
- public void TestFieldsAndPrivates()
- {
- var data = connection.Query<TestFieldCaseAndPrivatesEntity>(
- @"select a=1,b=2,c=3,d=4,f='5'").Single();
-
-
- data.a.IsEqualTo(1);
- data.GetB().IsEqualTo(2);
- data.c.IsEqualTo(3);
- data.GetD().IsEqualTo(4);
- data.e.IsEqualTo(5);
-
-
- }
-
- private class TestFieldCaseAndPrivatesEntity
- {
- public int a { get; set; }
- private int b { get; set; }
- public int GetB() { return b; }
- public int c = 0;
- private int d = 0;
- public int GetD() { return d; }
- public int e { get; set; }
- private string f
- {
- get { return e.ToString(); }
- set { e = int.Parse(value); }
- }
- }
-
- public void TestMultiReaderBasic()
- {
- var sql = @"select 1 as Id union all select 2 as Id select 'abc' as name select 1 as Id union all select 2 as Id";
- int i, j;
- string s;
- using (var multi = connection.QueryMultiple(sql))
- {
- i = multi.Read<int>().First();
- s = multi.Read<string>().Single();
- j = multi.Read<int>().Sum();
- }
- Assert.IsEqualTo(i, 1);
- Assert.IsEqualTo(s, "abc");
- Assert.IsEqualTo(j, 3);
- }
- public void TestMultiMappingVariations()
- {
- var sql = @"select 1 as Id, 'a' as Content, 2 as Id, 'b' as Content, 3 as Id, 'c' as Content, 4 as Id, 'd' as Content, 5 as Id, 'e' as Content";
-
- var order = connection.Query<dynamic, dynamic, dynamic, dynamic>(sql, (o, owner, creator) => { o.Owner = owner; o.Creator = creator; return o; }).First();
-
- Assert.IsEqualTo(order.Id, 1);
- Assert.IsEqualTo(order.Content, "a");
- Assert.IsEqualTo(order.Owner.Id, 2);
- Assert.IsEqualTo(order.Owner.Content, "b");
- Assert.IsEqualTo(order.Creator.Id, 3);
- Assert.IsEqualTo(order.Creator.Content, "c");
-
- order = connection.Query<dynamic, dynamic, dynamic, dynamic, dynamic>(sql, (o, owner, creator, address) =>
- {
- o.Owner = owner;
- o.Creator = creator;
- o.Owner.Address = address;
- return o;
- }).First();
-
- Assert.IsEqualTo(order.Id, 1);
- Assert.IsEqualTo(order.Content, "a");
- Assert.IsEqualTo(order.Owner.Id, 2);
- Assert.IsEqualTo(order.Owner.Content, "b");
- Assert.IsEqualTo(order.Creator.Id, 3);
- Assert.IsEqualTo(order.Creator.Content, "c");
- Assert.IsEqualTo(order.Owner.Address.Id, 4);
- Assert.IsEqualTo(order.Owner.Address.Content, "d");
-
- order = connection.Query<dynamic, dynamic, dynamic, dynamic, dynamic, dynamic>(sql, (a, b, c, d, e) => { a.B = b; a.C = c; a.C.D = d; a.E = e; return a; }).First();
-
- Assert.IsEqualTo(order.Id, 1);
- Assert.IsEqualTo(order.Content, "a");
- Assert.IsEqualTo(order.B.Id, 2);
- Assert.IsEqualTo(order.B.Content, "b");
- Assert.IsEqualTo(order.C.Id, 3);
- Assert.IsEqualTo(order.C.Content, "c");
- Assert.IsEqualTo(order.C.D.Id, 4);
- Assert.IsEqualTo(order.C.D.Content, "d");
- Assert.IsEqualTo(order.E.Id, 5);
- Assert.IsEqualTo(order.E.Content, "e");
-
- }
-
- class InheritanceTest1
- {
- public string Base1 { get; set; }
- public string Base2 { get; private set; }
- }
-
- class InheritanceTest2 : InheritanceTest1
- {
- public string Derived1 { get; set; }
- public string Derived2 { get; private set; }
- }
-
- public void TestInheritance()
- {
- // Test that inheritance works.
- var list = connection.Query<InheritanceTest2>("select 'One' as Derived1, 'Two' as Derived2, 'Three' as Base1, 'Four' as Base2");
- list.First().Derived1.IsEqualTo("One");
- list.First().Derived2.IsEqualTo("Two");
- list.First().Base1.IsEqualTo("Three");
- list.First().Base2.IsEqualTo("Four");
- }
-
-
- public class PostCE
- {
- public int ID { get; set; }
- public string Title { get; set; }
- public string Body { get; set; }
-
- public AuthorCE Author { get; set; }
- }
-
- public class AuthorCE
- {
- public int ID { get; set; }
- public string Name { get; set; }
- }
-
- public void MultiRSSqlCE()
- {
- if (File.Exists("Test.sdf"))
- File.Delete("Test.sdf");
-
- var cnnStr = "Data Source = Test.sdf;";
- var engine = new SqlCeEngine(cnnStr);
- engine.CreateDatabase();
-
- using (var cnn = new SqlCeConnection(cnnStr))
- {
- cnn.Open();
-
- cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
- cnn.Execute("create table Authors (ID int, Name nvarchar(50))");
-
- cnn.Execute("insert Posts values (1,'title','body',1)");
- cnn.Execute("insert Posts values(2,'title2','body2',null)");
- cnn.Execute("insert Authors values(1,'sam')");
-
- var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList();
- var firstPost = data.First();
- firstPost.Title.IsEqualTo("title");
- firstPost.Author.Name.IsEqualTo("sam");
- data[1].Author.IsNull();
- cnn.Close();
- }
- }
-
- enum TestEnum : byte
- {
- Bla = 1
- }
- class TestEnumClass
- {
- public TestEnum? EnumEnum { get; set; }
- }
- class TestEnumClassNoNull
- {
- public TestEnum EnumEnum { get; set; }
- }
- public void TestEnumWeirdness()
- {
- connection.Query<TestEnumClass>("select null as [EnumEnum]").First().EnumEnum.IsEqualTo(null);
- connection.Query<TestEnumClass>("select cast(1 as tinyint) as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
- }
- public void TestEnumStrings()
- {
- connection.Query<TestEnumClassNoNull>("select 'BLA' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
- connection.Query<TestEnumClassNoNull>("select 'bla' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
-
- connection.Query<TestEnumClass>("select 'BLA' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
- connection.Query<TestEnumClass>("select 'bla' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
- }
-
- public void TestSupportForDynamicParameters()
- {
- var p = new DynamicParameters();
- p.Add("name", "bob");
- p.Add("age", dbType: DbType.Int32, direction: ParameterDirection.Output);
-
- connection.Query<string>("set @age = 11 select @name", p).First().IsEqualTo("bob");
-
- p.Get<int>("age").IsEqualTo(11);
- }
- public void TestSupportForExpandoObjectParameters()
- {
- dynamic p = new ExpandoObject();
- p.name = "bob";
- object parameters = p;
- string result = connection.Query<string>("select @name", parameters).First();
- result.IsEqualTo("bob");
- }
-
- public void TestProcSupport()
- {
- var p = new DynamicParameters();
- p.Add("a", 11);
- p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
- p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
-
- connection.Execute(@"create proc #TestProc
- @a int,
- @b int output
- as
- begin
- set @b = 999
- select 1111
- return @a
- end");
- connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
-
- p.Get<int>("c").IsEqualTo(11);
- p.Get<int>("b").IsEqualTo(999);
-
- }
-
- public void TestDbString()
- {
- var obj = connection.Query("select datalength(@a) as a, datalength(@b) as b, datalength(@c) as c, datalength(@d) as d, datalength(@e) as e, datalength(@f) as f",
- new
- {
- a = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true },
- b = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = false },
- c = new DbString { Value = "abcde", IsFixedLength = false, Length = 10, IsAnsi = true },
- d = new DbString { Value = "abcde", IsFixedLength = false, Length = 10, IsAnsi = false },
- e = new DbString { Value = "abcde", IsAnsi = true },
- f = new DbString { Value = "abcde", IsAnsi = false },
- }).First();
- ((int)obj.a).IsEqualTo(10);
- ((int)obj.b).IsEqualTo(20);
- ((int)obj.c).IsEqualTo(5);
- ((int)obj.d).IsEqualTo(10);
- ((int)obj.e).IsEqualTo(5);
- ((int)obj.f).IsEqualTo(10);
- }
-
- class Person
- {
- public int PersonId { get; set; }
- public string Name { get; set; }
- }
-
- class Address
- {
- public int AddressId { get; set; }
- public string Name { get; set; }
- public int PersonId { get; set; }
- }
-
- class Extra
- {
- public int Id { get; set; }
- public string Name { get; set; }
- }
-
- public void TestFlexibleMultiMapping()
- {
- var sql =
- @"select
- 1 as PersonId, 'bob' as Name,
- 2 as AddressId, 'abc street' as Name, 1 as PersonId,
- 3 as Id, 'fred' as Name
- ";
- var personWithAddress = connection.Query<Person, Address, Extra, Tuple<Person, Address, Extra>>
- (sql, (p, a, e) => Tuple.Create(p, a, e), splitOn: "AddressId,Id").First();
-
- personWithAddress.Item1.PersonId.IsEqualTo(1);
- personWithAddress.Item1.Name.IsEqualTo("bob");
- personWithAddress.Item2.AddressId.IsEqualTo(2);
- personWithAddress.Item2.Name.IsEqualTo("abc street");
- personWithAddress.Item2.PersonId.IsEqualTo(1);
- personWithAddress.Item3.Id.IsEqualTo(3);
- personWithAddress.Item3.Name.IsEqualTo("fred");
-
- }
-
- public void TestMultiMappingWithSplitOnSpaceBetweenCommas()
- {
- var sql = @"select
- 1 as PersonId, 'bob' as Name,
- 2 as AddressId, 'abc street' as Name, 1 as PersonId,
- 3 as Id, 'fred' as Name
- ";
- var personWithAddress = connection.Query<Person, Address, Extra, Tuple<Person, Address, Extra>>
- (sql, (p, a, e) => Tuple.Create(p, a, e), splitOn: "AddressId, Id").First();
-
- personWithAddress.Item1.PersonId.IsEqualTo(1);
- personWithAddress.Item1.Name.IsEqualTo("bob");
- personWithAddress.Item2.AddressId.IsEqualTo(2);
- personWithAddress.Item2.Name.IsEqualTo("abc street");
- personWithAddress.Item2.PersonId.IsEqualTo(1);
- personWithAddress.Item3.Id.IsEqualTo(3);
- personWithAddress.Item3.Name.IsEqualTo("fred");
-
- }
-
- public void TestFastExpandoSupportsIDictionary()
- {
- var row = connection.Query("select 1 A, 'two' B").First() as IDictionary<string, object>;
- row["A"].IsEqualTo(1);
- row["B"].IsEqualTo("two");
- }
-
-
- class PrivateDan
- {
- public int Shadow { get; set; }
- private string ShadowInDB
- {
- set
- {
- Shadow = value == "one" ? 1 : 0;
- }
- }
- }
- public void TestDapperSetsPrivates()
- {
- connection.Query<PrivateDan>("select 'one' ShadowInDB").First().Shadow.IsEqualTo(1);
- }
-
-
- class IntDynamicParam : Dapper.SqlMapper.IDynamicParameters
- {
- IEnumerable<int> numbers;
- public IntDynamicParam(IEnumerable<int> numbers)
- {
- this.numbers = numbers;
- }
-
- public void AddParameters(IDbCommand command, Dapper.SqlMapper.Identity identity)
- {
- var sqlCommand = (SqlCommand)command;
- sqlCommand.CommandType = CommandType.StoredProcedure;
-
- List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
-
- // Create an SqlMetaData object that describes our table type.
- Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };
-
- foreach (int n in numbers)
- {
- // Create a new record, using the metadata array above.
- Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
- rec.SetInt32(0, n); // Set the value.
- number_list.Add(rec); // Add it to the list.
- }
-
- // Add the table parameter.
- var p = sqlCommand.Parameters.Add("ints", SqlDbType.Structured);
- p.Direction = ParameterDirection.Input;
- p.TypeName = "int_list_type";
- p.Value = number_list;
-
- }
- }
-
- // SQL Server specific test to demonstrate TVP
- public void TestTVP()
- {
- try
- {
- connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
- connection.Execute("CREATE PROC get_ints @ints int_list_type READONLY AS select * from @ints");
-
- var nums = connection.Query<int>("get_ints", new IntDynamicParam(new int[] { 1, 2, 3 })).ToList();
- nums[0].IsEqualTo(1);
- nums[1].IsEqualTo(2);
- nums[2].IsEqualTo(3);
- nums.Count.IsEqualTo(3);
-
- }
- finally
- {
- try
- {
- connection.Execute("DROP PROC get_ints");
- }
- finally
- {
- connection.Execute("DROP TYPE int_list_type");
- }
- }
- }
-
- class DynamicParameterWithIntTVP : Dapper.DynamicParameters, Dapper.SqlMapper.IDynamicParameters
- {
- IEnumerable<int> numbers;
- public DynamicParameterWithIntTVP(IEnumerable<int> numbers)
- {
- this.numbers = numbers;
- }
-
- public new void AddParameters(IDbCommand command, Dapper.SqlMapper.Identity identity)
- {
- base.AddParameters(command, identity);
-
- var sqlCommand = (SqlCommand)command;
- sqlCommand.CommandType = CommandType.StoredProcedure;
-
- List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
-
- // Create an SqlMetaData object that describes our table type.
- Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };
-
- foreach (int n in numbers)
- {
- // Create a new record, using the metadata array above.
- Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
- rec.SetInt32(0, n); // Set the value.
- number_list.Add(rec); // Add it to the list.
- }
-
- // Add the table parameter.
- var p = sqlCommand.Parameters.Add("ints", SqlDbType.Structured);
- p.Direction = ParameterDirection.Input;
- p.TypeName = "int_list_type";
- p.Value = number_list;
-
- }
- }
-
- public void TestTVPWithAdditionalParams()
- {
- try
- {
- connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
- connection.Execute("CREATE PROC get_values @ints int_list_type READONLY, @stringParam varchar(20), @dateParam datetime AS select i.*, @stringParam as stringParam, @dateParam as dateParam from @ints i");
-
- var dynamicParameters = new DynamicParameterWithIntTVP(new int[] { 1, 2, 3 });
- dynamicParameters.AddDynamicParams(new { stringParam = "stringParam", dateParam = new DateTime(2012, 1, 1) });
-
- var results = connection.Query("get_values", dynamicParameters, commandType: CommandType.StoredProcedure).ToList();
- results.Count.IsEqualTo(3);
- for (int i = 0; i < results.Count; i++)
- {
- var result = results[i];
- Assert.IsEqualTo(i + 1, result.n);
- Assert.IsEqualTo("stringParam", result.stringParam);
- Assert.IsEqualTo(new DateTime(2012, 1, 1), result.dateParam);
- }
-
- }
- finally
- {
- try
- {
- connection.Execute("DROP PROC get_values");
- }
- finally
- {
- connection.Execute("DROP TYPE int_list_type");
- }
- }
- }
-
- class Parent
- {
- public int Id { get; set; }
- public readonly List<Child> Children = new List<Child>();
- }
- class Child
- {
- public int Id { get; set; }
- }
- public void ParentChildIdentityAssociations()
- {
- var lookup = new Dictionary<int, Parent>();
- var parents = connection.Query<Parent, Child, Parent>(@"select 1 as [Id], 1 as [Id] union all select 1,2 union all select 2,3 union all select 1,4 union all select 3,5",
- (parent, child) =>
- {
- Parent found;
- if (!lookup.TryGetValue(parent.Id, out found))
- {
- lookup.Add(parent.Id, found = parent);
- }
- found.Children.Add(child);
- return found;
- }).Distinct().ToDictionary(p => p.Id);
- p…
Large files files are truncated, but you can click here to view the full file