Incorrect field type mapping SubSonic 2.2, SQLite 3

Question

I have a boolean (BOOL) type field in the SQLite table. In the SubSonic generated DAL it is represented as string (instead of bool). It is weird. How it can be managed? Please, help.

Answer 1

Hi,

I maybe wrong on this, I have only used Subsonic 2.2 with SQL2008 but I would recommend looking at the source for Subsonic, namely SQLLiteDataProvider.cs and reviewing the DbType function.

It seems to recognise the type Boolean but not Bool and the default when it does not recognise your type is to resort to String.

Hare is the code in question from that function

 switch(sqliteType.ToLowerInvariant())
            {
                case "text":
                case "char":
                case "nchar":
                case "varchar":
                case "nvarchar":
                    return DbType.String;
                case "boolean":
                case "bit":
                    return DbType.Boolean;
                case "bigint":
                case "int":
                case "integer":
                    return DbType.Int64;
                case "real":
                case "numeric":
                case "double":
                case "single":
                case "float":
                    return DbType.Single;
                case "smallint":
                    return DbType.Int16;
                case "date":
                case "time":
                case "datetime":
                case "smalldatetime":
                case "timestamp":
                    return DbType.DateTime;
                case "binary":
                case "blob":
                case "image":
                    return DbType.Binary;
                case "guid":
                    return DbType.Guid;
                default:
                    return DbType.String;
            }

I would recommend changing it, seeing if it works and then recommeding the change to the subsonic chaps.

Answer 2

I checked in a newer version of the provider in Dec. with several bug fixes, but from what I can tell it's not included in the source download -- Rob doesn't work with sqlite therefore it's not a priority for him to merge and test. I recommend looking my version since it solved several bugs for me. I have an even newer version that passes virtually all of the unit tests, modified for sqlite, but wonder if it's worthwhile uploading it. It was difficult to get the tests to pass due to database locking errors and the sql server bias in the core code -- sqlite has file level locking. The unit tests are not at all complete coverage.

Link to my SQLiteProvider.cs on github

My version has a slightly different GetDbType method, and I see for some brain dead reason I didn't include bool or boolean as a type to convert so I will add that.