/// <summary>
/// Delete the rows that matches the where expression, e.g:
///
/// dbCmd.Delete<Person>(p => p.Age == 27);
/// DELETE FROM "Person" WHERE ("Age" = 27)
/// </summary>
public static int Delete <T>(this IDbCommand dbCmd, Expression <Func <T, bool> > where)
{
var ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <T>();
ev.Where(where);
return(dbCmd.Delete(ev));
}
public static int Delete <T>(this IDbCommand dbCmd, Expression <Func <T, bool> > where, Action <IDbCommand> commandFilter = null)
{
var ev = dbCmd.GetDialectProvider().SqlExpression <T>();
ev.Where(where);
return(dbCmd.Delete(ev, commandFilter));
}
public void Delete<T>(Expression<Func<T,bool>> predicate)
where T: new()
{
Execute(dbCmd=>{
dbCmd.Delete(predicate);
});
}
public void Simple_CRUD_example()
{
using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(false);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Console.WriteLine(string.Format("rowsB, Has.Count({0})", rowsB.Count));
var rowIds = rowsB.ConvertAll(x => x.Id.ToString());
Console.WriteLine(string.Format("rowIds: {0}", string.Join(", ", rowIds.ToArray())));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Console.WriteLine(string.Format("rowsB, Has.Count({0})", rowsB.Count));
var rowsLeft = dbCmd.Select <User>();
Console.WriteLine(string.Format("rowsLeft, Has.Count({0})", rowsLeft.Count));
Console.WriteLine(string.Format("rowsLeft[0].Name = {0}", rowsLeft[0].Name));
}
}
public static void Main(string[] args)
{
//Set one before use (i.e. in a static constructor).
OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
using (IDbConnection db =
"User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
using (IDbCommand dbConn = db.CreateCommand())
{
//try{
dbConn.Insert(new User
{
Name = string.Format("Hello, World! {0}", DateTime.Now),
Password = "******",
Col1 = "01",
Col2 = "02",
Col3 = "03"
});
User user = new User()
{
Name = "New User ",
Password = "******",
Col1 = "XX",
Col2 = "YY",
Col3 = "ZZ",
Active = true
};
dbConn.Insert(user);
Console.WriteLine("++++++++++Id for {0} {1}", user.Name, user.Id);
var rows = dbConn.Select <User>();
Console.WriteLine("++++++++++++++records in users {0}", rows.Count);
foreach (User u in rows)
{
Console.WriteLine("{0} -- {1} -- {2} -- {3} -{4} --{5} ", u.Id, u.Name, u.SomeStringProperty, u.SomeDateTimeProperty,
(u.SomeInt32NullableProperty.HasValue)?u.SomeDateTimeNullableProperty.Value.ToString(): "",
u.Active);
dbConn.Delete(u);
}
rows = dbConn.Select <User>();
Console.WriteLine("-------------records in users after delete {0}", rows.Count);
//}
//catch(Exception e){
// Console.WriteLine(e);
//}
}
}
public void Simple_CRUD_example()
{
//using (IDbConnection db = ":memory:".OpenDbConnection())
var connStr = "Data Source=.;Initial Catalog=TestDb;Integrated Security=True";
var sqlServerFactory = new OrmLiteConnectionFactory(connStr, SqlServerOrmLiteDialectProvider.Instance);
using (IDbConnection db = sqlServerFactory.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <Dual>(true);
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new Dual {
Name = "Dual"
});
var lastInsertId = dbCmd.GetLastInsertId();
Assert.That(lastInsertId, Is.GreaterThan(0));
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public void Simple_CRUD_example()
{
var path = Config.SqliteFileDb;
if (File.Exists(path))
{
File.Delete(path);
}
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = path.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
var rowsB1 = dbCmd.Select <User>(user => user.Name == "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
Assert.That(rowsB1, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
File.Delete(path);
}
public void Simple_CRUD_example()
{
using (IDbConnection db = "User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
dbCmd.CreateTable <GuidId>(true);
Guid g = Guid.NewGuid();
dbCmd.Insert(new GuidId {
Id = g
});
GuidId gid = dbCmd.First <GuidId>("Id = {0}", g);
Assert.That(g == gid.Id);
}
}
public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
{
var dbFactory = new OrmLiteConnectionFactory(
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
SqlServerOrmLiteDialectProvider.Instance);
using (IDbConnection db = dbFactory.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
CreateSchemaIfNotExists(dbCmd);
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var lastInsertId = dbCmd.GetLastInsertId();
Assert.That(lastInsertId, Is.GreaterThan(0));
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
{
var dbFactory = new OrmLiteConnectionFactory(
"User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;",
FirebirdOrmLiteDialectProvider.Instance);
using (IDbConnection db = dbFactory.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var lastInsertId = dbCmd.GetLastInsertId();
Assert.That(lastInsertId, Is.GreaterThan(0));
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public void Simple_CRUD_example()
{
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = "~/App_Data/db.sqlite".MapAbsolutePath().OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public void Simple_CRUD_example()
{
using (IDbConnection dbConn = ConfigurationManager.ConnectionStrings["testDb"].ConnectionString.OpenDbConnection())
using (IDbCommand dbCmd = dbConn.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => dbCmd.Delete(x));
rowsB = dbCmd.Select <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
internal static int Delete <T>(this IDbCommand dbCmd, Func <SqlExpression <T>, SqlExpression <T> > where)
{
return(dbCmd.Delete(where (dbCmd.GetDialectProvider().SqlExpression <T>())));
}
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));
}
}
public static int Delete<T>(this IDbCommand dbCmd, Expression<Func<T, bool>> where)
{
var ev = dbCmd.GetDialectProvider().SqlExpression<T>();
ev.Where(where);
return dbCmd.Delete(ev);
}
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!");
}
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();
}
private static void TestDialect(Dialect dialect)
{
Console.Clear();
Console.WriteLine("Testing expressions for Dialect {0}", dialect.Name);
OrmLiteConfig.ClearCache();
OrmLiteConfig.DialectProvider = dialect.DialectProvider;
using (IDbConnection db =
dialect.ConnectionString.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.DropTable <Person>();
dbCmd.DropTable <City>();
dbCmd.DropTable <Country>();
dbCmd.CreateTable <Country>();
dbCmd.CreateTable <City>();
dbCmd.CreateTable <Person>();
dbCmd.InsertAll <Country>(Factory.CountryList);
dbCmd.InsertAll <City>(Factory.CityList);
dbCmd.InsertAll <Person>(Factory.PersonList);
try{
var vis = ReadExtensions.CreateExpression <TestPerson>();
vis.Where(r => r.Continent == "Europe");
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.ExcludeJoin = true;
vis.Where();
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
var r0 = dbCmd.Select <TestPerson>();
Console.WriteLine("Records en person: '{0}'", r0.Count);
vis.ExcludeJoin = false;
vis.Select(r => new { r.Continent, r.Name }).OrderBy(r => r.BirthCountry);
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.SelectDistinct(r => r.Name);
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.Select();
vis.Where(r => r.Continent == "Europe").OrderBy(r => r.BirthCountry);
r0 = dbCmd.Select(vis);
Console.WriteLine("Records en person r.Continent=='Europe': '{0}'", r0.Count);
r0 = dbCmd.Select <TestPerson>(r => r.BirthCity == "London");
Console.WriteLine("Records en person r.BirthCity=='London': '{0}'", r0.Count);
TestPerson tp = r0[0];
tp.Id = 0;
dbCmd.Insert(tp);
tp.Id = (int)dbCmd.GetLastInsertId();
Console.WriteLine("El id es :'{0}'", tp.Id);
Console.WriteLine("Actualizados : '{0}'", dbCmd.UpdateOnly(tp, r => r.Name, r => r.Id == 0));
try{
dbCmd.Update(tp); // all fields, except PK are updated where tp.Id==15
}
catch (Exception e) {
Console.WriteLine(e.Message);
}
Console.WriteLine("Borrados : '{0}'", dbCmd.Delete <TestPerson>(r => r.Id == 0));
int expected = 6;
var r1 = dbCmd.Select <City>(qr => qr.Population >= 10);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r1.Count, expected == r1.Count?"OK":"********* FAILED *********");
expected = 7;
var r2 = dbCmd.Select <Join1>(qr => qr.Population <= 5);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r2.Count, expected == r2.Count?"OK":"********* FAILED *********");
expected = 3;
var r3 = dbCmd.Select <Join2>(qr => qr.BirthCity == "London");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r3.Count, expected == r3.Count?"OK":"********* FAILED *********");
expected = 5;
var r4 = dbCmd.Select <Join3>(qr => qr.Continent == "Europe");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r4.Count, expected == r4.Count?"OK":"********* FAILED *********");
expected = 5;
var city = "Bogota";
var r5 = dbCmd.Select <PersonCity>(qr => qr.JobCity == city);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r5.Count, expected == r5.Count?"OK":"********* FAILED *********");
expected = 6;
var r6 = dbCmd.Select <DerivatedFromPerson>(qr => qr.BirthCityId != qr.JobCityId);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r6.Count, expected == r6.Count?"OK":"********* FAILED *********");
expected = 2;
var r7 = dbCmd.Select <DerivatedFromDerivatedFromPerson>(qr => qr.Continent == "Asia");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r7.Count, expected == r7.Count?"OK":"********* FAILED *********");
var r8 = dbCmd.Select <DerivatedFromDerivatedFromPerson>(
exp => exp.
Where(qr => qr.BirthCityId != qr.JobCityId).
OrderBy(qr => qr.Continent));
Console.WriteLine("Expected:{0} Selected:{1} {2}", "America", r8.FirstOrDefault().Continent, "America" == r8.FirstOrDefault().Continent?"OK":"********* FAILED *********");
}
catch (Exception e) {
Console.WriteLine(e);
Console.WriteLine(e.Message);
}
}
Console.WriteLine("Press enter to return to main menu");
Console.ReadLine();
PaintMenu();
}
internal static int Delete <T>(this IDbCommand dbCmd, T anonType, Action <IDbCommand> commandFilter = null)
{
return(dbCmd.Delete <T>((object)anonType, commandFilter));
}
public void Remove(T entity)
{
dbCmd.Delete <T>(entity);
}
/// <summary>
/// Delete the rows that matches the where expression, e.g:
///
/// dbCmd.Delete<Person>(ev => ev.Where(p => p.Age == 27));
/// DELETE FROM "Person" WHERE ("Age" = 27)
/// </summary>
public static int Delete <T>(this IDbCommand dbCmd, Func <SqlExpressionVisitor <T>, SqlExpressionVisitor <T> > where)
{
return(dbCmd.Delete(where (OrmLiteConfig.DialectProvider.ExpressionVisitor <T>())));
}
/// <summary>
/// Flexible Delete method to succinctly execute a delete statement using free-text where expression. E.g.
///
/// dbCmd.Delete<Person>(where:"Age = {0}".Params(27));
/// DELETE FROM "Person" WHERE Age = 27
/// </summary>
public static int Delete <T>(this IDbCommand dbCmd, string where = null)
{
return(dbCmd.Delete(typeof(T).GetModelDefinition().ModelName, where));
}
internal static int Delete <T>(this IDbCommand dbCmd, T anonType)
{
return(dbCmd.Delete <T>((object)anonType));
}
public static void Main(string[] args)
{
User us = new User();
/*
* object[] md = us.GetType().GetCustomAttributes(typeof(AliasAttribute),true);
* if(md!=null){
* foreach(var o in md){
*
* Console.WriteLine("Atributo {0}", (o as AliasAttribute).Name );
* }
* }
*
* Console.WriteLine(us.GetType().FirstAttribute<AliasAttribute>().Name);
*
* var objProperties = us.GetType().GetProperties(
* BindingFlags.Public | BindingFlags.Instance).ToList<PropertyInfo>();
*
* foreach(var pi in objProperties){
*
* Console.WriteLine( "Name : {0}, Alias {1}", pi.Name , pi.FirstAttribute<AliasAttribute>().Name);
* if(pi.Name=="Name")
* ReflectionUtils.SetProperty(us, pi, "My Name");
*
* }
*
*/
//var fieldInfos = us.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
// foreach (var fieldInfo in fieldInfos)
// {
// Console.WriteLine( "fielInfoName :{0} ", fieldInfo.Name);
// }
//Set one before use (i.e. in a static constructor).
Config.DialectProvider = new FirebirdDialectProvider();
Console.WriteLine(us.GetType().ToSelectStatement());
Console.WriteLine(us.ToInsertRowStatement());
//Console.WriteLine(us.ToInsertSentence() );
us.Name = "some name";
us.Password = "******";
us.Col1 = "ll";
us.Col2 = "xx";
us.Col2 = "ys";
Console.WriteLine(us.ToInsertRowStatement());
Console.WriteLine(us.ToUpdateRowStatement());
//using (FirebirdSql.Data.FirebirdClient.FbConnection db =
// new FirebirdSql.Data.FirebirdClient.FbConnection(
// "User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;"))
//{
// db.Open();
//using (FirebirdSql.Data.FirebirdClient.FbCommand dbConn = db.CreateCommand())
//{
using (IDbConnection db =
"User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
using (IDbCommand dbConn = db.CreateCommand())
{
try{
dbConn.Insert(new User
{
Name = string.Format("Hello, World! {0}", DateTime.Now),
Password = "******",
Col1 = "01",
Col2 = "02",
Col3 = "03"
});
User user = new User()
{
Name = "New User ",
Password = "******",
Col1 = "XX",
Col2 = "YY",
Col3 = "ZZ",
Active = true
};
dbConn.Insert(user);
Console.WriteLine("++++++++++Id for {0} {1}", user.Name, user.Id);
var rows = dbConn.Select <User>();
Console.WriteLine("++++++++++++++records in users {0}", rows.Count);
foreach (User u in rows)
{
Console.WriteLine("{0} -- {1} -- {2} -- {3} -{4} --{5} ", u.Id, u.Name, u.SomeStringProperty, u.SomeDateTimeProperty,
(u.SomeInt32NullableProperty.HasValue)?u.SomeDateTimeNullableProperty.Value.ToString(): "",
u.Active);
Console.WriteLine(u.ToDeleteRowStatement());
dbConn.Delete(u);
}
rows = dbConn.Select <User>();
Console.WriteLine("-------------records in users after delete {0}", rows.Count);
}
catch (Exception e) {
Console.WriteLine(e);
}
//Assert.That(rows, Has.Count(1));
//Assert.That(rows[0].Id, Is.EqualTo(1));
}
//}
}
