public static DataTable GetFormatTableMySQL(DataTable ImportFSWareHouse) { DataRow rowtemp; MysqlMES mysqlMES = new MysqlMES(); DataTable Temp = new DataTable(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SHOW COLUMNS FROM t_importfg_warehouse"); mysqlMES.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref Temp); DataTable returndt = new DataTable(); DataRow toInsert = returndt.NewRow(); returndt.Rows.Add(toInsert); foreach (DataRow item in Temp.Rows) { DataColumn dc = new DataColumn(item.ItemArray[0].ToString()); returndt.Columns.Add(dc); } for (int k = 0; k < ImportFSWareHouse.Rows.Count; k++) { if (k != 0) { rowtemp = returndt.NewRow(); returndt.Rows.Add(rowtemp); } returndt.Rows[k]["TransactionID"] = ImportFSWareHouse.Rows[k]["TransactionID"]; returndt.Rows[k]["STT"] = ImportFSWareHouse.Rows[k]["STT"]; returndt.Rows[k]["UserID"] = ImportFSWareHouse.Rows[k]["UserID"]; returndt.Rows[k]["ProductOrder"] = ImportFSWareHouse.Rows[k]["ProductOrder"]; returndt.Rows[k]["Product"] = ImportFSWareHouse.Rows[k]["Product"]; returndt.Rows[k]["Quantity"] = ImportFSWareHouse.Rows[k]["Quantity"]; returndt.Rows[k]["LotNo"] = ImportFSWareHouse.Rows[k]["LotNo"]; returndt.Rows[k]["Warehouse"] = ImportFSWareHouse.Rows[k]["Warehouse"]; returndt.Rows[k]["dateImport"] = Convert.ToDateTime(ImportFSWareHouse.Rows[k]["dateImport"]).ToString("yyyy-MM-dd HH-mm-ss"); returndt.Rows[k]["ImportFlag"] = "Y"; returndt.Rows[k]["dateCreate"] = Convert.ToDateTime(ImportFSWareHouse.Rows[k]["dateImport"]).ToString("yyyy-MM-dd HH-mm-ss"); returndt.Rows[k]["SubQR"] = ImportFSWareHouse.Rows[k]["Product"] + "-" + DateTime.Now.ToString("yyyyy"); //returndt.Rows[k]["TransactionID"] = ImportFSWareHouse.Rows[k]["TransactionID"]; } return(returndt); }
public static bool Insert(DataTable ImportFSWareHouse) { try { StringBuilder stringbuider = new StringBuilder(); stringbuider.Append("insert into t_importfg_warehouse ("); DataTable DtForInsert = GetFormatTableMySQL(ImportFSWareHouse); // List<string> ListHeader = GetFormatTableMySQL().AsEnumerable().Select(x => x[0].ToString()).ToList(); for (int i = 0; i < DtForInsert.Columns.Count; i++) { if (i < DtForInsert.Columns.Count - 1) { stringbuider.Append(DtForInsert.Columns[i].ColumnName + ","); } else { stringbuider.Append(DtForInsert.Columns[i].ColumnName + ") values ( "); } } for (int i = 0; i < DtForInsert.Rows.Count; i++) { StringBuilder stringFun = new StringBuilder(); for (int j = 0; j < DtForInsert.Columns.Count; j++) { string valueCell = "NULL"; if (DtForInsert.Rows[i][DtForInsert.Columns[j].ColumnName] != null) { string tt = DtForInsert.Rows[i][DtForInsert.Columns[j].ColumnName].ToString(); if (DtForInsert.Columns[j].DataType == typeof(DateTime)) { if (DtForInsert.Columns[j].ColumnName == "MODI_DATE") { valueCell = "NULL"; } else { valueCell = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); } } else if (DtForInsert.Rows[i][DtForInsert.Columns[j].ColumnName].GetType() == typeof(DBNull)) { valueCell = "NULL"; } else { valueCell = DtForInsert.Rows[i][DtForInsert.Columns[j].ColumnName].ToString(); } } if (j < DtForInsert.Columns.Count - 1) { if (valueCell == "NULL") { stringFun.Append(" " + valueCell + " ,"); } else { stringFun.Append(" '" + valueCell + "',"); } } else { if (valueCell == "NULL") { stringFun.Append(" " + valueCell + ")"); } else { stringFun.Append(" '" + valueCell + "')"); } } } string sqlInsert = stringbuider.ToString() + stringFun.ToString(); MysqlMES sqlTLVN2 = new MysqlMES(); var result = sqlTLVN2.sqlExecuteNonQuery(sqlInsert, false); if (result == false) { return(false); } } return(true); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "Insert(DataTable ImportFSWareHouse)", ex.Message); } return(false); }