System.Data.SQLite

After some thought, I decided that rather than do some really rudimentary tutorial code that demonstrates the simplest of SQL, my time would be better spent hitting on some of the more advanced topics and features of SQLite.  Here are some of the samples I hope to complete in the coming days: 

1. Creating a new SQLite database file
2. Enabling NTFS file compression on a database file
3. Enabling NTFS encrypted file system support on a database file (obsolete, see #5)
4. Writing parameterized queries
5. Encrypting, decrypting and attaching to encrypted databases
6. Writing high performance inserts/updates
7. User-defined scalar, aggregate and collation functions
8. Attaching multiple databases to a connection (aka "how to read and write simultaneously")
9. Reclaiming unused space in a database file (vacuum)
10. Retrieving and interpreting schema information

 

I'll be finishing those up next week if I'm able.  Things are getting a little crazy with the holidays coming up!

Robert

 

Robert,

great job! it works wery nice and i love the performance.

I think other people will also agree that it would be great if you can make PDF manual which will come together with installation of ado.net sqlite.

grtz

Perica

So its been 1 year so far, and I still dont see an example of user defined functions.  This would be extremely helpful for me.  Could you show us a quick example of this?  Whenever I try it I get a no such function.

It was easy to find anexample of user function. Here is link to thread about adding regex function to sqlite:
http://sqlite.phxsoftware.com/forums/thread/1457.aspx

In the source code distribution is a project called Test.  Inside that project is TestCases.cs which contains several examples of user-defined scalar and aggregate functions and collating sequences.

Robert

 

Hi Does anyone know where I could find some examples written in VB.NET that would show how to use transactions to do bulk inserts? I am a n00b so please be gentle with me! Thanks!

Perhaps I should explain further. Below is sample code as provided in the sqlite.net help file. - I would ideally like to see this example written in VB,NET using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); for (n = 0; n

SharpDevelop is a free IDE for C# and VB.NET projects on Microsoft's .NET platform, you can download it at http://www.sharpdevelop.net.
You can then paste the C# code into an empty project and under Tools -> Convert Code to -> VB.NET let the IDE convert the code for you, VB.NET -> C# is also possible. Your sample is converted to:

Thanks - I think I am nearly there... This is my code: myConnection.Open() SqlCommand = myConnection.CreateCommand SQLcommand.CommandText = "CREATE TABLE ITEM1(id INTEGER PRIMARY KEY AUTOINCREMENT, Data integer);" SQLcommand.ExecuteNonQuery() Dim myTrans = myConnection.BeginTransaction() Using mytransaction As SQLiteTransaction = myConnection.BeginTransaction() Using mycommand As New SQLiteCommand(myConnection) Dim myparam As New SQLiteParameter() Dim n As Integer mycommand.CommandText = "INSERT INTO ITEM1 ([DATA]) VALUES(?)" mycommand.Parameters.Add(myparam) For n = 0 To 99999 myparam.Value = n + 1 mycommand.ExecuteNonQuery() Next End Using mytransaction.Commit() End Using myConnection.Close() It runs without error, but the table fails to be updated. Any ideas? Thanks !!

Thanks

This really helped me a lot!:D

 SEE i.e. the SQLite Encryption Extension which allows an application to read and write encrypted database files. This aapplication use the 'Attach' command of SQLite talk to two or more encrypted and unencrypted database files. Every database file have its own encryption key.