Adding Rows to SQL Server table RRS feed

  • 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)
    strSelect = "SELECT * FROM MyTable"
    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)
    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"
    'Update the database.
    daMyTable.Update(dsMyTable, "MyTable")
    Tuesday, October 6, 2009 12:50 PM


All replies