#3 typed dataset generator replacement

[Oskar Austegard]

It would be great if someone could create a typed
dataset generator replacement that used NullableTypes
rather than the cumbersome IsMyColNull() and
SetMyColNull() methods. A good starting point might be
the ADO Guy's ADO Power Tools
(http://www.gotdotnet.com/community/workspaces/wor
kspace.aspx?id=40d3e800-e2af-4220-a079-
66552dd2b825)

Thanks,
Oskar

[(luKa) aka Luca Minudel]

Logged In: YES
user_id=742947

First, please do read this post (and tell me if it's clear):
http://discuss.develop.com/archives/wa.exe?A2=ind0310B&L=ADVANCED-DOTNET&P=R1562&I=-3

I'll try to suggest this features to the "ADO Guy's ADO
Power Tools" ;-)

bye (luKa)

[Oskar Austegard]

Logged In: YES
user_id=911372

luKa - you have a point. On the other hand, if one is to
leverage the typed dataset generator, it would be best if this
generator could remain database agnostic. By using the
SqlTypes you'd tie the user to SQL Server, and the same with
the Oracle types (I guess - I have never used them).

Also, since TDS are not necessarily restricted to the
datalayer, (in my case I intend to build my business objects
around typed datasets), it would be preferable to use
something basic, like NullableTypes rather than the more
complex SqlTypes, et. al.

Or am I wrong?

What do you see as a limitation

[Oskar Austegard]

Logged In: YES
user_id=911372

sorry - ignore the last line in the previous post ("what do you
see as a limitation" - it was meant to have been deleted

[(luKa) aka Luca Minudel]

Logged In: YES
user_id=742947

No, you are not wrong. I just wanna be sure to be able to
make one thing clear (if my uncertain english let me do so).

NullableTypes when used in the persistence layer or in the
business layer do _completely_ resolve the problem (not just
shift it elsewhere) of handling null values for built-in
value-types. So NullableTypes there are a valid general
purpose solutions.

NullableTypes when used in the data layer have pros and cons
because the db type system is not always isomorphic to the
CLR built-in types (the CTS).
Here is an example.

-----------------------------------------------------

Create a table like this:

CREATE TABLE [dbo].[Costs] (
[ID] [bigint] NOT NULL ,
[TotalCosts] [decimal](38, 30) NULL
) ON [PRIMARY]
GO

Then insert a row like this:

INSERT INTO Costs (ID, TotalCosts)
VALUES
(13, 12345678.901234567890123456789012345678)

Note that the value
'12345678.901234567890123456789012345678' (Scale 30,
Precision 38) cannot be contained in a System.Decimal
because a System.Decimal have a scaling factor that range
from 0 to 28.

Anyway the value '12345678.901234567890123456789012345678'
can be contained in a SqlDecimal.
Try the following code:

// The connection
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection();
sqlConnection1.ConnectionString = ...

// The Select command
System.Data.SqlClient.SqlCommand sqlSelectCommand1 =
new System.Data.SqlClient.SqlCommand("SELECT
ID, TotalCosts FROM TmpCosts",

sqlConnection1);

// Get DataRows
sqlConnection1.Open();
System.Data.SqlClient.SqlDataReader sqlDr =
sqlSelectCommand1.ExecuteReader();
while (sqlDr.Read()) {
System.Console.WriteLine(

((System.Data.SqlTypes.SqlDecimal)sqlDr.GetSqlValue(1)).ToString()
);
}

You will get the value
'12345678.901234567890123456789012345678' printed in the
output window.

But if you try this:

while (sqlDr.Read()) {
System.Console.WriteLine(
sqlDr.GetValue(1).ToString()
// it is like this if the value is not
DBNull.Value: sqlDr.GetDecimal(1).ToString()
);
}

you get a System.OverflowException because the value
'12345678.901234567890123456789012345678' .
It is the same if you try to use a NullableDecimal (because
it behave like a System.Decimal):

NullableTypes.NullableDecimal nd;
NullableTypes.DBNullConvert.ToNullableDecimal()

while (sqlDr.Read()) {
nd =
NullableTypes.DBNullConvert.ToNullableDecimal(sqlDr.GetValue(1));
System.Console.WriteLine(nd.ToString());
}

A typed DataSet built on the Costs table will have a CostRow
type (with base the class DataRow) and the TotalCost
column will look like this:

public System.Decimal TotalCosts {
get {
try {
return
((System.Decimal)(this[this.tableCosts.TotalCostsColumn]));
}
catch (InvalidCastException e) {
throw new
StrongTypingException("Cannot get value because it is
DBNull.", e);
}
}
set {
this[this.tableCosts.TotalCostsColumn] =
value;
}
}

So it do suffer the same problem of System.OverflowException.

In conclusion, when you have values in the DB that cannot be
represented with CLR built-in types (the CTS) you must use
SqlTypes (the Oracle types or the .NET Data Provider types
for your data base) or you are out of luck!
Neither a DataSet or a type DataSet came in help here.

-----------------------------------------------------

Is it clear now?

[Oskar Austegard]

Logged In: YES
user_id=911372

Absolutely - that was a very thorough explanation. In my
case I guess I'm lucky enough to not have to worry about
such extremes.

So I guess my original request is still valid - where a regular
TDS can be used, a nullable TDS would be even better.

Thanks,
Oskar

[Nobody/Anonymous]

Logged In: NO

I keep getting an error when i use strongly typed dataset.The column takes decimal values,i want to insert a null,any idea how i can do this,it throw error saying DBnull not allowed