Brian B. Brian B. - 3 months ago 8
C# Question

How do I apply string.Format to SQL Results?

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

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.)

Comments