/// <summary>
/// 执行存储过程,返回DataTable
/// </summary>
/// <param name="storeProcName">存储过程名</param>
/// <param name="ps">存储过程所需参数数组</param>
/// <param name="dbName">数据库名</param>
/// <returns>存储过程返回的DataTable</returns>
public static DataTable RunProcedure(string storeProcName, IDataParameter[] ps, string dbName)
{
IDbCommand cmd = Query.BuildQueryCommand(storeProcName, ps, dbName);
PersistenceBroker broker = PersistenceBroker.Instance();
return(broker.ExecuteQuery(cmd, dbName));
}
/// <summary>
/// 执行Sql语句返回DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dbName">操作的数据库名</param>
/// <returns>SQL语句查询结果集</returns>
public static DataTable ProcessSql(string sql, string dbName)
{
PersistenceBroker broker = PersistenceBroker.Instance();
IDbCommand cmd = broker.GetCommand(dbName);
cmd.CommandTimeout = 120;
cmd.CommandText = sql;
Logger.Write(sql); //add by duhu
return(broker.ExecuteQuery(cmd, dbName));
}
/// <summary>
/// 执行查询，并返回查询所返回结果集的第一行第一列。忽略额外的行或列
/// </summary>
/// <returns></returns>
public object ExecuteScalar()
{
PersistenceBroker broker = PersistenceBroker.Instance();
RelationalDatabase rdb = broker.GetDatabaseCopy(this.DatabaseName);
IDbCommand cmd = rdb.GetCommand();
cmd.CommandText = this.BuildForQuery(cmd);
DataTable dt = broker.ExecuteQuery(cmd, this.DatabaseName);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(dt.Rows[0][0]);
}
}
/// <summary>
/// add by tintown 2008-7-3使用Parameter的方式进行传参执行SQL
///如：string sql = "select top 100 * from Line_TCPF_Line where memberid=@id";
///IDataParameter para = PersistenceLayer.Query.GetParameter("hotel");
///para.ParameterName = "@id";
/// para.Value = 6632;
/// IDataParameter[] paras = new IDataParameter[1];
///paras[0] = para;
/// DataTable dt = PersistenceLayer.Query.ProcessSql(sql, paras, "hotel");
/// </summary>
/// <param name="sql">自己写的SQL语句</param>
/// <param name="param">参数数组</param>
/// <param name="dbName">数据源名</param>
/// <returns>查询结果集</returns>
public static DataTable ProcessSql(string sql, IDataParameter[] param, string dbName)
{
PersistenceBroker broker = PersistenceBroker.Instance();
IDbCommand cmd = broker.GetCommand(dbName);
cmd.CommandTimeout = 120;
cmd.CommandText = sql;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
IDataParameter p = param[i];
cmd.Parameters.Add(p);
}
}
Logger.Write(sql); // add by duhu
return(broker.ExecuteQuery(cmd, dbName));
}
/// <summary>
/// 执行查询，并返回查询结果集
/// </summary>
/// <returns>查询结果</returns>
public DataTable Execute()
{
PersistenceBroker broker = PersistenceBroker.Instance();
RelationalDatabase rdb = broker.GetDatabaseCopy(this.DatabaseName);
IDbCommand cmd = rdb.GetCommand();
cmd.CommandText = this.BuildForQuery(cmd);
string cmdText = cmd.CommandText;
if (rdb.Vendor == DatabaseVendor.Oracle)
{
int topInd = cmdText.IndexOf("Top");
if (topInd != -1)
{
cmdText = cmdText.Substring(0, topInd) + cmdText.Substring(topInd + 6);
//使用ORCALE添加rownum的方法重装SQL语句
cmdText = PersistenceBroker.AddOracleTopWhere(cmdText, this.Top);
}
}
cmd.CommandText = cmdText;
return(broker.ExecuteQuery(cmd, this.DatabaseName));
}
