private void ImportDataNew() { DataTable myDT = new DataTable(); string SQL = ""; string _file = ""; string temp = ""; string user_id = "", result = ""; try { if (!string.IsNullOrEmpty(_img_pk)) { CtlLib.SetUser(_dbuser); SQL = "SELECT DATA, FILENAME, FILESIZE, CRT_BY FROM " + _dbuser + ".TC_FSBINARY WHERE PK = " + _img_pk; myDT = CtlLib.TableReadOpen(SQL); _file = myDT.Rows[0]["FILENAME"].ToString(); _file = _file.Substring(_file.LastIndexOf("\\") + 1, _file.Length - _file.LastIndexOf("\\") - 1); _file = "../temp/" + _file; _file = Server.MapPath(_file); if (File.Exists(_file)) { File.Delete(_file); } byte[] MyData = new byte[0]; MyData = (byte[])myDT.Rows[0]["DATA"]; BinaryWriter bw = new BinaryWriter(File.Open(_file, FileMode.OpenOrCreate)); bw.Write(MyData); bw.Close(); user_id = myDT.Rows[0]["CRT_BY"].ToString(); IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(_file); IWorksheet exSheet = exBook.Worksheets[1]; exSheet.UsedRange.UnMerge(); int sCols = 0; if (string.IsNullOrEmpty(_p_cols)) { sCols = myDT.Columns.Count; } else { sCols = int.Parse(_p_cols); } int sRow = 0; if (string.IsNullOrEmpty(_p_start)) { sRow = 2; } else { sRow = int.Parse(_p_start); } // chỗ này hơi chuối, vì dữ liệu trên file execel đã mer // nên phải set lại để truyền đủ tham số vào if (_procedure_file.Substring(_procedure_file.IndexOf(".") + 1, _procedure_file.Length - _procedure_file.IndexOf(".") - 1) == "HR_PRO_10020018_IMP_WT_OT") { myDT = exSheet.UsedRange.GetDataTable(false, false); for (int i = sRow; i <= myDT.Rows.Count; i++) { exSheet.Cells["A" + i].Value = i - sRow; if (exSheet.Cells["B" + i].Value == null) { exSheet.Cells["B" + i].Value = exSheet.Cells["B" + (i - 1)].Value; } if (exSheet.Cells["C" + i].Value == null) { exSheet.Cells["C" + i].Value = exSheet.Cells["C" + (i - 1)].Value; } if (exSheet.Cells["D" + i].Value == null) { exSheet.Cells["D" + i].Value = exSheet.Cells["D" + (i - 1)].Value; } if (exSheet.Cells["E" + i].Value == null) { exSheet.Cells["E" + i].Value = exSheet.Cells["E" + (i - 1)].Value; } } } string conString = CtlLib.GetConnecting(); OracleConnection connection = new OracleConnection(conString); connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction; transaction = connection.BeginTransaction(); command.Transaction = transaction; myDT = exSheet.UsedRange.GetDataTable(false, false); //for(int row = sRow; row < myDT.Rows.Count; row++) for (int row = sRow; row < myDT.Rows.Count; row++) { temp = ""; for (int col = 0; col < sCols; col++) { temp += myDT.Rows[row][col].ToString().Replace("'", "''").ToString() + "!"; } CtlLib.WriteLogError(_procedure_file + "('" + temp + "')"); temp += _p_1 + "!" + _p_2 + "!" + _p_3 + "!" + _p_4 + "!" + _p_5 + "!" + _import_seq; //temp += "!" + user_id; string[] paraIn = temp.Split('!'); result = CtlLib.ExecuteProcedureNotCommit(connection, command, _procedure_file, paraIn); if (result == "1") { _count++; } else { CtlLib.WriteLogError(_procedure_file + "('" + temp + "')"); this.hiddenRecord.Value = result; command.Dispose(); connection.Close(); connection.Dispose(); return; } } if (result == "1") { this.hiddenRecord.Value = _count.ToString(); } else { this.hiddenRecord.Value = result; } transaction.Commit(); command.Dispose(); connection.Close(); connection.Dispose(); } } catch (Exception e) { CtlLib.WriteLogError("ImportData:" + e.StackTrace); //Response.Write("ImportData:" +e.Message); this.hiddenRecord.Value = e.Message; } }