public static Nullable <int> GetCount(string where, int loginId) { StringBuilder sql = new StringBuilder(); sql.Append("select count(*) from ViewPolicy "); //if (where != string.Empty) // sql += "where 1=1 " + where; string ids = LoginDataLimitServices.GetLimit(loginId); if (string.IsNullOrEmpty(ids)) { sql.AppendFormat("where policy_makerId = {0} ", loginId); } else { sql.AppendFormat("where policy_makerId in ({0}) ", ids + "," + loginId); } if (string.IsNullOrEmpty(where)) { sql.Append(where); } object result = DBHelper.ExecuteScalar(sql.ToString()); if (result == null) { return(null); } else { return(Convert.ToInt32(result)); } }
/// <summary> /// 获取记录总数 /// </summary> /// <param name="where"></param> /// <returns></returns> public static int GetTotalCount(string where, int userID) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT COUNT(*) FROM To_Collecting "); string ids = LoginDataLimitServices.GetLimit(userID); if (string.IsNullOrEmpty(ids)) { sqlBuilder.AppendFormat(" WHERE markerID = {0}", userID); } else { sqlBuilder.AppendFormat(" WHERE markerID in ({0},{1})", ids, userID); } if (!string.IsNullOrEmpty(where)) { sqlBuilder.Append(where); } using (SqlConnection conn = new SqlConnection(DBHelper.connectionString)) { conn.Open(); SqlCommand sqlCmd = new SqlCommand(sqlBuilder.ToString(), conn); object result = sqlCmd.ExecuteScalar(); return(result == null ? 0 : int.Parse(result.ToString())); } }
public static DataTable GetListByPage(int?id, int loginId, string orderby, string strWhere, int startIndex, int endIndex) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT * FROM ( "); sql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { sql.Append("order by p." + orderby); } else { sql.Append("order by p.id desc"); } sql.Append(")AS Row,"); sql.Append("p.* from ViewPolicy as p "); StringBuilder where = new StringBuilder(); string ids = LoginDataLimitServices.GetLimit(loginId); if (string.IsNullOrEmpty(ids)) { where.AppendFormat("where policy_makerId = {0} ", loginId); } else { where.AppendFormat("where policy_makerId in ({0}) ", ids + "," + loginId); } if (id != null) { where.AppendFormat("and p.id={0}", id); } sql.Append(where); if (!string.IsNullOrEmpty(strWhere)) { sql.Append(strWhere); } sql.Append(" ) TT"); sql.AppendFormat(" WHERE TT.Row between {0} and {1} ", startIndex, endIndex); if (!string.IsNullOrEmpty(orderby.Trim())) { sql.Append("order by " + orderby + " "); } else { sql.Append("order by id desc "); } DataTable dt = DBHelper.GetDataSet(sql.ToString()); return(dt); }