Is there a .NET/C# wrapper for SQLite?
From https://system.data.sqlite.org:
System.Data.SQLite is an ADO.NET adapter for SQLite.
System.Data.SQLite was started by Robert Simpson. Robert still has commit privileges on this repository but is no longer an active contributor. Development and maintenance work is now mostly performed by the SQLite Development Team. The SQLite team is committed to supporting System.Data.SQLite long-term.
"System.Data.SQLite is the original SQLite database engine and a complete ADO.NET 2.0 provider all rolled into a single mixed mode assembly. It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll). Unlike normal mixed assemblies, it has no linker dependency on the .NET runtime so it can be distributed independently of .NET."
It even supports Mono.
C# wrapper for SQLite
In fact, you have found the right place already.
You problem may comes from you're using the wrong version of System.Data.SQLite.dll, because there are two of them, one for x86, one for x64.
Try use Nuget instead of downloading it manually.
Cross-platform Sqlite
Some options for cross-platform .NET/C# development with SQLite are:
SQLitePCL.raw
A Portable Class Library (PCL) for low-level (raw) access to SQLite
SQLitePCL.raw provides a very thin C# wrapper ontop of the SQLite C API. The API exposed by SQLitePCL.raw is hostile from an application developer perspective, but is designed for use as a common portable layer upon which friendlier wrappers can be built.
License: Apache License v2
Source code: https://github.com/ericsink/SQLitePCL.raw
Nuget: https://www.nuget.org/packages/SQLitePCLRaw.core
Platforms: Xamarin.Android, Xamarin.iOS, UWP, Windows Phone 8.1, .NET 4.5, .NET 4.0, .NET 3.5, Linux, MacOS, NetStandard 1.1, Windows Phone 8 (with limitations), Windows Phone 8.1 Silverlight (with limitations)
SQLitePCL.pretty
A pretty face on top of SQLitePCL.raw
This library wraps the C like SQLiteAPI provided by SQLitePCL.raw with a friendly C# object oriented API. SQLitePCL.pretty has extensive unit test coverage and supports many newer features available in more recent SQLite versions.
License: Apache License v2
Source code: https://github.com/bordoley/SQLitePCL.pretty
Nuget: https://www.nuget.org/packages/SQLitePCL.pretty
Platforms: same as SQLitePCL.raw
SQLite-net
Simple, powerful, cross-platform SQLite client and ORM for .NET
SQLite-net was designed as a quick and convenient database layer. Very easy to integrate with existing projects and runs on all the .NET platforms, with very simple methods for executing CRUD operations and queries safely (using parameters) and for retrieving the results of those query in a strongly typed fashion.
License: MIT License
Source code: https://github.com/praeclarum/sqlite-net
Nuget: https://www.nuget.org/packages/sqlite-net-pcl
Platforms: same as SQLitePCL.raw
Microsoft.Data.Sqlite
SQLite implementations of the System.Data.Common interfaces
This project is part of ASP.NET Core and maintained by Microsoft
License: Apache License v2
Source code: https://github.com/aspnet/Microsoft.Data.Sqlite
Nuget: https://www.nuget.org/packages/Microsoft.Data.SQLite
Platforms: .NET Framework, Mono, .NET Core (.NET Native, CoreCLR, Windows Universal), Xamarin (planned)
Wrapper for SQL and SQLite
Usually, every class in the ADO.NET framework has a common parent used by all providers. So you should be able to get around the fact that the application uses two kinds of databases just by using a Factory pattern (http://www.dofactory.com/Patterns/PatternFactory.aspx) or an Abstract Factory (http://www.dofactory.com/Patterns/PatternAbstract.aspx).
This would insure that you don't need two implementation of the wrappers. For example :
public abstract class SqlFactory
{
public abstract DbConnection CreateConnection();
public abstract DataAdapter CreateAdapter(string command, DbConnection connection);
}
public class SqlLiteFactory : SqlFactory
{
public override DbConnection CreateConnection()
{
return new SQLiteConnection("Data Source=Resources\\DB.sqlite;Version=3");
}
public override DataAdapter CreateAdapter(string command, DbConnection connection)
{
return new SQLiteDataAdapter(command, connection as SQLiteConnection);
}
}
public class MSSqlFactory : SqlFactory
{
public override DbConnection CreateConnection()
{
return new SqlConnection("CONNECTION STRING HERE");
}
public override DataAdapter CreateAdapter(string command, DbConnection connection)
{
return new SqlDataAdapter(command, connection as SqlConnection);
}
}
//Composite and Singleton class...
public class SqlHandler : SqlFactory
{
private static SqlHandler _instance;
private SqlLiteFactory _sqlLiteFactory;
private MSSqlFactory _msSqlFactory;
//Singleton pattern.
public static SqlHandler Instance
{
get
{
if (_instance == null)
{
_instance = new SqlHandler();
}
return _instance;
}
}
private SqlHandler()
{
_sqlLiteFactory = new SqlLiteFactory();
_msSqlFactory = new MSSqlFactory();
}
public override DbConnection CreateConnection()
{
//Some code determining if better to use SqlLite or MS SQL.
if (useSqlLite)
{
return _sqlLiteFactory.CreateConnection();
}
else
{
return _msSqlFactory.CreateConnection();
}
}
public override DataAdapter CreateAdapter(string command, DbConnection connection)
{
//Some code determining if better to use SqlLite or MS SQL.
if (useSqlLite)
{
return _sqlLiteFactory.CreateAdapter(command, connection);
}
else
{
return _msSqlFactory.CreateAdapter(command, connection);
}
}
}
So when you would need to invoke anything database related in your wrapper, you can just do :
DbConnection c = SqlHandler.Instance.CreateConnection();
This way also allows you to implement easily new databases.
PS : For your connection strings, I would recommend you use the App.config file to set it. If anything changes in the future, you will be able to change the database without having to recompile your application.
SQLite3 in C#.NET
Have you looked at the System.Data.SQLite library? It's a free ADO.NET library to interact with SQLite and requires nothing else in order to run, since it has the engine built into it.
I've been using it for a while now and find it really easy to work with. It even has a plugin for Visual Studio should you decide to use some strongly-typed tables with it or want to use it to add/create tables.
C# SQlite Connection String Format
- You should pick the Mixed one.
the 1.0.84.0 is the newest version out for the
SQLite DLL
. I created
an application withSQLite
too inc#
, my connection string looks
like the following:sqlite_conn = new SQLiteConnection("Data Source=C:\SQLITEDATABASES\SQLITEDB1.sqlite;Version=3;");
The version you are using, is SQLite
version 3, the DLL is just a different version, but works with SQLite
version 3.
Related Topics
What Is "Best Practice" for Comparing Two Instances of a Reference Type
How to Pass Values (Parameters) Between Xaml Pages
C# Object Pooling Pattern Implementation
Concurrent Hashset<T> in .Net Framework
Get All Derived Types of a Type
Random String Generator Returning Same String
How to Restart My C# Winform Application
Calling C# Code from C++, But Executeindefaultappdomain() Is Too Limited
Dependency Injection Unity - Conditional Resolving
How to Implement a Configurationsection with a Configurationelementcollection
Is Everything in .Net an Object
How to Convert JSON Text into Objects Using C#