SqlCommand.ExecuteScalar 返回 null 但原始 SQL 不返回
我有以下代码,它使用 SqlClient.ExecuteScalar 方法从表中返回 ID。
using (var conn = new SqlConnection(connectionString))
using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn))
{
conn.Open();
var contribId = cmdContrib.ExecuteScalar();
}
最初它可以工作,但现在 contribId 为空。 从 Profiler 中提取后,我在 Management Studio 中测试了 SQL,它按预期返回了 ID。
接下来,我添加了一个附加命令来从不同的表(产品)中检索 ID。
ProductId 不为 null,而 contribId 继续为 null。
using (var conn = new SqlConnection(connectionString))
using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn))
using (var cmdTest = new SqlCommand("SELECT productId FROM Product WHERE [filename] = 'bda00001.jpg'", conn))
{
conn.Open();
var contribId = cmdContrib.ExecuteScalar();
var productId = cmdTest.ExecuteScalar();
}
我确信这是显而易见的事情,我会因为没有注意到它而惩罚自己,但现在我被难住了。
I have the following code that uses the SqlClient.ExecuteScalar method to return an ID from a table.
using (var conn = new SqlConnection(connectionString))
using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn))
{
conn.Open();
var contribId = cmdContrib.ExecuteScalar();
}
Originally it was working but now contribId is null. I tested the SQL in management studio after extracting from Profiler and it returned the ID as expected.
Next I added an additional command to retrieve an ID from a different table (Product).
productId is not null while contribId continues to be null.
using (var conn = new SqlConnection(connectionString))
using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn))
using (var cmdTest = new SqlCommand("SELECT productId FROM Product WHERE [filename] = 'bda00001.jpg'", conn))
{
conn.Open();
var contribId = cmdContrib.ExecuteScalar();
var productId = cmdTest.ExecuteScalar();
}
I am sure it is something obvious and I'll kick myself for not noticing it, but for now I'm stumped.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
使用 Profiler 确认:
A) 返回了多少行(我怀疑是 0)
B)它在什么数据库中
C) 它的登录/用户上下文是什么。
D) 实际的整个 SQL 命令是什么。
提取此命令并在同一数据库中重新执行它以确认它确实返回一个值。 如果成功,则将执行上下文更改为探查器表示连接正在运行的执行上下文,然后重试。 如果现在失败(返回 0 行),则检查源表(贡献者)是否实际上是实现行级安全性的视图。
Use Profiler to confirm:
A) how many rows are being returned (I suspect 0)
B) What database it is in
C) what its login/user context is.
D) what the actual entire SQL command is.
Extract this command and re-execute it in the same database to confirm that it does return a value. If this suceeds, then change your execution context to that which the Profiler said that the connection was running under and try again. If it fails now (returns 0 rows) then check to see if the source table (Contributor) may actually be a View that is implementing row-level security.