/// <summary>
        /// Logic used to perform bulk inserts with SqlServer's BulkCopy
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="sqlSyntaxProvider"></param>
        /// <param name="pd"></param>
        /// <param name="collection"></param>
        /// <returns></returns>
        internal static int BulkInsertRecordsSqlServer <T>(Database db, SqlServerSyntaxProvider sqlSyntaxProvider,
                                                           Database.PocoData pd, IEnumerable <T> collection)
        {
            //NOTE: We need to use the original db.Connection here to create the command, but we need to pass in the typed
            // connection below to the SqlBulkCopy
            using (var cmd = db.CreateCommand(db.Connection, string.Empty))
            {
                using (var copy = new SqlBulkCopy(
                           GetTypedConnection <SqlConnection>(db.Connection),
                           SqlBulkCopyOptions.Default,
                           GetTypedTransaction <SqlTransaction>(cmd.Transaction))
                {
                    BulkCopyTimeout = 10000,
                    DestinationTableName = pd.TableInfo.TableName
                })
                {
                    //var cols = pd.Columns.Where(x => IncludeColumn(pd, x)).Select(x => x.Value).ToArray();

                    using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(collection, pd, sqlSyntaxProvider))
                    {
                        copy.WriteToServer(bulkReader);

                        return(bulkReader.RecordsAffected);
                    }
                }
            }
        }
Exemple #2
0
        /// <summary>
        /// Bulk-insert records using SqlServer BulkCopy method.
        /// </summary>
        /// <typeparam name="T">The type of the records.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
        /// <param name="records">The records.</param>
        /// <returns>The number of records that were inserted.</returns>
        internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
        {
            // create command against the original database.Connection
            using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
            {
                // use typed connection and transactionf or SqlBulkCopy
                var tConnection  = GetTypedConnection <SqlConnection>(database.Connection);
                var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
                var tableName    = pocoData.TableInfo.TableName;

                var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
                if (syntax == null)
                {
                    throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
                }

                using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
                {
                    BulkCopyTimeout = 10000, DestinationTableName = tableName
                })
                    using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
                    {
                        copy.WriteToServer(bulkReader);
                        return(bulkReader.RecordsAffected);
                    }
            }
        }
Exemple #3
0
        /// <summary>
        /// Bulk-insert records using SqlServer BulkCopy method.
        /// </summary>
        /// <typeparam name="T">The type of the records.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
        /// <param name="records">The records.</param>
        /// <returns>The number of records that were inserted.</returns>
        internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
        {
            // create command against the original database.Connection
            using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
            {
                // use typed connection and transactionf or SqlBulkCopy
                var tConnection  = GetTypedConnection <SqlConnection>(database.Connection);
                var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
                var tableName    = pocoData.TableInfo.TableName;

                var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
                if (syntax == null)
                {
                    throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
                }

                using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
                {
                    BulkCopyTimeout = 10000, DestinationTableName = tableName
                })
                    using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
                    {
                        //we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared
                        //to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses
                        //the names instead of their ordering.
                        foreach (var col in bulkReader.ColumnMappings)
                        {
                            copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn);
                        }

                        copy.WriteToServer(bulkReader);
                        return(bulkReader.RecordsAffected);
                    }
            }
        }
        /// <summary>
        /// Bulk-insert records using SqlServer BulkCopy method.
        /// </summary>
        /// <typeparam name="T">The type of the records.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
        /// <param name="records">The records.</param>
        /// <returns>The number of records that were inserted.</returns>
        internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
        {
            // TODO: The main reason this exists is because the NPoco InsertBulk method doesn't return the number of items.
            // It is worth investigating the performance of this vs NPoco's because we use a custom BulkDataReader
            // which in theory should be more efficient than NPocos way of building up an in-memory DataTable.

            // create command against the original database.Connection
            using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
            {
                // use typed connection and transactionf or SqlBulkCopy
                var tConnection  = GetTypedConnection <SqlConnection>(database.Connection);
                var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
                var tableName    = pocoData.TableInfo.TableName;

                var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
                if (syntax == null)
                {
                    throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
                }

                using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
                {
                    BulkCopyTimeout = 0, // 0 = no bulk copy timeout. If a timeout occurs it will be an connection/command timeout.
                    DestinationTableName = tableName,
                    // be consistent with NPoco: https://github.com/schotime/NPoco/blob/5117a55fde57547e928246c044fd40bd00b2d7d1/src/NPoco.SqlServer/SqlBulkCopyHelper.cs#L50
                    BatchSize = 4096
                })
                    using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
                    {
                        //we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared
                        //to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses
                        //the names instead of their ordering.
                        foreach (var col in bulkReader.ColumnMappings)
                        {
                            copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn);
                        }

                        copy.WriteToServer(bulkReader);
                        return(bulkReader.RecordsAffected);
                    }
            }
        }