从存储过程获取结果，然后传递给 WPF 文本框

Question

我有这个存储过程可以很好地执行：

ALTER PROCEDURE [dbo].[SP_GENERATETICKET] 
@RESULT AS VARCHAR(255) OUTPUT
AS
BEGIN
DECLARE @D AS DATETIME = GETDATE()
DECLARE @LASTTICKET AS VARCHAR(50) ;
DECLARE @NUM AS VARCHAR(50);
SET @LASTTICKET=(SELECT TOP 1 TICKETNO FROM tblTicket WHERE 
MONTH(ENTRYVEHICLE  ) = MONTH(@D ) ORDER BY TICKETNO DESC);
IF (CONVERT(VARCHAR(10),YEAR(@D),110) <> CONVERT(VARCHAR(10),YEAR(GETDATE()),110))--IF YEAR IS DIFFERENT, RESET SERIES
    BEGIN
        SET @NUM = '1'
    END
ELSE
    BEGIN 
        IF ISNULL(@LASTTICKET,'1')  = '1'--IF MONTH IS DIFFERENT, RESET SERIES
            BEGIN
                SET @NUM ='1'
            END
        ELSE
        BEGIN
                SET @NUM = CAST(CAST (RIGHT(@LASTTICKET,5) AS INT) + 1 AS VARCHAR)
        END
    END
SET @RESULT = RIGHT(CONVERT(VARCHAR(10),@D,101),2) + '-' + LEFT(CONVERT(VARCHAR(10),@D,101),2) + '-' + RIGHT('0000'+CAST(@NUM AS VARCHAR),5)
SELECT @RESULT
END

我有这个代码，但它返回空

值

SqlConnection con = new SqlConnection(gsql.connectionString);
SqlCommand command = new SqlCommand("SP_GENERATETICKET", con);
try
{
    con.Open();
    command.CommandType = CommandType.StoredProcedure;
    SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar);
    result.Direction = ParameterDirection.Output;
    txtTicket.Text = (string)command.Parameters["@RESULT"].Value;
}
    catch(Exception ex)
{
    MessageBox.Show(ex.Message);
}

我该怎么办？

Answer 1

有

问题，你忘了执行命令

command.ExecuteNonQuery()

如果不执行命令，您希望返回结果。

另外，您忘了为您添加尺寸 varchar 参数 ，所以添加事物行

result.Size = 8000;

在下面的代码中输出

---

你的代码将是，你需要用using为你处理东西

using(SqlConnection con = new SqlConnection(gsql.connectionString))
{
    using(SqlCommand command = new SqlCommand("SP_GENERATETICKET", con))
    {
        try
        {
            con.Open();
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar);
            result.Size = 8000;//add parameter size 
            result.Direction = ParameterDirection.Output;
            command.ExecuteNonQuery();//this line need to be added 
            txtTicket.Text = (string)command.Parameters["@RESULT"].Value;
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

Answer 2

参数

是一种很好的做法，但对于输出，你不能有SQL注入(我知道(。

con.Open();
command.CommandType = CommandType.StoredProcedure;  
txtTicket.Text = (string)command.ExecuteScalar();

对于大小，您可以使用以下语法：

SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar, 800);