Example #1
0
        /// <summary>
        /// 查询显示分页后的列表数据.根据字典生成条件,用于时间条件为时间段时.
        /// </summary>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable SelectPage(Dictionary <string, object> dic, String order, int pageCurrent, int pageSize = 10, string tableName = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                int number;
                InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number); //获取分页信息

                string tt, ttt;
                InternalBase <T> .GetColumnName(columnName, out tt, out ttt);

                sql = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM (  SELECT " + tt + " FROM " + strTable + " t  " + strCondition + strOrder + ") tt  WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
Example #2
0
        /// <summary>
        /// 根据字典,返回分页需要是sql语句
        /// </summary>
        /// <param name="dataSql">返回查询datatable的sql</param>
        /// <param name="countSql">返回查询数量的sql</param>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public void GetSelectPageSql(out string dataSql, out string countSql, Dictionary <string, object> dic, String order, int pageCurrent, int pageSize = 10, string tableName = "", string columnName = "*")
        {
            dataSql  = string.Empty;
            countSql = string.Empty;
            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                int number;
                InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number); //获取分页信息

                string tt, ttt;
                InternalBase <T> .GetColumnName(columnName, out tt, out ttt);

                dataSql  = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM (  SELECT " + tt + " FROM " + strTable + " t  " + strCondition + strOrder + ") tt  WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
                countSql = "select count (*) from " + strTable + strCondition;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, dataSql + "\r\n" + countSql);
            }
        }
Example #3
0
        /// <summary>
        /// 查询返回所有数据,根据字典生成条件,用于时间条件为时间段时.
        /// </summary>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable Select(Dictionary <string, object> dic, string order = "", string tableName = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段

                sql = string.Format("select {0} from {1} {2} {3}", columnName, strTable, strCondition, strOrder);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
Example #4
0
        /// <summary>
        /// 查询返回所有数据,根据实体对象生成条件.
        /// </summary>
        /// <param name="t">实体类,将查询条件赋给实体类的属性</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable Select(T t, string order = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName();

                string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段

                sql = string.Format("select {0} from {1} {2} {3}", columnName, strTable, strCondition, strOrder);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
Example #5
0
        /// <summary>
        /// 查询显示分页数据.返回json字符串(包含列表数据和总数据量数据,配合easyui使用),根据实体对象生成条件.
        /// </summary>
        /// <param name="strTable">表名</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="strCondition">条件,需要填写where</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public string SelectPageToJSONBySQL(string strTable, int pageCurrent, int pageSize = 10, string order = "", String strCondition = "", string columnName = "*")
        {
            string sql      = "";
            string countSql = "";

            try
            {
                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                sql = this.GetSelectSql(strTable, pageCurrent, pageSize, strOrder, strCondition, columnName);
                int       number = (pageCurrent - 1) * pageSize;
                DataTable dt     = this.SelectBySQL(sql);
                countSql = "select count (*) from " + strTable + strCondition;
                var pageCount = dbl.GetSingle(countSql);
                return(JSONHelper.ObjectToJson(new { total = pageCount, rows = dt }));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql + "\r\n" + countSql);
                return("");
            }
        }