fedeteka fedeteka - 1 year ago 160
Vb.net Question

SQLite database locked after showing a DataGrid

I have a Sub to show data on a DataGridView but after this sub I can't add files because I get a database is locked message.

I tryed using


But is the same. Also I tryed to make the Using block as small as possible.

Any clue why the database is locked after this code?

Private Sub LoadDataGridView1()

Dim connLoadDataGrid = New SQLiteConnection("Data Source=myDataBase.sqlite;Version=3")

Using (connLoadDataGrid)


Dim sql = "SELECT * FROM users"

Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, connLoadDataGrid)

'Load data on a Table
Dim da As New SQLiteDataAdapter
da.SelectCommand = cmdDataGrid
Dim dt As New DataTable
DataGridView1.DataSource = dt

'Change the name of the columns
With DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "ID"
.Columns(1).HeaderCell.Value = "Name"
.Columns(2).HeaderCell.Value = "Type"
.Columns(3).HeaderCell.Value = "Notes"
End With

'Autoadjust the columns

Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()

End Using

Catch ex As Exception
End Try

End Sub

Answer Source

Fundamentally, your code leaves several other things undisposed: the DbCommand, DataAdapter and most importantly, an odd DataReader open and alive at the end. Anything which implements the Dispose method ought to be created and used in a Using block to be sure it is disposed and resources released.

Now, this will implement code which violates that rule to create a DataAdapter which will live for the life of your app/form and be able to perform all the DB operations for you:

' form/class level objects
Private daLite As SQLiteDataAdapter
Private dtLite As DataTable
Private LiteConn As SQLiteConnection

Initializing the DB provider objects:

' in this case, a persistent Connex is used
LiteConn = New SQLiteConnection(LiteConnStr)

' spell out your SQL
Dim SQL = "SELECT Id, Name, Fish, Bird, Color, Price FROM Sample"

' persistent datatadapter
daLite = New SQLiteDataAdapter(SQL, LiteConn)
dtLite = New DataTable


' teach the DA how to do everything
Dim cb As New SQLiteCommandBuilder(daLite)
daLite.InsertCommand = cb.GetInsertCommand
daLite.UpdateCommand = cb.GetUpdateCommand
daLite.DeleteCommand = cb.GetDeleteCommand

' load schema, table
daLite.FillSchema(dtLite, SchemaType.Source)

dgv1.DataSource = dtLite
' open and close for each use

At the end, your DataAdapter will know how to Insert, update and delete from the DB. After the user adds perhaps several rows to the DataGridView, changes some and maybe deletes one or two, update the database:

Dim rows = daLite.Update(dtLite)

Important notes

  • You need to retain and Open/Close the connection with SQLite which is not the case with all the other DB Providers.
  • You should spell out your SQL, that controls the order of the DGV column display. For this to work, the Primary Key needs to be part of the SQL.
    • Incidentally, changing the DGV HeaderText doesnt change the column name, just the displayed text.
  • A persistent DataTable is needed to store the data. Each row has a RowState indicating what the user has done to it (change, added etc). The DataAdapter uses this state to know what to do for each row when it comes time to update.
    • Updates need not be done one by one by one! Any number can be done at once.
    • When using something like this for bulk imports, a Transaction will speed it up.
  • The CommandBuilder is used to create the other DB commands from the SELECT SQL. In the case of SQLite, this cannot be disposed or the DataAdpater will loose the command(s). You can also specify/type in the SQL for the other commands if you prefer.

A side benefit is that you will also be able to "refresh" the rows. If there is more than one user or changes are being made outside the app (such as by you in a UI DB browser). Performing daLite.Fill(dtLite) again later, picks up just those changes.

You do need to remember to Open/Close the connection as you use it. Also, this only works on simple one table queries as yours appears to be.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download