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 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();
}
