SqlBulkCopy无法访问表 [英] SqlBulkCopy cannot access table

问题描述

读完一个Excel工作表（到transferTable）后，我想使用SqlBulkCopy将数据添加到新表（destinationTable）中，但出现错误：

After reading in an excel-sheet (to transferTable), I want to add that data to a new table (destinationTable) using SqlBulkCopy, but I'm getting the error:

Cannot access destination table 'test'

I已经尝试使用默认表名并使用方括号，但这没有用。

I've tried using the default tablename and using square brackets, but that didn't work.

有任何建议吗？

private void writeToDBButton_Click(object sender, EventArgs e) {
    MakeTable();
    destinationTable.TableName = "test";
    testDBDataSet.Tables.Add("test");

    // Connects to the sql-server using Connection.cs
    SqlConnection connection = Connection.GetConnection();

    using (connection) {
        connection.Open();

        // Uses SqlBulkCopy to copy the data from our transferTable to the destinationTable
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) {
            bulkCopy.DestinationTableName = destinationTable.TableName;

            try {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(transferTable);
                this.dataGridView2.DataSource = destinationTable;
            }
            catch (Exception ex) {
                MessageBox.Show(ex.Message);
            }

            connection.Close();
        }
    }
}

private void saveDBButton_Click(object sender, EventArgs e) {
    this.Validate();
    this.usersBindingSource.EndEdit();
    this.tableAdapterManager.UpdateAll(this.testDBDataSet);
}


private void MakeTable() {
    for (int counter = 0; counter < columns; counter++) {
        DataColumn dummy = new DataColumn();
        dummy.DataType = System.Type.GetType("System.Double");
        destinationTable.Columns.Add(dummy);
    }
}

推荐答案

我的问题有点不同，原来我的表名是SQL中的保留关键字，所以我必须执行以下操作：

My issue was a bit different, turns out my table name was a reserved keyword in SQL so I had to do the following:

bulkCopy.DestinationTableName = $"{schema}.[{tableName}]";

其中 schema 是目标架构，而 tableName 目标表名称

Where schema is the target schema and tableName the target table name

来自文档

DestinationTableName是一个由三部分组成的名称[database]。[owningschema]。[name]。您可以选择使用其数据库和所属架构来限定表名。但是，如果表名使用下划线（_）或任何其他特殊字符，则必须使用括号括住该名称，如（[database]。[owningschema]。[name_01]）

DestinationTableName is a three-part name [database].[owningschema].[name]. You can qualify the table name with its database and owning schema if you choose. However, if the table name uses an underscore ("_") or any other special characters, you must escape the name using surrounding brackets as in ([database].[owningschema].[name_01])

这篇关于SqlBulkCopy无法访问表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！