Problems with ExecuteScalar in Generated Typed DataSet code

[rmagruder]

I migrated some legacy typed dataset code to DevArt. The generated dataset contains functions like this:

global::Devart.Data.Oracle.OracleCommand command = ((global::Devart.Data.Oracle.OracleCommand)(this.CommandCollection[0]));
command.Parameters[0].Value = ((decimal)(myParameter));
global::System.Data.ConnectionState previousConnectionState = command.Connection.State;
if (((command.Connection.State & global::System.Data.ConnectionState.Open)
!= global::System.Data.ConnectionState.Open)) {
command.Connection.Open();
}
object returnValue;
try {
returnValue = command.ExecuteScalar();
}
finally {
if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
command.Connection.Close();
}
}
if (((returnValue == null)
|| (returnValue.GetType() == typeof(global::System.DBNull)))) {
return new global::System.Nullable<decimal>();
}
else {
return new global::System.Nullable<decimal>(((decimal)(returnValue)));
}

This code throws a cast exception on the last line where the returnValue object is cast to a decimal. The reason is that the DevArt OracleCommand is executing and returning an object whose underlying type is an Int32. There's no reason I can see for it to do this. The number returned is a 6-digit figure 316169. I have rather a lot of code, so it would be problematic to hand convert all the typed dataset generation code to properly cast the Int32 object to a decimal by first unboxing it or casting to a decimal.

Is there any way to either configure the provider to return these ExecuteScalars as Decimal at the configuration level? Or does DevArt provide its own typed dataset generator that can replace the Microsoft MSDataSetGenerator custom tool which is spitting out this code? It should be noted that the XSD file containing the metadata for the dataset generator DOES specify the return value should be System.Decimal, but it's like the DevArt provider just returns an Int32 boxed in an object.

[rmagruder]

Let me amend my previous post. I did find reference to the cusotm tool dcOracleDataSetGenerator. But when I right click on an XSD and click 'Run Custom Tool' after setting that, I get a dialog box with

Cannot find custom tool 'dcOracleDataSetGenerator' on this system.

I've checked the registry and the entries are there under
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\12.0\Generators]

(I'm using VS2013).
I've uninstalled and re-installed dotConnect twice, with no luck.

A coworker of mine does NOT have this problem. Any idea how to troubleshoot the custom tool? I've double/triple checked the spelling, the registry ,everything I can think of. It SHOULD find and run that tool.

Thanks,
Randy

[rmagruder]

Okay, I found out there is a registry entry for the GUID of the DevArt.Data.Oracle.Designer.Dll. In the "Inproc32" node, there was a node missing called ImplementedInThisVersion that was missing. (I found that devenv.exe was querying it and not finding it.) By adding a DWORD of 1 for this, suddenly it found and ran dcOracleDataSetGenerator.

All good..however, when I run the tool on my legacy xsd, all the Queries defined in the dataset designer are missing from the designer.cs. They are just left out.

Why???? Does dotConnect NOT support Queries in a dataset? Why did it not generate methods and command arrays ala Microsoft's generator?

[rmagruder]

Help me out here, Shalex?

[Pinturiccio]

dcOracleDataSetGenerator is used for a typed Devart DataSet, that is created with the Devart DataSet wizard. A DataSet, generated with the Devart DataSet wizard differs substantially from a DataSet generated with the Microsoft DataSet wizard. Query is not supported in the Devart DataSet wizard.

The ExecuteScalar method returns a value of a type, that is used by the first column and first row of the query execution result.

Please send us the DataSet and DDL scripts of the corresponding database objects to help us study the issue in more details.

[rmagruder]

Sorry I've been slow to get back to you on this.

I will package up and send that information as soon as I can.

The chilling words you used were "not supported".

[rmagruder]

We just recently renewed our devart license, and we had been running 8.4.225, and now we're on the current DevArt.

My question to you is this - were any fixes made to ExecuteScalar so that they would return a Decimal boxed in an 'object' instead of an Int32? Because I just ran LinqPad after updating DevArt to current and now on a ExecuteScalar for a SELECT COUNT, it's returning a Decimal as the underlying type instead of an Int32, as I was seeing back in 8.4.225.

If this is so, my hacking of edit designer.cs files to cast from the object to (int) then (decimal) to fix the problem are not only no longer needed, they will cause errors as the underlying type would now be decimal.

Randy

[rmagruder]

I have sent your support address the information you need. Standing by.

Thank you.

Randy

[Pinturiccio]

now on a ExecuteScalar for a SELECT COUNT, it's returning a Decimal as the underlying type instead of an Int32

A query like SELECT COUNT(*) returns the result, having the Number type, which is automatically mapped to Decimal.

When a database field has the type Number(7,0), the data returned from this field will have the Int32 type. For more information, please refer to https://www.devart.com/dotconnect/oracl ... pping.html

You can change the standard number mapping. For this, you can specify the following value for the "Number Mappings" connection string parameter: (Integer, 0, 7, System.Decimal). In this case a dataset created for such a connection will return System.Decimal for all columns of type Number(x,0), where x is between 0 and 7. For more information, please refer to https://www.devart.com/dotconnect/oracl ... tring.html

[rmagruder]

Thanks for the reply. I did notice the number mappings, and that it could be added to the connection string. We are looking at modifying our connection strings to make DevArt more 'backwards-compatible'.

My first suggestion is updating your documentation for using DevArt to migrate Ado.net code from System.Data.Oracle to DevArt, since it can break datasets generated with the MS Dataset generator, and you guys don't support custom Query types in your dataset generator. Your suggestion for custom Number Mappings was eventually discovered after a lot of searching, but I wish it had been easier to find. All the custom number mappings I found were to do with Entity Framework support, and this was not for EF.

My second suggestion is adding support for query types

[Pinturiccio]

My first suggestion is updating your documentation for using DevArt to migrate Ado.net code from System.Data.Oracle to DevArt

Thank you for your suggestion. We will investigate it.

My second suggestion is adding support for query types

Adding support for query types in Devart dataset isn't on our to do list and we won't implement it in the near future. We can't tell any timeframe at the moment.