Building asp.net web applications with sqlite

If you want to build and asp.net web site/application that is interactive you mostly use SQL server as backend for your site.
But sometimes you don't need full-fledged server; you could be building a personal site or a site with low traffic. In cases where you don’t need a SQL server one of the choices is SQLite database.
I’ve build couple of sites with SQLite and quite happy with performance. Although it is not recommended to use it on high traffic sites I have not seen any problem with sites that have 50-60 users online at any given time.

To use SQLite on your site as database provider you need couple of things

• Up-to-date version of SQLite .Net from http://sqlite.phxsoftware.com/
• To create/design my databases I use SQLite Admin from http://sqliteadmin.orbmu2k.de/ but you can use Visual Studio addin of SQLite .Net too.

Once you install above files all you need to do is create an empty asp.net website/application and put System.Data.SQLite.DLL in to your Bin folder. Create a SQLite database and put it to your App_Data folder.
You can connect to it with connection string like below

          static string connString = "Data Source=" + SqliteDataBasePath + "; Journal Mode=Off; Compress=True; Version=3;";

I use below class to communicate with SQLite Databases. It is quite simple.

using System.Data;
using System.Data.SQLite;
using System.Web;

/// 
/// Summary description for DB
/// 
public class DB
{

    public static DataSet GetTables(string sql)
    {
        DataSet ds = new DataSet();

        using (SQLiteConnection conn = new SQLiteConnection(connString))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                   
                    da.Fill(ds);
                    return ds;
                }
            }
        }

    }
    public static int ExecuteNonQuery(string sql)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connString))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                int rowsUpdated = cmd.ExecuteNonQuery();
                return rowsUpdated;

            }
        }

    }
    public static string ExecuteScalar(string sql)
    {

        using (SQLiteConnection conn = new SQLiteConnection(connString))
        {
            conn.Open();

            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                object value = cmd.ExecuteScalar();
                if (value != null)
                {
                    return value.ToString();
                }
                else
                {
                    return "";
                }
            }

        }
    }  
    
}