271

I'd sort of like to use SQLite from within C#.Net, but I can't seem to find an appropriate library. Is there one? An official one? Are there other ways to use SQLite than with a wrapper?

ian93
  • 1,488
  • 4
  • 24
  • 37
  • 4
    This is a duplicate of this question: http://stackoverflow.com/questions/26020/what-is-the-best-way-to-connect-and-use-a-sqlite-database-from-c and has a different answer. – Stewart Johnson Sep 24 '08 at 14:48
  • 2
    Actually, I believe that both accepted answers link to the same project. – Paul Jul 03 '09 at 16:54

12 Answers12

330

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.

ine
  • 14,014
  • 8
  • 55
  • 80
  • 35
    This is an old question but I had to add my $0.02. System.Data.SQLite rocks. It is super refined, stable and commercial grade quality. Best of all, it's 100% managed code and has been released as public domain source code. – Nathan Ridley May 15 '09 at 08:05
  • 4
    Just for consistency, because people is talking about both as different things. In sqlite.phxsoftware.com you are pointed to sourceforge.net/projects/sqlite-dotnet2 for downloading. – yeyeyerman Aug 10 '09 at 14:10
  • 17
    This wrapper is no longer being developed by its original author, and development seems to have been taken over by SQLite itself. The new site is at system.data.sqlite.org, though at the moment it seems less user-friendly and more work-in-progress compared to the original. – Michael Low Mar 11 '11 at 03:27
  • 5
    @NathanRidley - Are you sure its 100% managed, because this answer says "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. " – logicnp Aug 26 '13 at 11:19
  • 2
    FYI, I tried using Microsoft's Microsoft.Data.Sqlite .NET Standard Nuget package, but it failed on a handful of integration tests in the AsyncPoco Github library. After switching to System.Data.SQLite, everything worked perfectly. – Lee Grissom Jul 25 '17 at 17:00
  • Its not working with VS 201. shows the following error: Unhandled Exception: System.BadImageFormatException: Could not load file or assembly 'System.Data.SQLite, Version=1.0.79.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139'. An attempt was made to load a program with an incorrect format. at ConsoleApp1_del2.Program.Main(String[] args) – Sracanis Dec 28 '17 at 07:24
  • @dESource are you sure you downloaded the appropriate architecture for your binary? – ine Dec 29 '17 at 18:06
  • @ine.. yeah the binary is correct, I doubt my VS is not updated let me get back once it is done – Sracanis Dec 30 '17 at 20:20
  • @ine version 1.0.103 is working fine with visual studio 2015 update 3 not sure why is it causing issue in 2017 – Sracanis Jan 01 '18 at 15:29
32

Here are the ones I can find:

Sources:

Donut
  • 110,061
  • 20
  • 134
  • 146
Tom Robinson
  • 8,348
  • 9
  • 58
  • 102
26

There's also now this option: http://code.google.com/p/csharp-sqlite/ - a complete port of SQLite to C#.

xanadont
  • 7,493
  • 6
  • 36
  • 49
16

The folks from sqlite.org have taken over the development of the ADO.NET provider:

From their homepage:

This is a fork of the popular ADO.NET 4.0 adaptor for SQLite known as System.Data.SQLite. The originator of System.Data.SQLite, Robert Simpson, is aware of this fork, has expressed his approval, and has commit privileges on the new Fossil repository. The SQLite development team intends to maintain System.Data.SQLite moving forward.

Historical versions, as well as the original support forums, may still be found at http://sqlite.phxsoftware.com, though there have been no updates to this version since April of 2010.

The complete list of features can be found at on their wiki. Highlights include

  • ADO.NET 2.0 support
  • Full Entity Framework support
  • Full Mono support
  • Visual Studio 2005/2008 Design-Time support
  • Compact Framework, C/C++ support

Released DLLs can be downloaded directly from the site.

David Schmitt
  • 58,259
  • 26
  • 121
  • 165
8

sqlite-net is an open source, minimal library to allow .NET and Mono applications to store data in SQLite 3 databases. More information at the wiki page.

It is written in C# and is meant to be simply compiled in with your projects. It was first designed to work with MonoTouch on the iPhone, but has grown up to work on all the platforms (Mono for Android, .NET, Silverlight, WP7, WinRT, Azure, etc.).

It is available as a Nuget package, where it is the 2nd most popular SQLite package with over 60,000 downloads as of 2014.

