private void FillParameters(MySqlCommand comm, ProductionListDTO item) { comm.Parameters.AddWithValue("@ProductionCode", item.ProductionCode); comm.Parameters.AddWithValue("@MachineID", item.MachineID); comm.Parameters.AddWithValue("@ProductionID", item.ProductionID); comm.Parameters.AddWithValue("@TodoCode", item.TodoCode); comm.Parameters.AddWithValue("@EmployeeID", item.EmployeeID); comm.Parameters.AddWithValue("@NomalAmount", item.NomalAmount); comm.Parameters.AddWithValue("@DefectAmount", item.DefectAmount); comm.Parameters.AddWithValue("@ProductionDate", item.ProductionDate); }
private void PushToDB() { // Read Files List <string> fileList = GetSubFiles(storingPath); List <ProductionListDTO> storingList = new List <ProductionListDTO>(); foreach (var filePath in fileList) { try { using (StreamReader reader = new StreamReader(filePath, Encoding.UTF8)) { string str; if (string.IsNullOrEmpty(reader.ReadToEnd().Replace("\\n", "").Replace("\\r", ""))) // 빈 파일( Writer가 쓰고있던 파일 ) 이면 지운다. => ( stored로 옮길 때 덮어쓰면 안되기 때문 ) { reader.Close(); File.Delete(filePath); continue; } reader.BaseStream.Position = 0; // 포지션 옮기기 while (!string.IsNullOrEmpty(str = reader.ReadLine())) // 파일 읽기 { str = str.Replace("\\n", "").Replace("\\r", ""); string[] line = str.Split('|'); //D:\AIclass\project\MachineProject\MachineProject\bin\Debug\Productions\Running\20003\20191106114003.txt // 19:49:22 DateTime date = DateTime.ParseExact(Path.GetFileName(filePath).Substring(0, 8) + line[1], "yyyyMMddHH:mm:ss", CultureInfo.CurrentCulture); ProductionListDTO dto = new ProductionListDTO(); dto.TodoCode = Convert.ToInt32(line[0]); dto.ProductionDate = date; dto.ProductionID = line[2]; dto.EmployeeID = line[3]; dto.TotalAmount = Convert.ToInt32(line[4].Trim()); dto.NomalAmount = Convert.ToInt32(line[5].Trim()); dto.DefectAmount = Convert.ToInt32(line[6].Trim()); dto.MachineID = line[7]; storingList.Add(dto); // DTO를 만들어서 List 만들기 } } } catch { Debug.WriteLine(filePath + "는 액세스 중인 파일입니다."); } } // Push To DB // CREATE SQL StringBuilder sb = new StringBuilder(); StringBuilder sbvalues = new StringBuilder(); sb.Append("INSERT INTO PRODUCTIONLIST(TodoCode, MachineID, ProductionID, EmployeeID, NormalAmount, DefectAmount, ProductionDate) VALUES "); foreach (var item in storingList) { sbvalues.Append(string.Format("({0}, '{1}', '{2}', '{3}', {4}, {5}, '{6}'),", item.TodoCode, item.MachineID, item.ProductionID, item.EmployeeID, item.NomalAmount, item.DefectAmount, item.ProductionDate.ToString("yyyy-MM-dd HH:mm:ss"))); } sb.Append(sbvalues.ToString().Trim(',') + "; "); // INSERT MySqlConnection conn = new MySqlConnection(connstr); MySqlCommand comm = new MySqlCommand(sb.ToString(), conn); conn.Open(); comm.ExecuteNonQuery(); conn.Close(); } // db에 storing의 파일을 모두 읽어 올린다.