Example #1
0
        /// <summary>
        /// Inserts entities into table <typeparamref name="T"/>s (by default).
        /// </summary>
        /// <typeparam name="T">The type being inserted.</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="data">Entities to insert</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
        /// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
        public static void BulkInsert <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
        {
            var type               = typeof(T);
            var tableName          = TableMapper.GetTableName(type);
            var allProperties      = PropertiesCache.TypePropertiesCache(type);
            var keyProperties      = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);

            var allPropertiesExceptKeyAndComputed       = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
            var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed);
            var tempToBeInserted = $"#{tableName}_TempInsert";

            connection.Execute($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {tableName} target WITH(NOLOCK);", null, transaction);

            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.BulkCopyTimeout      = bulkCopyTimeout;
                bulkCopy.BatchSize            = batchSize;
                bulkCopy.DestinationTableName = tempToBeInserted;
                bulkCopy.WriteToServer(ToDataTable(data, tableName, allPropertiesExceptKeyAndComputed).CreateDataReader());
            }

            connection.Execute($@"
                INSERT INTO {tableName}({allPropertiesExceptKeyAndComputedString}) 
                SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}

                DROP TABLE {tempToBeInserted};", null, transaction);
        }
Example #2
0
        /// <summary>
        /// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously.
        /// </summary>
        /// <typeparam name="T">The type being inserted.</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="data">Entities to insert</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
        /// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
        public static async Task BulkInsertAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
        {
            var type               = typeof(T);
            var tableName          = TableMapper.GetTableName(type);
            var allProperties      = PropertiesCache.TypePropertiesCache(type);
            var keyProperties      = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns            = PropertiesCache.GetColumnNamesCache(type);

            var allPropertiesExceptKeyAndComputed       = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
            var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed, columns);
            var tempToBeInserted = $"#TempInsert_{tableName}".Replace(".", string.Empty);

            await connection.ExecuteAsync($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction, commandTimeout : connection.ConnectionTimeout);

            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.BulkCopyTimeout      = bulkCopyTimeout;
                bulkCopy.BatchSize            = batchSize;
                bulkCopy.DestinationTableName = tempToBeInserted;
                await bulkCopy.WriteToServerAsync(ToDataTable(data, allPropertiesExceptKeyAndComputed).CreateDataReader());
            }

            await connection.ExecuteAsync($@"
                INSERT INTO {FormatTableName(tableName)}({allPropertiesExceptKeyAndComputedString}) 
                SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}

                DROP TABLE {tempToBeInserted};", null, transaction, commandTimeout : connection.ConnectionTimeout);
        }
