CODEWITHSUNDEEP
sql-serversqlbulkcopy
SchwartzE
SchwartzE

Reputation: 2628

SQLBulkCopy Row Count When Complete

I am using SQLBulkCopy to move large amounts of data. I implemented the notification event to notify me every time a certain number of rows have been processed, but the OnSqlRowsCopied event does not fire when the job is completed. How do I get the total number of rows copied when the SQLBulkCopy writetoserver completes?

Upvotes: 26

Views: 26580

Answers (9)

Eamon Nerbonne
Eamon Nerbonne

Reputation: 48126

As of SqlClient 5.2.0, the internal field _rowsCopied has changed to a long.

The good news is that this was done to render the reflection-based hackery unnecessary. You can now retrieve the number of rows copied via a public, documented API: SqlBulkCopy.RowsCopied64.

No more need for unsupported reflection hacks!

Upvotes: 1

Ari Roth
Ari Roth

Reputation: 5534

Here's what I did -- it's a slight modification of Rahul Modi's solution in this thread (basically it just puts the SqlRowsCopied event inline, which I think is a bit cleaner in this instance than creating the new event handler method):

private long InsetData(DataTable dataTable, SqlConnection connection)
{
   using (SqlBulkCopy copier = new SqlBulkCopy(connection))
   {
      var filesInserted = 0L;

      connection.Open();

      copier.DestinationTableName = "dbo.MyTable";
      copier.NotifyAfter = dataTable.Rows.Count;
      copier.SqlRowsCopied += (s, e) => filesInserted = e.RowsCopied;
      copier.WriteToServer(dataTable);

      connection.Close();

      return filesInserted;
   }
}

Upvotes: 8

Hoang Tran
Hoang Tran

Reputation: 956

This is how I get row count from SqlBulkCopy, it's important that you set NotifyAfter to 1:

var rowsInserted = 0L;
using var sbc = new SqlBulkCopy(dbConnection, SqlBulkCopyOptions.KeepIdentity, transaction);
sbc.NotifyAfter = 1;
sbc.SqlRowsCopied += (s, e) => rowsInserted = e.RowsCopied;
sbc.WriteToServer(dr);
//Get row count
return rowsInserted;

Upvotes: 1

marsze
marsze

Reputation: 17094

The extension method:

(Based on Benzi's answer)

using System;
using System.Reflection;
using System.Data.SqlClient;
using static System.Reflection.BindingFlags;

namespace Extensions
{
    public static class SqlBulkCopyExtensions
    {
        private static readonly Lazy<FieldInfo> _rowsCopiedLazy = new Lazy<FieldInfo>(()
            => typeof(SqlBulkCopy).GetField("_rowsCopied", NonPublic | GetField | Instance));

        public static int GetRowsCopied(this SqlBulkCopy sqlBulkCopy)
            => (int)_rowsCopiedLazy.Value.GetValue(sqlBulkCopy);
    }
}

Tested & works as of .NET 4.6.1

Note that the field's type is int (while the property's type in the event args is long). Not sure what happens if you copy > int.MaxValue rows.

Upvotes: 1

Ronnie Overby
Ronnie Overby

Reputation: 46480

Set NotifyAfter to 1. In the handler for SqlRowsCopied, increment a counter. After WriteToServer completes, read the counter.

Upvotes: 5

Rahul Modi
Rahul Modi

Reputation: 838

By using SqlBulkCopy.SqlRowsCopied Event (Occurs every time that the number of rows specified by the NotifyAfter property has been processed) we can achieve SQLBulkCopy Row Count When Complete.

using (SqlBulkCopy s = new SqlBulkCopy(db.Database.Connection as SqlConnection))
{
  s.SqlRowsCopied += new SqlRowsCopiedEventHandler(sqlBulk_SqlRowsCopied);
  s.BatchSize = csvFileData.Rows.Count;//DataTable
  s.NotifyAfter = csvFileData.Rows.Count;
  foreach (var column in csvFileData.Columns)
     s.ColumnMappings.Add(column.ToString(), column.ToString());
  // Set the timeout.
  s.BulkCopyTimeout = 60;
  s.DestinationTableName = "Employee_Data";
  s.WriteToServer(csvFileData);
}

private static void sqlBulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    long Count = e.RowsCopied;
}

Upvotes: 4

user601543
user601543

Reputation:

For completeness I have implemented as an extension method and included the namespace. Copy and paste this class if you want a fast solution to get the copied count. Note: This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON.

namespace System.Data.SqlClient
{    
    using Reflection;

    public static class SqlBulkCopyExtension
    {
        const String _rowsCopiedFieldName = "_rowsCopied";
        static FieldInfo _rowsCopiedField = null;

        public static int RowsCopiedCount(this SqlBulkCopy bulkCopy)
        {
            if (_rowsCopiedField == null) _rowsCopiedField = typeof(SqlBulkCopy).GetField(_rowsCopiedFieldName, BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);            
            return (int)_rowsCopiedField.GetValue(bulkCopy);
        }
    }
}

Upvotes: 5

Benzi
Benzi

Reputation: 2459

The following hack (using reflection) is an option:

    /// <summary>
    /// Helper class to process the SqlBulkCopy class
    /// </summary>
    static class SqlBulkCopyHelper
    {
        static FieldInfo rowsCopiedField = null;

        /// <summary>
        /// Gets the rows copied from the specified SqlBulkCopy object
        /// </summary>
        /// <param name="bulkCopy">The bulk copy.</param>
        /// <returns></returns>
        public static int GetRowsCopied(SqlBulkCopy bulkCopy)
        {
            if (rowsCopiedField == null)
            {
                rowsCopiedField = typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
            }

            return (int)rowsCopiedField.GetValue(bulkCopy);
        }
    }

And then use the class as follows:

int rowsCopied = SqlBulkCopyHelper.GetRowsCopied(bulkCopyObjectInYourCode);

Hope this helps.

Upvotes: 34

AdaTheDev
AdaTheDev

Reputation: 147324

I think you have to run a COUNT() query on the table after finishing, as in the MSDN example here.

Other than that, can't you tell up front? e.g. if you're passing a DataTable to WriteToServer() then you know how many records by doing a .Rows.Count on it.

Upvotes: 0

Related Questions