示例#1
0
        public DataTable GetMPageDataTable(int pageIndex, int pageSize, string fields, string tableName, string conditions, string orderField, bool isDesc, bool showMehtodSql)
        {
            DataTable result;

            try
            {
                int    num = 0;
                object obj = this.ExecuteScalar(string.Concat(new string[]
                {
                    "select count(1) from ",
                    tableName,
                    " where 1 > 0 ",
                    conditions,
                    ""
                }));
                if (obj != null)
                {
                    num = int.Parse(obj.ToString());
                }
                int    num2 = (num % pageSize == 0) ? pageSize : (num % pageSize);
                int    num3 = (num % pageSize == 0) ? (num / pageSize) : (AccHelp.GetInt(num / pageSize) + 1);
                string text;
                if (isDesc)
                {
                    if (pageIndex <= 1)
                    {
                        text = string.Concat(new object[]
                        {
                            "select top ",
                            pageSize,
                            " ",
                            fields,
                            " from ",
                            tableName,
                            " where 1 > 0 ",
                            conditions,
                            " order by ",
                            orderField,
                            " desc"
                        });
                    }
                    else
                    {
                        if (pageIndex >= num3)
                        {
                            text = string.Concat(new object[]
                            {
                                "select * from (select top ",
                                num2,
                                " ",
                                fields,
                                " from ",
                                tableName,
                                " where 1 > 0 ",
                                conditions,
                                " order by ",
                                orderField,
                                " asc) T order by ",
                                orderField,
                                " desc"
                            });
                        }
                        else
                        {
                            text = string.Concat(new object[]
                            {
                                "Select Top ",
                                pageSize,
                                " ",
                                fields,
                                " From ",
                                tableName,
                                " Where ",
                                orderField,
                                "<(Select Min(",
                                orderField,
                                ") From (Select Top ",
                                (pageIndex - 1) * pageSize,
                                " ",
                                orderField,
                                " From ",
                                tableName,
                                " where 1=1 ",
                                conditions,
                                " Order By ",
                                orderField,
                                " desc) T) ",
                                conditions,
                                " Order By ",
                                orderField,
                                " desc "
                            });
                        }
                    }
                }
                else
                {
                    if (pageIndex <= 1)
                    {
                        text = string.Concat(new object[]
                        {
                            "select top ",
                            pageSize,
                            " ",
                            fields,
                            " from ",
                            tableName,
                            " where 1 > 0 ",
                            conditions,
                            " order by ",
                            orderField,
                            " asc"
                        });
                    }
                    else
                    {
                        if (pageIndex >= num3)
                        {
                            text = string.Concat(new object[]
                            {
                                "select *  from (select top ",
                                num2,
                                " ",
                                fields,
                                " from ",
                                tableName,
                                " where 1 > 0 ",
                                conditions,
                                " order by ",
                                orderField,
                                " desc) T order by ",
                                orderField,
                                " asc"
                            });
                        }
                        else
                        {
                            text = string.Concat(new object[]
                            {
                                "Select Top ",
                                pageSize,
                                " ",
                                fields,
                                " From ",
                                tableName,
                                " Where ",
                                orderField,
                                ">=(Select Max(",
                                orderField,
                                ") From (Select Top ",
                                (pageIndex - 1) * pageSize,
                                " ",
                                orderField,
                                " From ",
                                tableName,
                                " where 1=1 ",
                                conditions,
                                " Order By ",
                                orderField,
                                " Asc) T) ",
                                conditions,
                                " Order By ",
                                orderField,
                                " Asc"
                            });
                        }
                    }
                }
                DataTable dataTable = this.GetDataTable(text);
                if (showMehtodSql)
                {
                    HttpContext.Current.Response.Write(text);
                }
                result = dataTable;
            }
            catch (Exception)
            {
                result = null;
            }
            return(result);
        }
示例#2
0
        public DataTable GetPageDataTable(int pageIndex, int pageSize, int recordCount, string fields, string tableName, string orderField, string conditions, bool isDesc, bool showMehtodSql)
        {
            DataTable result;

            try
            {
                using (OleDbConnection oleDbConnection = new OleDbConnection(this.connStr))
                {
                    oleDbConnection.Open();
                    string text;
                    string text2;
                    if (isDesc)
                    {
                        text  = "desc";
                        text2 = "asc";
                    }
                    else
                    {
                        text  = "asc";
                        text2 = "desc";
                    }
                    int    num  = (recordCount % pageSize == 0) ? pageSize : (recordCount % pageSize);
                    int    num2 = (recordCount % pageSize == 0) ? (recordCount / pageSize) : (AccHelp.GetInt(recordCount / pageSize) + 1);
                    string text3;
                    if (pageIndex <= 1)
                    {
                        text3 = string.Concat(new object[]
                        {
                            "select top ",
                            pageSize,
                            " ",
                            fields,
                            " from ",
                            tableName,
                            " where ",
                            orderField,
                            ">0 ",
                            conditions,
                            " order by ",
                            orderField,
                            " ",
                            text
                        });
                    }
                    else
                    {
                        if (pageIndex >= num2)
                        {
                            text3 = string.Concat(new object[]
                            {
                                "select * from (select top ",
                                num,
                                " ",
                                fields,
                                " from ",
                                tableName,
                                " where ",
                                orderField,
                                ">0 ",
                                conditions,
                                " order by ",
                                orderField,
                                " ",
                                text2,
                                ")table1 order by ",
                                orderField,
                                " ",
                                text
                            });
                        }
                        else
                        {
                            text3 = string.Concat(new object[]
                            {
                                "select * from (select top ",
                                pageSize,
                                " * from (select top ",
                                pageIndex * pageSize,
                                " ",
                                fields,
                                " from ",
                                tableName,
                                " where ",
                                orderField,
                                ">0 ",
                                conditions,
                                " order by ",
                                orderField,
                                " ",
                                text,
                                ")table1 order by ",
                                orderField,
                                " ",
                                text2,
                                ")table2 order by ",
                                orderField,
                                " ",
                                text
                            });
                        }
                    }
                    OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(text3, oleDbConnection);
                    DataTable        dataTable        = new DataTable();
                    oleDbDataAdapter.Fill(dataTable);
                    if (showMehtodSql)
                    {
                        HttpContext.Current.Response.Write(text3);
                    }
                    result = dataTable;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return(result);
        }