What are the differences between System.Data.SQLite and Microsoft.Data.Sqlite?
I understand that System.Data.SQLite is older and got .NETStandard support after Microsoft.Data.Sqlite, but now both of them support .NETStandard 2.
What are the advantages of one over the other?
An advantage of System.Data.SQLite is that it is developed by the SQLite team who have stated a long-term commitment to keeping it supported.
An advantage of Microsoft.Data.Sqlite is that it is developed by Microsoft and can be assumed to be well tested with Entitity Framework, .NET Core etc.
I chose System.Data.SQLite for my project, one reason being that I use the GetBytes() DataReader method which is "not supported" in Microsoft.Data.Sqlite.
I have not tested performance, however it would not surprise me if Microsoft.Data.Sqlite wins since it claims to be a thinner wrapper. See below.
There is an informative comment by Brice Lambson, one of the Microsoft.Data.Sqlite developers here:
https://www.bricelam.net/2018/05/24/microsoft-data-sqlite-2-1.html#comment-3980760585
He says, "There are three main differences between Microsoft.Data.Sqlite and System.Data.SQLite.
"First, we don't aim to be a feature-complete ADO.NET provider. Microsoft.Data.Sqlite was created for .NET Core 1.0 when the goal was to create a lighter-weight, modernized version of .NET. That goal of .NET Core has largely been abandoned in favor of adding as many APIs as possible to make it easier to port from other .NET runtimes. However, the goal of Microsot.Data.Sqlite is still just to provide a basic ADO.NET implementation sufficient to support modern data access frameworks like EF Core, Dapper, etc. We tend not to add API for things that can be done using SQL. For example, see this comment for SQL alternatives to connection string keywords.
"The second big difference is that we're much closer to the native SQLite behavior. We don't try to compensate for any of SQLite's quirkiness. For example, System.Data.SQLite adds .NET semantics to column type names. They even have to parse every SQL statement before sending it to the native driver to handle a custom SQL statement for specifying the column type of results not coming directly from a table column (i.e. expressions in the SELECT clause). Instead, we embrace the fact that SQLite only supports four primitive types (INTEGER, REAL, TEXT, and BLOB) and implement ADO.NET APIs in a way that helps you coerce values between these and .NET types.
"Finally, we weren't written 10 years ago. :-) This allow us to create more modern APIs that feel more natural in modern, idiomatic C#. The API for registering user-defined functions is the best example of this."
I have tried System.Data.SQLite and Microsoft.Data.SQLite in a project, and I make a real world performace test, System.Data.SQLite is about 3x faster than Microsoft.Data.Sqlite when bulk insert.
