public WhereBuilder ParseSQL() { string sql = "Select mtb.*,tmptb1." + MstProvinceTable.C_PROVINCE_NAME_CN + " From " + MstCityTable.C_TableName + " mtb "; sql += " Left Join (Select " + MstProvinceTable.C_PROVINCE_ID + "," + MstProvinceTable.C_PROVINCE_NAME_CN + " From " + MstProvinceTable.C_TableName + ") tmptb1 on tmptb1." + MstProvinceTable.C_PROVINCE_ID + "=mtb." + MstCityTable.C_PROVINCE_ID + " "; WhereBuilder wb = new WhereBuilder(sql); wb.FixFirstCondition = true; if (!string.IsNullOrEmpty(CityNameCN)) { wb.AddAndCondition("mtb", MstCityTable.C_CITY_NAME_CN, SQLOperator.Like, CityNameCN); } if (ContainsKey(MstCityTable.C_CITY_NAME_EN) && !string.IsNullOrEmpty(CityNameEn)) { wb.AddAndCondition("mtb", MstCityTable.C_CITY_NAME_EN, SQLOperator.Like, CityNameEn); } if (!string.IsNullOrEmpty(CityShortName)) { wb.AddAndCondition("mtb", MstCityTable.C_SHORT_NAME, SQLOperator.Like, CityShortName); } if (!string.IsNullOrEmpty(CityProvinceID)) { wb.AddAndCondition("mtb", MstCityTable.C_PROVINCE_ID, SQLOperator.Equal, CityProvinceID); } if (!string.IsNullOrEmpty(CityType)) { wb.AddAndCondition("mtb", MstCityTable.C_CITY_TYPE, SQLOperator.Equal, CityType); } //wb.AddCondition(" Order By " + MstCityTable.C_CITY_NAME_EN + " "); return wb; }
//For test public WhereBuilder ParseSQL() { StringBuilder sb = new StringBuilder(); sb.Append("Select * from ").Append(LogErrorTable.C_TableName).Append(" e "); WhereBuilder wb = new WhereBuilder(sb.ToString()); wb.FixFirstCondition = true; wb.AddAndCondition("1", "1"); if (ContainsKey(LogErrorTable.C_IP) && !string.IsNullOrEmpty(Ip)) { wb.AddAndCondition("e", LogErrorTable.C_IP, SQLOperator.Like, Ip); } if (ContainsKey(LogErrorTable.C_LOG_ERROR_CATEGORY) && !string.IsNullOrEmpty(LogErrorCategory)) { wb.AddAndCondition("e", LogErrorTable.C_LOG_ERROR_CATEGORY, SQLOperator.Like, LogErrorCategory); } if (ContainsKey(LogErrorTable.C_MACHINE_NAME) && !string.IsNullOrEmpty(MachineName)) { wb.AddAndCondition("e", LogErrorTable.C_MACHINE_NAME, SQLOperator.Like, MachineName); } //if (DateFrom != null) //{ // wb.AddAndCondition("e", LogErrorTable.C_LOG_TIME, SQLOperator.GreaterEquals, DateFrom); //} //if (DateTo != null) //{ // wb.AddAndCondition("e", LogErrorTable.C_LOG_TIME, SQLOperator.LessEquals, DateTo); //} ////根据LogTime来排序 string sqlExtends = " order by e." + LogErrorTable.C_LOG_TIME + " desc"; wb.AddCondition(sqlExtends); return wb; }
public WhereBuilder ParseSQL() { StringBuilder sb = new StringBuilder(); sb.Append("Select p.*,c.").Append(SecPageTable.C_PAGE_NAME) .Append(" from ").Append(SecPermissionTable.C_TableName).Append(" p ") .Append(" inner join ").Append(SecPageTable.C_TableName).Append(" c on p.") .Append(SecPermissionTable.C_PAGE_ID).Append("=c.").Append(SecPageTable.C_PAGE_ID); WhereBuilder wb = new WhereBuilder(sb.ToString()); wb.FixFirstCondition = true; if (ContainsKey(SecPermissionTable.C_CONTROL_ID) && !string.IsNullOrEmpty(ControlId)) { wb.AddAndCondition("p", SecPermissionTable.C_CONTROL_ID, SQLOperator.Like, ControlId); } if (ContainsKey(SecPermissionTable.C_PERMISSION_NAME) && !string.IsNullOrEmpty(PermissionName)) { wb.AddAndCondition("p", SecPermissionTable.C_PERMISSION_NAME, SQLOperator.Like, PermissionName); } if (ContainsKey(SecPermissionTable.C_PAGE_ID) && !string.IsNullOrEmpty(PageId)) { wb.AddAndCondition("p", SecPermissionTable.C_PAGE_ID, SQLOperator.Equal, PageId); } return wb; }
public WhereBuilder ParseSQL() { StringBuilder sb = new StringBuilder(); sb.Append("Select * from ").Append(LogOperateTable.C_TableName).Append(" o "); WhereBuilder wb = new WhereBuilder(sb.ToString()); wb.FixFirstCondition = true; wb.AddAndCondition("1", "1"); if (ContainsKey(LogOperateTable.C_OPERATE_TYPE) && !string.IsNullOrEmpty(OperateType)) { wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TYPE, SQLOperator.Equal, OperateType); } if (ContainsKey(LogOperateTable.C_PAGE_NAME) && !string.IsNullOrEmpty(PageName)) { wb.AddAndCondition("o", LogOperateTable.C_PAGE_NAME, SQLOperator.Like, PageName); } if (DateFrom != null) { wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TIME, SQLOperator.GreaterEquals, DateFrom); } if (DateTo != null) { wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TIME, SQLOperator.LessEquals, DateTo); } ////根据OperateTime来排序 string sqlExtends = " order by o." + LogOperateTable.C_OPERATE_TIME + " desc"; wb.AddCondition(sqlExtends); return wb; }
public DataTable Query() { string strSql = @"Select * From V_PriceRecord"; WhereBuilder wb = new WhereBuilder(strSql); wb.AddAndCondition("1", "1"); return DataAccess.SelectDataSet(wb).Tables[0]; }
public WhereBuilder ParseSQL() { WhereBuilder wb; string sql = " select user_account from SEC_USER A " + " left join SEC_USER_ROLE B on A.USER_ID=B.USER_ID "+ " where B.ROLE_ID in (select ROLE_ID from MST_CATEGORY C "+ " left join MST_BRAND D on D.CATEGORY_ID=C.CATEGORY_ID "+ " left join MST_ROLE_BRAND E on E.BRAND_ID=D.BRAND_ID where C.CATEGORY_ID='" +CategoryId.Trim()+"' and D.BRAND_ID='"+BrandId.Trim()+"'"+ ")"; wb = new WhereBuilder(sql); return wb; }
public WhereBuilder ParseSQL() { WhereBuilder wb; string sql = @" select sum(a.total_days) totalday,sum(a.use_days) useday,sum(a.space_days) spaceday from atd_vaca_result a where a.person_id='" + PersonId + @"' and sysdate between to_date(a.use_date,'yyyy-mm-dd') and to_date(a.disable_date,'yyyy-mm-dd') and (a.belong_year=to_char(sysdate,'yyyy') and to_number(a.belong_month)<=to_number(to_char(sysdate,'mm'))) "; sql += " order by a.disable_date"; wb = new WhereBuilder(sql); return wb; }
/// <summary> /// 返回WhereBuilder,注意排序的用法 /// </summary> /// <returns></returns> public WhereBuilder ParseSQL() { string sql = @"select sum(quantity) as qty,sum(TotalValueUSD),action_no as usd from Fake_Product e"; WhereBuilder wb = new WhereBuilder(sql); // wb.FixFirstCondition = true; wb.AddCondition(" Where 1>0 "); if (!string.IsNullOrEmpty(Action_no)) { wb.AddAndCondition("e", FakeProductTable.C_Action_NO, SQLOperator.Equal, Action_no); } wb.AddCondition(" group by action_no "); return wb; }
public virtual DataSet Search(string brand_ID, string brandType_ID, string productSize_ID, string subCatrgory_ID) { string strSql = @"Select * From CaseCalculation_View"; WhereBuilder wb = new WhereBuilder(strSql); wb.AddAndCondition("1", "1"); if (!string.IsNullOrEmpty(brand_ID)) wb.AddAndCondition(CasecalculationTable.C_Brand_ID, brand_ID); if (!string.IsNullOrEmpty(brandType_ID)) wb.AddAndCondition(CasecalculationTable.C_BrandType_ID, brandType_ID); if (!string.IsNullOrEmpty(productSize_ID)) wb.AddAndCondition(CasecalculationTable.C_Product_Size_ID, productSize_ID); if (!string.IsNullOrEmpty(subCatrgory_ID)) wb.AddAndCondition(CasecalculationTable.C_SubCategory_ID, subCatrgory_ID); return DataAccess.SelectDataSet(wb); }
/// <summary> /// 根据roleType查出List<SecRoleEntity>, Add by Alex on 2010.3.20 /// </summary> /// <param name="roleType"></param> /// <returns></returns> public static List<SecRoleEntity> GetRoleEntityByRoleType(string roleType) { List<SecRoleEntity> roleEntityList = new List<SecRoleEntity>(); string sql = "select * from " + SecRoleTable.C_TableName; WhereBuilder wb = new WhereBuilder(sql); wb.FixFirstCondition = true; wb.AddAndCondition("1", "1"); wb.AddAndCondition(SecRoleTable.C_TableName, SecRoleTable.C_ROLE_TYPE, SQLOperator.Equal, roleType); //角色种类,1为业务角色 wb.AddAndCondition(SecRoleTable.C_TableName, SecRoleTable.C_ROLE_CATEGORY, SQLOperator.Equal, 1); //根据RoleName来排序 string sqlExtends = " order by " + SecRoleTable.C_TableName + "." + SecRoleTable.C_ROLE_NAME; wb.AddCondition(sqlExtends); roleEntityList = DataAccess.Select<SecRoleEntity>(wb); return roleEntityList; }
/// <summary> /// 返回WhereBuilder,注意排序的用法 /// </summary> /// <returns></returns> public WhereBuilder ParseSQL() { string sql = @"select a.no , (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=33) as KYQD, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=34) as XDBG, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=35) as XHZM, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=36) as CFJDS, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=37) as XRZL, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=38) as JDBG, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=39) as XSAJZJBG, (select count(*) from UploadFile b where b.action_no=a.no and b.category_id=40) as XSPJS from dbo.Normal_Raid a "; WhereBuilder wb = new WhereBuilder(sql); // wb.FixFirstCondition = true; wb.AddCondition(" Where 1>0 " + " and a.I_or_Raid<>'Investigation' "); //2012-7-18 jane modified 在upload report中,不应该出现investigation的数据 if (!string.IsNullOrEmpty(Action_no)) { wb.AddAndCondition("a", NormalRaidTable.C_NO, SQLOperator.Like, Action_no); } if (!string.IsNullOrEmpty(Agency_ID)) { wb.AddAndCondition("a", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency_ID); } wb.AddCondition(" Order by " + NormalRaidTable.C_Action_Date + " desc"); return wb; }
public DataSet FillSQLDataSet(WhereBuilder wb) { return FillSQLDataSet(wb.SQLString, wb.Parameters); }
public IDataReader ExecuteReader(WhereBuilder wb) { return ExecuteReader(wb.SQLString, wb.Parameters, CommandType.Text); }
public object ExecuteScalar(WhereBuilder wb) { return ExecuteScalar(wb.SQLString, wb.Parameters, CommandType.Text); }
public int ExecuteNonQuery(WhereBuilder wb) { return ExecuteNonQuery(wb.SQLString, wb.Parameters, CommandType.Text); }
/// <summary> /// 返回WhereBuilder,注意排序的用法 /// </summary> /// <returns></returns> public WhereBuilder ParseSQL() { string sql = @"select * from dbo.MainPage_View e"; WhereBuilder wb = new WhereBuilder(sql); // wb.FixFirstCondition = true; wb.AddCondition(" Where 1>0 "); if (!string.IsNullOrEmpty(Action_no)) { wb.AddAndCondition("e", NormalRaidTable.C_NO, SQLOperator.Like, Action_no); } if (!string.IsNullOrEmpty(Begindate)) { wb.AddCondition(" and e." + NormalRaidTable.C_Action_Date + " >='" + Begindate + "'"); } if (!string.IsNullOrEmpty(Enddate)) { wb.AddCondition(" and e." + NormalRaidTable.C_Action_Date + " <='" + Enddate + "'"); } if (!string.IsNullOrEmpty(Province)) { wb.AddAndCondition("e", NormalRaidTable.C_Province, SQLOperator.Equal, Province); } if (!string.IsNullOrEmpty(City)) { wb.AddAndCondition("e", NormalRaidTable.C_City, SQLOperator.Equal, City); } if (!string.IsNullOrEmpty(Status)) { wb.AddAndCondition("e", "actionstatus", SQLOperator.Equal, Status); } if (!string.IsNullOrEmpty(Type)) { wb.AddAndCondition("e", "type", SQLOperator.Equal, Type); } if (!string.IsNullOrEmpty(Agency)) { wb.AddAndCondition("e", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency); } if (!string.IsNullOrEmpty(User)) { wb.AddAndCondition("e", NormalRaidTable.C_User_ID, SQLOperator.Equal, User); } if (!string.IsNullOrEmpty(RequestStatus)) { if (RequestStatus.Equals("Applying")) { wb.AddCondition(" and (e." + NormalRaidTable.C_EditRecordID + " is not null and e.EditRecordID <>'') "); } else { string sql2 = ""; string s = ""; if (!string.IsNullOrEmpty(RequestEditRecordBLL.CurrentUser.SecUserEntity.AgencyId)) { s = string.Format(" and rer.RequestAgencyID='{0}' ", RequestEditRecordBLL.CurrentUser.SecUserEntity.AgencyId); } if (RequestStatus.Equals("Approved")) { sql2 = @" and exists ( select s.IsApproved from RequestEditRecord s inner join ( select rer.[type],rer.Action_NO,rer.RequestAgencyID,MAX(rer.requesttime) as requesttime from RequestEditRecord rer where e.[type]=rer.[Type] and e.id=rer.Action_NO {0} --and rer.RequestAgencyID ='' group by rer.[type],rer.Action_NO,rer.RequestAgencyID ) As A on s.Action_NO =a.Action_NO and s.RequestAgencyID = a.RequestAgencyID and s.[type]=a.[Type] and s.RequestTime=a.requesttime where s.IsApproved=1 )"; } if (RequestStatus.Equals("Rejected")) { sql2 = @" and exists ( select s.IsApproved from RequestEditRecord s inner join ( select rer.[type],rer.Action_NO,rer.RequestAgencyID,MAX(rer.requesttime) as requesttime from RequestEditRecord rer where e.[type]=rer.[Type] and e.id=rer.Action_NO {0} --and rer.RequestAgencyID ='' group by rer.[type],rer.Action_NO,rer.RequestAgencyID ) As A on s.Action_NO =a.Action_NO and s.RequestAgencyID = a.RequestAgencyID and s.[type]=a.[Type] and s.RequestTime=a.requesttime where s.IsApproved=0 )"; } wb.AddCondition(string.Format(sql2, s)); } } wb.AddCondition(" Order by e." + NormalRaidTable.C_EditRecordID + " desc,e." + NormalRaidTable.C_Action_Date + " Desc"); return wb; }
/// <summary> /// 返回WhereBuilder,注意排序的用法 /// </summary> /// <returns></returns> public WhereBuilder ParseSQL() { string sql = @" select e.* from ( select a.* ,b.province,b.city,b.agency,b.agency_id,null as Defendant,b.User_ID,b.User_Name from UploadFile a left join Normal_Raid b on a.action_no=b.no and a.target='Upload_File_For_Raid' where a.target='Upload_File_For_Raid' union all select a.* ,b.province,b.city,d.agency,d.agency_id,b.defendant,d.User_ID,d.User_Name from UploadFile a left join Criminal_Detail b on a.action_no=b.action_no and a.target='Upload_File_For_Criminal' left join Criminal d on b.action_no=d.action_no where a.target='Upload_File_For_Criminal') e"; WhereBuilder wb = new WhereBuilder(sql); // wb.FixFirstCondition = true; wb.AddCondition(" Where 1>0 "); if (!string.IsNullOrEmpty(Action_no)) { wb.AddAndCondition("e", CriminalDetailTable.C_Action_NO, SQLOperator.Like, Action_no); } if (!string.IsNullOrEmpty(Begindate)) { wb.AddCondition(" and e." + UploadfileTable.C_Upload_Date+" >='"+Begindate+"'"); } if (!string.IsNullOrEmpty(Enddate)) { wb.AddCondition(" and e." + UploadfileTable.C_Upload_Date + " <='" + Enddate + "'"); } if (!string.IsNullOrEmpty(Province)) { wb.AddAndCondition("e", NormalRaidTable.C_Province, SQLOperator.Equal, Province); } if (!string.IsNullOrEmpty(City)) { wb.AddAndCondition("e", NormalRaidTable.C_City, SQLOperator.Equal, City); } if (!string.IsNullOrEmpty(Agency)) { wb.AddAndCondition("e", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency); } if (!string.IsNullOrEmpty(Type)) { wb.AddAndCondition("e", UploadfileTable.C_Category_ID, SQLOperator.Equal, Type); } if (!string.IsNullOrEmpty(Defendant)) { wb.AddAndCondition("e", CriminalDetailTable.C_Defendant, SQLOperator.Like, Defendant); } if (!string.IsNullOrEmpty(User)) { wb.AddAndCondition("e", NormalRaidTable.C_User_ID, SQLOperator.Equal, User); } wb.AddCondition(" Order by e." + UploadfileTable.C_Upload_Date); return wb; }