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_example2()
{
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.CommandText = "PRAGMA synchronous = OFF; PRAGMA page_size = 4096; PRAGMA cache_size = 3000; PRAGMA journal_mode = OFF;";
dbCmd.ExecuteNonQuery();
dbCmd.CreateTable <User2>(false);
// we have to do a custom insert because the provider base ignores AutoInc columns
dbCmd.CommandText = "INSERT INTO Users VALUES(5000000000, -1)";
dbCmd.ExecuteNonQuery();
var obj1 = new User2 {
Value = 6000000000L
};
dbCmd.Insert(obj1);
var last = dbCmd.GetLastInsertId();
Assert.AreEqual(5000000001L, last);
var obj2 = dbCmd.QueryById <User2>(last);
Assert.AreEqual(obj1.Value, obj2.Value);
}
File.Delete(path);
}
public void SetUp()
{
CreateNewDatabase();
dbConn = ConnectionString.OpenDbConnection();
dbCmd = dbConn.CreateCommand();
dbCmd.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
}
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 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 SetUp()
{
dbConn = ConnectionString.OpenDbConnection();
dbCmd = dbConn.CreateCommand();
dbCmd.CreateTable<Person>(overwrite: true);
//People.ToList().ForEach(x => dbCmd.Insert(x));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <Order>(true);
}
dbCmd.Insert(NorthwindFactory.Order(this.Iteration, "VINET", 5, new DateTime(1996, 7, 4), new DateTime(1996, 1, 8), new DateTime(1996, 7, 16), 3, 32.38m, "Vins et alcools Chevalier", "59 rue de l'Abbaye", "Reims", null, "51100", "France"));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<SampleOrderLine>(true);
}
dbCmd.Insert(SampleOrderLine.Create(userId, this.Iteration, 1));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<Supplier>(true);
}
dbCmd.Insert(NorthwindFactory.Supplier(this.Iteration, "Exotic Liquids", "Charlotte Cooper", "Purchasing Manager", "49 Gilbert St.", "London", null, "EC1 4SD", "UK", "(171) 555-2222", null, null));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<Customer>(true);
}
dbCmd.Insert(NorthwindFactory.Customer(this.Iteration.ToString("x"), "Alfreds Futterkiste", "Maria Anders", "Sales Representative", "Obere Str. 57", "Berlin", null, "12209", "Germany", "030-0074321", "030-0076545", null));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<Order>(true);
}
dbCmd.Insert(NorthwindFactory.Order(this.Iteration, "VINET", 5, new DateTime(1996, 7, 4), new DateTime(1996, 1, 8), new DateTime(1996, 7, 16), 3, 32.38m, "Vins et alcools Chevalier", "59 rue de l'Abbaye", "Reims", null, "51100", "France"));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <SampleOrderLine>(true);
}
dbCmd.Insert(SampleOrderLine.Create(userId, this.Iteration, 1));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <Customer>(true);
}
dbCmd.Insert(NorthwindFactory.Customer(this.Iteration.ToString("x"), "Alfreds Futterkiste", "Maria Anders", "Sales Representative", "Obere Str. 57", "Berlin", null, "12209", "Germany", "030-0074321", "030-0076545", null));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <ModelWithFieldsOfDifferentTypes>(true);
}
dbCmd.Insert(ModelWithFieldsOfDifferentTypes.Create(this.Iteration));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
}
dbCmd.Insert(ModelWithFieldsOfDifferentTypes.Create(this.Iteration));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <Supplier>(true);
}
dbCmd.Insert(NorthwindFactory.Supplier(this.Iteration, "Exotic Liquids", "Charlotte Cooper", "Purchasing Manager", "49 Gilbert St.", "London", null, "EC1 4SD", "UK", "(171) 555-2222", null, null));
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<SampleOrderLine>(true);
20.Times(i => dbCmd.Insert(SampleOrderLine.Create(userId, i, 1)));
}
var rows = dbCmd.Select<SampleOrderLine>();
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <SampleOrderLine>(true);
20.Times(i => dbCmd.Insert(SampleOrderLine.Create(userId, i, 1)));
}
var rows = dbCmd.Select <SampleOrderLine>();
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable <ModelWithFieldsOfDifferentTypes>(true);
20.Times(i => dbCmd.Insert(ModelWithFieldsOfDifferentTypes.Create(i)));
}
var rows = dbCmd.Select <ModelWithFieldsOfDifferentTypes>();
}
protected override void Run(IDbCommand dbCmd)
{
if (this.IsFirstRun)
{
dbCmd.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
20.Times(i => dbCmd.Insert(ModelWithFieldsOfDifferentTypes.Create(i)));
}
var rows = dbCmd.Select<ModelWithFieldsOfDifferentTypes>();
}
protected void FillTestEntityTableWithTestData(IDbCommand dbCmd)
{
dbCmd.CreateTable <TestEntity>(true);
for (int i = 1; i < 1000; i++)
{
dbCmd.Insert(new TestEntity()
{
Foo = RandomString(16),
Bar = RandomString(16),
Baz = RandomDecimal(i)
});
}
}
public Database()
{
//Use in-memory Sqlite DB instead
var dbPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Calls.db");
var dbFactory = new OrmLiteConnectionFactory(dbPath, false, SqliteOrmLiteDialectProvider.Instance);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
IDbConnection dbConn = dbFactory.OpenDbConnection();
dbCmd = dbConn.CreateCommand();
////Re-Create all table schemas:
//dbCmd.DropTable<CallItem>();
dbCmd.CreateTable<CallItem>();
}
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 Can_Create_Tables_With_Schema_In_Sqlite()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
var tables =
dbCmd.GetFirstColumn <string>
(@"SELECT name FROM sqlite_master WHERE type='table';");
//sqlite dialect should just concatenate the schema and table name to create a unique table name
Assert.That(tables.Contains("Security_Users"));
}
}
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_Create_Tables_With_Schema_In_Sqlite()
{
OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
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);
var schema = new Schema()
{
Connection = db
};
var table = schema.GetTable("Security_Users".ToUpper());
//sqlite dialect should just concatenate the schema and table name to create a unique table name
Assert.That(!(table == null));
}
}
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 Can_Create_Tables_With_Schema_in_SqlServer()
{
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);
var tables = dbCmd.GetFirstColumn <string>
(@"SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables");
//sql server dialect should create the table in the schema
Assert.That(tables.Contains("[Security].[Users]"));
}
}
public static void Main(string[] args)
{
Console.WriteLine("Hello World!");
LogManager.LogFactory = new ConsoleLogFactory();
log = LogManager.GetLogger(typeof(MainClass));
log.Info("Configurado log");
OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
using (IDbConnection db =
"User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
using (IDbCommand cmd = db.CreateCommand())
{
cmd.CreateTable <BlobTable>();
cmd.CommandText = "INSERT INTO BLOBTABLE (Id, SomeBytes) VALUES (8, @bytes)";
var parameter = cmd.CreateParameter();
parameter.ParameterName = "bytes";
parameter.Value = new byte[] { 0, 1, 2, 3 };
cmd.Parameters.Add(parameter);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
//----
cmd.Insert(new BlobTable {
Id = 9, SomeBytes = new byte[] { 0, 1, 2, 3 }
});
List <BlobTable> bt = cmd.Select <BlobTable>("SELECT * FROM BLOBTABLE");
foreach (var r in bt)
{
Console.WriteLine(r);
}
}
Console.WriteLine("This is The End my friend!");
}
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"));
}
}
public static void Main(string[] args)
{
OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
using (IDbConnection db =
"User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
//try{
// due to firebirdslq features, we have to drop book first and then author
dbCmd.DropTable <Book>();
dbCmd.DropTable <Author>();
dbCmd.CreateTable <Author>();
dbCmd.CreateTable <Book>();
dbCmd.Insert(new Author()
{
Name = "Demis Bellot",
Birthday = DateTime.Today.AddYears(20),
Active = true,
Earnings = 99.9m,
Comments = "ServiceStack.Net ...",
City = "London",
Rate = 10
});
dbCmd.Insert(new Author()
{
Name = "Angel Colmenares",
Birthday = DateTime.Today.AddYears(30),
Active = true,
Earnings = 50.25m,
Comments = "OrmLite.Firebird",
City = "Bogota",
Rate = 9
});
dbCmd.Insert(new Author()
{
Name = "Adam Witco",
Birthday = DateTime.Today.AddYears(25),
Active = true,
Comments = "other books...",
City = "London",
Rate = 8
});
dbCmd.Insert(new Author()
{
Name = "Claudia Espinel",
Birthday = DateTime.Today.AddYears(28),
Active = false,
Comments = "other books...",
City = "Bogota",
Rate = 10
});
//-------------------------------------------------------------------
SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>();
ev.Insert(r => new { r.Id, r.Name, r.Birthday, r.Active, r.Rate }); // fields to insert
var author = new Author()
{
Name = "William",
Birthday = DateTime.Today.AddYears(250),
Active = false,
City = "London",
Rate = 0,
Comments = "this will not be inserted" // null in db
};
dbCmd.Insert(author, ev);
author.Comments = "this will be updated";
ev.Update(rn => rn.Comments).Where(r => r.Id == author.Id);
dbCmd.Update(author, ev);
// update comment for all authors from london...
author.Comments = "update from london";
ev.Where(rn => rn.City == "London");
dbCmd.Update(author, ev);
// select author from Bogota
ev.Where(rn => rn.City == "Bogota");
var authors = dbCmd.Select(ev);
Console.WriteLine(authors.Count);
// select author from Bogota and Active=true;
ev.Where(rn => rn.City == "Bogota" && rn.Active == true); // sorry for firebird must write ==true !
authors = dbCmd.Select(ev);
Console.WriteLine(authors.Count);
//-------------------------------------------------------------------
authors = dbCmd.Select <Author>();
Console.WriteLine("Rows in Author : '{0}'", authors.Count);
foreach (Author a in authors)
{
Console.WriteLine("Id :{0} - Name : {1} -- Earnings {2}", a.Id,
a.Name,
a.Earnings.HasValue? a.Earnings.Value: 0.0m);
}
author = authors.FirstOrDefault <Author>(r => r.Name == "Angel Colmenares");
if (author != default(Author))
{
dbCmd.Insert(new Book()
{
IdAuthor = author.Id,
Title = "The big book",
Price = 18.55m,
});
Console.WriteLine("{0} == {1}", dbCmd.HasChildren <Book>(author), true);
}
else
{
Console.WriteLine("Something wrong ");
}
author = authors.FirstOrDefault <Author>(r => r.Name == "Adam Witco");
if (author != default(Author))
{
Console.WriteLine("{0} == {1}", dbCmd.HasChildren <Book>(author), false);
}
else
{
Console.WriteLine("Something wrong ");
}
var books = dbCmd.Select <Book>();
foreach (var b in books)
{
Console.WriteLine("Title {0} Price {1}", b.Title, b.Price);
}
ev.Select(r => new { r.Name, r.Active }).Where(); // only Name and Active fields will be retrived
authors = dbCmd.Select(ev);
Console.WriteLine(ev.SelectExpression);
foreach (Author r in authors)
{
Console.WriteLine("'{0}' '{1}' '{2}'", r.Name, r.Active, r.Id);
}
dbCmd.DeleteAll <Book>();
dbCmd.DeleteAll <Author>();
//}
//catch(Exception e){
// Console.WriteLine("Error : " + e.Message);
// return;
//}
Console.WriteLine("This is The End my friend !");
}
}
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!");
}
public static void Test()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
var path = GetFileConnectionString();
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.CreateTable <UserData>(true);
dbCmd.CreateTable <UserService>(true);
dbCmd.Insert(new UserData {
Id = 5, UserDataValue = "Value-5"
});
dbCmd.Insert(new UserData {
Id = 6, UserDataValue = "Value-6"
});
dbCmd.Insert(new UserService {
Id = 8, ServiceName = "Value-8"
});
dbCmd.Insert(new UserService {
Id = 9, ServiceName = "Value-9"
});
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9
});
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");
var jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.Select <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql = jn.ToSql();
var items = db.Query <UserEx>(sql);
jn.Clear();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.OrderByDescending <UserService>(x => x.ServiceName)
.Where <User>(x => x.Id > 0)
.Or <User>(x => x.Id < 10)
.And <User>(x => x.Name != "" || x.Name != null);
var sql2 = jn.ToSql();
var item = db.QuerySingle <UserEx>(sql2);
}
File.Delete(path);
}
public void SetUp()
{
dbConn = ConnectionString.OpenDbConnection();
dbCmd = dbConn.CreateCommand();
dbCmd.CreateTable<ModelWithFieldsOfDifferentTypes>(overwrite: true);
}
public static void Main(string[] args)
{
Console.WriteLine("Hello World!");
OrmLiteConfig.DialectProvider = new OracleOrmLiteDialectProvider();
ServiceStack.OrmLite.SqlExpressionVisitor <Company> sql =
new OracleSqlExpressionVisitor <Company>();
List <Object> names = new List <Object>();
names.Add("SOME COMPANY");
names.Add("XYZ");
List <Object> ids = new List <Object>();
ids.Add(1);
ids.Add(2);
using (IDbConnection db =
"Data Source=x;User Id=x;Password=x;".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.DropTable <Company>();
dbCmd.CreateTable <Company>();
Company company = new Company()
{
Id = 1, Name = "XYZ"
};
Console.WriteLine(company.Id.In(ids));
Console.WriteLine(company.Name.In(names));
dbCmd.Insert <Company>(company);
sql.Where(cp => cp.Name == "On more Company");
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.Name != "On more Company");
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.Name == null);
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.Name != null);
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.SomeBoolean); // TODO : fix
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => !cp.SomeBoolean && 1 == 1); //TODO : fix
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.SomeBoolean && 1 == 1); //TODO : fix
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => 1 == 1); // TODO : fix ?
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => "1" == "1"); // TODO : fix ?
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => "1" == "0"); // TODO : fix ?
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => 1 != 1); //ok
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.SomeBoolean == true); //OK
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.SomeBoolean == false); //OK
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => !cp.SomeBoolean); // OK
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name == cp.Name));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name == "On more Company" || cp.Id > 30));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.CreatedDate == DateTime.Today));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.CreatedDate == DateTime.Today && (cp.Name == "On more Company" || cp.Id > 30)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToUpper() == "ONE MORE COMPANY"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToLower() == "ONE MORE COMPANY".ToLower()));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToLower().StartsWith("one")));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToUpper().EndsWith("COMPANY")));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToUpper().Contains("MORE")));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.Substring(0) == "ONE MORE COMPANY"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Name.ToUpper().Substring(0, 7) == "ONE MOR"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.CreatedDate >= new DateTime(2000, 1, 1)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Employees / 2 > 10.0));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (cp.Employees * 2 > 10.0 / 5));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => ((cp.Employees + 3) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => ((cp.Employees - 3) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => ((cp.Employees % 3) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Math.Round(cp.SomeDouble) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Math.Round(cp.SomeDouble, 3) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Math.Floor(cp.SomeDouble) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Math.Ceiling(cp.SomeDouble) > (10.0 + 5)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (string.Concat(cp.SomeDouble, "XYZ") == "SOME COMPANY XYZ"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (string.Concat(cp.SomeDouble, "X", "Y", "Z") == "SOME COMPANY XYZ"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (string.Concat(cp.Name, "X", "Y", "Z") == "SOME COMPANY XYZ"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (string.Concat(cp.SomeDouble.ToString(), "X", "Y", "Z") == "SOME COMPANY XYZ"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => ((cp.CreatedDate ?? DateTime.Today) == DateTime.Today));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => ((cp.Turnover ?? 0) > 15));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Math.Abs(cp.Turnover ?? 0) > 15));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Sql.In(cp.Name, names)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (Sql.In(cp.Id, ids)));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.OrderBy(cp => cp.Name);
Console.WriteLine("{0}", sql.OrderByExpression);
dbCmd.Select <Company>(sql);
sql.OrderBy(cp => new { cp.Name, cp.Id });
Console.WriteLine("{0}", sql.OrderByExpression);
dbCmd.Select <Company>(sql);
sql.OrderBy(cp => new { cp.Name, Id = cp.Id * -1 });
Console.WriteLine("{0}", sql.OrderByExpression);
dbCmd.Select <Company>(sql);
sql.OrderByDescending(cp => cp.Name);
Console.WriteLine("{0}", sql.OrderByExpression);
dbCmd.Select <Company>(sql);
sql.OrderBy(cp => new { cp.Name, X = cp.Id.Desc() });
Console.WriteLine("{0}", sql.OrderByExpression);
dbCmd.Select <Company>(sql);
sql.Limit(1, 5);
Console.WriteLine(sql.LimitExpression);
dbCmd.Select <Company>(sql);
sql.Limit(1);
Console.WriteLine(sql.LimitExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => (string.Concat(cp.Name, "_", cp.Employees) == "SOME COMPANY XYZ_2"));
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Where(cp => cp.Id != 1);
Console.WriteLine(sql.WhereExpression);
dbCmd.Select <Company>(sql);
sql.Select(cp => new { cp.Employees, cp.Name });
Console.WriteLine("To Select:'{0}' ", sql.SelectExpression);
dbCmd.Select <Company>(sql);
sql.Select(cp => new { cp.Employees, cp.Name, Some = (cp.Id * 4).As("SomeExpression") });
Console.WriteLine("To Select:'{0}' ", sql.SelectExpression);
dbCmd.Select <Company>(sql);
sql.Select(cp => new { cp.Employees, cp.Name, Some = cp.Turnover.Sum().As("SomeExpression") });
Console.WriteLine("To Select:'{0}' ", sql.SelectExpression);
dbCmd.Select <Company>(sql);
sql.Select(cp => new { cp.Employees, cp.Name, Some = DbMethods.Sum(cp.Turnover ?? 0).As("SomeExpression") });
Console.WriteLine("To Select:'{0}' ", sql.SelectExpression);
dbCmd.Select <Company>(sql);
sql.Update(cp => new { cp.Employees, cp.Name });
Console.WriteLine("To Update:'{0}' ", string.Join(",", sql.UpdateFields.ToArray()));
dbCmd.Select <Company>(sql);
sql.Insert(cp => new { cp.Id, cp.Employees, cp.Name });
Console.WriteLine("To Insert:'{0}' ", string.Join(",", sql.InsertFields.ToArray()));
dbCmd.Select <Company>(sql);
}
Console.WriteLine("This is The End my friend!");
}
public void Run()
{
//Setup SQL Server Connection Factory
var dbFactory = new OrmLiteConnectionFactory(
ConfigurationManager.ConnectionStrings["testDb"].ConnectionString,
MySqlDialectProvider.Instance);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
IDbConnection dbConn = dbFactory.OpenDbConnection();
IDbCommand dbCmd = dbConn.CreateCommand();
//Re-Create all table schemas:
dbCmd.DropTable <OrderDetail>();
dbCmd.DropTable <Order>();
dbCmd.DropTable <Customer>();
dbCmd.DropTable <Product>();
dbCmd.DropTable <Employee>();
dbCmd.CreateTable <Employee>();
dbCmd.CreateTable <Product>();
dbCmd.CreateTable <Customer>();
dbCmd.CreateTable <Order>();
dbCmd.CreateTable <OrderDetail>();
dbCmd.Insert(new Employee {
Id = 1, Name = "Employee 1"
});
dbCmd.Insert(new Employee {
Id = 2, Name = "Employee 2"
});
var product1 = new Product {
Id = 1, Name = "Product 1", UnitPrice = 10
};
var product2 = new Product {
Id = 2, Name = "Product 2", UnitPrice = 20
};
dbCmd.Save(product1, product2);
var customer = new Customer
{
FirstName = "Orm",
LastName = "Lite",
Email = "*****@*****.**",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address {
Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101"
} },
},
CreatedAt = DateTime.UtcNow,
};
dbCmd.Insert(customer);
var customerId = dbCmd.GetLastInsertId(); //Get Auto Inserted Id
customer = dbCmd.QuerySingle <Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (var trans = dbCmd.BeginTransaction(IsolationLevel.ReadCommitted))
{
var order = new Order
{
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address {
Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121"
},
};
dbCmd.Save(order); //Inserts 1st time
order.Id = (int)dbCmd.GetLastInsertId(); //Get Auto Inserted Id
var orderDetails = new[] {
new OrderDetail
{
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail
{
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
dbCmd.Insert(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
dbCmd.Save(order); //Updates 2nd Time
trans.Commit();
}
}
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();
}
protected void FillTestEntityTableWithTestData(IDbCommand dbCmd)
{
dbCmd.CreateTable<TestEntity>(true);
for (int i = 1; i < 1000; i++)
{
dbCmd.Insert(new TestEntity() {
Foo = RandomString(16),
Bar = RandomString(16),
Baz = RandomDecimal(i)
});
}
}
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();
}
public void Shippers_UseCase()
{
using (IDbConnection dbConn = ConfigurationManager.ConnectionStrings["testDb"].ConnectionString.OpenDbConnection())
using (IDbCommand dbCmd = dbConn.CreateCommand())
{
const bool overwrite = true;
dbCmd.CreateTable <ShipperType>(overwrite);
dbCmd.CreateTable <Shipper>(overwrite);
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>("ShipperTypeId = {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>("ShipperTypeId = {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), "ShipperTypeId = {0}", planesTypeId);
Assert.That(partialColumns, Has.Count.EqualTo(2));
//Select into another POCO class that matches sql
var rows = dbCmd.Select <ShipperTypeCount>(
"SELECT ShipperTypeId, COUNT(*) AS Total FROM Shippers GROUP BY ShipperTypeId 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));
}
}
