private void btnStoreSeries_Click(object sender, EventArgs e) { int newSeries = int.Parse(tbxSeries.Text); string newSeriesName = tbxSeriesName.Text; try { if (this.SerialState == EditStateType.Edit) { if (bsSeries.Current != null) { DatabaseSet.產品系列Row row = this.SelectedSerialRow; row.FillRow(newSeries, newSeriesName); 產品系列TableAdapter.Instance.Update(row); LoadPart(false); } } else if (this.SerialState == EditStateType.New) { DatabaseSet.產品系列Row newRow = DatabaseSet.產品系列Table.New產品系列Row(); newRow.FillRow(newSeries, newSeriesName); DatabaseSet.產品系列Table.Rows.Add(newRow); 產品系列TableAdapter.Instance.Update(newRow); } this.SerialState = EditStateType.None; } catch (ConstraintException) { MessageBox.Show("已存在系列號 " + newSeries + ",請指定其他系列號"); tbxSeries.SelectAll(); } catch (Exception ex) { Global.ShowError(ex); } finally { if (this.SerialState == EditStateType.Edit) { tbxSeries.DataBindings[0].ReadValue(); tbxSeriesName.DataBindings[0].ReadValue(); } } }
void ImportProductsData() { WorksheetAdapter sheetAdapter = new WorksheetAdapter(_sheet); Dictionary <string, DataRow> dic = new Dictionary <string, DataRow>(); DataTable dstTable = (DataTable)dgvDstCol.DataSource; int serialNoCol = (int)_dstRowList["系列編號"]["索引"]; int serialCodeNameCol = (int)_dstRowList["系列代號"]["索引"]; //int serialNameCol = (int)_dstRowList["系列名稱"]["索引"]; int partNoCol = (int)_dstRowList["品號"]["索引"]; int partNameCol = (int)_dstRowList["品名"]["索引"]; int lineCol = (int)_dstRowList["產線"]["索引"]; int laborHoursCol = (int)_dstRowList["工時"]["索引"]; int laborCostCol = (int)_dstRowList["系統時薪"]["索引"]; //int wageCol = (int)_dstRowList["標準工資"]["索引"]; this.BeginInvoke(new Action <string>(UpdateImportMessage), "從工作表擷取產品資料..."); this.BeginInvoke(new Action <int>(InitProgressBar), _sheet.UsedRange.Rows.Count - 1); StringBuilder sbEx = new StringBuilder(); DatabaseSet.產品品號DataTable updateTable = new DatabaseSet.產品品號DataTable(); DatabaseSet.產品品號DataTable insertTable = new DatabaseSet.產品品號DataTable(); List <string> lineList = new List <string>(); List <int> newSerial = new List <int>(); bool errorBreak = false; int errorCount = 0; int errorMax = 50; for (int i = 2; i <= _sheet.UsedRange.Rows.Count; i++) { try { string partNubmer = sheetAdapter.GetNotEmptyString(i, partNoCol); if (partNubmer != null) { int serialNo; try { serialNo = sheetAdapter.GetValue <int>(i, serialNoCol); } catch (Exception) { throw new SWLHMSException("系列編號欄位格式錯誤"); } string serialCodeName; try { serialCodeName = sheetAdapter.GetValue <string>(i, serialCodeNameCol); } catch (Exception) { throw new SWLHMSException("系列代號欄位格式錯誤"); } //string serialName = sheetAdapter.GetValue<string>(i, serialNameCol, false); string partName = sheetAdapter.GetValue <string>(i, partNameCol, false); string line; try{ line = sheetAdapter.GetValue <string>(i, lineCol); } catch (Exception) { throw new SWLHMSException("產線欄位格式錯誤"); } if (!lineList.Contains(line)) { lineList.Add(line); } decimal laborHours; try { laborHours = Convert.ToDecimal(sheetAdapter.GetValue <double>(i, laborHoursCol)); } catch (Exception) { throw new SWLHMSException("工時欄位格式錯誤"); } //decimal wage; //try { wage = Convert.ToDecimal(sheetAdapter.GetValue<double>(i, wageCol)); } //catch (Exception) { throw new Exception("標準工資欄位格式錯誤"); } decimal laborCost; try { laborCost = Convert.ToDecimal(sheetAdapter.GetValue <double>(i, laborCostCol)); } catch (Exception) { throw new SWLHMSException("單位標準工資欄位格式錯誤"); } DatabaseSet.產品品號Row row; // Make sure the serial exists if (DatabaseSet.產品系列Table.Select("編號 = " + serialNo).Length == 0) { DatabaseSet.產品系列Row serialRow = DatabaseSet.產品系列Table.New產品系列Row(); serialRow.FillRow(serialNo, serialCodeName); DatabaseSet.產品系列Table.Add產品系列Row(serialRow); 產品系列TableAdapter.Instance.Update(serialRow); newSerial.Add(serialNo); } // Get the conut of the part number from database int?num = 產品品號TableAdapter.Instance.GetCountBy品號(partNubmer); // Exists same part number if (num != null && num > 0) { row = updateTable.New產品品號Row(); row.品號 = partNubmer; updateTable.Add產品品號Row(row); row.AcceptChanges(); } else { row = insertTable.New產品品號Row(); row.品號 = partNubmer; insertTable.Add產品品號Row(row); } row.系列編號 = serialNo; row.品名 = partName; row.產線 = line; row.工時 = laborHours; //row.標準工資 = wage; row.標準工資 = laborHours * laborCost; row.單位標準工資 = laborCost; // If part number is duplicated, use the latter if (dic.ContainsKey(partNubmer)) { sbEx.AppendLine("行 " + i + ": 品號 '" + partNubmer + "' 重複,採用後者資料儲存"); dic[partNubmer] = row; } else { dic.Add(partNubmer, row); } } } catch (Exception ex) { if (errorCount++ > errorMax) { errorBreak = true; } sbEx.AppendLine("行 " + i + ": 發生錯誤 " + ex.Message); } this.BeginInvoke(new MethodInvoker(progressBar1.PerformStep)); if (errorBreak) { break; } } StringBuilder sb = new StringBuilder(); if (!errorBreak) { this.BeginInvoke(new Action <string>(UpdateImportMessage), "更新資料庫..."); //檢查有無新增產線 List <string> newLine = new List <string>(); foreach (string l in lineList) { if (產線TableAdapter.Instance.CheckLineAndCreate(l)) { newLine.Add(l); } } int updateCount = 產品品號TableAdapter.Instance.UpdateEx(updateTable); int insertCount = 產品品號TableAdapter.Instance.UpdateEx(insertTable); //產生報告 sb.AppendLine("來源檔案: " + dic.Count + " 筆品號資料"); sb.AppendLine("資料庫 : " + 產品品號TableAdapter.Instance.GetCount() + " 筆品號資料"); sb.AppendLine("已更新 : " + updateCount + " 筆資料"); sb.AppendLine("已新增 : " + insertCount + " 筆資料"); if (newSerial.Count > 0) { List <string> list = new List <string>(); foreach (int serial in newSerial) { list.Add(serial.ToString()); } sb.AppendLine("建立了新系列 : " + string.Join(",", list.ToArray())); } if (newLine.Count > 0) { sb.AppendLine("建立了新產線 : " + string.Join(",", newLine.ToArray())); } } else { sb.AppendLine("發生錯誤太多,停止匯入"); } if (sbEx.Length > 0) { sb.AppendLine("===錯誤==="); sb.AppendLine(sbEx.ToString()); } _report = sb.ToString(); if (!errorBreak) { this.BeginInvoke(new MethodInvoker(ImportDone)); } else { this.BeginInvoke(new MethodInvoker(ImportError)); } }
private void btnDelSeries_Click(object sender, EventArgs e) { try { if (bsSeries.Current != null) { DatabaseSet.產品系列Row row = (bsSeries.Current as DataRowView).Row as DatabaseSet.產品系列Row; int series = row.編號; string seriesName = row.代號; // Check whether all partnubmer are not in any worksheet OleDbConnection conn = 工作單品號TableAdapter.Instance.Connection; string cmdTxt = "SELECT COUNT(*) FROM 工作單品號 INNER JOIN 產品品號 ON 工作單品號.品號 = 產品品號.品號 WHERE 系列編號 = " + series; OleDbCommand cmd = new OleDbCommand(cmdTxt, conn); conn.Open(); object o = cmd.ExecuteScalar(); conn.Close(); if (o != null && (int)o != 0) { if (MessageBox.Show("系列" + series + "(" + seriesName + ")部分品號已建立工作單無法刪除,確定刪除其他品號?", "刪除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK) { conn.Open(); cmd = new OleDbCommand( "SELECT 產品品號.品號 FROM 產品品號 LEFT JOIN 工作單品號 ON 工作單品號.品號 = 產品品號.品號 WHERE 系列編號 = " + series + " AND 單號 IS NULL" , conn); OleDbDataReader dr = cmd.ExecuteReader(); string delCmdTxt = "DELETE FROM 產品品號 WHERE 品號 = ?"; OleDbCommand delCmd = new OleDbCommand(delCmdTxt, conn); OleDbParameter param = new OleDbParameter(); delCmd.Parameters.Add(param); int delCount = 0; while (dr.Read()) { string pn = (string)dr.GetValue(0); param.Value = pn; delCount += delCmd.ExecuteNonQuery(); } conn.Close(); MessageBox.Show("刪除了 " + delCount + " 筆品號"); } } else { if (MessageBox.Show("確定刪除 系列" + series + "(" + seriesName + ") 及其所有品號?", "刪除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK) { // delete all series row.Delete(); 產品系列TableAdapter.Instance.Update(row); } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }