Trailing Zero missing from Data in Data Table

[dyslexicanaboko]

Hello All,

I am trying to debug this issue and I will tell you what led up to this problem. Recently we (the company I work for) upgraded their DB to MySQL 5.1 and since then for some reason or another columns with the data type decimal will be missing their trailing zeros when they are put into a datatable. I am upgraded to the latest provider (dotConnect) as well.

I am posting here because when I run a query on the MySQL 5.1 and I check the column with the decimal data type (lets call this amount) the trailing zeros DO show up.

However when I run the same query in code, the data that is returned is missing its trailing zero.

For example:

Column: Amount decimal(8,2)

Example 1:
Data in Data Base: 100.10
Data in Data Table: 100.1

Example 2:
Data in Data Base: 125.00
Data in Data Table: 125

Under normal conditions this wouldn't be a big deal, but because this is accounting software the client is complaining that they want their zeros back.

If this isn't a data provider issue, I will post in the MySQL forums next.

Any recommendations?

Thanks in Advance.

[dyslexicanaboko]

I was informed by one of my colleagues that we were using a different provider before with the previous version of MySQL. When we upgraded to MySQL 5.1 we changed our provider to the Devart provider, so now I am 90% sure it is the provider.

[StanislavK]

The decimals stored in databases have trailing zeros as it is the way MySQL represents them. The decimals stored in DataTable are of the .NET type "decimal" which has no information about their precision. Thus, when the decimals from DataTable are displayed, e.g. in some Grid class, they are converted to strings by the standard ToString() method.

Probably, the provider you've previously used had specific ToString() conversion which added trailing zeros. dotConnect for MySQL doesn't have such functionality, hence we can only advice you to customize the controls in which you display data.

[dyslexicanaboko]

Thanks for the reply. We were tinkering around with the properties of the Devart.Data.MySql dll and here is what we found:

Code: Select all

MySqlCommand cmd = new MySqlCommand(SQLStatementHere, ConnectionStringHere);
cmd.CommandTimeout = 0;

MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.ReturnProviderSpecificTypes = true;
adapter.SelectCommand = cmd;
dataset = new DataSet();
adapter.Fill(dataset);

If I set the "ReturnProviderSpecificTypes" to true, it fixes the above issue I was having, however it creates another problem for me and that is I can't convert the provider types to a native .Net type.

Example Problem:

In our code, when this point is reached:

Code: Select all

PercentOwned = Convert.ToDecimal(ds.Tables[0].Rows[0]["Percent_Owned"]);

This exception occurs:
"Unable to cast object of type 'Devart.Data.MySql.MySqlDecimal' to type 'System.IConvertible'."

This can be remedied by doing the following:

Code: Select all

PercentOwned = Convert.ToDecimal(ds.Tables[0].Rows[0]["Percent_Owned"].ToString());

However I am trying to find the easiest solution for us because this would require a large number of code changes. Same problem with updating all my grids. I am trying to find a single point solution if possible.

Any recommendations?

If not I already have a few choices now, just choosing the one that hurts the least.

Thank you for your time

[dyslexicanaboko]

I found another issue in the mean time as well, is there a System.DBNull equivalent for the provider? The reason I ask is because a statement like this...

Code: Select all

if(ds.Tables[0].Rows[0]["Percent_Owned"] != DBNull.Value)
    //Do something...

...does not evaluate properly if I have the "ReturnProviderSpecificTypes" set to true.

Meaning if the value of "ds.Tables[0].Rows[0]["Percent_Owned"]" is null, it is not equivalent to the value of "DBNull.Value", which makes sense if I am not using dotNet types and using provider types instead.

I tried searching the forum and the reference pages for a Devart.Data.MySql DNull equivalent, but I couldn't find any.

Any recommendations on this?

[StanislavK]

To resolve the problem with converting MySqlDecimal you can try creating your own conversion function, e.g.

Code: Select all

class MyConverter
{
    public static decimal ToDecimal(object value)
    {
        if (value is MySqlDecimal)
            return (decimal)((MySqlDecimal)value);
        else return System.Convert.ToDecimal(value);
    }
}

Though this solution is not completely one-point, at least you'll be able to auto-replace Convert.ToDecimal with MyConverter.ToDecimal.

As for DBNull issue, you can use the IsNull property casting objects like ds.Tables[0].Rows[0]["Percent_Owned"] to System.Data.SqlTypes.INullable interface.

[dyslexicanaboko]

Thank you for your reply.

[szaheer]

Hi,

I just came across this post & found that this is what the issue I have been facing.
I was curious to know if Devart has come up with any solution for the issue concerned here in any of latest version of dotConnect for MySql after 2009.

I have used Devart.Data.MySql - 8.1.45.0.

Looking forward to have solution,

[Pinturiccio]

When in a database, a table column is of the Decimal(*,*) type, then when using the Fill method of the MySqlDataAdapter type, the DataType property of the corresponding DataTable column will be of the System.Decimal type. Consequently, when you set this DataTable as a DataSource for DataGridView, the value of the System.Decimal type will be transmitted to the DataGridView column, what consequently results in that the reqired nulls are not displayed.

If you set the ReturnProviderSpecificTypes = true property for MySqlDataAdapter, then, when using the Fill method of the MySqlDataAdapte type, the DataType property of the corresponding DataTable column will be of the Devart.Data.MySql.MySqlDecimal type. And if you set the given DataTable as a DataSource for DataGridView, then the required nulls will be displayed in the column of the Decimal type.