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 … Read more
SQLite contains unmanaged code, you can’t run it on a 64-bit operating system unless you deploy the 64-bit version. Quick fix: Project + Properties, Build tab, Platform Target = x86.
http://msdn.microsoft.com/en-us/library/x0w2664k.aspx 1) Allways check msdn first. Mixed mode means that the assembly can run managed and unmanaged code. 2) Setups for 32-bit Windows (.NET Framework 4.0) http://system.data.sqlite.org/sqlite-netFx40-setup-bundle-x86-2010-1.0.74.0.exe You kinda answered that question yourself “My project that is going to use this library is all .NET 4 which will be compiled to x86.”
I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dll version 1.0.82.0 — after closing the connection we were unable to read the database file using System.IO.FileStream. I was disposing correctly both connection and tableadapters and I was not using connection pooling. According to my first searches (for example this … Read more
Robert Simpson has a great example of a REGEX function you can use in your sqlite queries: // taken from http://sqlite.phxsoftware.com/forums/p/348/1457.aspx#1457 [SQLiteFunction(Name = “REGEXP”, Arguments = 2, FuncType = FunctionType.Scalar)] class MyRegEx : SQLiteFunction { public override object Invoke(object[] args) { return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]),Convert.ToString(args[0])); } } // example SQL: SELECT * FROM Foo WHERE Foo.Name REGEXP … Read more
Edit (12.08.2021) This post/lib is about EF 6 not EF Core. If you use EF Core, code first for SQLite is supported. Initial (05.04.2015) I started with the code from fried and created a project which lets you use CodeFirst. The project is available open source on GitHub or as NuGet Package. If you encounter … Read more
Somewhere along the way a connection is getting left open. Get rid of OpenConnection and CloseConnection and change ExecuteNonQuery to this: using (SQLiteConnection c = new SQLiteConnection(ConnectionString)) { c.Open(); using (SQLiteCommand cmd = new SQLiteCommand(sql, c)) { cmd.ExecuteNonQuery(); } } Further, change the way you read data to this: using (SQLiteConnection c = new SQLiteConnection(ConnectionString)) … Read more
Wrap BEGIN \ END statements around your bulk inserts. Sqlite is optimized for transactions. dbcon = new SQLiteConnection(connectionString); dbcon.Open(); SQLiteCommand sqlComm; sqlComm = new SQLiteCommand(“begin”, dbcon); sqlComm.ExecuteNonQuery(); //—INSIDE LOOP sqlComm = new SQLiteCommand(sqlQuery, dbcon); nRowUpdatedCount = sqlComm.ExecuteNonQuery(); //—END LOOP sqlComm = new SQLiteCommand(“end”, dbcon); sqlComm.ExecuteNonQuery(); dbcon.close();
Encountered the same problem a while ago while writing a DB abstraction layer for C# and I never actually got around to finding out what the issue was. I just ended up throwing an exception when you attempted to delete a SQLite DB using my library. Anyway, this afternoon I was looking through it all … Read more
The next link will bring you to a great tutorial, that helped me a lot! How to SQLITE in C# I nearly used everything in that article to create the SQLite database for my own C# Application. Don’t forget to download the SQLite.dll, and add it as a reference to your project. This can be … Read more