SQLiteCommand, SQLiteConnection, SQLiteDataAdapter, SQLiteTransaction, Parametrized Queries, DataTable, DataSet
Install the nuget package System.Data.SQLite. Please note this package only works on windows. If on linux or mac use the sqlite dll provided by mono.
Install-Package System.Data.SQLite
public static IDbConnection CnFactory
{
get
{
var path = @"The\Path\To\Your\Sqlite\file"
if (!System.IO.File.Exists(path))
{
System.Data.SQLite.SQLiteConnection.CreateFile(path);
}
var cnString = "Data Source=" + path + "; Version=3";
return new System.Data.SQLite.SQLiteConnection(cnString);
}
}
Now to use the connection string
using(var cn = CnFactory)
{
cn.Open();
// Now do whatever with the connection. It will be auto closed in the using statement.
}
Create a new connection, initialize a transaction and command. Run said command.
using(var cn = CnFactory)
{
cn.Open();
// Now do whatever with the connection. It will be auto closed in the using statement.
using(var txn = cn.BeginTransaction())
{
// Now do whatever with the transaction. It will be auto closed in the using statement.
// create and execute a command using the connection and transaction
var cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE Users Set FirstName='Peter'";
cmd.Connection = cn;
cmd.CommandTimeout = DefaultTime;
cmd.Transaction = txn;
cmd.ExecuteNonQuery();
}
}
In all seriousness; avoid SQLiteDataAdapter, SQLiteTransaction, DataTable, and DataSet if you can and use Dapper instead.