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.dll System.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>
<!--
<add key="dbroot" value="database"/>
<!--
<!--
<!--
<add key="Database_sqlite_Provider" value="Data Source=%mdbfilepath%;Version=3;"/>
<!--
<!--
<add key="Database_sqlite_sample1" value="sample1"/>
<add key="Database_sqlite_sample2" value="sample2"/>
<!--
<!--
</appSettings>
dbroot, is the folder where you keep the database file, just the folder name. In this case, it is database
.
<!--
<add key="dbroot" value="database"/>
<!--
Database_sqlite_Provider
is the default value.
<!--
<add key="Database_sqlite_Provider" value="Data Source=%mdbfilepath%;Version=3;"/>
<!--
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.
<!--
<add key="Database_sqlite_sample1" value="sample1"/>
<add key="Database_sqlite_sample2" value="sample2"/>
<!--
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 = ""
If Directory.Exists(dbroot) = False Then
Directory.CreateDirectory(dbroot)
End If
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
SQLiteConnection
and delete the database file.
Public Shared Sub CreateNewDatabase(DatabaseType As SQLiteDatabaseType)
Dim dbfile As String = SQLiteConnDatabaseFilePath(DatabaseType)
Dim con As String = SQLiteConnSettings(DatabaseType)
Dim conn As SQLiteConnection = New SQLiteConnection(con & "New=True;")
Try
If File.Exists(dbfile) = True Then
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()
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