有没有办法将 SqlTypes 与 SqlDataAdapter 一起使用？
我正在尝试编写一些简单的 C# 代码，该代码将从 SQL Server 实例中选择一些数据、修改数据并将更改更新回数据库。但我很难弄清楚到底如何做到这一点。这是我的代码的简化版本：
using (SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=true;Database=master"))
{
SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Field FROM test", conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "test");
foreach (DataRow dr in ds.Tables["test"].Rows)
{
dr["Field"] = someFunction((SqlString)dr["Field"]);
}
da.Update(ds, "test");
}
但是这段代码给了我以下错误：
System.InvalidCastException: specified cast is not valid.
因此，我将代码更改为使用 string 而不是 SqlString：
dr["Field"] = someFunction((string)dr["Field"]);
但是如果我的数据中有任何空值，我会收到此错误：
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
我认为 SqlTypes 命名空间的全部目的是提供可以处理数据库空值的类型。如果是这种情况，为什么我不能将它们与 SqlDataAdapter 一起使用？希望我遗漏了一些明显的东西。
I'm trying to write some simple C# code that will select some data from a SQL Server instance, modify the data, and update the changes back to the database. But I'm having a hard time figuring out exactly how to do this. Here is a simplified version of my code:
using (SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=true;Database=master"))
{
SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Field FROM test", conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "test");
foreach (DataRow dr in ds.Tables["test"].Rows)
{
dr["Field"] = someFunction((SqlString)dr["Field"]);
}
da.Update(ds, "test");
}
But this code is giving me the following error:
System.InvalidCastException: specified cast is not valid.
So I change the code to use string rather than SqlString:
dr["Field"] = someFunction((string)dr["Field"]);
But then I get this error if there are any nulls in my data:
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
I thought the whole point of the SqlTypes namespace was to provide types that can handle database nulls. If that is the case, why can't I use them with a SqlDataAdapter? Hopefully I'm missing something obvious.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您正在寻找 ReturnProviderSpecificTypes 属性：
You are looking for the ReturnProviderSpecificTypes property:
尝试
Try
来自 womp 的评论：
如果数据表的 Field 列输入为 SqlString，那么此时强制转换 (SqlString)dr["Field"] 不会成功吗？ – womp 9 月 10 日 17:11
这是正确答案。
From womp's comment:
If the datatable had the Field column typed to SqlString, wouldn't the cast (SqlString)dr["Field"] succeed at that point? – womp Sep 10 at 17:11
This was the correct answer.
我在这里可能是非常错误的（只是免责声明），但我认为您需要从 db 值创建一个新的 SqlString，如下所示：
我认为这适用于您的 null 情况，但我还没有测试过。
I could be very wrong here (just a disclaimer), but I think you need to make a new SqlString from your db value, like so:
I think that will work for your null case, I haven't tested it though.