Problems with ExecuteScalar in Generated Typed DataSet code

Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Wed 08 Jun 2016 18:49

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
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Wed 08 Jun 2016 20:43

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
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Thu 09 Jun 2016 17:25

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
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Fri 10 Jun 2016 16:53

Help me out here, Shalex?
rmagruder
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby Pinturiccio » Mon 13 Jun 2016 13:40

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.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Thu 07 Jul 2016 14:10

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
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Thu 07 Jul 2016 14:32

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
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Thu 07 Jul 2016 16:23

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

Thank you.

Randy
rmagruder
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby Pinturiccio » Mon 11 Jul 2016 12:52

rmagruder wrote: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/oracle/docs/?DataTypeMapping.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/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~ConnectionString.html
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby rmagruder » Mon 11 Jul 2016 16:57

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 :)
rmagruder
 
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Problems with ExecuteScalar in Generated Typed DataSet code

Postby Pinturiccio » Thu 14 Jul 2016 13:01

rmagruder wrote: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.

rmagruder wrote: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.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle