It appears that this was resolved in 1.0.95 however broken again in 1.0.97. The solution is to set the BinaryGUID property on the connection string to true and set the following environment variable (before you make the connection) Environment.SetEnvironmentVariable(“AppendManifestToken_SQLiteProviderManifest”,”;BinaryGUID=True;”); Data Source=c:\mydb.db;Version=3;BinaryGUID=True; https://www.connectionstrings.com/sqlite/
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
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
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();
Try a different approach, naming your fields in the query and naming the parameters in the query: this.command.CommandText = “INSERT INTO StringData (field1, field2) VALUES(@param1, @param2)”; this.command.CommandType = CommandType.Text; this.command.Parameters.Add(new SQLiteParameter(“@param1”, data.Data)); this.command.Parameters.Add(new SQLiteParameter(“@param2”, data.ByteIndex)); …
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
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
In order to use a CLR 2.0 mixed mode assembly, you need to modify your App.Config file to include: <?xml version=”1.0″?><configuration> <startup useLegacyV2RuntimeActivationPolicy=”true”> <supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/> </startup></configuration> The key is the useLegacyV2RuntimeActivationPolicy flag. This causes the CLR to use the latest version (4.0) to load your mixed mode assembly. Without this, it will not work. … Read more