Return value of DbCommand.ExecuteScalar()

[Anton Shepelev]

Hello all

I suspect that a third-party ADO.NET data provider I
am using at work is broken, but I cannot prove it to
their support team.

I have a situation where DbCommand.ExecuteScalar()
returns a box object with a value of a type that is
not a native .NET type, i.e. it is OurOwnDecimal
rather than decimal. I cannot handle this value
without an explicit reference to a third-party as-
sembly that implements it. Whereas I am working on
a generic ADO.NET wrapper, I do not want it to de-
pend on anything outside System.Data and System.Da-
ta.Common.

Does the ADO.NET standard stipulate that DbCommand.
ExecuteScalar() shall return objects that may be
cast or unboxed to native .NET types?

[Marcel Mueller]

Post by Anton Shepelev
I have a situation where DbCommand.ExecuteScalar()
returns a box object with a value of a type that is
not a native .NET type, i.e. it is OurOwnDecimal
rather than decimal.

You should be able to examine this with the debugger easily.

Post by Anton Shepelev
Does the ADO.NET standard stipulate that DbCommand.
ExecuteScalar() shall return objects that may be
cast or unboxed to native .NET types?

There is a type mapping between SQL types and .NET types. This is
documented. But at the end it is up to the database driver to implement
the behavior.

If you are in doubt use a decompiler like ILSpy or Reflector and have a
look into the driver code.


Marcel

[Anton Shepelev]

I have a situation where DbCommand.
ExecuteScalar() returns a box object with a val-
ue of a type that is not a native .NET type,
i.e. it is OurOwnDecimal rather than decimal.

You should be able to examine this with the debug-
ger easily.

You misunderstand my question. I did find that us-
ing the debugger and report it as fact. My question

Does the ADO.NET standard stipulate that
DbCommand.ExecuteScalar() shall return objects
that may be cast or unboxed to native .NET
types?

There is a type mapping between SQL types and .NET
types. This is documented.

Do you mean the documentation for MS SQL server, or
the documentation for ADO.NET? If the latter, than
can you please post a link or at least hint where to
look for it?

If you are in doubt use a decompiler like ILSpy or
Reflector and have a look into the driver code.

I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.

I think it does, because such a behavior prevents
the writing of provider-independent code, which is a
fundamental feature of ADO.NET with its inversion of
control:

https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx

I need an official document that proves it.

[Arne Vajhøj]

Post by Anton Shepelev
I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.
I think it does, because such a behavior prevents
the writing of provider-independent code, which is a
fundamental feature of ADO.NET with its inversion of
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

I don't think it violates the ADO.NET standard.

First then there is as far as I know no official ADO.NET specification,
so it is difficult to violate a standard that is not clearly defines

Second MS seems to hint it being OK in its documentation.

https://msdn.microsoft.com/en-us/library/aa720160.aspx

on how to write a data reader clear states that it can return
custom data types.

https://msdn.microsoft.com/en-us/library/aa720687.aspx

is a C# template for writing a command and it shows ExecuteScalar
returning same data types as data reader.

Combining those will result in ExecuteScalar being able to return
custom data types.

Arne

[Anton Shepelev]

Post by Arne Vajhøj

I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.
I think it does, because such a behavior prevents
the writing of provider-independent code, which is
a fundamental feature of ADO.NET with its inver-
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

I don't think it violates the ADO.NET standard.
First then there is as far as I know no official
ADO.NET specification, so it is difficult to vio-
late a standard that is not clearly defines

That's too bad.

Post by Arne Vajhøj
Second MS seems to hint it being OK in its documen-
tation.
https://msdn.microsoft.com/en-us/library/aa720160.aspx
on how to write a data reader clear states that it
can return custom data types.

Yes, but:

Data types from your data source will be stored in
your .NET-based application as .NET Framework
types.
[...]
If your .NET Framework data provider has propri-
etary types that cannot adequately be exposed as
.NET Framework types, you may extend the inter-
faces to support proprietary types, then add typed
accessors for your DataReader that return propri-
etary types as well.

It seems to imply that these proprietary types shall
be exposed only in strongly typed accessors, whereas
the weakly-typed one shall still return exclusively
.NET types.

