Adkit Adkit - 2 months ago 9
C# Question

Use decimal in a column in a datatable

I can make a datatable fine as long as I use strings and as long as my int is an even number but when I want to use a number with a period in it I get errors.

static DataTable GetTable()
{
DataTable table = new DataTable();

table.Columns.Add("Date", typeof(string));
table.Columns.Add("Item Name", typeof(string));
table.Columns.Add("Buyer", typeof(string));
table.Columns.Add("Quantity", typeof(int));
table.Columns.Add("Price", typeof(string)); //I want this to be a decimal or whatever


using (TextFieldParser parser = new TextFieldParser("c:\\folder\\sold.csv"))
{
parser.CommentTokens = new string[] { "#" };
parser.SetDelimiters(new string[] { "," });
parser.HasFieldsEnclosedInQuotes = true;

parser.ReadLine();

while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();

table.Rows.Add(fields[0], fields[1], fields[2], fields[3], fields[4]);
}
}
return table;
}


The "Quantity" don't mind being an int since it's a "1" or a "2" but the "Price" gives me errors when it's "21.56" and so on. What am I missing?

I'm also having problems getting the "Date" to be a "DateTime" when it's "8/31/16" but I'm assuming that's because it actually needs to be three ints instead of a string...

Edit
It seemed it was a culture problem, I'm from Sweden and we use commas instead of decimals. Here's the code in case anyone is curious, I'm sure there's a more elegant way of doing this though.

static DataTable GetTable()
{
DataTable table = new DataTable();

table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Item Name", typeof(string));
table.Columns.Add("Buyer", typeof(string));
table.Columns.Add("Quantity", typeof(int));
table.Columns.Add("Price", typeof(decimal));

using (TextFieldParser parser = new TextFieldParser("c:\\folder\\sold.csv"))
{
parser.CommentTokens = new string[] { "#" };
parser.SetDelimiters(new string[] { "," });
parser.HasFieldsEnclosedInQuotes = true;

parser.ReadLine();

while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();

table.Rows.Add(DateTime.ParseExact(fields[0], "MM/dd/yy", null), fields[1], fields[2], int.Parse(fields[3]), Convert.ToDecimal(fields[4], new CultureInfo("en-US")));
}
}
return table;
}

Answer

Do not use string for every type of data.(I made the same mistake when i started writing code in C# few years back) . Decimal, Double and DateTime types are created to handle this kind of data.

You should use the decimal type for price

table.Columns.Add("Price", typeof(decimal));

For date, you should use DateTime

table.Columns.Add("Date", typeof(DateTime));

Columns.Add takes an an object array. So as long as your string values can be safely converted to these types, your code should work.

For example, The below code will work fine.

DataTable table = new DataTable();

table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Item Name", typeof(string));
table.Columns.Add("Buyer", typeof(string));
table.Columns.Add("Quantity", typeof(int));
table.Columns.Add("Price", typeof(decimal));

string[] fields = {"12/12/2013","test","Hello","3","345.45"};
table.Rows.Add(fields[0], fields[1], fields[2], fields[3], fields[4]);

Even though the items in the fields array is string type, they can be safely converted to the DateTime and decimal (For Date and Price column).