Microsoft.Data.Sqlite: Improve SqliteDataReader.GetSchemaTable() DataType when no rows

[Perksey]

I'm using a custom DataAdapter i wrote to fill the void for the lack of one in this library, that is as follows:

using System;
using System.Data;
using System.Data.Common;
using Microsoft.Data.Sqlite;

namespace Ultz.BeagleFramework.SQLite
{
    public delegate void SqliteRowUpdatedEventHandler(Object sender, SqliteRowUpdatedEventArgs e);
    public delegate void SqliteRowUpdatingEventHandler(Object sender, SqliteRowUpdatingEventArgs e);
    public sealed class SqliteDataAdapter : DbDataAdapter
    {
        public event SqliteRowUpdatedEventHandler RowUpdated;

        public event SqliteRowUpdatingEventHandler RowUpdating;

        public SqliteDataAdapter() { }

        public SqliteDataAdapter(SqliteCommand selectCommand)
        {
            SelectCommand = selectCommand;
        }

        public SqliteDataAdapter(string selectCommandText, SqliteConnection selectConnection)
            : this(new SqliteCommand(selectCommandText, selectConnection)) { }

        public SqliteDataAdapter(string selectCommandText, string selectConnectionString)
            : this(selectCommandText, new SqliteConnection(selectConnectionString)) { }

        protected override RowUpdatedEventArgs CreateRowUpdatedEvent( DataRow dataRow,  IDbCommand command,
                                                                     System.Data.StatementType statementType,
                                                                      DataTableMapping tableMapping)
        {
            return new SqliteRowUpdatedEventArgs(dataRow, command, statementType, tableMapping);
        }

        protected override RowUpdatingEventArgs CreateRowUpdatingEvent( DataRow dataRow,  IDbCommand command,
                                                                       System.Data.StatementType statementType,
                                                                        DataTableMapping tableMapping)
        {
            return new SqliteRowUpdatingEventArgs(dataRow, command, statementType, tableMapping);
        }

        protected override void OnRowUpdated( RowUpdatedEventArgs value)
        {
            //base.OnRowUpdated(value);
            if (RowUpdated != null && value is SqliteRowUpdatedEventArgs)
                RowUpdated(this, (SqliteRowUpdatedEventArgs)value);
        }

        protected override void OnRowUpdating( RowUpdatingEventArgs value)
        {
            if (RowUpdating != null && value is SqliteRowUpdatingEventArgs)
                RowUpdating(this, (SqliteRowUpdatingEventArgs)value);
        }

        public new SqliteCommand DeleteCommand
        {
            get { return (SqliteCommand)base.DeleteCommand; }
            set { base.DeleteCommand = value; }
        }

        public new SqliteCommand SelectCommand
        {
            get { return (SqliteCommand)base.SelectCommand; }
            set { base.SelectCommand = value; }
        }

        public new SqliteCommand UpdateCommand
        {
            get { return (SqliteCommand)base.UpdateCommand; }
            set { base.UpdateCommand = value; }
        }

        public new SqliteCommand InsertCommand
        {
            get { return (SqliteCommand)base.InsertCommand; }
            set { base.InsertCommand = value; }
        }
    }

#pragma warning disable 1591

    public class SqliteRowUpdatingEventArgs : RowUpdatingEventArgs
    {
        public SqliteRowUpdatingEventArgs(DataRow dataRow, IDbCommand command, System.Data.StatementType statementType,
                                          DataTableMapping tableMapping)
            : base(dataRow, command, statementType, tableMapping) { }
    }

    public class SqliteRowUpdatedEventArgs : RowUpdatedEventArgs
    {
        public SqliteRowUpdatedEventArgs(DataRow dataRow, IDbCommand command, System.Data.StatementType statementType,
                                         DataTableMapping tableMapping)
            : base(dataRow, command, statementType, tableMapping) { }
    }

#pragma warning restore 1591
}

After creating a table, I then SELECT * FROM the table, and use SqliteDataAdapter.Fill(). However, all of the types are set to Int32. This also happens when using DataTable.Load().

[Perksey]

It's worth noting that this only happens to empty tables. If I manually correct the types, and add data to the DataTable, SELECT * FROM the table works perfectly fine.

[bricelam]

Related to #435. This happens because columns don't have static types in SQLite. It uses a dynamic type system. The type comes from each field, not the column definition. At its worst, this means each row could actually have a different type.

[bricelam]

We should apply the same decision for #435 to SqliteDataReader.GetSchemaTable() when there are no rows.

[bricelam]

(I'm leaning toward using the column's type affinity at this point.)

[JensNordenbro]

@Perksey, what license is the code you posted under?

[Perksey]

MIT