SqlBulkCopyOptions 枚举

定义

按位标志,指定要与 SqlBulkCopy 的实例一起使用的一个或多个选项。Bitwise flag that specifies one or more options to use with an instance of SqlBulkCopy.

此枚举有一个 FlagsAttribute 属性,允许按位组合成员值。

public enum class SqlBulkCopyOptions
[System.Flags]
public enum SqlBulkCopyOptions
type SqlBulkCopyOptions = 
Public Enum SqlBulkCopyOptions
继承
SqlBulkCopyOptions
属性

字段

AllowEncryptedValueModifications 64

如果指定,AllowEncryptedValueModifications 可启用表或数据库之间加密数据的大容量复制,无需对数据解密。When specified, AllowEncryptedValueModifications enables bulk copying of encrypted data between tables or databases, without decrypting the data. 通常情况下,应用程序会从一个表的加密列中选择数据并且不对数据解密(该应用程序会连接到列加密设置关键字设置为已禁用的数据库),然后使用此选项大容量插入数据(仍然为加密状态)。Typically, an application would select data from encrypted columns from one table without decrypting the data (the app would connect to the database with the column encryption setting keyword set to disabled) and then would use this option to bulk insert the data, which is still encrypted. 有关详细信息,请参阅始终加密For more information, see Always Encrypted.

指定 AllowEncryptedValueModifications 时要格外小心,因为这可能导致数据库损坏,原因是驱动程序不会检查数据是否确实已加密,或者是否使用与目标列相同的加密类型、算法和密钥进行了正确加密。Use caution when specifying AllowEncryptedValueModifications as this may lead to corrupting the database because the driver does not check if the data is indeed encrypted, or if it is correctly encrypted using the same encryption type, algorithm and key as the target column.

CheckConstraints 2

插入数据时检查约束。Check constraints while data is being inserted. 默认情况下,不检查约束。By default, constraints are not checked.

Default 0

使用所有选项的默认值。Use the default values for all options.

FireTriggers 16

如果指定,则使服务器激发插入触发器,从而使行插入数据库。When specified, cause the server to fire the insert triggers for the rows being inserted into the database.

KeepIdentity 1

保留源标识值。Preserve source identity values. 未指定标识值时,该值由目标指派。When not specified, identity values are assigned by the destination.

KeepNulls 8

在目标表中保留 null 值,而不考虑默认值的设置。Preserve null values in the destination table regardless of the settings for default values. 如果未指定,由默认值替换 null 值(适用时)。When not specified, null values are replaced by default values where applicable.

TableLock 4

获取批量复制操作持续时间的批量更新锁定。Obtain a bulk update lock for the duration of the bulk copy operation. 如果未指定,则使用行锁定。When not specified, row locks are used.

UseInternalTransaction 32

如果指定,批量复制操作的每一批都将在事务内发生。When specified, each batch of the bulk-copy operation will occur within a transaction. 如果指示该选项,并且还向构造函数提供 SqlTransaction 对象,则发生 ArgumentExceptionIf you indicate this option and also provide a SqlTransaction object to the constructor, an ArgumentException occurs.

示例

下面的控制台应用程序演示如何执行大容量加载, 将源表的 identity 列中的值复制到目标表中的对应列, 而不是为每个行的标识列生成新值。The following console application demonstrates how to perform a bulk load that copies the value in the identity column of the source table to the corresponding column in the destination table, instead of generating a new value for each row's identity column.

若要查看选项如何更改大容量加载的工作方式, 请运行包含 dbo 的示例 。BulkCopyDemoMatchingColumns表为空。To see how the option changes the way the bulk load works, run the sample with the dbo.BulkCopyDemoMatchingColumns table empty. 从源加载所有行。All rows load from the source. 接下来, 再次运行示例而不清空该表。Next, run the sample again without emptying the table. 引发异常, 并且代码向控制台窗口写入一条消息, 通知您由于主键冲突而未添加行。An exception is thrown, and the code writes a message to the console window notifying you that rows were not added because of primary key violations.

重要

除非已按照大容量复制示例设置中所述创建了工作表, 否则此示例将不会运行。This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. 提供此代码是为了演示仅使用SqlBulkCopy的语法。This code is provided to demonstrate the syntax for using SqlBulkCopy only. 如果源表和目标表位于同一个 SQL Server 实例中, 则使用 transact-sql INSERT … SELECT语句复制数据会更加简单快捷。If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, " +
                "ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            // Create the SqlBulkCopy object using a connection string 
            // and the KeepIdentity option. 
            // In the real world you would not use SqlBulkCopy to move
            // data from one table to the other in the same database.
            using (SqlBulkCopy bulkCopy =
                new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the using block.
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static string GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a connection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Perform an initial count on the destination table.
            Dim commandRowCount As New SqlCommand( _
            "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
                sourceConnection)
            Dim countStart As Long = _
               System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("Starting row count = {0}", countStart)

            ' Get data from the source table as a SqlDataReader.
            Dim commandSourceData As SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            ' Create the SqlBulkCopy object using a connection string 
            ' and the KeepIdentity option. 
            ' In the real world you would not use SqlBulkCopy to move
            ' data from one table to the other in the same database.
            Using bulkCopy As SqlBulkCopy = _
              New SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"

                Try
                    ' Write from the source to the destination.
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                    Finally
                        ' Close the SqlDataReader. The SqlBulkCopy
                        ' object is automatically closed at the end
                        ' of the Using block.
                        reader.Close()
                End Try
            End Using

            ' Perform a final count on the destination table
            ' to see how many rows were added.
            Dim countEnd As Long = _
                System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("Ending row count = {0}", countEnd)
            Console.WriteLine("{0} rows were added.", countEnd - countStart)

            Console.WriteLine("Press Enter to finish.")
            Console.ReadLine()
        End Using
    End Sub

    Private Function GetConnectionString() As String
        ' To avoid storing the sourceConnection string in your code, 
        ' you can retrieve it from a configuration file. 
        Return "Data Source=(local);" & _
            "Integrated Security=true;" & _
            "Initial Catalog=AdventureWorks;"
    End Function
End Module

注解

SqlBulkCopyOptions构造实例WriteToServer时, 可以使用枚举来更改该实例的方法的SqlBulkCopy行为方式。You can use the SqlBulkCopyOptions enumeration when you construct a SqlBulkCopy instance to change how the WriteToServer methods for that instance behave.

适用于

另请参阅