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()); }