C# 存储过程输出参数返回@Value

Stored procedure output parameter returns @Value

提问by Dewald Henning

I'm struggling with this thing for the past hour and I'm sure I'm missing something small, I have a stored procedure in SQL Server 2008 and C# code that I want to return the output parameters of my stored procedure.

在过去的一个小时里，我一直在为这件事苦苦挣扎，我确信我遗漏了一些小东西，我在 SQL Server 2008 和 C# 代码中有一个存储过程，我想返回我的存储过程的输出参数。

SQL :

查询语句：

Alter Procedure dbo.GetAssessment
    @UserID int,
    @AssessmentName varchar(255),
    @Score varchar(100) output,
    @Completed varchar(10) output,
    @DisplayName nvarchar(128) output,
    @Result varchar(2500) output
as
begin
        select @Score = A.Score, @Completed = A.Completed, @DisplayName = U.Displayname, @Result = A.Result 
        from Assessment A 
            inner join Users U
            on U.UserId = A.UserID
        where U.UserID = @UserId
        and AssessmentName = @AssessmentName

end
GO

C#

C＃

String SScore, SName, SResult, SComp;
            lblAsse.Text = Request.QueryString["AID"];

            InsertAssessment(lblAsse.Text, "No", 2, "N/A", "N/A");

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString))
            {
                SqlParameter outScore = new SqlParameter("@Score", SqlDbType.VarChar,100){ Direction = ParameterDirection.Output };
                SqlParameter outComp = new SqlParameter("@Completed", SqlDbType.VarChar,10){ Direction = ParameterDirection.Output };
                SqlParameter outName = new SqlParameter("@DisplayName", SqlDbType.NVarChar, 128) { Direction = ParameterDirection.Output };
                SqlParameter outResult = new SqlParameter("@Result", SqlDbType.VarChar,2500){ Direction = ParameterDirection.Output };              

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "GetAssessment";
                cmd.Parameters.AddWithValue("@AssessmentName", lblAsse.Text);
                cmd.Parameters.AddWithValue("@UserId", 2);
                cmd.Parameters.Add(outScore);
                cmd.Parameters.Add(outComp);
                cmd.Parameters.Add(outName);
                cmd.Parameters.Add(outResult);
                cmd.ExecuteScalar();

                SScore = outScore.ToString();
                SName = outName.ToString();
                SResult = outResult.ToString();
                SComp = outComp.ToString();

                conn.Close();

                lblAsse.Text = SScore;`

Output :

输出 ：

@Score

What can possibly be wrong with me or my code. Please help!

我或我的代码可能有什么问题。请帮忙！

采纳答案by marc_s

You just need to read out the actual valuesfrom your output parameters:

您只需要从输出参数中读出实际值：

 SScore = outScore.Value;

The .ToString()doesn't return the value - it returns the name of the parameter instead...

将.ToString()不会返回值-它返回的参数的名称，而不是...

See the MSDN documentation on SqlParameterfor more details.

有关更多详细信息，请参阅MSDN 文档SqlParameter。

回答by Wasif Ali

just need to do this. Before getting the output parameters you must close the Data reader as

只需要这样做。在获取输出参数之前，您必须关闭数据阅读器

    reader.Close();

and then you get output parameters as

然后你得到输出参数

    SScore = outScore.Value.Tostring();

for more help consult this http://msdn.microsoft.com/en-us/library/ms971497

如需更多帮助，请咨询此http://msdn.microsoft.com/en-us/library/ms971497

回答by Yashwant Software Developer

>Try this its working fine for the multiple output parameter:

>试试这个它的多输出参数工作正常：

     using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["conStringEndicia"].ConnectionString)){

            using (var sqlCmd = new SqlCommand("endicia.credentialLookup", sqlConnection))
            { 

                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@accountNumber", accountNumber);
                SqlParameter outLogin = new SqlParameter("@login", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                sqlCmd.Parameters.Add(outLogin);
                SqlParameter outPassword = new SqlParameter("@password", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                sqlCmd.Parameters.Add(outPassword);
                sqlConnection.Open();
                sqlCmd.ExecuteNonQuery();
                string login, password;
                login = outLogin.Value.ToString();
                password = outPassword.Value.ToString();                        
            }
        }