/// <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(); } } } }
/// <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(); } } } }