[Asp.Net]ミリオン・ページ
5515 ワード
/// <param name="fieldlist"></param> , id,title,content
/// <param name="condition"></param> , id<100
/// <param name="pkey"></param> ,
/// <param name="tablename"></param> , News
/// <param name="sort"></param> ,0 ,1
/// <param name="pagesize"></param>
/// <param name="cpage"></param>
public DataSet GetCurrentDataSet(string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage)//
{
DBCon OAcon = new DBCon();
SqlConnection conn = new SqlConnection(OAcon.getOAcon());
SqlDataAdapter cmd = new SqlDataAdapter(GetSql(fieldlist, condition, pkey, tablename, sort, pagesize, cpage), conn);
DataSet ds = new DataSet();
cmd.Fill(ds);
return ds;
}
static string GetSql(string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage)
{
string sql = "";
if (sort == 0)
{
if (condition != "")
{
sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + condition + " and " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " where " + condition + " and order by " + pkey + " desc) order by " + pkey + " desc";
}
else
{
sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " order by " + pkey + " desc) order by " + pkey + " desc";
}
}
else
{
if (condition != "")
{
sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + condition + " and " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " where " + condition + " and order by " + pkey + " asc) order by " + pkey + " asc";
}
else
{
sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " order by " + pkey + " asc) order by " + pkey + " asc";
}
}
return sql;
}
public int GetCounts(string pkey, string tablename, string condition)//
{
string sql;
if (condition != "")
{
sql = "select count(" + pkey + ") from " + tablename + " where " + condition;
}
else
{
sql = "select count(" + pkey + ") from " + tablename;
}
DBCon OAcon = new DBCon();
SqlConnection conn = new SqlConnection(OAcon.getOAcon());
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dr.Read();
string str = dr[0].ToString();
dr.Close();
return Convert.ToInt32(str);
}
public DataSet GetCurrentDataSet2(string tbName, string tbFields, string OrderField, int PageSize, int PageIndex, bool OrderType)
{
SqlParameter[] parameters = new SqlParameter[8];
parameters[0] = new SqlParameter("@tbName", SqlDbType.NVarChar,255);
parameters[1] = new SqlParameter("@tbFields", SqlDbType.NVarChar, 1000);
parameters[2] =new SqlParameter("@OrderField", SqlDbType.NVarChar,1000 );
parameters[3] =new SqlParameter("@PageSize", SqlDbType.Int);
parameters[4] =new SqlParameter("@PageIndex", SqlDbType.Int);
parameters[5]=new SqlParameter("@OrderType", SqlDbType.Bit);
parameters[6] =new SqlParameter("@strWhere", SqlDbType.VarChar,1000);
parameters[7]=new SqlParameter("@Total", SqlDbType.Int);
parameters[0].Value = tbName;
parameters[1].Value = tbFields;
parameters[2].Value = OrderField;
parameters[3].Value = PageSize;
parameters[4].Value = PageIndex;
parameters[5].Value = 1;
parameters[6].Value = "";
parameters[7].Value = 0;
TX.DataAccess.DABaseAccess db = new TX.DataAccess.DABaseAccess();
return db.RunprocToDataSet("SqlDataPaging", parameters); //DbHelperSQL.RunProcedure("SqlDataPaging", parameters, "ds");
}