public void Transaction_rollsback_transactions_to_different_tables()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
db.CreateTable<ModelWithOnlyStringFields>(true);
db.DeleteAll<ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new ModelWithIdAndName(0));
db.Insert(ModelWithFieldsOfDifferentTypes.Create(3));
db.Insert(ModelWithOnlyStringFields.Create("id3"));
Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(2));
Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(1));
Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(1));
}
Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(1));
Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(0));
Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(0));
}
}
public void Can_Select_In_for_string_value()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
for (int i = 1; i <= n; i++)
{
ModelWithIdAndName m = new ModelWithIdAndName()
{
Name = "Name" + i.ToString()
};
db.Insert(m);
}
var selectInNames = new[] { "Name1", "Name2" };
var rows = db.Select <ModelWithIdAndName>("Name IN ({0})".Fmt(selectInNames.SqlInParams()),
new { values = selectInNames.SqlInValues() });
Assert.That(rows.Count, Is.EqualTo(selectInNames.Length));
rows = db.Select <ModelWithIdAndName>("Name IN (@p1, @p2)", new { p1 = "Name1", p2 = "Name2" });
Assert.That(rows.Count, Is.EqualTo(selectInNames.Length));
}
}
public void Transaction_commits_inserts_to_different_tables()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.CreateTable <ModelWithFieldsOfDifferentTypes>(true);
db.CreateTable <ModelWithOnlyStringFields>(true);
db.DeleteAll <ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new ModelWithIdAndName(0));
db.Insert(ModelWithFieldsOfDifferentTypes.Create(3));
db.Insert(ModelWithOnlyStringFields.Create("id3"));
Assert.That(db.Select <ModelWithIdAndName>(), Has.Count.EqualTo(2));
Assert.That(db.Select <ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(1));
Assert.That(db.Select <ModelWithOnlyStringFields>(), Has.Count.EqualTo(1));
dbTrans.Commit();
}
Assert.That(db.Select <ModelWithIdAndName>(), Has.Count.EqualTo(2));
Assert.That(db.Select <ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(1));
Assert.That(db.Select <ModelWithOnlyStringFields>(), Has.Count.EqualTo(1));
}
}
public void Can_preserve_ModelWithIdAndName_table()
{
using var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open();
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
db.Insert(new ModelWithIdAndName(0));
db.CreateTable <ModelWithIdAndName>(false);
var rows = db.Select <ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(2));
}
public void Can_SaveAll_and_select_from_ModelWithFieldsOfDifferentTypes_table_with_no_ids()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
db.DeleteAll<ModelWithFieldsOfDifferentTypes>();
var rowIds = new List<int> {1, 2, 3, 4, 5};
var newRows = rowIds.ConvertAll(x => ModelWithFieldsOfDifferentTypes.Create(default(int)));
db.SaveAll(newRows);
var rows = db.Select<ModelWithFieldsOfDifferentTypes>();
Assert.That(rows, Has.Count.EqualTo(newRows.Count));
}
}
public void Can_GetFirstColumnDistinct()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var ids = db.ColumnDistinct <int>("SELECT Id FROM ModelWIN");
Assert.That(ids.Count, Is.EqualTo(n));
}
}
public void Can_select_scalar_value()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var count = db.Scalar <int>("SELECT COUNT(*) FROM ModelWIN");
Assert.That(count, Is.EqualTo(n));
}
}
public void Can_GetDictionary()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var dictionary = db.Dictionary <int, string>("SELECT Id, Name FROM ModelWIN");
Assert.That(dictionary, Has.Count.EqualTo(5));
//Console.Write(dictionary.Dump());
}
}
public void Can_Save_table_with_null_fields()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
var row = ModelWithIdAndName.Create(0);
row.Name = null;
db.Save(row);
var rows = db.Select<ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(1));
ModelWithIdAndName.AssertIsEqual(rows[0], row);
}
}
public void Can_insert_table_with_null_fields()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
var row = ModelWithIdAndName.Create(0);
row.Name = null;
db.Insert(row);
var rows = db.Select <ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(1));
ModelWithIdAndName.AssertIsEqual(rows[0], row);
}
}
public void Can_SaveAll_and_select_from_ModelWithFieldsOfDifferentTypes_table_with_no_ids()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithFieldsOfDifferentTypes>(true);
db.DeleteAll <ModelWithFieldsOfDifferentTypes>();
var rowIds = new List <int> {
1, 2, 3, 4, 5
};
var newRows = rowIds.ConvertAll(x => ModelWithFieldsOfDifferentTypes.Create(default(int)));
db.SaveAll(newRows);
var rows = db.Select <ModelWithFieldsOfDifferentTypes>();
Assert.That(rows, Has.Count.EqualTo(newRows.Count));
}
}
public void Transaction_rollsback_if_not_committed()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new ModelWithIdAndName(0));
db.Insert(new ModelWithIdAndName(0));
var rowsInTrans = db.Select<ModelWithIdAndName>();
Assert.That(rowsInTrans, Has.Count.EqualTo(3));
}
var rows = db.Select<ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(1));
}
}
public void Transaction_rollsback_if_not_committed()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new ModelWithIdAndName(0));
db.Insert(new ModelWithIdAndName(0));
var rowsInTrans = db.Select <ModelWithIdAndName>();
Assert.That(rowsInTrans, Has.Count.EqualTo(3));
}
var rows = db.Select <ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(1));
}
}
public void Can_GetLookup()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithIdAndName>(true);
db.DeleteAll <ModelWithIdAndName>();
n.Times(x => {
var row = ModelWithIdAndName.Create(0);
row.Name = x % 2 == 0 ? "OddGroup" : "EvenGroup";
db.Insert(row);
});
var lookup = db.Lookup <string, int>("SELECT Name, Id FROM ModelWIN");
Assert.That(lookup, Has.Count.EqualTo(2));
Assert.That(lookup["OddGroup"], Has.Count.EqualTo(3));
Assert.That(lookup["EvenGroup"], Has.Count.EqualTo(2));
}
}
public void Can_get_scalar_value(){
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", FloatProperty=10.25f, DoubleProperty=3.23 });
authors.Add(new Author() { Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 50.0m, Comments = "CSharp books", Rate = 5, City = "Bogota",FloatProperty=7.59f,DoubleProperty=4.23 });
authors.Add(new Author() { Name = "Adam Witco", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 80.0m, Comments = "Math Books", Rate = 9, City = "London",FloatProperty=15.5f,DoubleProperty=5.42 });
authors.Add(new Author() { Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(-23), Active = true, Earnings = 60.0m, Comments = "Cooking books", Rate = 10, City = "Bogota",FloatProperty=0.57f, DoubleProperty=8.76});
authors.Add(new Author() { Name = "Libardo Pajaro", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 80.0m, Comments = "CSharp books", Rate = 9, City = "Bogota", FloatProperty=8.43f, DoubleProperty=7.35});
authors.Add(new Author() { Name = "Jorge Garzon", Birthday = DateTime.Today.AddYears(-28), Active = true, Earnings = 70.0m, Comments = "CSharp books", Rate = 9, City = "Bogota", FloatProperty=1.25f, DoubleProperty=0.3652});
authors.Add(new Author() { Name = "Alejandro Isaza", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 70.0m, Comments = "Java books", Rate = 0, City = "Bogota", FloatProperty=1.5f, DoubleProperty=100.563});
authors.Add(new Author() { Name = "Wilmer Agamez", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 30.0m, Comments = "Java books", Rate = 0, City = "Cartagena", FloatProperty=3.5f,DoubleProperty=7.23 });
authors.Add(new Author() { Name = "Rodger Contreras", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 90.0m, Comments = "CSharp books", Rate = 8, City = "Cartagena", FloatProperty=0.25f,DoubleProperty=9.23 });
authors.Add(new Author() { Name = "Chuck Benedict", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "CSharp books", Rate = 8, City = "London", FloatProperty=9.95f,DoubleProperty=4.91 });
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",FloatProperty=4.44f,DoubleProperty=6.41 });
authors.Add(new Author() { Name = "Ethan Brown", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 45.0m, Comments = "CSharp books", Rate = 5, City = "Madrid", FloatProperty=6.67f, DoubleProperty=8.05 });
authors.Add(new Author() { Name = "Xavi Garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 75.0m, Comments = "CSharp books", Rate = 9, City = "Madrid", FloatProperty=1.25f, DoubleProperty=3.99});
authors.Add(new Author() { Name = "Luis garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.0m, Comments = "CSharp books", Rate = 10,
City = "Mexico",
LastActivity= DateTime.Today,
NRate=5,
FloatProperty=1.25f,
NFloatProperty=3.15f,
DoubleProperty= 1.25,
NDoubleProperty= 8.25
});
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<Author>(true);
db.DeleteAll<Author>();
db.InsertAll(authors);
var expectedDate = authors.Max(e=>e.Birthday);
var r1 = db.GetScalar<Author, DateTime>( e => Sql.Max(e.Birthday) );
Assert.That(expectedDate, Is.EqualTo(r1));
expectedDate = authors.Where(e=>e.City=="London").Max(e=>e.Birthday);
r1 = db.GetScalar<Author, DateTime>( e => Sql.Max(e.Birthday), e=>e.City=="London" );
Assert.That(expectedDate, Is.EqualTo(r1));
r1 = db.GetScalar<Author, DateTime>( e => Sql.Max(e.Birthday), e=>e.City=="SinCity" );
Assert.That( default(DateTime), Is.EqualTo(r1));
var expectedNullableDate= authors.Max(e=>e.LastActivity);
DateTime? r2 = db.GetScalar<Author,DateTime?>(e=> Sql.Max(e.LastActivity));
Assert.That(expectedNullableDate, Is.EqualTo(r2));
expectedNullableDate= authors.Where(e=> e.City=="Bogota").Max(e=>e.LastActivity);
r2 = db.GetScalar<Author,DateTime?>(
e=> Sql.Max(e.LastActivity),
e=> e.City=="Bogota" );
Assert.That(expectedNullableDate, Is.EqualTo(r2));
r2 = db.GetScalar<Author, DateTime?>( e => Sql.Max(e.LastActivity), e=>e.City=="SinCity" );
Assert.That( default(DateTime?), Is.EqualTo(r2));
var expectedDecimal= authors.Max(e=>e.Earnings);
decimal r3 = db.GetScalar<Author,decimal>(e=> Sql.Max(e.Earnings));
Assert.That(expectedDecimal, Is.EqualTo(r3));
expectedDecimal= authors.Where(e=>e.City=="London").Max(e=>e.Earnings);
r3 = db.GetScalar<Author,decimal>(e=> Sql.Max(e.Earnings), e=>e.City=="London");
Assert.That(expectedDecimal, Is.EqualTo(r3));
r3 = db.GetScalar<Author,decimal>(e=> Sql.Max(e.Earnings), e=>e.City=="SinCity");
Assert.That( default(decimal), Is.EqualTo(r3));
var expectedNullableDecimal= authors.Max(e=>e.NEarnings);
decimal? r4 = db.GetScalar<Author,decimal?>(e=> Sql.Max(e.NEarnings));
Assert.That(expectedNullableDecimal, Is.EqualTo(r4));
expectedNullableDecimal= authors.Where(e=>e.City=="London").Max(e=>e.NEarnings);
r4 = db.GetScalar<Author,decimal?>(e=> Sql.Max(e.NEarnings), e=>e.City=="London");
Assert.That(expectedNullableDecimal, Is.EqualTo(r4));
r4 = db.GetScalar<Author,decimal?>(e=> Sql.Max(e.NEarnings), e=>e.City=="SinCity");
Assert.That( default(decimal?), Is.EqualTo(r4));
var expectedDouble =authors.Max(e=>e.DoubleProperty);
double r5 = db.GetScalar<Author,double>(e=> Sql.Max(e.DoubleProperty));
Assert.That(expectedDouble, Is.EqualTo(r5));
expectedDouble =authors.Where(e=>e.City=="London").Max(e=>e.DoubleProperty);
r5 = db.GetScalar<Author,double>(e=> Sql.Max(e.DoubleProperty), e=>e.City=="London");
Assert.That(expectedDouble, Is.EqualTo(r5));
r5 = db.GetScalar<Author,double>(e=> Sql.Max(e.DoubleProperty), e=>e.City=="SinCity");
Assert.That(default(double),Is.EqualTo(r5));
var expectedNullableDouble =authors.Max(e=>e.NDoubleProperty);
double? r6 = db.GetScalar<Author,double?>(e=> Sql.Max(e.NDoubleProperty));
Assert.That(expectedNullableDouble, Is.EqualTo(r6));
expectedNullableDouble =authors.Where(e=>e.City=="London").Max(e=>e.NDoubleProperty);
r6 = db.GetScalar<Author,double?>(e=> Sql.Max(e.NDoubleProperty), e=>e.City=="London");
Assert.That(expectedNullableDouble, Is.EqualTo(r6));
r6 = db.GetScalar<Author,double?>(e=> Sql.Max(e.NDoubleProperty), e=>e.City=="SinCity");
Assert.That(default(double?),Is.EqualTo(r6));
var expectedFloat =authors.Max(e=>e.FloatProperty);
var r7 = db.GetScalar<Author,float>(e=> Sql.Max(e.FloatProperty));
Assert.That(expectedFloat, Is.EqualTo(r7));
expectedFloat =authors.Where(e=>e.City=="London").Max(e=>e.FloatProperty);
r7 = db.GetScalar<Author,float>(e=> Sql.Max(e.FloatProperty), e=>e.City=="London");
Assert.That(expectedFloat, Is.EqualTo(r7));
r7 = db.GetScalar<Author,float>(e=> Sql.Max(e.FloatProperty), e=>e.City=="SinCity");
Assert.That(default(float),Is.EqualTo(r7));
var expectedNullableFloat =authors.Max(e=>e.NFloatProperty);
var r8 = db.GetScalar<Author,float?>(e=> Sql.Max(e.NFloatProperty));
Assert.That(expectedNullableFloat, Is.EqualTo(r8));
expectedNullableFloat =authors.Where(e=>e.City=="London").Max(e=>e.NFloatProperty);
r8 = db.GetScalar<Author,float?>(e=> Sql.Max(e.NFloatProperty), e=>e.City=="London");
Assert.That(expectedNullableFloat, Is.EqualTo(r8));
r8 = db.GetScalar<Author,float?>(e=> Sql.Max(e.NFloatProperty), e=>e.City=="SinCity");
Assert.That(default(float?),Is.EqualTo(r8));
var expectedString=authors.Min(e=>e.Name);
var r9 = db.GetScalar<Author,string>(e=> Sql.Min(e.Name));
Assert.That(expectedString, Is.EqualTo(r9));
expectedString=authors.Where(e=>e.City=="London").Min(e=>e.Name);
r9 = db.GetScalar<Author,string>(e=> Sql.Min(e.Name), e=>e.City=="London");
Assert.That(expectedString, Is.EqualTo(r9));
r9 = db.GetScalar<Author,string>(e=> Sql.Max(e.Name), e=>e.City=="SinCity");
Assert.IsNullOrEmpty(r9);
var expectedBool=authors.Min(e=>e.Active);
var r10 = db.GetScalar<Author,bool>(e=> Sql.Min(e.Active));
Assert.That(expectedBool, Is.EqualTo(r10));
expectedBool=authors.Max(e=>e.Active);
r10 = db.GetScalar<Author,bool>(e=> Sql.Max(e.Active));
Assert.That(expectedBool, Is.EqualTo(r10));
r10 = db.GetScalar<Author,bool>(e=> Sql.Max(e.Active), e=>e.City=="SinCity");
Assert.IsFalse(r10);
var expectedShort =authors.Max(e=>e.Rate);
var r11 = db.GetScalar<Author,short>(e=> Sql.Max(e.Rate));
Assert.That(expectedShort, Is.EqualTo(r11));
expectedShort =authors.Where(e=>e.City=="London").Max(e=>e.Rate);
r11 = db.GetScalar<Author,short>(e=> Sql.Max(e.Rate), e=>e.City=="London");
Assert.That(expectedShort, Is.EqualTo(r11));
r11 = db.GetScalar<Author,short>(e=> Sql.Max(e.Rate), e=>e.City=="SinCity");
Assert.That(default(short),Is.EqualTo(r7));
var expectedNullableShort =authors.Max(e=>e.NRate);
var r12 = db.GetScalar<Author,short?>(e=> Sql.Max(e.NRate));
Assert.That(expectedNullableShort, Is.EqualTo(r12));
expectedNullableShort =authors.Where(e=>e.City=="London").Max(e=>e.NRate);
r12 = db.GetScalar<Author,short?>(e=> Sql.Max(e.NRate), e=>e.City=="London");
Assert.That(expectedNullableShort, Is.EqualTo(r12));
r12 = db.GetScalar<Author,short?>(e=> Sql.Max(e.NRate), e=>e.City=="SinCity");
Assert.That(default(short?),Is.EqualTo(r12));
}
}
public void Can_get_scalar_value()
{
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", FloatProperty = 10.25f, DoubleProperty = 3.23
});
authors.Add(new Author()
{
Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 50.0m, Comments = "CSharp books", Rate = 5, City = "Bogota", FloatProperty = 7.59f, DoubleProperty = 4.23
});
authors.Add(new Author()
{
Name = "Adam Witco", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 80.0m, Comments = "Math Books", Rate = 9, City = "London", FloatProperty = 15.5f, DoubleProperty = 5.42
});
authors.Add(new Author()
{
Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(-23), Active = true, Earnings = 60.0m, Comments = "Cooking books", Rate = 10, City = "Bogota", FloatProperty = 0.57f, DoubleProperty = 8.76
});
authors.Add(new Author()
{
Name = "Libardo Pajaro", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 80.0m, Comments = "CSharp books", Rate = 9, City = "Bogota", FloatProperty = 8.43f, DoubleProperty = 7.35
});
authors.Add(new Author()
{
Name = "Jorge Garzon", Birthday = DateTime.Today.AddYears(-28), Active = true, Earnings = 70.0m, Comments = "CSharp books", Rate = 9, City = "Bogota", FloatProperty = 1.25f, DoubleProperty = 0.3652
});
authors.Add(new Author()
{
Name = "Alejandro Isaza", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 70.0m, Comments = "Java books", Rate = 0, City = "Bogota", FloatProperty = 1.5f, DoubleProperty = 100.563
});
authors.Add(new Author()
{
Name = "Wilmer Agamez", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 30.0m, Comments = "Java books", Rate = 0, City = "Cartagena", FloatProperty = 3.5f, DoubleProperty = 7.23
});
authors.Add(new Author()
{
Name = "Rodger Contreras", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 90.0m, Comments = "CSharp books", Rate = 8, City = "Cartagena", FloatProperty = 0.25f, DoubleProperty = 9.23
});
authors.Add(new Author()
{
Name = "Chuck Benedict", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "CSharp books", Rate = 8, City = "London", FloatProperty = 9.95f, DoubleProperty = 4.91
});
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", FloatProperty = 4.44f, DoubleProperty = 6.41
});
authors.Add(new Author()
{
Name = "Ethan Brown", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 45.0m, Comments = "CSharp books", Rate = 5, City = "Madrid", FloatProperty = 6.67f, DoubleProperty = 8.05
});
authors.Add(new Author()
{
Name = "Xavi Garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 75.0m, Comments = "CSharp books", Rate = 9, City = "Madrid", FloatProperty = 1.25f, DoubleProperty = 3.99
});
authors.Add(new Author()
{
Name = "Luis garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.0m, Comments = "CSharp books", Rate = 10,
City = "Mexico",
LastActivity = DateTime.Today,
NRate = 5,
FloatProperty = 1.25f,
NFloatProperty = 3.15f,
DoubleProperty = 1.25,
NDoubleProperty = 8.25
});
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <Author>(true);
db.DeleteAll <Author>();
db.InsertAll(authors);
var expectedDate = authors.Max(e => e.Birthday);
var r1 = db.Scalar <Author, DateTime>(e => Sql.Max(e.Birthday));
Assert.That(expectedDate, Is.EqualTo(r1));
expectedDate = authors.Where(e => e.City == "London").Max(e => e.Birthday);
r1 = db.Scalar <Author, DateTime>(e => Sql.Max(e.Birthday), e => e.City == "London");
Assert.That(expectedDate, Is.EqualTo(r1));
r1 = db.Scalar <Author, DateTime>(e => Sql.Max(e.Birthday), e => e.City == "SinCity");
Assert.That(default(DateTime), Is.EqualTo(r1));
var expectedNullableDate = authors.Max(e => e.LastActivity);
DateTime?r2 = db.Scalar <Author, DateTime?>(e => Sql.Max(e.LastActivity));
Assert.That(expectedNullableDate, Is.EqualTo(r2));
expectedNullableDate = authors.Where(e => e.City == "Bogota").Max(e => e.LastActivity);
r2 = db.Scalar <Author, DateTime?>(
e => Sql.Max(e.LastActivity),
e => e.City == "Bogota");
Assert.That(expectedNullableDate, Is.EqualTo(r2));
r2 = db.Scalar <Author, DateTime?>(e => Sql.Max(e.LastActivity), e => e.City == "SinCity");
Assert.That(default(DateTime?), Is.EqualTo(r2));
var expectedDecimal = authors.Max(e => e.Earnings);
decimal r3 = db.Scalar <Author, decimal>(e => Sql.Max(e.Earnings));
Assert.That(expectedDecimal, Is.EqualTo(r3));
expectedDecimal = authors.Where(e => e.City == "London").Max(e => e.Earnings);
r3 = db.Scalar <Author, decimal>(e => Sql.Max(e.Earnings), e => e.City == "London");
Assert.That(expectedDecimal, Is.EqualTo(r3));
r3 = db.Scalar <Author, decimal>(e => Sql.Max(e.Earnings), e => e.City == "SinCity");
Assert.That(default(decimal), Is.EqualTo(r3));
var expectedNullableDecimal = authors.Max(e => e.NEarnings);
decimal?r4 = db.Scalar <Author, decimal?>(e => Sql.Max(e.NEarnings));
Assert.That(expectedNullableDecimal, Is.EqualTo(r4));
expectedNullableDecimal = authors.Where(e => e.City == "London").Max(e => e.NEarnings);
r4 = db.Scalar <Author, decimal?>(e => Sql.Max(e.NEarnings), e => e.City == "London");
Assert.That(expectedNullableDecimal, Is.EqualTo(r4));
r4 = db.Scalar <Author, decimal?>(e => Sql.Max(e.NEarnings), e => e.City == "SinCity");
Assert.That(default(decimal?), Is.EqualTo(r4));
var expectedDouble = authors.Max(e => e.DoubleProperty);
double r5 = db.Scalar <Author, double>(e => Sql.Max(e.DoubleProperty));
Assert.That(expectedDouble, Is.EqualTo(r5));
expectedDouble = authors.Where(e => e.City == "London").Max(e => e.DoubleProperty);
r5 = db.Scalar <Author, double>(e => Sql.Max(e.DoubleProperty), e => e.City == "London");
Assert.That(expectedDouble, Is.EqualTo(r5));
r5 = db.Scalar <Author, double>(e => Sql.Max(e.DoubleProperty), e => e.City == "SinCity");
Assert.That(default(double), Is.EqualTo(r5));
var expectedNullableDouble = authors.Max(e => e.NDoubleProperty);
double?r6 = db.Scalar <Author, double?>(e => Sql.Max(e.NDoubleProperty));
Assert.That(expectedNullableDouble, Is.EqualTo(r6));
expectedNullableDouble = authors.Where(e => e.City == "London").Max(e => e.NDoubleProperty);
r6 = db.Scalar <Author, double?>(e => Sql.Max(e.NDoubleProperty), e => e.City == "London");
Assert.That(expectedNullableDouble, Is.EqualTo(r6));
r6 = db.Scalar <Author, double?>(e => Sql.Max(e.NDoubleProperty), e => e.City == "SinCity");
Assert.That(default(double?), Is.EqualTo(r6));
var expectedFloat = authors.Max(e => e.FloatProperty);
var r7 = db.Scalar <Author, float>(e => Sql.Max(e.FloatProperty));
Assert.That(expectedFloat, Is.EqualTo(r7));
expectedFloat = authors.Where(e => e.City == "London").Max(e => e.FloatProperty);
r7 = db.Scalar <Author, float>(e => Sql.Max(e.FloatProperty), e => e.City == "London");
Assert.That(expectedFloat, Is.EqualTo(r7));
r7 = db.Scalar <Author, float>(e => Sql.Max(e.FloatProperty), e => e.City == "SinCity");
Assert.That(default(float), Is.EqualTo(r7));
var expectedNullableFloat = authors.Max(e => e.NFloatProperty);
var r8 = db.Scalar <Author, float?>(e => Sql.Max(e.NFloatProperty));
Assert.That(expectedNullableFloat, Is.EqualTo(r8));
expectedNullableFloat = authors.Where(e => e.City == "London").Max(e => e.NFloatProperty);
r8 = db.Scalar <Author, float?>(e => Sql.Max(e.NFloatProperty), e => e.City == "London");
Assert.That(expectedNullableFloat, Is.EqualTo(r8));
r8 = db.Scalar <Author, float?>(e => Sql.Max(e.NFloatProperty), e => e.City == "SinCity");
Assert.That(default(float?), Is.EqualTo(r8));
var expectedString = authors.Min(e => e.Name);
var r9 = db.Scalar <Author, string>(e => Sql.Min(e.Name));
Assert.That(expectedString, Is.EqualTo(r9));
expectedString = authors.Where(e => e.City == "London").Min(e => e.Name);
r9 = db.Scalar <Author, string>(e => Sql.Min(e.Name), e => e.City == "London");
Assert.That(expectedString, Is.EqualTo(r9));
r9 = db.Scalar <Author, string>(e => Sql.Max(e.Name), e => e.City == "SinCity");
Assert.That(r9, Is.Null.Or.Empty);
var expectedBool = authors.Min(e => e.Active);
var r10 = db.Scalar <Author, bool>(e => Sql.Min(e.Active));
Assert.That(expectedBool, Is.EqualTo(r10));
expectedBool = authors.Max(e => e.Active);
r10 = db.Scalar <Author, bool>(e => Sql.Max(e.Active));
Assert.That(expectedBool, Is.EqualTo(r10));
r10 = db.Scalar <Author, bool>(e => Sql.Max(e.Active), e => e.City == "SinCity");
Assert.IsFalse(r10);
var expectedShort = authors.Max(e => e.Rate);
var r11 = db.Scalar <Author, short>(e => Sql.Max(e.Rate));
Assert.That(expectedShort, Is.EqualTo(r11));
expectedShort = authors.Where(e => e.City == "London").Max(e => e.Rate);
r11 = db.Scalar <Author, short>(e => Sql.Max(e.Rate), e => e.City == "London");
Assert.That(expectedShort, Is.EqualTo(r11));
r11 = db.Scalar <Author, short>(e => Sql.Max(e.Rate), e => e.City == "SinCity");
Assert.That(default(short), Is.EqualTo(r7));
var expectedNullableShort = authors.Max(e => e.NRate);
var r12 = db.Scalar <Author, short?>(e => Sql.Max(e.NRate));
Assert.That(expectedNullableShort, Is.EqualTo(r12));
expectedNullableShort = authors.Where(e => e.City == "London").Max(e => e.NRate);
r12 = db.Scalar <Author, short?>(e => Sql.Max(e.NRate), e => e.City == "London");
Assert.That(expectedNullableShort, Is.EqualTo(r12));
r12 = db.Scalar <Author, short?>(e => Sql.Max(e.NRate), e => e.City == "SinCity");
Assert.That(default(short?), Is.EqualTo(r12));
}
}
public void Can_preserve_ModelWithIdAndName_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
db.Insert(new ModelWithIdAndName(0));
db.CreateTable<ModelWithIdAndName>(false);
var rows = db.Select<ModelWithIdAndName>();
Assert.That(rows, Has.Count.EqualTo(2));
}
}
public void Can_Select_In_for_string_value()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
for (int i = 1; i <= n; i++)
{
ModelWithIdAndName m = new ModelWithIdAndName()
{
Name = "Name" + i.ToString()
};
db.Insert(m);
}
var selectInNames = new[] {"Name1", "Name2"};
var rows = db.Select<ModelWithIdAndName>("Name IN ({0})", selectInNames.SqlInValues());
Assert.That(rows.Count, Is.EqualTo(selectInNames.Length));
}
}
public void Can_GetDictionary()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var dictionary = db.GetDictionary<int, string>("SELECT Id, Name FROM ModelWIN");
Assert.That(dictionary, Has.Count.EqualTo(5));
//Console.Write(dictionary.Dump());
}
}
public void Can_GetLookup()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
n.Times(x =>
{
var row = ModelWithIdAndName.Create(0);
row.Name = x%2 == 0 ? "OddGroup" : "EvenGroup";
db.Insert(row);
});
var lookup = db.GetLookup<string, int>("SELECT Name, Id FROM ModelWIN");
Assert.That(lookup, Has.Count.EqualTo(2));
Assert.That(lookup["OddGroup"], Has.Count.EqualTo(3));
Assert.That(lookup["EvenGroup"], Has.Count.EqualTo(2));
}
}
public void Can_GetFirstColumnDistinct()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var ids = db.GetFirstColumnDistinct<int>("SELECT Id FROM ModelWIN");
Assert.That(ids.Count, Is.EqualTo(n));
}
}
public void Shippers_UseCase()
{
using (var db = new OrmLiteConnectionFactory("User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;", FirebirdDialect.Provider).Open())
{
const bool overwrite = false;
db.DropTable <Shipper>();
db.DropTable <ShipperType>();
db.CreateTables(overwrite, typeof(ShipperType), typeof(Shipper)); // ShipperType must be created first!
int trainsTypeId, planesTypeId;
//Playing with transactions
using (IDbTransaction dbTrans = db.BeginTransaction())
{
db.Insert(new ShipperType {
Name = "Trains"
});
trainsTypeId = (int)db.LastInsertId();
db.Insert(new ShipperType {
Name = "Planes"
});
planesTypeId = (int)db.LastInsertId();
dbTrans.Commit();
}
using (IDbTransaction dbTrans = db.BeginTransaction(IsolationLevel.ReadCommitted))
{
db.Insert(new ShipperType {
Name = "Automobiles"
});
Assert.That(db.Select <ShipperType>(), Has.Count.EqualTo(3));
dbTrans.Rollback();
}
Assert.That(db.Select <ShipperType>(), Has.Count.EqualTo(2));
//Performing standard Insert's and Selects
db.Insert(new Shipper {
CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsTypeId
});
db.Insert(new Shipper {
CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesTypeId
});
db.Insert(new Shipper {
CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesTypeId
});
var trainsAreUs = db.Single <Shipper>("\"Type\" = @id", new { id = trainsTypeId });
Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
Assert.That(db.Select <Shipper>("CompanyName = @company OR Phone = @phone",
new { company = "Trains R Us", phone = "555-UNICORNS" }), Has.Count.EqualTo(2));
Assert.That(db.Select <Shipper>("\"Type\" = @id", new { id = planesTypeId }), Has.Count.EqualTo(2));
//Lets update a record
trainsAreUs.Phone = "666-TRAINS";
db.Update(trainsAreUs);
Assert.That(db.SingleById <Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS"));
//Then make it dissappear
db.Delete(trainsAreUs);
Assert.That(db.SingleById <Shipper>(trainsAreUs.Id), Is.Null);
//And bring it back again
db.Insert(trainsAreUs);
//Performing custom queries
//Select only a subset from the table
var partialColumns = db.Select <SubsetOfShipper>(typeof(Shipper), "\"Type\" = @id", new { id = planesTypeId });
Assert.That(partialColumns, Has.Count.EqualTo(2));
//Select into another POCO class that matches sql
var rows = db.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:
db.DeleteAll <Shipper>();
db.DeleteAll <ShipperType>();
Assert.That(db.Select <Shipper>(), Has.Count.EqualTo(0));
Assert.That(db.Select <ShipperType>(), Has.Count.EqualTo(0));
}
}
public void Can_select_scalar_value()
{
const int n = 5;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.DeleteAll<ModelWithIdAndName>();
n.Times(x => db.Insert(ModelWithIdAndName.Create(0)));
var count = db.GetScalar<int>("SELECT COUNT(*) FROM ModelWIN");
Assert.That(count, Is.EqualTo(n));
}
}
