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.
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;
}