Create ADO.NET SQLite Database Programmatically by using VB.NET
4.10/5 (3 votes)
Create ADO.NET SQLite Database programmatically by using VB.NET
Introduction
This article shows you how to create an ADO.NET SQLite Database programmatically by using VB.NET.
I'm using ADO.NET Data Provider for SQLite. The origin from Finisar.SQLite, source code and binaries DLL files can be downloaded at Finisar.SQLite website. This example uses x86 binaries.
When starting a new project, do not forget to import:
SQLite.NET, add at References > Browse, then select the SQLite.NET.dllSystem.Configuration, add at References > Assemblies > Framework
Do not forget to set Compile > Target CPU > x86. Just in case getting unhappy error, try to install Microsoft Visual C++ Runtime Library.
And also, this file will have to put into the debug and release folder:
- SQLite3.dll
- SQLite.dll
Background
I came up with this idea because my apps need a database. Code implementation is almost the same for Winform and ASP.NET project, it just needs some work.
Using the Code
So, let's start looking into the coding.
For the full source code, better download the source code and look for these files:
- App.config
- CloudAppSettings.vb
- CloudCreateSQLiteDatabase.vb
We start with the app.config. This are the few settings to add into the app.config:
<appSettings>
<!-- database root-->
<add key="dbroot" value="database"/>
<!-- database root-->
<!-- SQLite Database-->
<!-- Database_Provider-->
<add key="Database_sqlite_Provider" value="Data Source=%mdbfilepath%;Version=3;"/>
<!-- Database_Provider-->
<!-- database_sqlite filename only without extension -->
<add key="Database_sqlite_sample1" value="sample1"/>
<add key="Database_sqlite_sample2" value="sample2"/>
<!-- database_sqlite filename only -->
<!-- SQLite Database-->
</appSettings>
dbroot, is the folder where you keep the database file, just the folder name. In this case, it is database.
<!-- database root-->
<add key="dbroot" value="database"/>
<!-- database root-->
Database_sqlite_Provider is the default value.
<!-- Database_Provider -->
<add key="Database_sqlite_Provider" value="Data Source=%mdbfilepath%;Version=3;"/>
<!-- Database_Provider -->
Name the database file and the key. In this case, the key is Database_sqlite_sample1 and the filename is sample1. You have to add more item here and you have one more database file.
<!-- database_sqlite filename only without extension -->
<add key="Database_sqlite_sample1" value="sample1"/>
<add key="Database_sqlite_sample2" value="sample2"/>
<!-- database_sqlite filename only -->
Now, here is the code.
To create a new database, all you need to do is just one line of code.
CloudCreateSQLiteDatabase.CreateNewDatabase(CloudCreateSQLiteDatabase.SQLiteDatabaseType.sample1)
CloudCreateSQLiteDatabase.CreateNewDatabase(CloudCreateSQLiteDatabase.SQLiteDatabaseType.sample2)
But, before that, you have some work to do.
Step 1: Add your database id. If you have 3,4,5 database, just put any name you like, example sample3=2.
Public Enum SQLiteDatabaseType
sample1 = 0
sample2 = 1
End Enum
Step 2: Get the database file name from the app.config to create a valid file path:
- dbroot, get the database folder path
- check the path, if it does not exist, then create it
- get the database file name from App.config
- create a valid database file path
Shared Function SQLiteConnDatabaseFilePath(stype As SQLiteDatabaseType) As String
Dim dbroot As String = CloudAppSettings.GetValidDatabaseRoot
Dim dbfilename As String = ""
'check if the db folder not exist then create it
If Directory.Exists(dbroot) = False Then
Directory.CreateDirectory(dbroot)
End If
'get the access file name from app.config
Select Case stype
Case SQLiteDatabaseType.sample1
dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sqlite_sample1")
Case SQLiteDatabaseType.sample2
dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sqlite_sample2")
End Select
dbfilename = String.Format("{0}{1}", dbfilename, ".sqlite")
dbroot = String.Format("{0}{1}", dbroot, dbfilename)
Return dbroot
End Function
Step 2.1: You need to change only this section if have any new database added.
Select Case stype
Case SQLiteDatabaseType.sample1
dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sqlite_sample1")
Case SQLiteDatabaseType.sample2
dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sqlite_sample2")
End Select
Step 3: Write the database table command if your SQLiteDatabaseType Enum has SQLiteDatabaseType.sample3, so you have to write something for it.
Note: Auto increment is not really working here.
Shared Function CommandString(DatabaseType As SQLiteDatabaseType) As ArrayList
Dim result As New ArrayList
Select Case DatabaseType
Case SQLiteDatabaseType.sample1
result.Add("CREATE TABLE sample1 ([id] COUNTER IDENTITY(1,1), _
[samplename] LONGTEXT,[sampleid] LONG, [samplevalue] LONG, PRIMARY KEY (id))")
Case SQLiteDatabaseType.sample2
result.Add("CREATE TABLE sample2 ([id] COUNTER IDENTITY(1,1), _
[file_path] LONGTEXT,[file_extension] LONGTEXT ,[file_date] DATETIME, _
[file_name] LONGTEXT, PRIMARY KEY (id))")
result.Add("CREATE TABLE sample3 ([id] COUNTER IDENTITY(1,1), _
[file_path] LONGTEXT,[file_extension] LONGTEXT ,[file_date] DATETIME, _
[file_name] LONGTEXT, PRIMARY KEY (id))")
End Select
Return result
End Function
Step 4: Get the SQLite Connection Settings from the App.config:
Shared Function SQLiteConnSettings(stype As SQLiteDatabaseType) As String
Dim dbroot As String = SQLiteConnDatabaseFilePath(stype)
Dim returnvalue As String = CloudAppSettings.GetConfigAppSettingsByName("Database_sqlite_Provider").Trim
returnvalue = returnvalue.Replace("%mdbfilepath%", dbroot)
Return returnvalue
End Function
So, now you understand a bit?
The Core
All the preparation above is for this code.
- Get the database file path.
- Get the connection string.
- Create
SQLiteConnection - Check if database already exists.
- Create Database Table, either one or more table.
- Done
- If error, close the
SQLiteConnectionand delete the database file.
Public Shared Sub CreateNewDatabase(DatabaseType As SQLiteDatabaseType)
Dim dbfile As String = SQLiteConnDatabaseFilePath(DatabaseType)
Dim con As String = SQLiteConnSettings(DatabaseType)
'Note - use New=True to create a new database
Dim conn As SQLiteConnection = New SQLiteConnection(con & "New=True;")
Try
If File.Exists(dbfile) = True Then
'database already exist
Else
If CommandString(DatabaseType).Count > 1 Then
conn.Open()
For items = 0 To CommandString(DatabaseType).Count - 1
Dim cmdCreate As New SQLiteCommand
With cmdCreate
.Connection = conn
.CommandText = CommandString(DatabaseType).Item(items).ToString
End With
cmdCreate.ExecuteNonQuery()
Next
conn.Close()
Else
Dim cmdCreate As New SQLiteCommand
With cmdCreate
.Connection = conn
.CommandText = CommandString(DatabaseType).Item(0).ToString
End With
conn.Open()
cmdCreate.ExecuteNonQuery()
conn.Close()
End If
End If
Catch ex As SQLiteException
conn.Close()
'delete the failed database
File.Delete(dbfile)
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "SQLiteException")
End Try
End Sub
Points of Interest
After reading this walkthrough, you can see common sense with Create an Access Database programmatically by using VB.NET without Microsoft Office.
History
- 31st August, 2014: Initial version