Is there a way to differentiate between SQL Money and Decimal types in .NET?

Question

Money and Decimal SQL types both get interpreted as Decimal in .NET. Is there any way to tell the difference? I am trying to format an Excel spreadsheet correctly, but do not know which fields should be formatted for currency and which should be decimals. For example:

+----------------+------------------+--------------+-----------------+
| Name (VarChar) | Weight (Decimal) | Cost (Money) | Inventory (Int) |
+----------------+------------------+--------------+-----------------+
| Widget         | 3.75             | 9.99         |              25 |
+----------------+------------------+--------------+-----------------+

With the headers of the above including the data types for this question's purposes and query being a stored procedure that selects from that table:

var result = conn.Query<dynamic>(query, parameters, commandType: CommandType.StoredProcedure).ToList();

foreach (var pair in (IDictionary<string, object>)result[0]) {
  Console.WriteLine($"{pair.Key}: {pair.Value.GetType().Name}");
}

Will result in:

Name: String
Weight: Decimal
Cost: Decimal
Inventory: Int32

I found this post: Money datatype and decimal type of SQL in .net but unfortunately, I do not have standardized column names as this is meant to convert any result set into an Excel spreadsheet.

I also considered changing the stored procedure(s) to pre-pend the dollar sign when needed, but this would be far more work and more than likely break other functionality.

Is there a different way to determine the SQL type of the results?

Answer 1

Here's my solution...

I created a localdb(v11.0) in VS2019 ([TestDb].[dbo].[Table]). It has 3 columns:

• Id(int)
• Number(decimal(18))
• Dollars(money)

Here's my code to get the schema and then get the ColumnName and its ProviderSpecificDataType:

    [TestMethod]
    public void GetProviderSpecificDataType()
    {
        using (SqlConnection connection = new SqlConnection("Server=(localdb)\\v11.0;Integrated Security=true;"))
        {
            connection.Open();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "select * from [TestDb].[dbo].[Table]";
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable table = reader.GetSchemaTable();

            var columnTypes = GetColumnTypes(table);

            Assert.AreEqual("SqlInt32", columnTypes["Id"]);
            Assert.AreEqual("SqlDecimal", columnTypes["Number"]);
            Assert.AreEqual("SqlMoney", columnTypes["Dollars"]);
        }
    }

    private Dictionary<string, string> GetColumnTypes(DataTable table)
    {
        Dictionary<string, string> data = new Dictionary<string, string>();

        foreach (DataRow row in table.Rows)
        {
            string columnName = row["ColumnName"].ToString();
            string dataType = ((Type)row["ProviderSpecificDataType"]).Name;

            data[columnName] = dataType;
        }

        return data;
    }

The test passes for me...Hopefully this will at least help you out.