public void ImportPLDatabase(int userid, string excelFilePath, int uploadRoleId, ExcelModel lstMapping, string excelFileName)
{
int index = 0, columnIndex = 0, count = 0; int year = 0;
//int index = 0, columnIndex = 0, count = 0; int year = 0;
string oleConnString = GetOleDbConnectionString(excelFilePath);
OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
OleDbCommand oleReadCommand = new OleDbCommand();
OracleTransaction transaction = null;
try
{
//int output = 0;
//string sqlInserPara2tDWValues = null;
//string sqlInsertPara2DW = null;
//string oleConnString = GetOleDbConnectionString(excelFilePath);
//OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
//OleDbCommand oleReadCommand = new OleDbCommand();
//OracleTransaction transaction = null;
//GET EXCEL DATA
string sqlOle = "select * from [" + lstMapping.ExcelSheetName + "$]";
oleDbConnection.Open();
oleReadCommand.Connection = oleDbConnection;
oleReadCommand.CommandText = sqlOle;
ConnectOracleDB.OpenDBConnect();
transaction = ConnectOracleDB.OracleDBConnect.BeginTransaction();
ConnectOracleDB.command.Transaction = transaction;
ConnectOracleDB.command.Parameters.Clear();
/**************************************************INSERT HISTORY**************************************/
this.DBConnection.OpenDBConnect();
int idHistory = 0;
this.InsertHistory(userid, uploadRoleId, lstMapping.FolderHelpDocumentPath, lstMapping.LangNote, lstMapping.FolderFileNativeName, excelFileName, ref idHistory);
this.DBConnection.CloseDBConnect();
string deleteRecordQuery = "DELETE FROM " + lstMapping.DBTableName + " WHERE ";
string sqlInsertDW = "insert into " + lstMapping.DBTableName + "(";
string sqlInsertDWValues = " values (";
bool isCommplateGenerateSQL = false;
using (OleDbDataReader dataReader = oleReadCommand.ExecuteReader())
{
while (dataReader.Read())
{
index++;
if (index == 1)
{
continue;
}
if (index == 2)
{
var madviqly = dataReader[0].ToString().Substring(0, 2);
var _month = dataReader[1].ToString();
var _year = dataReader[2].ToString();
deleteRecordQuery += "ma_dviqly like '%" + madviqly + "%'" + " " + "AND " + "THANG_BC " + "= " + _month + " " + "AND " + "NAM_BC " + "= " + _year;
this.ConnectOracleDB.command.CommandText = deleteRecordQuery;
this.ConnectOracleDB.command.ExecuteNonQuery();
}
if (index >= lstMapping.ExcelStartRow)
{
columnIndex = 0;
int countNullColumn = 0;
foreach (MappingExcelDB column in lstMapping.DBLstColumns)
{
//var test = dataReader.
columnIndex++;
column.Value = dataReader[column.ExcelColumn];
if (!isCommplateGenerateSQL)
{
sqlInsertDW += column.ColumnName + ",";
sqlInsertDWValues += ":" + column.ColumnName + ",";
}
try
{
if (column.Value.ToString().Trim() == "")
{
countNullColumn++;
}
}
catch (Exception)
{
countNullColumn++;
}
ConnectOracleDB.command.Parameters.Add(new OracleParameter(":" + column.ColumnName, column.Value ?? (object)DBNull.Value));
}
if (!isCommplateGenerateSQL)
{
sqlInsertDW = sqlInsertDW.Trim(',') + ")";
sqlInsertDWValues = sqlInsertDWValues.Trim(',') + ")";
isCommplateGenerateSQL = true;
}
//*****************************INSERT INTO DATABASE******************************************************
if (countNullColumn == lstMapping.DBLstColumns.Count())
{
break;
}
else
{
this.ConnectOracleDB.command.CommandText = sqlInsertDW + sqlInsertDWValues;
this.ConnectOracleDB.command.CommandType = CommandType.Text;
this.ConnectOracleDB.command.ExecuteNonQuery();
ConnectOracleDB.command.Parameters.Clear();
}
}
}
}
transaction.Commit();
UpdateHistory(idHistory, index);
}
catch (Exception ex)
{
this.ERROR = "tại dòng số : " + (index + 1).ToString() + ", Cột số : " + (columnIndex) + " " + ex.Message;;
}
finally
{
ConnectOracleDB.CloseDBConnect();
oleReadCommand.Dispose();
oleDbConnection.Close();
oleDbConnection.Dispose();
}
//return output;
}
public int ImportToDatabase(int currentYear, int userid, string excelFilePath, string tablename, int startRow, string sheetActive,
string helpDocumentPath, string note, string fileNativeName, string navetiveFile, int uploadRoleId, List <MappingExcelDB> lstMapping,
string FileUploadedName) //, int number_row, int number_cell, List<string> lstCellName
{
//List<int> data = GetDataInLine(excelFilePath, sheetActive, number_cell, fileNativeName, number_row);
int output = 0;
int index = 0, columnIndex = 0;
Char charRange = ':';
int Year = 0;
int Month = 0;
//Dictionary<string, int> line = new Dictionary<string, int>();
//if (data != null)
//{
// for (int i = 0; i < lstCellName.Count; i++)
// {
// line.Add(lstCellName[i], data[i]);
// }
//}
string oleConnString = GetOleDbConnectionString(excelFilePath);
OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
OleDbCommand oleReadCommand = new OleDbCommand();
OracleTransaction transaction = null;
try
{
//GET EXCEL DATA
string sqlOle = "select * from [" + sheetActive + "$]";
oleDbConnection.Open();
oleReadCommand.Connection = oleDbConnection;
oleReadCommand.CommandText = sqlOle;
int idHistory = 0;
//******************INSERT INTO HISTORY*********************************************************
//this.ConnectOracleDB.OpenDBConnect();6
this.InsertHistory(userid, uploadRoleId, helpDocumentPath, note, navetiveFile, FileUploadedName, ref idHistory);
ConnectOracleDB.OpenDBConnect();
transaction = ConnectOracleDB.OracleDBConnect.BeginTransaction();
ConnectOracleDB.command.Transaction = transaction;
using (OleDbDataReader dataReader = oleReadCommand.ExecuteReader())
{
while (dataReader.Read())
{
index++;
string sqlInsertDW = "insert into " + tablename + "(";
string sqlInsertDWValues = " values (";
string _sqlCheck = "Delete from " + tablename + " WHERE Year = " + Year;
bool isCommplateGenerateSQL = false;
if (index == 1)
{
string time = dataReader[0].ToString();
int startIndex = time.IndexOf(charRange);
if (time.ToString() == "")
{
throw new Exception("Time is null or not correct format.");
}
int endIndex = time.Length;
int length = endIndex - startIndex - 1;
int number = Int32.Parse(time.Substring(startIndex + 1, length));
if (number > 12)
{
Year = number;
}
else
{
Month = number;
}
//Delete data before insert with condition year
//_sqlCheck = Year > 0 ? "Delete from " + tablename + " WHERE Year = " + Year :
// "Delete from " + tablename + " WHERE MONTH = " + Month;
//this.ConnectOracleDB.command.CommandText = _sqlCheck;
//this.ConnectOracleDB.command.ExecuteNonQuery();
}
if (index < startRow)
{
continue;
}
ConnectOracleDB.command.Parameters.Clear();
columnIndex = 0;
int countNullColumn = 0;
foreach (MappingExcelDB column in lstMapping)
{
//var test = dataReader.
columnIndex++;
column.Value = dataReader[column.ExcelColumn];
if (!isCommplateGenerateSQL)
{
sqlInsertDW += column.ColumnName + ",";
sqlInsertDWValues += ":" + column.ColumnName + ",";
}
try
{
if (column.Value.ToString().Trim() == "")
{
countNullColumn++;
}
}
catch (Exception)
{
countNullColumn++;
}
ConnectOracleDB.command.Parameters.Add(new OracleParameter(":" + column.ColumnName, column.Value ?? (object)DBNull.Value));
}
//if (line != null)
//{
// foreach (var cell in line)
// {
// if (!isCommplateGenerateSQL)
// {
// sqlInsertDW += cell.Key + ",";
// sqlInsertDWValues += "@" + cell.Key + ",";
// }
// this._dbDwhConnection.command.Parameters.Add(new OracleParameter("@" + cell.Key, cell.Value));
// }
//}
if (!isCommplateGenerateSQL)
{
sqlInsertDW = Year > 0 ? sqlInsertDW.Trim(',') + ",Year)" : sqlInsertDW.Trim(',') + ",Month)";
sqlInsertDWValues = Year > 0 ? sqlInsertDWValues.Trim(',') + ",:Year)" : sqlInsertDWValues.Trim(',') + ", :Month)";
if (Year > 0)
{
ConnectOracleDB.command.Parameters.Add(new OracleParameter(":Year", Year));
}
else
{
ConnectOracleDB.command.Parameters.Add(new OracleParameter(":Month", Month));
}
isCommplateGenerateSQL = true;
}
//*****************************INSERT INTO DATABASE******************************************************
if (countNullColumn == lstMapping.Count)
{
break;
}
else
{
this.ConnectOracleDB.command.CommandText = sqlInsertDW + sqlInsertDWValues;
this.ConnectOracleDB.command.CommandType = CommandType.Text;
this.ConnectOracleDB.command.ExecuteNonQuery();
ConnectOracleDB.command.Parameters.Clear();
}
}
}
transaction.Commit();
//******************UPDATE ENDDATE HISTORY***************************************************************
//UpdateHistory(idHistory, index);
}
catch (Exception ex)
{
if (null != transaction)
{
transaction.Rollback();
}
this.ERROR = "ERROR in line=" + index.ToString() + ", column=" + columnIndex + ":::::" + ex.ToString();
this.ERROR_USER = "******" + index.ToString() + ", column=" + columnIndex;
}
finally
{
ConnectOracleDB.CloseDBConnect();
oleReadCommand.Dispose();
oleDbConnection.Close();
ConnectOracleDB.CloseDBConnect();
}
return(output);
}