data types .NET vs OleDb

create procedure sp_TestTypes 
  @FDecimal_4_0 decimal(4,0),
  @FDecimal_4_2 decimal(4,2),
  @FDecimal_5_0 decimal(5,0),
  @FDecimal_5_2 decimal(5,2),
  @FDecimal_8_0 decimal(8,0),
  @FDecimal_8_2 decimal(8,2),
  @FDecimal_9_0 decimal(9,0),
  @FDecimal_9_2 decimal(9,2),
  @FDecimal_10_0 decimal(10,0),
  @FDecimal_10_2 decimal(10,2),
  @FDecimal_18_0 decimal(18,0),
  @FDecimal_18_2 decimal(18,2),
  @FNumeric_4_0 numeric(4,0),
  @FNumeric_4_2 numeric(4,2),
  @FNumeric_5_0 numeric(5,0),
  @FNumeric_5_2 numeric(5,2),
  @FNumeric_8_0 numeric(8,0),
  @FNumeric_8_2 numeric(8,2),
  @FNumeric_9_0 numeric(9,0),
  @FNumeric_9_2 numeric(9,2),
  @FNumeric_10_0 numeric(10,0),
  @FNumeric_10_2 numeric(10,2),
  @FNumeric_18_0 numeric(18,0),
  @FNumeric_18_2 numeric(18,2),
  @FMoney money,
  @FDecimal_4_0_out decimal(4,0) output,
  @FDecimal_4_2_out decimal(4,2) output,
  @FDecimal_5_0_out decimal(5,0) output,
  @FDecimal_5_2_out decimal(5,2) output,
  @FDecimal_8_0_out decimal(8,0) output,
  @FDecimal_8_2_out decimal(8,2) output,
  @FDecimal_9_0_out decimal(9,0) output,
  @FDecimal_9_2_out decimal(9,2) output,
  @FDecimal_10_0_out decimal(10,0) output,
  @FDecimal_10_2_out decimal(10,2) output,
  @FDecimal_18_0_out decimal(18,0) output,
  @FDecimal_18_2_out decimal(18,2) output,
  @FNumeric_4_0_out numeric(4,0) output,
  @FNumeric_4_2_out numeric(4,2) output,
  @FNumeric_5_0_out numeric(5,0) output,
  @FNumeric_5_2_out numeric(5,2) output,
  @FNumeric_8_0_out numeric(8,0) output,
  @FNumeric_8_2_out numeric(8,2) output,
  @FNumeric_9_0_out numeric(9,0) output,
  @FNumeric_9_2_out numeric(9,2) output,
  @FNumeric_10_0_out numeric(10,0) output,
  @FNumeric_10_2_out numeric(10,2) output,
  @FNumeric_18_0_out numeric(18,0) output,
  @FNumeric_18_2_out numeric(18,2) output,
  @FMoney_out money output
as 
begin
  declare
    @RetVal int

  select @FDecimal_4_0_out = @FDecimal_4_0
  select @FDecimal_4_2_out = @FDecimal_4_2
  select @FDecimal_5_0_out = @FDecimal_5_0
  select @FDecimal_5_2_out = @FDecimal_5_2
  select @FDecimal_8_0_out = @FDecimal_8_0
  select @FDecimal_8_2_out = @FDecimal_8_2
  select @FDecimal_9_0_out = @FDecimal_9_0
  select @FDecimal_9_2_out = @FDecimal_9_2
  select @FDecimal_10_0_out = @FDecimal_10_0
  select @FDecimal_10_2_out = @FDecimal_10_2
  select @FDecimal_18_0_out = @FDecimal_18_0
  select @FDecimal_18_2_out = @FDecimal_18_2
  select @FNumeric_4_0_out = @FNumeric_4_0
  select @FNumeric_4_2_out = @FNumeric_4_2
  select @FNumeric_5_0_out = @FNumeric_5_0
  select @FNumeric_5_2_out = @FNumeric_5_2
  select @FNumeric_8_0_out = @FNumeric_8_0
  select @FNumeric_8_2_out = @FNumeric_8_2
  select @FNumeric_9_0_out = @FNumeric_9_0
  select @FNumeric_9_2_out = @FNumeric_9_2
  select @FNumeric_10_0_out = @FNumeric_10_0
  select @FNumeric_10_2_out = @FNumeric_10_2
  select @FNumeric_18_0_out = @FNumeric_18_0
  select @FNumeric_18_2_out = @FNumeric_18_2
  select @FMoney_out = @FMoney

  select @RetVal=65535 
 
  return(@RetVal)
