public void InsertData(IDbCommand dbCmd) { if (this.IsFirstRun) { dbCmd.CreateTables(true, NorthwindFactory.ModelTypes.ToArray()); } else { NorthwindFactory.ModelTypes.ForEach(x => dbCmd.DeleteAll(x)); } NorthwindData.Categories.ForEach(x => dbCmd.Insert(x)); NorthwindData.Customers.ForEach(x => dbCmd.Insert(x)); NorthwindData.Employees.ForEach(x => dbCmd.Insert(x)); NorthwindData.Shippers.ForEach(x => dbCmd.Insert(x)); NorthwindData.Orders.ForEach(x => dbCmd.Insert(x)); NorthwindData.Products.ForEach(x => dbCmd.Insert(x)); NorthwindData.OrderDetails.ForEach(x => dbCmd.Insert(x)); NorthwindData.CustomerCustomerDemos.ForEach(x => dbCmd.Insert(x)); NorthwindData.Regions.ForEach(x => dbCmd.Insert(x)); NorthwindData.Territories.ForEach(x => dbCmd.Insert(x)); NorthwindData.EmployeeTerritories.ForEach(x => dbCmd.Insert(x)); }
public static void Main(string[] args) { OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider(); using (IDbConnection db = "User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection()) using (IDbCommand dbCmd = db.CreateCommand()) { //try{ // due to firebirdslq features, we have to drop book first and then author dbCmd.DropTable <Book>(); dbCmd.DropTable <Author>(); dbCmd.CreateTable <Author>(); dbCmd.CreateTable <Book>(); dbCmd.Insert(new Author() { Name = "Demis Bellot", Birthday = DateTime.Today.AddYears(20), Active = true, Earnings = 99.9m, Comments = "ServiceStack.Net ...", City = "London", Rate = 10 }); dbCmd.Insert(new Author() { Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(30), Active = true, Earnings = 50.25m, Comments = "OrmLite.Firebird", City = "Bogota", Rate = 9 }); dbCmd.Insert(new Author() { Name = "Adam Witco", Birthday = DateTime.Today.AddYears(25), Active = true, Comments = "other books...", City = "London", Rate = 8 }); dbCmd.Insert(new Author() { Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(28), Active = false, Comments = "other books...", City = "Bogota", Rate = 10 }); //------------------------------------------------------------------- SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>(); ev.Insert(r => new { r.Id, r.Name, r.Birthday, r.Active, r.Rate }); // fields to insert var author = new Author() { Name = "William", Birthday = DateTime.Today.AddYears(250), Active = false, City = "London", Rate = 0, Comments = "this will not be inserted" // null in db }; dbCmd.Insert(author, ev); author.Comments = "this will be updated"; ev.Update(rn => rn.Comments).Where(r => r.Id == author.Id); dbCmd.Update(author, ev); // update comment for all authors from london... author.Comments = "update from london"; ev.Where(rn => rn.City == "London"); dbCmd.Update(author, ev); // select author from Bogota ev.Where(rn => rn.City == "Bogota"); var authors = dbCmd.Select(ev); Console.WriteLine(authors.Count); // select author from Bogota and Active=true; ev.Where(rn => rn.City == "Bogota" && rn.Active == true); // sorry for firebird must write ==true ! authors = dbCmd.Select(ev); Console.WriteLine(authors.Count); //------------------------------------------------------------------- authors = dbCmd.Select <Author>(); Console.WriteLine("Rows in Author : '{0}'", authors.Count); foreach (Author a in authors) { Console.WriteLine("Id :{0} - Name : {1} -- Earnings {2}", a.Id, a.Name, a.Earnings.HasValue? a.Earnings.Value: 0.0m); } author = authors.FirstOrDefault <Author>(r => r.Name == "Angel Colmenares"); if (author != default(Author)) { dbCmd.Insert(new Book() { IdAuthor = author.Id, Title = "The big book", Price = 18.55m, }); Console.WriteLine("{0} == {1}", dbCmd.HasChildren <Book>(author), true); } else { Console.WriteLine("Something wrong "); } author = authors.FirstOrDefault <Author>(r => r.Name == "Adam Witco"); if (author != default(Author)) { Console.WriteLine("{0} == {1}", dbCmd.HasChildren <Book>(author), false); } else { Console.WriteLine("Something wrong "); } var books = dbCmd.Select <Book>(); foreach (var b in books) { Console.WriteLine("Title {0} Price {1}", b.Title, b.Price); } ev.Select(r => new { r.Name, r.Active }).Where(); // only Name and Active fields will be retrived authors = dbCmd.Select(ev); Console.WriteLine(ev.SelectExpression); foreach (Author r in authors) { Console.WriteLine("'{0}' '{1}' '{2}'", r.Name, r.Active, r.Id); } dbCmd.DeleteAll <Book>(); dbCmd.DeleteAll <Author>(); //} //catch(Exception e){ // Console.WriteLine("Error : " + e.Message); // return; //} Console.WriteLine("This is The End my friend !"); } }
public static void Main(string[] args) { Console.WriteLine("Hello World!"); OrmLiteConfig.DialectProvider = PostgreSQLDialectProvider.Instance; SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>(); using (IDbConnection db = "Server=localhost;Port=5432;User Id=postgres; Password=postgres; Database=ormlite".OpenDbConnection()) using (IDbCommand dbCmd = db.CreateCommand()) { dbCmd.DropTable <Author>(); dbCmd.CreateTable <Author>(); dbCmd.DeleteAll <Author>(); List <Author> authors = new List <Author>(); authors.Add(new Author() { Name = "Demis Bellot", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 99.9m, Comments = "CSharp books", Rate = 10, City = "London" }); authors.Add(new Author() { Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 50.0m, Comments = "CSharp books", Rate = 5, City = "Bogota" }); authors.Add(new Author() { Name = "Adam Witco", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 80.0m, Comments = "Math Books", Rate = 9, City = "London" }); authors.Add(new Author() { Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(-23), Active = true, Earnings = 60.0m, Comments = "Cooking books", Rate = 10, City = "Bogota" }); authors.Add(new Author() { Name = "Libardo Pajaro", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 80.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Jorge Garzon", Birthday = DateTime.Today.AddYears(-28), Active = true, Earnings = 70.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Alejandro Isaza", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 70.0m, Comments = "Java books", Rate = 0, City = "Bogota" }); authors.Add(new Author() { Name = "Wilmer Agamez", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 30.0m, Comments = "Java books", Rate = 0, City = "Cartagena" }); authors.Add(new Author() { Name = "Rodger Contreras", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 90.0m, Comments = "CSharp books", Rate = 8, City = "Cartagena" }); authors.Add(new Author() { Name = "Chuck Benedict", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "CSharp books", Rate = 8, City = "London" }); authors.Add(new Author() { Name = "James Benedict II", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "Java books", Rate = 5, City = "Berlin" }); authors.Add(new Author() { Name = "Ethan Brown", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 45.0m, Comments = "CSharp books", Rate = 5, City = "Madrid" }); authors.Add(new Author() { Name = "Xavi Garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 75.0m, Comments = "CSharp books", Rate = 9, City = "Madrid" }); authors.Add(new Author() { Name = "Luis garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.0m, Comments = "CSharp books", Rate = 10, City = "Mexico" }); dbCmd.InsertAll(authors); // lets start ! // select authors born 20 year ago int year = DateTime.Today.AddYears(-20).Year; int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); List <Author> result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = dbCmd.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31))); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = dbCmd.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from London, Berlin and Madrid : 6 expected = 6; ev.Where(rn => Sql.In(rn.City, new object[] { "London", "Madrid", "Berlin" })); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from Bogota and Cartagena : 7 expected = 7; ev.Where(rn => Sql.In(rn.City, new object[] { "Bogota", "Cartagena" })); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = dbCmd.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name starts with A expected = 3; ev.Where(rn => rn.Name.StartsWith("A")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; ev.Where(rn => rn.Name.ToUpper().EndsWith("GARZON")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with garzon ( no case sensitive ) expected = 3; ev.Where(rn => rn.Name.EndsWith("garzon")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name contains Benedict expected = 2; ev.Where(rn => rn.Name.Contains("Benedict")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Earnings <= 50 expected = 3; ev.Where(rn => rn.Earnings <= 50); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Rate = 10 and city=Mexio expected = 1; ev.Where(rn => rn.Rate == 10 && rn.City == "Mexico"); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; ev.Where(rn => rn.Rate == 0).Update(rn => rn.Active); var rows = dbCmd.Update(new Author() { Active = false }, ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); dbCmd.Insert(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); dbCmd.Insert(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where(rn => !rn.Active); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); //update comment for City == null expected = 2; ev.Where(rn => rn.City == null).Update(rn => rn.Comments); rows = dbCmd.Update(new Author() { Comments = "No comments" }, ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // delete where City is null expected = 2; rows = dbCmd.Delete(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new{ at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.WriteLine(ev.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); //paging : ev.Limit(0, 4); // first page, page size=4; result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); ev.Limit(4, 4); // second page result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name); ev.Limit(8, 4); // third page result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name); // select distinct.. ev.Limit().OrderBy(); // clear limit and order for postgres ev.SelectDistinct(r => r.City); expected = 6; result = dbCmd.Select(ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.WriteLine(); // Tests for predicate overloads that make use of the expression visitor Console.WriteLine("First author by name (exists)"); author = dbCmd.First <Author>(a => a.Name == "Jorge Garzon"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon", author.Name, "Jorge Garzon" == author.Name); try { Console.WriteLine("First author by name (does not exist)"); author = dbCmd.First <Author>(a => a.Name == "Does not exist"); Console.WriteLine("Expected exception thrown, OK? False"); } catch { Console.WriteLine("Expected exception thrown, OK? True"); } Console.WriteLine("First author or default (does not exist)"); author = dbCmd.FirstOrDefault <Author>(a => a.Name == "Does not exist"); Console.WriteLine("Expected:null ; OK? {0}", author == null); Console.WriteLine("First author or default by city (multiple matches)"); author = dbCmd.FirstOrDefault <Author>(a => a.City == "Bogota"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); Console.ReadLine(); Console.WriteLine("Press Enter to continue"); } Console.WriteLine("This is The End my friend!"); }
public void Shippers_UseCase() { using (IDbConnection dbConn = "User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection()) using (IDbCommand dbCmd = dbConn.CreateCommand()) { const bool overwrite = false; dbCmd.DropTable <Shipper>(); dbCmd.DropTable <ShipperType>(); dbCmd.CreateTables(overwrite, typeof(ShipperType), typeof(Shipper)); // ShipperType must be created first! int trainsTypeId, planesTypeId; //Playing with transactions using (IDbTransaction dbTrans = dbCmd.BeginTransaction()) { dbCmd.Insert(new ShipperType { Name = "Trains" }); trainsTypeId = (int)dbCmd.GetLastInsertId(); dbCmd.Insert(new ShipperType { Name = "Planes" }); planesTypeId = (int)dbCmd.GetLastInsertId(); dbTrans.Commit(); } using (IDbTransaction dbTrans = dbCmd.BeginTransaction(IsolationLevel.ReadCommitted)) { dbCmd.Insert(new ShipperType { Name = "Automobiles" }); Assert.That(dbCmd.Select <ShipperType>(), Has.Count.EqualTo(3)); dbTrans.Rollback(); } Assert.That(dbCmd.Select <ShipperType>(), Has.Count.EqualTo(2)); //Performing standard Insert's and Selects dbCmd.Insert(new Shipper { CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsTypeId }); dbCmd.Insert(new Shipper { CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesTypeId }); dbCmd.Insert(new Shipper { CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesTypeId }); var trainsAreUs = dbCmd.First <Shipper>("\"Type\" = {0}", trainsTypeId); Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us")); Assert.That(dbCmd.Select <Shipper>("CompanyName = {0} OR Phone = {1}", "Trains R Us", "555-UNICORNS"), Has.Count.EqualTo(2)); Assert.That(dbCmd.Select <Shipper>("\"Type\" = {0}", planesTypeId), Has.Count.EqualTo(2)); //Lets update a record trainsAreUs.Phone = "666-TRAINS"; dbCmd.Update(trainsAreUs); Assert.That(dbCmd.GetById <Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS")); //Then make it dissappear dbCmd.Delete(trainsAreUs); Assert.That(dbCmd.GetByIdOrDefault <Shipper>(trainsAreUs.Id), Is.Null); //And bring it back again dbCmd.Insert(trainsAreUs); //Performing custom queries //Select only a subset from the table var partialColumns = dbCmd.Select <SubsetOfShipper>(typeof(Shipper), "\"Type\" = {0}", planesTypeId); Assert.That(partialColumns, Has.Count.EqualTo(2)); //Select into another POCO class that matches sql var rows = dbCmd.Select <ShipperTypeCount>( "SELECT \"Type\" as ShipperTypeId, COUNT(*) AS Total FROM ShippersT GROUP BY \"Type\" ORDER BY COUNT(*)"); Assert.That(rows, Has.Count.EqualTo(2)); Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsTypeId)); Assert.That(rows[0].Total, Is.EqualTo(1)); Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesTypeId)); Assert.That(rows[1].Total, Is.EqualTo(2)); //And finally lets quickly clean up the mess we've made: dbCmd.DeleteAll <Shipper>(); dbCmd.DeleteAll <ShipperType>(); Assert.That(dbCmd.Select <Shipper>(), Has.Count.EqualTo(0)); Assert.That(dbCmd.Select <ShipperType>(), Has.Count.EqualTo(0)); } }
private static void TestDialect(Dialect dialect) { Console.Clear(); Console.WriteLine("Testing expressions for Dialect {0}", dialect.Name); OrmLiteConfig.ClearCache(); OrmLiteConfig.DialectProvider = dialect.DialectProvider; SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>(); using (IDbConnection db = dialect.ConnectionString.OpenDbConnection()) using (IDbCommand dbCmd = db.CreateCommand()) { try { dbCmd.DropTable <Author>(); var tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(dbCmd, typeof(Author).Name); Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.FalseString, tableExists.ToString(), !tableExists ? "OK" : "************** FAILED ***************"); dbCmd.CreateTable <Author>(); tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(dbCmd, typeof(Author).Name); Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.TrueString, tableExists.ToString(), tableExists ? "OK" : "************** FAILED ***************"); dbCmd.DeleteAll <Author>(); Console.WriteLine("Inserting..."); DateTime t1 = DateTime.Now; dbCmd.InsertAll(authors); DateTime t2 = DateTime.Now; Console.WriteLine("Inserted {0} rows in {1}", authors.Count, t2 - t1); Console.WriteLine("Selecting....."); int year = DateTime.Today.AddYears(-20).Year; var lastDay = new DateTime(year, 12, 31); int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Console.WriteLine(ev.ToSelectStatement()); List <Author> result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); Author a = new Author() { Birthday = lastDay }; result = dbCmd.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= a.Birthday); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors from London, Berlin and Madrid : 6 expected = 6; //Sql.In can take params object[] var city = "Berlin"; ev.Where(rn => Sql.In(rn.City, "London", "Madrid", city)); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => Sql.In(rn.City, new[] { "London", "Madrid", "Berlin" })); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors from Bogota and Cartagena : 7 expected = 7; //... or Sql.In can take List<Object> city = "Bogota"; List <Object> cities = new List <Object>(); cities.Add(city); cities.Add("Cartagena"); ev.Where(rn => Sql.In(rn.City, cities)); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name starts with A expected = 3; ev.Where(rn => rn.Name.StartsWith("A")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Name.StartsWith("A")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; var name = "GARZON"; ev.Where(rn => rn.Name.ToUpper().EndsWith(name)); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Name.ToUpper().EndsWith(name)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name ends with garzon //A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters //in the string. //An underscore ("_") in the LIKE pattern matches any single character in the string. //Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). expected = 3; ev.Where(rn => rn.Name.EndsWith("garzon")); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Name.EndsWith("garzon")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name contains Benedict expected = 2; name = "Benedict"; ev.Where(rn => rn.Name.Contains(name)); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Name.Contains("Benedict")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); a.Name = name; result = dbCmd.Select <Author>(rn => rn.Name.Contains(a.Name)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors with Earnings <= 50 expected = 3; var earnings = 50; ev.Where(rn => rn.Earnings <= earnings); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Earnings <= 50); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors with Rate = 10 and city=Mexio expected = 1; city = "Mexico"; ev.Where(rn => rn.Rate == 10 && rn.City == city); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = dbCmd.Select <Author>(rn => rn.Rate == 10 && rn.City == "Mexico"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); a.City = city; result = dbCmd.Select <Author>(rn => rn.Rate == 10 && rn.City == a.City); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; var rate = 0; ev.Where(rn => rn.Rate == rate).Update(rn => rn.Active); var rows = dbCmd.Update(new Author() { Active = false }, ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); dbCmd.Insert(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); dbCmd.Insert(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where(rn => !rn.Active); result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); //update comment for City == null expected = 2; ev.Where(rn => rn.City == null).Update(rn => rn.Comments); rows = dbCmd.Update(new Author() { Comments = "No comments" }, ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // delete where City is null expected = 2; rows = dbCmd.Delete(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new { at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); Console.WriteLine(ev.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name ? "OK" : "************** FAILED ***************"); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = dbCmd.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // and finally lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), rn.Name), rn.City }); Console.WriteLine(ev.SelectExpression); result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); //paging : ev.Limit(0, 4); // first page, page size=4; result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Limit(4, 4); // second page result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Limit(8, 4); // third page result = dbCmd.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); // select distinct.. ev.Limit().OrderBy(); // clear limit, clear order for postres ev.SelectDistinct(r => r.City); expected = 6; result = dbCmd.Select(ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Max(r.Birthday), "Birthday")); result = dbCmd.Select(ev); var expectedResult = authors.Max(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Max(r.Birthday), r.Birthday)); result = dbCmd.Select(ev); expectedResult = authors.Max(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday ? "OK" : "************** FAILED ***************"); var r1 = dbCmd.FirstOrDefault(ev); Console.WriteLine("FOD: Expected:{0} Selected {1} {2}", expectedResult, r1.Birthday, expectedResult == r1.Birthday ? "OK" : "************** FAILED ***************"); var r2 = dbCmd.GetScalar <Author, DateTime>(e => Sql.Max(e.Birthday)); Console.WriteLine("GetScalar DateTime: Expected:{0} Selected {1} {2}", expectedResult, r2, expectedResult == r2 ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Min(r.Birthday), "Birthday")); result = dbCmd.Select(ev); expectedResult = authors.Min(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Min(r.Birthday), r.Birthday)); result = dbCmd.Select(ev); expectedResult = authors.Min(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday? "OK" : "************** FAILED ***************"); ev.Select(r => new{ r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), "Birthday") }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = dbCmd.Select(ev); var expectedStringResult = "Berlin"; Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); ev.Select(r => new{ r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), r.Birthday) }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = dbCmd.Select(ev); expectedStringResult = "Berlin"; Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); r1 = dbCmd.FirstOrDefault(ev); Console.WriteLine("FOD: Expected:{0} Selected {1} {2}", expectedResult, r1.City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); var expectedDecimal = authors.Max(e => e.Earnings); Decimal?r3 = dbCmd.GetScalar <Author, Decimal?>(e => Sql.Max(e.Earnings)); Console.WriteLine("GetScalar decimal?: Expected:{0} Selected {1} {2}", expectedDecimal, r3.Value, expectedDecimal == r3.Value ? "OK" : "************** FAILED ***************"); var expectedString = authors.Max(e => e.Name); string r4 = dbCmd.GetScalar <Author, String>(e => Sql.Max(e.Name)); Console.WriteLine("GetScalar string?: Expected:{0} Selected {1} {2}", expectedString, r4, expectedString == r4 ? "OK" : "************** FAILED ***************"); var expectedDate = authors.Max(e => e.LastActivity); DateTime?r5 = dbCmd.GetScalar <Author, DateTime?>(e => Sql.Max(e.LastActivity)); Console.WriteLine("GetScalar datetime?: Expected:{0} Selected {1} {2}", expectedDate, r5, expectedDate == r5 ? "OK" : "************** FAILED ***************"); var expectedDate51 = authors.Where(e => e.City == "Bogota").Max(e => e.LastActivity); DateTime?r51 = dbCmd.GetScalar <Author, DateTime?>( e => Sql.Max(e.LastActivity), e => e.City == "Bogota"); Console.WriteLine("GetScalar datetime?: Expected:{0} Selected {1} {2}", expectedDate51, r51, expectedDate51 == r51 ? "OK" : "************** FAILED ***************"); try{ var expectedBool = authors.Max(e => e.Active); bool r6 = dbCmd.GetScalar <Author, bool>(e => Sql.Max(e.Active)); Console.WriteLine("GetScalar bool: Expected:{0} Selected {1} {2}", expectedBool, r6, expectedBool == r6 ? "OK" : "************** FAILED ***************"); } catch (Exception e) { if (dialect.Name == "PostgreSQL") { Console.WriteLine("OK PostgreSQL: " + e.Message); } else { Console.WriteLine("************** FAILED *************** " + e.Message); } } // Tests for predicate overloads that make use of the expression visitor Console.WriteLine("First author by name (exists)"); author = dbCmd.First <Author>(q => q.Name == "Jorge Garzon"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon", author.Name, "Jorge Garzon" == author.Name); try { Console.WriteLine("First author by name (does not exist)"); author = dbCmd.First <Author>(q => q.Name == "Does not exist"); Console.WriteLine("Expected exception thrown, OK? False"); } catch { Console.WriteLine("Expected exception thrown, OK? True"); } Console.WriteLine("First author or default (does not exist)"); author = dbCmd.FirstOrDefault <Author>(q => q.Name == "Does not exist"); Console.WriteLine("Expected:null ; OK? {0}", author == null); Console.WriteLine("First author or default by city (multiple matches)"); author = dbCmd.FirstOrDefault <Author>(q => q.City == "Bogota"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); a.City = "Bogota"; author = dbCmd.FirstOrDefault <Author>(q => q.City == a.City); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); // count test var expectedCount = authors.Count(); long r7 = dbCmd.GetScalar <Author, long>(e => Sql.Count(e.Id)); Console.WriteLine("GetScalar long: Expected:{0} Selected {1} {2}", expectedCount, r7, expectedCount == r7 ? "OK" : "************** FAILED ***************"); expectedCount = authors.Count(e => e.City == "Bogota"); r7 = dbCmd.GetScalar <Author, long>( e => Sql.Count(e.Id), e => e.City == "Bogota"); Console.WriteLine("GetScalar long: Expected:{0} Selected {1} {2}", expectedCount, r7, expectedCount == r7 ? "OK" : "************** FAILED ***************"); // more updates..... Console.WriteLine("more updates....................."); ev.Update();// all fields will be updated // select and update expected = 1; var rr = dbCmd.FirstOrDefault <Author>(rn => rn.Name == "Luis garzon"); rr.City = "Madrid"; rr.Comments = "Updated"; ev.Where(r => r.Id == rr.Id); // if omit, then all records will be updated rows = dbCmd.Update(rr, ev); // == dbCmd.Update(rr) but it returns void Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = 0; ev.Where(r => r.City == "Ciudad Gotica"); rows = dbCmd.Update(rr, ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = dbCmd.Select <Author>(x => x.City == "Madrid").Count; author = new Author() { Active = false }; rows = dbCmd.Update(author, x => x.Active, x => x.City == "Madrid"); Console.WriteLine("Expected:{0} Updated:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = dbCmd.Select <Author>(x => x.Active == false).Count; rows = dbCmd.Delete <Author>(x => x.Active == false); Console.WriteLine("Expected:{0} Deleted:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); DateTime t3 = DateTime.Now; Console.WriteLine("Expressions test in: {0}", t3 - t2); Console.WriteLine("All test in : {0}", t3 - t1); } catch (Exception e) { Console.WriteLine(e.Message); } } Console.WriteLine("Press enter to return to main menu"); Console.ReadLine(); PaintMenu(); }