/// <summary> /// SQL /// </summary> /// <param name="sqlString"></param> public WhereBuilder(string sqlString) { _whereString = ""; _fixFirstCondition = true; _sqlString = sqlString; _parameters = new DataAccessParameterCollection(); }
public static DataTable GetAgencyLike(string agencyName, string isEnable) { StringBuilder sql = new StringBuilder(); sql.Append("select * from Agency where 1=1"); if (agencyName.Trim() != "") { sql.Append(" and Agency_Name like @AgencyName "); } if (isEnable.Trim() != "") { sql.Append(" and IsEnable = @IsEnable "); } sql.Append(" order by Modify_Date desc"); Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@AgencyName", "%" + agencyName + "%"); dpc1.AddWithValue("@IsEnable", isEnable); DataSet ds = DataAccess.Select(sql.ToString(), dpc1); if (ds != null && ds.Tables.Count > 0) return ds.Tables[0]; return null; }
/// <summary> /// 根据实体生成关系为AND的条件 /// </summary> /// <param name="entity"></param> public WhereBuilder(EntityBase entity) { _sqlString = "SELECT * FROM " + entity.OringTableSchema.TableName; _whereString = ""; _fixFirstCondition = true; _parameters = new DataAccessParameterCollection(); AddAndCondition(entity); }
public static DataTable GetUsedProductSize(string subCastegory_id) { string strSql = @"SELECT DISTINCT Product.Product_Size_ID, Product.Product_Size FROM Product WHERE Product.SubCategory_ID=@SubCategory_ID ORDER BY Product.Product_Size"; Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@SubCategory_ID", subCastegory_id); DataSet ds = DataAccess.Select(strSql, dpc1); return ds.Tables[0]; }
public static DataTable Search(string actionNo, string province, string city, string defendant, string wholesaler, string dateFrom, string dateTo, string agencyID, string userID) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" select a.Action_NO,a.Sequence,a.Province,a.City,a.Defendant,a.Wholesaler_Manufacturer,a.PnG_Brand,a.Seizure,a.Raid_Date,a.Criminal_Date,a.Criminal_Detail_ID,b.agency,a.EditRecordID "); sbSql.Append(" from Criminal_Detail a inner join Criminal b on a.Action_NO=b.Action_NO"); sbSql.Append(" where 1=1 "); if (actionNo != "") { sbSql.Append(" and a.Action_NO like @Action_NO "); } if (province != "") { sbSql.Append(" and a.Province like @Province "); } if (city != "") { sbSql.Append(" and a.City like @City "); } if (defendant != "") { sbSql.Append(" and a.Defendant like @Defendant "); } if (wholesaler != "") { sbSql.Append(" and a.Wholesaler_Manufacturer like @Wholesaler_Manufacturer "); } if (dateFrom != "") { sbSql.Append(" and a.Criminal_Date >= @Raid_Date_From "); } if (dateTo != "") { sbSql.Append(" and a.Criminal_Date <= @Raid_Date_To "); } if (agencyID != "") { sbSql.Append(" and b.Agency_ID=@Agency_ID"); } if (userID != "") { sbSql.Append(" and b.User_ID=@User_ID"); } sbSql.Append(" order by a.Raid_Date desc "); Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@Action_NO", "%" + actionNo + "%"); dpc1.AddWithValue("@Province", "%" + province + "%"); dpc1.AddWithValue("@City", "%" + city + "%"); dpc1.AddWithValue("@Defendant", "%" + defendant + "%"); dpc1.AddWithValue("@Wholesaler_Manufacturer", "%" + wholesaler + "%"); dpc1.AddWithValue("@Raid_Date_From", dateFrom); dpc1.AddWithValue("@Raid_Date_To", dateTo); dpc1.AddWithValue("@Agency_ID", agencyID); dpc1.AddWithValue("@User_ID", userID); DataSet ds = DataAccess.Select(sbSql.ToString(), dpc1); if (ds != null && ds.Tables.Count > 0) return ds.Tables[0]; return null; }
public static DataSet GetAllStore(int page_index, int pageSize, string StoreID, string DMSID, string StoreName, string DIVISION, string MARKET, string RD_ID, string SOLD_TO_CODE, string SHIP_TO_CODE, string HUB_NAME, string STORE_SEQ, string BP_STORE_SEQ, string STORE_CITY, string CHANNEL_TYPE_CODE, string STORE_TYPE_CODE, string STORE_STATE, string ADRP_FLAG, string PROVINCE, string CITY, string VILLAGE, string TOWN, string STREET, string DOORPLATE, string ROOM) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" select * from Store where 1=1 "); #region 1 if (StoreID != "") { sbSql.Append(" and DMS_STORE_ID like @StoreID "); } if (DMSID != "") { sbSql.Append(" DMS_ID like @DMSID "); } if (StoreName != "") { sbSql.Append(" and STORE_NAME like @StoreName "); } if (DIVISION != "") { sbSql.Append(" and DIVISION like @DIVISION "); } if (MARKET != "") { sbSql.Append(" and MARKET like @MARKET "); } if (RD_ID != "") { sbSql.Append(" and RD_ID like @RD_ID "); } if (SOLD_TO_CODE != "") { sbSql.Append(" and SOLD_TO_CODE like @SOLD_TO_CODE "); } if (SHIP_TO_CODE != "") { sbSql.Append(" and SHIP_TO_CODE like @SHIP_TO_CODE "); } #endregion #region 2 if (HUB_NAME != "") { sbSql.Append(" and HUB_NAME like @HUB_NAME "); } if (STORE_SEQ != "") { sbSql.Append(" and STORE_SEQ like @STORE_SEQ "); } if (BP_STORE_SEQ != "") { sbSql.Append(" and BP_Store_SEQ like @BP_STORE_SEQ "); } if (STORE_CITY != "") { sbSql.Append(" and STORE_CITY like @STORE_CITY "); } if (CHANNEL_TYPE_CODE != "") { sbSql.Append(" and CHANNEL_TYPE_CODE like @CHANNEL_TYPE_CODE "); } if (STORE_TYPE_CODE != "") { sbSql.Append(" and STORE_TYPE_CODE like @STORE_TYPE_CODE "); } if (STORE_STATE != "") { sbSql.Append(" and STORE_STATE like @STORE_STATE "); } if (ADRP_FLAG != "") { sbSql.Append(" and ADRP_FLAG like @ADRP_FLAG "); } if (PROVINCE != "") { sbSql.Append(" and PROVINCE like @PROVINCE "); } if (CITY != "") { sbSql.Append(" and CITY like @CITY "); } #endregion #region 3 if (VILLAGE != "") { sbSql.Append(" and VILLAGE like @VILLAGE "); } if (TOWN != "") { sbSql.Append(" and TOWN like @TOWN "); } if (STREET != "") { sbSql.Append(" and STREET like @STREET "); } if (DOORPLATE != "") { sbSql.Append(" and DOORPLATE like @DOORPLATE "); } if (ROOM != "") { sbSql.Append(" and ROOM like @ROOM "); } #endregion StringBuilder sql = new StringBuilder(); sql.Append(GetRecordCount("Store_ID", sbSql.ToString())); sql.Append(";"); sql.Append(GetPagingSQL(sbSql.ToString(), pageSize, page_index, "CREATE_DATE", "desc")); Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@StoreID", "%" + StoreID + "%"); dpc1.AddWithValue("@DMSID", "%" + DMSID + "%"); dpc1.AddWithValue("@StoreName", "%" + StoreName + "%"); dpc1.AddWithValue("@DIVISION", "%" + DIVISION + "%"); dpc1.AddWithValue("@MARKET", "%" + MARKET + "%"); dpc1.AddWithValue("@RD_ID", "%" + RD_ID + "%"); dpc1.AddWithValue("@SOLD_TO_CODE", "%" + SOLD_TO_CODE + "%"); dpc1.AddWithValue("@SHIP_TO_CODE", "%" + SHIP_TO_CODE + "%"); dpc1.AddWithValue("@HUB_NAME", "%" + HUB_NAME + "%"); dpc1.AddWithValue("@STORE_SEQ", "%" + STORE_SEQ + "%"); dpc1.AddWithValue("@BP_STORE_SEQ", "%" + BP_STORE_SEQ + "%"); dpc1.AddWithValue("@STORE_CITY", "%" + STORE_CITY + "%"); dpc1.AddWithValue("@CHANNEL_TYPE_CODE", "%" + CHANNEL_TYPE_CODE + "%"); dpc1.AddWithValue("@STORE_TYPE_CODE", "%" + STORE_TYPE_CODE + "%"); dpc1.AddWithValue("@STORE_STATE", "%" + STORE_STATE + "%"); dpc1.AddWithValue("@ADRP_FLAG", "%" + ADRP_FLAG + "%"); dpc1.AddWithValue("@PROVINCE", "%" + PROVINCE + "%"); dpc1.AddWithValue("@CITY", "%" + CITY + "%"); dpc1.AddWithValue("@VILLAGE", "%" + VILLAGE + "%"); dpc1.AddWithValue("@TOWN", "%" + TOWN + "%"); dpc1.AddWithValue("@STREET", "%" + STREET + "%"); dpc1.AddWithValue("@DOORPLATE", "%" + DOORPLATE + "%"); dpc1.AddWithValue("@ROOM", "%" + ROOM + "%"); return DataAccess.Select(sql.ToString(), dpc1); }
/// <summary> /// SQL 与 参数 /// </summary> /// <param name="sqlString"></param> /// <param name="parameters"></param> public WhereBuilder(string sqlString, DataAccessParameterCollection parameters) : this(sqlString) { _parameters = parameters; }
public bool InsertCriminalRaid(CriminalDetailEntity criminalEntity, string agency,string agencyID, bool isInsert,string userName,string userID) { Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@actionNO", criminalEntity.ActionNo); dpc1.AddWithValue("@agency", agency); dpc1.AddWithValue("@AgencyID", agencyID); dpc1.AddWithValue("@User_ID", userID); dpc1.AddWithValue("@User_Name", userName); string strOtherSql = @" INSERT INTO Criminal (Action_NO,Agency,Conviction_Report_Upload_Date,Agency_ID,User_ID,User_Name) values (@actionNO,@agency,'" + DateTime.Now + "',@AgencyID,@User_ID,@User_Name)"; using (DataAccessBroker objBroker = DataAccessFactory.Instance()) { try { objBroker.BeginTransaction(); WhereBuilder wb = WhereBuilder.SelectAll(CriminalTable.Current.TableName); wb.AddAndCondition(CriminalTable.C_Action_NO, criminalEntity.ActionNo); //wb.AddAndCondition(CriminalTable.C_Agency, agency); //原系统逻辑 bool flag = DataAccess.Select<CriminalEntity>(wb).Count > 0; if (!flag) { objBroker.ExecuteSQL(strOtherSql, dpc1); } if (isInsert) { DataAccess.Insert(criminalEntity, objBroker); //Insert(criminalEntity, objBroker); } else { DataAccess.Update(criminalEntity, objBroker); } objBroker.Commit(); return true; } catch (Exception ex) { //WhereBuilder wb = WhereBuilder.SelectAll(CriminalTable.Current.TableName); //wb.AddAndCondition(CriminalTable.C_Action_NO, criminalEntity.ActionNO); //wb.AddAndCondition(CriminalTable.C_Agency, agency); //bool flag = DataAccess.Select<CriminalEntity>(wb).Count > 0; //if (flag) //{ // CriminalEntity criminalDelEntity = new CriminalEntity(); // criminalDelEntity.ActionNO = criminalEntity.ActionNO; // DataAccess.Delete(criminalDelEntity); //} objBroker.RollBack(); return false; } } }
public static DataSet GetAdministratorRoleInfoByUserID(string userID) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" select a.* from SEC_ROLE a inner join SEC_USER_ROLE b on a.ROLE_ID=b.ROLE_ID "); sbSql.Append(" where (a.EXTERNAL_USER_ROLE=@RoleType or a.EXTERNAL_USER_ROLE=@RoleType2) and b.USER_ID=@UserID "); Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@RoleType", RoleExternalUser.Administrator.GetHashCode()); dpc1.AddWithValue("@RoleType2", RoleExternalUser.SuperUser.GetHashCode()); dpc1.AddWithValue("@UserID", userID); return DataAccess.Select(sbSql.ToString(), dpc1); }
//Add by Andy.Gui on 2011/12/26 public static DataSet GetUserListWithAgency(int isDelete,string account, string chineseName,string agency) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" select a.*, b.Agency_ID,b.Agency_Name,b.IsEnable from SEC_USER a "); sbSql.Append(" left join Agency b on a.Agency_ID=b.Agency_ID "); sbSql.Append(" where " + SecUserTable.C_IS_DELETE + "=@IsDelete "); sbSql.Append(" and " + SecUserTable.C_USER_ACCOUNT + " like @Account "); sbSql.Append(" and " + SecUserTable.C_CHINESE_NAME + " like @ChineseName "); if (agency.Trim() != "") { sbSql.Append(" and a." + AgencyTable.C_Agency_ID + " =@Agency "); } sbSql.Append(" order by " + SecUserTable.C_USER_ACCOUNT); Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc1.AddWithValue("@IsDelete", isDelete); dpc1.AddWithValue("@Account", "%" + account + "%"); dpc1.AddWithValue("@ChineseName", "%" + chineseName + "%"); dpc1.AddWithValue("@Agency", agency.Trim()); return DataAccess.Select(sbSql.ToString(), dpc1); }
public bool SaveUpLoadFile(UploadfileEntity upLoadFileEntity) { string fileID = string.Empty; string actionNO = string.Empty; string target = string.Empty; string categoryID = string.Empty; string fileName = string.Empty; string fileType = string.Empty; string filePath = string.Empty; string uploadUserID = string.Empty; Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc.AddWithValue("@fileID", upLoadFileEntity.FileId); dpc.AddWithValue("@actionNO", upLoadFileEntity.ActionNo); dpc.AddWithValue("@target", upLoadFileEntity.Target); dpc.AddWithValue("@categoryID", upLoadFileEntity.CategoryId); dpc.AddWithValue("@fileName", upLoadFileEntity.Filename); dpc.AddWithValue("@fileType", upLoadFileEntity.Filetype); dpc.AddWithValue("@filePath", upLoadFileEntity.Filepath); dpc.AddWithValue("@uploadUserID", upLoadFileEntity.UploadUserId); string strSql = @" INSERT INTO UploadFile ( File_ID,Action_NO,Target,Category_ID,FileName,FileType,FilePath,Upload_User_ID,Upload_Date ) Values (@fileID ,@actionNO,@target,@categoryID,@fileName,@fileType,@filePath, @uploadUserID,'" + upLoadFileEntity.UploadDate + "' ) "; using (DataAccessBroker objBroker = DataAccessFactory.Instance()) { try { objBroker.ExecuteSQL(strSql, dpc); return true; } catch { return false; } } }