What is the most efficient approach for adding rows to a SQL server table using VB.NET?
As a starting point I have included some code below which achieves the desired result, however, I am concerned it will result in a "memory hungry" application - since it calls the DataAdapter.Fill Method which requires the application to hold the entire table in memory prior to adding the row (correct me if I am wrong).
Can anyone provide examples which achieve the same result but with less overhead?
Any advice is much appreciated. Thanks for your time.
Dim strSelect As String
Dim cn As SqlConnection = New SqlConnection(My.Settings.MyConnectionString)
cn.Open()
strSelect = "SELECT * FROM MyTable"
'DataAdapter
Dim daMyTable As SqlDataAdapter = New SqlDataAdapter(strSelect, cn)
'Auto Generate UPDATE, INSERT, and DELETE commands based on SELECT command
Dim autogenMyTable As New SqlCommandBuilder(daMyTable)
'DataSet
Dim dsMyTable As DataSet = New DataSet()
'Fill DataSet
daMyTable.Fill(dsMyTable, "MyTable")
'Temporary DataTable
Dim dtMyTable As DataTable = dsMyTable.Tables("MyTable")
Dim rowMyTable As DataRow
'Add a record.
rowMyTable = dtMyTable.NewRow()
rowMyTable("Field1") = "Test Data"
dtMyTable.Rows.Add(rowMyTable)
'Update the database.
daMyTable.Update(dsMyTable, "MyTable")
cn.Close()
index > Windows Forms Data Controls and Databinding > Adding Rows to SQL Server table