public void Asp(GridView gv, AspNetPager pager, UserIntegralDetailEntity mo)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@DetailID", SqlDbType.Int);
param[0].Value = mo.DetailID;
param[1] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[1].Value = mo.UserID;
string comstr = "with IntegralInfo as\r\n (\r\n select ui.DetailID as DetailID,ui.UserID as UserID,ui.IntegralChange as IntegralChange,ui.ChangeDes as ChangeDes,us.UserName as UserName,Row_Number() over(order by DetailID) as row_number from UserIntegralDetail ui,Users us where ui.UserID=us.UserID";
string comstr2 = "select count(*) from UserIntegralDetail ui,Users us where ui.UserID=us.UserID";
if (mo.DetailID != 0)
{
comstr += " and ui.DetailID=@DetailID";
comstr2 += " and ui.DetailID=@DetailID";
}
if (mo.UserID != null && mo.UserID != "")
{
comstr += " and ui.UserID=@UserID";
comstr2 += " and ui.UserID=@UserID";
}
comstr += " ) select * from IntegralInfo where row_number>{0} and row_number<{1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public void Asp2(DataList gv, AspNetPager pager, UserHolidayEntity mo)
{
DataBase db = new DataBase();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[0].Value = mo.UserID;
string comstr = "with UserHolidayInfo as\r\n (\r\n select UserHoliday.*,(case when RecordState=1 then '未读' when RecordState=2 then '已读,认同加分' else '已读,不认同加分' end ) as CheckState,Users.UserName as UserName,ChangDes,Row_Number() over(order by UserHolidayID) as row_number from Users,UserHoliday,UserIntegraDetail where UserHoliday.UserID=Users.UserID,Users.UserID=UserIntegraDetail.UserID";
// string comstr = "with WrongProblemInfo as\r\n (\r\n select WrongProblem.*,(case when RecordState=1 then '教师未读' when RecordState=2 then '教师已读,认同加分' else '教师已读,不认同加分' end ) as CheckState,Problems.ProblemName as ProblemName,Problems.ProblemDes as ProblemDes,Users.UserName as UserName,Row_Number() over(order by WrongProblemID) as row_number from Problems,Users,WrongProblem where WrongProblem.UserID=Users.UserID and WrongProblem.ProblemID=Problems.ProblemID";
string comstr2 = "select count(*) from Users,UserHoliday where UserHoliday.UserID=Users.UserID ";
if (mo.UserID != null && mo.UserID != "")
{
comstr += " and UserHoliday.UserID=@UserID";
comstr2 += " and UserHoliday.UserID=@UserID";
}
comstr += " )select * from UserHolidayInfo where row_number>{0} and row_number<={1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public void Asp_Holiday(GridView gv, AspNetPager pager, UserEntity en)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[0].Value = en.UserID;
param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
param[1].Value = en.UserName;
string comstr = "with UsersInfo as(select users.*,\r\n (\r\n select count(*) from UserHoliday where UserHoliday.UserID=users.UserID and UserHoliday.RecordState=1) as num,\r\n Row_Number() over(order by users.UserID) as row_number from users where 1=1";
string comstr2 = "select count(*) from users where 1=1 \t";
if (en.UserID != null && en.UserID != "")
{
comstr += " and UserID=@UserID";
comstr2 += " and UserID=@UserID";
}
if (en.UserName != null && en.UserName != "")
{
comstr += " and UserName Like '%'+@UserName+'%'";
comstr2 += " and UserName Like '%'+@UserName+'%'";
}
comstr += " )select * from UsersInfo where row_number>{0} and row_number<={1};";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public void Asp(GridView gv, AspNetPager pager, DepartmentEntity en)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@DepartmentName", SqlDbType.VarChar, 100)
};
param[0].Value = en.DepartmentName;
DataBase db = new DataBase();
string comstr = "with DepartmentsInfo as\r\n(select *,Row_Number() over(order by DepartmentID) as row_number from Department\twhere 1=1";
// string comstr="with DepartmentsInfo as\r\n(select *,Row_Number() over(order by DepartmentID) as row_number from Department\twhere 1=1";
string comstr2 = "select count(*) from Department\twhere 1=1";
// string comstr2 = "select count(*) from Department\twhere 1=1";
if (en.DepartmentName != null && en.DepartmentName != "")
{
comstr += " and DepartmentName Like '%'+@DepartmentName+'%'";
comstr2 += " and DepartmentName Like '%'+@DepartmentName+'%'";
}
comstr += " )select * from DepartmentsInfo where row_number>{0} and row_number<={1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public void Asp(GridView gv, AspNetPager pager)
{
DataBase db = new DataBase();
string comstr = "with PowersInfo as \r\n(\r\n\tselect *,Row_Number() over(order by PowerID ) as row_number from Powers \r\n)\r\nselect * from PowersInfo where row_number>{0} and row_number<={1}";
string comstr2 = "select count(*) from Powers";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2));
gv.DataSource = db.GetDataTable(comstr);
gv.DataBind();
}
public void Asp(DataList gv, AspNetPager pager)
{
DataBase db = new DataBase();
string sql = @"with OrderTable as(select ItemID,ParentID,ItemName,ItemContent,ItemSort,ItemKind,ItemUrl,IsTop,IsHome,NewsSort,DisplayNum,DepartID,Row_Number() over(order by ItemID,ItemSort)as row_number from Item) select * from OrderTable where row_number>{0} and row_number<{1}";
string sql1 = "select count(*) from Item";
sql = string.Format(sql, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(sql1));
gv.DataSource = db.GetDataTable(sql);
gv.DataBind();
}
public bool IsOverDate(UserEntity en)
{
DataBase db = new DataBase();
string comstr = "select count(*) from users where UserID=@UserID and OverDate>getdate() ";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UserID", SqlDbType.VarChar, 50)
};
param[0].Value = en.UserID;
return(int.Parse(db.ExecuteValue(comstr, param)) > 0);
}
public void Asp(GridView gv, AspNetPager pager, ManagerEnitity en)
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@ManagerID", SqlDbType.VarChar, 50);
param[0].Value = en.ManagerID;
param[1] = new SqlParameter("@DepartmentID", SqlDbType.Int, 4);
param[1].Value = en.DepartmentID;
param[2] = new SqlParameter("@PowerID", SqlDbType.Int, 4);
param[2].Value = en.PowerID;
param[3] = new SqlParameter("@ManagerName", SqlDbType.VarChar, 100);
param[3].Value = en.ManagerName;
/*row_number() over (order by '字段名')是
* 微软最新发布的MSSQL2005,对TSQL进行了小规模的加强 按照字段名进行排序，可以实现数据分页功能
*
*/
string comstr = "with ManagerInfo as\r\n(\r\nselect Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n ,Powers.PowerName,Department.DepartmentName\t,\r\nRow_Number() over(order by ManagerID) as row_number\tfrom Manager ,Powers,Department\r\nwhere\tManager.PowerID=powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID";
// string comstr = "with ManagerInfo as\r\n(\r\nselect Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n,Powers.PowerName,Department.DepartmentName\t,\r\nRow_Number() over(order by ManagerID) as row_number\tfrom Manager,Powers,Department\r\nwhere\tManager.PowerID=powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID)";
// string comstr2 = "select count(*) from Manager,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\t and Manager.DepartmentID=Department.DepartmentID";
string comstr2 = "select count(*)\tfrom Manager ,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID";
if (en.DepartmentID != 0)
{
comstr += " and Manager.DepartmentID=@DepartmentID";
comstr2 += " and Manager.DepartmentID=@DepartmentID";
}
if (en.PowerID != 0)
{
comstr += " and Manager.PowerID=@PowerID";
comstr2 += " and Manager.PowerID=@PowerID";
}
if (en.ManagerID != "" && en.ManagerID != null)
{
comstr += " and ManagerID=@ManagerID";
comstr2 += " and ManagerID=@ManagerID";
}
if (en.ManagerName != "" && en.ManagerName != null)
{
comstr += " and ManagerName Like '%'+@ManagerName+'%'";
comstr2 += " and ManagerName Like '%'+ManagerName+'%'";
}
comstr += " )select * from ManagerInfo where row_number>{0} and row_number<={1};";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public bool Validate_Login(UserEntity en)
{
DataBase db = new DataBase();
string comstr = "select count(*) from users where userid=@UserID and userPwd=@UserPwd";
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[0].Value = en.UserID;
param[1] = new SqlParameter("@UserPwd", SqlDbType.VarChar, 50);
param[1].Value = en.UserPwd;
int flag = Convert.ToInt32(db.ExecuteValue(comstr, param));
return(flag > 0);
}
public void Asp(DataList gv, AspNetPager pager, ItemEntity en)
{
DataBase db = new DataBase();
SqlParameter[] p = new SqlParameter[3];
p[0] = new SqlParameter("@DepartID", SqlDbType.Int);
p[0].Value = en.DepartID;
p[1] = new SqlParameter("@ItemName", SqlDbType.VarChar, 100);
p[1].Value = en.ItemName;
p[2] = new SqlParameter("@ItemContent", SqlDbType.VarChar, -1);
p[2].Value = en.ItemContent;
string sql = @"with OrderTable as(select ItemID,ParentID,ItemName,ItemContent,ItemSort,ItemKind,ItemUrl,IsTop,IsHome,NewsSort,DisplayNum,DepartID,Row_Number() over(order by ItemID,ItemSort)as row_number from Item where 1=1 ";
string sql1 = "select count(*) from Item where 1=1 ";
if (null != (object)en.DepartID)//???
{
sql += " and DepartID=@DepartID";
sql1 += " and DepartID=@DepartID";
}
if (en.ItemName != null && en.ItemName != "")
{
sql += "and ItemName like '%'+@ItemName+'%' ";
sql1 += "and ItemName like '%'+@ItemName+'%' ";
}
if (en.ItemContent != "" && en.ItemContent != null)
{
sql += "and ItemContent like '%'+@ItemContent+'%' ";
sql1 += "and ItemContent like '%'+@ItemContent+'%' ";
}
sql += ") select * from OrderTable where row_number>{0} and row_number<{1}";
sql = string.Format(sql, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(sql1, p));
gv.DataSource = db.GetDataTable(sql, p);
gv.DataBind();
}
public void Asp2(GridView gv, AspNetPager pager, ProjectPaperEntity en)
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@PPID", SqlDbType.Int, 4);
param[0].Value = en.PPID;
string comstr = "with ProjectPaperInfo as\r\n(select ProjectPaper.Name,ProjectPaper.ProjectHead,Department.* from ProjectPaper,Department where ProjectPaper.State =2 and ProjectPaper.DepartmentID=Department.DepartmentID ";
string comstr2 = "select count(*) from ProjectPaper ";
if (en.PPID != 0)
{
comstr += " and ProjectPaper.PPID=@PPID ";
comstr2 += " where ProjectPaper.PPID=@PPID ";
}
comstr += " )select * from ProjectPaperInfo;";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public int GetMaxSort(PagesEnitity en)
{
DataBase db = new DataBase();
string comstr = "select max(pagesort) from pages where parentid=@ParentID";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ParentID", SqlDbType.Int, 4)
};
param[0].Value = en.ParentID;
string str = db.ExecuteValue(comstr, param);
int result;
if (str.Length > 0)
{
result = int.Parse(str) + 1;
}
else
{
result = 1;
}
return(result);
}
public int GetChildCount(PagesEnitity en)
{
DataBase db = new DataBase();
string comstr = "select count(*) from pages where parentid=@ID";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ID", SqlDbType.Int, 4)
};
param[0].Value = en.Id;
string str = db.ExecuteValue(comstr, param);
int result;
if (str.Length > 0)
{
result = int.Parse(str);
}
else
{
result = 0;
}
return(result);
}
