请勿创建
DataTable以通过BulkCopy进行加载。这对于较小的数据集是可以接受的解决方案，但在调用数据库之前将所有1000万行加载到内存中绝对没有必要。
除了
BCP/
BULK INSERT/
OPENROWSET(BULK...)之外，最好的选择是通过表值参数（TVP）将文件内容流式传输到数据库中。通过使用TVP，您可以打开文件，读取行并发送行，直到完成，然后关闭文件。此方法的内存占用量仅为一行。我写了一篇文章
从应用程序将数据流式传输到SQL Server 2008，其中有一个非常相似的示例。
结构的简单概述如下。我假设与上面显示的问题相同的导入表和字段名称。
所需的数据库对象：
CREATE TYPE ImportStructure AS TABLE (Field VARCHAR(MAX));
GO
CREATE PROCEDURE dbo.ImportData (
@ImportTable dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;
TRUNCATE TABLE dbo.DATAs;
INSERT INTO dbo.DATAs (DatasField)
SELECT Field
FROM @ImportTable;
GO
以下是使用C#应用程序代码来利用上述SQL对象的方法。注意，与其填充一个对象（如DataTable），然后执行存储过程不同，在这种方法中，执行存储过程会启动读取文件内容的过程。存储过程的输入参数不是一个变量；它是一个方法
GetFileContents的返回值。当
SqlCommand调用
ExecuteNonQuery时，将调用该方法，打开文件，读取一行，并通过
IEnumerable<SqlDataRecord>和
yield return结构将该行发送到SQL Server，然后关闭文件。存储过程只看到一个表变量@ImportTable，可以在数据开始传输时立即访问（
注意：即使不是全部内容，数据也会在tempdb中短暂存储）。
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;
private static IEnumerable<SqlDataRecord> GetFileContents()
{
SqlMetaData[] _TvpSchema = new SqlMetaData[] {
new SqlMetaData("Field", SqlDbType.VarChar, SqlMetaData.Max)
};
SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);
StreamReader _FileReader = null;
try
{
_FileReader = new StreamReader("{filePath}");
while (!_FileReader.EndOfStream)
{
_DataRecord.SetString(0, _FileReader.ReadLine());
yield return _DataRecord;
}
}
finally
{
_FileReader.Close();
}
}
上面的
GetFileContents方法被用作下面所示的存储过程的输入参数值：
public static void test()
{
SqlConnection _Connection = new SqlConnection("{connection string}");
SqlCommand _Command = new SqlCommand("ImportData", _Connection);
_Command.CommandType = CommandType.StoredProcedure;
SqlParameter _TVParam = new SqlParameter();
_TVParam.ParameterName = "@ImportTable";
_TVParam.TypeName = "dbo.ImportStructure";
_TVParam.SqlDbType = SqlDbType.Structured;
_TVParam.Value = GetFileContents();
_Command.Parameters.Add(_TVParam);
try
{
_Connection.Open();
_Command.ExecuteNonQuery();
}
finally
{
_Connection.Close();
}
return;
}
附加说明：
- 通过一些修改，上述的 C# 代码可用于批量处理数据。
- 通过一些小修改，上述的 C# 代码可以适应发送多个字段（上面“流式数据…”文章中展示的示例传入了2个字段）。
- 您还可以在过程的
SELECT 语句中操作每个记录的值。
- 您还可以通过在过程中使用 WHERE 条件来过滤行。
- 您可以多次访问 TVP 表变量；它是只读的但不是“前向只读”的。
- TVP 相对于
SqlBulkCopy 的优势：
SqlBulkCopy 仅支持插入，而使用 TVP 可以以任何方式使用数据：您可以调用 MERGE; 您可以根据某些条件 DELETE;您可以将数据拆分成多个表等等。- 由于 TVP 不仅支持插入，因此无需单独的临时表来转储数据。
- 您可以通过调用
ExecuteReader 而不是 ExecuteNonQuery 从数据库中获取数据。例如，如果导入表 DATAs 上有一个 IDENTITY 字段，则可以在 INSERT 语句中添加 OUTPUT 子句以传回 INSERTED.[ID]（假设 ID 是 IDENTITY 字段的名称）。或者您可以传回完全不同查询的结果，或两者都可以，因为多个结果集可以通过 Reader.NextResult() 发送和访问。使用 SqlBulkCopy 时无法从数据库中获取信息，但是这里有几个关于人们想要做到这一点（至少是关于新创建的 IDENTITY 值）的 S.O. 上的问题。
- 有关为什么在整个过程中它有时更快，即使将数据从磁盘读取到 SQL Server 的速度稍慢，请参见 SQL Server 客户咨询团队的此白皮书：Maximizing Throughput with TVP
BULK INSERT吗？- http://msdn.microsoft.com/zh-cn/library/ms188365.aspx? - Kami