The "SQL Server Data Type Mappings"

https://msdn.microsoft.com/en-us/library/cc716729.aspx

lists a native .NET type for each proprietary type.
This .NET type is returned from ExecuteScalar(),
whereas the proprietary type -- form the correspond-
ing stronly typed accessor.

Post by Arne Vajhøj
https://msdn.microsoft.com/en-us/library/aa720687.aspx
is a C# template for writing a command and it shows
ExecuteScalar returning same data types as data
reader.

It uses the weakly typed accessor, which I think
must always return a native .NET type...

Thanks for the links.

[Anton Shepelev]

Second MS seems to hint it being OK in its docu-
mentation.
https://msdn.microsoft.com/en-us/library/aa720160.aspx
on how to write a data reader clear states that it
can return custom data types.

Data types from your data source will be stored
in your .NET-based application as .NET Framework
types.
[...]
If your .NET Framework data provider has propri-
etary types that cannot adequately be exposed as
.NET Framework types, you may extend the inter-
faces to support proprietary types, then add
typed accessors for your DataReader that return
proprietary types as well.
It seems to imply that these proprietary types
shall be exposed only in strongly typed accessors,
whereas the weakly-typed one shall still return ex-
clusively .NET types.
The "SQL Server Data Type Mappings"
https://msdn.microsoft.com/en-us/library/cc716729.aspx
lists a native .NET type for each proprietary type.
This .NET type is returned from ExecuteScalar(),
whereas the proprietary type -- form the corre-
sponding stronly typed accessor.

See also their introduction:

SQL Server and the .NET Framework are based on
different type systems. For example, the .NET
Framework Decimal structure has a maximum scale of
28, whereas the SQL Server decimal and numeric da-
ta types have a maximum scale of 38. To maintain
data integrity when reading and writing data, the
SqlDataReader exposes SQL Server-specific typed
accessor methods that return objects of System.
Data.SqlTypes as well as accessor methods that re-
turn .NET Framework types.

[Arne Vajhøj]

Post by Anton Shepelev
SQL Server and the .NET Framework are based on
different type systems. For example, the .NET
Framework Decimal structure has a maximum scale of
28, whereas the SQL Server decimal and numeric da-
ta types have a maximum scale of 38. To maintain
data integrity when reading and writing data, the
SqlDataReader exposes SQL Server-specific typed
accessor methods that return objects of System.
Data.SqlTypes as well as accessor methods that re-
turn .NET Framework types.

Yes.

But that does not say that ExecuteScalar will return
a .NET FX type.

And if the provider is using the MS template for
command then it will not as that just return the
internal representation.

And furthermore if there is no .NET FX type that
can represent the data properly then it is not
possible.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Anton Shepelev
SQL Server and the .NET Framework are based on
different type systems. For example, the .NET
Framework Decimal structure has a maximum scale of
28, whereas the SQL Server decimal and numeric da-
ta types have a maximum scale of 38. To maintain
data integrity when reading and writing data, the
SqlDataReader exposes SQL Server-specific typed
accessor methods that return objects of System.
Data.SqlTypes as well as accessor methods that re-
turn .NET Framework types.

Yes.
But that does not say that ExecuteScalar will return
a .NET FX type.
And if the provider is using the MS template for
command then it will not as that just return the
internal representation.
And furthermore if there is no .NET FX type that
can represent the data properly then it is not
possible.

All that said, then I would also expect that
ExecuteScalar returned a .NET FX type if there
exists a suitable type.

It is just so much nicer.

I still remember when I did a SELECT SUM(integerfield) ...
and was rather surprise that ExecuteScalar gave me
a double forcing me to do:

int sum = (int)(double)cmd.ExecuteScalar();

There are reasons for that - it is not guaranteed that
a sum of int's can be in an int.

But it was not what I was expecting.

Arne

[Anton Shepelev]

All that said, then I would also expect that
ExecuteScalar returned a .NET FX type if there exists
a suitable type.
It is just so much nicer.

About this we agree.

I still remember when I did a
SELECT SUM(integerfield) ...
and was rather surprise that ExecuteScalar gave me a
int sum = (int)(double)cmd.ExecuteScalar();
There are reasons for that - it is not guaranteed that
a sum of int's can be in an int.

