如何从TableAdapter检索存储过程返回值

I cannot find an elegant way to get the return value from a stored procedure when using TableAdapters.

使用TableAdapter时，我找不到一种优雅的方法来从存储过程中获取返回值。

It appears the TableAdapter does not support SQL stored procedure return values when using a non-scalar stored procedure call. You'd expect the return value from the auto-generated function would be the return value from the stored procedure but it isn't (it is actually the number of rows affected). Although possible to use 'out' parameters and pass a variable as a ref to the auto generated functions it isn't a very clean solution.

使用非标量存储过程调用时，TableAdapter似乎不支持SQL存储过程返回值。您希望自动生成函数的返回值是存储过程的返回值，但不是（实际上是受影响的行数）。虽然可以使用'out'参数并将变量作为ref传递给自动生成的函数，但它不是一个非常干净的解决方案。

I have seen some ugly hacks on the web to solve this, but no decent solution. Any help would be appreciated.

我在网上看到了一些丑陋的黑客来解决这个问题，但没有像样的解决方案。任何帮助，将不胜感激。

6 个解决方案

#1

The way to get the return value is to use a SqlParameter on the SqlCommand object which has its Direction set to ParameterDirection.ReturnValue. You should check the SelectCommand property of the TableAdapter after calling Fill.

获取返回值的方法是在SqlCommand对象上使用SqlParameter，该对象的Direction设置为ParameterDirection.ReturnValue。调用Fill后，应检查TableAdapter的SelectCommand属性。

#2

NOTE: The way to go is using a SqlParameter where the Direction = ParameterDirection.ReturnValue

注意：要采用的方法是使用SqlParameter，其中Direction = ParameterDirection.ReturnValue

With that said, as someone already mentioned SqlParameters, here is a dynamic method alternate using a DataSet. (if thats how you ride):

话虽如此，正如有人已经提到的SqlParameters，这里是一个使用DataSet的动态方法替代。（如果那就是你骑的方式）：

Example SQL statement and C# as fallows:

示例SQL语句和C＃作为休眠：

string sql = @"DECLARE @ret int 
            EXEC @ret = SP_DoStuff 'parm1', 'parm2'
            SELECT @ret as ret";

DataSet ds = GetDatasetFromSQL(sql); //your sql to dataset code here...

int resultCode = -1;
int.TryParse(ds.Tables[ds.Tables.Count-1].Rows[0][0].ToString(), out resultCode);

The stored procedure results are loaded into a DataSet and will have as many DataTables as return select statements in the stored procedure.

存储过程结果将加载到DataSet中，并且将具有与存储过程中的return select语句一样多的DataTable。

The last DataTable in the DataSet will have 1 row and 1 column that will contain the stored procedure return value.

DataSet中的最后一个DataTable将包含1行和1列，其中包含存储过程返回值。

#3

http://blogs.msdn.com/smartclientdata/archive/2006/08/09/693113.aspx

#4

Actually, all you need to do is to return your final value from the stored procedure using a SELECT statement, not a RETURN statement. The result of the SELECT will be the return value. For example, if you simply make your sp exit statement "SELECT 1" then you'll get back a 1. Now just SELECT the actual scalar you want returned.

实际上，您需要做的就是使用SELECT语句而不是RETURN语句从存储过程返回最终值。 SELECT的结果将是返回值。例如，如果您只是将sp退出语句设置为“SELECT 1”，那么您将返回1.现在只需选择要返回的实际标量。

#5

I cannot say for certain because I have do not use TableAdapters, but you might need to look at your stored procedure and include the following around your procedure.

我不能肯定地说因为我没有使用TableAdapter，但您可能需要查看存储过程并在程序中包含以下内容。

SET ROWCOUNT OFF

BEGIN
<Procedure Content>
END

SET ROWCOUNT ON

#6

Closing this question as it appears return values aren't supported and there is no elegant workaround!

关闭此问题，因为它似乎不支持返回值，并且没有优雅的解决方法！

#1