コード例 #1
0
        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();
        }
コード例 #2
0
        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";
            }
        }
コード例 #3
0
        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";
            }
        }
コード例 #4
0
        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)
            { }
        }