/// <summary> /// 得到List集合 只支持有序的字段(max,min函数支持的字段) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sqlWhere"></param> /// <param name="page"></param> /// <param name="pageSize"></param> /// <param name="orderBy">默认是主键(不是代码写的默认值"")</param> /// <param name="desc"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <T> GetListByPage <T>(ref int totalCount, string sqlWhere = "", int page = 1, int pageSize = 20, string orderBy = "", bool desc = true) where T : new() { DealSqlWhere(ref sqlWhere); var type = typeof(T); TblStruct st = type.GetTblStruct(); if (string.IsNullOrEmpty(orderBy)) { orderBy = st.PrimaryKey; } var skipCount = (pageSize * (page - 1)); var maxMin = string.Empty; if (skipCount > 0) { //string sqlServerIsNull = "ISNULL(" + (desc ? "MIN" : "MAX") + "(" + orderBy + "),0)"; string oracleIsNull = "iif(IsNull(" + (desc ? "MIN" : "MAX") + "(" + orderBy + ")), 0, " + (desc ? "MIN" : "MAX") + "(" + orderBy + "))"; maxMin = " and " + orderBy + (desc ? "<" : ">") + "(select " + oracleIsNull + " from (select top " + skipCount + " " + orderBy + " from " + st.TblName + " order by " + orderBy + (desc ? " desc" : " asc") + "))"; } string sql = "select top " + pageSize + " " + st.SqlSearchCol + " from " + st.TblName + " where " + sqlWhere + maxMin + " order by " + orderBy + (desc ? " desc" : " asc"); if (totalCount == -1) { totalCount = 0; } else { string sqlTotalCount = "select COUNT(1) from " + st.TblName + " where " + sqlWhere; var obj = DbHelperAccess.GetScalar(sqlTotalCount); if (obj == null) { totalCount = 0; } else { totalCount = Convert.ToInt32(obj); } } return(GetListBySql <T>(sql)); }
/// <summary> /// 主键是自增,则返回主键ID /// 不是,则返回影响行数 /// 默认一般是>0就是成功 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public static int Add <T>(T t) { lock (_sycn) { var type = typeof(T); var st = type.GetTblStruct(); string insertCol = string.Empty; string values = string.Empty; foreach (var item in st.ListCol) { if (st.Identity) //自增的主键,主键就不赋值 { if (item.PrimaryKey) { continue; } } if (item.NotAdd) { continue; } insertCol += item.ColName + ","; var p = type.GetProperty(item.ClassColName); var obj = p.GetValue(t, null); if (obj == null) { values += "NULL,"; } else { values += "'" + Convert.ToString(obj).SqlClear() + "',"; } } if (insertCol.Length > 0) { insertCol = insertCol.Remove(insertCol.Length - 1); } if (values.Length > 0) { values = values.Remove(values.Length - 1); } string sql = "insert into " + st.TblName + "(" + insertCol + ") values(" + values + ");"; if (st.Identity) { var count2 = DbHelperAccess.ExecuteCommand(sql); if (count2 > 0) { sql = "select @@identity;"; object obj = DbHelperAccess.GetScalar(sql); if (obj == null) { return(0); } var id = Convert.ToInt32(obj); var p = type.GetProperty(st.ClassPrimaryKey); p.SetValue(t, id, null); return(id); } return(0); } var count = DbHelperAccess.ExecuteCommand(sql); return(count); } }