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;
}
}