Is it possible to insert an entire VB.NET DataTable into a SQL Server at once
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:
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.
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 ha开发者_如何学Gove 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?
try with SqlBulkCopy
With SQL Server 2008 you can use Table-Valued Parameters:
Dim sc As New SqlCommand(
"INSERT INTO MyNewTable (field1, field2,...)"&
"SELECT field1, field2,... FROM @MyTable;", MyDBConnection)
sc.Parameters.AddWithValue("@MyTable", MyDataset)
sc.ExecuteNonQuery()
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.
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")
You could call .WriteXML()
on the DataSet and dump that into the database in one insert.
A way simplier way is to use a table adapter. Then you can use the Fill method to give a datatable as argument :
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...
精彩评论