Save application settings to SQLite in C#

Environment used Visual Studio2010 / C# will be used for this explanation.

Download SQLite.dll

To use SQLite as a DB, first download SQLite.dll.

SQLite Dowload Page

Here, download “sqlite-netFx40-binary-Win32-2010-1.0.105.1.zip” from “Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)”. Change the file according to your PC environment.

Extract “System.Data.SQLite.dll” from the “sqlite-netFx40-binary-Win32-2010-1.0.105.1.zip” folder and save it in any folder.

Open the application in Visual Studio, go to “Reference Settings”, click “Add Reference”, select “System.Data.SQLite.dll” that you just saved, and add it to the reference settings.

Create a folder for the target platform in the bin>Debug folder of the application. If the platform target is Any CPU with either “x64” or “x86”, create both folders.

Take the “SQLite.Interop.dll” in the ZIP folder and save it in the “x64” or “x86” folder you created in the Debug folder.

If you do not save SQLite.Interop.dll in this form, you will get the error message “Cannot load DLL ‘SQLite.Interop.dll’: The specified module cannot be found. error message and cannot be executed.

Data read/write

SQLiteClass.cs

using System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;


class SQLiteClass
{
    private SQLiteConnection con;
    
    //Initialize
    public void InitializeSQLite()
    {
        //C:\Users\username\AppData\Roaming\SampleApplication1 にユーザ設定ファイルを保存する
        string appfol = System.IO.Path.Combine(Environment.GetFolderPath(
                Environment.SpecialFolder.ApplicationData), Application.ProductName);

        //Create the application folder
        if (System.IO.Directory.Exists(appfol) != true)
        {
            System.IO.Directory.CreateDirectory(appfol);
        }

        string dbfile = appfol + "\\" + "setting.sqlite3";

        con = new SQLiteConnection("Data Source=" + dbfile);

        //Open db
        con.Open();

        //Create table
        SQLiteCommand cmd = con.CreateCommand();

        //Create table
        cmd.CommandText = "Create Table If Not Exists Setting ("
            + "Fld1 Text,"
            + "Fld2 Text,"
            + "Fld3 Integer,"
            + "Fld4 Real)";
        cmd.ExecuteNonQuery();

        //Close db
        con.Close();
    }
}

Add “using System.Data.SQLite;” to the beginning of the class. Call the Load event InitializeSQLite method of the application to create a folder for saving settings, create the DB, and initialize the table creation. Open(); If the DB file does not exist, it will be created by itself. If it exists, the DB is opened. In the Create Table If Not Exists Setting, the keyword “If Not Exists Table Name” will create the table if it does not exist.

Saving Records

        //Save the basic settings
        private static void InsertToBasicTable(DTConnection db,string fldName,string textValue)
        {
            SQLiteCommand cmd = DTConnection.dtCN.CreateCommand();

            //Delete the exisiting settings
            cmd.CommandText = "Delete from Setting where Name='" + fldName+"'";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "Insert into Setting"
                + " (Name,TextValue) values ('" + fldName + "','" + textValue + "')";

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }