public static bool InsertIntoCache(int methodId, SQLContextNew sqlCtx) { lock (_cacheSyncObj) { if (!_cache.ContainsKey(methodId)) { _cache.Add(methodId, sqlCtx); return true; } else return false; } }
internal static bool GetFromCache(int methodId, ref SQLContextNew sqlCtx) { lock (_cacheSyncObj) { if (_cache.ContainsKey(methodId)) { sqlCtx = _cache[methodId]; return true; } else { sqlCtx = new SQLContextNew(); _cache.Add(methodId, sqlCtx); return false; } } }
private static bool PeerTheSQL_Inner(int methodId, out SQLContextNew sqlCntxt) { sqlCntxt = null; bool res1 = false; lock (_cacheSyncObj) { if (_cache.ContainsKey(methodId)) { sqlCntxt = new SQLContextNew(_cache[methodId]); res1 = true; } else { res1 = false; } } return res1; }
public static SQLContext ToOld(SQLContextNew item) { SQLContext ret = new SQLContext(); ret.Sentence = item.Sentence; item.FillIndexes(ret.Indexes); item.FillParams(ret.Params); return ret; }
public SQLContextNew ToNew() { SQLContextNew ret = new SQLContextNew(new SQLContextNew(this.Sentence, this.Params, this.Indexes)); return ret; }
public IList<ModelBOM> GetModelBOM(string material) { try { IList<ModelBOM> ret = new List<ModelBOM>(); MethodBase mthObj = MethodBase.GetCurrentMethod(); int tk = mthObj.MetadataToken; SQLContextNew sqlCtx = null; lock (mthObj) { if (!SQLCache.PeerTheSQL(tk, out sqlCtx)) { sqlCtx = new SQLContextNew(); sqlCtx.Sentence = @"with temp as ( select a.Material, a.Quantity, a.Component, a.Alternative_item_group, a.Flag, 1 as [Level], a.Cdt, a.Udt from ModelBOM a where Material= @Material union all select a1.Material, a1.Quantity, a1.Component, a1.Alternative_item_group, a1.Flag, b1.[Level]+1, a1.Cdt, a1.Udt from ModelBOM a1 inner join temp b1 on(a1.Material = b1.Component) ) select * from temp order by [Level]"; sqlCtx.AddParam("Material", new SqlParameter("@Material", SqlDbType.VarChar)); SQLCache.InsertIntoCache(tk, sqlCtx); } } sqlCtx.Param("Material").Value = material; using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params)) { while (sqlR != null && sqlR.Read()) { ret.Add(IMES.Infrastructure.Repository._Schema.SQLData.ToObjectByField<ModelBOM>(sqlR)); } } return ret; } catch (Exception) { throw; } }
public IList<string> GetAssetPartNo(string model, IList<string> astType) { try { IList<string> ret = new List<string>() ; MethodBase mthObj = MethodBase.GetCurrentMethod(); int tk = mthObj.MetadataToken; SQLContextNew sqlCtx = null; lock (mthObj) { if (!SQLCache.PeerTheSQL(tk, out sqlCtx)) { sqlCtx = new SQLContextNew(); sqlCtx.Sentence = @"select a.Component from ModelBOM a, Part b, @ASTType c where a.Component = b.PartNo and b.Descr = c.data and a.Material = @Material and a.Component like '2TG%' and b.Flag=1"; sqlCtx.AddParam("Material", new SqlParameter("@Material", SqlDbType.VarChar)); SqlParameter para1 = new SqlParameter("@ASTType", SqlDbType.Structured); para1.TypeName = "TbStringList"; para1.Direction = ParameterDirection.Input; sqlCtx.AddParam("ASTType", para1); SQLCache.InsertIntoCache(tk, sqlCtx); } } sqlCtx.Param("Material").Value = model; DataTable dt1 = IMES.Infrastructure.Repository._Schema.SQLData.ToDataTable(astType); sqlCtx.Param("ASTType").Value = dt1; using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader_OnTrans(_Schema.SqlHelper.ConnectionString_GetData, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params)) { while (sqlR != null && sqlR.Read()) { ret.Add(sqlR.GetString(0).Trim()); } } return ret; } catch (Exception) { throw; } }
public bool CheckIfExistProductPartWithBomForRCTO(string descr, string model, string productId) { try { bool ret = false; MethodBase mthObj = MethodBase.GetCurrentMethod(); int tk = mthObj.MetadataToken; SQLContextNew sqlCtx = null; lock (mthObj) { if (!SQLCache.PeerTheSQL(tk, out sqlCtx)) { sqlCtx = new SQLContextNew(); SQLCache.InsertIntoCache(tk, sqlCtx); sqlCtx.Sentence = "SELECT COUNT(1) FROM {0} a " + "INNER JOIN {1} b " + "ON a.{4}=b.{7} " + "AND b.{8}=@{8} " + "LEFT JOIN {2}..{3} c " + "ON b.{9}=c.{13} " + "AND c.{11}=@{11} " + "WHERE a.{5}=@{5} " + "AND c.{12} IS NULL " + "AND a.{6}=1 " + "AND b.{10}=1 "; sqlCtx.Sentence = string.Format(sqlCtx.Sentence, ToolsNew.GetTableName(typeof(ModelBOM_NEW)), ToolsNew.GetTableName(typeof(Part_NEW)), _Schema.SqlHelper.DB_FA, ToolsNew.GetTableName(typeof(_Metas.Product_Part)), ModelBOM_NEW.fn_component, ModelBOM_NEW.fn_material, ModelBOM_NEW.fn_flag, Part_NEW.fn_partNo, Part_NEW.fn_descr, Part_NEW.fn_partNo, Part_NEW.fn_flag, _Metas.Product_Part.fn_productID, _Metas.Product_Part.fn_partSn, _Metas.Product_Part.fn_partNo ); sqlCtx.AddParam(ModelBOM_NEW.fn_material, new SqlParameter("@" + ModelBOM_NEW.fn_material, ToolsNew.GetDBFieldType<ModelBOM_NEW>(ModelBOM_NEW.fn_material))); sqlCtx.AddParam(Part_NEW.fn_descr, new SqlParameter("@" + Part_NEW.fn_descr, ToolsNew.GetDBFieldType<Part_NEW>(Part_NEW.fn_descr))); sqlCtx.AddParam(_Metas.Product_Part.fn_productID, new SqlParameter("@" + _Metas.Product_Part.fn_productID, ToolsNew.GetDBFieldType<_Metas.Product_Part>(_Metas.Product_Part.fn_productID))); } } sqlCtx.Param(ModelBOM_NEW.fn_material).Value = model; sqlCtx.Param(Part_NEW.fn_descr).Value = descr; sqlCtx.Param(_Metas.Product_Part.fn_productID).Value = productId; using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params)) { if (sqlR != null && sqlR.Read()) { int cnt = g.GetValue_Int32(sqlR, 0); ret = cnt > 0 ? true : false; } } return ret; } catch (Exception) { throw; } }
public IList<string> GetPartNoInModelBOMByBomNodeTypeAndContainDescr(IList<string> modelList, string bomNodeType, string containBomDescr) { try { IList<string> ret = new List<string>(); MethodBase mthObj = MethodBase.GetCurrentMethod(); int tk = mthObj.MetadataToken; SQLContextNew sqlCtx = null; lock (mthObj) { if (!SQLCache.PeerTheSQL(tk, out sqlCtx)) { sqlCtx = new SQLContextNew(); sqlCtx.Sentence = @"select Distinct b.PartNo from ModelBOM a, Part b where a.Component = b.PartNo and a.Material in ('{0}') and b.BomNodeType =@BomNodeType and b.Descr like @Descr "; sqlCtx.AddParam("BomNodeType", new SqlParameter("@BomNodeType" , SqlDbType.VarChar)); sqlCtx.AddParam("Descr", new SqlParameter("@Descr", SqlDbType.VarChar )); SQLCache.InsertIntoCache(tk, sqlCtx); } } sqlCtx.Param("BomNodeType").Value = bomNodeType; sqlCtx.Param("Descr").Value = "%"+containBomDescr + "%"; string sentence = string.Format(sqlCtx.Sentence, string.Join("','", modelList.ToArray())); using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sentence, sqlCtx.Params)) { while (sqlR != null && sqlR.Read()) { string partsn = GetValue_Str(sqlR, 0); ret.Add(partsn); } } return ret; } catch (Exception) { throw; } }
public static bool PeerTheSQL(int methodId, out SQLContextNew sqlCntxt) { return PeerTheSQL_Inner(methodId, out sqlCntxt); }