是否可以一次将整个 VB.NET DataTable 插入到 SQL Server 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1533139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to insert an entire VB.NET DataTable into a SQL Server at once
提问by SqlRyan
I have a SQLClient.DataSet in VB.NET, and I want to insert the entire thing into a SQL Server table without having to do the following:
我在 VB.NET 中有一个 SQLClient.DataSet，我想将整个内容插入到 SQL Server 表中，而无需执行以下操作：
For Each dr as Datarow in MyDataset
Dim sc As New SqlCommand("INSERT INTO MyNewTable " & _
"VALUES (@column1, @column2)", MyDBConnection)
sc.Parameters.AddWithValue("@column1", dr.Item(0))
sc.Parameters.AddWithValue("@column2", dr.Item(1))
sc.ExecuteNonQuery()
Next
Since I've got close to a million rows (all pretty skinny, so it's not much space), I obviously don't want to run this loop and generate a million INSERT statements.
因为我有接近一百万行（都很瘦，所以空间不大），我显然不想运行这个循环并生成一百万条 INSERT 语句。
I know that one option is to use a linked server when I initially fetch the data, since it's coming from another SQL Server, and just have it to the INSERT from there. However, if I already have the data in my application, is there a more efficient way to bulk insert it? Can I somehow pass the DataTable as a parameter to SQL Server and have it sort it out and insert the rows?
我知道一个选项是在我最初获取数据时使用链接服务器，因为它来自另一个 SQL Server，然后从那里将它插入到 INSERT。但是，如果我的应用程序中已经有数据，是否有更有效的方法来批量插入它？我可以以某种方式将 DataTable 作为参数传递给 SQL Server 并让它排序并插入行吗？
回答by anishMarokey
try with SqlBulkCopy
尝试使用SqlBulkCopy
回答by Remus Rusanu
With SQL Server 2008 you can use Table-Valued Parameters:
在 SQL Server 2008 中，您可以使用表值参数：
Dim sc As New SqlCommand(
"INSERT INTO MyNewTable (field1, field2,...)"&
"SELECT field1, field2,... FROM @MyTable;", MyDBConnection)
sc.Parameters.AddWithValue("@MyTable", MyDataset)
sc.ExecuteNonQuery()
回答by C-Pound Guru
Use the SqlDataAdapter's InsertCommand to define your Insert query. Then call the DataAdapter's Update Method with your dataset as a parameter to have it push the data.
使用SqlDataAdapter的 InsertCommand 来定义您的 Insert 查询。然后使用您的数据集作为参数调用 DataAdapter 的更新方法以使其推送数据。
Something like:
就像是：
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter
DA.InsertCommand = New SqlCommand("Insert Into tbl1(fld0, fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter ("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld2", SqlDbType.NVarChar, 50, "fld2"))
DA.Update(dataset1, "tbl1")
回答by CSharpAtl
You could call .WriteXML()on the DataSet and dump that into the database in one insert.
您可以调用.WriteXML()DataSet 并在一次插入中将其转储到数据库中。
回答by Yannick
A way simplier way is to use a table adapter. Then you can use the Fill method to give a datatable as argument :
一种更简单的方法是使用表适配器。然后您可以使用 Fill 方法将数据表作为参数：
Dim oStronglyTypedTable As StronglyTypedDataTable = GetTable() 'A custom function that creates your table from wherever you want)
If Not oStronglyTypedTable Is Nothing Then
Using oAdapter As New StronglyTypedTableAdapter
Dim res As Integer = oAdapter.Update(oStronglyTypedTable)
MsgBox(res & " rows have been updated.")
End Using
End If
Do not forget to change your Database "Copy to Output Directory" property to "Do net copy" and set your connection string properly...
不要忘记将数据库“复制到输出目录”属性更改为“进行网络复制”并正确设置连接字符串...
