Skip to content

Release Notes

Jump to bottom Edit New page
mythz edited this page Feb 9, 2012 · 14 revisions

#v3.48 of OrmLite Released!

Thanks to our new PostgreSQL maintainer @tomaszkubacki we're happy to bring you OrmLite for PostgreSQL on NuGet.

With this addition OrmLite now supports all major Open Source RDBMS's (inc SQL Server) which can all be installed via NuGet:

We've also improved our SQL Expression support to bring you even nicer LINQ-liked querying to all our providers. To give you a flavour here are some examples with their partial SQL output (done in SQL Server):

int agesAgo = DateTime.Today.AddYears(-20).Year;
dbCmd.Select<Author>(rn => rn.Birthday >= new DateTime(agesAgo, 1, 1) && rn.Birthday <= new DateTime(agesAgo, 12, 31));

WHERE (("Birthday" >= '1992-01-01 00:00:00.000') AND ("Birthday" <= '1992-12-31 00:00:00.000'))

dbCmd.Select<Author>(rn => Sql.In(rn.City, "London", "Madrid", "Berlin"));

WHERE "JobCity" In ('London', 'Madrid', 'Berlin')

dbCmd.Select<Author>(rn => rn.Earnings <= 50);

WHERE ("Earnings" <= 50)

dbCmd.Select<Author>(rn => rn.Name.StartsWith("A"));

WHERE upper("Name") like 'A%'

dbCmd.Select<Author>(rn => rn.Name.EndsWith("garzon"));

WHERE upper("Name") like '%GARZON'

dbCmd.Select<Author>(rn => rn.Name.Contains("Benedict"));

WHERE upper("Name") like '%BENEDICT%'

dbCmd.Select<Author>(rn => rn.Rate == 10 && rn.City == "Mexico");

WHERE (("Rate" = 10) AND ("JobCity" = 'Mexico'))

Right now the Expression support can satisfy many simple queries with a strong-typed API. For anything more complex e.g. queries with table joins you can still easily fall back to raw SQL.

With the SqlExpression framework now in place for all RDBMS's drivers - we intend on broadening its support even further in future. We also welcome any pull-requests and contributions if you would like to see your favourite feature implemented sooner.

.

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:

MySQL OrmLite client on NuGet

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

Firebird OrmLite client on NuGet

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

	var authors = new List<Author> {
	new Author {Name="Demis Bellot",Birthday=DateTime.Today.AddYears(-20),
		Active=true,Earnings=99.9m,Comments="CSharp books",Rate=10, City="London"},
	new Author {Name="Angel Colmenares",Birthday=DateTime.Today.AddYears(-25),
		Active=true,Earnings=50.0m,Comments="CSharp books", Rate=5, City="Bogota"},
	new Author {Name="Adam Witco",Birthday=DateTime.Today.AddYears(-20),
		Active=true,Earnings=80.0m,Comments="Math Books", Rate=9, City="London"},
	new Author {Name="Claudia Espinel",Birthday=DateTime.Today.AddYears(-23),
		Active=true,Earnings=60.0m,Comments="Cooking books", Rate=10, City="Bogota"},
	new Author {Name="Libardo Pajaro",Birthday=DateTime.Today.AddYears(-25),
		Active=true,Earnings=80.0m,Comments="CSharp books", Rate=9, City="Bogota"},
	new Author {Name="Jorge Garzon",Birthday=DateTime.Today.AddYears(-28),
		Active=true,Earnings=70.0m,Comments="CSharp books", Rate=9, City="Bogota"},
	new Author {Name="Alejandro Isaza",Birthday=DateTime.Today.AddYears(-20),
		Active=true,Earnings=70.0m,Comments="Java books", Rate=0, City="Bogota"},
	new Author {Name="Wilmer Agamez",Birthday=DateTime.Today.AddYears(-20),
		Active=true,Earnings=30.0m,Comments="Java books", Rate=0, City="Cartagena"},
	new Author {Name="Rodger Contreras",Birthday=DateTime.Today.AddYears(-25),
		Active=true,Earnings=90.0m,Comments="CSharp books", Rate=8, City="Cartagena"},
	new Author {Name="Chuck Benedict",Birthday=DateTime.Today.AddYears(-22),
		Active=true,Earnings=85.5m,Comments="CSharp books", Rate=8, City="London"},
	new Author {Name="James Benedict II",Birthday=DateTime.Today.AddYears(-22),
		Active=true,Earnings=85.5m,Comments="Java books", Rate=5, City="Berlin"},
	new Author {Name="Ethan Brown",Birthday=DateTime.Today.AddYears(-20),
		Active=true,Earnings=45.0m,Comments="CSharp books", Rate=5, City="Madrid"},
	new Author {Name="Xavi Garzon",Birthday=DateTime.Today.AddYears(-22),
		Active=true,Earnings=75.0m,Comments="CSharp books", Rate=9, City="Madrid"},
	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);
}
Add a custom footer
Add a custom sidebar
Clone this wiki locally