Exemplo n.º 1
0
        public static int Of_GetPageSQL(ref string as_SQL, string as_keyName, int ai_PageSize, int ai_PageIndex, bool ab_needTotalPage = true)
        {
            string ls_find_key_SQL;
            ls_find_key_SQL = as_SQL;

            n_findby_dw lnv_SQL = new n_findby_dw();
            lnv_SQL.of_setSQL(ls_find_key_SQL);
            lnv_SQL.of_SetDbtype(SqlHelper.Dbtype.ToString().ToLower());

            //只取主键列的数据 ,目前假设主键列,只有一个字段
            lnv_SQL.of_SetSelectPart(as_keyName);

            //取到前面多少条记录
            int li_top = 0;
            li_top = ai_PageSize * ai_PageIndex;
            lnv_SQL.of_SetTop(li_top);

            DataTable ldt_Tb = new DataTable();
            ls_find_key_SQL = lnv_SQL.of_GetSQLselect();
            ldt_Tb = SqlHelper.ExecuteDataTable(ls_find_key_SQL);

            //最终返回的SQL语句
            n_findby_dw lnv_rc = new n_findby_dw();
            lnv_rc.of_setSQL(as_SQL);

            string ls_InWhere = "";
            if (ldt_Tb.Rows.Count == 0)  //没有记录
            {
                lnv_rc.of_add_ons("find", "0=1");
                as_SQL = lnv_rc.of_GetSQLselect();
                return 0;
            }


            string ls_ColType = null;
            if (ldt_Tb.Columns.Count == 1)
            {
                ls_ColType = ldt_Tb.Columns[0].DataType.Name.ToLower();
            }
            else
            {
                ls_ColType = "string"; //有多列
            }

            string ls_One = null;
            string ls_In = null;
            ls_In = "";
            int li_row = 0;
            //取到这一页的主键值 
            int li_Total = 0;
            li_Total = ai_PageSize * ai_PageIndex;

            if (ldt_Tb.Rows.Count < li_Total)
                li_Total = ldt_Tb.Rows.Count;

            for (li_row = ai_PageSize * (ai_PageIndex - 1); li_row <= li_Total - 1; li_row++)
            {
                ls_One = ldt_Tb.Rows[li_row][0].ToString().Trim();

                //目前最多支持二列
                if (ldt_Tb.Columns.Count > 1)
                {
                    ls_One += "#" + ldt_Tb.Rows[li_row][1].ToString().Trim();
                }

                if (ls_ColType == "string")
                {
                    ls_One = "'" + ls_One + "'";
                }

                GYstring.gf_addstring_list(ref ls_In, ls_One, ",");
                //每5个值,换一行,好看点
                if (li_row % 5 == 0)
                {
                    ls_In += "\r\n";
                }

            }

            if (ls_In.Trim().Length > 0)
            {
                if (ldt_Tb.Columns.Count > 1)
                {
                    //多列用#联结起来
                    string ls_oneCol, ls_TwoCol;
                    int li_pos;
                    li_pos = as_keyName.IndexOf(",");
                    ls_oneCol = as_keyName.Substring(0, li_pos);
                    ls_TwoCol = as_keyName.Substring(li_pos + 1);

                    if (SqlHelper.Dbtype.ToString().ToLower() == "mssql")
                    {
                        as_keyName = "TRIM(" + ls_oneCol + ")+TRIM(" + ls_TwoCol + ")";
                    }
                    else //mysql版
                    {
                        as_keyName = "CONCAT(TRIM(" + ls_oneCol + "), CONCAT('#',TRIM(" + ls_TwoCol + "))) ";
                    }
                    ls_InWhere = as_keyName + " in (" + ls_In + ")";
                }

                ls_InWhere = as_keyName + " in (" + ls_In + ")";

            }
            else
            {
                ls_InWhere = "0=1";
            }

            //本次需要显示的记录对应的主键 id
            lnv_rc.of_add_ons("find", ls_InWhere);
            as_SQL = lnv_rc.of_GetSQLselect();


            //第一次计算一下,一共有多少条记录
            if (ai_PageIndex == 1)
            {
                if (!ab_needTotalPage)
                {
                    return 0;
                }

                lnv_SQL.of_SetTop(0);
                //不要显示top
                lnv_SQL.of_DelOrderBy(true);

                string ls_SelectCount = null;
                ls_SelectCount = lnv_SQL.of_GetSQLselect();

                //ls_SelectCount 统计里面的SQL出来的记录数量
                ls_SelectCount = " Select count(*) from (" + ls_SelectCount + ") as tt ";

                int ll_Total = 0;
                ll_Total = SqlHelper.ExecuteScalarNum(ls_SelectCount);

                return ll_Total;
                //返回总共多少条记录
            }
            else
            {
                return 1;
            }

        }
Exemplo n.º 2
0
        /// <summary>
        /// 取到分页的数据
        /// </summary>
        /// <param name="as_sql"></param>
        /// <param name="as_keyName"></param>
        /// <param name="ai_PageSize"></param>
        /// <param name="ai_PageIndex"></param>
        /// <param name="ai_totalrow"></param>
        /// <param name="ap_list"></param>
        /// <returns></returns>
        public static DataTable Of_GetPageDataTable(string as_sql, string as_keyName, int ai_PageSize, int ai_PageIndex, ref int ai_totalrow, GysoftParameter[] ap_list)
        {
            ErrStr = "";
            n_findby_dw lnv_find = new n_findby_dw();
            lnv_find.of_setSQL(as_sql);

            DataTable ldt_tb = new DataTable();
            ldt_tb= lnv_find.Of_GetPageDataTable(as_keyName, ai_PageSize, ai_PageIndex, ref ai_totalrow, ap_list);

            ErrStr = lnv_find.ErrorStr;

            return ldt_tb;
        }