C# Datareader在VS中没有返回结果但存储过程在Sql Server中返回多个结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/370165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Datareader returns no results in VS yet Stored Procedure returns multiple result sets in Sql Server
提问by
I am having trouble retrieving results from my datareader in visual studio 2008. I have several stored Procs in the same database. I am able to retrieve values from those that dont receive input parameters. However, when i use the executreReader() method on a stored proc with input parameters i get an empty datareader. Upon examining the result collection the message "IEnumerable returned no results" appears. I am baffled as I can execute the stored procs within sql server and return result sets. I was previously able to retrieve rows from these stored procedures within Visual Studio but apparently it just stopped working one day.
我在 Visual Studio 2008 中从我的 datareader 检索结果时遇到问题。我在同一个数据库中有几个存储的 Procs。我能够从那些不接收输入参数的值中检索值。但是，当我在带有输入参数的存储过程上使用 executreReader() 方法时，我得到一个空的数据读取器。检查结果集合时，会出现消息“IEnumerable 未返回结果”。我很困惑，因为我可以在 sql server 中执行存储的过程并返回结果集。我以前能够在 Visual Studio 中从这些存储过程中检索行，但显然有一天它停止工作。
I have tried using a dataadapter to fill a dataset with my results and using the executereader() method to get a sqldatareader and Still I get no results. No exceptions are thrown either. My parameters are all named properly but I should be able to call these stored procs with no parameters and have that return an unfiltered result set. The code im currently using is the following:
我曾尝试使用 dataadapter 用我的结果填充数据集，并使用 executereader() 方法获取 sqldatareader，但仍然没有结果。也不会抛出异常。我的参数都被正确命名，但我应该能够调用这些没有参数的存储过程，并返回一个未过滤的结果集。我目前使用的代码如下：
string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"]
.ConnectionString;
SqlConnection connCactus = new SqlConnection(connStr);
SqlCommand cmdPopulateFilterDropDowns = new SqlCommand( "dbo.MyStoredProc",
connCactus);
SqlDataReader rdrFilterSearch = null;
cmdPopulateFilterDropDowns.CommandType = CommandType.StoredProcedure;
connCactus.Open();
rdrFilterSearch = cmdPopulateFilterDropDowns
.ExecuteReader(CommandBehavior.CloseConnection);
return (rdrFilterSearch);
Please Help!
请帮忙！
回答by Kevin Tighe
When do you get the "IEnumerable returned no results" error? Could you show an example of how you're accessing the DataReader?
你什么时候收到“IEnumerable 没有返回结果”错误？您能举例说明您如何访问 DataReader 吗？
What happens if you set a breakpoint before the return, and run
如果在返回之前设置断点并运行会发生什么
rdrFilterSearch.GetString(0);
in the immediate window?
在直接窗口中？
回答by BFree
Are you ever adding the parameters to the SqlCommand's Parameter collection? You mentioned that the ones that aren't working are the ones that take input params, yet in your code you don't have anything like this:
您是否曾经将参数添加到 SqlCommand 的 Parameter 集合中？你提到那些不工作的是那些接受输入参数的人，但在你的代码中你没有这样的东西：
cmdPopulateFilterDropDowns.Parameters.AddWithValue(...);
回答by Dave Markle
Run a SQL server trace against the server you think you're executing your command against. What's actually being sent to the server? I think you'll find your smoking-gun clue there.
针对您认为正在执行命令的服务器运行 SQL 服务器跟踪。什么实际上被发送到服务器？我想你会在那里找到确凿的线索。
BFree has a good point, to check AddWtihValue() vs. Add(). Also make sure that if you're explicitly instantiating SqlParameter objects, the same problem you'll have with .Add() can occur. Note that there's a design flaw withe Parameters.Add() when you pass values of (int)0 into it -- they get taken as an enum instead of a value...
BFree 有一个很好的观点，即检查 AddWtihValue() 与 Add()。还要确保如果您显式实例化 SqlParameter 对象，可能会发生与 .Add() 相同的问题。请注意，当您将 (int)0 的值传递给 Parameters.Add() 时，它存在设计缺陷——它们被视为枚举而不是值......
回答by keithwarren7
this may seem obvious but please tell me you are calling the .Read() method? I hate to pose such a question but sometimes it is the most obvious things we forget when we are driving ourselves mad over an issue.
这似乎很明显，但请告诉我您正在调用 .Read() 方法？我讨厌提出这样的问题，但有时这是我们在为某个问题发疯时最容易忘记的事情。
回答by keithwarren7
Stupid mistake on my part. After a few hours truggling with this i realized that the stored procedures I was using return multiple result sets of which the first result set was always empty. Hence the lack of results.
我犯了一个愚蠢的错误。经过几个小时的努力，我意识到我使用的存储过程返回多个结果集，其中第一个结果集总是空的。因此缺乏结果。
回答by Henriette
Setting the parameterdirection on the sqlParam should solve the problem.
在 sqlParam 上设置参数方向应该可以解决问题。
mySqlParam.Direction = ParameterDirection.ReturnValue;
If you expect more than one value to be returned, just add the param and set it's direction:
如果您希望返回多个值，只需添加参数并设置其方向：
SqlParameter mySqlParam = new SqlParameter();
mySqlParam.ParameterName = "@ID";
mySqlParam.SqlDbType = SqlDbType.int;
mySqlParam.Direction = ParameterDirection.ReturnValue;
SqlParameter mySqlParam = new SqlParameter();
mySqlParam.ParameterName = "@Name";
mySqlParam.SqlDbType = SqlDbType.NVarChar;
mySqlParam.Direction = ParameterDirection.ReturnValue;
SqlParameter mySqlParam = new SqlParameter();
mySqlParam.ParameterName = "@Address";
mySqlParam.SqlDbType = SqlDbType.NVarChar;
mySqlParam.Direction = ParameterDirection.ReturnValue;
The mySqlParam.ParameterNamedoesn't have to be exactly like the name in the stored procedure.
在mySqlParam.ParameterName不必完全像在存储过程的名称。
You can then read the values like this (not complete example):
然后您可以读取这样的值（不完整的示例）：
int.Parse(dataReader["ID"]);
dataReader["name"].ToString();
dataReader["address"].ToString();
the value in dataReader[""].ToString();has to match the coloumname from the stored procedure.
in 的值dataReader[""].ToString();必须与存储过程中的 coloumname 匹配。
回答by DOK
I had a similar, but not identical, problem that was driving me nuts, so I'll throw this in in case it helps someone along the line.
我有一个类似但不完全相同的问题让我发疯，所以我会把它扔掉，以防它对沿线的人有所帮助。
I could run the SQL, or call a stored procedure with the same SQL, on SQL Server using Management Studio, and get back a populated resultset. But when I used the identical SQL inline, or called the sproc containing it, from my Visual Studio code for an ASP.Net web application project, I got an empty DataReader.
我可以在 SQL Server 上使用 Management Studio 运行 SQL，或使用相同的 SQL 调用存储过程，并获取填充的结果集。但是，当我使用相同的内联 SQL 或调用包含它的 sproc 时，从我的 Visual Studio 代码中用于 ASP.Net Web 应用程序项目，我得到了一个空的 DataReader。
How could the same identical SQL return a resultset in Management Studio, but an empty DataReader in Visual Studio? The answer was that I was running as a different user with different permissions in the two tools.In Management Studio, I was running as a developer in a specific SQL role with a lot of permissions. But in Visual Studio, I was running as a service account which had not been given permissions on the tables I was querying.
相同的 SQL 如何在 Management Studio 中返回结果集，而在 Visual Studio 中返回空的 DataReader？答案是我以不同的用户身份运行，在这两个工具中具有不同的权限。在 Management Studio 中，我作为开发人员以特定 SQL 角色运行，并拥有很多权限。但是在 Visual Studio 中，我作为服务帐户运行，该帐户没有获得对我查询的表的权限。
Actually, my problem was one step more complicated. Everything was fine until I altered the SQL/sproc and added a table JOIN to one more table, and that table lacked the necessary service account permissions. But the principle is the same: examine all of the permissions for the user making the database call.
实际上，我的问题更复杂了一步。一切都很好，直到我更改了 SQL/sproc 并将一个表 JOIN 添加到另一个表，并且该表缺乏必要的服务帐户权限。但原理是一样的：检查进行数据库调用的用户的所有权限。
回答by Biju
Use
用
reader.ExecuteNonQuery()
Happy coding
快乐编码
