Dates returned as String?

[RickStrahl]

I'm running the following code against a SqLite db and I'm getting back dates as strings:

        var db = new SqlDataAccess("Data Source=" + DataPath, DataAccessProviderTypes.SqLite);
        using (var reader = db.ExecuteReader("select * from Localizations"))
        {
            while (reader.Read())
            {
                var updated = reader["Updated"];
                Console.WriteLine(updated);
                Assert.IsTrue(updated != null, "Updated shouldn't be null");
                Assert.IsTrue(updated.GetType() == typeof(DateTime),"Invalid updated type: " + updated.GetType());
            }
        }

against a Db defined like this:

CREATE TABLE [{0}] (
   [Pk] INTEGER PRIMARY KEY 
   , [ResourceId] nvarchar(1024) COLLATE NOCASE NOT NULL
   , [Value] ntext  NULL
   , [LocaleId] nvarchar(10) COLLATE NOCASE DEFAULT '' NULL
   , [ResourceSet] nvarchar(512) COLLATE NOCASE DEFAULT ''  NULL
   , [Type] nvarchar(512) DEFAULT '' NULL
   , [BinFile] image NULL
  , [TextFile] ntext NULL
  , [Filename] nvarchar(128) NULL
  , [Comment] nvarchar(512) NULL
   , [ValueType] unsigned integer(2) DEFAULT 0
  , [Updated] datetime  DEFAULT CURRENT_TIMESTAMP   <---- 
);

Values are filled and they're coming back as string values as a string in this format:

 "2017-11-11 22:41:32"

FWIW, the helper above just creates a connection and executes the reader on it - and data is otherwise returned (so the commands are executing properly), just the date is coming back as a string.

While I understand that SqLite doesn't have a 'native' DateTime type and uses a mapped string type (in this case) the old System.Data.SqLite driver managed to do the conversion transparently.

It would be nice if this driver did this as well.

[bricelam]

System.Data.SQLite did this by applying additional semantics to column types. We decided to stick as close to native SQLite semmantics. GetValue() (and the indexer) will only return database primitive types. If you want the primitive value coerced, ask for a specific type.

If you want to mimic System.Data.SQLite, you can write a wrapper that looks at the column type to determine the return type.

[bricelam]

Note, they also had to parse every command before sending it to SQLite for a special TYPES statement so users could specify the column type for expressions in the SELECT clause.

[RickStrahl]

Type coercion is not really an option if you're writing code that's provider agnostic - this is exactly where this is biting me.

My use cases is that I have a Db localization provider that works with several provider types - Sql Server, MySql and SqLite. All Db access goes through DbXXXX interfaces never through the specific provider. The provider used is configured via configuration settings (application.json, or Startup config).

Yes I can work around it in this case - I can subclass the SqLite implementation and override each method that accesses a date type (which in this case happens to be only 2 places), but in more realistic application scenarios this wouldn't be so simple.

To turn this around think of it this way:

I can't imagine any application where you would not want date conversion to happen automatically.

If it is possible to do the conversion at the provider level, why would you want to force that conversion on every single application using this provider externally - with duplication of untold amounts of code collectively?

The whole point of ADO.NET's provider model is to provide consistency and if there's a reasonable way to do this in the provider I honestly think that the provider should provide that functionality.

I understand if you don't but that's my 2 cents at least :-)

ps.
The lack of consideration that is given to provider agnostic processing in all of the Microsoft.Data related packages is really annoying. This was one of the big tenants in ADO.NET and one of the big features that made for its success and staying power over the lifetime of .NET so far. I hope more effort can go into that in the future because I feel this is an important aspect that should not be shoved aside.

[AlexanderTaeschner]

Hi, if you want to be provider agnostic, why do you use DbDataReader.GetDateTime then?

[RickStrahl]

Not sure what you mean. I'm not using DbDataReader.GetDateTime(). I'm reading raw values and they are pushed into entities via a generic helper. The helper reads raw reader["key"] values and maps the vallue type to matching properties. This doesn't go well for string values that want to map to dates.

[AlexanderTaeschner]

Yes, I see that you do not use DbDataReader.GetDateTime, but why? You know that Sqlite does not recognize your table definition "[Updated] datetime" as defining a date - it simply assigns NUMERIC type affinity to this column. Since you populated it with text values containing the date, you get back text values (since TEXT is the data type the library gets back from Sqlite). If you know your desired .NET data type use this knowledge and use the specific DbDataReader.Get function (so GetDateTime here).

[bricelam]

If it is possible to do the conversion at the provider level...

It's not possible without adding application logic to Microsoft.Data.Sqlite. Any time you do that in a library, it's never correct for every application. We'd have to add hooks to override the default behavior. It's a can of worms. Ultimately, we decided to keep Microsoft.Data.Sqlite simple and predictable and enable this type of application logic to easily be layered on top of it.

+1 to what @AlexanderTaeschner is suggesting. If you're expecting a DateTime value, your code should look like this:

var ordinal = reader.GetOrdinal("Updated");
var updated = reader.IsDBNull(ordinal)
    ? null
    : reader.GetDateTime(ordinal);

[ericsink]

It's not possible without adding application logic to Microsoft.Data.Sqlite.

Well said. I keep wanting to post a comment on this thread saying that I like the current design, but I had trouble figuring out to explain why.

[AlexanderTaeschner]

I'm wondering if it would help the users if we would add a GetDateTime(string columnName) function (which maps to GetDateTime(GetOrdinal(columnName))?

[bricelam]

Could file an issue on dotnet/corefx to add them to DbDataReader. I don't think adding them locally would be useful since it deviates from the ADO.NET contract.

[bricelam]

I think I remember seeing a library that adds continent ADO.NET extension APIs like this, but I can't remember its name. (No trolls, it's not called "Dapper".)