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); }