jquery datatable server side paging for postgresql

参考自这篇修改

https://github.com/peterschlosser/DataTablesCoreMVCDapper

因为常用 jquery datatable 但是一直没时间整合后端分页,所幸老外都写好了

这篇需要注意到分页页码有 bug 需要将 DataTablesLogicDapper.cs 修改成以下这样才可以正常运作

// Copyright (c) Peter Schlosser. All rights reserved.  Licensed under the MIT license. See LICENSE.txt in the project root for license information.
using DataTablesCoreMVCDapper.Data;
using System.Threading.Tasks;

namespace DataTablesCoreMVCDapper.Models
{
    /// <summary>
    /// The DataTablesLogicDapper links DataTables AJAX requests from MVC Controllers to non-generic
    /// response handlers fulfilled by Dapper ORM.  The non-generic methods define the root SQL query
    /// and pass requests through the DataTablesRequestAsync generic method for request fulfillment.
    /// </summary>
    public class DataTablesLogicDapper
    {
        /// <summary>
        /// Handles generic DataTablesRequest for TSource data using Dapper ORM
        /// </summary>
        /// <param name="request">DataTables Ajax Request</param>
        /// <param name="baseSQLQuery">The base SQL query for TSource</param>
        /// <returns>DataTablesResponse for TSource data</returns>
        public static async Task<DataTablesResponse<TSource>> DataTablesRequestAsync<TSource>(DataTablesRequest request, string baseSQLQuery)
        {
            //这边要改 MSSQL or PG
            // prepare the data and count SQL queries
            //var totalQuery = new DataTablesContextPG(baseSQLQuery, request);

            var totalQuery = new DataTablesContext(baseSQLQuery, request);
            var filterQuery = totalQuery.Where();

            //
            int total = await totalQuery.CountAsync();

            //
            int recordsFilter = await filterQuery.CountAsync();

            var dataQuery = filterQuery.OrderBy().SkipTake();

            // run the queries and return the response
            return new DataTablesResponse<TSource>()
            {
                Draw = request.Draw,
                //RecordsTotal = await totalQuery.CountAsync(),

                //资料总数
                RecordsTotal = total,

                //分页数量
                RecordsFiltered = recordsFilter,

                //
                Data = await dataQuery.DataAsync<TSource>(),
                Error = request.Error,
            };
        }

        /// <summary>
        /// Handles the DataTablesRequest for the Customer Database
        /// </summary>
        /// <param name="request">DataTables Ajax Request</param>
        /// <returns>DataTablesResponse for Customer table</returns>
        /// <remarks>
        /// When intergration with projects without Data.Repository classes, the 
        /// base SQL query may be hard-coded here when making the call to the generic
        /// DataTablesRequestAsync() method.
        /// </remarks>
        public static async Task<DataTablesResponse<Customer>> DataTablesCustomerRequestAsync(DataTablesRequest request)
        {
            return await DataTablesRequestAsync<Customer>(request, CustomerRepository.BaseQuery());

            //return await DataTablesRequestAsync<Customer>(request, CustomerRepositoryPG.BaseQuery());
        }
    }
}

 

接着若要使用 postgresql 则需要安装 npgsql 并且修改连线字串

将此专案内新增类别 DataTablesContextPG

其中 DataTablesContextPG  需要修改 MSSQL 内的 + 号语法为 PG 的 || 语法,并且把 count 的子查询加上 alias name

