none
sqlbulkcopy notifyafter property used in conjunction with transactions

    質問

  • Morning, im trying to figure out if its possible to use a notifyafter property in conjunction with a transaction with SQLBULKCOPY. I have seen examples of this online yet the MSDN documentation says its not possible.

    MSDN

    I tried to test this last night but couldnt figure out how to get the transaction to fail and roll back.

    using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    {
                            try
                            {
                                using (SqlBulkCopy copy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity |SqlBulkCopyOptions.UseInternalTransaction))
    
                                {
                                    //Column mapping for the required columns.
                                    for (int count = 0; count < numberOfColumns; count++)
                                    {
                                        copy.ColumnMappings.Add(count, count);
                                    }
    
                                    //SQLBulkCopy parameters.
                                    copy.DestinationTableName = dataTableName;
                                    copy.BatchSize = batchSize;
    
                                    copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                                    copy.NotifyAfter = 5;
    
                                    copy.WriteToServer(fullDataTable);
                                }
                            }
                            //Error(s) occured while trying to commit the transaction.
                            catch (InvalidOperationException transactionEx)
                            {
                                //uploadTransaction.Rollback();
                                status = "The current transaction has been rolled back due to an error. \n\r" + transactionEx.Message;
                                MessageBox.Show(status, "Error Message:");
                                alreadyCaught = true;
                                throw;
                            }
                    }

    Any help would be appreciated.

    2012年3月16日 8:04

回答

  • Morning Dan,

    Ive been looking into this a little more, specifically "If an exception occurs during the WriteToServer method, the entire transaction will be rolled back automatically and no rows will be inserted." and im not sure that is correct. If you read the below extract from MSDN, specifically the highlighted section at the bottom it seems to suggest that as long as you set the batch sizing then it will only rollback the batch containing the error. That or ive missed something, in which case id really appreciate it if you could help me understand this.

    You are correct that if your batchsize is less than the number of rows in your DataTable and you specify UseInternalTransaction, then only the batch that encountered the error will be rolled back.  Each batch is in an individual implict transaction when UseInternalTransaction is specified so successful batches are committed.  However, if you use an explicit transaction, then the transaction rather than the batch size determines how rows are committed or rolled back.  Each batch still is all-or-none in both cases; partial batches cannot be committed.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • 回答としてマーク KJian_ 2012年3月22日 8:28
    2012年3月21日 1:19
    モデレータ

すべての返信

  • Hello,

    Please, could you tell us how you have loaded the "fullDataTable" ?I am asking that because of

    "If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data"  that i found in  http://msdn.microsoft.com/en-us/library/tchktcdk.aspx 

    Have a nice day

    PS : i am not sure that this old link could explain your problem but i give you it :

    http://blogs.msdn.com/b/dataaccess/archive/2005/03/22/400459.aspx


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    2012年3月17日 10:37
    モデレータ
  • Morning, im trying to figure out if its possible to use a notifyafter property in conjunction with a transaction with SQLBULKCOPY. I have seen examples of this online yet the MSDN documentation says its not possible.

    MSDN

    I tried to test this last night but couldnt figure out how to get the transaction to fail and roll back.

    I'm not sure what you mean when you say the it is not possible to use NotifyAfter in conjunection with a transaction according to the documentation.  Can you clarify?

    In your sample code, you have specified UseInternalTransaction so all data passed to WriteToServer will be processed in as a single implicit transaction.  If an exception occurs during the WriteToServer method, the entire transaction will be rolled back automatically and no rows will be inserted.  The SqlRowsCopied event will fire every 5 rows until the entire data table is loaded. 

    If you specify an explict transaction instead of UseInternalTransaction, the same behavior will occur except that you'll need to explictly Commit after the WriteToServer and Rollback in the catch block as desired.  It's a good practice to enclose the Rollback in a try/catch.

    If you abort the operation in the SqlRowsCopied handler using the event's Abort property, an OperationAbortedException will be thrown and you'll need to handle that as desired (you are currently only handling InvalidOperationExceptions).  Again, with an explict transaction, you'll need to Rollback in the try/catch.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    2012年3月17日 13:35
    モデレータ
  • Good evening Papy, Thanks very much for taking the time to reply to my post. Im using sqlbulkcopy to upload a .csv/.txt file into the database. Thanks for the links i will bookmark them for when i need to pass data between tables.
    2012年3月17日 20:40
  • Good evening Dan, thanks for taking the time to reply as well. Regarding the link, i was puzzeled with the following

    "No action, such as transaction activity, is supported in the connection during the execution of the bulk copy operation, and it is recommended that you not use the same connection used during the SqlRowsCopied event."

    as i understood it, it meant i wasnt allowed to use transactions as well as the notifyafter property, but i wasnt 100% sure hence why i decided to test it but couldnt figure out how to get the transaction to roll back.

    Regarding your comment on my use of transactions, Thanks very much. Im not infront of my pc atm but i had a transaction object created in a using block, then a nested using block for the SQLBULKCOPY but when i tried to commit the transaction, it kept saying that a transaction already existed. Just dawned on me now, but should the transaction using statement have been nested inside the SQLBULKCOPY using statement?

    Thanks for pointing out the OperationAbortedException. I will need to look into it a little more as i was planning to just catch the aggregateException and scan it for OperationCanceledException.

    Thanks again for your help

    2012年3月17日 20:52
  • Morning Dan,

    Ive been looking into this a little more, specifically "If an exception occurs during the WriteToServer method, the entire transaction will be rolled back automatically and no rows will be inserted." and im not sure that is correct. If you read the below extract from MSDN, specifically the highlighted section at the bottom it seems to suggest that as long as you set the batch sizing then it will only rollback the batch containing the error. That or ive missed something, in which case id really appreciate it if you could help me understand this.

    Thanks for your time.

    Performing a Dedicated Bulk Copy Operation in a Transaction

    By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction. In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

    You can explicitly specify the UseInternalTransaction option in the SqlBulkCopy class constructor to explicitly cause a bulk copy operation to execute in its own transaction, causing each batch of the bulk copy operation to execute within a separate transaction.

    NoteNote

    Since different batches are executed in different transactions, if an error occurs during the bulk copy operation, all the rows in the current batch will be rolled back, but rows from previous batches will remain in the database.

    The following console application is similar to the previous example, with one exception: In this example, the bulk copy operation manages its own transactions. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.

    2012年3月20日 8:56
  • Morning Dan,

    Ive been looking into this a little more, specifically "If an exception occurs during the WriteToServer method, the entire transaction will be rolled back automatically and no rows will be inserted." and im not sure that is correct. If you read the below extract from MSDN, specifically the highlighted section at the bottom it seems to suggest that as long as you set the batch sizing then it will only rollback the batch containing the error. That or ive missed something, in which case id really appreciate it if you could help me understand this.

    You are correct that if your batchsize is less than the number of rows in your DataTable and you specify UseInternalTransaction, then only the batch that encountered the error will be rolled back.  Each batch is in an individual implict transaction when UseInternalTransaction is specified so successful batches are committed.  However, if you use an explicit transaction, then the transaction rather than the batch size determines how rows are committed or rolled back.  Each batch still is all-or-none in both cases; partial batches cannot be committed.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • 回答としてマーク KJian_ 2012年3月22日 8:28
    2012年3月21日 1:19
    モデレータ
  • Thanks very much for your help.
    2012年3月22日 8:29