Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

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:

INSERT, UPDATE and DELETEs

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

UPDATE

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')

UpdateOnly

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

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')

DELETE

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:

Improved Sql Expression API

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.

.


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())
{
       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);
}
Something went wrong with that request. Please try again.