NULLABLE TYPES FAQ _

Q: What System.Data.SqlTypes (and types provided by other .NET Data Providers)
   can do that NullableTypes can't?

A: Here is an example. Create a table like this:   
   CREATE TABLE [Costs] (
       [ID] [bigint] NOT NULL ,
       [TotalCosts] [decimal](38, 30) NULL
   ) ON [PRIMARY]
   GO

   Then with SqlServer Enterprise Manager 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 System.Decimal have a scaling factor that
   range from 0 to 28. Anyway the value '12345678.901234567890123456789012345678' can be
   contained in a SqlTypes.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 Costs",
           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 when 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());
   }

   In conclusion, when you have values in the database that cannot be represented with CLR
   built-in types (the CTS) you must use System.Data.SqlTypes (and types provided by other
   .NET Data Providers for your database) in the Data Layer or you are out of luck.



Q: Shouldn't I use NullableTypes with DataSet and Strongly Typed DataSet?

A: Currently using System.Data.SqlTypes (and types provided by other .NET Data Providers)
   in the DataSet and Strongly Typed DataSet is not an elegant as it could be.
   For example, generate a strongly typed dataset for this table:
   CREATE TABLE [Costs] (
       [ID] [bigint] NOT NULL ,
       [TotalCosts] [decimal](38, 30) NULL
   ) ON [PRIMARY]
   GO

   The typed DataSet built on the Costs table will have a CostRow type (with the base
   class DataRow) and the property for 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 for values that exceeds
   System.Decimal scaling factor (that range from 0 to 28) like the value
   '12345678.901234567890123456789012345678' does (Scale 30, Precision 38).
   Then you could use NullableTypes with DataSet and Strongly Typed DataSet without get in
   more limitations than System.Data.SqlTypes (and types provided by other .NET Data
   Providers) already have with DataSet and Strongly Typed DataSet.