获取返回值从存储过程在C＃ [英] Getting return value from stored procedure in C#

问题描述

我有以下查询：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output

AS

SET @Password = 
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)

RETURN @b
GO

这编译完全正常。

在C＃中，我想执行这个查询并获得返回值。

In C#, I want to execute this query and get the return value.

我的code是如下：

  SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
        System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);

        string returnValue = string.Empty;

        try
        {
            SqlConn.Open();
            sqlcomm.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = Username;
            sqlcomm.Parameters.Add(param);



            SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
            retval.Direction = ParameterDirection.ReturnValue;


            string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

请注意：异常处理斩保持code短。每次我到最后一行，则返回null。什么是与此code中的逻辑错误？

Note: Exception handling cut to keep the code short. Everytime I get to the last line, null is returned. What's the logic error with this code?

感谢

推荐答案

迈赫达德使一些好点，不过我注意到，最主要的是，你的从不运行查询 ...

Mehrdad makes some good points, but the main thing I noticed is that you never run the query...

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

这篇关于获取返回值从存储过程在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！