public void Iif() { if (!Dialect.Functions.ContainsKey("iif")) { Assert.Ignore(Dialect + "doesn't support iif function."); } using (ISession s = OpenSession()) { s.Save(new MaterialResource("Flash card 512MB", "A001/07", MaterialResource.MaterialState.Available)); s.Save(new MaterialResource("Flash card 512MB", "A002/07", MaterialResource.MaterialState.Available)); s.Save(new MaterialResource("Flash card 512MB", "A003/07", MaterialResource.MaterialState.Reserved)); s.Save(new MaterialResource("Flash card 512MB", "A004/07", MaterialResource.MaterialState.Reserved)); s.Save(new MaterialResource("Flash card 512MB", "A005/07", MaterialResource.MaterialState.Discarded)); s.Flush(); } // Statistic using (ISession s = OpenSession()) { string hql = @"select mr.Description, sum(iif(mr.State= 0,1,0)), sum(iif(mr.State= 1,1,0)), sum(iif(mr.State= 2,1,0)) from MaterialResource mr group by mr.Description"; IList lresult = s.CreateQuery(hql).List(); Assert.AreEqual("Flash card 512MB", ((IList)lresult[0])[0]); Assert.AreEqual(2, ((IList)lresult[0])[1]); Assert.AreEqual(2, ((IList)lresult[0])[2]); Assert.AreEqual(1, ((IList)lresult[0])[3]); hql = "from MaterialResource mr where iif(mr.State=2,true,false)=true"; MaterialResource result = (MaterialResource)s.CreateQuery(hql).UniqueResult(); Assert.AreEqual("A005/07", result.SerialNumber); } // clean up using (ISession s = OpenSession()) { s.Delete("from MaterialResource"); s.Flush(); } }
public async Task IifAsync() { AssumeFunctionSupported("Iif"); using (ISession s = OpenSession()) { await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A001/07", MaterialResource.MaterialState.Available))); await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A002/07", MaterialResource.MaterialState.Available))); await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A003/07", MaterialResource.MaterialState.Reserved))); await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A004/07", MaterialResource.MaterialState.Reserved))); await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A005/07", MaterialResource.MaterialState.Discarded))); await(s.FlushAsync()); } // Statistic using (ISession s = OpenSession()) { string hql = @"select mr.Description, sum(iif(mr.State= 0,1,0)), sum(iif(mr.State= 1,1,0)), sum(iif(mr.State= 2,1,0)) from MaterialResource mr group by mr.Description"; IList lresult = await(s.CreateQuery(hql).ListAsync()); Assert.AreEqual("Flash card 512MB", ((IList)lresult[0])[0]); Assert.AreEqual(2, ((IList)lresult[0])[1]); Assert.AreEqual(2, ((IList)lresult[0])[2]); Assert.AreEqual(1, ((IList)lresult[0])[3]); hql = "from MaterialResource mr where iif(mr.State=2,true,false)=true"; MaterialResource result = (MaterialResource)await(s.CreateQuery(hql).UniqueResultAsync()); Assert.AreEqual("A005/07", result.SerialNumber); } // clean up using (ISession s = OpenSession()) { await(s.DeleteAsync("from MaterialResource")); await(s.FlushAsync()); } }
public void Chr() { AssumeFunctionSupported("chr"); using (var s = OpenSession()) { var m = new MaterialResource("Blah", "000", (MaterialResource.MaterialState) 32); s.Save(m); s.Flush(); } using (var s = OpenSession()) { var space = s.CreateQuery("select chr(m.State) from MaterialResource m").UniqueResult <char>(); Assert.That(space, Is.EqualTo(' ')); var count = s .CreateQuery("select count(*) from MaterialResource m where chr(m.State) = :c") .SetCharacter("c", ' ') .UniqueResult <long>(); Assert.That(count, Is.EqualTo(1)); } }
public void Ascii() { AssumeFunctionSupported("ascii"); using (var s = OpenSession()) { var m = new MaterialResource(" ", "000", MaterialResource.MaterialState.Available); s.Save(m); s.Flush(); } using (var s = OpenSession()) { var space = s.CreateQuery("select ascii(m.Description) from MaterialResource m").UniqueResult <int>(); Assert.That(space, Is.EqualTo(32)); var count = s .CreateQuery("select count(*) from MaterialResource m where ascii(m.Description) = :c") .SetInt32("c", 32) .UniqueResult <long>(); Assert.That(count, Is.EqualTo(1)); } }
public async Task RoundAsync() { AssumeFunctionSupported("round"); using (var s = OpenSession()) { var a1 = new Animal("a1", 1.87f); await(s.SaveAsync(a1)); var m1 = new MaterialResource("m1", "18", MaterialResource.MaterialState.Available) { Cost = 51.76m }; await(s.SaveAsync(m1)); await(s.FlushAsync()); } using (var s = OpenSession()) { var roundF = await(s.CreateQuery("select round(a.BodyWeight) from Animal a").UniqueResultAsync <float>()); Assert.That(roundF, Is.EqualTo(2), "Selecting round(double) failed."); var countF = await(s .CreateQuery("select count(*) from Animal a where round(a.BodyWeight) = :c") .SetInt32("c", 2) .UniqueResultAsync <long>()); Assert.That(countF, Is.EqualTo(1), "Filtering round(double) failed."); roundF = await(s.CreateQuery("select round(a.BodyWeight, 1) from Animal a").UniqueResultAsync <float>()); Assert.That(roundF, Is.EqualTo(1.9f).Within(0.01f), "Selecting round(double, 1) failed."); countF = await(s .CreateQuery("select count(*) from Animal a where round(a.BodyWeight, 1) between :c1 and :c2") .SetDouble("c1", 1.89) .SetDouble("c2", 1.91) .UniqueResultAsync <long>()); Assert.That(countF, Is.EqualTo(1), "Filtering round(double, 1) failed."); var roundD = await(s.CreateQuery("select round(m.Cost) from MaterialResource m").UniqueResultAsync <decimal?>()); Assert.That(roundD, Is.EqualTo(52), "Selecting round(decimal) failed."); var count = await(s .CreateQuery("select count(*) from MaterialResource m where round(m.Cost) = :c") .SetInt32("c", 52) .UniqueResultAsync <long>()); Assert.That(count, Is.EqualTo(1), "Filtering round(decimal) failed."); roundD = await(s.CreateQuery("select round(m.Cost, 1) from MaterialResource m").UniqueResultAsync <decimal?>()); Assert.That(roundD, Is.EqualTo(51.8m), "Selecting round(decimal, 1) failed."); if (TestDialect.HasBrokenDecimalType) { // SQLite fails the equality test due to using double instead, wich requires a tolerance. return; } count = await(s .CreateQuery("select count(*) from MaterialResource m where round(m.Cost, 1) = :c") .SetDecimal("c", 51.8m) .UniqueResultAsync <long>()); Assert.That(count, Is.EqualTo(1), "Filtering round(decimal, 1) failed."); } }