CODEWITHSUNDEEP
c#sql-serverdatatablesqlbulkcopycolumnmappings
wham12
wham12

Reputation: 295

SqlBulkCopy fails on columns with spaces in them

I know that I need to wrap any column names with spaces in square brackets []. I do this when I'm building my DataTable columns.

for (int i = 0; i < columns.Count; i++)
{
   string colname = columns[i].ToString();

   if (colname.Contains(" "))
   {
      colname = "[" + colname + "]";
   }

   dt.Columns.Add(colname);
}

Then, once I've added all of the data as rows in my DataTable, I call an insert function, which uses the below code to map the columns. The columns names are identical in both the DataTable and the database table.

using (var connection = new SqlConnection(ConnectionString))
{
    SqlTransaction transaction = null;
    connection.Open();

    try
    {
        transaction = connection.BeginTransaction();

        using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
        {
            sqlBulkCopy.DestinationTableName = "DBTableName";

            foreach (DataColumn col in dataTable.Columns)
            {
                sqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            sqlBulkCopy.WriteToServer(dataTable);
        }

        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();
    }
}

If I set a breakpoint when it is doing the ColumnMapping, the col.ColumName correctly contains the square brackets around the names with spaces in them.

However when it tries to call the WriteToServer method, it fails and the exception says

The given ColumnName 'Name With Space' does not match up with any column in data source.

Why/where is it stripping out the square brackets, and how do I fix this issue?

Upvotes: 1

Views: 2087

Answers (2)

wham12
wham12

Reputation: 295

Not sure if this has to do with the version of .net or sql server I'm hitting, but I was wrong and didn't need to be adding the square brackets around my column names. It handles the spaces in them correctly anyway.

Upvotes: 2

j.v.
j.v.

Reputation: 997

If you want to insert all columns then you don't need to have mapping. You can remove this:

    foreach (DataColumn col in dataTable.Columns)
    {
            sqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    }

Upvotes: 0

Related Questions