System.Data.SQLite

Since the SQLite engine is inherently typeless and ADO.NET is much more strongly typed, the ADO.NET wrapper must make certain decisions about the columns returned in any given SQL query.  One of the deciding factors is the underlying datatype declared in the CREATE TABLE statement.

The following lists the datatypes and mappings the SQLite ADO.NET wrapper understands.  If there is anything missing, wrong, or needs adding, please let me know:

One more thing regarding the difference in mapping between INT and INTEGER:

The reason INT maps to Int32 and INTEGER maps to Int64 is compatibility.  Access databases use the INT keyword, which is (in Access) 32-bits.  Since one of the main reasons I wrote this wrapper was to allow people to replace their Access databases with SQLite, I figured people would have less bugs and problems porting this way.  However, there are people replacing their Access databases with SQLite because they want to break free of the 2gb database size limitation of Access and therefore need Int64 keys.  If that's a design goal then all you really need to do is change the SQLite table definition to use INTEGER instead of INT.

Robert

Now that I look at the code, it appears that "Datetime" will properly map to System.DateTime.

DateTime's are stored in one of two formats.  The default datetime format is ISO8601, and you specify the format in the connection string like Finisar does.  The varying ISO8601 strings that this class uses to convert are:

"yyyy-MM-dd HH:mm:ss",
"yyyyMMddHHmmss",
"yyyyMMddTHHmmssfffffff",
"yyyy-MM-dd",
"yy-MM-dd",
"yyyyMMdd",
"HH:mm:ss",
"THHmmss"

The other format is Ticks.  In the SQLiteConnection class in the helpfile is a description of all the connectionstring options.

Robert

NTEXT isn't on the above list. Is TEXT a good subsitute?

I'm getting a GetFieldType() returning null as a result.  

The NTEXT was in my table definition because it was just the opposite with SQLServerCE. It supports NTEXT but not TEXT.

This is fixed in the upcoming 1.0.28.0 release due out the end of this week or early next.

Robert

GetFieldType() still seems to return null for an NTEXT field in version 1.28.

Is there something I am doing wrong?

Hard to say.  I'll see if I can duplicate this.

Robert

This happens creating a dataset and dataadpater from a database table with NText.

It would be acceptable to use TEXT if it handles UNICODE characters okay. I don't know if that is the case.

Nathan 

I created a new table with an ntext column and it worked just fine for me.  Also, SQLite treats text, ntext, varchar, nvarchar and all other string types as all being equal.  If you open the database with UseUTF16Encoding=True in the connectionstring, then the entire database will be UNICODE.  Otherwise it'll be UTF-8.

Robert

I've resolved that. The program was still picking up 1.27.1 by mistake. I think I failed to save the project file when I changed it.

UTF-8 is just fine.

Nathan

The preferred method is to not set it at all.  Just create a new parameter, set the Value, and go.  The provider will call TypeToDbType() internally and set the DbType for you.

Robert

Yep I think I need to add unsigned support :)