C# sql-server-2008 sqldatareader sqldataadapter

SqlDataAdapter.Fill() - 转换溢出

发布于 2024-12-06 17:05:46 字数 401 浏览 0 评论 0原文

所有，

我在小数字段的 SqlDataAdapter.Fill() 用法之一上遇到“转换溢出”异常。该错误发生在 100 亿开始的值，但直到 10 亿为止。这是代码：

DataSet ds = new DataSet();
SqlDataAdapter sd = new SqlDataAdapter();

adapter.SelectCommand = <my SQL Command instance>
adapter.Fill(ds);

我已阅读使用 SqlDataReader 作为替代方案，但我们需要显式设置数据类型和精度。我正在提取至少 70 列，但我不想将所有列仅设置为一个错误的小数字段。

谁能建议替代方法？

谢谢。

原文

All,

I am encountering "Conversion overflow" exceptions on one of the SqlDataAdapter.Fill() usages for a decimal field. The error occurs for value beginning 10 billion, but not till 1 billion. Here is the code:

DataSet ds = new DataSet();
SqlDataAdapter sd = new SqlDataAdapter();

adapter.SelectCommand = <my SQL Command instance>
adapter.Fill(ds);

I have read using SqlDataReader as an alternate but we need to set the datatype and precision explicitly. There are at least 70 columns that I am fetching and I don't want to set all of them only for one decimal field in error.

Can anyone suggest alternate approaches?

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哑 2024-12-13 17:05:46

尽管允许使用数据集“填充”数据适配器，但我通常使用 DataTable 来完成，因为在查询时，我只期望一个结果集。话虽如此，我会预先查询表，只是为了获取其结构...类似这样，

select whatever from yourTable(s) where 1=2

当您执行以下操作时，这将获得预期的结果列

DataTable myTable = new DataTable();
YourAdapter.Fill( myTable );

现在您有一个本地表，该表不会因为没有记录而因内容大小而失败将已返回，您现在可以显式转到有问题的一列，并根据需要设置其数据类型/大小信息...

myTable.Columns["NameOfProblemColumn"].WhateverDataType/Precision = Whatever you need...

现在，您的本地架构是合法的，并且问题列将以其精度进行识别。现在，使用正确的 where 子句输入正确的查询，而不是 1=2 来实际返回数据...由于第一遍中没有实际行，您甚至不需要执行 myTable.Clear() 清除行...只需重新运行查询和 dataAdapter.Fill()。

我实际上没有尝试过，因为我没有你的数据问题来模拟相同的问题，但理论过程应该可以让你通过，而不必明确地遍历所有列......只是少数可能会引起问题的列。

Although dataset is allowed for "filling" a data adapter, I've typically done with a DataTable instead as when querying, I'm only expecting one result set. Having said that, I would pre-query the table, just to get its structure... something like

select whatever from yourTable(s) where 1=2

This will get the expected result columns when you do a

DataTable myTable = new DataTable();
YourAdapter.Fill( myTable );

Now that you have a local table that will not fail for content size because no records will have been returned, you can now explicitly go to that one column in question and set its data type / size information as you need...

myTable.Columns["NameOfProblemColumn"].WhateverDataType/Precision = Whatever you need...

NOW, your local schema is legit and the problem column will have been identified with its precision. Now, put in your proper query with proper where clause and not the 1=2 to actually return data... Since no actual rows in the first pass, you don't even need to do a myTable.Clear() to clear the rows... Just re-run the query and dataAdapter.Fill().

I haven't actually tried as I don't have your data issues to simulate same problem, but the theoretical process should get you by without having to explicitly go through all columns... just the few that may pose the problem.

回复收藏 0 原文

与之呼应 2024-12-13 17:05:46

我遇到了同样的问题，原因是因为在我的存储过程中我返回了一个小数（38,20）字段。我将其更改为十进制（20,10）并且一切正常。这似乎是 Ado.Net 的限制。

CREATE PROCEDURE FOOPROCEDURE AS
BEGIN
    DECLARE @A DECIMAL(38,20) = 999999999999999999.99999999999999999999;
    SELECT @A;
END
GO

string connectionString ="";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("EXEC FOOPROCEDURE", conn);
SqlDataAdapter adt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adt.Fill(ds); //exception thrown here

I had the same problem and the reason is because in my stored procedure I returned a decimal(38,20) field. I changed it into decimal(20,10) and all works fine. It seems to be a limitation of Ado.Net.

CREATE PROCEDURE FOOPROCEDURE AS
BEGIN
    DECLARE @A DECIMAL(38,20) = 999999999999999999.99999999999999999999;
    SELECT @A;
END
GO

string connectionString ="";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("EXEC FOOPROCEDURE", conn);
SqlDataAdapter adt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adt.Fill(ds); //exception thrown here

回复收藏 0 原文

~没有更多了~