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