public class DataTablesContextPG
    {
        public DataTablesContextPG(string query, DataTablesRequest request)
        {
            Query = query;
            Request = request;
        }

        public static string SearchToken { get; } = "SearchValue";
        public string Query { get; protected internal set; }
        public DataTablesRequest Request { get; protected set; }

        /// <summary>
        /// Dapper ORM SqlConnection Factory
        /// </summary>
        /// <returns>A new instance of DbConnection with the database for use by Dapper ORM.</returns>
        public static DbConnection GetOpenConnection()
        {
            var connection = new NpgsqlConnection(ConfigurationExtensions.GetConnectionString(Startup.Configuration, "DefaultConnection"));
            connection.Open();

            return connection;
        }

        /// <summary>
        /// Runs the specified data query and returns an IEnumerable of one or more TSource records.
        /// </summary>
        public static async Task<IEnumerable<TSource>> QueryAsync<TSource>(string query, object param)
        {
            using (var db = GetOpenConnection())
            {
                return await db.QueryAsync<TSource>(query, param);
            }
        }

        /// <summary>
        /// Runs the specified T query and return the single T result.
        /// </summary>
        public static async Task<T> QuerySingleAsync<T>(string query, object param = null)
        {
            using (var db = GetOpenConnection())
            {
                return await db.QuerySingleAsync<T>(query, param);
            }
        }
    }

    /// <summary>
    /// Extensions to format and execute generic SQL queries from DataTablesContext.Request settings
    /// </summary>
    /// <remarks>
    /// These extensions link the DataTablesContext with data returned by Dapper ORM.  The SQL query
    /// string formatters prepare the SQL query (DataTablesContext.Query) for sorting, filtering and
    /// paging using criteria defined by the DataTables AJAX request (DataTablesContext.Request.)
    /// The SQL query executors pass one or more SQL queries through mehtods in DataTablesContext
    /// down to Dapper ORM returning data needed to fulfill the DataTables AJAX request.
    /// </remarks>
    public static class DataTablesContextExtensionsPG
    {
        #region SQL Query Formatters

        // methods to format fragments of a SQL query in the form:
        // SELECT * FROM (BASE-QUERY) WHERE-CLAUSE ORDER-BY OFFSET-FETCH
        // where:
        //  BASE-QUERY is the SQL query to populate a list of data-model-class objects.
        //      the base query is a complete SQL query without ORDER-BY or GROUP-BY declaritives,
        //      and may include one or more JOIN to populate lists of data-model-class objects,
        //      example: SELECT * FROM [table]
        //  WHERE-CLAUSE is the SQL query fragment used to filter results by search string.
        //      example: WHERE [column0] LIKE '%@SearchValue%' OR [column1] LIKE '%@SearchValue%' OR ...
        //  ORDER-BY orders results by one or more columns. example: ORDER BY [column1] DESC
        //  OFFSET-FETCH limits result set returns for a given page. example: OFFSET 20 ROWS
        //      FETCH NEXT 10 ROWS ONLY
        // fragments are used to build three different queries returning:
        //  count of all displayable rows in table
        //  count of filtered (using search item) rows in table
        //  list of (optionally) filtered and ordered data rows for one page in DataTables UI table.

        public static DataTablesContextPG OrderBy(this DataTablesContextPG context)
        {
            try
            {
                context.Query = string.Concat(context.Query, context.Request.Order.Count == 0
                    // when no orderby specified, provide generic ORDER BY
                    // because ORDER BY is required when using OFFSET-FETCH.
                    ? @" ORDER BY 1"
                    : @" ORDER BY " + string.Join(",", context.Request.Order
                        .Select(o => $@"{context.Request.Columns[o.Column].Name}"
                            + (o.Descending ? " DESC" : "")
                        )
                    ));
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        public static DataTablesContextPG SkipTake(this DataTablesContextPG context)
        {
            try
            {
                // OFFSET-FETCH works beautifully to give us a specific page of rows
                // from the full result set, but requires the use of ORDER BY.
                var offset = Math.Max(0, context.Request.Start);
                var fetch = Math.Max(0, context.Request.Length);

                //context.Query = string.Concat(context.Query, $@" OFFSET {offset} ROWS" +
                //    (fetch > 0 ? $" FETCH NEXT {fetch} ROWS ONLY" : string.Empty));

                context.Query = string.Concat(context.Query,
                    $@" LIMIT {fetch} " +
                    $@" OFFSET {offset} ");

            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        public static DataTablesContextPG Where(this DataTablesContextPG context)
        {
            try
            {
                // Note: the use of percent wilcard around search value.
                context.Query = string.Concat(context.Query, (string.IsNullOrWhiteSpace(context.Request.Search?.Value))
                    ? " WHERE 1=1"
                    : " WHERE " + string.Join(" OR ", context.Request.Columns
                        .Where(c => c.Searchable)
                        .Select(c => $@"{c.Name} LIKE '%'||@{DataTablesContextPG.SearchToken}||'%'")));
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        #endregion

        #region SQL Query Executors
        // methods to execute generic SQL queries using Dapper

        public static DynamicParameters GetParam(this DataTablesContextPG context)
        {
            var param = new DynamicParameters();
            param.Add(DataTablesContextPG.SearchToken, context.Request.Search?.Value);
            return param;
        }

        /// <summary>
        /// Runs the context COUNT query and return the integer result.
        /// </summary>
        public static async Task<int> CountAsync(this DataTablesContextPG context)
        {
            var query = $@"SELECT COUNT(*) FROM ( {context.Query} ) as cnt ;";
            try
            {
                return await DataTablesContextPG.QuerySingleAsync<int>(query, context.GetParam());
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    query,
                    Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
                    ex.StackTrace);
                return 0;
            }
        }

        /// <summary>
        /// Runs the context data query and returns an IEnumerable of one or more TSource records.
        /// </summary>
        public static async Task<IEnumerable<TSource>> DataAsync<TSource>(this DataTablesContextPG context)
        {
            try
            {
                return await DataTablesContextPG.QueryAsync<TSource>(context.Query, context.GetParam());
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    context.Query,
                    Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
                    ex.StackTrace);
                return new List<TSource>();
            }
        }

        #endregion
    }

 

最后就是把 DataTablesLogicDapper 类别的 function DataTablesRequestAsync

var totalQuery = new DataTablesContextPG(baseSQLQuery, request);

修改为

var totalQuery = new DataTablesContext(baseSQLQuery, request);

如此一来就搞定了

 

 

 

 

 

 

 

 

 


发布留言