Exemplo n.º 1
0
        public static DataSourceResult KendoDataByQuery <T>([DataSourceRequest] DataSourceRequest request, string Query, string CustomWhere, string sortPrefix)
        {
            using (IDbConnection dbConn = new OrmliteConnection().openConn())
                using (var dbCommand = dbConn.CreateCommand())
                {
                    var data = new DataSourceResult();
                    //filter
                    List <string> sort  = new List <string>();
                    var           Tname = typeof(T).Name;
                    if (request.Sorts.Any())
                    {
                        foreach (SortDescriptor sortDescriptor in request.Sorts)
                        {
                            if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
                            {
                                sort.Add(sortPrefix + sortDescriptor.Member + " ASC");
                            }
                            else
                            {
                                sort.Add(sortPrefix + sortDescriptor.Member + " DESC");
                            }
                        }
                    }

                    string sortString = string.Join(",", sort.Select(s => s));


                    if (request.Filters.Any())
                    {
                        var where = KendoApplyFilter.ApplyFilterV(request.Filters[0], "") + (string.IsNullOrWhiteSpace(CustomWhere) ? "" : " AND " + CustomWhere);
                        var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
                        if (request.PageSize > 0)
                        {
                            if (request.Page > 1)
                            {
                                var    q        = Query.Replace("SELECT", "").Replace("select", "");
                                string newquery = @"SELECT  *
                                        FROM    ( SELECT ROW_NUMBER() OVER ( " + (!string.IsNullOrEmpty(order) ? "a." + order : "ORDER BY (SELECT NULL)") + @") AS RowNum, " + q + @"
                                                ) AS result
                                        WHERE   RowNum > " + (request.Page - 1) * request.PageSize + @"
                                            AND RowNum <= " + request.Page * request.PageSize + @"
                                        ORDER BY RowNum";
                                data.Data = dbConn.Query <T>(newquery);
                            }
                            else
                            {
                                data.Data = dbConn.Select <T>(q => q.Select(Query).Where(where).OrderBy(order).Limit(skip: ((request.Page - 1) * request.PageSize), rows: request.PageSize));
                            }
                        }
                        else
                        {
                            data.Data = dbConn.Select <T>(q => q.Select(Query).Where(where).OrderBy(order));
                        }

                        data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM (" + Query + " WHERE " + where + ") a");
                    }
                    else
                    {
                        var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "") + " ";
                        if (request.PageSize > 0)
                        {
                            data.Data = dbCommand.Select <T>(q => q.Select(Query).Where(CustomWhere).OrderBy(order).Limit(skip: ((request.Page - 1) * request.PageSize), rows: request.PageSize));
                            if (request.Page > 1)
                            {
                                var sql = dbCommand.CommandText.Replace(" )", " " + " WHERE " + CustomWhere + ")") + order;
                                data.Data = dbConn.Select <T>(sql);
                            }
                        }
                        else
                        {
                            data.Data = dbConn.Select <T>(q => q.Select(Query).Where(CustomWhere).OrderBy(order));
                        }

                        data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM (" + Query + (string.IsNullOrWhiteSpace(CustomWhere) ? "" : " WHERE " + CustomWhere) + ") a");
                    }
                    return(data);
                }
        }
Exemplo n.º 2
0
        public static DataSourceResult KendoData <T>([DataSourceRequest] DataSourceRequest request, string CustomWhere)
        {
            using (IDbConnection dbConn = new OrmliteConnection().openConn())
                using (var dbCommand = dbConn.CreateCommand())
                {
                    var data = new DataSourceResult();
                    //filter
                    List <string> sort  = new List <string>();
                    var           Tname = typeof(T).Name;
                    if (request.Sorts.Any())
                    {
                        foreach (SortDescriptor sortDescriptor in request.Sorts)
                        {
                            if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
                            {
                                sort.Add(sortDescriptor.Member + " ASC");
                            }
                            else
                            {
                                sort.Add(sortDescriptor.Member + " DESC");
                            }
                        }
                    }

                    string sortString = string.Join(",", sort.Select(s => s));


                    if (request.Filters.Any())
                    {
                        var where = KendoApplyFilter.ApplyFilterV(request.Filters[0], "") + " AND " + CustomWhere;
                        var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
                        if (request.PageSize > 0)
                        {
                            data.Data = dbCommand.Select <T>(q => q.Select("SELECT * FROM " + Tname + " WHERE" + where + order).Limit(skip: (request.Page - 1) * request.PageSize, rows: request.PageSize));
                            if (request.Page > 1)
                            {
                                var sql = dbCommand.CommandText.Replace(" )", " " + " WHERE" + where + ")") + order;
                                data.Data = dbConn.Select <T>(sql);
                            }
                        }
                        else
                        {
                            data.Data = dbConn.Select <T>(q => q.Select("SELECT * FROM " + Tname + " WHERE" + where + order));
                        }

                        data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM " + Tname + " WHERE " + where);
                    }
                    else
                    {
                        var where = " WHERE " + CustomWhere;
                        var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
                        if (request.PageSize > 0)
                        {
                            try
                            {
                                data.Data = dbCommand.Select <T>(q => q.Select("SELECT * FROM " + Tname + where + order).Limit(skip: (request.Page - 1) * request.PageSize, rows: request.PageSize));
                                if (request.Page > 1)
                                {
                                    var sql = dbCommand.CommandText.Replace(" )", " " + where + ")") + order;
                                    data.Data = dbConn.Select <T>(sql);
                                }
                            }
                            catch (Exception ex)
                            {
                                data.Data = null;
                            }
                        }
                        else
                        {
                            data.Data = dbConn.Select <T>(q => q.Select("SELECT * FROM " + Tname + where + order));
                        }
                        data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM " + Tname + where);
                    }
                    return(data);
                }
        }