Static Fields?

[bbakermmc]

Is there a way to add static fields? EX: When loading from file and putting into the DB, we have a FileId that we generate before the insert, so we would want to add this, but it doesnt exist in the file. The code seems to blow up if I try to do it.

Field index must be included in [0, FieldCount[. Specified field index was : '16'.
Parameter name: field
Actual value was 16.

ref:

using (var reader = new CsvReader(new StreamReader(blobFile.OpenRead()), false, '|'))
               {

                   reader.MissingFieldAction = MissingFieldAction.ReplaceByNull;

                   reader.ParseError += (sender, args) =>
                   {
                       var error = args.Error;
                   };

                   var cols = new List<Column>();
                   cols.Add(new Column(){Name ="FileLogId", Type = typeof(string), OverrideValue = fileLogId.ToString(), DefaultValue = fileLogId.ToString()});

                   foreach (var field in fileDefinitionFields.Where(x => x.FieldOrder >0))
                   {
                       cols.Add(new Column(){Name = field.ColumnName, Type = typeof(string)});
                   }

                   reader.Columns = cols;

                   // Now use SQL Bulk Copy to move the data
                   using (var sbc = bulkCopy)
                   {


                       sbc.WriteToServer(reader);
                   }
               }

[jeddytier4]

I'm having a very similar issue. It appears that DefaultValue and OverrideValue are only applied when reading the CSV row by row. I need them to be applied for bulkcopy as well. I'm looking at the source, but can't see yet where the issue occurs.

[HTI-DaraiusKeeka]

Hi @bbakermmc Was there any solution to this please?

[bbakermmc]

@HTI-DaraiusKeeka Since there was no response looks like I ended up using a diff framework: https://github.com/Cinchoo/ChoETL

REF:

private static void ChoEtlDelimitedMethod(CloudBlockBlob blobFile, int fileLogId, IEnumerable<FileDefinitionFieldsDto> fileDefinitionFields, SqlBulkCopy bulkCopy)
        {
            var config = new ChoCSVRecordConfiguration
                         {
                             FileHeaderConfiguration =
                             {
                                 HasHeaderRecord = true
                             }
                             , Delimiter                  = "|"
                             , AutoDiscoverColumns        = false
                             , AutoDiscoverFieldTypes     = false
                             , IsDynamicObject            = true
                             , ThrowAndStopOnMissingField = false
                         };

            foreach (var field in fileDefinitionFields.Where(x => x.FieldOrder > 0 && !x.IgnoreColumnFlagBool))
            {
                config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration(field.ColumnName, field.FieldOrder));
            }

            using (var parser = new ChoCSVReader(blobFile.OpenRead(), config).Select(row =>
                                                                             {
                                                                                 row.FileLogId = fileLogId.ToString();

                                                                                 return row;
                                                                             })
                                                                             .AsDataReader())
            {
                using (var bc = bulkCopy)
                {
                    bc.WriteToServer(parser);
                    bc.Close();
                    bulkCopy.Close();
                    bulkCopy = null;
                }
            }
        }

[HTI-DaraiusKeeka]

@HTI-DaraiusKeeka Since there was no response looks like I ended up using a diff framework: https://github.com/Cinchoo/ChoETL

REF:

private static void ChoEtlDelimitedMethod(CloudBlockBlob blobFile, int fileLogId, IEnumerable<FileDefinitionFieldsDto> fileDefinitionFields, SqlBulkCopy bulkCopy)
        {
            var config = new ChoCSVRecordConfiguration
                         {
                             FileHeaderConfiguration =
                             {
                                 HasHeaderRecord = true
                             }
                             , Delimiter                  = "|"
                             , AutoDiscoverColumns        = false
                             , AutoDiscoverFieldTypes     = false
                             , IsDynamicObject            = true
                             , ThrowAndStopOnMissingField = false
                         };

            foreach (var field in fileDefinitionFields.Where(x => x.FieldOrder > 0 && !x.IgnoreColumnFlagBool))
            {
                config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration(field.ColumnName, field.FieldOrder));
            }

            using (var parser = new ChoCSVReader(blobFile.OpenRead(), config).Select(row =>
                                                                             {
                                                                                 row.FileLogId = fileLogId.ToString();

                                                                                 return row;
                                                                             })
                                                                             .AsDataReader())
            {
                using (var bc = bulkCopy)
                {
                    bc.WriteToServer(parser);
                    bc.Close();
                    bulkCopy.Close();
                    bulkCopy = null;
                }
            }
        }

Thank you.