Well, is not a sum of INTs an INT in that RDBMS your
were using? Of course, if in your scenario the result
may exceed Int32 and Int64, then an exponential (float-
ing-point) number must be used.

[Arne Vajhøj]

Post by Anton Shepelev

Post by Arne Vajhøj
I still remember when I did a
SELECT SUM(integerfield) ...
and was rather surprise that ExecuteScalar gave me a
int sum = (int)(double)cmd.ExecuteScalar();
There are reasons for that - it is not guaranteed that
a sum of int's can be in an int.

Well, is not a sum of INTs an INT in that RDBMS your
were using? Of course, if in your scenario the result
may exceed Int32 and Int64, then an exponential (float-
ing-point) number must be used.

Apparently. Some OleDb stuff. Long time ago so I don't
remember any details.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Anton Shepelev
SQL Server and the .NET Framework are based on
different type systems. For example, the .NET
Framework Decimal structure has a maximum scale of
28, whereas the SQL Server decimal and numeric da-
ta types have a maximum scale of 38. To maintain
data integrity when reading and writing data, the
SqlDataReader exposes SQL Server-specific typed
accessor methods that return objects of System.
Data.SqlTypes as well as accessor methods that re-
turn .NET Framework types.

Yes.
But that does not say that ExecuteScalar will return
a .NET FX type.
And if the provider is using the MS template for
command then it will not as that just return the
internal representation.
And furthermore if there is no .NET FX type that
can represent the data properly then it is not
possible.

I have been searching for an example of something
non representable as a .NET FX type being returned
from ExecuteScalar.

Not easy to find.

But finally I found an example with PostgreSQL
ADO.NET provider:

https://stackoverflow.com/questions/42337076/how-to-return-custom-table-types-from-npgsql-and-stored-procedures

Arne

[Anton Shepelev]

Post by Anton Shepelev
SQL Server and the .NET Framework are based on
different type systems. For example, the .NET
Framework Decimal structure has a maximum scale of
28, whereas the SQL Server decimal and numeric da-
ta types have a maximum scale of 38. To maintain
data integrity when reading and writing data, the
SqlDataReader exposes SQL Server-specific typed
accessor methods that return objects of System.
Data.SqlTypes as well as accessor methods that re-
turn .NET Framework types.

Yes.
But that does not say that ExecuteScalar will return a
.NET FX type.
And if the provider is using the MS template for com-
mand then it will not as that just return the internal
representation.

That code works with SampleDb.SampleDbResultSet:

https://msdn.microsoft.com/en-us/library/x4tyt7c4(v=vs.71).aspx

which stores only native .NET types by design:

public class SampleDbResultSet
{
public struct MetaData
{
public string name;
public Type type;
public int maxSize;
}

public int recordsAffected;
public MetaData[] metaData;
public object[,] data;
}

And furthermore if there is no .NET FX type that can
represent the data properly then it is not possible.

Indeed. But Microsoft have managed to map all the types
of SQL Server to .NET FX types.

[Arne Vajhøj]

Post by Anton Shepelev

And furthermore if there is no .NET FX type that can
represent the data properly then it is not possible.

Indeed. But Microsoft have managed to map all the types
of SQL Server to .NET FX types.

All builtin types.

But you can define a UDT in SQLServer using CLR code.

And I would assume that would have the problem.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Anton Shepelev

And furthermore if there is no .NET FX type that can
represent the data properly then it is not possible.

Indeed. But Microsoft have managed to map all the types
of SQL Server to .NET FX types.

All builtin types.
But you can define a UDT in SQLServer using CLR code.
And I would assume that would have the problem.

Verified.

Demo code:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

using Microsoft.SqlServer.Server;

namespace CLRUDT
{
[Serializable]

[SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,MaxByteSize=255)]

