stigzler stigzler - 6 months ago 87
Vb.net Question

Update sqlite file from datatable

Aiming to load a table from a db3 (sqlite) file into a datatable. Then load this datatable into a datagridview to allow editing of it. Then I want to save the edited datatable back to the original db3 file - overwriting any changes.

I've got as far as getting the table from the db3 + into the DGV. I wont include any code around the datatable to dgv. Just needing how to write this datable back to the db3 file. How do I do this? Code:

Imports System.Data.SQLite

Public Class DBOps
Public Function ImportGEDb3(Filepath As String) As DataTable
Dim dt As New DataTable("Data")
Dim cnn As New SQLiteConnection("Data Source='" & Filepath & "'")
cnn.Open()
Dim mycommand As New SQLiteCommand(cnn)
mycommand.CommandText = "Select * from Data"
Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
dt.Load(reader)
reader.Close()
cnn.Close()
Return dt
End Function

Public Sub SaveGEDb3(dt as datatable, filepath as string)
' save passed datatable to File above
end sub
end class

Answer

The SQLiteDataAdapter class is the simplest choice for this kind of updates. The method Update of the class scans the DataTable passed and for each row that has the RowState property different from DataRowState.Unchanged executes the appropriate INSERT, DELETE, UPDATE command (if the SELECT command extracts from the DataTable the primary key). So assuming that your SELECT * FROM Data returns also the primary key of the table you could change your code to take advantage of the SQLiteDataAdapter functionality

Imports System.Data.SQLite

Public Class DBOps
    ' Global because you create it in the ImportGEDb3 and use it in the SaveGEDB3 '
    Private daImport As SQLiteDataAdapter

    Public Function ImportGEDb3(Filepath As String) As DataTable
        Dim dt As New DataTable("Data")
        daImport = new SQLiteDataAdapter("Select * from Data", "Data Source='" & Filepath & "'")                
        daImport.Fill(dt)

        ' This is critical, it is the SQLiteCommandBuilder that takes '
        ' the SQLiteDataAdapter SELECT statement and builds the required'
        ' INSERT,UPDATE,DELETE commands.'
        Dim builder = new SQLiteCommandBuilder(daImport)
        Return dt
    End Function

    Public Sub SaveGEDb3(dt as datatable)
      if daImport IsNot Nothing Then
         daImport.Update(dt)
      End If
    End Sub 
End class