无法从存储过程中获取值

I am trying to get the value from a stored procedure, below is my code

我正在尝试从存储过程中获取值，下面是我的代码

SqlConnection connection = new SqlConnection("");
connection.Open();
DataTable timeZoneDt = new DataTable();
var sqlCommand = new SqlCommand("GetTimeZoneGMT", connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@TimeZone", SqlDbType.NVarChar).Value = "Asia/Kuala_Lumpur";
//timeZoneDt.Load(sqlCommand.ExecuteReader());
//new SqlDataAdapter(sqlCommand).Fill(timeZoneDt);

var reader2 = sqlCommand.ExecuteReader();


while (reader2.Read())
{
    var ID = int.Parse(reader2[0].ToString());
}
connection.Close();

the following is my stored procedure,

下面是我的存储过程，

/****** Object:  StoredProcedure [dbo].[GetTimeZoneGMT]    Script Date: 23/4/2018 4:05:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[GetTimeZoneGMT]
    @TimeZone NVARCHAR(128)
AS
BEGIN
 DECLARE @timeZoneNumber as INT = -20;
 IF @TimeZone ='Pacific/Midway'
 SET @timeZoneNumber = -11
 ELSE IF @TimeZone ='Pacific/Niue'
 SET @timeZoneNumber = -11
 ELSE IF @TimeZone ='Pacific/Pago_Pago'
 SET @timeZoneNumber = -11

 RETURN @timeZoneNumber;
END

I cannot write it all to you all because the timezone have 400 rows, however it is similiar like this.

我不能把所有的都写出来，因为时区有400行，但是它是这样的。

However, when i get my c# code, it is empty value, why? whats wrong with the code

但是，当我得到c#代码时，它是空值，为什么?代码有什么问题

1 个解决方案

#1

You need to declare a parameter for the ReturnValue even if you don't have it in your Stored Procedure declaration. You can name the parameter whatever you like but you should mark it as ParameterDirection.ReturnValue.

您需要为ReturnValue声明一个参数，即使在存储过程声明中没有。您可以随意命名参数，但应该将其标记为ParameterDirection.ReturnValue。

SqlParameter r = cmd.Parameters.Add("@timeZoneNumber", SqlDbType.Int);
r.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();

int result = Convert.ToInt32(r.Value);

Notice that you don't need to call ExecuteReader but just ExecuteNonQuery.

注意，不需要调用ExecuteReader，只需调用ExecuteNonQuery。