sqlite-net was designed as a quick and convenient database layer. Its design follows from these goals:

  • Very easy to integrate with existing projects and with MonoTouch projects.
  • Thin wrapper over SQLite and should be fast and efficient. (The library should not be the performance bottleneck of your queries.)
  • 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.
  • Works with your data model without forcing you to change your classes. (Contains a small reflection-driven ORM layer.)
  • 0 dependencies aside from a compiled form of the sqlite2 library.

Non-goals include:

  • Not an ADO.NET implementation. This is not a full SQLite driver. If you need that, use System.Data.SQLite.
dodgy_coder
  • 12,407
  • 10
  • 54
  • 67
  • I notice that most of the comment prior 2014 said System.Data.SQLite which I tried to install on my Windows Store App (didn't work). Sqlite-net works. – JeeShen Lee Jan 27 '16 at 02:54
8

I'd definitely go with System.Data.SQLite (as previously mentioned: http://sqlite.phxsoftware.com/)

It is coherent with ADO.NET (System.Data.*), and is compiled into a single DLL. No sqlite3.dll - because the C code of SQLite is embedded within System.Data.SQLite.dll. A bit of managed C++ magic.

tonyz
  • 191
  • 1
  • 2
3

Microsoft.Data.Sqlite

Microsoft now provides Microsoft.Data.Sqlite as a first-party SQLite solution for .NET, which is provided as part of ASP.NET Core. The license is the Apache License, Version 2.0.

* Disclaimer: I have not actually tried using this myself yet, but there is some documentation provided on Microsoft Docs here for using it with .NET Core and UWP.

Knowledge Cube
  • 990
  • 12
  • 35
2

Mono comes with a wrapper. https://github.com/mono/mono/tree/master/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0 gives code to wrap the actual SQLite dll ( http://www.sqlite.org/sqlite-shell-win32-x86-3071300.zip found on the download page http://www.sqlite.org/download.html/ ) in a .net friendly way. It works on Linux or Windows.

This seems the thinnest of all worlds, minimizing your dependence on third party libraries. If I had to do this project from scratch, this is the way I would do it.

lmat - Reinstate Monica
  • 7,289
  • 6
  • 48
  • 62
2

For those like me who don't need or don't want ADO.NET, those who need to run code closer to SQLite, but still compatible with netstandard (.net framework, .net core, etc.), I've built a 100% free open source project called SQLNado (for "Not ADO") available on github here:

https://github.com/smourier/SQLNado

It's available as a nuget here https://www.nuget.org/packages/SqlNado but also available as a single .cs file, so it's quite practical to use in any C# project type.

It supports all of SQLite features when using SQL commands, and also supports most of SQLite features through .NET:

  • Automatic class-to-table mapping (Save, Delete, Load, LoadAll, LoadByPrimaryKey, LoadByForeignKey, etc.)
  • Automatic synchronization of schema (tables, columns) between classes and existing table
  • Designed for thread-safe operations
  • Where and OrderBy LINQ/IQueryable .NET expressions are supported (work is still in progress in this area), also with collation support
  • SQLite database schema (tables, columns, etc.) exposed to .NET
  • SQLite custom functions can be written in .NET
  • SQLite incremental BLOB I/O is exposed as a .NET Stream to avoid high memory consumption
  • SQLite collation support, including the possibility to add custom collations using .NET code
  • SQLite extensions (.dll) loading support
  • SQLite Full Text Search engine (FTS3) support, including the possibility to add custom FTS3 tokenizers using .NET code (like localized stop words for example). I don't believe any other .NET wrappers do that.
  • Automatic support for Windows 'winsqlite3.dll' (only on recent Windows versions) to avoid shipping any binary dependency file. This works in Azure Web apps too!.
Simon Mourier
  • 132,049
  • 21
  • 248
  • 298
0

Version 1.2 of Monotouch includes support for System.Data. You can find more details here: http://monotouch.net/Documentation/System.Data

But basically it allows you to use the usual ADO .NET patterns with sqlite.

0

http://www.devart.com/dotconnect/sqlite/

dotConnect for SQLite is an enhanced data provider for SQLite that builds on ADO.NET technology to present a complete solution for developing SQLite-based database applications. As a part of the Devart database application development framework, dotConnect for SQLite offers both high performance native connectivity to the SQLite database and a number of innovative development tools and technologies.

dotConnect for SQLite introduces new approaches for designing application architecture, boosts productivity, and leverages database application implementation.

I use the standard version,it works perfect :)

Antonio
  • 381
  • 4
  • 12
0

A barebones wrapper of the functions as provided by the sqlite library. Latest version supports functions provided sqlite library 3.7.10

SQLiteWrapper project

Eminem
  • 7,206
  • 15
  • 53
  • 95