Way to figure out if SqlBulkCopy operation inserted values or not

c# sqlbulkcopy

Question

I have a SqlBulkCopy method that does inserts. The destination table to which it does inserts has a Unique Key constraint defined, so the bulk copy does not insert duplicates. But since the restriction is only at the database level, I am not able to programmatically verify if the insert was done or not. Here is my method:

string connect = "user id=*********; password=*******; server=.\\SQLEXPRESS;database=*********;";
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connect, SqlBulkCopyOptions.UseInternalTransaction))
    {
        bulkCopy.DestinationTableName = "dbo.BaseTermPrice";
        bulkCopy.ColumnMappings.Add("BaseID", "BaseID");
        bulkCopy.ColumnMappings.Add("ContractDate", "ContractDate");
        bulkCopy.ColumnMappings.Add("TermID", "TermID");
        bulkCopy.ColumnMappings.Add("Price", "Price");
        bulkCopy.ColumnMappings.Add("PeakType", "PeakType");
        try
        {
            // Write from the source to the destination.
            bulkCopy.WriteToServer(ds.Tables[0]);
        }
        catch (SqlException ex)
        {
            success = false;
            logger.Error(ex.Message);
        }
        catch (Exception ex)
        {
            success = false;
            logger.Error(ex.Message);
        }
    }

There is no exception from here when no insert takes place. How will I determine this so that I can inform the user he is trying to insert duplicates?

My Unique Key constraint:

ALTER TABLE [dbo].[BaseTermPrice] ADD  CONSTRAINT [UK_BaseTermPrice] UNIQUE NONCLUSTERED 
(
[BaseID] ASC,
[ContractDate] ASC,
[TermID] ASC,
[PeakType] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]
GO

Accepted Answer

There is no exception from here when no insert takes place.

Did you make a unique index IGNORE_DUP_KEY = ON? This is bad practice because it hides data errors.

If this option is false, a bulk insert operation will throw an exception. If it seems it does not, then your error logging is broken. Set the debugger to break on all exceptions.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why