/// <summary>
        /// 执行 T-SQL 语句,并返回数据库记录集合 [ 含分页 ]
        /// </summary>
        /// <param name="sql">string T-SQL 语句</param>
        /// <param name="values">params object[] 参数集合 </param>
        /// <param name="pageId">int 页码</param>
        /// <param name="pageSize">int 页尺寸</param>
        /// <returns>Utils.HashCollection 记录集合</returns>
        public Utils.Hash GetHashCollectionByPageId(string sql, int pageId, int pageSize, params object[] values)
        {
            using (MySqlConnection conn = new MySqlConnection(this._ConnectionString))
            {
                using (MySqlCommand comd = new MySqlCommand(sql, conn))
                {
                    double recordCount = 0;
                    double recordStart = ((pageId - 1) * pageSize);
                    double recordEnd   = recordStart + pageSize;

                    this.AddParameters(comd, values);
                    conn.Open();
                    try
                    {
                        using (MySqlDataReader reader = comd.ExecuteReader())
                        {
                            Utils.Hash           result = new Utils.Hash();
                            Utils.HashCollection data   = new Utils.HashCollection();
                            while (reader.Read())
                            {
                                if (recordCount >= recordStart && recordCount < recordEnd)
                                {
                                    Utils.Hash item = new Utils.Hash();
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        if (reader.GetValue(i) == DBNull.Value && reader.GetFieldType(i).Name == "String")
                                        {
                                            item[reader.GetName(i)] = String.Empty;
                                        }
                                        else
                                        {
                                            item[reader.GetName(i)] = reader.GetValue(i);
                                        }
                                    }
                                    data.Add(item);
                                }
                                recordCount++;
                            }
                            reader.Close();
                            result["data"]      = data;
                            result["pageCount"] = Math.Ceiling(recordCount / pageSize);
                            return(result);
                        }
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
 /// <summary>
 /// 执行 T-SQL 语句,并返回数据库记录集合
 /// </summary>
 /// <param name="sql">string T-SQL 语句</param>
 /// <param name="values">params object[] 参数集合 </param>
 /// <returns>Utils.HashCollection 记录集合</returns>
 public Utils.Hash GetHashCollection(string sql, params object[] values)
 {
     using (MySqlConnection conn = new MySqlConnection(this._ConnectionString))
     {
         using (MySqlCommand comd = new MySqlCommand(sql, conn))
         {
             this.AddParameters(comd, values);
             conn.Open();
             try
             {
                 using (MySqlDataReader reader = comd.ExecuteReader())
                 {
                     Utils.Hash           result = new Utils.Hash();
                     Utils.HashCollection data   = new Utils.HashCollection();
                     while (reader.Read())
                     {
                         Utils.Hash item = new Utils.Hash();
                         for (int i = 0; i < reader.FieldCount; i++)
                         {
                             if (reader.GetValue(i) == DBNull.Value && reader.GetFieldType(i).Name == "String")
                             {
                                 item[reader.GetName(i)] = String.Empty;
                             }
                             else
                             {
                                 item[reader.GetName(i)] = reader.GetValue(i);
                             }
                         }
                         data.Add(item);
                     }
                     reader.Close();
                     result["data"] = data;
                     return(result);
                 }
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 conn.Close();
             }
         }
     }
 }
Esempio n. 3
0
 /// <summary>
 /// 执行 T-SQL 语句,并返回数据库记录集合
 /// </summary>
 /// <param name="sql">string T-SQL 语句</param>
 /// <param name="pageID">int 页码</param>
 /// <param name="pageSize">int 页尺寸</param>
 /// <param name="values">params object[] 参数集合 </param>
 /// <returns>Utils.HashCollection 记录集合</returns>
 public Utils.Hash GetHashCollection(string sql, int pageID, int pageSize, params object[] values)
 {
     using (SqlConnection conn = new SqlConnection(this._ConnectionString))
     {
         Match  match       = Regex.Match(sql, "Order By [^()]*$", RegexOptions.IgnoreCase);
         int    rowStart    = ((pageID - 1) * pageSize) + 1;
         int    rowEnd      = rowStart + pageSize - 1;
         int    recordCount = 0;
         string sqlOrder    = "Order By 1 Asc";
         string sqlCount    = "Select Count(*) As Count From ({SQL}) T";
         string sqlSelect   = "Select * From (Select *,ROW_NUMBER() OVER ({ORDER}) AS [RowIndex] From ({SQL}) T) TT Where [RowIndex] Between " + rowStart + " And " + rowEnd + " Order By [RowIndex] Asc";
         if (match.Success)
         {
             sql      = sql.Replace(match.Value, "");
             sqlOrder = match.Value;
         }
         sqlCount  = sqlCount.Replace("{SQL}", sql);
         sqlCount  = sqlCount.Replace("{ORDER}", sqlOrder);
         sqlSelect = sqlSelect.Replace("{SQL}", sql);
         sqlSelect = sqlSelect.Replace("{ORDER}", sqlOrder);
         using (SqlCommand comd = new SqlCommand(sqlSelect, conn))
         {
             this.AddParameters(comd, values);
             conn.Open();
             try
             {
                 using (SqlCommand _comd = new SqlCommand(sqlCount, conn))
                 {
                     recordCount = Utils.Parse.ToInt(_comd.ExecuteScalar());
                 }
                 using (SqlDataReader reader = comd.ExecuteReader())
                 {
                     Utils.Hash           result = new Utils.Hash();
                     Utils.HashCollection data   = new Utils.HashCollection();
                     while (reader.Read())
                     {
                         Utils.Hash item = new Utils.Hash();
                         for (int i = 0; i < reader.FieldCount; i++)
                         {
                             if (reader.GetValue(i) == DBNull.Value && reader.GetFieldType(i).Name == "String")
                             {
                                 item[reader.GetName(i)] = String.Empty;
                             }
                             else
                             {
                                 item[reader.GetName(i)] = reader.GetValue(i);
                             }
                         }
                         data.Add(item);
                     }
                     reader.Close();
                     result["Data"]      = data;
                     result["Count"]     = recordCount;
                     result["PageCount"] = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(recordCount) / Convert.ToDouble(pageSize)));
                     return(result);
                 }
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 conn.Close();
             }
         }
     }
 }