public void TestFixtureSetUp()
{
LogManager.LogFactory = new ConsoleLogFactory();
OrmLiteConfig.DialectProvider = OracleOrmLiteDialectProvider.Instance;
OrmLiteConfig.ClearCache();
ConnectionString = GetFileConnectionString();
}
public void TestFixtureSetUp()
{
LogManager.LogFactory = new ConsoleLogFactory();
OrmLiteConfig.DialectProvider = OracleOrmLiteDialectProvider.Instance;
OrmLiteConfig.ClearCache();
ConnectionString = ConfigurationManager.ConnectionStrings["testDb"].ConnectionString;
}
public void TestFixtureSetUp()
{
LogManager.LogFactory = new ConsoleLogFactory();
OrmLiteConfig.DialectProvider = PostgreSQLDialectProvider.Instance;
OrmLiteConfig.DialectProvider.NamingStrategy = new OrmLiteNamingStrategyBase();
OrmLiteConfig.ClearCache();
ConnectionString = ConfigurationManager.ConnectionStrings["testDb"].ConnectionString;
}
internal static Task InsertOnlyAsync <T>(this IDbCommand dbCmd, T obj, SqlExpression <T> onlyFields, CancellationToken token)
{
if (OrmLiteConfig.InsertFilter != null)
{
OrmLiteConfig.InsertFilter(dbCmd, obj);
}
var sql = dbCmd.GetDialectProvider().ToInsertRowStatement(dbCmd, obj, onlyFields.InsertFields);
return(dbCmd.ExecuteSqlAsync(sql, token));
}
public static void InsertOnly <T>(this IDbCommand dbCmd, T obj, SqlExpression <T> onlyFields)
{
if (OrmLiteConfig.InsertFilter != null)
{
OrmLiteConfig.InsertFilter(dbCmd, obj);
}
var sql = dbCmd.GetDialectProvider().ToInsertRowStatement(dbCmd, obj, onlyFields.InsertFields);
dbCmd.ExecuteSql(sql);
}
protected string GetRefFieldSql(Type refType, FieldDefinition refField)
{
var sqlFilter = dialectProvider.GetQuotedColumnName(refField.FieldName) + "={0}";
var sql = dialectProvider.ToSelectStatement(refType, sqlFilter, pkValue);
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sql = OrmLiteConfig.LoadReferenceSelectFilter(refType, sql);
}
return(sql);
}
protected string GetRefListSql(ModelDefinition refModelDef, FieldDefinition refField)
{
var sqlRef = $"SELECT {dialectProvider.GetColumnNames(refModelDef)} " +
$"FROM {dialectProvider.GetQuotedTableName(refModelDef)} " +
$"WHERE {dialectProvider.GetQuotedColumnName(refField)} " +
$"IN ({subSql})";
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sqlRef = OrmLiteConfig.LoadReferenceSelectFilter(refModelDef.ModelType, sqlRef);
}
return(sqlRef);
}
public override object FromDbValue(Type fieldType, object value)
{
var strValue = value as string;
if (strValue != null)
{
if (OrmLiteConfig.StringFilter != null)
{
return(OrmLiteConfig.StringFilter(strValue));
}
}
return(value.ToString());
}
/// <summary>Initialises this object.</summary>
/// <param name="scenarioBase">The scenario base.</param>
public void Init(ScenarioBase scenarioBase)
{
var dbScenarioBase = scenarioBase as DatabaseScenarioBase;
if (dbScenarioBase == null)
{
return;
}
OrmLiteConfig.DialectProvider = this.DialectProvider;
OrmLiteConfig.ClearCache();
//PropertyInvoker.ConvertValueFn = OrmLiteConfig.DialectProvider.ConvertDbValue;
dbScenarioBase.ConnectionString = this.ConnectionString;
}
protected string GetRefSelfSql(Type refType, FieldDefinition refSelf, ModelDefinition refModelDef)
{
//Load Self Table.RefTableId PK
var refPkValue = refSelf.GetValue(instance);
if (refPkValue == null)
{
return(null);
}
var sqlFilter = dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey.FieldName) + "={0}";
var sql = dialectProvider.ToSelectStatement(refType, sqlFilter, refPkValue);
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sql = OrmLiteConfig.LoadReferenceSelectFilter(refType, sql);
}
return(sql);
}
protected string GetFieldReferenceSql(FieldDefinition fieldDef, FieldReference fieldRef)
{
var refModelDef = fieldRef.RefModelDef;
var useSubSql = $"SELECT {dialectProvider.GetQuotedColumnName(fieldRef.RefIdFieldDef)} FROM "
+ subSql.RightPart("FROM");
var pk = dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey);
var sqlRef = $"SELECT {pk}, {dialectProvider.GetQuotedColumnName(fieldRef.RefFieldDef)} " +
$"FROM {dialectProvider.GetQuotedTableName(refModelDef)} " +
$"WHERE {pk} " +
$"IN ({useSubSql})";
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sqlRef = OrmLiteConfig.LoadReferenceSelectFilter(refModelDef.ModelType, sqlRef);
}
return(sqlRef);
}
public void Does_log_SQL_Insert_for_Saves_with_Auto_Ids()
{
var sbLogFactory = new StringBuilderLogFactory();
OrmLiteConfig.ResetLogFactory(sbLogFactory);
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <PersonWithAutoId>();
db.Save(new PersonWithAutoId {
Id = 1, FirstName = "first", LastName = "last", Age = 27
});
}
var sql = sbLogFactory.GetLogs();
Assert.That(sql, Does.Contain("INSERT INTO"));
OrmLiteConfig.ResetLogFactory(null);
}
protected string GetRefSelfSql(ModelDefinition modelDef, FieldDefinition refSelf, ModelDefinition refModelDef)
{
//Load Self Table.RefTableId PK
var refQ = q.Clone();
refQ.Select(dialectProvider.GetQuotedColumnName(modelDef, refSelf));
refQ.OrderBy().ClearLimits(); //clear any ORDER BY or LIMIT's in Sub Select's
var subSqlRef = refQ.ToMergedParamsSelectStatement();
var sqlRef = $"SELECT {dialectProvider.GetColumnNames(refModelDef)} " +
$"FROM {dialectProvider.GetQuotedTableName(refModelDef)} " +
$"WHERE {dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey)} " +
$"IN ({subSqlRef})";
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sqlRef = OrmLiteConfig.LoadReferenceSelectFilter(refModelDef.ModelType, sqlRef);
}
return(sqlRef);
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="execFunc">
/// The exec func.
/// </param>
/// <param name="cmd">
/// The cmd.
/// </param>
/// <param name="dbTransaction">
/// The db transaction.
/// </param>
/// <typeparam name="T">
/// </typeparam>
/// <returns>
/// The <see cref="T"/>.
/// </returns>
public virtual T Execute <T>(Func <IDbCommand, T> execFunc, IDbCommand cmd = null, IDbTransaction dbTransaction = null)
{
var command = cmd ?? this.GetCommand(string.Empty, CommandType.Text);
OrmLiteConfig.ClearCache();
using (var p = this._profiler.Start(command.CommandText))
{
T result = default(T);
if (dbTransaction == null)
{
if (command.Connection != null && command.Connection.State == ConnectionState.Open)
{
result = execFunc(command);
}
else
{
using (var connection = this.CreateConnectionOpen())
{
// get an open connection
command.Connection = connection;
result = execFunc(command);
connection.Close();
}
}
}
else
{
command.Populate(dbTransaction);
result = execFunc(command);
}
return(result);
}
}
protected string GetFieldReferenceSql(FieldDefinition fieldDef, FieldReference fieldRef)
{
var refPkValue = fieldRef.RefIdFieldDef.GetValue(instance);
if (refPkValue == null)
{
return(null);
}
var refModelDef = fieldRef.RefModelDef;
var pk = dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey);
var sqlRef = dialectProvider.ToSelectStatement(fieldRef.RefModel,
$"SELECT {pk}, {dialectProvider.GetQuotedColumnName(fieldRef.RefFieldDef)} " +
$"FROM {dialectProvider.GetQuotedTableName(refModelDef)} " +
$"WHERE {pk}" + "={0}", refPkValue);
if (OrmLiteConfig.LoadReferenceSelectFilter != null)
{
sqlRef = OrmLiteConfig.LoadReferenceSelectFilter(fieldRef.RefModel, sqlRef);
}
return(sqlRef);
}
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();
}
protected void EstablishContext(int numberOfRandomObjects)
{
OrmLiteConfig.ClearCache();
EstablishContext(numberOfRandomObjects, null);
}
public void SetUp()
{
OrmLiteConfig.ClearCache();
LicenseUtils.RemoveLicense();
JsConfig.Reset();
}
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())
{
try
{
db.DropTable <Author>();
var tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(db, typeof(Author).Name);
Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.FalseString, tableExists.ToString(), !tableExists ? "OK" : "************** FAILED ***************");
db.CreateTable <Author>();
tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(db, typeof(Author).Name);
Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.TrueString, tableExists.ToString(), tableExists ? "OK" : "************** FAILED ***************");
db.DeleteAll <Author>();
Console.WriteLine("Inserting...");
DateTime t1 = DateTime.Now;
db.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().Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay);
Console.WriteLine(ev.ToSelectStatement());
List <Author> result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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 = db.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 = db.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().Where(rn => Sql.In(rn.City, "London", "Madrid", city)); //clean prev
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => Sql.In(rn.City, cities));
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => rn.Name.StartsWith("A"));
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => rn.Name.ToUpper().EndsWith(name));
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => rn.Name.EndsWith("garzon"));
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => rn.Name.Contains(name));
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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 = db.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().Where(rn => rn.Earnings <= earnings);
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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().Where(rn => rn.Rate == 10 && rn.City == city);
result = db.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************");
result = db.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 = db.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().Where(rn => rn.Rate == rate).Update(rn => rn.Active);
var rows = db.UpdateOnly(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 });
db.InsertOnly(new Author()
{
Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18)
}, ev);
db.InsertOnly(new Author()
{
Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19)
}, ev);
ev.Where().Where(rn => !rn.Active);
result = db.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().Where(rn => rn.City == null).Update(rn => rn.Comments);
rows = db.UpdateOnly(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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.FirstOrDefault(ev);
Console.WriteLine("FOD: Expected:{0} Selected {1} {2}", expectedResult,
r1.Birthday,
expectedResult == r1.Birthday ? "OK" : "************** FAILED ***************");
var r2 = db.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 = db.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 = db.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 = db.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 = db.Select(ev);
expectedStringResult = "Berlin";
Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult,
result[0].City,
expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************");
r1 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.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 = db.FirstOrDefault <Author>(rn => rn.Name == "Luis garzon");
rr.City = "Madrid";
rr.Comments = "Updated";
ev.Where().Where(r => r.Id == rr.Id); // if omit, then all records will be updated
rows = db.UpdateOnly(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().Where(r => r.City == "Ciudad Gotica");
rows = db.UpdateOnly(rr, ev);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************");
expected = db.Select <Author>(x => x.City == "Madrid").Count;
author = new Author()
{
Active = false
};
rows = db.UpdateOnly(author, x => x.Active, x => x.City == "Madrid");
Console.WriteLine("Expected:{0} Updated:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************");
expected = db.Select <Author>(x => x.Active == false).Count;
rows = db.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();
}