public struct FullName : INullable, IBinarySerialize
{
public string FirstName { get; set; }
public string LastName { get; set; }
public bool IsNull { get; set; }
public static FullName Null
{
get { return new FullName { FirstName = "", LastName = "",
IsNull = true }; }
}
public override string ToString()
{
return IsNull ? "NULL" : string.Format("{0} {1}",
FirstName, LastName);
}
[SqlMethod(OnNullCall = false)]
public static FullName Parse(SqlString s)
{
string[] parts = s.ToString().Split(' ');
return new FullName { FirstName = parts[0], LastName =
parts[1], IsNull = false };
}
public void Read(BinaryReader r)
{
FirstName = r.ReadString();
LastName = r.ReadString();
IsNull = r.ReadBoolean();
}
public void Write(BinaryWriter w)
{
w.Write(FirstName);
w.Write(LastName);
w.Write(IsNull);
}
}
}

and:

using System;
using System.Data.SqlClient;

using CLRUDT;

namespace CLRUDTdemo
{
public class Program
{
public static void Execute(SqlConnection con, string sql)
{
using(SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
}
public static void Main(string[] args)
{
using(SqlConnection con = new
SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Database=Test;Trusted_Connection=True"))
{
con.Open();
Execute(con, string.Format("CREATE ASSEMBLY CLRUDTdll
FROM '{0}' WITH PERMISSION_SET = SAFE",
typeof(FullName).Assembly.Location));
Execute(con, "CREATE TYPE dbo.FullName EXTERNAL NAME
CLRUDTdll.[CLRUDT.FullName]");
Execute(con, "CREATE TABLE demo (id INTEGER NOT NULL
PRIMARY KEY, name FullName)");
Execute(con, "INSERT INTO demo VALUES(1,'Arne Vajhoej')");
using(SqlCommand sel = new SqlCommand("SELECT name FROM
demo WHERE id=1", con))
{
object o = sel.ExecuteScalar();
Console.WriteLine(o.GetType().FullName);
FullName name = (FullName)o;
Console.WriteLine("{0} {1}", name.FirstName,
name.LastName);
}
Execute(con, "DROP TABLE demo");
Execute(con, "DROP TYPE dbo.FullName");
Execute(con, "DROP ASSEMBLY CLRUDTdll");
}
Console.WriteLine("All done");
Console.ReadKey();
}
}
}

Output:

CLRUDT.FullName
Arne Vajhoej
All done

Arne

PS: No - I don't think this is particular relevant for your original
problem. Most likely you just got a nasty ADO.NET provider. But hey
I have never don a CLR UDR before, so fun!

[Anton Shepelev]

But you can define a UDT in SQLServer using CLR
code.
And I would assume that would have the problem.
Verified.
object o = sel.ExecuteScalar();
Console.WriteLine(o.GetType().FullName);
FullName name = (FullName)o;
Console.WriteLine("{0} {1}", name.FirstName, name.LastName);
...
CLRUDT.FullName
Arne Vajhoej
All done

Thanks for the example. I belive you already know
how I should solve it. I should have
ExecuteScalar() return the string "Vajhoej Arne",
and the method .AsFullName() return an instance of
the custom FullName structure.

[Arne Vajhøj]

Post by Anton Shepelev

But you can define a UDT in SQLServer using CLR
code.
And I would assume that would have the problem.
Verified.
object o = sel.ExecuteScalar();
Console.WriteLine(o.GetType().FullName);
FullName name = (FullName)o;
Console.WriteLine("{0} {1}", name.FirstName, name.LastName);
...
CLRUDT.FullName
Arne Vajhoej
All done

Thanks for the example. I belive you already know
how I should solve it. I should have
ExecuteScalar() return the string "Vajhoej Arne",
and the method .AsFullName() return an instance of
the custom FullName structure.

Unless the ADO.NET provider generates code on the fly, then
it can't have any .AsXxxx() methods, because UDT'd are not
database brand specific but database site specific.

Obviously returning String from ExecuteScalar is possible.

Arne

[Anton Shepelev]

Thanks for the example. I belive you already know
how I should solve it. I should have
ExecuteScalar() return the string "Vajhoej Arne",
and the method .AsFullName() return an instance of
the custom FullName structure.

Unless the ADO.NET provider generates code on the
fly, then it can't have any .AsXxxx() methods, be-
cause UDT'd are not database brand specific but
database site specific.

Of course. I thought your example was not so much
about UTDs as about the problems one might encounter
in the mapping of DB-specific types to the native
types of programming language.

[Arne Vajhøj]

Post by Anton Shepelev

Post by Arne Vajhøj
Second MS seems to hint it being OK in its documen-
tation.
https://msdn.microsoft.com/en-us/library/aa720160.aspx
on how to write a data reader clear states that it
can return custom data types.

Data types from your data source will be stored in
your .NET-based application as .NET Framework
types.
[...]
If your .NET Framework data provider has propri-
etary types that cannot adequately be exposed as
.NET Framework types, you may extend the inter-
faces to support proprietary types, then add typed
accessors for your DataReader that return propri-
etary types as well.
It seems to imply that these proprietary types shall
be exposed only in strongly typed accessors,

The text says "may" not "shall".

Post by Anton Shepelev
whereas
the weakly-typed one shall still return exclusively
.NET types.

I do not read that from that text.

Post by Anton Shepelev

Post by Arne Vajhøj
https://msdn.microsoft.com/en-us/library/aa720687.aspx
is a C# template for writing a command and it shows
ExecuteScalar returning same data types as data
reader.

It uses the weakly typed accessor, which I think
must always return a native .NET type...

It returns the internal data type as is with
no conversion.

Post by Anton Shepelev
The "SQL Server Data Type Mappings"
https://msdn.microsoft.com/en-us/library/cc716729.aspx

Note SQL server specific.

Post by Anton Shepelev
lists a native .NET type for each proprietary type.
This .NET type is returned from ExecuteScalar(),

I don't see that in the text.

Post by Anton Shepelev
whereas the proprietary type -- form the correspond-
ing stronly typed accessor.

Arne

[Anton Shepelev]

Post by Arne Vajhøj
Second MS seems to hint it being OK in its documen-
tation.
https://msdn.microsoft.com/en-us/library/aa720160.aspx
on how to write a data reader clear states that it
can return custom data types.
https://msdn.microsoft.com/en-us/library/aa720687.aspx
is a C# template for writing a command and it shows
ExecuteScalar returning same data types as data
reader.

On the other hand, their document titled "Data Type
Mappings in ADO.NET"

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/data-type-mappings-in-ado-net

seems to require that the standard methods return
.NET Framework types:

This means that when a DataAdapter fills a
DataTable in a DataSet with values from a data
source, the resulting data types of the columns
in the DataTable are .NET Framework types, in-
stead of types specific to the .NET Framework da-
ta provider that is used to connect to the data
source.

Likewise, when a DataReader returns a value from
a data source, the resulting value is stored in a
local variable that has a .NET Framework type.
For both the Fill operations of the DataAdapter
and the Get methods of the DataReader, the .NET
Framework type is inferred from the value re-
turned from the .NET Framework data provider.

Would that be a contradicion?

[Arne Vajhøj]

Post by Anton Shepelev

Post by Arne Vajhøj
Second MS seems to hint it being OK in its documen-
tation.
https://msdn.microsoft.com/en-us/library/aa720160.aspx
on how to write a data reader clear states that it
can return custom data types.
https://msdn.microsoft.com/en-us/library/aa720687.aspx
is a C# template for writing a command and it shows
ExecuteScalar returning same data types as data
reader.

On the other hand, their document titled "Data Type
Mappings in ADO.NET"
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/data-type-mappings-in-ado-net
seems to require that the standard methods return
This means that when a DataAdapter fills a
DataTable in a DataSet with values from a data
source, the resulting data types of the columns
in the DataTable are .NET Framework types, in-
stead of types specific to the .NET Framework da-
ta provider that is used to connect to the data
source.
Likewise, when a DataReader returns a value from
a data source, the resulting value is stored in a
local variable that has a .NET Framework type.
For both the Fill operations of the DataAdapter
and the Get methods of the DataReader, the .NET
Framework type is inferred from the value re-
turned from the .NET Framework data provider.
Would that be a contradicion?

Sounds like it.

Arne

[Arne Vajhøj]

Post by Anton Shepelev
I think it does, because such a behavior prevents
the writing of provider-independent code, which is a
fundamental feature of ADO.NET with its inversion of
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

ADO.NET are not intended to guarantee database independent
code but to make it possible to write such.

If you use standard SQL and you use ADO.NET the correct way
then you can be database independent.

But you can write you ADO.NET code so that it is database
dependent.

Or you can use database features that are database
dependent. If that is the case there is no way ADO.NET
can make it database independent.

Arne

[Anton Shepelev]

ADO.NET are not intended to guarantee database in-
dependent code but to make it possible to write
such.
If you use standard SQL and you use ADO.NET the
correct way then you can be database independent.

That is my intention.

But you can write you ADO.NET code so that it is
database dependent.

Absolutely, and it is much easier than keeping it
generic.

Or you can use database features that are database
dependent. If that is the case there is no way
ADO.NET can make it database independent.

We have two approaches to this case:

1. An assembly that exposes generic methods and
returnes database-specific SQL code. We use
it, for example, to execute stored procedures
in a generic manner.

2. Use a specialized privider directly, so as to
have all the extended features of the given
RDBMS.

[Arne Vajhøj]

Post by Anton Shepelev

I have a situation where DbCommand.
ExecuteScalar() returns a box object with a val-
ue of a type that is not a native .NET type,
i.e. it is OurOwnDecimal rather than decimal.

I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.
I think it does, because such a behavior prevents
the writing of provider-independent code, which is a
fundamental feature of ADO.NET with its inversion of
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

If the data type in the database can be converted to
a System.Decimal just fine, then it is certainly
a nasty ADO.NET provider.

Nasty does not equal violating specification, but it certainly
warrants for looking for another vendor.

But if there is some reason why the custom data type
better matches the type in the database, then it is
behavior as expected.

Sure it will make your C# code non-portable, but
it is really the choice of data type in the database
that are non-portable and the problem is cascading up
from the database to the application code.

Arne

[Anton Shepelev]

I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.
I think it does, because such a behavior prevents
the writing of provider-independent code, which is
a fundamental feature of ADO.NET with its inver-
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

If the data type in the database can be converted
to a System.Decimal just fine, then it is certainly
a nasty ADO.NET provider.
Nasty does not equal violating specification, but
it certainly warrants for looking for another ven-
dor.
But if there is some reason why the custom data
type better matches the type in the database, then
it is behavior as expected.

Well, according to what I said in the previous
posts, the untyped accessor (and consequently
ExecuteScalar()) should return the native .NET
decimal even if it is subobptimal, and the strongly
typed accessor should return the custom database-
specific type.

[Arne Vajhøj]

Post by Anton Shepelev

I have no doubt that the abovementioned data
provider returns a custom data type, and wish to
know whether it does not violate the ADO.NET stan-
dard in doing so.
I think it does, because such a behavior prevents
the writing of provider-independent code, which is
a fundamental feature of ADO.NET with its inver-
https://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic
https://msdn.microsoft.com/en-us/library/ff649512.aspx
I need an official document that proves it.

If the data type in the database can be converted
to a System.Decimal just fine, then it is certainly
a nasty ADO.NET provider.
Nasty does not equal violating specification, but
it certainly warrants for looking for another ven-
dor.
But if there is some reason why the custom data
type better matches the type in the database, then
it is behavior as expected.

Well, according to what I said in the previous
posts, the untyped accessor (and consequently
ExecuteScalar()) should return the native .NET
decimal even if it is subobptimal, and the strongly
typed accessor should return the custom database-
specific type.

I don't see that in any of the original texts.

But I am sure that you do like MS OracleClient
(which is different than Oracle ODP) approach:

<quote>
OracleCommand.ExecuteScalar Method ()

Executes the query, and returns the first column of the first row in the
result set returned by the query as a .NET Framework data type. Extra
columns or rows are ignored.
</quote>

<quote>
OracleCommand.ExecuteOracleScalar Method ()

Executes the query, and returns the first column of the first row in the
result set returned by the query as an Oracle-specific data type. Extra
columns or rows are ignored.
</quote>

so someone at MS was definitely thinking like you.

Arne

[Anton Shepelev]

But I am sure that you do like MS OracleClient
OracleCommand.ExecuteScalar Method ()
Executes the query, and returns the first col-
umn of the first row in the result set
returned by the query as a .NET Framework data
type. Extra columns or rows are ignored.
OracleCommand.ExecuteOracleScalar Method ()
Executes the query, and returns the first col-
umn of the first row in the result set
returned by the query as an Oracle-specific
data type. Extra columns or rows are ignored.
so someone at MS was definitely thinking like you.

Not quite. I am of opinion that database-specific
descendents of DbCommand should return database-spe-
cific types whenever required, whereas the generic,
database-independent class DbCommand (and the other
classes in System.Data.Common) must return only na-
tive .NET types.

That said, I find the Oracle implementation of
OracleCommand better, but I wonder what will Ora-
cle's provider return when accessed via DbCommand
rather than via OracleCommand?

[Arne Vajhøj]

Post by Anton Shepelev

But I am sure that you do like MS OracleClient
OracleCommand.ExecuteScalar Method ()
Executes the query, and returns the first col-
umn of the first row in the result set
returned by the query as a .NET Framework data
type. Extra columns or rows are ignored.
OracleCommand.ExecuteOracleScalar Method ()
Executes the query, and returns the first col-
umn of the first row in the result set
returned by the query as an Oracle-specific
data type. Extra columns or rows are ignored.
so someone at MS was definitely thinking like you.

Not quite. I am of opinion that database-specific
descendents of DbCommand should return database-spe-
cific types whenever required, whereas the generic,
database-independent class DbCommand (and the other
classes in System.Data.Common) must return only na-
tive .NET types.
That said, I find the Oracle implementation of
OracleCommand better, but I wonder what will Ora-
cle's provider return when accessed via DbCommand
rather than via OracleCommand?

DbCommand.ExecuteScalar is abstract.

So it will always call the specific implementation
ExecuteScalar.

And therefore there is only one possible return type
no matter if it is IDbCommand or DbCommand or XxxxCommand.

Arne

[Anton Shepelev]

Post by Arne Vajhøj

I am of opinion that database-specific descendents
of DbCommand should return database-specific types
whenever required, whereas the generic, database-in-
dependent class DbCommand (and the other classes in
System.Data.Common) must return only native .NET
types.

DbCommand.ExecuteScalar is abstract.
So it will always call the specific implementation Ex-
ecuteScalar.
And therefore there is only one possible return type
no matter if it is IDbCommand or DbCommand or
XxxxCommand.

I missed that. Thank you.

[Anton Shepelev]

My current solution (or work-around) is to analyse
the name of the type of the returned value and to
convert it to System.Decimal by means of the static
Convert class. That custom datatype implements
IConvertible, which is somehow available even though
the assemby where the type is implemented is not
referenced.

static void FilterAdoType( ref object value )
{ if( value.GetType().Name == "HanaDecimal" )
{ value = Convert.ToDecimal( value ); }
}

I fear this is not efficient.

[Arne Vajhøj]

Post by Anton Shepelev
My current solution (or work-around) is to analyse
the name of the type of the returned value and to
convert it to System.Decimal by means of the static
Convert class. That custom datatype implements
IConvertible, which is somehow available even though
the assemby where the type is implemented is not
referenced.
static void FilterAdoType( ref object value )
{ if( value.GetType().Name == "HanaDecimal" )
{ value = Convert.ToDecimal( value ); }
}
I fear this is not efficient.

Compared to what it takes to get the data from the database
over in the application then I doubt that the
conversion will add much overhead.

Arne

[Anton Shepelev]

I suspect that a third-party ADO.NET data provider
I am using at work is broken, but I cannot prove it
to their support team.
I have a situation where DbCommand.ExecuteScalar()
returns a box object with a value of a type that is
not a native .NET type, i.e. it is OurOwnDecimal
rather than decimal. I cannot handle this value
without an explicit reference to a third-party as-
sembly that implements it. Whereas I am working on
a generic ADO.NET wrapper, I do not want it to de-
pend on anything outside System.Data and System.Da-
ta.Common.
Does the ADO.NET standard stipulate that DbCommand.
ExecuteScalar() shall return objects that may be
cast or unboxed to native .NET types?

They have finally accepted it as a bug but offered a
solution that is worse than the bug: to return a
.NET decimal if the value may be converted to it
without loss of precision, and to return their cus-
tom decimal type otherwise. To put it short, users
will not receive different types for different rows
in the same table, and therefore check the returned
type in runtime. I think they are crazy.

What arguments may you suggest to convince them that
it is an incompetent decision?

[Anton Shepelev]

To put it short, users will not receive different
types for different rows in the same table, and
therefore check the returned type in runtime. I
think they are crazy.

will *now* receive.

[Marcel Mueller]

Post by Anton Shepelev

To put it short, users will not receive different
types for different rows in the same table, and
therefore check the returned type in runtime. I
think they are crazy.

will *now* receive.

This is /your/ statement.

It may also depend on the /static/ type of the Column, e.g. the number
of digits.

Using a type that cannot provide a turn around safe representation of
the column is undesirable too.

In fact IDataReader provides the function GetDecimal to force a
(possible) conversion by the user.


Marcel

[Anton Shepelev]

Post by Marcel Mueller

Post by Anton Shepelev

To put it short, users will not receive dif-
ferent types for different rows in the same
table, and therefore check the returned type
in runtime. I think they are crazy.

will *now* receive.

This is /your/ statement.

Yes.

Do you realise that with the fix I shall have to
write this code:

decimal result;
object whatIsIt = command.ExecuteScalar();
if ( whatIsIt.GetType().ToString() = "Their.Custom.Decimal" )
{ /* signal-out-of range value */ }
else
{ result := ( decimal )whatIsIt; }

Post by Marcel Mueller
It may also depend on the /static/ type of the
Column, e.g. the number of digits.

I wish it did. I proposed to them to return .NET
decimal if both scale and precision are less than 29
and return their specific decimal otherwise. It
would be a tolerable solution because the type of
the return value would be the same for each query.

Post by Marcel Mueller
Using a type that cannot provide a turn around
safe representation of the column is undesirable
too.

I agree. But this is hardly possible with universal
DbCommand.ExecuteScalar(). But I have propsed to
them, after Arne's advice, always to return .NET
decimal from ExecuteScalar() and to implement a
GetCustomDecimal() in their CustomDataReader. This
is the way taken by Microsoft and it is very good:

a. Generic DB-agnostic code need not depend on
custom third-party libraries to process the
output of ExecuteScalar(), because it always
returns native types.

b. Whoever wants the full range and precision of
the custom decimal type may use the classes
form the DB-specific library instead of those
from System.Data.Common.

c. In all cases the return types are statically
determined, so that magical run-time checks
are never required.

Post by Marcel Mueller
In fact IDataReader provides the function
GetDecimal to force a (possible) conversion by the
user.

Thanks for the suggestion.

[Arne Vajhøj]

Post by Anton Shepelev

I suspect that a third-party ADO.NET data provider
I am using at work is broken, but I cannot prove it
to their support team.
I have a situation where DbCommand.ExecuteScalar()
returns a box object with a value of a type that is
not a native .NET type, i.e. it is OurOwnDecimal
rather than decimal. I cannot handle this value
without an explicit reference to a third-party as-
sembly that implements it. Whereas I am working on
a generic ADO.NET wrapper, I do not want it to de-
pend on anything outside System.Data and System.Da-
ta.Common.
Does the ADO.NET standard stipulate that DbCommand.
ExecuteScalar() shall return objects that may be
cast or unboxed to native .NET types?

They have finally accepted it as a bug but offered a
solution that is worse than the bug: to return a
.NET decimal if the value may be converted to it
without loss of precision, and to return their cus-
tom decimal type otherwise. To put it short, users
will now receive different types for different rows
in the same table, and therefore check the returned
type in runtime. I think they are crazy.
What arguments may you suggest to convince them that
it is an incompetent decision?

I do not like that solution either.

My argument would be that the:
* a given value should be returned as the same type
no matter how it is retrieved:
- obj = cmd.ExceuteScalar("SELECT f FROM t WHERE id=?");
- rdr = = cmd.ExceuteReader("SELECT f FROM t WHERE id=?");
rdr.Next(); obj = rdr.GetValue(0);
- rdr = = cmd.ExceuteReader("SELECT f FROM t"); where(rdr.Next()) {
obj = rdr.GetValue(0);}
* the last form has to return all values in a column as the same
type as rdr.GetFieldType(0) must return that type

Those two requirements are not compatible with that solution.

Arne