/// <summary>
/// 获取以往项目列表，带有总数目
/// </summary>
/// <param name="opType">旧项目分类</param>
/// <param name="page">页数</param>
/// <param name="pageSize">每页数量</param>
/// <param name="sort">排序元</param>
/// <param name="sordtOrder">排序顺序</param>
/// <returns></returns>
public oldProjectWithtotal GetProjectsWithTotal(int opType,int page,int pageSize,string sort,string sordtOrder)
{
string optype_;
if (opType == 0)
{
optype_ = " ";
}
else
{
optype_ = " and opType = " + opType.ToString() + " ";
}
oldProjectWithtotal opt = new oldProjectWithtotal();
conn.Open();
cmd.CommandText = "select count(opID) from oldProjects_tb where 1 = 1 " + optype_;
opt.total =Convert.ToInt32( cmd.ExecuteScalar());
if (page == 1)
{
//cmd.CommandText = "select * from oldProjects_tb where opType=" + optype_;
cmd.CommandText = "select top " + pageSize +
" oldProjects_tb.opID,oldProjects_tb.opTitle,oldProjects_tb.opImg,oldProjects_tb.opIntro,oldProjects_tb.opYear,oldProjects_tb.opLeader,oldProjects_tb.opType,opTypeTB.typeName from ( oldProjects_tb left join opTypeTB on oldProjects_tb.opType=opTypeTB.typeID ) where 1=1 "
+ optype_+" order by "
+ sort + " " + sordtOrder + " ";
}
else
{
cmd.CommandText = "select top " + pageSize +
" oldProjects_tb.opID,oldProjects_tb.opTitle,oldProjects_tb.opImg,oldProjects_tb.opIntro,oldProjects_tb.opYear,oldProjects_tb.opLeader,oldProjects_tb.opType,opTypeTBtypeName from (oldProjects_tb left join opTypeTB on oldProjects_tb.opType=opTypeTB.typeID ) where 1=1 "
+ optype_ + " and opID not in ( select top " + (page - 1) * pageSize +
" and opID from oldProjects_tb order by " + sort + " " + sordtOrder + " ) order by " + sort + " " + sordtOrder + " ";
}
dr = cmd.ExecuteReader();
List<oldProject> oplist = new List<oldProject>();
while (dr.Read())
{
oldProject op = new oldProject();
op.opID = Convert.ToInt32(dr["opID"]);
op.opTitle = dr["opTitle"].ToString();
op.opYear = dr["opYear"].ToString();
op.opLeader = dr["opLeader"].ToString();
op.opType = Convert.ToInt32(dr["opType"]);
op.opTypeName = dr["typeName"].ToString();
op.opImg = dr["opImg"].ToString().Trim();
op.opIntro = dr["opIntro"].ToString();
oplist.Add(op);
}
conn.Close();
opt.rows = oplist;
return opt;
}
public int SaveOldProject(oldProject op)
{
conn.Open();
if (op.opID == 0)
{
//添加
cmd.CommandText = "insert into oldProjects_tb (opContent,opDepartment,opEmail,opImg,opIntro,opLeader,opNum,opTitle,opType,opYear) "
+ " values(@opContent,@opDepartment,@opEmail,@opImg,@opIntro,@opLeader,@opNum,@opTitle,@opType,@opYear)";
cmd.Parameters.AddWithValue("@opContent", op.opContent);
cmd.Parameters.AddWithValue("@opDepartment", op.opDepartment);
cmd.Parameters.AddWithValue("@opEmail", op.opEmail);
cmd.Parameters.AddWithValue("@opImg", op.opImg);
cmd.Parameters.AddWithValue("@opIntro", op.opIntro);
cmd.Parameters.AddWithValue("@opLeader", op.opLeader);
cmd.Parameters.AddWithValue("@opNum", op.opNum);
cmd.Parameters.AddWithValue("@opTitle", op.opTitle);
cmd.Parameters.AddWithValue("@opType", op.opType);
cmd.Parameters.AddWithValue("@opYear", op.opYear);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity ";
op.opID = (int)cmd.ExecuteScalar();
}
else
{
//修改
cmd.CommandText = "update oldProjects_tb set opContent=@opContent,opDepartment=@opDepartment,opEmail=@opEmail,opImg=@opImg,opIntro=@opIntro,opLeader=@opLeader,opNum=@opNum,opTitle=@opTitle,opType=@opType,opYear=@opYear "
+ " where opID=@opID ";
cmd.Parameters.AddWithValue("@opContent", op.opContent);
cmd.Parameters.AddWithValue("@opDepartment", op.opDepartment);
cmd.Parameters.AddWithValue("@opEmail", op.opEmail);
cmd.Parameters.AddWithValue("@opImg", op.opImg);
cmd.Parameters.AddWithValue("@opIntro", op.opIntro);
cmd.Parameters.AddWithValue("@opLeader", op.opLeader);
cmd.Parameters.AddWithValue("@opNum", op.opNum);
cmd.Parameters.AddWithValue("@opTitle", op.opTitle);
cmd.Parameters.AddWithValue("@opType", op.opType);
cmd.Parameters.AddWithValue("@opYear", op.opYear);
cmd.Parameters.AddWithValue("@opID", op.opID);
cmd.ExecuteNonQuery();
}
conn.Close();
return op.opID;
}
public oldProject GetOldProject(int opid)
{
oldProject op = new oldProject();
conn.Open();
cmd.CommandText = "select * from( oldProjects_tb left join opTypeTB on oldProjects_tb.opType=opTypeTB.typeID )where opID=" + opid;
dr = cmd.ExecuteReader();
if (dr.Read())
{
op.opContent = dr["opContent"].ToString();
op.opDepartment = dr["opDepartment"].ToString();
op.opEmail = dr["opEmail"].ToString();
op.opID = opid;
op.opImg = dr["opImg"].ToString();
op.opIntro = dr["opIntro"].ToString();
op.opLeader = dr["opLeader"].ToString();
op.opNum = dr["opNum"].ToString();
op.opTitle = dr["opTitle"].ToString();
op.opType = Convert.ToInt32(dr["opType"]);
op.opTypeName = dr["TypeName"].ToString();
op.opYear = dr["opYear"].ToString();
}
conn.Close();
return op;
}