C# Sql.OrderBy方法代码示例

        public PagedPeopleResult GetPaged(int itemsPerPage, int pageNumber, string sortColumn,
            string sortOrder, string searchTerm)
        {
            var items = new List<People>();
            var db = DatabaseContext.Database;

            var currentType = typeof(People);

            var query = new Sql().Select("*").From("demo_people");

            if (!string.IsNullOrEmpty(searchTerm))
            {
                int c = 0;
                foreach (var property in currentType.GetProperties())
                {
                    string before = "WHERE";
                    if (c > 0)
                    {
                        before = "OR";
                    }

                    var columnAttri =
                           property.GetCustomAttributes(typeof(ColumnAttribute), false);

                    var columnName = property.Name;
                    if (columnAttri.Any())
                    {
                        columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    }

                    query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
                    c++;
                }
            }
            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);
            else
            {
                query.OrderBy("id asc");
            }

            var p = db.Page<People>(pageNumber, itemsPerPage, query);
            var result = new PagedPeopleResult
            {
                TotalPages = p.TotalPages,
                TotalItems = p.TotalItems,
                ItemsPerPage = p.ItemsPerPage,
                CurrentPage = p.CurrentPage,
                People = p.Items.ToList()
            };
            return result;
        }

        public IEnumerable<object> GetAll(string typeName, string sortColumn, string sortOrder)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
            var strTableName = tableName.Value;

            var db = (Database)DatabaseContext.Database;
            if(!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            if (strTableName.IndexOf("[") < 0)
            {
                strTableName = "[" + strTableName + "]";
            }

            var query = new Sql().Select("*").From(strTableName);


            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
            {
                var strSortColumn = sortColumn;
                if (strSortColumn.IndexOf("[") < 0)
                {
                    strSortColumn = "[" + strSortColumn + "]";
                }

                query.OrderBy(strSortColumn + " " + sortOrder);
            }

            foreach (dynamic item in db.Fetch<dynamic>(query))
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);
                

                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    if(values.ContainsKey(propName))
                        prop.SetValue(obj, values[propName]);
                }

                yield return obj;
            }
            

            
        }

 public IEnumerable<Models.Forum> GetForums(int rootId)
 {
     var sql = new Sql();
     sql.Where<Models.Forum>(x => x.ParentId == rootId);
     sql.OrderBy<Models.Forum>(x => x.SortOrder);
     return _databaseContext.Database.Fetch<Models.Forum>(sql);
 }

        public Page<ForumTopicSubscriber> GetTopicSubscriptionsFromMember(int memberId, long take = 50, long page = 1)
        {
            var sql = new Sql()
                .Select("*")
                .From<ForumTopicSubscriber>();

            sql.Where<ForumTopicSubscriber>(x => x.MemberId == memberId);
            sql.OrderBy<ForumTopicSubscriber>(x => x.TopicId); // have to order by something when paging

            return _databaseContext.Database.Page<ForumTopicSubscriber>(page, take, sql);
        }

        public UIOMaticPagedResult GetPaged(string typeName, int itemsPerPage, int pageNumber, string sortColumn,
            string sortOrder, string searchTerm)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));

            var db = (Database)DatabaseContext.Database;
            if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            var query = new Sql().Select("*").From(tableName.Value);

            EventHandler<QueryEventArgs> tmp = BuildingQuery;
            if (tmp != null)
                tmp(this, new QueryEventArgs(tableName.Value, query));

            if (!string.IsNullOrEmpty(searchTerm))
            {
                int c = 0;
                foreach (var property in currentType.GetProperties())
                {
                    //if (property.PropertyType == typeof (string))
                    //{
                        string before = "WHERE";
                        if (c > 0)
                            before = "OR";

                        var columnAttri =
                           property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                        var columnName = property.Name;
                        if (columnAttri.Any())
                            columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;

                        query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
                        c++;

                    //}
                }
            }
            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);
            else
            {
                var primaryKeyColum = "id";

                var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
                if (primKeyAttri.Any())
                    primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;

                foreach (var property in currentType.GetProperties())
                {
                    var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
                    if (keyAttri.Any())
                        primaryKeyColum = property.Name;
                }

                query.OrderBy(primaryKeyColum + " asc");
            }

            EventHandler<QueryEventArgs> temp = BuildedQuery;
            if (temp != null)
                temp(this, new QueryEventArgs(tableName.Value,query));

            var p = db.Page<dynamic>(pageNumber, itemsPerPage, query);
            var result = new UIOMaticPagedResult
            {
                CurrentPage = p.CurrentPage,
                ItemsPerPage = p.ItemsPerPage,
                TotalItems = p.TotalItems,
                TotalPages = p.TotalPages
            };
            var items  = new List<object>();

            foreach (dynamic item in p.Items)
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);


                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;

                    if(values.ContainsKey(propName))
                        prop.SetValue(obj, values[propName]);
                }

