CODEWITHSUNDEEP
c#.nett-sqldapperclosedxml
Dryden Long
Dryden Long

Reputation: 10182

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

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?

Upvotes: 2

Views: 698

Answers (3)

Kaz
Kaz

Reputation: 731

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.

Upvotes: 2

Calyfs0
Calyfs0

Reputation: 119

I would suggest you to use Types under programmability in SQL (User defined types).

You can create a type like we create a class which consists of Name, weight, cost and inventory in SQL and in procedure where you are returning these values, add them to the user defined type you just created and send it as a collection.

and in .Net create a class consisting of same properties(Name, weight, cost and inventory) and get the value there.

Upvotes: 0

zambonee
zambonee

Reputation: 1647

I have run into similar issues before, and I have just queried the columns from sys.columns INNER JOIN sys.types ON columns.user_type_id = types.user_type_id. But it looks like you are using a stored procedure. In SQL Server 2012 and above, you can find a stored procedure's metadata with sys.dm_exec_describe_first_result_set_for_object(@object_id,1).

Upvotes: 1

Related Questions