Release Notes
##v3.85 of OrmLite Released!
The biggest addition to this release are the typed and un-typed expression-based APIs for Updating, Inserting and Deleting POCOs:
To see the behaviour of the different APIs, all examples uses this simple model
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
}
In its most simple form, updating any model without any filters will update every field, except the Id which is used to filter the update to this specific record:
db.Update(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27});UPDATE "Person" SET "FirstName" = 'Jimi',"LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1
If you supply your own where expression, it updates every field (inc. Id) but uses your filter instead:
db.Update(new Person { Id = 1, FirstName = "JJ" }, p => p.LastName == "Hendrix");UPDATE "Person" SET "Id" = 1,"FirstName" = 'JJ',"LastName" = NULL,"Age" = NULL WHERE ("LastName" = 'Hendrix')
One way to limit the fields which gets updated is to use an Anonymous Type:
db.Update<Person>(new { FirstName = "JJ" }, p => p.LastName == "Hendrix");Or by using UpdateNonDefaults which only updates the non-default values in your model using the filter specified:
db.UpdateNonDefaults(new Person { FirstName = "JJ" }, p => p.LastName == "Hendrix");UPDATE "Person" SET "FirstName" = 'JJ' WHERE ("LastName" = 'Hendrix')
As updating a partial row is a common use-case in Db's, we've added a number of methods for just this purpose, named UpdateOnly.
The first expression in an UpdateOnly statement is used to specify which fields should be updated:
db.UpdateOnly(new Person { FirstName = "JJ" }, p => p.FirstName);UPDATE "Person" SET "FirstName" = 'JJ'
When present, the second expression is used as the where filter:
db.UpdateOnly(new Person { FirstName = "JJ" }, p => p.FirstName, p => p.LastName == "Hendrix");UPDATE "Person" SET "FirstName" = 'JJ' WHERE ("LastName" = 'Hendrix')
Instead of using the expression filters above you can choose to use an ExpressionVisitor builder which provides more flexibility when you want to programatically construct the update statement:
db.UpdateOnly(new Person { FirstName = "JJ", LastName = "Hendo" }, ev => ev.Update(p => p.FirstName));UPDATE "Person" SET "FirstName" = 'JJ'
db.UpdateOnly(new Person { FirstName = "JJ" }, ev => ev.Update(p => p.FirstName).Where(x => x.FirstName == "Jimi"));UPDATE "Person" SET "FirstName" = 'JJ' WHERE ("LastName" = 'Hendrix')
For the ultimate flexibility we also provide un-typed, string-based expressions. Use the .Params() extension method
escape parameters (inspired by massive):
db.Update<Person>(set: "FirstName = {0}".Params("JJ"), where: "LastName = {0}".Params("Hendrix"));Even the Table name can be a string so you perform the same update without requiring the Person model at all:
db.Update(table: "Person", set: "FirstName = {0}".Params("JJ"), where: "LastName = {0}".Params("Hendrix"));UPDATE "Person" SET FirstName = 'JJ' WHERE LastName = 'Hendrix'
Insert's are pretty straight forward since in most cases you want to insert every field:
db.Insert(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });INSERT INTO "Person" ("Id","FirstName","LastName","Age") VALUES (1,'Jimi','Hendrix',27)
But do provide an API that takes an Expression Visitor for the rare cases you don't want to insert every field
db.InsertOnly(new Person { FirstName = "Amy" }, ev => ev.Insert(p => new { p.FirstName }));INSERT INTO "Person" ("FirstName") VALUES ('Amy')
Like updates for DELETE's we also provide APIs that take a where Expression:
db.Delete<Person>(p => p.Age == 27);Or an Expression Visitor:
db.Delete<Person>(ev => ev.Where(p => p.Age == 27));DELETE FROM "Person" WHERE ("Age" = 27)
As well as un-typed, string-based expressions:
db.Delete<Person>(where: "Age = {0}".Params(27));Which also can take a table name so works without requiring a typed Person model
db.Delete(table: "Person", where: "Age = {0}".Params(27));DELETE FROM "Person" WHERE Age = 27
##v3.48 of OrmLite Released!
Thanks to our new PostgreSQL maintainer @tomaszkubacki we're happy to bring you OrmLite for PostgreSQL, now available on NuGet.
With this addition OrmLite now supports all major Open Source RDBMS's (inc SQL Server) which can all be installed via NuGet:
- ServiceStack.OrmLite.PostgreSQL
- ServiceStack.OrmLite.SqlServer
- ServiceStack.OrmLite.MySql
- ServiceStack.OrmLite.Firebird
- ServiceStack.OrmLite.Sqlite32
- ServiceStack.OrmLite.Sqlite64
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;
db.Select<Author>(q => q.Birthday >= new DateTime(agesAgo, 1, 1) && q.Birthday <= new DateTime(agesAgo, 12, 31));
WHERE (("Birthday" >= '1992-01-01 00:00:00.000') AND ("Birthday" <= '1992-12-31 00:00:00.000'))
db.Select<Author>(q => Sql.In(q.City, "London", "Madrid", "Berlin"));WHERE "JobCity" In ('London', 'Madrid', 'Berlin')
db.Select<Author>(q => q.Earnings <= 50);WHERE ("Earnings" <= 50)
db.Select<Author>(q => q.Name.StartsWith("A"));WHERE upper("Name") like 'A%'
db.Select<Author>(q => q.Name.EndsWith("garzon"));WHERE upper("Name") like '%GARZON'
db.Select<Author>(q => q.Name.Contains("Benedict"));WHERE upper("Name") like '%BENEDICT%'
db.Select<Author>(q => q.Rate == 10 && q.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.
.
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.
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:
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())
{
db.DropTable<Author>();
db.CreateTable<Author>();
db.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"},
};
db.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=db.Select(ev);
// without SqlExpressionVisitor you must write:
// db.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=db.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=db.Select(ev);
// select authors which name starts with A :3
ev.Where(rn=> rn.Name.StartsWith("A"));
result=db.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=db.Select(ev);
// select authors which name ends with garzon ( case sensitive )
ev.Where(rn=> rn.Name.EndsWith("garzon"));
result=db.Select(ev);
// select authors which name contains Benedict
ev.Where(rn=> rn.Name.Contains("Benedict"));
result=db.Select(ev);
// select authors with Earnings <= 50
ev.Where(rn=> rn.Earnings<=50 );
result=db.Select(ev);
// select authors with Rate = 10 and city=Mexico
ev.Where(rn=> rn.Rate==10 && rn.City=="Mexico");
result=db.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 = db.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} );
db.Insert(new Author(){Active=false,Rate=0,Name="Victor Grozny", Birthday=DateTime.Today.AddYears(-18) }, ev);
db.Insert(new Author(){Active=false,Rate=0,Name="Ivan Chorny", Birthday=DateTime.Today.AddYears(-19) }, ev);
ev.Where(rn=> !rn.Active);
result=db.Select(ev);
// delete where City is null : 2 records
ev.Where( rn => rn.City==null );
rows = db.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=db.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=db.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=db.Select(ev);
author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia
Espinel".ToUpper(), author.Name, "Claudia
Espinel".ToUpper()==author.Name);
}