/// <summary>
/// 计算查询结果数
/// </summary>
/// <param name="eaconn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public long Count(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);
return cmd.ExecuteScalar().ToLong();
}
}
public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return new ADODataAdapter().FillDataSet(cmd);
}
}
public object ExecuteScalarWithTrans(IEAConnection eaconn, string cmdText)
{
object value = null;
using (IDbConnection conn = eaconn.Open())
{
using (System.Data.IDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
try
{
cmd.Transaction = trans;
value = cmd.ExecuteScalar();
trans.Commit();
}
catch (Exception ex)
{
GlobalLogger.Error<DataContext>(string.Format("执行sql={0}出错", cmdText), ex);
trans.Rollback();
throw;
}
}
}
}
return value.ToSafeString();
}
public object ExecuteScalar(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return cmd.ExecuteScalar();
}
}
public object ExecuteScalar(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
return cmd.ExecuteScalar();
}
}
public object ExecuteProcScalar(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return cmd.ExecuteScalar();
}
}
public DataSet ExecuteProcDataSet(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
{
using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return new ADODataAdapter().FillDataSet(cmd);
}
}
}
public void ExecuteNonQuery(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行sql命令，返回DataTable
/// </summary>
/// <param name="cmdText">sql命令</param>
/// <param name="parameters">条件参数</param>
/// <param name="pageInfo">分页信息</param>
/// <returns></returns>
public DataTable ExecuteDataTable(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters, string orderBy, PageInfo pageInfo)
{
pageInfo.ItemCount = Count(eaconn, cmdText, parameters);
if (!string.IsNullOrEmpty(orderBy) && !orderBy.Replace(" ", "").ToLower().StartsWith("orderby"))
{
orderBy = string.Format("order by {0} ", orderBy);
}
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
//AddParameters(eaconn.DatabaseType, cmd, parameters);
//cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);
AddParameters(eaconn.DatabaseType, cmd, parameters);
string paramChar = DataUtil.GetParamChar(eaconn.DatabaseType);
parameters.SafeAdd(paramChar + "PageIndex", pageInfo.PageIndex);
parameters.SafeAdd(paramChar + "PageSize", pageInfo.PageSize);
if (eaconn.DatabaseType == DatabaseType.MySQL)
{
cmd.CommandText = string.Format("{0} {1} limit {2}, {3} ", cmd.CommandText, orderBy, (pageInfo.PageIndex - 1) * pageInfo.PageSize, pageInfo.PageSize);
}
else if (eaconn.DatabaseType == DatabaseType.Oracle)
{
cmd.CommandText = string.Format("SELECT * FROM(SELECT ROWNUM RN,T.* FROM ({0} {2})T WHERE ROWNUM <= {1}PageIndex*{1}PageSize) WHERE RN >=({1}PageIndex-1)*{1}PageSize+1", cmd.CommandText, paramChar, orderBy);
}
else
{
cmd.CommandText = string.Format("select Top ({0}PageSize) * from (select ROW_NUMBER() OVER( {2} ) AS row_number, * from ({1}) P ) T Where row_number>({0}PageIndex-1)*{0}PageSize", paramChar, cmd.CommandText, string.IsNullOrEmpty(orderBy) ? "ORDER BY CURRENT_TIMESTAMP" : orderBy);
}
AddParameter(cmd, "PageIndex", pageInfo.PageIndex);
AddParameter(cmd, "PageSize", pageInfo.PageSize);
DataSet ds = new ADODataAdapter().FillDataSet(cmd);
return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
}
}
public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
DataSet ds = new DataSet();
return new ADODataAdapter().FillDataSet(cmd);
}
}