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?

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Marcel Mueller]

On 31.05.17 17.15, Anton Shepelev wrote:
> 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.

> 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]

Marcel Mueller to 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
is:

> > 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.

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Arne Vajhøj]

On 5/31/2017 5:16 PM, Anton Shepelev wrote:
> 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.

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

[Arne Vajhøj]

On 5/31/2017 5:16 PM, Anton Shepelev wrote:

> 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.

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

[Arne Vajhøj]

On 5/31/2017 5:16 PM, Anton Shepelev wrote:

>>> 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
> 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.

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]

Arne Vajhoj to 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-

>>sion 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.
>
>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.

>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.

> 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.

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Anton Shepelev]

Arne Vajhoj:

>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.

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Anton Shepelev]

Arne Vajhoj to 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-

>>sion 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.
>
>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.

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Anton Shepelev]

Anton Shepelev to Arne Vajhoj:

>>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.
>
>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

> 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]

On 6/2/2017 11:01 AM, Anton Shepelev wrote:
> Arne Vajhoj to Anton Shepelev:

>> 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,

The text says "may" not "shall".

> whereas
> the weakly-typed one shall still return exclusively
> .NET types.

I do not read that from that text.

>> 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.

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

Note SQL server specific.

> lists a native .NET type for each proprietary type.
> This .NET type is returned from ExecuteScalar(),

I don't see that in the text.

> whereas the proprietary type -- form the correspond-
> ing stronly typed accessor.

Arne

[Arne Vajhøj]

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

[Arne Vajhøj]

On 6/2/2017 12:37 PM, Anton Shepelev wrote:
> 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.

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]

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

[Arne Vajhøj]

On 6/2/2017 9:24 PM, Arne Vajhøj wrote:

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]

Arne Vajhoj to Anton Shepelev:

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

> 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?

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Anton Shepelev]

Arne Vajhoj to Anton Shepelev:

> > 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.
>
> 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.

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Anton Shepelev]

Arne Vajhoj:

> 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
> 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.

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.

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Arne Vajhøj]

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

[Arne Vajhøj]

On 6/4/2017 1:01 PM, Anton Shepelev wrote:
> Arne Vajhoj to 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]

On 6/4/2017 1:08 PM, Anton Shepelev wrote:
> Arne Vajhoj:

>> 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.
>
> 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]

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]

Arne Vajhoj to Anton Shepelev:

> > 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]

Arne Vajhoej:

> 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);

> ...

> Output:
>
> 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]

On 6/9/2017 1:34 PM, Anton Shepelev wrote:
> Arne Vajhoej:
>> 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);
>> ...
>> Output:
>>
>> 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]

Arne Vajhoj to 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.

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[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]

On 6/26/2017 7:54 AM, Anton Shepelev wrote:
> 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 wrote:

>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]

I wrote:

>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]

On 19.10.17 18.39, Anton Shepelev wrote:
>> 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]

Marcel Mueller to 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; }

> 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.

> 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.

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

Thanks for the suggestion.

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Arne Vajhøj]

On 10/19/2017 12:36 PM, Anton Shepelev wrote:
> I wrote:
>
>> 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

[Anton Shepelev]

Arne Vajhoj:

>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?

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Arne Vajhøj]

Sounds like it.

Arne