none
Adding Rows to SQL Server table

    Question

  • 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()
    Tuesday, October 06, 2009 12:50 PM

Answers

All replies