Release Notes

v3.43 of OrmLite Released!

OrmLite has recently received a number of generous contributions from the Open Source community that warrants their own separate release notes - which were normally combined in ServiceStack's Release notes.

MySql and Firebird OrmLite Clients now available!

The MySQL client was contributed by Thomas Grassauer (@brainless83) and is available on NuGet at:

The Firebird client was contributed by Angel ignacio colmenares laguado (@angelcolmenares) and is available on NuGet at:

LINQ-like Expression Visitor

Angel has also contributed a LINQ-like SQL Expression Visitor allowing strong-typed queries in OrmLite! His examples below demonstrate some of its features:

OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
SqlExpressionVisitor<Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Author>();

var connStr = "User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;";
using (IDbConnection db = connStr.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;

	ev.Where(rn=> rn.Birthday>=new DateTime(year, 1,1) && rn.Birthday<=new DateTime(year, 12,31));
	List<Author> result=dbCmd.Select(ev);
	
	// without SqlExpressionVisitor you must write:
	// dbCmd.Select<Author>("Birthday>={0} and Birthday<={1}",
	// new DateTime(year, 1,1),
	// new DateTime(year, 12,31));
	// but you have to know that  fieldname is "Birhtday" or "BirthDay" ?
	// and fieldname must be quoted ?
	// select authors from London, Berlin and Madrid : 6
	ev.Where(rn=> Sql.In( rn.City, new object[]{"London", "Madrid",	"Berlin"}));
	result=dbCmd.Select(ev);

	// select authors from Bogota and Cartagena : 7
	List<object> cities = new List<object>(new object[]{"Bogota","Cartagena"}); //works only object..
	ev.Where(rn=> Sql.In( rn.City, cities));
	result=dbCmd.Select(ev);

	// select authors which name starts with A :3
	ev.Where(rn=>  rn.Name.StartsWith("A"));
	result=dbCmd.Select(ev);

	// select authors which name ends with Garzon o GARZON o garzon
	( no case sensitive )
	ev.Where(rn=>  rn.Name.ToUpper().EndsWith("GARZON"));
	result=dbCmd.Select(ev);

	// select authors which name ends with garzon ( case sensitive )
	ev.Where(rn=>  rn.Name.EndsWith("garzon"));
	result=dbCmd.Select(ev);

	// select authors which name contains Benedict
	ev.Where(rn=>  rn.Name.Contains("Benedict"));
	result=dbCmd.Select(ev);

	// select authors with Earnings <= 50
	ev.Where(rn=>  rn.Earnings<=50 );
	result=dbCmd.Select(ev);

	// select authors with Rate = 10 and city=Mexico
	ev.Where(rn=>  rn.Rate==10 && rn.City=="Mexico");
	result=dbCmd.Select(ev);

	//  enough selecting, lets upate;
	// set Active=false where rate =0  (2 records)
	ev.Where(rn=>  rn.Rate==0 ).Update(rn=> rn.Active);
	var rows = dbCmd.Update( new Author(){ Active=false }, ev);
	Console.WriteLine(rows);

	// insert values  only in Id, Name, Birthday, Rate and Active fields
	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);

	// delete where City is null :  2 records
	ev.Where( rn => rn.City==null );
	rows = dbCmd.Delete( ev);

	//   lets select  all records ordered by Rate Descending and Name Ascending
	ev.Where().OrderBy(rn=> new{ at=Sql.Desc(rn.Rate), rn.Name }); //clear where condition
	result=dbCmd.Select(ev);
	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 ....
	ev.Limit(5); // note: order is the same as in the last sentence
	result=dbCmd.Select(ev);

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