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:
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 & "'")
Dim mycommand As New SQLiteCommand(cnn)
mycommand.CommandText = "Select * from Data"
Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
Public Sub SaveGEDb3(dt as datatable, filepath as string)
' save passed datatable to File above
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