Just want to share something I use to do bulk inserts - using LLBLGen. LLBLGen 3.5, Adapter, MSSQL 2005-2012

Maybe it proves to be useful for someone. There is room for improvement, was just a first shot.

The EntityCollection passed in to insert can only be FLAT, there is no graph-support.

Place this as a partial class to the DatabaseSpecific MSSQL project

Use it as:

adapter.InsertDataUsingSqlBulkCopy<CustomerEntity>(myEntityCollectionToInsert);

public partial class DataAccessAdapter
    {

public void InsertDataUsingSqlBulkCopy<TEntity>(EntityCollectionBase2<TEntity> collection)
            where TEntity : EntityBase2, IEntity2, new()
        {
            var dummy = new TEntity();
            var bulkCopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)
            {
                DestinationTableName = GetTableName(dummy.Fields[0])
            };
            
            foreach (IEntityField2 field in dummy.Fields)
            {
                bulkCopy.ColumnMappings.Add(field.Name, GetFieldName(field));
            }

            using (var dataReader = new EntityCollectionDataReader<TEntity>(collection))
            {
                bulkCopy.WriteToServer(dataReader);
            }
        }


        public string GetFieldName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = GetFieldPersistenceInfo(field);
            return i.SourceColumnName;
        }
        public string GetTableName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = GetFieldPersistenceInfo(field);
            return i.SourceObjectName;
        }
    }

Along with:

public class EntityCollectionDataReader<TEntity> : IDataReader
        where TEntity : EntityBase2, IEntity2, new()
    {
        /// <summary>
        /// The enumerator for the IEnumerable{TEntity} passed to the constructor for 
        /// this instance.
        /// </summary>
        private IEnumerator<TEntity> _dataEnumerator;

        /// <summary>
        /// The lookup of accessor functions for the properties on the TEntity type.
        /// </summary>
        private readonly Func<TEntity, object>[] _accessors;

        /// <summary>
        /// The lookup of property names against their ordinal positions.
        /// </summary>
        private readonly Dictionary<string, int> _ordinalLookup;

        /// <summary>
        /// Initializes a new instance of the <see cref="IEnumerable<TEntity>"/> class.
        /// </summary>
        /// <param name="data">The data this instance should enumerate through.</param>
        public EntityCollectionDataReader(IEnumerable<TEntity> data)
        {
            this._dataEnumerator = data.GetEnumerator();

            var entity = new TEntity();

            // Get all the readable properties for the class and
            // compile an expression capable of reading it
            var propertyAccessors = entity.Fields
                .Select((p, i) => new
                    {
                        Index = p.FieldIndex,
                        Name = p.Name,
                        Accessor = CreatePropertyAccessor(p.Name)
                    })
                .ToArray();

            this._accessors = propertyAccessors.Select(p => p.Accessor).ToArray();
            this._ordinalLookup = propertyAccessors.ToDictionary(
                p => p.Name,
                p => p.Index,
                StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// Creates a property accessor for the given property information.
        /// </summary>
        /// <param name="name">Property name</param>
        /// <returns>The generated accessor function.</returns>
        private static Func<TEntity, object> CreatePropertyAccessor(string name)
        {
            // Define the parameter that will be passed - will be the current object
            var parameter = Expression.Parameter(typeof(TEntity), "input");

            // Define an expression to get the value from the property
            var propertyAccess = Expression.Property(parameter, name);

            // Make sure the result of the get method is cast as an object
            var castAsObject = Expression.TypeAs(propertyAccess, typeof(object));

            // Create a lambda expression for the property access and compile it
            var lamda = Expression.Lambda<Func<TEntity, object>>(castAsObject, parameter);
            return lamda.Compile();
        }

        #region IDataReader Members

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

        public int Depth
        {
            get { return 1; }
        }

        public DataTable GetSchemaTable()
        {
            return null;
        }

        public bool IsClosed
        {
            get { return this._dataEnumerator == null; }
        }

        public bool NextResult()
        {
            return false;
        }

        public bool Read()
        {
            if (this._dataEnumerator == null)
            {
                throw new ObjectDisposedException("ObjectDataReader");
            }

            return this._dataEnumerator.MoveNext();
        }

        public int RecordsAffected
        {
            get { return -1; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this._dataEnumerator != null)
                {
                    this._dataEnumerator.Dispose();
                    this._dataEnumerator = null;
                }
            }
        }

        #endregion

        #region IDataRecord Members

        public int FieldCount
        {
            get { return this._accessors.Length; }
        }

        public bool GetBoolean(int i)
        {
            throw new NotImplementedException();
        }

        public byte GetByte(int i)
        {
            throw new NotImplementedException();
        }

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

        public char GetChar(int i)
        {
            throw new NotImplementedException();
        }

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

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

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

        public DateTime GetDateTime(int i)
        {
            throw new NotImplementedException();
        }

        public decimal GetDecimal(int i)
        {
            throw new NotImplementedException();
        }

        public double GetDouble(int i)
        {
            throw new NotImplementedException();
        }

        public Type GetFieldType(int i)
        {
            throw new NotImplementedException();
        }

        public float GetFloat(int i)
        {
            throw new NotImplementedException();
        }

        public Guid GetGuid(int i)
        {
            throw new NotImplementedException();
        }

        public short GetInt16(int i)
        {
            throw new NotImplementedException();
        }

        public int GetInt32(int i)
        {
            throw new NotImplementedException();
        }

        public long GetInt64(int i)
        {
            throw new NotImplementedException();
        }

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

        public int GetOrdinal(string name)
        {
            int ordinal;
            if (!this._ordinalLookup.TryGetValue(name, out ordinal))
            {
                throw new InvalidOperationException("Unknown parameter name " + name);
            }

            return ordinal;
        }

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

        public object GetValue(int i)
        {
            if (this._dataEnumerator == null)
            {
                throw new ObjectDisposedException("ObjectDataReader");
            }

            return this._accessors[i](this._dataEnumerator.Current);
        }

        public int GetValues(object[] values)
        {
            throw new NotImplementedException();
        }

        public bool IsDBNull(int i)
        {
            throw new NotImplementedException();
        }

        public object this[string name]
        {
            get { throw new NotImplementedException(); }
        }

        public object this[int i]
        {
            get { throw new NotImplementedException(); }
        }

        #endregion


        }

If anyone has any additions, feel free to post them here

Looks good ! It refers to EntityCollectionDataReader !? Where can I find that class ?

Regards, Danny

I knew I forgot something... Edited the original post.

Cheers

Thanks for the share.

Hi,

thanks for the share. Does this also update entities if already existing by any chance?

StampedeXV wrote:

Hi,

thanks for the share. Does this also update entities if already existing by any chance?

No, it does not. It uses the SqlBulkCopy class, which does not allow to do this.

Here is however a nice workaround, which could still give you some speed advantage: http://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists

Cheers

Just a note to say thank you for this; I had a particular problem with approximately 15,000 records to insert. It was taking 120 seconds in LLBLGen, but with this it takes 272ms.

You're welcome