public List <Partner> GetPartners(PartnerQuery param) { var WhereClause = string.Empty; var parameters = BuildCriteria(param, ref WhereClause); var dataTable = this.db.GetData("Select * from partner " + WhereClause + " order by partner_name ", parameters); if (dataTable == null) { return(null); } if (dataTable.Rows.Count == 0) { return(null); } var partners = new List <Partner>(); foreach (DataRow row in dataTable.Rows) { var obj = ConvertDataRowToPartner(row); partners.Add(obj); } return(partners); }
public List <Partner> GetPartnersWithPaging(PartnerQuery param) { var WhereClause = string.Empty; var parameters = BuildCriteria(param, ref WhereClause); var strSqlStatment = new StringBuilder(); strSqlStatment.Append("Select * from ( "); strSqlStatment.Append("select rownum as seq , main_data.* from ( "); strSqlStatment.Append("Select * from partner " + WhereClause + " order by partner_name "); strSqlStatment.Append(") main_data ) "); strSqlStatment.Append($"WHERE seq >= ({param.Paging.PageNo - 1}) * {param.Paging.PageSize} AND ROWNUM <= {param.Paging.PageSize}"); var dataTable = this.db.GetData(strSqlStatment.ToString(), parameters); if (dataTable == null) { return(null); } if (dataTable.Rows.Count == 0) { return(null); } var partners = new List <Partner>(); foreach (DataRow row in dataTable.Rows) { var obj = ConvertDataRowToPartner(row); partners.Add(obj); } return(partners); }
public int GetCount(PartnerQuery param) { string WhereClause = string.Empty; var parameters = BuildCriteria(param, ref WhereClause); var strSqlStatment = new StringBuilder(); strSqlStatment.Append($"Select count(*) val from partner { WhereClause }"); var count = this.db.GetIntScalarValue(strSqlStatment.ToString(), parameters); return(count); }
private List <OracleParameter> BuildCriteria(PartnerQuery param, ref string criteria) { var WhereClause = new StringBuilder(); var parameters = new List <OracleParameter>(); var permission = partnerActivity.GetPartAct("Partner.Query", param.QCreatorRoleId); if (permission == null || permission.Scope.Id == "CurOpOnly") { var parm = new OracleParameter { ParameterName = "QPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QCreatorId }; WhereClause.Append(" WHERE partner_id=:QPartnerId "); parameters.Add(parm); return(parameters); } if (!string.IsNullOrEmpty(param.QPartnerId)) { var parm = new OracleParameter { ParameterName = "QPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId }; WhereClause.Append(" WHERE partner_id=:QPartnerId "); parameters.Add(parm); } if (param.QAccount > 0) { WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE partner_acc=:QAccount " : " AND partner_acc=:QAccount "); var parm = new OracleParameter { ParameterName = "QAccount", OracleDbType = OracleDbType.Int32, Value = param.QAccount }; parameters.Add(parm); } if (!string.IsNullOrEmpty(param.QRefPartnerId)) { WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE ref_partner=:QRefPartnerId " : " AND ref_partner=:QRefPartnerId "); var parm = new OracleParameter { ParameterName = "QRefPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QRefPartnerId }; parameters.Add(parm); } if (param.QRoleId > 0) { WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE roleid=:QRoleId " : " AND roleid=:QRoleId "); var parm = new OracleParameter { ParameterName = "QRoleId", OracleDbType = OracleDbType.Int32, Value = param.QRoleId }; parameters.Add(parm); } if (param.QStatusId > 0) { WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE status=:QStatusId " : " AND status=:QStatusId "); var parm = new OracleParameter { ParameterName = "QStatusId", OracleDbType = OracleDbType.Int32, Value = param.QStatusId }; parameters.Add(parm); } if (!string.IsNullOrEmpty(param.QPartnerName)) { //param.QPartnerName = Utility.RemoveSpecialChar(param.QPartnerName); WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE (partner_name LIKE '%' || :QPartnerName || '%') " : " AND (partner_name LIKE '%' || :QPartnerName || '%') "); var parm = new OracleParameter { ParameterName = "QPartnerName", OracleDbType = OracleDbType.NVarchar2, Value = param.QPartnerName }; parameters.Add(parm); } if (permission.Scope.Id == "Exclusive") { var parm = new OracleParameter { ParameterName = "RefId", OracleDbType = OracleDbType.Varchar2, Value = param.QCreatorId }; WhereClause.Append(" WHERE ref_partner=:RefId "); parameters.Add(parm); } if (permission.Details == null || permission.Details.Count == 0) { WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE roleid=-1 " : " AND roleid=-1 "); } else { var allowedRoles = string.Join(",", permission.Details.Select(x => x.ToRole.Id).ToList()); WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? $" WHERE roleid in ( {allowedRoles} )" : $" AND roleid in ( {allowedRoles} )"); } criteria = WhereClause.ToString(); return(parameters); }
/// <summary> /// 获得列表数据 /// </summary> private DataSet GetData() { DataSet ds = new PartnerQuery().GetPartnerInfo(this.Name, this.Status, viewpage1.CurrentPageIndex, viewpage1.PageSize); return(ds); }