c# - SQL输出参数

Question

我有一份调查问卷，其中存储每个进行测试的用户的答案。我需要将输出存储在数据库中(这是进行测试的用户。)我不太确定我做错了什么。目前我的inputID返回0。(InputAnswers将该用户的测试答案输入数据库)

CREATE PROCEDURE [dbo].[InputAnswers]

@QuestionID int,
@InputID int OUTPUT,
@Answer nchar,
@Remark nvarchar(50)

AS
BEGIN

SET NOCOUNT ON;

INSERT INTO Answers (InputID, QuestionID, Answer, Remark) VALUES (@InputID, @QuestionID, @Answer, @Remark)

END

下面的代码从包含问题的 ListView 加载答案:

        private void LoadAnswers(int inputID)
       {
        foreach (StepControl step in dckSteps.Children)
        {
            foreach (QuestionControl quest in step.listQuestions.Items)
            {
                int questionID = quest.questionID;
                string answer = quest.answer;
                string remark = quest.txtRemark.Text;

                SqlConnection conDatabase = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLSERVER_ID, SQLDatabaseName, SQLServerLoginName, SQLServerPassword));
                string query = "InputAnswers";

                SqlCommand cmd = new SqlCommand(query, conDatabase);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = inputID;
                cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = questionID;
                cmd.Parameters.Add("@Answer", SqlDbType.NChar).Value = answer;
                cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = remark;

                conDatabase.Open();
                cmd.ExecuteNonQuery();
                conDatabase.Close();
            }
        }
    }

然后我将这些答案加载到按钮单击方法中:

     private void btnSubmit_Click(object sender, RoutedEventArgs e)
        {
        string name = txtName.Text;
        string cno = txtCNO.Text;
        var date = datePicker.SelectedDate;
        int inputID = 0;

        SqlConnection conDatabase = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLSERVER_ID, SQLDatabaseName, SQLServerLoginName, SQLServerPassword));
        string query = "SaveInput";

        SqlCommand cmd = new SqlCommand(query, conDatabase);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = ParameterDirection.Output;
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
        cmd.Parameters.Add("@CNO", SqlDbType.NVarChar).Value = cno;
        cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;

        conDatabase.Open();
        cmd.ExecuteNonQuery();
        conDatabase.Close();

        LoadAnswers(inputID);
      }

除了将 OUTPUT 发送回数据库之外，这一切都可以正常工作。有什么想法吗？

编辑:过程 SaveInput 如下所示:(SaveInput 存储用户信息)

CREATE PROCEDURE [dbo].[SaveInput]

@InputID int OUTPUT,
@Name nvarchar(50),
@CNO nvarchar(50),
@Date DATETIME

AS
BEGIN

SET NOCOUNT ON;

IF(@InputID = 0)
BEGIN

 INSERT INTO Input (Name, CNO, Date) VALUES (@Name, @CNO, @Date)
 SET @InputID = @@IDENTITY 
END
ELSE
BEGIN

UPDATE Input SET Name = @Name,
CNO = @CNO,
Date = @Date
WHERE InputID = @InputID

END
END

我有一种感觉，我不需要在两个程序上都使用 OUTPUT。

Answer 1

这至少是问题的一部分:

cmd.Parameters.Add("@InputID", SqlDbType.Int).Value = ParameterDirection.Output;

这会创建一个输入参数 - 但为其提供一个值，即枚举值ParameterDirection.Output。你想要:

cmd.Parameters.Add("@InputID", SqlDbType.Int).Direction = ParameterDirection.Output;

执行命令后，您需要获取参数的结果:

cmd.ExecuteNonQuery();
// You might want to store the parameter reference in a local variable instead
// of fetching it again afterwards.
int inputId = (int) cmd.Parameters["@InputID"].Value;

如果您希望参数成为输入/输出，则应该使用ParameterDirection.InputOutput(并在执行命令之前为其指定一个值)。例如:

int inputId = ...;

var inputIdParameter = cmd.Parameters.Add("@InputID", SqlDbType.Int);
inputIdParameter.Direction = ParameterDirection.InputOutput;
inputIdParameter.Value = inputId;

...
cmd.ExecuteNonQuery();
inputId = (int) inputIdParameter.Value;