silverAndroid silverAndroid - 3 months ago 25
C# Question

Unique constraint being violated on the wrong column when using SqlBulkCopy

I'm trying to use

SqlBulkCopy
as a way of doing multiple INSERTs at once but for some reason, I'm getting a unique constraint violation when running
WriteToServer(DataTable)
. The odd thing about this
SqlException
is it's saying that .

My table schema:

CREATE TABLE Product (
ID INT IDENTITY (1, 1) PRIMARY KEY,
Name NVARCHAR(450) UNIQUE NOT NULL, -- Unique constraint being called
BulkInsertID NCHAR(6) -- Column the constraint is being called on
);


The only reason I can think of as to why this is happening is because I mixed up the column names when assigning them inside the
DataColumn
s but I checked them multiple times and I cannot find any issues with them.

Minimal, Complete and Verifiable Example:

class Program
{
private static SqlConnection connection;
private static string connectionURL = "Server=ASUS-X750JA\\DIRECTORY;Database=directory;Integrated Security=True;";
private static Random _random = new Random();

public static SqlConnection openConnection()
{
connection = new SqlConnection(connectionURL);
connection.Open();
Console.WriteLine("Opened connection to DB");
return connection;
}

public static void closeConnection()
{
connection.Close();
Console.WriteLine("Closed connection to DB");
}

static void Main(string[] args)
{
List<string> productNames = new List<string>();
productNames.Add("Diamond");
productNames.Add("Gold");
productNames.Add("Silver");
productNames.Add("Platinum");
productNames.Add("Pearl");
addProducts(productNames);
}

private static void addProducts(List<string> productNames)
{
const string tableName = "Product";
DataTable table = new DataTable(tableName);

string bulkInsertID;
do
{
bulkInsertID = generateID();
} while (isDuplicateBulkInsertID(tableName, bulkInsertID));

DataColumn nameColumn = new DataColumn("Name");
nameColumn.Unique = true;
nameColumn.AllowDBNull = false;

DataColumn bulkInsertIDColumn = new DataColumn("BulkInsertID");
bulkInsertIDColumn.Unique = false;
bulkInsertIDColumn.AllowDBNull = true;

table.Columns.Add(nameColumn);
table.Columns.Add(bulkInsertIDColumn);

foreach (string productName in productNames)
{
DataRow row = table.NewRow();
row[nameColumn] = productName;
row[bulkInsertIDColumn] = bulkInsertID;
table.Rows.Add(row);
}

using (SqlConnection connection = openConnection())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.WriteToServer(table);
}
}
}

/// <summary>
/// Generates random 6-character string but it's not like GUID so may need to check for duplicates
/// </summary>
/// <returns></returns>
public static string generateID()
{
char[] _base62chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz".ToCharArray();
int length = 6;

var sb = new StringBuilder(length);

for (int i = 0; i < length; i++)
sb.Append(_base62chars[_random.Next(62)]);

return sb.ToString();
}

public static bool isDuplicateBulkInsertID(string tableName, string bulkInsertID)
{
string query = string.Format("SELECT BulkInsertID FROM {0} WHERE BulkInsertID = @bulkinsertid", tableName);
SqlCommand command = new SqlCommand(query, openConnection());
SqlParameter bulkInsertIDParam = new SqlParameter("@bulkinsertid", SqlDbType.NChar, bulkInsertID.Length);
bulkInsertIDParam.Value = bulkInsertID;

command.Parameters.Add(bulkInsertIDParam);
command.Prepare();
Task<SqlDataReader> asyncTask = command.ExecuteReaderAsync();
SqlDataReader reader = asyncTask.Result;
bool isDuplicate = reader.HasRows;

closeConnection();
return isDuplicate;
}
}


enter image description here

The unique constraint shown in the screenshot belongs to the
Name
column but the duplicate key value is being sent to the
BulkInsertID
column and I don't know why the error is being thrown.

EDIT: I just changed my schema to use
uniqueidentifier
as the
bulkInsertID
column and changed
row[bulkInsertIDColumn] = bulkInsertID
to
row[bulkInsertIDColumn] = Guid.NewGuid().ToString()
. When I reran my code, I found that the generated GUID ran but when I looked at the table, the GUID was in the name column. So I can conclude it's not a server issue but a problem in the program.

Answer

Because you have a identity column bulk insert is trying to insert nameColumn in to ID (and ignoring it because the column is a identity column) and bulkInsertIDColumn in to Name. Just add the following to your insert to tell it to go to the correct columns.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.ColumnMappings.Add("Name", "Name"); //NEW
    bulkCopy.ColumnMappings.Add("BulkInsertID", "BulkInsertID"); //NEW
    bulkCopy.DestinationTableName = table.TableName;
    bulkCopy.WriteToServer(table);
}

The other option is add a ID column to table and just don't put any values in it.

DataColumn idColumn = new DataColumn("ID");

DataColumn nameColumn = new DataColumn("Name");
//nameColumn.Unique = true; //SqlBulkCopy does not care about these settings.
//nameColumn.AllowDBNull = false;

DataColumn bulkInsertIDColumn = new DataColumn("BulkInsertID");
//bulkInsertIDColumn.Unique = false;
//bulkInsertIDColumn.AllowDBNull = true;

table.Columns.Add(ID);
table.Columns.Add(nameColumn);
table.Columns.Add(bulkInsertIDColumn);

foreach (string productName in productNames)
{
    DataRow row = table.NewRow();
    //We don't do anything with row[idColumn]
    row[nameColumn] = productName;
    row[bulkInsertIDColumn] = bulkInsertID;
    table.Rows.Add(row);
}
Comments