/// <summary>
/// 直接设置并返回一个参数对象
/// </summary>
/// <param name="name">参数名</param>
/// <param name="type">参数类型</param>
/// <param name="value">值</param>
/// <returns>参数对象</returns>
public _DbParameter Set(string name, DbType type, object value)
{
_DbParameter par = new _DbParameter();
par.Name = name;
par.Type = type;
par.Value = value;
return par;
}
/// <summary>
/// 执行语句并返回第一行第一列的数据
/// </summary>
/// <param name="sql">语句</param>
/// <param name="pars">参数</param>
/// <returns>第一行第一列的数据</returns>
public object ExecuteScalar(string sql, _DbParameter[] pars)
{
_cmd.Parameters.Clear();
if (pars != null)
{
foreach (_DbParameter item in pars)
{
IDataParameter par = _cmd.CreateParameter();
par.ParameterName = item.Name;
par.DbType = item.Type;
par.Value = item.Value;
_cmd.Parameters.Add(par);
}
}
_cmd.CommandText = sql;
_exeCount++;
return _cmd.ExecuteScalar();
}
/// <summary>
/// 执行数据阅读
/// </summary>
/// <param name="sql">语句</param>
/// <param name="pars">参数</param>
/// <returns>阅读对象</returns>
public IDataReader ExecuteReader(string sql, _DbParameter[] pars)
{
return ExecuteReader(sql, pars, 0);
}
/// <summary>
/// 执行数据阅读
/// </summary>
/// <param name="sql">语句</param>
/// <param name="pars">参数</param>
/// <param name="limit">数量</param>
/// <returns>阅读对象</returns>
public IDataReader ExecuteReader(string sql, _DbParameter[] pars, int limit)
{
IDataReader reader;
using (IDbCommand cmd = _conn.CreateCommand())
{
if (_isTan) { cmd.Transaction = _tan; }
if (pars != null)
{
foreach (_DbParameter item in pars)
{
IDataParameter par = _cmd.CreateParameter();
par.ParameterName = item.Name;
par.DbType = item.Type;
par.Value = item.Value;
cmd.Parameters.Add(par);
}
}
if (!string.IsNullOrEmpty(sql) && limit > 0) { sql = sql.ToUpper().Replace("SELECT ", string.Format("SELECT TOP {0} ", limit)); }
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
}
_exeCount++;
return reader;
}
/// <summary>
/// 配合分页使用的数据阅读
/// </summary>
/// <param name="tb">数据表名</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">条件，不含 WHERE 关键字</param>
/// <param name="orderBy">排序针对字段</param>
/// <param name="orderMode">排序模式：ASC 或 DESC</param>
/// <param name="pageThis">当前分页，大于零</param>
/// <param name="pageSize">分页大小，大于零</param>
/// <param name="pars">参数列表</param>
/// <param name="rows">当前语句的处理行数</param>
/// <returns>数据阅读器</returns>
public IDataReader ExecutePager(string tb, string fieldList, string where, string orderBy, string orderMode, int pageThis, int pageSize, _DbParameter[] pars, ref int rows)
{
if (string.IsNullOrEmpty(orderBy)) { orderBy = "ID"; }
if (string.IsNullOrEmpty(orderMode)) { orderMode = "DESC"; }
string orderModeBack = "ASC";
if (!string.IsNullOrEmpty(orderMode) && orderMode.ToUpper() == "ASC") { orderModeBack = "DESC"; }
_cmd.Parameters.Clear();
if (pars != null)
{
foreach (_DbParameter item in pars)
{
IDataParameter par = _cmd.CreateParameter();
par.ParameterName = item.Name;
par.DbType = item.Type;
par.Value = item.Value;
_cmd.Parameters.Add(par);
}
}
if (pageThis < 1) { pageThis = 1; }
if (!string.IsNullOrEmpty(where))
{
where = string.Format(" WHERE {0}", where);
}
_cmd.CommandText = string.Format("SELECT COUNT([ID]) FROM {0}{1}", tb, where);
rows = Convert.ToInt32(_cmd.ExecuteScalar());
//构建SQL语句
string sql = "SELECT * FROM (";
sql += "SELECT TOP {6} * FROM (";
sql += "SELECT TOP {7} {5} FROM {0}{1} ORDER BY {2} {3}, [ID] {3}";
sql += ") {0} ORDER BY {2} {4}, [ID] {3}";
sql += ") {0} ORDER BY {2} {3}, [ID] {3}";
_cmd.CommandText = string.Format(sql, tb, where, orderBy, orderMode, orderModeBack, fieldList, pageSize, pageThis * pageSize);
_exeCount++;
return _cmd.ExecuteReader();
}
