Sybase DB的IDataReader 1.1和.Net 4.0中的问题

Question

我有一个sybase DB，它正确地获取查询的结果，如下所示.

select   
    S.ipoInternalID,  
    clientAccount,  
    clientPrice,  
    clientAccountType,  
    interestOnLoan = 
               CASE WHEN useHIBOR = 1 then  
                    ROUND(financingAmount * (fixedRate + spreadRate) * 
                            I.noOfDaysForInterest/365/100,2)
               ELSE 
                    ROUND(financingAmount * (I.fundingRate+ spreadRate) * 
                           I.noOfDaysForInterest/365/100,2) END,
    useHIBORSTR = 
               CASE WHEN useHIBOR = 1 then
                    "LOCK-IN RATE + SPREAD" 
               ELSE
                    "COST OF FUNDING + SPREAD" END,
from subscription S, iPO I, allocation A  
where   
            S.ipoInternalID = @ipoInternalID  and  
            I.ipoInternalID = @ipoInternalID  and  
            A.ipoInternalID = @ipoInternalID  and
            S.ccassID *= A.ccassID  
order by  S.ccassID 

注意上面计算interestOnLoan字段的方式。

现在，当我在SQL工具中运行这个查询时，它运行得很好，并给出了interestOnLoan的计算值。当我使用.Net 1.1API(通过OleDB加载此查询)运行此查询时，它运行良好.

myCommand.CommandText = myQuery;
myAdapter.SelectCommand = myCommand;
int i = myAdapter.Fill(resultSet);

我的结果集满了。

但是，当我在.net 4.0中执行上述代码时，结果集错误如下

对于十进制而言，“值要么太大要么太小。

它的值是interestOnLoan，因为我还通过IDataReader执行命令，如下所示.

  using (var dr = myCommand.ExecuteReader())
  {
         resultSet.Tables.Add(ConvertDataReaderToTableManually(dr));
  }

private static DataTable ConvertDataReaderToTableManually(IDataReader dr) {
    var dt = new DataTable();
    var dtSchema = dr.GetSchemaTable();
    var listCols = new List<DataColumn>();

    if (dtSchema != null) {
        foreach (DataRow drow in dtSchema.Rows) {
        var columnName = Convert.ToString(drow["ColumnName"]);
        var t = (Type) (drow["DataType"]);
        var column = new DataColumn(columnName, t);
        column.Unique = (bool) drow["IsUnique"];
        column.AllowDBNull = (bool) drow["AllowDBNull"];
        column.AutoIncrement = (bool) drow["IsAutoIncrement"];
        listCols.Add(column);
        dt.Columns.Add(column);
        }
    }

    // Read rows from DataReader and populate the DataTable
    int j = 0;

    while (dr.Read()) {
        j++;
        var dataRow = dt.NewRow();
        for (int i = 0; i < listCols.Count; i++) {
        try {
            dataRow[((DataColumn)listCols[i])] = dr[i];
        } catch (Exception ex1) { }         
        }
        dt.Rows.Add(dataRow);
    }

    return dt;
    }

在这里，它在dataRow[((DataColumn)listCols[i])] = dr[i]上出错，在那里它有从dri读取的问题；

当观察到i第四列时，它只不过是i

因此，.Net 4.0无法读取此值。它可以正确读取其他十进制值，如clientPrice。

为什么会发生这种事..。

另外，我想问一下，在默认情况下，是否可以将DataReader中的值加载为Double (而不是Decimal)？

Answer 1

我不知道为什么.NET 4.0在上面的查询中有问题，但是当我按下面的方式更改查询时，它在这两种情况下都有效(.Net 1.1和4.0)

select   
  S.ipoInternalID,  
  clientAccount,  
  clientPrice,  
  clientAccountType,  
  interestOnLoan = ROUND(
     (CASE WHEN useHIBOR = 1 THEN
           ((financingAmount*(fixedRate + spreadRate) * .noOfDaysForInterest)/365.0)
           ELSE 
           ((financingAmount*(I.fundingRate+spreadRate)*I.noOfDaysForInterest)/365.0)
     END) / 100.0, 2),
  useHIBORSTR = 
                   CASE WHEN useHIBOR = 1 then
                   "LOCK-IN RATE + SPREAD" 
                   ELSE
                  "COST OF FUNDING + SPREAD" END,
 from subscription S, iPO I , allocation A  
 where   
                S.ipoInternalID = @ipoInternalID  and  
                I.ipoInternalID = @ipoInternalID  and  
                A.ipoInternalID = @ipoInternalID  and
                S.ccassID *= A.ccassID  
 order by  S.ccassID