Example #3
0
        /// <summary>
        /// Inserts entities into table <typeparamref name="T"/>s (by default) returns inserted entities.
        /// </summary>
        /// <typeparam name="T">The element type of the array</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="data">Entities to insert</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
        /// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
        /// <param name="identityInsert">Usage of db generated ids. By default DB generated IDs are used (identityInsert=false)</param>
        /// <returns>Inserted entities</returns>
        public static IEnumerable <T> BulkInsertAndSelect <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30, bool identityInsert = false)
        {
            var type               = typeof(T);
            var tableName          = TableMapper.GetTableName(type);
            var allProperties      = PropertiesCache.TypePropertiesCache(type);
            var keyProperties      = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns            = PropertiesCache.GetColumnNamesCache(type);

            if (keyProperties.Count == 0)
            {
                var dataList = data.ToList();
                connection.BulkInsert(dataList, transaction, batchSize, bulkCopyTimeout);
                return(dataList);
            }

            var insertProperties = allProperties.Except(computedProperties).ToList();

            if (!identityInsert)
            {
                insertProperties = insertProperties.Except(keyProperties).ToList();
            }

            var(identityInsertOn, identityInsertOff, sqlBulkCopyOptions) = GetIdentityInsertOptions(identityInsert, tableName);

            var keyPropertiesString         = GetColumnsStringSqlServer(keyProperties, columns);
            var keyPropertiesInsertedString = GetColumnsStringSqlServer(keyProperties, columns, "inserted.");
            var insertPropertiesString      = GetColumnsStringSqlServer(insertProperties, columns);
            var allPropertiesString         = GetColumnsStringSqlServer(allProperties, columns, "target.");

            var tempToBeInserted         = $"#TempInsert_{tableName}".Replace(".", string.Empty);
            var tempInsertedWithIdentity = $"@TempInserted_{tableName}".Replace(".", string.Empty);

            connection.Execute($"SELECT TOP 0 {insertPropertiesString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction);

            using (var bulkCopy = new SqlBulkCopy(connection, sqlBulkCopyOptions, transaction))
            {
                bulkCopy.BulkCopyTimeout      = bulkCopyTimeout;
                bulkCopy.BatchSize            = batchSize;
                bulkCopy.DestinationTableName = tempToBeInserted;
                bulkCopy.WriteToServer(ToDataTable(data, insertProperties).CreateDataReader());
            }

            var table  = string.Join(", ", keyProperties.Select(k => $"[{k.Name }] bigint"));
            var joinOn = string.Join(" AND ", keyProperties.Select(k => $"target.[{k.Name }] = ins.[{k.Name }]"));

            return(connection.Query <T>($@"
                {identityInsertOn}
                DECLARE {tempInsertedWithIdentity} TABLE ({table})
                INSERT INTO {FormatTableName(tableName)}({insertPropertiesString}) 
                OUTPUT {keyPropertiesInsertedString} INTO {tempInsertedWithIdentity} ({keyPropertiesString})
                SELECT {insertPropertiesString} FROM {tempToBeInserted}
                {identityInsertOff}

                SELECT {allPropertiesString}
                FROM {FormatTableName(tableName)} target INNER JOIN {tempInsertedWithIdentity} ins ON {joinOn}
                
                DROP TABLE {tempToBeInserted};", null, transaction));
        }
Example #4
0
        /// <summary>
        /// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously and returns inserted entities.
        /// </summary>
        /// <typeparam name="T">The type being inserted.</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="data">Entities to insert</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
        /// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
        /// <returns>Inserted entities</returns>
        public static async Task <IEnumerable <T> > BulkInsertAndSelectAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
        {
            var type               = typeof(T);
            var tableName          = TableMapper.GetTableName(type);
            var allProperties      = PropertiesCache.TypePropertiesCache(type);
            var keyProperties      = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns            = PropertiesCache.NewColumnNamesCache(type);

            if (keyProperties.Count == 0)
            {
                var dataList = data.ToList();
                await connection.BulkInsertAsync(dataList, transaction, batchSize, bulkCopyTimeout);

                return(dataList);
            }

            var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();

            var keyPropertiesString                     = GetColumnsStringSqlServer(keyProperties, columns);
            var keyPropertiesInsertedString             = GetColumnsStringSqlServer(keyProperties, columns, "inserted.");
            var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed, columns);
            var allPropertiesString                     = GetColumnsStringSqlServer(allProperties, columns, "target.");

            var tempToBeInserted         = $"#{tableName}_TempInsert".Replace(".", string.Empty);
            var tempInsertedWithIdentity = $"@{tableName}_TempInserted".Replace(".", string.Empty);

            await connection.ExecuteAsync($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {tableName} target WITH(NOLOCK);", null, transaction);

            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.BulkCopyTimeout      = bulkCopyTimeout;
                bulkCopy.BatchSize            = batchSize;
                bulkCopy.DestinationTableName = tempToBeInserted;
                await bulkCopy.WriteToServerAsync(ToDataTable(data, tableName, allPropertiesExceptKeyAndComputed).CreateDataReader());
            }

            var table  = string.Join(", ", keyProperties.Select(k => $"[{k.Name }] bigint"));
            var joinOn = string.Join(" AND ", keyProperties.Select(k => $"target.[{k.Name }] = ins.[{k.Name }]"));

            return(await connection.QueryAsync <T>($@"
                DECLARE {tempInsertedWithIdentity} TABLE ({table})
                INSERT INTO {tableName}({allPropertiesExceptKeyAndComputedString}) 
                OUTPUT {keyPropertiesInsertedString} INTO {tempInsertedWithIdentity} ({keyPropertiesString})
                SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}

                SELECT {allPropertiesString}
                FROM {tableName} target INNER JOIN {tempInsertedWithIdentity} ins ON {joinOn}

                DROP TABLE {tempToBeInserted};", null, transaction));
        }
Example #5
0
        /// <summary>
        /// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously.
        /// </summary>
        /// <typeparam name="T">The type being inserted.</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="data">Entities to insert</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
        /// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
        /// <param name="identityInsert">Usage of db generated ids. By default DB generated IDs are used (identityInsert=false)</param>
        public static async Task BulkInsertAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30, bool identityInsert = false)
        {
            var type               = typeof(T);
            var tableName          = TableMapper.GetTableName(type);
            var allProperties      = PropertiesCache.TypePropertiesCache(type);
            var keyProperties      = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns            = PropertiesCache.GetColumnNamesCache(type);

            var insertProperties = allProperties.Except(computedProperties).ToList();

            if (!identityInsert)
            {
                insertProperties = insertProperties.Except(keyProperties).ToList();
            }

            var(identityInsertOn, identityInsertOff, sqlBulkCopyOptions) = GetIdentityInsertOptions(identityInsert, tableName);

            var insertPropertiesString = GetColumnsStringSqlServer(insertProperties, columns);
            var tempToBeInserted       = $"#TempInsert_{tableName}".Replace(".", string.Empty);

            await connection.ExecuteAsync($@"SELECT TOP 0 {insertPropertiesString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction);

            using (var bulkCopy = new SqlBulkCopy(connection, sqlBulkCopyOptions, transaction))
            {
                bulkCopy.BulkCopyTimeout      = bulkCopyTimeout;
                bulkCopy.BatchSize            = batchSize;
                bulkCopy.DestinationTableName = tempToBeInserted;
                await bulkCopy.WriteToServerAsync(ToDataTable(data, insertProperties).CreateDataReader());
            }

            await connection.ExecuteAsync($@"
                {identityInsertOn}
                INSERT INTO {FormatTableName(tableName)}({insertPropertiesString}) 
                SELECT {insertPropertiesString} FROM {tempToBeInserted}
                {identityInsertOff}

                DROP TABLE {tempToBeInserted};", null, transaction);
        }