SqlBulkCopyOptions SqlBulkCopyOptions SqlBulkCopyOptions SqlBulkCopyOptions Enum

定义

按位标志,指定要与 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
继承
SqlBulkCopyOptionsSqlBulkCopyOptionsSqlBulkCopyOptionsSqlBulkCopyOptions
属性

字段

AllowEncryptedValueModifications AllowEncryptedValueModifications AllowEncryptedValueModifications 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 CheckConstraints CheckConstraints CheckConstraints 2

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

Default Default Default Default 0

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

FireTriggers FireTriggers FireTriggers FireTriggers 16

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

KeepIdentity KeepIdentity KeepIdentity KeepIdentity 1

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

KeepNulls KeepNulls KeepNulls 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 TableLock TableLock TableLock 4

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

UseInternalTransaction UseInternalTransaction UseInternalTransaction 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.

示例

以下控制台应用程序演示如何执行大容量加载的标识列的源表中的值复制到目标表,而不是生成每个行的标识列的新值中的相应列。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 SQLINSERT … 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枚举在构造时SqlBulkCopy实例,若要更改如何WriteToServer为该实例的方法的行为。You can use the SqlBulkCopyOptions enumeration when you construct a SqlBulkCopy instance to change how the WriteToServer methods for that instance behave.

适用于

另请参阅