/// <summary> /// 查询diamond /// </summary> /// <param name="diamondQueryItem"></param> /// <param name="localization">国际化标记</param> /// <returns></returns> public DataSet GetDiamondList(DiamondQueryItem queryItem, List <ScendingItem> scendingList, string localization) { SqlParameter[] paramList = GetDiamondWhere(queryItem, scendingList, DIAMOND_SELECT, localization, out sql); using (DataSet ds = SQLHelper.ExecuteDataset(DBConnStr, CommandType.Text, sql, paramList)) { try { return(ds); } catch (Exception ex) { throw ex; } } }
/// <summary> /// 增加where参数字符串 /// </summary> /// <param name="queryItem"></param> /// <param name="selectSql"></param> /// <param name="localization"></param> /// <param name="querySql"></param> /// <returns></returns> private SqlParameter[] GetDiamondWhere(DiamondQueryItem queryItem, List <ScendingItem> ScendingList, string selectSql, string localization, out string querySql) { StringBuilder sbWhere = new StringBuilder(); List <SqlParameter> sqlParamList = new List <SqlParameter>(); //List<SqlParameter> sqlParamList2 = new List<SqlParameter>(); //sqlParamList.Add(new SqlParameter("@TOTALROWS", SqlDbType.Int)); //sqlParamList.Add(new SqlParameter("@TOTALPAGES", SqlDbType.Int)); sqlParamList.Add(new SqlParameter("@LOCALIZATION", localization)); if (!string.IsNullOrEmpty(queryItem.ArticleNo)) { sbWhere.Append(" and DiamondNo like @DIAMONDNO"); sqlParamList.Add(new SqlParameter("@DIAMONDNO", "%" + queryItem.DiamondNo + "%")); } if (queryItem.CaratRangeList != null) { if (queryItem.CaratRangeList.Count > 0) { string caratWhere = string.Empty; for (int i = 0; i < queryItem.CaratRangeList.Count; i++) { caratWhere += "(Carat>=@CARATMIN" + i.ToString() + " and Carat<=@CARATMAX" + i.ToString() + ") or "; sqlParamList.Add(new SqlParameter("@CARATMIN" + i.ToString(), queryItem.CaratRangeList[i].CaratMin)); sqlParamList.Add(new SqlParameter("@CARATMAX" + i.ToString(), queryItem.CaratRangeList[i].CaratMax)); } caratWhere = caratWhere.Substring(0, caratWhere.Length - 4); sbWhere.Append(" and (" + caratWhere + ")"); } } if (queryItem.StoreIDList != null) { sbWhere.Append(" and a.storeid in (" + InsertParamList(queryItem.StoreIDList, "@STOREID", ref sqlParamList) + ")"); } if (queryItem.ShapeCodeList != null) { sbWhere.Append(" and ShapeCode in (" + InsertParamList(queryItem.ShapeCodeList, "@SHAPECODE", ref sqlParamList) + ")"); } if (queryItem.ColorCodeList != null) { sbWhere.Append(" and ColorCode in (" + InsertParamList(queryItem.ColorCodeList, "@COLORCODE", ref sqlParamList) + ")"); } if (queryItem.ClarityCodeList != null) { sbWhere.Append(" and ClarityCode in (" + InsertParamList(queryItem.ClarityCodeList, "@CLARITYCODE", ref sqlParamList) + ")"); } if (!string.IsNullOrEmpty(queryItem.StatusCode)) { sbWhere.Append(" and StatusCode=@STATUSCODE"); sqlParamList.Add(new SqlParameter("@STATUSCODE", queryItem.StatusCode)); } if (!string.IsNullOrEmpty(queryItem.PairID)) { sbWhere.Append(" and PairID like @PAIRID"); sqlParamList.Add(new SqlParameter("@PAIRID", "%" + queryItem.PairID + "%")); } if (!string.IsNullOrEmpty(queryItem.ArticleNo)) { sbWhere.Append(" and ArticleNo like @ARTICLENO"); sqlParamList.Add(new SqlParameter("@ARTICLENO", "%" + queryItem.ArticleNo + "%")); } if (queryItem.UploadDate != DateTime.MinValue) { sbWhere.Append(" and UploadDate>=@UploadDate"); sqlParamList.Add(new SqlParameter("@UploadDate", queryItem.UploadDate)); } sbWhere.Append(" order by"); foreach (var scendingItem in ScendingList) { sbWhere.Append(" " + scendingItem.ColumnName + " " + scendingItem.ScendingType.ToString()); } //sqlParamList.Add(new SqlParameter("@PAGESIZE", diamondSearch.PageSize)); //sqlParamList.Add(new SqlParameter("@PAGEINDEX", diamondSearch.PageIndex)); querySql = selectSql + sbWhere.ToString(); SqlParameter[] paramList = sqlParamList.ToArray(); return(paramList); }