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 DataTable GetAll() { DataBase db = new DataBase(); string comstr = "select DepartmentID,DepartmentName,DepartmentDes,X,Y,Z from Department"; return(db.GetDataTable(comstr)); }
public DataTable GetAll() { DataBase db = new DataBase(); string comstr = "select users.* from users where 1=1"; return(db.GetDataTable(comstr)); }
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 DataTable GetAll(PaperTypeEntity en) { DataBase db = new DataBase(); string sql = "select PTID,PTName,PTDesc from PaperType"; return(db.GetDataTable(sql)); }
public DataTable GetAll() { string sql = "select PPID,PRKID,PTID,Name,PICPath,SelfResource,UpdateTime,PPDesc,ProblemNum from ProblemPaper"; DataBase db = new DataBase(); return(db.GetDataTable(sql)); }
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 DataTable GetAll() { DataBase db = new DataBase(); string comstr = "select PowerID,PowerName,PowerDes from Powers"; return(db.GetDataTable(comstr)); }
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 DataTable GetAll() { DataBase db = new DataBase(); string sql = "select * from UserIntegralDetail"; return(db.GetDataTable(sql)); }
public DataTable select(ManagerEnitity en) { DataBase db = new DataBase(); string comstr = "select ManagerName from Manager where ManagerID=@ManagerID"; SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@ManagerID", SqlDbType.VarChar, 50); param[0].Value = en.ManagerID; return(db.GetDataTable(comstr, param)); }
public DataTable select(ClientEntity en) { DataBase db = new DataBase(); string comstr = "select ClientName from Client where ClientID=@ClientID"; SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@ClientID", SqlDbType.VarChar, 50); param[0].Value = en.ClientID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByID(PowerEntity en) { DataBase db = new DataBase(); string comstr = "select PowerID,PowerName,PowerDes from Powers where PowerID=@PowerID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("PowerID", SqlDbType.Int, 4) }; param[0].Value = en.PowerID; return(db.GetDataTable(comstr, param)); }
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 DataTable GetByID(DepartmentEntity en) { DataBase db = new DataBase(); string comstr = "select DepartmentName,DepartmentDes,X,Y,Z from Department where DepartmentID=@DepartmentID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DepartmentID", SqlDbType.Int, 4) }; param[0].Value = en.DepartmentID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByID(PagesEnitity en) { DataBase db = new DataBase(); string comstr = "select p.pageDes,p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl from pages p where p.id=@ID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.Int, 4) }; param[0].Value = en.Id; return(db.GetDataTable(comstr, param)); }
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 DataTable GetByID(ManagerEnitity en) { DataBase db = new DataBase(); string comstr = "select Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n,Powers.PowerName,Department.DepartmentName\tfrom Manager,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID\r\nand ManagerID=@ManagerID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ManagerID", SqlDbType.VarChar, 500) }; param[0].Value = en.ManagerID; return(db.GetDataTable(comstr, param)); }
public DataTable GetProblemNum(ProjectPaperEntity en) { DataBase db = new DataBase(); string sql = "select ProblemNum from ProblemPaper where PPID=@PPID"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@PPID", SqlDbType.Int) }; para[0].Value = en.PPID; return(db.GetDataTable(sql, para)); }
public DataTable GetByID(ManagerPagePowerEnitity en) { DataBase db = new DataBase(); string comstr = "select p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl from pages p ,ManagerPagePower tpp,Manager t where tpp.id=p.id and tpp.managerid=t.managerid and tpp.TPP_ID=@ID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@TPP_ID", SqlDbType.Int, 4) }; param[0].Value = en.Tpp_ID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByManager(ManagerPagePowerEnitity en) { DataBase db = new DataBase(); string comstr = "select pagesort as id,pagename as title ,pageurl as url,pid as ParentID from Managerpagepower tpp,pages p where p.id=tpp.id and tpp.Managerid=@ManagerID order by p.pagesort "; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ManagerID", SqlDbType.VarChar, 50) }; param[0].Value = en.ManagerID; return(db.GetDataTable(comstr, param)); }
public DataTable GetAllByManager(ManagerPagePowerEnitity en) { DataBase db = new DataBase(); string comstr = "select p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl ,(select count(*) from pages pp where pp.parentid=p.id )as ChildCount ,tpp.managerid from managerpagepower tpp,pages p where p.id=tpp.id and tpp.Managerid=@ManagerID order by p.pagesort "; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ManagerID", SqlDbType.VarChar, 50) }; param[0].Value = en.ManagerID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByID(UserEntity en) { DataBase db = new DataBase(); string comstr = "select users.*,DepartmentName from users,Department where userid=@UserID and users.DepartmentID= Department.DepartmentID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.VarChar, 50) }; param[0].Value = en.UserID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByParent(PagesEnitity en) { DataBase db = new DataBase(); string comstr = "select p.pageDes,p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl ,(select count(*) from pages pp where pp.parentid=p.id )as ChildCount from pages p where p.parentid=@ParentID order by p.pagesort"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ParentID", SqlDbType.Int, 4) }; param[0].Value = en.ParentID; return(db.GetDataTable(comstr, param)); }
public DataTable GetByUrl(PagesEnitity en) { DataBase db = new DataBase(); string comstr = "select p.pageDes from pages p where p.PageUrl=@PageUrl"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@PageUrl", SqlDbType.VarChar, 200) }; param[0].Value = en.PageURL; return(db.GetDataTable(comstr, param)); }
public DataTable GetByID(ClientEntity en) { DataBase db = new DataBase(); string comstr = "select Client.ClientID,ClientName,ClientPwd,ClientImage,ClientDes\r\nfrom Client\r\nwhere ClientID=@ClientID"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ClientID", SqlDbType.VarChar, 500) }; param[0].Value = en.ClientID; return(db.GetDataTable(comstr, param)); }
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 DataTable GetById(ProjectPaperEntity en) { DataBase db = new DataBase(); SqlParameter[] param = new SqlParameter[] { new SqlParameter("@PPID", SqlDbType.Int, 4) }; param[0].Value = en.PPID; string sql = "select ProjectPaper.*,PaperType.PTName from ProjectPaper,PaperType where PPID=@PPID and PaperType.PTID=ProjectPaper.PTID"; return(db.GetDataTable(sql, param)); }
public DataTable GetById(RecordEntity en) { DataBase db = new DataBase(); SqlParameter[] param = new SqlParameter[] { new SqlParameter("@RecordID", SqlDbType.Int, 4) }; param[0].Value = en.RecordID; string sql = "select Record.* from Record where RecordID=@RecordID "; return(db.GetDataTable(sql, param)); }
public DataTable GetByID(UserIntegralDetailEntity mo) { DataBase db = new DataBase(); string sql = "select ui.DetailID as DetailID,ui.UserID as UserID,ui.IntegralChange as IntegralChange,ui.ChangeDes as ChangeDes,us.UserName as UserName from UserIntegralDetail ui,Users us where ui.UserID=us.UserID"; if (mo.DetailID != 0) { sql += " and ui.DetailID=@DetailID"; } if (mo.UserID != null && mo.UserID != "") { sql += " and ui.UserID=@UserID"; } 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; return(db.GetDataTable(sql, param)); }