public T FindByid(string sql, string parmName, string parmValue) { Debug.Assert(!string.IsNullOrWhiteSpace(parmName)); Debug.Assert(!string.IsNullOrWhiteSpace(parmValue)); T result = default(T); iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) }; using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } result = t; } } return(result); }
public JsonResult savecoordinate(List <ReqRpt216coordinate> savelist) { try { //首先检查该RecipeName是否有坐标存在 string sql = string.Format("select count(1) as num from ISTRPT.RPT_WAT_Recipe_coordinate where RecipeName='{0}'", savelist.First().RECIPENAME); DB2Helper db2 = new DB2Helper(); db2.GetSomeData(sql); DataTable dt = db2.dt; if (Convert.ToInt32(dt.Rows[0][0]) > 0) { return(Json("exist")); } List <String> list = new List <string>(); String sql2 = ""; foreach (ReqRpt216coordinate o in savelist) { sql2 = string.Format("insert into ISTRPT.RPT_WAT_Recipe_coordinate(RECIPENAME,SITENAME,COORDINATE,CREATETIME,OWNER) values('{0}','{1}','{2}','{3}','{4}') ;", o.RECIPENAME, o.SITENAME, o.COORDINATEX + ',' + o.COORDINATEY, o.CREATETIME, o.OWNER); list.Add(sql2); } db2.UpdateBatchCommand(list); return(Json("success")); } catch (Exception) { return(Json("error")); } }
public List <T> FindAll(string sql) { List <T> list = new List <T>(); using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } return(list); }
private void DB2Oper() { DB2Helper dB2 = new DB2Helper(); dB2.GetSomeData(sql); foreach (DataRow dr in dB2.dt.Rows) { T t = new T(); // 获得此模型的公共属性 PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { if (dB2.dt.Columns.Contains(pi.Name)) { if (!pi.CanWrite) { continue; } object value = dr[pi.Name]; if (value != DBNull.Value) { pi.SetValue(t, value, null); } } } entities.EntityList.Add(t); } //部分更新的表需要设定最后的一条记录时间 if (entities.CompareTimeColumnName != "" && entities.EntityList.Count > 0) { T t = entities.EntityList.OrderBy(o => o.GetType().GetProperty(entities.CompareTimeColumnName)).LastOrDefault(); LastRecordTime = (DateTime)t.GetType().GetProperty(entities.CompareTimeColumnName).GetValue(t, null); } }
private void DB2Oper() { DB2Helper dB2 = new DB2Helper(); dB2.GetSomeData(sql); foreach (DataRow dr in dB2.dt.Rows) { T t = new T(); // 获得此模型的公共属性 PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { if (dB2.dt.Columns.Contains(pi.Name)) { if (!pi.CanWrite) { continue; } object value = dr[pi.Name]; if (value != DBNull.Value) { pi.SetValue(t, value, null); } } } entities.EntityList.Add(t); } }
//querycoordinate public JsonResult querycoordinate() { string RecipeName = Request["RecipeName"].ToString(); string sql = string.Format("select RecipeName,SiteName,Coordinate,CreateTime,Owner from ISTRPT.RPT_WAT_Recipe_coordinate where RecipeName='{0}'", RecipeName); DB2Helper db2 = new DB2Helper(); db2.GetSomeData(sql); DataTable dt = db2.dt; dt.Columns.Add("COORDINATEX", Type.GetType("System.String")); dt.Columns.Add("COORDINATEY", Type.GetType("System.String")); if (dt.Rows.Count != 0) { JsonSerializerSettings setting = new JsonSerializerSettings() { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }; //JavaScriptSerializer serialize = new JavaScriptSerializer(); string str = JsonConvert.SerializeObject(dt, setting); return(Json(str, JsonRequestBehavior.AllowGet)); } else { //return Json("该RecipeName没有Coordinate,请先添加再查询"); return(Json("nodata")); } }
public List <User> findAll() { string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" ORDER BY USERNAME"; using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, null)) { if (rdr.Read()) { List <User> list = new List <User>(); do { User user = new User(); user.userNo = rdr.GetInt32(0); user.username = rdr.GetString(1); user.password = rdr.GetString(2); list.Add(user); } while (rdr.Read()); return(list); } else { return(null); } } }
int GetCount(string eqp, string startTime) { DB2Helper db2 = new DB2Helper(); db2.GetSomeData(string.Format("select distinct proc_time from mmview.fhcdatahs_lot where proc_eqp_id='{0}' and proc_time >'{1}'", eqp, startTime)); return(db2.dt.Rows.Count); }
public ReqRpt025Translator() { DB2Helper db2 = new DB2Helper(); db2.GetSomeData("select max(Start_Time) from istrpt.RPT_RealTime_Lin"); LastRecordTime = (DateTime)db2.dt.Rows[0][0]; Translate(LastRecordTime, DateTime.Now); }
public List <Entidad.RecursosLiq> ConsultaRecursosLIQ(Entidad.RecursosLiqQueryinput Parametros) { var libreria = ConfigurationManager.AppSettings["Libreria"]; dtResultado = DB2Helper.ExecuteDb2Query(CommandType.StoredProcedure, "SP_CONSULTA_RECURSOSLIQ", Parametros); return(dtResultado.ToList <Entidad.RecursosLiq>()); }
public List <Entidad.Historial> ConsultaHistorial(Entidad.Historialinput Parametros) { var libreria = ConfigurationManager.AppSettings["Libreria"]; dtResultado = DB2Helper.ExecuteDb2Query(CommandType.StoredProcedure, "SP_CONSULTA_AUDITORIA_ORD_TRABAJO", Parametros); return(dtResultado.ToList <Entidad.Historial>()); }
public List <Entidad.ProveedoresLiq> ConsultaProveedores() { var libreria = ConfigurationManager.AppSettings["Libreria"]; var Resultado = DB2Helper.ExecuteDb2Query(CommandType.Text, "SELECT IDPROV, RAZCOMER FROM " + libreria + ".PROVEELIQCAB_WEB WHERE SESTRG != '*'", null); return(Resultado.ToList <Entidad.ProveedoresLiq>()); }
private InlineDBEntity GetLikelyEntity(InlineDBEntity entity) { var list = inlineDBEntities.Where(a => a.Lot == entity.Lot && a.WaferPosition == entity.WaferPosition && a.WaferSeq != "*" && a.ItemType != "Derived").OrderBy(p => (p.ClaimTime - entity.ClaimTime).Duration()); InlineDBEntity likelyEntity = list.FirstOrDefault(); //如果存在不一致的情况要记录 var testmax = list.Max(p => p.WaferSeq); var testmin = list.Min(p => p.WaferSeq); if (testmax != testmin) { LogHelper.InlineInfoLog(string.Format("testmax:{0},testmin:{1},lot:{2},claimtime:{3}", testmax, testmin, entity.Lot, entity.ClaimTime.ToString())); } //如果没找到,就到数据库中去找 if (likelyEntity is null) { string sqlt = string.Format("(select claim_time, wafer_seq,sourcelot,techbology, product,lotype,owner,measoperator,measequipment,measrecipe,procroute,procroutever,procstep,proctime,procoperatoruser,procequipment,procrecipe from istrpt.fvace_inline_dc where claim_time >= '{0}' and wafer_seq not in ('', '*') order by claim_time FETCH FIRST 1 ROWS ONLY) " + "union (select claim_time, wafer_seq,sourcelot,techbology, product,lotype,owner,measoperator,measequipment,measrecipe,procroute,procroutever,procstep,proctime,procoperatoruser,procequipment,procrecipe from istrpt.fvace_inline_dc where claim_time <= '{0}' and wafer_seq not in ('', '*') order by claim_time desc FETCH FIRST 1 ROWS ONLY)", entity.ClaimTime.ToString("yyyy-MM-dd-HH.mm.ss.ffffff")); DB2Helper dB2Helper = new DB2Helper(); dB2Helper.GetSomeData(sqlt); likelyEntity = new InlineDBEntity(); DataRow dr = dB2Helper.dt.NewRow(); if (dB2Helper.dt.Rows.Count == 2 && dB2Helper.dt.Rows[0][1].ToString() != dB2Helper.dt.Rows[1][1].ToString()) { dr = (DateTime)dB2Helper.dt.Rows[0][0] - entity.ClaimTime < entity.ClaimTime - (DateTime)dB2Helper.dt.Rows[1][0] ? dB2Helper.dt.Rows[0] : dB2Helper.dt.Rows[1]; } else if (dB2Helper.dt.Rows.Count > 0) { dr = dB2Helper.dt.Rows[0]; } likelyEntity.ClaimTime = (DateTime)dr[0]; likelyEntity.WaferSeq = dr[1].ToString(); likelyEntity.SourceLot = dr[2].ToString(); likelyEntity.Technology = dr[3].ToString(); likelyEntity.Product = dr[4].ToString(); likelyEntity.LotType = dr[5].ToString(); likelyEntity.Owner = dr[6].ToString(); likelyEntity.MeasOperator = dr[7].ToString(); likelyEntity.MeasEquipment = dr[8].ToString(); likelyEntity.MeasRecipe = dr[9].ToString(); likelyEntity.ProcRoute = dr[10].ToString(); likelyEntity.ProcRouteVer = dr[11].ToString(); likelyEntity.ProcStep = dr[12].ToString(); if (dr[13] is null) { likelyEntity.ProcTime = null; } else { likelyEntity.ProcTime = (DateTime)dr[13]; } likelyEntity.ProcOperatorUser = dr[14].ToString(); likelyEntity.ProcRecipe = dr[15].ToString(); } return(likelyEntity); }
public void GetSequenceTest() { long id; using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString)) { id = DB2Helper.GetSequence(conn, "USER"); } Assert.AreNotEqual(id, 0); }
public ReqRpt030DeleteSpecialLotResultModel(string LotID) { LotID = LotID.Replace("'", ""); LotID = LotID.Replace("\"", ""); List <string> list = LotID.Split(',').ToList(); string sql = string.Format("delete from ISTRPT.RPT_WIP_SPECIAL_LOT where LOT_ID in ('{0}')", string.Join("','", list)); DB2Helper db2 = new DB2Helper(); db2.GetSomeData(sql); }
public string AccionesDetalle(Entidad.PlanificacionDetalleInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPJNTAOPED", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public ReqRpt030MainViewModel() { DB2Helper db2 = new DB2Helper(); db2.GetSomeData("select lot_id from istrpt.rpt_wip_special_lot"); foreach (DataRow dr in db2.dt.Rows) { Lot_ID.Add(dr[0].ToString()); } }
public string Programar(Entidad.ProgramarInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_PROGRAMAR_ORDEN_TRABAJO_WEB_TODOS", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string AccionesModulos(ModulosInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPMODULOS", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string AccionesEnvioAlert(Entidad.EnvioAlertaQueryInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_ACCIONES_ENVIO_ALERT", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string AccionesAccesos(AccesoInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPACCESOUSUARIO", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public static void Run() { string[] sql = { @"delete from istrpt.rpt_click_count_history where date=to_char(current date, 'yyyy-MM-dd')", @"insert into istrpt.rpt_click_count_history ( select privilegeid, usage_counter, to_char(current date, 'yyyy-MM-dd'), current timestamp from istrpt.rptfuncusage )" }; DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(sql.ToList()); }
public string Acciones(Entidad.CitaDPWQueryInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_ACCIONES_CITADPW2", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string ActualizaRCE(Entidad.ActRCEQueryInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_UPD_RCE", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string FalsoServicio(Entidad.FalsoServicioInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_FALSO_SERVICIO_OT_TODOS", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string LiquidarServicio(Entidad.LiquidarServicioInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_LIQUIDAR_ORD_TRABAJO_WEB_TODOS", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
public string AddRecurso(Entidad.RecursosLiqInput Parametros) { var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_CREAR_RECURSO_LIQ_OT_TODOS", Parametros); if (Resultado == null) { Resultado = "OK"; } return(Resultado.ToString()); }
private void Initialize() { string sql = string.Format("select Priority_Class from mmview.fhopehs where lot_id='{0}' order by Claim_Time desc fetch first 1 rows only", LotID); var db2 = new DB2Helper(); db2.GetSomeData(sql); Priority = (int)db2.dt.DefaultView[0][0]; GetDb2Datas(); SetHistEntities(); SetForcastEntities(); SetQtValue(); }
public List <T> nativeQuerySql(string sql, IDictionary <string, object> parms) { iDB2Parameter[] db2Parms = null; List <T> list = new List <T>(); if (parms != null) { List <iDB2Parameter> parmsList = new List <iDB2Parameter>(); foreach (var item in parms) { Debug.Assert(item.Value != null); parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value)); } db2Parms = parmsList.ToArray(); } try { using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } } catch (Exception ex) { log.Error("SQL错误:" + ex.Message); log.Error("SQL错误:" + sql); if (parms != null) { log.Error("SQL错误:" + parms.toJson()); } throw ex; } return(list); }
public void delete(string username) { string sql = "DELETE FROM \"USER\" WHERE USERNAME=@USERNAME"; using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString)) { conn.Open(); iDB2Parameter[] parms = new iDB2Parameter[] { new iDB2Parameter("@USERNAME", username) }; DB2Helper.ExecuteNonQuery(conn, CommandType.Text, sql, parms); } }