void IExpressionNodeVisitor <EntityType, BinaryExpression> .Visit(SqlExpressionVisitor <EntityType> visitor, BinaryExpression node)
{
if (node.Left.NodeType == ExpressionType.Constant && ((ConstantExpression)node.Left).Value == null)
{
visitor.Visit(node.Right);
visitor.AddFragment(" IS NOT NULL");
}
else if (node.Right.NodeType == ExpressionType.Constant && ((ConstantExpression)node.Right).Value == null)
{
visitor.Visit(node.Left);
visitor.AddFragment(" IS NOT NULL");
}
else
{
visitor.Visit(node.Left);
visitor.AddFragment(" <> ");
visitor.Visit(node.Right);
}
}
/// <summary>
/// 拼接修改的SqlQuery
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">要修改的内容</param>
/// <param name="condition">修改的条件</param>
/// <param name="tableName">表名</param>
/// <param name="ignoreFields">需要忽略的字段</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <returns>修改的SqlQuery</returns>
public static SqlQuery BuilderUpdateSqlQuery <T>(object data, Expression <Func <T, bool> > condition, string tableName, string[] ignoreFields = null, DatabaseType dbType = DatabaseType.MSSQLServer)
{
var updatePropertyInfos = GetUpdatePropertyList(data, ignoreFields);
var updateProperties = updatePropertyInfos.Select(p => p.Name);
var updateFields = string.Join(",", updateProperties.Select(p => p + " =" + GetSign(dbType) + p));
var whereInfo = SqlExpressionVisitor.GetSqlWhere(condition, paramertNamePrefix: "W_", dbType: dbType);
var whereFields = string.Empty;
if (whereInfo.Item1.IsNotNullAndNotEmptyWhiteSpace())
{
whereFields = $" WHERE {whereInfo.Item1}";
}
SqlQuery sqlQuery = new SqlQuery()
.AddObjectParam(data, ignoreFields: ignoreFields)
.AddObjectParam(whereInfo.Item2)
.ChangeCommandText(string.Format("UPDATE {0} SET {1}{2};", tableName, updateFields, whereFields));
return(sqlQuery);
}
/// <summary>
/// 拼接查询前几条的SqlQuery
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">查询条件</param>
/// <param name="tableName">表名</param>
/// <param name="order">排序字段</param>
/// <param name="topCount">查询数量</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <returns>查询的SqlQuery</returns>
public static SqlQuery BuilderQueryTopSqlQuery <T>(Expression <Func <T, bool> > condition, string tableName, string order, int topCount = 1, DatabaseType dbType = DatabaseType.MSSQLServer)
{
var whereInfo = SqlExpressionVisitor.GetSqlWhere(condition, dbType: dbType);
var whereFields = string.Empty;
if (whereInfo.Item1.IsNotNullAndNotEmptyWhiteSpace())
{
whereFields = $" WHERE {whereInfo.Item1}";
}
string sql = string.Empty;
if (dbType == DatabaseType.MySql)
{
sql = string.Format("SELECT * FROM {1}{2} {3} LIMIT 0,{0};", topCount.ToString(), tableName, whereFields, string.IsNullOrWhiteSpace(order) ? string.Empty : "ORDER BY " + order);
}
else
{
sql = string.Format("SELECT TOP {0} * FROM {1}{2} {3};", topCount.ToString(), tableName, whereFields, string.IsNullOrWhiteSpace(order) ? string.Empty : "ORDER BY " + order);
}
return(new SqlQuery(sql, whereInfo.Item2));
}
public virtual CommandDefinition ToCountStatement <T>(SqlExpressionVisitor <T> visitor)
{
var modelDef = typeof(T).GetModelDefinition();
var sql = new StringBuilder();
sql.AppendFormat("SELECT COUNT({0}) FROM {1}",
visitor.Fields.Count == 0 ? "*" : (visitor.IsDistinct ? "DISTINCT" : String.Empty) + visitor.Fields.Aggregate((x, y) => x + ", " + y),
GetQuotedTableName(modelDef));
if (!string.IsNullOrEmpty(visitor.WhereExpression))
{
sql.Append(visitor.WhereExpression);
}
if (!string.IsNullOrEmpty(visitor.LimitExpression))
{
sql.Append(" ");
sql.Append(visitor.LimitExpression);
}
return(new CommandDefinition(sql.ToString(), visitor.Parameters));
}
/// <summary>
/// 拼接查询前几条的SqlQuery
/// </summary>
/// <typeparam name="TModel">查询字段的类型</typeparam>
/// <typeparam name="T"></typeparam>
/// <param name="condition">查询条件</param>
/// <param name="tableName">表名</param>
/// <param name="order">排序字段</param>
/// <param name="topCount">查询数量</param>
/// <param name="ignoreFields">需要忽略的字段</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <returns></returns>
public static SqlQuery BuilderQueryTopSqlQuery <TModel, T>(Expression <Func <T, bool> > condition, string tableName, string order, int topCount = 1, string[] ignoreFields = null, DatabaseType dbType = DatabaseType.MSSQLServer)
{
var whereInfo = SqlExpressionVisitor.GetSqlWhere(condition, dbType: dbType);
var whereFields = string.Empty;
if (whereInfo.Item1.IsNotNullAndNotEmptyWhiteSpace())
{
whereFields = $" WHERE {whereInfo.Item1}";
}
var selectProperties = PropertyUtil.GetTypeProperties(typeof(TModel), ignoreFields);
string sql = string.Empty;
if (dbType == DatabaseType.MySql)
{
sql = string.Format("SELECT {1} FROM {2}{3} {4} LIMIT 0,{0};", topCount.ToString(), string.Join(",", selectProperties.Select(m => m.Name)), tableName, whereFields, string.IsNullOrWhiteSpace(order) ? string.Empty : "ORDER BY " + order);
}
else
{
sql = string.Format("SELECT TOP {0} {1} FROM {2}{3} {4};", topCount.ToString(), string.Join(",", selectProperties.Select(m => m.Name)), tableName, whereFields, string.IsNullOrWhiteSpace(order) ? string.Empty : "ORDER BY " + order);
}
return(new SqlQuery(sql, whereInfo.Item2));
}
public void CreateFromExpression_CanHandleAndAlsoNodes_WithNonConstantValues_AndBools()
{
// Arrange
var sagaId = NewId.NextGuid();
Expression <Func <SimpleSaga, bool> > filter = x => x.CorrelationId == sagaId && x.Completed;
// Act
var result = SqlExpressionVisitor.CreateFromExpression(filter);
// Assert
Assert.That(result.Count, Is.EqualTo(2));
var first = result.First();
Assert.That(first.Item1, Is.EqualTo(nameof(SimpleSaga.CorrelationId)));
Assert.That(first.Item2, Is.EqualTo(sagaId));
var last = result.Last();
Assert.That(last.Item1, Is.EqualTo(nameof(SimpleSaga.Completed)));
Assert.That(last.Item2, Is.True);
}
void IExpressionNodeVisitor <TEntity, MemberExpression> .Visit(SqlExpressionVisitor <TEntity> visitor, MemberExpression node)
{
if (node.Expression?.NodeType == ExpressionType.Parameter)
{
if (node.Expression.Type != typeof(TEntity))
{
throw new InvalidOperationException($"仅支持访问模型{typeof(TEntity).Name}参数的成员 {node}.");
}
var member = _descriptor.Members?.FirstOrDefault(_ => _.Member.Name == node.Member.Name /*通过名称匹配，避免接口调用 Member 不一致*/);
if (member == null)
{
throw new InvalidOperationException($"访问模型{typeof(TEntity).Name}参数未找到映射 {node}.");
}
visitor.AddName(member.Name);
}
else
{
visitor.Visit(EvaluateExternalMember(node));
}
}
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())
{
db.CreateTable <User>(true);
db.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8
});
db.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9
});
db.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
SqlExpressionVisitor <User> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <User>();
var count1 = db.Count <User>(x => x.Id == 1);
var count2 = db.Count <User>(ev.Where(x => x.Id == 2));
var count3 = db.Count <User>();
count1 = db.Count <User>(x => x.Id == 1);
count2 = db.Count <User>(ev.Where(x => x.Id == 2));
count3 = db.Count <User>();
}
File.Delete(path);
}
public virtual CommandDefinition ToSelectStatement <T>(SqlExpressionVisitor <T> visitor, CommandFlags flags)
{
var sql = new StringBuilder();
sql.Append(visitor.SelectExpression);
sql.Append(string.IsNullOrEmpty(visitor.WhereExpression)
? String.Empty
: "\n" + visitor.WhereExpression);
sql.Append(string.IsNullOrEmpty(visitor.GroupByExpression)
? String.Empty
: "\n" + visitor.GroupByExpression);
sql.Append(string.IsNullOrEmpty(visitor.HavingExpression)
? String.Empty
: "\n" + visitor.HavingExpression);
sql.Append(string.IsNullOrEmpty(visitor.OrderByExpression)
? String.Empty
: "\n" + visitor.OrderByExpression);
sql.Append(string.IsNullOrEmpty(visitor.LimitExpression)
? String.Empty
: "\n" + visitor.LimitExpression);
return(new CommandDefinition(sql.ToString(), visitor.Parameters, flags: flags));
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
SqlExpressionVisitor <T> expressionVisitor,
string selectExpression = "",
string whereExpression = "",
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool runCount = false)
{
return(await QueryPagination <T>(cnn,
expressionVisitor,
selectExpression,
whereExpression,
"",
"",
"",
"",
startPageNo,
endPageNo,
pageSize,
expressionVisitor.Parameters,
runCount));
}
public JsonResult GetDMHCByRole(string role)
{
List<DanhMuc_HanhChinh> data = new List<DanhMuc_HanhChinh>();
string ma_hc = CurrentUser.MaHC != null ? CurrentUser.MaHC : "";
List<ListModel> roles_lower = this.GetLowerRoles((RoleEnum)Enum.Parse(typeof(RoleEnum), CurrentUser.Roles[0]));
if (roles_lower.Select(x => x.Id).Contains(role))
{
JoinSqlBuilder<DanhMuc_HanhChinh, DanhMuc_HanhChinh> jn = new JoinSqlBuilder<DanhMuc_HanhChinh, DanhMuc_HanhChinh>();
jn.And<DanhMuc_HanhChinh>(x => ((RoleEnum)Enum.Parse(typeof(RoleEnum), CurrentUser.Roles[0]) == RoleEnum.Admin || x.MaHC.StartsWith(ma_hc)));
SqlExpressionVisitor<DanhMuc_HanhChinh> sql_exp = Db.CreateExpression<DanhMuc_HanhChinh>();
string st = jn.ToSql();
int idx = st.IndexOf("WHERE");
sql_exp.SelectExpression = st.Substring(0, idx);
sql_exp.WhereExpression = string.Format("{0} AND LEN([MaHC]) = {1}", st.Substring(idx), this.GetLenMaHCByRole((RoleEnum)Enum.Parse(typeof(RoleEnum), role)));
string sql = sql_exp.ToSelectStatement();
data = Db.Select<DanhMuc_HanhChinh>(sql);
}
return Json(new {
Data = data
});
}
/// <summary>
/// DC updates new value.
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public GXValuesUpdateResponse Post(GXValuesUpdateRequest request)
{
List <GXEventsItem> events = new List <GXEventsItem>();
bool partOfTable = request.Values[0] is GXAmiDataRow;
lock (Db)
{
if (request.Values != null && request.Values.Length != 0)
{
using (var trans = Db.OpenTransaction(IsolationLevel.ReadCommitted))
{
if (partOfTable)
{
uint maxRow = 0;
//Find last row number.
#if !SS4
SqlExpressionVisitor <GXAmiDataRow> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <GXAmiDataRow>();
#else
SqlExpression <GXAmiDataRow> ev = OrmLiteConfig.DialectProvider.SqlExpression <GXAmiDataRow>();
#endif
ev.Limit(1);
ev.Where(q => q.TableID == (request.Values[0] as GXAmiDataRow).TableID);
ev.OrderByDescending(q => q.RowIndex);
List <GXAmiDataRow> items = Db.Select <GXAmiDataRow>(ev);
if (items.Count == 1)
{
maxRow = items[0].RowIndex;
}
else
{
maxRow = 0;
}
foreach (GXAmiDataRow it in request.Values)
{
//Increase row count.
if (it.RowIndex == 0 && it.ColumnIndex == 0)
{
++maxRow;
}
it.TimeStamp = DateTime.Now;
if (it.RowIndex == 0)
{
it.RowIndex = maxRow;
Db.Insert(it);
#if !SS4
it.Id = (ulong)Db.GetLastInsertId();
#else
it.Id = (ulong)Db.LastInsertId();
#endif
}
else
{
Db.Update(it);
}
events.Add(new GXEventsItem(ActionTargets.TableValueChanged, Actions.Add, it));
}
}
else
{
foreach (GXAmiDataValue it in request.Values)
{
//Delete old value from latest value.
Db.Delete <GXAmiLatestValue>(q => q.Id == it.PropertyID);
GXAmiLatestValue v = new GXAmiLatestValue();
v.TimeStamp = DateTime.Now;
v.PropertyID = it.PropertyID;
ulong mask = 0xFFFF;
v.DeviceID = v.PropertyID & ~mask;
v.UIValue = it.UIValue;
GXAmiValueLog lv = new GXAmiValueLog();
lv.PropertyID = it.PropertyID;
lv.DeviceID = v.DeviceID;
lv.UIValue = v.UIValue;
lv.TimeStamp = v.TimeStamp;
Db.Insert(v);
Db.Insert(lv);
events.Add(new GXEventsItem(ActionTargets.ValueChanged, Actions.Add, v));
}
}
trans.Commit();
}
}
}
AppHost host = this.ResolveService <AppHost>();
host.SetEvents(Db, this.Request, 0, events);
return(new GXValuesUpdateResponse());
}
internal int Update <T>(T model, SqlExpressionVisitor <T> ev)
{
var cmd = DialectProvider.ToUpdateRowStatement(model, ev);
return(this.ExecuteScalar <int>(cmd));
}
static void VisitEndsWith(SqlExpressionVisitor <TEntity> visitor, MethodCallExpression call)
{
visitor.Visit(call.Object);
visitor.AddFragment(" LIKE '%' || ");
visitor.Visit(call.Arguments[0]);
}
/// <summary>An OrmConnection method that firsts.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The expression.</param>
/// <returns>A T.</returns>
public async Task <T> FirstAsync <T>(SqlExpressionVisitor <T> expression)
{
var r = await SelectAsync(expression.Limit(1));
return(r.First());
}
internal static List <T> Get <T>(this IDbCommand dbCmd, IRedisClient redisClient, string cacheKey, SqlExpressionVisitor <T> visitor)
where T : new()
{
return(redisClient.Get(cacheKey, () =>
{
return visitor == null? dbCmd.Select <T>():dbCmd.Select <T>(visitor);
},
TimeSpan.FromDays(DiasEnCache)));
}
static void VisitToLower(SqlExpressionVisitor <TEntity> visitor, MethodCallExpression call)
{
visitor.AddFragment("LOWER(");
visitor.Visit(call.Object);
visitor.AddFragment(")");
}
public static void Main(string[] args)
{
Console.WriteLine("----------DotNetCoreSample----------");
// var dataLogger = LogHelper.GetLogger(typeof(DataExtension));
// DataExtension.CommandLogAction = msg => dataLogger.Debug(msg);
var services = new ServiceCollection();
services.AddTransient <IFly, MonkeyKing>();
IConfiguration configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
var city = configuration.GetAppSetting("City");
var number = configuration.GetAppSetting <int>("Number");
Console.WriteLine($"City:{city}, Number:{number}");
services.AddSingleton(configuration);
services.AddSingleton <IEventStore, EventStoreInMemory>();
services.AddSingleton <IEventBus, EventBus>();
services.AddSingleton(DelegateEventHandler.FromAction <CounterEvent>(@event =>
LogHelper.GetLogger(typeof(DelegateEventHandler <CounterEvent>))
.Info($"Event Info: {@event.ToJson()}")
)
);
DependencyResolver.SetDependencyResolver(services);
//DependencyResolver.Current.ResolveRequiredService<IFly>()
// .Fly();
//DependencyInjectionTest.Test();
// EventTest.MainTest();
// SerilogTest.MainTest();
//var builder = new ContainerBuilder();
//builder.RegisterType<MonkeyKing>().As<IFly>();
//DependencyResolver.SetDependencyResolver((IServiceProvider)new AutofacDependencyResolver(builder.Build()));
//DependencyInjectionTest.Test();
//int a = 1;
//Console.WriteLine(JsonConvert.SerializeObject(a));// output 1
//var pagedListModel = new PagedListModel<int>()
//{
// PageNumber = 1,
// PageSize = 4,
// TotalCount = 10,
// Data = new[] { 1, 2, 3, 4 }
//};
//Console.WriteLine(pagedListModel.ToJson());
// log test
// LoggerTest.MainTest();
// Log4NetTest.MainTest();
ILoggerFactory loggerFactory = new LoggerFactory();
//loggerFactory.AddConsole();
//loggerFactory.AddDebug();
loggerFactory.AddDelegateLogger(
(category, logLevel, exception, msg) =>
{
Console.WriteLine($"{category}:[{logLevel}] {msg}\n{exception}");
}
);
var logger = new Logger <Program>(loggerFactory);
logger.LogInformation("Logging information from Microsoft.Extensions.Logging");
InvokeHelper.TryInvoke(DataExtensionTest.MainTest);
//TaskTest.TaskWhenAllTest().GetAwaiter().GetResult();
//Base64UrlEncodeTest.MainTest();
//var a = new { Name = "2123" };
//var name = a.GetProperty("Name").GetValueGetter().Invoke(a);
//Console.WriteLine(name);
//var structTest = new TestStruct() { Name = "1233" };
//var obj = (object)structTest;
//Console.WriteLine(structTest.GetProperty("Name").GetValueGetter<TestStruct>().Invoke(structTest));
//structTest.GetProperty("Name").GetValueSetter().Invoke(obj, "Name1");
//structTest = (TestStruct)obj;
//Console.WriteLine(structTest.Name);
//Expression<Func<TestEntity, bool>> exp = t => t.Id > 10 && t.Token == "123" && t.Token.Contains("12");
//var str = SqlExpressionParser.ParseExpression(exp);
//Console.WriteLine("sql: {0}", str);
//exp = t => true;
//str = SqlExpressionParser.ParseExpression(exp);
//Console.WriteLine("sql: {0}", str);
//RepositoryTest.MainTest();
//var securityToken = ApplicationHelper.ApplicationName + "_test_123";
//var code123 = TotpHelper.GenerateCode(securityToken);
//Console.WriteLine(code123);
//var result = TotpHelper.ValidateCode(securityToken, code123);
//Console.WriteLine($"validate result: {result}");
//var ttl = 2;
//while (ttl > 1)
//{
// ttl = TotpHelper.TTL(securityToken);
// Console.WriteLine($"Current ttl: {ttl}, newId: {ObjectIdGenerator.Instance.NewId()}");
// Thread.Sleep(1000);
//}
//result = TotpHelper.ValidateCode(securityToken, code123);
//Console.WriteLine($"validate result1: {result}");
//result = TotpHelper.ValidateCode(securityToken, code123, 60);
//Console.WriteLine($"validate result2: {result}");
//var code1234 = TotpHelper.GenerateCode(ApplicationHelper.ApplicationName + "test_1234");
//Console.WriteLine(code1234);
// InvokeHelper.TryInvoke(HttpRequesterTest.MainTest);
//var pagedListModel = new PagedListModel<int>()
//{
// PageNumber = 2, PageSize = 2, TotalCount = 6, Data = new int[] {1, 2},
//};
//var pagedListModel1 = new PagedListModel1<int>()
//{
// PageNumber = 2,
// PageSize = 2,
// TotalCount = 6,
// Data = new int[] { 1, 2 },
//};
//Console.WriteLine($"pagedListModel:{JsonConvert.SerializeObject(pagedListModel)}, pagedListModel1:{JsonConvert.SerializeObject(pagedListModel1)}");
//var posts = new[] { new { PostId = 1, PostTitle = "12333", }, new { PostId = 2, PostTitle = "12333", }, };
//var postTags = new[] { new { PostId = 1, Tag = "HHH" } };
//var result = posts.LeftJoin(postTags, p => p.PostId, pt => pt.PostId, (p, pt) => new { p.PostId, p.PostTitle, pt?.Tag }).ToArray();
//Console.WriteLine(result.ToJson());
// CronHelperTest.MainTest();
// DependencyInjectionTest.BuiltInIocTest();
Expression <Func <TestEntity, bool> > expression =
t => t.Id > 0 && t.CreatedTime < DateTime.Now && t.Token == null;
var visitor = new SqlExpressionVisitor(null);
visitor.Visit(expression);
Console.WriteLine(visitor.GetCondition());
Console.ReadLine();
}
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 !");
}
}
/// <summary>
/// An OrmConnection method that counts the given database connection.
/// </summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The ev.</param>
/// <returns>A long.</returns>
public long Count <T>(SqlExpressionVisitor <T> expression)
{
return(this.ExecuteScalar <long>(DialectProvider.ToCountStatement(expression)));
}
/// <summary>An IDbCommand extension method that counts.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="dbCmd"> The dbCmd to act on.</param>
/// <param name="expression">The expression.</param>
/// <returns>A long.</returns>
public static long Count <T>(this IDbCommand dbCmd, SqlExpressionVisitor <T> expression)
{
string sql = expression.ToCountStatement();
return(dbCmd.GetScalar <long>(sql));
}
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();
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
SqlExpressionVisitor <T> expressionVisitor,
string selectExpression = "",
string whereExpression = "",
string countExpression = "",
string groupByExpression = "",
string havingExpression = "",
string orderByExpression = "",
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool runCount = false)
{
if (expressionVisitor == null)
{
expressionVisitor = cnn.DialectProvider.ExpressionVisitor <T>();
}
if (string.IsNullOrEmpty(selectExpression))
{
selectExpression = expressionVisitor.SelectExpression;
}
if (string.IsNullOrEmpty(whereExpression))
{
whereExpression = expressionVisitor.WhereExpression;
}
if (string.IsNullOrEmpty(havingExpression))
{
havingExpression = expressionVisitor.HavingExpression;
}
if (string.IsNullOrEmpty(groupByExpression))
{
groupByExpression = expressionVisitor.GroupByExpression;
}
if (string.IsNullOrEmpty(orderByExpression))
{
orderByExpression = expressionVisitor.OrderByExpression;
}
ModelDefinition modelDef = ModelDefinition <T> .Definition;
string tableName = cnn.DialectProvider.GetQuotedTableName(modelDef);
string countQuery = countExpression;
if (string.IsNullOrEmpty(countQuery))
{
countQuery = "SELECT COUNT(*) FROM " + tableName;
}
var sql = selectExpression + " " + whereExpression + " " + " order by id desc";
var data = await cnn.QueryAsync <T>(sql, parameters);
// var data = (await cnn.SelectAsync<T>());
long countValue = 0;
countValue = await cnn.QueryFirstAsync <long>(countQuery);
return(new PaginationQuery <T>()
{
Data = data
,
TotalRecords = countValue
});
}
internal int UpdateAll <T>(object obj, SqlExpressionVisitor <T> ev)
{
var cmd = DialectProvider.ToUpdateAllRowStatement(obj, ev);
return(this.ExecuteScalar <int>(cmd));
}
void IExpressionNodeVisitor <TEntity, UnaryExpression> .Visit(SqlExpressionVisitor <TEntity> visitor, UnaryExpression node)
{
visitor.Visit(node.Operand);
visitor.AddFragment(" <> 0");
}
/// <param name="where"> The where.</param>
/// <returns>An int.</returns>
public int DeleteAll <T>(SqlExpressionVisitor <T> where)
{
return(this.ExecuteScalar <int>(DialectProvider.ToDeleteRowStatement(where)));
}
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!");
}
/// <summary>An OrmConnection method that selects.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The ev.</param>
/// <param name="buffered"></param>
/// <returns>A List<T></returns>
public IEnumerable <T> Select <T>(SqlExpressionVisitor <T> expression, CommandFlags flags = CommandFlags.Buffered)
{
return(this.Query <T>(DialectProvider.ToSelectStatement(expression, flags)));
}
/// <inheritdoc />
protected internal override void Accept(SqlExpressionVisitor visitor)
{
visitor.Unary(this);
}
/// <summary>An OrmConnection method that firsts.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The ev.</param>
/// <returns>A T.</returns>
public T First <T>(SqlExpressionVisitor <T> expression)
{
return(Select(expression.Limit(1)).First());
}
