0

I'm trying to insert a list of file names into a simple Sql Server table.

I'm trying to leverage SqlBulkCopy and @markgravell's FastMember library, as suggested by other SO answers.

public async Task AddFileNamesAsync(string[] fileNames)
{
    fileNames.ShouldNotBeNull();

    using (var bulkCopy = new SqlBulkCopy(ConnectionString))
    {
        using (var reader = ObjectReader.Create(fileNames))
        {
            bulkCopy.DestinationTableName = "FileNames";
            bulkCopy.ColumnMappings.Add("value", "FileName");
            await bulkCopy.WriteToServerAsync(reader)
                          .ConfigureAwait(false);
        }
    }
}

CREATE TABLE [dbo].[FileNames](
[FileNameId] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](500) NOT NULL

So I feel like it's a mapping issue either with:

  • FastMember not able to map to some internal backing collection
  • The FastMember backing collection doesn't have the same name as the DB column so it can't map.
halfer
  • 19,824
  • 17
  • 99
  • 186
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • You could just write your own implementation of an IDataReader. SqlBulkCopy only calls the GetValue, Read, and FieldCount implementations on the reader, so it is relatively easy to write a custom reader. Just a thought. – TnTinMn Mar 03 '17 at 06:10

1 Answers1

1

I have never used this Library before however reviewing the GitHub source code it it requires a property to query from the source. Now on a string there isn't a property value really all you have to use is the Length property. Using Length. Now this may be an issue with the FastMember library, where it creates a CallSite accessor function to capture the Property from the target object.

Source here

Now I have had a play and cannot get access to any property that will work. At first glance their is a Chars property being returned in the TypeAccessor results but this does not appear to work.

My suggestion is not really an answer to the question but a way to make it work. If you created a type that had a Property of the string then you could effectively get around this.

public async Task AddFileNamesAsync(string[] fileNames)
{
    fileNames.ShouldNotBeNull();

    var list = fileNames.Select(f => new { value = f });

    using (var bulkCopy = new SqlBulkCopy(ConnectionString))
    {
        using (var reader = ObjectReader.Create(list))
        {
            bulkCopy.DestinationTableName = "FileNames";
            bulkCopy.ColumnMappings.Add("value", "FileName");

            try
            {
                await bulkCopy.WriteToServerAsync(reader)
                                .ConfigureAwait(false);

            }
            catch(Exception ex)
            {

            }
        }
    }
}

Now this will work as we generated a new type with a property of value which is each file name. Now the execution should work as expected. (Note the try..catch... was just for testing).

Nico
  • 12,493
  • 5
  • 42
  • 62
  • I was afraid of this suggested answer, because then I would have TWO copies of the data. Sure, we're not talking a bazillion (source) strings ... but it's the point. This was why I avoided mentioning this, in the OP. Hmmm....... bummer. But yeah ... I was thinking along the same lines as this answer. – Pure.Krome Mar 03 '17 at 04:04
  • @Pure.Krome sorry couldnt find another way to hack it without actually changing and recompiling the source code of FastMember. I tried ways of accessing Methods (ie calling `ToString()` on a string) but it locked to Properties only. – Nico Mar 03 '17 at 04:11
  • No problems. :) I really appreciate your time, though! – Pure.Krome Mar 03 '17 at 04:15