//.........这里部分代码省略.........

        public UIOMaticPagedResult GetPaged(string typeName, int itemsPerPage, int pageNumber, string sortColumn,
            string sortOrder)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));

            var db = (Database)DatabaseContext.Database;
            if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            var query = new Sql().Select("*").From(tableName.Value);

            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);
            else
            {
                var primaryKeyColum = "id";

                var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
                if (primKeyAttri.Any())
                    primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;

                foreach (var property in currentType.GetProperties())
                {
                    var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
                    if (keyAttri.Any())
                        primaryKeyColum = property.Name;
                }

                query.OrderBy(primaryKeyColum + " asc");
            }

            var p = db.Page<dynamic>(pageNumber, itemsPerPage, query);
            var result = new UIOMaticPagedResult
            {
                CurrentPage = p.CurrentPage,
                ItemsPerPage = p.ItemsPerPage,
                TotalItems = p.TotalItems,
                TotalPages = p.TotalPages
            };
            var items  = new List<object>();

            foreach (dynamic item in p.Items)
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);

                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    prop.SetValue(obj, values[propName]);
                }

                items.Add(obj);
            }
            result.Items = items;
            return result;
        }

        public OrdersInfo Get(int pageNumber, string from, string till, string manager, string status, string searchString, string orderBy, string orderDirection, int discount)
        {
            DateTime fromDate, tillDate;
            var fromInUse = DateTime.TryParseExact(from, "yyyy-MM-dd", null, DateTimeStyles.AssumeLocal, out fromDate);
            var tillInUse = DateTime.TryParseExact(till, "yyyy-MM-dd", null, DateTimeStyles.AssumeLocal, out tillDate);
            var managerId = Int32.Parse(manager);
            var statusId = Int32.Parse(status);

            var query = new Sql();
            if (fromInUse)
            {
                query.Where("DateCreated >= @0", fromDate);
            }
            if (tillInUse)
            {
                query.Where("DateCreated <= @0", tillDate);
            }
            if (managerId == 0)
            {
                query.Where("ManagerId is NULL");
            }
            else if (managerId > 0)
            {
                query.Where("ManagerId = @0", managerId);
            }
            if (statusId == 0)
            {
                query.Where("Status is NULL");
            }
            else if (statusId > 0)
            {
                var newStatusNode = OrderModel.StatusCache.First(x => x.Value.Id == statusId).Value;
                query.Where("Status = @0", newStatusNode.GetPropertyValue<string>("dbValue"));
            }
            query.OrderBy(orderBy + " " + (orderDirection == "asc" ? "" : "desc"));

            var orderDtos = ApplicationContext.Current.DatabaseContext.Database.Fetch<OrderDto>(query); //заказы с учетом возможной фильтрации по дате, менеджеру и статусу, но без учета поисковой строки
            var orderModels = from orderDto in orderDtos select new OrderModel(orderDto, !String.IsNullOrEmpty(searchString)); //заказы же, но уже в виде "моделей" (с максимумом данных)
            if (!String.IsNullOrEmpty(searchString))
            {
                searchString = searchString.ToLower();

                var searchPhone = '+' + searchString.TrimStart('+'); //такая операция с плюсами для того, чтобы можно было искать и с плюсом, и без
                var ordersByMobilePhone = orderModels.Where(orderModel => orderModel.customerPhone.StartsWith(searchPhone));

                var searchNameParts = Regex.Matches(searchString, @"\b[A-Za-zА-ЯЁа-яё]+\b").Cast<Match>().Select(x => x.Value);
                var ordersByName = orderModels.Where(orderModel =>
                    {
                        return searchNameParts.All(searchNamePart =>
                            orderModel.customerFirstName.ToLower().StartsWith(searchNamePart) ||
                            orderModel.customerSecondName.ToLower().StartsWith(searchNamePart) ||
                            orderModel.customerLastName.ToLower().StartsWith(searchNamePart)
                        );
                    }
                ); //вариант фильтрации, когда необходимо попадание каждого словесного компонента поисковой строки в какой-нибудь компонент имени
                /*var ordersByName = orderModels.Where(orderModel =>
                    {
                        var firstName = orderModel.customerFirstName.ToLower();
                        var secondName = orderModel.customerSecondName.ToLower();
                        var lastName = orderModel.customerLastName.ToLower();
                        var found = searchNameParts.Any(x => firstName.StartsWith(x)) ||
                            searchNameParts.Any(x => secondName.StartsWith(x)) ||
                            searchNameParts.Any(x => lastName.StartsWith(x));
                        return found;
                    }
                );*/ //вариант фильтрации, когда достаточно попадания компонентов поисковой строки в один компонент имени

                var ordersByOrganization = orderModels.Where(orderModel => Regex.IsMatch(orderModel.customerOrganization.ToLower(), @"\b" + searchString));

                var ordersByProduct = orderModels.Where(orderModel => orderModel.items.Any(item => Regex.IsMatch(item.name.ToLower(), @"\b" + searchString)));

                orderModels = ordersByMobilePhone
                    .Concat(ordersByName)
                    .Concat(ordersByOrganization)
                    .Concat(ordersByProduct)
                    .Distinct()
                    .ToArray();
            }

            var totalPages = (int)Math.Ceiling((float)orderModels.Count() / PAGE_SIZE);
            if (pageNumber > totalPages) pageNumber = totalPages;
            return new OrdersInfo
            {
                items = orderModels.Skip(PAGE_SIZE * (pageNumber - 1)).Take(PAGE_SIZE),
                totalPages = totalPages
            };
        }