System.Data.SQLite

Reading in this forum I found that the topic of supporting unsigned types came up a few times but isn't yet resolved. Mainly because there is no idea how to implement it. In my case I need to store FRN (FileReferenceNumbers) values in a table, which are UInt64. They use the whole address space, so converting from Int64 to UInt64 and the other way round throws exceptions. At the moment I work around this issue in using the BitConverter:

From UInt64 to Int64: BitConverter.ToInt64(BitConverter.GetBytes(FRN))
From Int64 to UInt64: BitConverter.ToUInt64(BitConverter.GetBytes(DataReader.GetInt64))

This works, but for me it seems not very elegant und also degrades performance. Why not overload the native calls to SQLite C-engine with one unsigned variant for Int64 and Double, so that the bytes stay the same, but everybody can decide himself if the values should be interpreted signed or unsigned? So the interop layer will pass the values from one side signed and the other side unsigned. I hope my bad English doesn't confuse you :-)

 If I want to do the changes myself, can you give me a hint, which places in the source code I should look for?

Thank you very much,

Andreas

Primarily the problem is that it doesn't fit into ADO.NET very well.  There is no GetUInt64() on DbDataReader, nor is there any other GetXXX function that returns an unsigned value.

I just tried a couple casts that seemed to work fine:

(UInt64)long.MinValue and got the right number ... I also tried (Int64)UInt64.MaxValue and got the right number.

What error are you getting when you try and cast?

Robert

If I use SQLiteParameter("@FRN", DbType.UInt64) for INSERT/UPDATE I get

System.OverflowException: Der Wert für einen Int64 war zu groß oder zu klein.
   bei System.Convert.ToInt64(UInt64 value)
   bei System.UInt64.System.IConvertible.ToInt64(IFormatProvider provider)
   bei System.Convert.ToInt64(Object value, IFormatProvider provider)
   bei System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param)
   bei System.Data.SQLite.SQLiteStatement.BindParameters()
   bei System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

Also, even if I disable Overflow checking in compiler, the cast still needs some time, I have around one million records, so I would like to have a "native" way.

I understand your argument that there is no DataReader.GetUInt64... at the moment I don't see a solution either. Maybe provide a derived DataReader with GetUInt64, .....

The only thing I know is that if you declare a C function like this

---------------------
[DllImport(SQLITE_DLL)]
    internal static extern void sqlite3_column_int64_interop(IntPtr stmt, int index, out long value);

[DllImport(SQLITE_DLL)]
    internal static extern void sqlite3_column_int64_interop(IntPtr stmt, int index, out ulong value);
-----------------

you can call this function with ulong and long without any casting, the marshaling just copies the bytes to and from the original long. And this is the fastest way.

Instead, use SQLiteParameter("@FRN", DbType.Int64)

and set the .Value = (Int64)myvalue; // where myvalue is a UInt64

Then when you read it back out you call (UInt64)datareader.GetInt64(x)

Robert

yes, that would work if the code wouldn't be in VB :-(

Despite the belief VB is in some areas more strict then C# and doesn't allow this cast, it even doesn't compile if I write UnsignedValue = Int64.MinValue

But I see, the most easy solution for now is to write this peace of code in C#. There was just my hope that there is an easy way to integrate unsigned types seamless.

Thank you very much,

Andreas