IDataReader that returns multiple IDataRecords for each object in the input list

Question

I am trying to implement the fastest way of importing millions of rows into a SQL table via use of the SqlBulkCopy function and providing it with a custom IDataReader (I am told this would be faster than trying to create a DataTable)

I currently have a list of objects that represent documents we have in our document management system. Each document has a number of properties. Here we will assume it has 4 properties which are its GUID, name, date created, and file size

The SQL table I am putting this into (simplified for the purposes of this question) has 3 columns These are the document GUID, attribute name, and attribute value.

For the first document object in the input list, I would need the IDataReader to return 3 data records for that document. It's only 3 because the document GUID is a value that gets returned on each of the 3 records

eg if i had the following document object

doc.Guid = 7418db3a-6de6-4657-ac0a-c12585c7c3f2 
doc.Name = "Test Document" 
doc.DateCreated = 28/04/2021 
doc.Filesize = 3345

Then the IDataReader would stream out 3 rows like the following:

DocumentGUID	AttributeName	AttributeValue
7418db3a-6de6-4657-ac0a-c12585c7c3f2	Name	Test Document
7418db3a-6de6-4657-ac0a-c12585c7c3f2	DateCreated	28/04/2021
7418db3a-6de6-4657-ac0a-c12585c7c3f2	Filesize	3345

I am using the code from the following website as my starting point: http://andreyzavadskiy.com/2017/07/03/converting-list-to-idatareader/

For ease, I have copied the code below

public class ListDataReader<T> : IDataReader
        where T : class
{
    #region Private fields
    private IEnumerator<T> _iterator;
    private List<PropertyInfo> _properties = new List<PropertyInfo>();
    #endregion

    #region Public properties
    #endregion

    #region Constructors
    public ListDataReader(IEnumerable<T> list)
    {
        this._iterator = list.GetEnumerator();

        _properties.AddRange(
            typeof(T).GetProperties(
                BindingFlags.GetProperty |
                BindingFlags.Instance |
                BindingFlags.Public |
                BindingFlags.DeclaredOnly
                )
            );
    }
    #endregion

    #region IDataReader implementation
    public int Depth { get; }
    public bool IsClosed { get; }
    public int RecordsAffected { get; }

    public void Close()
    {
        _iterator.Dispose();
    }

    public DataTable GetSchemaTable()
    {
        throw new NotImplementedException();
    }

    public bool NextResult()
    {
        throw new NotImplementedException();
    }

    public bool Read()
    {
        return _iterator.MoveNext();
    }
    #endregion

    #region IDisposable implementation
    public void Dispose()
    {
        Close();
    }
    #endregion

    #region IDataRecord
    public string GetName(int i)
    {
        return _properties[i].Name;
    }

    public string GetDataTypeName(int i)
    {
        throw new NotImplementedException();
    }

    public Type GetFieldType(int i)
    {
        return _properties[i].PropertyType;
    }

    public object GetValue(int i)
    {
        return _properties[i].GetValue(_iterator.Current, null);
    }

    public int GetValues(object[] values)
    {
        int numberOfCopiedValues = Math.Max(_properties.Count, values.Length);

        for (int i = 0; i < numberOfCopiedValues; i++)
        {
            values[i] = GetValue(i);
        }

        return numberOfCopiedValues;
    }

    public int GetOrdinal(string name)
    {
        var index = _properties.FindIndex(p => p.Name == name);

        return index;
    }

    public bool GetBoolean(int i)
    {
        return (bool) GetValue(i);
    }

    public byte GetByte(int i)
    {
        return (byte) GetValue(i);
    }

    public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
    {
        throw new NotImplementedException();
    }

    public char GetChar(int i)
    {
        return (char) GetValue(i);
    }

    public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
    {
        throw new NotImplementedException();
    }

    public Guid GetGuid(int i)
    {
        return (Guid) GetValue(i);
    }

    public short GetInt16(int i)
    {
        return (short) GetValue(i);
    }

    public int GetInt32(int i)
    {
        return (int) GetValue(i);
    }

    public long GetInt64(int i)
    {
        return (long) GetValue(i);
    }

    public float GetFloat(int i)
    {
        return (float) GetValue(i);
    }

    public double GetDouble(int i)
    {
        return (double) GetValue(i);
    }

    public string GetString(int i)
    {
        return (string) GetValue(i);
    }

    public decimal GetDecimal(int i)
    {
        return (decimal) GetValue(i);
    }

    public DateTime GetDateTime(int i)
    {
        return (DateTime) GetValue(i);
    }

    public IDataReader GetData(int i)
    {
        throw new NotImplementedException();
    }

    public bool IsDBNull(int i)
    {
        return GetValue(i) == null;
    }

    public int FieldCount
    {
        get { return _properties.Count; } 
    }

    object IDataRecord.this[int i]
    {
        get { return GetValue(i); }
    }

    object IDataRecord.this[string name]
    {
        get { return GetValue(GetOrdinal(name)); }
    }
    #endregion
}

Can anyone give any guidance on how to amend this to produce the output I want?

Answer 1

You can use LINQ's SelectMany with yield return to pass through multiple records to the ListDataReader's constructor, because it will accept any IEnumerable<T>:

var list = new List<Document> {
    new Document {
        Guid = 7418db3a-6de6-4657-ac0a-c12585c7c3f2 ,
        Name = "Test Document",
        DateCreated = 28/04/2021,
        Filesize = 3345,
    }
};

var reader = NewListDataReader(list.SelectMany(doc =>
    new []
    {
        new {
            DocumentGUID = doc.Guid,
            AttributeName = "Name",
            AttributeValue = doc.Name,
        },
        new {
            DocumentGUID = doc.Guid,
            AttributeName = "DateCreated",
            AttributeValue = doc.DateCreated.ToString(),
        },
        new {
            DocumentGUID = doc.Guid,
            AttributeName = "Filesize",
            AttributeValue = doc.Filesize.ToString(),
        },
    }));

You also need this little helper function for anonymous types:

ListDataReader NewListDataReader<T>(IEumerable<T> source)
{
    return new ListDataReader(source);
}

Or you could even make a separate struct instead of the anonymous object:

struct Record
{
    public Guid DocumentGUID;
    public string AttributeName;
    public string AttributeValue;
}