public async Task <DataTable> ReadUnityLink(string FileDirectory, int ReconTypeId, string fileNamenAndType)
{
var dTable = new DataTable();
try
{
var getReconTypeInfo = await repoReconTypeRepository.GetAsync(c => c.ReconTypeId == ReconTypeId);
if (!fileNamenAndType.Contains(getReconTypeInfo.FileNamingConvention))
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Unrecognized file name";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read File, the Naming Convention does not match for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " . The file Name and type is: " + fileNamenAndType + " and the conventional name must be: " + getReconTypeInfo.FileNamingConvention);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
else
{
var chkfile = await repoadmFileProcessedRepository.GetAsync(c => c.FileName == fileNamenAndType);
if (chkfile != null)
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Couldn't processed the same File more than once";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read the File more than once, File already Read for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " The file Name and it type is: " + fileNamenAndType);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + FileDirectory + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
}
OleDbConnection excelConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileDirectory + "\\" + fileNamenAndType + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
excelConnection.Open();
DataTable Sheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sht = "";
foreach (DataRow dr in Sheets.Rows)
{
sht = dr[2].ToString().Replace("'", "");
// if (sht == "Sheet1$")
// {
OleDbCommand cmd = new OleDbCommand("select * from [" + sht + "]", excelConnection);
SqlDataAdapter da = new SqlDataAdapter();
//excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
//dt = dReader.GetSchemaTable(); We can use this one to get the column table name
dTable.Load(dReader);
//int count = 0;
int numberofCol = dTable.Columns.Count;
var hhh = dTable;
}
return(dTable);
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in Line Library ReadExcel File Source:" + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
throw;
}
return(dTable);
}
public async Task <DataTable> ReadExcelUnityLink(string FileDirectory, int ReconTypeId, string fileNamenAndType)
{
DataTable dTable = new DataTable();
try
{
var getReconTypeInfo = await repoReconTypeRepository.GetAsync(c => c.ReconTypeId == ReconTypeId);
if (!fileNamenAndType.Contains(getReconTypeInfo.FileNamingConvention))
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Unrecognized file name";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read File, the Naming Convention does not match for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " . The file Name and type is: " + fileNamenAndType + " and the conventional name must be: " + getReconTypeInfo.FileNamingConvention);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
else
{
var chkfile = await repoadmFileProcessedRepository.GetAsync(c => c.FileName == fileNamenAndType);
if (chkfile != null)
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Couldn't process File more than once";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read the File more than once, File already Read for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " The file Name and it type is: " + fileNamenAndType);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + FileDirectory + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
}
IExcelDataReader excelReader = null;
using (MemoryStream ms = new MemoryStream())
using (FileStream file = new FileStream(FileDirectory + "\\" + fileNamenAndType, FileMode.Open, FileAccess.Read))
{
byte[] bytes = new byte[file.Length];
file.Read(bytes, 0, (int)file.Length);
ms.Write(bytes, 0, (int)file.Length);
if (fileNamenAndType.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(new MemoryStream(bytes));
}
else if (fileNamenAndType.Contains("xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(bytes));
}
else if (fileNamenAndType.Contains("XLS"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(bytes));
}
}
var result = excelReader.AsDataSet();
excelReader.IsFirstRowAsColumnNames = true;
var dt = new DataTable();
dt = result.Tables[0];
return(dTable);
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in Line Library ReadExcel File Source:" + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
throw;
}
return(dTable);
}
