//public static DataTable PageQuery(string needfields, string fromcondition,string keyfields ,string keyfieldsdeclare, int pagesize, int pages, out int counts, string db)
//{
// string sqlCreate = "declare @indextable table(indextableid int identity(1,1) PRIMARY KEY," + keyfieldsdeclare + ") ;";
// string KeySql = "select "+keyfields+" from "+fromcondition+" ";
// string sqlKeyinsert = "insert into @indextable(" + keyfields + ") " + KeySql + ";";
// string sqlCt = "select recrowcount= @@rowcount;";
// string[] needf = needfields.Split(new char[] { ',' });
// for (int i = 0; i < needf.Length; i++)
// {
// needf[i] = "t1."+needf[i].ToString();
// }
// string sqlSearch = "select "+string.Join(",",needf)+" from @indextable t0,(" + orgSql + ") t1 where t0.indextableid>=" + ((page - 1) * pageSize + 1).ToString() + " and t0.indextableid<" + (page * pageSize + 1).ToString() + tempKey3;
//}
/// <summary>
/// 执行Sql语句返回影响条数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dbName">操作的数据库名</param>
/// <returns>SQL语句影响条数</returns>
public static int ProcessSqlNonQuery(string sql, string dbName)
{
PersistenceBroker broker = PersistenceBroker.Instance();
IDbCommand cmd = broker.GetCommand(dbName);
cmd.CommandText = sql;
Logger.Write(sql); //add by duhu
return(broker.Execute(dbName, cmd));
}
/// <summary>
/// 执行Sql语句返回DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dbName">操作的数据库名</param>
/// <returns>SQL语句查询结果集</returns>
public static DataSet ProcessMultiSql(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.ExecuteMultiQuery(cmd, dbName));
}
//创建新的Sql命令对象：需要的参数加入IDbCommand.Parameters集合
private static IDbCommand BuildQueryCommand(string storedProcName, IDataParameter[] param, string dbName)
{
PersistenceBroker broker = PersistenceBroker.Instance();
IDbCommand cmd = broker.GetCommand(dbName);
cmd.CommandText = storedProcName;
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
IDataParameter p = param[i];
cmd.Parameters.Add(p);
}
}
return(cmd);
}
/// <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>
/// 返回一个IDataParameter,是SqlParameter,还是OleDbParameter
/// 由databaseName对应数据库的数据库类型确定
/// </summary>
/// <param name="databaseName">数据库名</param>
/// <returns></returns>
public static IDataParameter GetParameter(string databaseName)
{
PersistenceBroker broker = PersistenceBroker.Instance();
return(broker.GetCommand(databaseName).CreateParameter());
}
