Read/Write to SQLLite via .NET - Solved

[Guest]

OK, so apparently there have been some people wanting to have read/write database access to the new mediamonkey database format, only to be met with some frustrating IUNICODE collation errors.

I did a little digging, and turned up the following links regarding this subject.

Python script for defining a collation sequence:
http://www.mediamonkey.com/forum/viewtopic.php?p=127635#127635

and another snippet for defining a collation sequence in C#
http://sqlite.phxsoftware.com/forums/p/207/757.aspx#757

so, I took the concepts in those articles to see if I could apply them using the ADO.NET 2.0 Provider for SQLite found here:

http://sourceforge.net/project/showfiles.php?group_id=132486&package_id=145568&release_id=564661

When I tried using the .NET provider out of the box in a project, it gave me the same IUNICODE issue as has been reported here in the past, until I added the following class to the project (in C#):

Code: Select all

    [SQLiteFunction(Name = "IUNICODE", FuncType = FunctionType.Collation)]
    class IUNICODE : SQLiteFunction
    {

        public override int Compare(string param1, string param2)
        {
            return String.Compare(param1, param2, true);
        }

    }


Voila! it worked. Apparently the attributes on the function are enough to tell SqlLite how to find the function without any other code settings.

I'll try to post a solution file that illustrates this usage when I get a chance to clean it up.

Enjoy!

[taschell]

OK, immediately after posting that, I realized I wasn't logged in.

In any case, is there any way/where I can post that solution file as an example of usage here on the forum?

Thanks!

[nohitter151]

OK, immediately after posting that, I realized I wasn't logged in.

In any case, is there any way/where I can post that solution file as an example of usage here on the forum?

Thanks!

Not on the forum, but you could probably upload it to the wiki and create a new page explaining everything:

http://www.mediamonkey.com/wiki

[MMuser2011]

Maybe this code snippet could help if you need the above for VB.NET (tested in 2010 Express).
Add a new class to your VB.NET project with the following content:

Code: Select all

Imports System.Data.SQLite

<SQLiteFunction(Name:="iUNICODE", FuncType:=FunctionType.Collation)> _
Class iUNICODE
    Inherits SQLiteFunction

    Public Overrides Function Compare(param1 As String, param2 As String) As Integer
        Return [String].Compare(param1, param2, True)
    End Function

End Class

You have to add the following line in the beginning of your application:

Code: Select all

SQLiteFunction.RegisterFunction(GetType(iUNICODE))

JFYI:
If you use SQL-Queries with the suffix-workaround COLLATE NOCASE you will find the following problem:
"Loredana Bertè" <> "LOREDANA BERTÈ"
Even if you compare the uppercased values with something like
...WHERE UPPER(artist) = '" & lsArtistName.ToUpper & "' COLLATE NOCASE"
you don't get the needed result!!


All the credits and a BIG 'thank you' to taschell!!