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:

1. SQLite3.dll
2. 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:

1. App.config
2. CloudAppSettings.vb
3. 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:

1. dbroot, get the database folder path
2. check the path, if it does not exist, then create it
3. get the database file name from App.config
4. 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.

1. Get the database file path.
2. Get the connection string.
3. Create SQLiteConnection
4. Check if database already exists.
5. Create Database Table, either one or more table.
6. Done
7. 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