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의 파일을 모두 읽어 올린다.
