C# Validation Assistance

[Pho3nix]

Hi guys,

I am currently doing some validation on Excel Files which get imported into a SSIS package.
Now the issue I am having is C# validates strings as integers when this isn't actually possible resulting in the package failing.

Sample of the Code is :

foreach (SourceValidation sourceValidation in sourceValidations)
                                {
                                   switch (sourceValidation.ColumnDatatype)
                                    {
                                        case "String" :
                                            result = true;
                                                break;
                                        case "Integer":
                                                if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!int.TryParse(row.GetCell(columnIndex).StringValue, out tempInt)))) 
                                                {
                                                    result = false;
                                                    throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'. Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(),(rowIndex-1).ToString()));
                                                }
                                            break;
                                        case "Date":
                                            if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!DateTime.TryParse(row.GetCell(columnIndex).DateTimeValue.ToString(), out tempDate))))
                                            {
                                                result = false;
                                                throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'.Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(), (rowIndex - 1).ToString()));
                                            }
                                            break;
                                        case "Decimal":
                                            if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!decimal.TryParse(row.GetCell(columnIndex).StringValue, out tempDec))))
                                            {
                                                result = false;
                                                throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'.Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(),(rowIndex - 1).ToString()));
                                            }
                                            break;
                                    }
                                    tempInt = 0;
                                    tempDec = 0;
                                    tempDate = DateTime.Now;
                                    columnIndex++;
                                }
                                rowIndex++;
                            }

 

[envo]

It's not c# validating it as an integer, you will find that excel changes it for you (thinking they're smart and can determine what you need)

This tends to happen when the data pasted in the excel spreadsheet is mixed, but starts with a bunch of rows containing integers in that column. The entire column gets marked as an "Integer" even though it contains strings as well. The same with all the other column types.

I'd recommend checking row by row and throwing an error if the person put a string into an expected integer field by trying to cast that row to integer instead of leverageing off of what excel tells you the column type is

 

[Pho3nix]

It's not c# validating it as an integer, you will find that excel changes it for you (thinking they're smart and can determine what you need)

This tends to happen when the data pasted in the excel spreadsheet is mixed, but starts with a bunch of rows containing integers in that column. The entire column gets marked as an "Integer" even though it contains strings as well. The same with all the other column types.

I'd recommend checking row by row and throwing an error if the person put a string into an expected integer field by trying to cast that row to integer instead of leverageing off of what excel tells you the column type is

Think I might need to put up the entire method but I am checking row by row. In the DB, for each column I am telling the compiler what data type that column should be. Problem column should be an integer and that's what SSIS is expecting when it imports the file.
Getting the package re-done is the easiest solution I know but the admin around such is a little problematic.

Will post up some screenshots to elaborate a little more clearly if I'm not making sense.

 

[Pho3nix]

Full Method

private bool ValidateDataTypes()
        {
            bool result = true;
            int rowIndex = 0;

            try
            {
                ILookups lookuprepository = new LookupRepository(this.ConnectionString);
                List<ValidationSource> validationSources = lookuprepository.GetValidationSourcesBySource(this.SourceFile);

                Workbook book = Workbook.Load(string.Format("D:\\{0}", this.SourceFile.FileName));
                List<Worksheet> sheets = book.Worksheets;

                foreach (ValidationSource source in validationSources)
                {
                    foreach (Worksheet item in sheets)
                    {
                        if (item.Name == source.Caption)
                        {
                            List<SourceValidation> sourceValidations = lookuprepository.GetSourceValidationsForValidationSource(source);

                            int columnIndex = 0;
                            rowIndex = 1;
                            int tempInt = 0;
                            decimal tempDec = 0;
                            DateTime tempDate = DateTime.Now;

                            while(rowIndex <= (item.Cells.Rows.Count -1))
                            {
                                Row row = item.Cells.GetRow(rowIndex);
                                
                                foreach (SourceValidation sourceValidation in sourceValidations)
                                {
                                   switch (sourceValidation.ColumnDatatype)
                                    {
                                        case "String" :
                                            result = true;
                                                break;
                                        case "Integer":
                                                if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!int.TryParse(row.GetCell(columnIndex).StringValue, out tempInt)))) 
                                                {
                                                    result = false;
                                                    throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'. Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(),(rowIndex-1).ToString()));
                                                }
                                            break;
                                        case "Date":
                                            if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!DateTime.TryParse(row.GetCell(columnIndex).DateTimeValue.ToString(), out tempDate))))
                                            {
                                                result = false;
                                                throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'.Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(), (rowIndex - 1).ToString()));
                                            }
                                            break;
                                        case "Decimal":
                                            if ((!string.IsNullOrEmpty(row.GetCell(columnIndex).StringValue) && (!decimal.TryParse(row.GetCell(columnIndex).StringValue, out tempDec))))
                                            {
                                                result = false;
                                                throw new Exception(string.Format("The column type '{0}' is not correct. The correct type should be '{1}' for the Column '{2}'.Row number {3}", row.GetCell(columnIndex).StringValue, sourceValidation.ColumnDatatype.ToString(), sourceValidation.ColumnName.ToString(),(rowIndex - 1).ToString()));
                                            }
                                            break;
                                    }
                                    tempInt = 0;
                                    tempDec = 0;
                                    tempDate = DateTime.Now;
                                    columnIndex++;
                                }
                                rowIndex++;
                            }
                            
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

            return result;
        }

 

[Pho3nix]

Sample of the DB

[table="width: 500"]
[tr]
[td]Id[/td]
[td]SourceId[/td]
[td]FieldName[/td]
[td]DataType[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]First Name[/td]
[td]String[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]Rate[/td]
[td]Decimal[/td]
[/tr]
[/table]

 

[Pho3nix]

A new year bump