SQLBulkCopy Error - The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. String or binary data would be truncated.

Good day!

Normally, in a situation where you want to inject thousands of rows to the database using C# and SQL Server, the optimal solution would be using built-in SQLBulkCopy() function. However, you may encounter the error message "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. String or binary data would be truncated." The code below works but in some instance may throw that kind of exception.

private bool SQLBulkCopy<T>(string SqlConn, List<T> inList, string tableName, ref string errMsg, int optBatchSize = 5000, bool optTableLock = true)
{
 SqlBulkCopyOptions lockType;
 SqlTransaction transaction;

 if (optTableLock)
	lockType = SqlBulkCopyOptions.TableLock;
 else
	lockType = SqlBulkCopyOptions.Default;

 try
 {
	using (var connection = new SqlConnection(SqlConn))
	{
	   connection.Open();
	   transaction = connection.BeginTransaction();

	   using (var bulkCopy = new SqlBulkCopy(connection, lockType, transaction))
	   {
		  bulkCopy.BatchSize = optBatchSize;
		  bulkCopy.DestinationTableName = FixTableName(tableName);

		  try
		  {
			 bulkCopy.WriteToServer(inList.AsDataTable());			 
		  }
		  catch (Exception ex)
		  {
			 ErrorLog.Log("EmployeeInformation.cs", "SQLBulkCopy<T>()", ex, ex.Message);
			 transaction.Rollback();
			 connection.Close();
			 errMsg = ex.Message;
			 return false;
		  }
	   }

	   transaction.Commit();
	}
 }
 catch (Exception ex)
 {
	ErrorLog.Log("EmployeeInformation.cs", "SQLBulkCopy<T>()", ex, ex.Message);
	errMsg = ex.Message;
	return false;
 }

 return true;
}

After investingating, I found the issue which is the code that converts the List object to DataTable that will reorder the properties in my class which is different from the database table's field order. The fix was to use SqlBulkCopy SqlBulkCopyColumnMapping Class which will match the property and the table field in the database.

private bool SQLBulkCopy<T>(string SqlConn, List<T> inList, string tableName, ref string errMsg, int optBatchSize = 5000, bool optTableLock = true)
{
 SqlBulkCopyOptions lockType;
 SqlTransaction transaction;

 if (optTableLock)
	lockType = SqlBulkCopyOptions.TableLock;
 else
	lockType = SqlBulkCopyOptions.Default;

 try
 {
	using (var connection = new SqlConnection(SqlConn))
	{
	   connection.Open();
	   transaction = connection.BeginTransaction();

	   using (var bulkCopy = new SqlBulkCopy(connection, lockType, transaction))
	   {
		  bulkCopy.BatchSize = optBatchSize;
		  bulkCopy.DestinationTableName = FixTableName(tableName);

		  try
		  {
			 var dataTable = inList.AsDataTable();

			 foreach (DataColumn column in dataTable.Columns)
			 {
				bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
			 }

			 bulkCopy.WriteToServer(dataTable);
			 
		  }
		  catch (Exception ex)
		  {
			 ErrorLog.Log("EmployeeInformation.cs", "SQLBulkCopy<T>()", ex, ex.Message);
			 transaction.Rollback();
			 connection.Close();
			 errMsg = ex.Message;
			 return false;
		  }
	   }

	   transaction.Commit();
	}
 }
 catch (Exception ex)
 {
	ErrorLog.Log("EmployeeInformation.cs", "SQLBulkCopy<T>()", ex, ex.Message);
	errMsg = ex.Message;
	return false;
 }

 return true;
}

But before that, this error may occur if your column length is smaller and the data is larger than the field size. So make sure to set the length correctly of the table column. Ex. PartName nvarchar(50) - You need to change that to PartName nvarchar(100) assuming that the max number of characters of a certain part which will be saved to the database is below or equal to 100.

Cheers!