g.hnyhua.cn/Mtxfw.Utility/DataAccess/DataTable.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using SqlSugar;

namespace Mtxfw.Utility.DataAccess
{
    /// <summary>
    /// DataTable类
    /// </summary>
    public partial class Sql : DbHelperSqlSugar
    {
        /// <summary>
        /// 通过SQL语句获取DataSet
        /// </summary>
        /// <param name="sql">sql -> select * from table where id=@id and name=@name</param>
        /// <param name="parameters">参数 -> new {id=1,name="a"}</param>
        /// <returns>返回DataSet数据</returns>
        public DataTable GetDataTable(string sql, object parameters)
        {
            return Db.Ado.GetDataTable(sql, parameters);
        }

        /// <summary>
        /// 通过SQL语句获取DataSet
        /// </summary>
        /// <param name="sql">sql -> select * from table where id=@id and name=@name</param>
        /// <param name="parameters">
        /// 参数 -> new SugarParameter("@id",1), 
        /// 多个参数 -> new SugarParameter []{ new SugarParameter("@id",1), new SugarParameter("@name",2)}
        /// </param>
        /// <returns>返回DataSet数据</returns>
        public DataTable GetDataTable(string sql, params SugarParameter[] parameters)
        {
            return Db.Ado.GetDataTable(sql, parameters);
        }

        /// <summary>
        /// 通过SQL语句获取DataSet
        /// </summary>
        /// <param name="sql">sql -> select * from table where id=@id and name=@name</param>
        /// <param name="parameters">
        /// 多个参数 -> new List<SugarParameter>(){ new SugarParameter("@id",1), new SugarParameter("@name",2) }
        /// </param>
        /// <returns>返回DataSet数据</returns>
        public DataTable GetDataTable(string sql, List<SugarParameter> parameters)
        {
            return Db.Ado.GetDataTable(sql, parameters);
        }

        /// <summary>
        /// 通过SQL语句获取DataTable(带分页)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="page">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, int page, int pageSize, string orderBy)
        {
            int recordCount = Db.Ado.GetInt(PagingHelper.CreateCountingSql(sql));
            return Db.Ado.GetDataTable(PagingHelper.CreatePagingSql(recordCount, pageSize, page, sql, orderBy));
        }

        /// <summary>
        /// 通过SQL语句获取DataTable(带分页)
        /// <param name="sql"></param>
        /// <param name="page">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="orderBy">排序</param>
        /// <param name="recordCount">总记录数 返回值</param>
        public DataTable GetDataTable(string sql, int page, int pageSize, string orderBy, out int recordCount)
        {
            recordCount = Db.Ado.GetInt(PagingHelper.CreateCountingSql(sql));
            return Db.Ado.GetDataTable(PagingHelper.CreatePagingSql(recordCount, pageSize, page, sql, orderBy));
        }


        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public DataTable FindToDataTable<T>(Expression<Func<T, bool>> predicate, string orderBy = "") where T : class, new()
        {
            return Db.Queryable<T>().Where(predicate).OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy).ToDataTable();
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public DataTable FindToDataTable<T>(string strWhere, string orderBy = "") where T : class, new()
        {
            return Db.Queryable<T>().Where(strWhere).OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy).ToDataTable();
        }
        
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public DataTable FindToDataTableByWhere<T>(string strWhere = "", string orderBy = "") where T : class, new()
        {
            return Db.Queryable<T>().WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy).ToDataTable();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="predicate"></param>
        /// <param name="orderByPredicate"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public DataTable FindToDataTablePage<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> predicate, string orderBy, ref int records) where T : class, new()
        {
            int count = GetCount(predicate);
            var page = Db.Queryable<T>().Where(predicate).OrderBy(orderBy).ToDataTablePage(pageIndex, pageSize, ref records);
            return page;
        }
    }
}