public void LoadDataTable(string filePath, string FileName, int FileSerial, int ProjectSerial)
{
string fileExtension = Path.GetExtension(filePath);
switch (fileExtension.ToLower())
{
case ".xlsx":
ConvertExcelToDataTable(filePath, true, FileSerial);
break;
case ".xls":
ConvertExcelToDataTable(filePath, false, FileSerial);
break;
case ".csv":
ConvertCsvToDataTable(filePath, FileSerial);
break;
case ".txt":
ConvertTextDocumentToDataTable(filePath, FileSerial);
break;
}
ValidtionDatatable(dataTable, FileName, DateTime.Now, FileSerial, ProjectSerial);
DataValidationAPI validation = new DataValidationAPI();
}
public void ConvertTextDocumentToDataTable(string filePath, int FileSerial)
{
DataTable dt = new DataTable();
if (File.Exists(filePath))
{
using (StreamReader sr = new StreamReader(filePath, Encoding.GetEncoding(1251)))
{
var line = sr.ReadLine();
if (null != line)
{
string[] headers = line.Split('|');
DataValidationAPI ob = new DataValidationAPI();
if (ob.ChekCol(headers, FileSerial) == true)
{
foreach (string header in headers)
{
dt.Columns.Add(header);
}
while (!sr.EndOfStream)
{
string[] rows = sr.ReadLine().Split('|');
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
dt.Rows.Add(dr);
}
if (sr.EndOfStream == true)
{
dataTable = dt;
}
else
{
Message = "DataEmpty";
}
}
else
{
Message = "Missing Column";
}
}
else
{
Message = "Missing Column";
}
}
}
else
{
Message = "File_not_Exists";
}
}
public void ConvertExcelToDataTable(string filePath, bool isXlsx, int FileSerial)
{
FileStream stream = null;
IExcelDataReader excelReader = null;
DataTable dataTables = null;
if (File.Exists(filePath))
{
stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
excelReader = isXlsx ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
if (result != null && result.Tables.Count > 0)
{
dataTables = result.Tables[0];
}
List <string> columnNameList = new List <string>();
foreach (DataColumn dataColumn in dataTables.Columns)
{
columnNameList.Add(dataColumn.ColumnName);
}
DataValidationAPI ob = new DataValidationAPI();
string[] headers = columnNameList.ToArray();
if (ob.ChekCol(headers, FileSerial) == true)
{
ArrayList rows = new ArrayList();
foreach (DataRow dataRow in dataTables.Rows)
{
rows.Add(dataRow.ItemArray.Select(item => item.ToString()));
}
if (rows.Count > 0)
{
dataTable = dataTables;
}
else
{
Message = "DataEmpty";
}
}
else
{
Message = "Missing Column";
}
}
else
{
Message = "File_not_Exists";
}
}
public void ValidtionDatatable(DataTable DT, string FileName, DateTime date, int FileSerial, int ProjectSerial)
{
try
{
DbCon CSDB = new DbCon();
List <SqlParameter> mySqlParameter_list = new List <SqlParameter>();
if (DT != null)
{
int errorFlag = 0;
DataValidationAPI validation = new DataValidationAPI();
DataTable myDataTable = new DataTable();
myDataTable = CSDB.Execute_Of_Query_Function_dataSet("Select * From tblFileFields");
for (int i = 0; i < DT.Rows.Count; i++)
{
foreach (DataColumn dc in DT.Columns)
{
var ColumnName_Document = dc.ColumnName.Trim().ToString();
DataRow[] dr = myDataTable.Select("vchrFieldName = '" + ColumnName_Document + "'");
var value = DT.Rows[i]["" + ColumnName_Document + ""].ToString();
foreach (DataRow row in dr)
{
var serial = Convert.ToInt32(row.ItemArray[0]);
var type = row.ItemArray[2].ToString();
var columnName = row.ItemArray[5].ToString();
var AllowNull = row.ItemArray[8].ToString();
var dcMaxValue = Convert.ToInt32(row.ItemArray[9]);
var Length = Convert.ToInt32(row.ItemArray[10]);
var vchrFormat = row.ItemArray[14].ToString();
var scale = Convert.ToInt32(row.ItemArray[4]);
var precision = Convert.ToInt32(row.ItemArray[3]);
var Negative = row.ItemArray[11].ToString();
var btLookup = row.ItemArray[12].ToString();
if (type == "1")
{
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (Length > 0)
{
if (validation.Number_length(value, Length) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "length Dose Not Match");
}
}
if (validation.Number(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Number");
}
if (Negative.ToLower() == "true")
{
if (validation.Number_Negative(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Negative Number");
}
}
if (dcMaxValue > 0)
{
if (validation.Number_MaxValue(value, dcMaxValue) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "MaxValue Dose Not Match");
}
}
}
else if (type == "2")
{
if (validation.Arabic(value))
{
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
}
else
{
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (vchrFormat == "Combined")
{
if (validation.Text(value, "Combined") == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Not valid text");
}
}
else if (vchrFormat == "Letters")
{
if (validation.Text(value, "Letters") == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Not valid text");
}
}
else
{
if (validation.Text(value, "Numbers") == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Text");
}
}
}
}
else if (type == "3")
{
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (validation.Date(value, vchrFormat) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Invalid Date Foramt");
}
}
else if (type == "4")
{
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (validation.Bit(value, vchrFormat) == false)
{
if (vchrFormat == "true/false")
{
errorFlag++;
factory.AddToReportList(columnName, "Not True or False");
}
else if (vchrFormat == "yes/no")
{
errorFlag++;
factory.AddToReportList(columnName, "Not Yes or No");
}
else
{
errorFlag++;
factory.AddToReportList(columnName, "Not 1 or 0");
}
}
}
else//Decimal
{
if (btLookup == "True")
{
if (validation.IsLockUp(value, serial) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Value doesn't exist in lockup");
}
}
if (AllowNull == "True")
{
if (validation.NotEmptyOrNull(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Null or Empty");
}
}
if (Negative.ToLower() == "true")
{
if (validation.Number_Negative(value) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Negative Number");
}
}
if (validation.Decimal(value, precision, scale) == false)
{
errorFlag++;
factory.AddToReportList(columnName, "Not valid decimal");
}
}
}
}
if (errorFlag > 0)
{
totalNumberOfRowsHaveErrors++;
}
RowsCount = DT.Rows.Count - 1;
}
InsertEmail(FileName, date, FileSerial, ProjectSerial, Message);
}
else
{
InsertEmail(FileName, date, FileSerial, ProjectSerial, Message);
}
}
catch (Exception ex)
{ }
}