Below we have an equivalent to “Hello World” in SQLite. OK its not really a “Hello World” application – but it does have all the core components to kick start a C# coder to get started with SQLite. Its definitely not elegant, nor the way you would choose to actually do this – but it does run and provides a fair bit of knowledge in a relatively small package.
In order for the code to run you will need to have installed “SQLite.Net” and reference the “System.Data.SQLite” DLL (probably found at "C:\Program Files\SQLite.NET\bin\System.Data.SQLite.dll"). With this and the below code you should have something to get started with.
using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
namespace SampleSqliteConsoleDemo
{
class Program
{
static void Main(string[] args)
{
// Create table and information using native types
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:\testperf.db"))
{
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand())
{
int limit = 100;
comm.Connection = conn;
comm.CommandText = "DROP TABLE IF EXISTS test;" +
"CREATE TABLE IF NOT EXISTS test (n integer);";
try
{
comm.ExecuteNonQuery();
using (SQLiteTransaction tran = conn.BeginTransaction())
{
// You can use the below in the for loop if you 'need'
// to, and it does make it 'easier' to read as you dont
// need the additional param code but not only is this
// faster, but avoids the potential for SQL injection.
//comm.CommandText = "INSERT INTO test values (" + i.ToString() + ")";
comm.CommandText = "INSERT INTO test values (@myTestIndex)";
DbParameter param = comm.CreateParameter();
param.ParameterName = "myTestIndex";
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
for (int i = 0; i < limit; i++)
{
param.Value = i;
comm.ExecuteNonQuery();
}
tran.Commit();
}
}
catch (SQLiteException exception)
{
Console.WriteLine("Failed :" + exception.Message);
}
}
conn.Close();
}
// Access data using the more traditional ADO.NET namespace
DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection cnn = fact.CreateConnection())
{
cnn.ConnectionString = "Data Source=c:\\testperf.db";
cnn.Open();
{
IDbCommand dbcmd = cnn.CreateCommand();
string sql =
"SELECT n " +
"FROM test";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("n: " + reader.GetInt32(0));
}
}
}
}
}
}
So the things to note are:
- This this is obviously not a guide for great programming – but it should touch on the highlevels to access information from SQLite.
- We are using a combination of direct instantiation of “SQLiteConnection” and the ADO.NET base classes. Normally you should try to just use the base classes where possible for portability (should you want to change the DB). We chose both just to highlight the two approaches.
- The DDL creation uses the SQLite “IF EXISTS” for DROP TABLE and “IF NOT EXISTS” for the CREATE TABLE. These are handy features, but are not standard.
- Instead of using the commonly found string concatenation approach to execute the SQL (most introduction web examples show this) we show the preferred approach using DbParameter. This is not only safer (avoids SQL Injection), but is faster as well! Hard to complain about faster and more secure!
Related Links:
- SQLite for C# – Part 1 – Am I allowed to use it?
- SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
- SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
- SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
- SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
- SQLite for C# – Part 6 – SQLite Connection String Definitions
- SQLite for C# – Part 7 – Building SQLite.Net from source
- SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line
.