end

cmd.CommandText="{? = call sp_TestTypes(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
cmd.Parameters.Clear();

SPRetValue=cmd.Parameters.Add("@return",SqlDbType.Int);
SPRetValue.Direction=ParameterDirection.ReturnValue;

cmd.Parameters.Add("@FDecimal_4_0",OleDbType.Numeric).Value=9999;
cmd.Parameters.Add("@FDecimal_4_2",OleDbType.Numeric).Value=9999.99;
cmd.Parameters.Add("@FDecimal_5_0",OleDbType.Numeric).Value=99999;
cmd.Parameters.Add("@FDecimal_5_2",OleDbType.Numeric).Value=99999.99;
cmd.Parameters.Add("@FDecimal_8_0",OleDbType.Numeric).Value=99999999;
cmd.Parameters.Add("@FDecimal_8_2",OleDbType.Numeric).Value=99999999.99;
cmd.Parameters.Add("@FDecimal_9_0",OleDbType.Numeric).Value=999999999;
cmd.Parameters.Add("@FDecimal_9_2",OleDbType.Numeric).Value=999999999.99;
cmd.Parameters.Add("@FDecimal_10_0",OleDbType.Numeric).Value=9999999999;
cmd.Parameters.Add("@FDecimal_10_2",OleDbType.Numeric).Value=9999999999.99;
cmd.Parameters.Add("@FDecimal_18_0",OleDbType.Numeric).Value=999999999999999999;
cmd.Parameters.Add("@FDecimal_18_2",OleDbType.Numeric).Value=999999999999999999.99;
cmd.Parameters.Add("@FNumeric_4_0",OleDbType.Numeric).Value=9999;
cmd.Parameters.Add("@FNumeric_4_2",OleDbType.Numeric).Value=9999.99;
cmd.Parameters.Add("@FNumeric_5_0",OleDbType.Numeric).Value=99999;
cmd.Parameters.Add("@FNumeric_5_2",OleDbType.Numeric).Value=99999.99;
cmd.Parameters.Add("@FNumeric_8_0",OleDbType.Numeric).Value=99999999;
cmd.Parameters.Add("@FNumeric_8_2",OleDbType.Numeric).Value=99999999.99;
cmd.Parameters.Add("@FNumeric_9_0",OleDbType.Numeric).Value=999999999;
cmd.Parameters.Add("@FNumeric_9_2",OleDbType.Numeric).Value=999999999.99;
cmd.Parameters.Add("@FNumeric_10_0",OleDbType.Numeric).Value=9999999999;
cmd.Parameters.Add("@FNumeric_10_2",OleDbType.Numeric).Value=9999999999.99;
cmd.Parameters.Add("@FNumeric_18_0",OleDbType.Numeric).Value=999999999999999999;
cmd.Parameters.Add("@FNumeric_18_2",OleDbType.Numeric).Value=999999999999999999.99;
cmd.Parameters.Add("@FMoney",OleDbType.Currency).Value=922337203685477.5807;
SPResult=cmd.Parameters.Add("@FDecimal_4_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_4_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_5_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_5_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_8_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_8_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_9_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_9_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_10_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_10_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_18_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FDecimal_18_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_4_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_4_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_5_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_5_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_8_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_8_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_9_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_9_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_10_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_10_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_18_0_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FNumeric_18_2_out",OleDbType.Numeric);
SPResult.Direction=ParameterDirection.Output;
SPResult=cmd.Parameters.Add("@FMoney_out",OleDbType.Currency);
SPResult.Direction=ParameterDirection.Output;

