Using SQLite in .NET with Visual Studio 2013

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is a popular choice as embedded database for local/client storage.

In order to use SQLite in your .NET application, you can run following command in Visual Studio 2013 Package Manager Console.

PM> Install-Package System.Data.SQLite 

Once the package has been successfully installed, your project will have proper reference to SQLite dll. In the class you would like to use SQLite, add following "using" statement.

using System.Data.SQLite;

Here is the sample code that uses SQLite:

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

namespace DataAccessLayer
{
    public class SqlLiteHelper
    {
        public static int ExecuteNonQuery(string strConnectionString, string strSqlCommand, SQLiteParameter[] sqlParams)
        {
            using (SQLiteConnection dbConnection = new SQLiteConnection(strConnectionString))
            {
                dbConnection.Open();
                int rowsUpdated = 0;
                using (SQLiteCommand sqlCommand = new SQLiteCommand(strSqlCommand, dbConnection))
                {
                    if (sqlParams != null && sqlParams.Length > 0)
                        sqlCommand.Parameters.AddRange(sqlParams);
                   
                    rowsUpdated = sqlCommand.ExecuteNonQuery();
                }
                dbConnection.Close();
                return rowsUpdated;
            }
        }

        public static int ExecuteNonQuery(string strConnectionString, string strSqlCommand)
        {
            return ExecuteNonQuery(strConnectionString, strSqlCommand, null);
        }

        public static DataTable ExecuteDataTable(string strConnectionString, string strSqlCommand)
        {
            using (SQLiteConnection dbConnection = new SQLiteConnection(strConnectionString))
            {
                DataTable dtResults = new DataTable();
                dbConnection.Open();
                using (SQLiteCommand sqlCommand = new SQLiteCommand(strSqlCommand, dbConnection))
                {
                    using (SQLiteDataReader reader = sqlCommand.ExecuteReader())
                    {
                        dtResults.Load(reader);
                        reader.Close();
                    }
                }
                dbConnection.Close();
                return dtResults;
            }
        }
    }
}

You will need to add connection string configuration inside your web.config file or app.config file like this:
  <connectionStrings>
    <add name="SQLiteConnection" connectionString="~/App_Data\kp.s3db;" providerName="SQLite Data Provider"/>
  </connectionStrings>
  <system.data>
    <!--
        NOTE: The extra "remove" element below is to prevent the design-time
              support components within EF6 from selecting the legacy ADO.NET
              provider for SQLite (i.e. the one without any EF6 support).  It
              appears to only consider the first ADO.NET provider in the list
              within the resulting "app.config" or "web.config" file.
    -->
    <DbProviderFactories>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant="System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    </DbProviderFactories>
  </system.data>

SQLite database has one of the following data types:

• NULL. The value is a NULL value.
  
• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. In .NET, if you want to cast an INTEGER value from SQLite, you should use long data type. For example:
          public static StatisticsEntity GetRequestStatistics(string strConnectionString)
          {
              DataTable dtStatistics = SqlLiteHelper.ExecuteDataTable(strConnectionString, "SELECT IsSuccessful, Count(*) AS \"VisitCount\" FROM SingleSignOnRequest GROUP BY IsSuccessful");
              StatisticsEntity statistics = new StatisticsEntity();
              foreach(DataRow drRow in dtStatistics.Rows)
              {
                  if ((long)drRow["IsSuccessful"] == 1)
                  {
                      statistics.SuccessfulVisitCount = (long)drRow["VisitCount"];
                  }
                  else
                  {
                      statistics.UnsuccessfulVisitCount = (long)drRow["VisitCount"];
                  }
              }
              return statistics;
          }
  
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  
• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  
• BLOB. The value is a blob of data, stored exactly as it was input.
  

Note:
1. SQLite does not have Boolean data type. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
2. SQLite does not have date and/or time data type. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

In order to create database and design table schemas, you will need to use some SQLite tools to do that. Following are two of them:
1. SQLite Browser: http://sqlitebrowser.org/
2. SQLite Administrator: http://sqliteadmin.orbmu2k.de/ 

However, SQLite also has integrated the database designer with Visual Studio 2013. Simply Install this:
https://system.data.sqlite.org/downloads/1.0.91.0/sqlite-netFx451-setup-bundle-x86-2013-1.0.91.0.exe
After installation, follow these steps to create your SQLite database/tables:

1. Start your Visual Studio 2013. In "Server Explorer", right click "Data Connections" and then choose "Add Connection..."

2. The "Add Connection" dialog will show. Click the "Change" button to change the data provider and choose "System.Data.SQLite Database File" as your data provider and click "OK":

 3. In the "Add Connection" dialog, you can either browse to existing SQLite database file or create your own database file. Once you have specified the database file, click "OK" button.

4. Now, you can see a new Data Connection called "SQLite" has been created. Expand this connection and you can see the Tables folder. Right click on the Tables folder, you can "Add New Table" or query the existing tables.