//public string ExecuteNonQuery(string lstSql,params SqlParameter[] parameters) //{ // string strReturn = ""; // DbConnection conn = null; // DbTransaction trans = null; // try // { // conn = db.CreateConnection(); // conn.Open(); // trans = conn.BeginTransaction(); // DbCommand command = null; // command = conn.CreateCommand(); // command.Transaction = trans; // command.CommandText = lstSql; // command.CommandType = CommandType.Text; // command.Parameters.Add(parameters); // command.ExecuteNonQuery(); // trans.Commit(); // } // catch (Exception er) // { // strReturn = er.Message; // trans.Rollback(); // } // finally // { // conn.Close(); // } // return strReturn; //} public static DatabaseEx getDbInstance() { if (dbDatabaseEx == null) { dbDatabaseEx = new DatabaseEx(); } return(dbDatabaseEx); }
private void FrmJYNYDataUpload_Load(object sender, EventArgs e) { timer1.Enabled = false; bool blFound = false; string hostInfo = Dns.GetHostName(); System.Net.IPAddress[] addressList = Dns.GetHostEntry(Dns.GetHostName()).AddressList; //IPAddress ipaddress = addressList[0]; //string ips = ipaddress.ToString(); //MessageBox.Show(ips); //获取配置节信息 this._section = (JYNYTestConfigurationSection)ConfigurationManager.GetSection("mes.JYNYtest"); //增加线程个数。 foreach (JYNYTestDeviceElement element in this._section.Devices) { foreach (IPAddress ipAddress in addressList) { strIP = ipAddress.ToString(); if (strIP == element.Name) { blFound = true; _deviceElement = element; break; } } } if (blFound == false) { MessageBox.Show("此电脑的IP地址没有在配置文件中找到对应的配置,请联系IT"); Application.Exit(); } else { txtFilePath.Text = CommonFun.GetFullFile(_deviceElement.Path, _deviceElement.Format); //txtFilePath.Text = _deviceElement.Path; } string strSql = " select top 1 * from [dbo].[ZWIP_VIR_TEST] where EQUIPMENT_CODE='" + _deviceElement.EqpName + "' order by TEST_TIME desc"; DataTable dt = DatabaseEx.getDbInstance().getDataTable(strSql); if (dt != null && dt.Rows.Count > 0) { txtTestTime.Text = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dt.Rows[0]["TEST_TIME"].ToString()); } else { txtTestTime.Text = "1900-01-01 01:01:01"; } txtEqpName.Text = _deviceElement.EqpName; btnStart.Enabled = true; btnPause.Enabled = false; txtEqpName.ReadOnly = true; txtTestTime.ReadOnly = true; //btnStart_Click(null, null); notifyJYNYData.Visible = false; if (CommonFun.CheckFileExists(txtFilePath.Text.Trim()) == false) { MessageBox.Show(string.Format("{0}不存在.", txtFilePath)); return; } strAccessConnection = string.Format(_deviceElement.ConnectionString, txtFilePath.Text.Trim()); dataTransferAction = new JYNYTestDataTransferAction(strAccessConnection); dataTransferAction.OnDataTransferFinished += new EventHandler <DataTransferFinishedArgs>(DataTransferFinished); int nSecond = 0; if (int.TryParse(txtInterval.Text, out nSecond) == true) { if (nSecond < 10) { MessageBox.Show("时间设置不能小于(10)秒"); return; } } timer1.Interval = nSecond * 1000; timer1.Enabled = true; StartTransfer(); }
/// <summary> /// 将数据从Access数据库转移到MES数据库。 /// </summary> /// <param name="element">设备配置。</param> /// <returns>true:转置成功。false:转置失败。</returns> public void Execute(JYNYTestDeviceElement element, System.DateTime MaxTestDateTimeFromServer) { bool blTransferDataResult = true; string strTransferDataMsg = ""; dNewMaxTestTime = MaxTestDateTimeFromServer; dMaxUploadTestTime = MaxTestDateTimeFromServer; List <string> lstSqlForInsert = new List <string>(); List <string> lstSqlForUpdateEFCDataDefault = new List <string>(); try { strAccessFullPath = CommonFun.GetFullFile(element.Path, element.Format); AccessConnectionString = string.Format(element.ConnectionString, strAccessFullPath); if (string.IsNullOrEmpty(strAccessFullPath)) { strTransferDataMsg = string.Format("没有找到对应的安规数据文件{0} ", strAccessFullPath); blTransferDataResult = false; } DataSet dsJYNYTestData = new DataSet(); StringBuilder sbSql = new StringBuilder(); string sql = string.Empty; if (blTransferDataResult) { #region //Access 数据库正确 //组织查询JYNY测试数据的SQL语句 if (element.Type == JYNYTestDeviceType.Customer) { sql = element.Sql; } else { sql = GetQueryTestDataSql(element.Type); } //SELECT Format(data.[日期],'yyyy-MM-dd ')+Format(data.[时间],'HH:mm:ss') AS TEST_TIME,data.[ID] AS TEST_ID,data.[产品条码] AS LOT_NUMBER,data.[测试结果] AS TEST_RESULT, // data.[是否测试] AS TEST_FLAG, //data.[步骤] AS TEST_STEP_SEQ,data.[测试类型] AS TEST_TYPE,data.[步骤判断] AS TEST_STEP_RESULT,data.[测试数据1] AS TEST_PARAM1,data.[测试数据2] AS TEST_PARAM2,data.[PROMPT], // data.[VOLTAGE],data.[FREQUENCY],data.[FREQUENCY],data.[HILIMIT],data.[LOLIMIT],data.[RAMPUP],data.[DWELLTIME],data.[DELAYTIME],data.[RAMPHI],data.[CHARGELO],data.[OFFSET], //data.[ARCSENSE],data.[ARCFAIL],data.[SCANNER] FROM data sbSql.AppendFormat("SELECT TOP 1 '{2}' AS EQUIPMENT_CODE,a.* FROM ({0}) a WHERE a.TEST_TIME>'{1}' ORDER BY a.TEST_TIME ASC", sql, MaxTestDateTimeFromServer.ToString("yyyy-MM-dd HH:mm:ss"), element.EqpName); //创建 Access的连接对象。 using (OleDbConnection oleCon = new OleDbConnection(this.AccessConnectionString)) { oleCon.Open(); using (OleDbCommand oleCmd = oleCon.CreateCommand()) { //从Access数据库获取>开始日期和开始时间的数据。 oleCmd.CommandType = CommandType.Text; oleCmd.CommandText = sbSql.ToString(); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCmd); oleAdapter.Fill(dsJYNYTestData); } oleCon.Close(); } #endregion //End Access数据库正确 } DataTable dt = null; //新增IV测试数据 if (dsJYNYTestData != null && dsJYNYTestData.Tables.Count > 0 && dsJYNYTestData.Tables[0].Rows.Count > 0) { #region //Get Access JYNY测试数据 string strInsertSql = ""; bool isDefault = false; foreach (DataRow dr in dsJYNYTestData.Tables[0].Rows) { #region Builder Insert Sql strInsertSql = @"INSERT INTO [dbo].[ZWIP_VIR_TEST] ([EQUIPMENT_CODE] ,[TEST_ID] ,[TEST_TIME] ,[LOT_NUMBER] ,[TEST_RESULT] ,[TEST_FLAG] ,[TEST_STEP_SEQ] ,[TEST_TYPE] ,[TEST_STEP_RESULT] ,[TEST_PARAM1] ,[TEST_PARAM2] ,[PROMPT] ,[VOLTAGE] ,[FREQUENCY] ,[ECURREN] ,[HILIMIT] ,[LOLIMIT] ,[RAMPUP] ,[DWELLTIME] ,[DELAYTIME] ,[RAMPHI] ,[CHARGELO] ,[OFFSET] ,[ARCSENSE] ,[ARCFAIL] ,[SCANNER]) VALUES ("; strInsertSql = strInsertSql + "'" + element.EqpName + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_ID"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_TIME"]).ToUpper() + "',"; if (DateTime.TryParse(dr["TEST_TIME"].ToString(), out dNewMaxTestTime) == false) { blTransferDataResult = false; strTransferDataMsg = "安规数据生成的日期格式不正确,请联系设备厂商。"; return; } String LOT_NUMBER = Convert.ToString(dr["LOT_NUMBER"]).ToUpper(); if (LOT_NUMBER.Contains("'")) { LOT_NUMBER = LOT_NUMBER.Replace("'", "''"); } strInsertSql = strInsertSql + "'" + LOT_NUMBER + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_RESULT"]).ToUpper() + "',"; if (Convert.ToString(dr["TEST_FLAG"]).ToUpper() == "Y") { strInsertSql = strInsertSql + " " + 1 + ", "; } else { strInsertSql = strInsertSql + " " + 0 + ", "; } strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_STEP_SEQ"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_TYPE"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_STEP_RESULT"]).ToUpper() + "',"; //,[TEST_PARAM1],[TEST_PARAM2],[PROMPT],[VOLTAGE],[FREQUENCY],[ECURREN],[HILIMIT],[LOLIMIT] strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_PARAM1"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_PARAM2"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["PROMPT"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["VOLTAGE"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["FREQUENCY"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ECURREN"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["HILIMIT"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["LOLIMIT"]).ToUpper() + "',"; //[RAMPUP] ,[DWELLTIME],[DELAYTIME],[RAMPHI],[CHARGELO],[OFFSET],[ARCSENSE],[ARCFAIL],[SCANNER] strInsertSql = strInsertSql + "'" + Convert.ToString(dr["RAMPUP"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["DWELLTIME"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["DELAYTIME"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["RAMPHI"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["CHARGELO"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["OFFSET"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ARCSENSE"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ARCFAIL"]).ToUpper() + "',"; strInsertSql = strInsertSql + "'" + Convert.ToString(dr["SCANNER"]).ToUpper() + "'"; strInsertSql = strInsertSql + ")"; //数据在数据库中是否已有(LOT_NUMBER\TEST_TIME\EQUIPMENT_CODE) string TEST_ID = ""; TEST_ID = "select TEST_ID from ZWIP_VIR_TEST where TEST_ID='" + dr["TEST_ID"] + "'" + "and EQUIPMENT_CODE ='" + element.EqpName + "'"; if (TEST_ID != dr["TEST_ID"]) { lstSqlForInsert.Add(strInsertSql); } strInsertSql = ""; isDefault = false; #endregion } sql = ""; if (blTransferDataResult) { string strExeResult = DatabaseEx.getDbInstance().ExecuteNonQuery(lstSqlForInsert); strInsertSql = ""; if (strExeResult != "" && strExeResult.Length > 0) { blTransferDataResult = false; strTransferDataMsg = strExeResult; } } #endregion //End Get Access JYNY测试数据 } } catch (Exception ex) { blTransferDataResult = false; strTransferDataMsg = ex.Message; } finally { lstSqlForInsert.Clear(); lstSqlForUpdateEFCDataDefault.Clear(); } DataTransferFinishedArgs arg = new DataTransferFinishedArgs(); arg.TransferDataResult = blTransferDataResult; arg.TransferDbFile = strAccessFullPath; if (blTransferDataResult) { strTransferDataMsg = string.Format("{1:yyyy-MM-dd HH:mm:ss}上传安规数据数据成功>{0} ", strTransferDataMsg, System.DateTime.Now.ToString()); arg.MaxTestDateTime = dNewMaxTestTime; } else { strTransferDataMsg = string.Format("{1:yyyy-MM-dd HH:mm:ss}上传安规数据数据失败>{0} ", strTransferDataMsg, System.DateTime.Now.ToString()); arg.MaxTestDateTime = dMaxUploadTestTime; } arg.TransferMsg = strTransferDataMsg; if (OnDataTransferFinished != null) { OnDataTransferFinished(this, arg); } }