How do I apply string.Format to SQL Results?

Question

In my application, I am querying the database, and would like to apply string.Format("{0:n}") to my results, so that the results have commas and decimals.

However, I am having trouble getting it to work correctly. The code compiles, and everything works fine, but the numbers are not formatted how I want them.

Here is my current code:

using (CacheConnection myConnection = new CacheConnection())
{
    myConnection.ConnectionTimeout = 9999;
    myConnection.ConnectionString = monthPropStruct.connectionString;
    myConnection.Open();

    using (CacheCommand myCommand = new CacheCommand(sqlTest, myConnection))
    {
        myCommand.CommandTimeout = 9999;
        using (CacheDataReader myReader = myCommand.ExecuteReader())
        {
            while (myReader.Read())
            {
                myWriter = new StreamWriter(myParameters.fullSaveDirectory, true);
                myWriter.WriteLine("Amount: $" + string.Format("{0:n}", myReader[myReader.GetOrdinal("total_amt")]));
                myWriter.Close();
             }
         }
     }
 }

The current result I am getting is "245687" or whatever, but I would like it display as 245,678.00

Answer 1

String formatting will work for numeric value, whereas your code myReader[myReader.GetOrdinal("total_amt")]) will return an object. Convert your value to numeric and then apply formatting like:

Convert.ToDecimal((myReader[myReader.GetOrdinal("total_amt")]))

Or use:

pReader.GetDecimal(pReader.GetOrdinal("total_amt"))

(I believe you have Decimal type for money, in any case it should be the one used for Money.)