C Solutions Blog

PROBLEM:

   I was working with a gridview and needed to show the values from a field in currency format, but, not all of the entries had a value. Of course, if you try to convert a null value to a currency value you will get an error. But, I still needed the data outputted and wanted to make it easy for now and the future.

SOLUTION:

   I created a method which allows you to take a data table and have it transformed to include a string currency value for a field that has a monetary value, even if there are null values for some of the entries. Also, the method gives you the optional ability to delete the currency field.

   Before I get to the source code, I need to give some ground rules for the data table formatting which is needed for this to work. Of course, you need a field which will contain the monetary numeric value. Additionally, you will need a field for the string representation of the monetary numeric value. In its simplest form, below, is a sample SQL statement which achieves these goals.  Of course, your field names will be different for your tables, but, clearly, the example below is for representational purposes only.

SELECT

MonetaryValueField,

'                 ' AS sColumnToHoldCurrencyFormattedValue

FROM tblWithNumberValue

    The first column cited in the SELECT statement above is 'MonetaryValueField'. This would be whatever field you are using to fill the numeric currency value.

   The intent and use of the second column in the select statement, 'sColumnToHoldCurrencyFormattedValue', is as follows. As you can see, the default value is  '                 '. This is to allow plenty of space for whatever numeric value we may need to transform into a currency value. You can also see that I am using the SQL reserved word of  'AS'. I always omit 'AS'  from my SELECT statements in SQL Server, but, for maximum database compatibility sake I have it included here.    

   In my example I have a table name as 'tblWithNumberValue'.  Of course, this is purely a hypothetical tablename and you would substitute it with whatever table you would be using to extract your data values.

   So, above, we need to have a field which has a monetary value which we want to transform to a currency value, and we need a field in which to hold the string representation of the monetary value. So to hold the string representation of the monetary value I had simply added an additional field to my SQL query which includes a lot of empty space. This empty space will be transformed to the currency value, or if a currency value is lacking, a substitute string value. Of course, this could've been done programmatically with the data table, creating the string field, but I find it much easier, simpler, clearer, and cleaner to do it in SQL.

   Now, finally, let's get to the source code which allows us to take a monetary value and convert it to a string currency value, and if a value is missing, has a problem, or is null, we can substitute a proper text value as a placeholder.

   Here's the code:  

/// <summary>

/// Returns a datatable which has a string field which is filled with the currency 'look' for a money/currency/dollar amount.

/// </summary>

/// <param name="dt">The datatable which will have the process performed, and will possibly be transformed.</param>

/// <param name="sMoneyValueFieldName">The field name in the table which has the currency value in a numeric format.</param>

/// <param name="sFieldToShowCurrencyFormattedValue">The string field which will hold the string currency formatted field value.</param>

/// <param name="sStringValueIfNoValueInField">The value we will fill the string field if the money/numeric field has a null or unworkable value.</param>

/// <param name="bRemoveCurrencyNumericField">Boolean value which tells if you want to remove the column which has the numeric representation of the currency value.</param>

/// <returns>Returns the transformed datatable with a string field filled with a currency representation of the currency value.</returns>

public DataTable dtGetDataTableWithFieldCurrencyStringFormatted(

DataTable dt, string sMoneyValueFieldName, string sFieldToShowCurrencyFormattedValue,

string sStringValueIfNoValueInField, bool bRemoveCurrencyNumericField)

   foreach (DataRow dr in dt.Rows)

      try

       {

           double dblValue =  Convert.ToDouble (dr[sMoneyValueFieldName]);

           string sFormattedValue = dblValue.ToString("C");

           dr[sFieldToShowCurrencyFormattedValue] = sFormattedValue;

       }

       catch (Exception ee)

        {

           dr[sFieldToShowCurrencyFormattedValue] = sStringValueIfNoValueInField;

        }

   }

   if (bRemoveCurrencyNumericField)

      dt.Columns.Remove(sMoneyValueFieldName);

   return dt;

}

   As you can see above, the code example includes commenting but I will also go over it here to help make it all the more clearer. 

   As you can probably tell, I am a fan of descriptive variable and method names. For the purposes of this code example I made them much longer than I normally do, for clarity sake. Of course, the compiler doesn't care how long your variable names are. But, as you look back over your code after a few hours, days, weeks, or months, your memory can fade. So, I like to make my variable names stand out where they tell me what type of variable they are at the front of the variable name, and then in the base of the variable name what it is actually being used for. The way I look at it, the less I need to think on what's going on with my variables, the more I can focus on cleanly accomplishing the task at hand.

   As you can see in the source code we are giving the method a data table which is expected to include a field which has numeric values and another field which is to hold our string representation of that numeric field as a string value.

   Our code starts with a for each loop to go through each of the records within the data table. Inside of the loop is a try/catch code block. We start out in the try section to convert the given row's numeric field to a double value. Then we try to convert that double value into a string value formatted as a currency value. Then, this string value is placed into the field we reserved for it with a lot of blank space, but it will now hold the currency value for the number.

   Of course, sometimes things go wrong. For instance, if one of our numeric records has no value, or is merely null. But, we are all ready for that with our catch block. If that happens, we will simply put insert for our currency value the default we have passed as a parameter to the method. 

   Then, finally, after we have gone through all of the data tables rows, we have the option to delete the numeric column. I have this in there as you may want to display the record information in a grid view without any customized formatting by using the default of the data tables values, and field names. Since you wouldn't want to have the numeric value in there twice, once as the number the other as a string representation, I have included ability to delete the numeric column as an option.

   Lastly, the method returns the final version of the data table. 

   All that said above, here is a sample call to the method above to return the transformed data table which includes the currency value for the numeric field value given.

dt = dtGetDataTableWithFieldCurrencyStringFormatted(dt, "MonetaryValueField", "sColumnToHoldCurrencyFormattedValue", "N.A.", true);

   As you can see in the line of code above I am setting a variable called 'dt' to be equal to the value returned by the method which includes 'dt' as a parameter. Of course, 'dt' refers to a data table which we have already populated, normally from a database. Unfortunately this discussion does not include how to do that and presupposes you already know how to to accomplish that goal.

    That said, you can also see we have as our second parameter, "MonetaryValueField". This is the name of our SELECT statement field which contains the numeric/monetary value. Note. This is not need to be a money column, integer column, but simply one which could hold a value which is convertible into a numeric value. It's your choice. In SQL Server, I usually, as you'd imagine, have this as a 'Money' or 'SmallMoney' type field.

   Our third parameter is, "sColumnToHoldCurrencyFormattedValue". As you will recall, this is our SQL SELECT string column placeholder for our currency value to output.

    Our fourth parameter value is, "N.A.".  This is the value we are telling the method to use if it is unable to convert a fields the numeric value to a double variable type. For me at the time, this works. Perhaps for your needs this may need to be "$0.00". You're given the flexibility to have whatever default value you'd like there with this parameter setting.

    Our fifth, and last parameter value is, 'true'. We are sending a Boolean value to the method telling it to delete the column which has the original numeric value. You may want to keep the numeric field column in the datatable. If so, simply send this parameter as 'false'.

   I hope and trust this code and description helps you, your work, and your job.

   If you or your company needs .NET programming or database assistance, feel free to contact us here at C Solutions. We are here to help.

   Have a great day.