ADO.Net reporting empty data type in SQLite

Question

I am trying to dynamically get the schema of one of my views in SQLite using C#. I am using this code:

using (var connection = new SQLiteConnection(ConnectionString))
{
    connection.Open();
    using (DataTable columns = connection.GetSchema("Columns"))
    {
        foreach (DataRow row in columns.Rows)
        {
            string dataType = ((string) row["DATA_TYPE"]).Trim();
            // doing irrelevant other stuff here
        }
    }
}

Its working perfectly for all my tables and views except for one view. For some reason, the data type for the field called SaleAmount is coming up blank. There is nothing in the row["DATA_TYPE"] element.

Here is my view:

SELECT [Order Subtotals].Subtotal AS SaleAmount, 
              Orders.OrderID, 
           Customers.CompanyName, 
              Orders.ShippedDate
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID 
WHERE ([Order Subtotals].Subtotal >2500) 
AND (Orders.ShippedDate BETWEEN DATETIME('1997-01-01') And DATETIME('1997-12-31'))

I am using the standard System.Data.SQLite libraries. Anyone have any idea why this would come up blank? Like I said, it is happening only on this one field in this one view.

UPDATE

I figured out how to duplicate the issue. Apparently if a view contains an aggregate function, such as Sum, GetSchema returns an empty data type. Anyone know of a workaround?

Answer 1

System.Data.SQLite supports an SQL-based "workaround" for exactly this scenario.

Have a look here for instructions:

http://system.data.sqlite.org/index.html/artifact/c87341d109c60a64

You're SQL should look something like this (sorry I'm not at a real PC to check what data type names to use, but the principle is there):

TYPES [DECIMAL], [INTEGER], [STRING], [DATETIME];
SELECT [Order Subtotals].Subtotal AS SaleAmount, 
        Orders.OrderID, 
        Customers.CompanyName, 
        Orders.ShippedDate
etc...

UPDATE I got to a real PC. The above will ensure GetSchema has data types for all columns.

NOTE: It appears, from my own tests, that you can't have the TYPES keyword within a VIEW definition. It can only be present in your .NET code when you execute your SELECT statement to grab data. Of course, you can use the TYPES + SELECT workaround to grab data from a VIEW definition, no problem there ;-)