Create ADO.NET SQLite Database Programmatically by using VB.NET

Lau Han Ching

4.10/5 (3 votes)

Sep 3, 2014

CPOL

3 min read

33901

1699

Create ADO.NET SQLite Database programmatically by using VB.NET

Download source - 675.8 KB

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>
      <!-- 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 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)
        '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

31^st August, 2014: Initial version