Description
SQLite is a lightweight portable database. mainly to store small - medium data. if you have large data you must use a proper database like mysql , sqlserver or oracle. in this article will explain how to CRUD SQLite in VB.NET and display into datagridview. i will make this tutorial simply , very easy to understand.
HowTo
1. Download SQLite library for .NET, you can find HERE (your version must same with your visual studio)
2. After download and install you must add reference that library, open your visual studio there's 2 refference you must include.
-go Project > Add Refference > go tab NET > double click System.Configuration
System Configuration
- go Project > add Refference > go Tab Browse > find file system.data.sqlite.dll , default installation :
"C:\Program Files\System.Data.SQLite\[VERSION]\bin"
SQLite Library
3. Download SQLitebrowser , you can find HERE
4. After download and install , now we must create database and table from SQLitebrowser. now open the SQLitebrowser application.
- Click "New Database", and save to your visual studio either DEBUG or RELEASE (as you wish). it will create new file mydatabase.db (That's your portable database file). if you open that file using notepad you will see the data cannot be readable.
- now, create your table by click "Create Table" button in "Database Structure Tab"and field structure, you can explore this feature, i won't explain this.
- after finish you can add record by click "Browse Data Tab" and click "New Record" Button, you can add data by fill the datagrid.
- if you finish add data , now CLOSE DATABASE AND SAVE. if you forgot close you can't connect the table (don't worry it's first time only).
5. back to visual studio. now mydatabase.db has one record, next step we just using SQL language to manipulation or edit the database.
- add 1 button and 1 datagridview to form.
- double click the button and you will trigger event click. add this code below :
'-------start here----------------------------------------------------------------------------------
Imports System.Data.SQLite
Dim mydata As New DataTable
Dim myconnection As New SQLiteConnection("Data Source=PATH_TO_DB\mydatabase.db")
Dim mycommand, reader
myconnection.Open()
mycommand = New SQLiteCommand(myconnection)
mycommand.CommandText = "select * from mytable"
reader = mycommand.ExecuteReader()
mydata.Load(reader)
reader.Close()
myconnection.Close()
datagridview.DataSource = mydata
'try show one data
'MsgBox(mydata.Rows(0)("YOUR FIELD").ToString)
simple right ? hehe :)