1 ///
2 ///默认下获取分页数据3 ///
4 /// 当前页码
5 /// 每一页的行数
6 /// BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime
7 public DataTable GetPagedModel(int pageIndex, intpageSize)8 {9 string strOrder = "order by AddDate DESC";10 string strSQL = string.Format(@"select * from11 (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0) as tb12 where num between {1}*({2}-1)+1 and {1}*{2} {0}", strOrder, pageSize, pageIndex);13
14 returnSQLHelper.GetDataTable(strSQL);15 }16
17 ///
18 ///含参数的查询19 ///
20 ///
21 ///
22 /// BookName、ClassName、DeptID、DeptPID、Category、[State]
23 /// BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime
24 public DataTable GetPagedModel(int pageIndex, intpageSize, Hashtable ht)25 {26 stringstrCondition;27 ListparasList;28 string sOrder="order by AddDate DESC";29
30 //获取参数
31 GetSearchCondition(ht,out strCondition,outparasList);32
33 string strSQL = string.Format(@"select * from (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0 {3}) as tb34 where num between {1}*({2}-1)+1 and {1}*{2} {0}", sOrder , pageSize, pageIndex,strCondition);35
36 returnSQLHelper.GetDataTable(strSQL,parasList.ToArray());37
38 }39
40 ///
41 ///获取详细信息42 ///
43 ///
44 ///
45 public DataTable GetDetail(intBookID)46 {47 string strSQL = "select ClassTarget,BookFeature from BookInfo where BookID="+BookID;48
49 returnSQLHelper.GetDataTable(strSQL);50 }51 ///
52 ///含参数记录的条数53 ///
54 /// BookName、ClassName、DeptID、DeptPID、Category、[State]
55 ///
56 public intGetRecordCount(Hashtable ht)57 {58 stringstrCondition;59 ListparasList;60
61 //获取参数
62 GetSearchCondition(ht, out strCondition, outparasList);63
64 string strSQL = string.Format("select count(0) from ViewBookInfo where IsDel=0 {0}", strCondition);65
66 returnConvert.ToInt32(SQLHelper.ExecuteScalar(strSQL, parasList.ToArray()));67 }68
69 ///
70 ///装配查询参数,获取查询要的字符串,和参数集合71 ///
72 /// BookName、ClassName、DeptID、DeptPID、Category、[State]
73 ///
74 ///
75 private void GetSearchCondition(Hashtable ht, out string contString, out Listparas)76 {77 StringBuilder sOrder = newStringBuilder();78 paras=new List();79
80 if (ht.Count == 0)81 {82 contString = "";83 return;84 }85
86 object oBookName = ht["BookName"];87 object oClassName = ht["ClassName"];88 object oDeptID = ht["DeptID"];89 object oDeptPID = ht["DeptPID"];90 object oCategory = ht["Category"];91 object oState = ht["State"];92
93 //书名
94 if (oBookName != null && !string.IsNullOrEmpty(oBookName.ToString()))95 {96 sOrder.Append("and BookName like @BookName or BookPY like @BookName");97 paras.Add(new SqlParameter("@BookName",oBookName.ToString()+"%"));98 }99 //课程名
100 if(oClassName!=null&&!string.IsNullOrEmpty(oClassName.ToString()))101 {102 sOrder.Append("and ClassName like @ClassName");103 paras.Add(new SqlParameter("@ClassName",oClassName.ToString()+"%"));104 }105 //部门ID
106 if (oDeptID != null && !string.IsNullOrEmpty(oDeptID.ToString()))107 {108 sOrder.Append("and DeptID=@DeptID");109 paras.Add(new SqlParameter("@DeptID",Convert.ToInt32(oDeptID)));110 }111
112 //上级部门PID
113 if (oDeptPID != null && !string.IsNullOrEmpty(oDeptPID.ToString()))114 {115 sOrder.Append("and DeptPID=@DeptPID");116 paras.Add(new SqlParameter("@DeptPID",Convert.ToInt32(oDeptPID)));117 }118
119 //种类Category
120 if (oCategory != null && !string.IsNullOrEmpty(oCategory.ToString()))121 {122 sOrder.Append("and Category=@Category");123 paras.Add(new SqlParameter("@Category",oCategory.ToString()));124 }125
126 //状态State
127 if (oState != null && !string.IsNullOrEmpty(oState.ToString()))128 {129 sOrder.Append("and [State]=@State");130 paras.Add(new SqlParameter("@State",oState.ToString()));131 }132
133 contString =sOrder.ToString();134
135 }