cmd.ExecuteNonQuery();

cmd.Parameters.Add("@FMoney",OleDbType.Currency).Value=999.9999;

cmd.Parameters.Add("@FMoney",OleDbType.Currency).Value=922337203685477.5807m; // т.е. decimal ната

cmd.Parameters.Add("@FNumeric_4_2",OleDbType.Numeric).Value=99.99;

SPResult=cmd.Parameters.Add("@FNumeric_4_2",OleDbType.Numeric);
SPResult.Precision=4;
SPResult.Scale=2;
SPResult.Value=99.99;
SPResult=cmd.Parameters.Add("@FNumeric_4_2_out",OleDbType.Numeric);
SPResult.Precision=4;
SPResult.Scale=2;
SPResult.Direction=ParameterDirection.Output;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=C.TABLE_NAME

create procedure sp_TestTypes_Decimal_10_6  
  @FDecimal_10_6 decimal(10,6),  
  @FDecimal_10_6_out decimal(10,6) output  
as   
begin  
  declare  
    @RetVal int  
  
  select @FDecimal_10_6_out = @FDecimal_10_6  
  
  select @RetVal=65535   
   
  return(@RetVal)  
end

create procedure sp_TestTypes_Numeric_10_6  
  @FNumeric_10_6 numeric(10,6),  
  @FNumeric_10_6_out numeric(10,6) output  
as   
begin  
  declare  
    @RetVal int  
  
  select @FNumeric_10_6_out =@FNumeric_10_6  
  
  select @RetVal=65535   
   
  return(@RetVal)  
end

double
  tmpDoubleIn,
  tmpDoubleOut;

decimal
  tmpDecimalIn,
  tmpDecimalOut;

int
  ReturnValue;

tmpDoubleIn=13;
SQLText="sp_TestTypes_Decimal_10_6";
cmd=cn.CreateCommand();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText=SQLText;
OleDbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["FDecimal_10_6"].Value=tmpDoubleIn;
cmd.ExecuteNonQuery();
ReturnValue=Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value);
if(ReturnValue==65535)
{
   tmpDoubleOut=Convert.ToDouble(cmd.Parameters["FDecimal_10_6_out"].Value);
   if(Math.Abs(tmpDoubleIn-tmpDoubleOut)>Double.Epsilon)
   {
      Console.WriteLine("In != Out");
   }
}

SQLText="sp_TestTypes_Numeric_10_6";
cmd.CommandText=SQLText;
OleDbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["FNumeric_10_6"].Value=tmpDoubleIn;
cmd.ExecuteNonQuery();
ReturnValue=Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value);
if(ReturnValue==65535)
{
   tmpDoubleOut=Convert.ToDouble(cmd.Parameters["FNumeric_10_6_out"].Value);
   if(Math.Abs(tmpDoubleIn-tmpDoubleOut)>Double.Epsilon)
   {
      Console.WriteLine("In != Out");
   }
}

tmpDecimalIn=13m;
SQLText="sp_TestTypes_Decimal_10_6";
cmd.CommandText=SQLText;
OleDbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["FDecimal_10_6"].Value=tmpDecimalIn;
cmd.ExecuteNonQuery();
ReturnValue=Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value);
if(ReturnValue==65535)
{
   tmpDecimalOut=Convert.ToDecimal(cmd.Parameters["FDecimal_10_6_out"].Value);
   if(tmpDecimalIn!=tmpDecimalOut)
   {
      Console.WriteLine("In != Out");
   }
}

SQLText="sp_TestTypes_Numeric_10_6";
cmd.CommandText=SQLText;
OleDbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["FNumeric_10_6"].Value=tmpDecimalIn;
cmd.ExecuteNonQuery();
ReturnValue=Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value);
if(ReturnValue==65535)
{
   tmpDecimalOut=Convert.ToDecimal(cmd.Parameters["FNumeric_10_6_out"].Value);
   if(tmpDecimalIn!=tmpDecimalOut)
   {
      Console.WriteLine("In != Out");
   }
}