Constraints not being enforced (consistently) while using SqlBulkCopy

c# sql sqlbulkcopy

Question

On our database table we use two unique non-clustered indexes to create a unique constraint across four fields. We use two due to the fact one of the fields, ZipCode is a nullable field. If a record exists on the table with a null entry for ZipCode, we don't want a scenario where a new record matches the three other fields but has a defined ZipCode and gets added (and vice versa).

The problem is it seems using a SqlBulkCopy neither constraint is being enforced as you can add any record you like regardless of what you already have on the table.

In another program we have we use the Entity Framework, since we are loading a significantly smaller amount of data. Using the EF, these constraints work perfectly (they are currently in production). However, using the SqlBulkCopy it appears they are being completely ignored.

T-SQL

CREATE UNIQUE NONCLUSTERED INDEX [UQ_ChannelStateEndDateZipCodeNOTNULL] ON [dbo].[ZipCodeTerritory]
(
    [ChannelCode] ASC,
    [StateCode] ASC,
    [ZipCode] ASC,
    [EndDate] ASC
)
WHERE ([ZipCode] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_ChannelStateEndDateZipCodeISNULL] ON [dbo].[ZipCodeTerritory]
(
    [ChannelCode] ASC,
    [StateCode] ASC,
    [ZipCode] ASC,
    [EndDate] ASC
)
WHERE ([ZipCode] IS NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

C#

Dictionary<DataRow, string> faultyRows = new Dictionary<DataRow, string>();
using (SqlConnection connection = new SqlConnection(connString))
{
    //Open Database connection
    connection.Open();

    //Create transaction objects
    SqlTransaction transaction = connection.BeginTransaction();
    SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
    bulkCopy.DestinationTableName = "ZipCodeTerritory";

    //Load data and initialize datatable
    DataTable dataTable = LoadData(inserts);
    IDataReader reader = dataTable.CreateDataReader();
    DataTable dataSchema = reader.GetSchemaTable();
    DataTable tmpDataTable = InitializeStructure();

    //Create array to hold data being transfered into tmpDataTable
    object[] values = new object[reader.FieldCount];

    while (reader.Read())
    {
        //Clear temp table for single-record use
        tmpDataTable.Rows.Clear();

        //Get data for current row
        reader.GetValues(values);

        //Load values into the temp table
        tmpDataTable.LoadDataRow(values, true);

        //Load one record at a time
        try
        {
            bulkCopy.WriteToServer(tmpDataTable);
            transaction.Commit();
        }
        catch (Exception ex)
        {
            faultyRows.Add(tmpDataTable.Rows[0], ex.Message);
        }
    }
}

EDIT

I've found that this constraint actually is being enforced if the ZipCode field is defined. Any match of the ChannelCode, StateCode, ZipCode, EndDate fields will result in a SqlException with the following Message property (pulled from the specific text I just ran):

Cannot insert duplicate key row in object 'dbo.ZipCodeTerritory' with unique index 'UQ_ChannelStateEndDateZipCodeNOTNULL'. The duplicate key value is (9, WA , 98102 , 9999-12-31)

However, this is the only time I can get one of our two constraints to fire.

Popular Answer

This was occurring due to the method I used transfereing data from a Model object (I am still using the Entity Framework in places) to a DataRow. Below is the method I am using now. Prior to my fix, the ZipCode field, when null, was instead being placed into the DataRow column as an empty string. Since this wasn't technically a null field, the constraint was not triggered.

    private static DataTable LoadData(List<ZipCodeTerritory>zipCodeList, bool update = false)
    {
        DataTable dataTable = InitializeStructure();

        foreach (var zipcode in zipCodeList)
        {
            DataRow row = dataTable.NewRow();

            try
            {
                row[0] = zipcode.ChannelCode.Trim();
                row[1] = zipcode.DrmTerrDesc.Trim();
                row[2] = zipcode.IndDistrnId.Trim();
                row[3] = zipcode.StateCode.Trim();
                row[4] = (string.IsNullOrWhiteSpace(zipcode.ZipCode) ? null : zipcode.ZipCode.Trim());
                row[5] = zipcode.EndDate.Date;
                row[6] = zipcode.EffectiveDate.Date;
                row[7] = zipcode.LastUpdateId;
                row[8] = DateTime.Now.Date;
                row[10] = zipcode.ErrorCodes;
                row[11] = zipcode.Status;

                //Add the Id column if we're doing an update
                if(update) row[9] = zipcode.Id;
            }
            catch (Exception ex)
            {

            }

            dataTable.Rows.Add(row);
        }

        return dataTable;
    }



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