在c#中读取输出sql server存储过程参数

本文关键字：server 存储过程 参数 sql 输出 读取 | 更新日期: 2023-09-27 18:16:17

我期望在SQL Server 2008中插入语句的两个输出id。我相信我在初始化AddParam时做了一些错误的事情，因为它没有插入数据。存储过程本身，也是没有参数的代码，已经测试过了，它工作了。

存储过程

USE [mydb]
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_assessment_dfn_statementAnswer_insert]
@StatementID AS bigint,
@StaffID AS int,
@AssessmentID As bigint,
@StatementText AS nvarchar(MAX), 
@StatementDate AS Date,
@StatementAnswerID AS bigint OUTPUT,
               --
@SignatureCheck AS bit,
@SignatureDate AS Date,
@ElectronicSignatureID AS bigint OUTPUT
 AS
 SET NOCOUNT ON 
 SET XACT_ABORT ON 
-- local variables
DECLARE @l_object    AS SYSNAME = OBJECT_NAME(@@PROCID)
   ,@l_error_msg AS NVARCHAR(2000)   

 BEGIN TRY
   BEGIN TRAN
 INSERT INTO [adb_TestDb].[dbo].[Assessment_Statement_Answer]
       ([StatementID],[StaffID],[AssessmentID],[StatementText],[Date])
 VALUES (@StatementID, @StaffID, @AssessmentID, @StatementText, @StatementDate)

        SET @StatementAnswerID = @@IDENTITY;
 IF(@StatementAnswerID>0)
 BEGIN
    INSERT INTO [adb_TestDb].[dbo].[Assessment_ElectronicSignature]([AssessmentID],[ElectronicSignatureCheck],[SignatureDateAndTime])
    VALUES (@AssessmentID, @SignatureCheck,@SignatureDate)
        SET @ElectronicSignatureID = @@IDENTITY;
 END

  IF(@ElectronicSignatureID>0)
  BEGIN
    INSERT INTO [adb_TestDb].[dbo].[AssessorSignature]([AssessmentElectronicSignatureID],[StatementAnswerID],[AssessorID])
    VALUES(@ElectronicSignatureID, @StatementAnswerID, @AssessmentID)
   END
 COMMIT TRAN 

END TRY
BEGIN CATCH
 -- rollback any open/uncomitted transactions
IF XACT_STATE() IN ( -1, 1) ROLLBACK TRANSACTION            
 -- return an error containing the object, error number and error description
SELECT @l_error_msg = 'Error number : ' + CAST(ERROR_NUMBER()AS VARCHAR) + '. ' + ERROR_MESSAGE()          
RAISERROR (@l_error_msg,16,1)
END CATCH

ViewModel 

    public class AssessmentStatementAnswerViewModel
{
    public AssessmentStatementAnswerViewModel() { }

        public long StatementAnswerID { get; set; }
        public long StatementID { get; set; }
        public int StaffID { get; set; }
        public long AssessmentID { get; set; }
        public string StatementText { get; set; }
        public DateTime Date { get; set; }
        public string SubmitStatus { get; set; }
        //
        public long AssessmentElectronicSignatureID { get; set; }
        public bool ElectronicSignatureCheck { get; set; }
        public DateTime SignatureDateAndTime { get; set; }
}

需要帮助的c#类

 private static long InsertStatementAnswer_DF_P(ADB.Model.AssessmentStatementAnswerViewModel assessmentAnswerObj, MSSQL sql)
   {
       bool killConnection = Utils.getConnection(ref sql);
       long returnValue = 0;
       try
       {
           sql.SetSProc("[dbo].[p_assessment_dfn_statementAnswer_insert]");
           sql.AddParam("@StatementID", assessmentAnswerObj.StatementID);
           sql.AddParam("@StaffID", assessmentAnswerObj.StaffID);
           sql.AddParam("@AssessmentID", assessmentAnswerObj.AssessmentID);
           sql.AddParam("@StatementText", assessmentAnswerObj.StatementText);
           sql.AddParam("@StatementDate", assessmentAnswerObj.Date);              
           sql.AddParam("@SignatureCheck", assessmentAnswerObj.ElectronicSignatureCheck);
           sql.AddParam("@SignatureDate", assessmentAnswerObj.SignatureDateAndTime);
           sql.AddOutputParam("@StatementAnswerID", SqlDbType.BigInt, 0);
           sql.AddOutputParam("@ElectronicSignatureID", SqlDbType.BigInt, 0);

           sql.ExecuteNonQuery();

       }
       finally
       {
           if (killConnection)
               sql.Dispose();
       }
       return returnValue;
   }

在c#中读取输出sql server存储过程参数

设置@StatementAnswerID和@ElectronicSignatureID为输出参数:

cmd.Parameters("@StatementAnswerID").Direction = ParameterDirection.Output;
cmd.Parameters("@ElectronicSignatureID").Direction = ParameterDirection.Output;

执行存储过程。阅读如下:

cmd.Parameters("@StatementAnswerID").Value;
cmd.Parameters("@ElectronicSignatureID").Value;

private static long InsertStatementAnswer_DF_P(ADB.Model.AssessmentStatementAnswerViewModel assessmentAnswerObj, MSSQL sql)
   {
       bool killConnection = Utils.getConnection(ref sql);
       long returnValue = 0;
       try
       {
           sql.SetSProc("[dbo].[p_assessment_dfn_statementAnswer_insert]");
           sql.AddParam("@StatementID", assessmentAnswerObj.StatementID);
           sql.AddParam("@StaffID", assessmentAnswerObj.StaffID);
           sql.AddParam("@AssessmentID", assessmentAnswerObj.AssessmentID);
           sql.AddParam("@StatementText", assessmentAnswerObj.StatementText);
           sql.AddParam("@StatementDate", assessmentAnswerObj.Date);              
           sql.AddParam("@SignatureCheck", assessmentAnswerObj.ElectronicSignatureCheck);
           sql.AddParam("@SignatureDate", assessmentAnswerObj.SignatureDateAndTime);
         //  sql.AddOutputParam("@StatementAnswerID", SqlDbType.BigInt, 0);
          //  sql.AddOutputParam("@ElectronicSignatureID", SqlDbType.BigInt, 0);
SqlParameter outPutParameter1 = new SqlParameter();
outPutParameter1 .ParameterName = “@StatementAnswerID”;
outPutParameter1 .SqlDbType = System.Data.SqlDbType.Int;
outPutParameter1 .Direction = System.Data.ParameterDirection.Output;
SqlParameter outPutParameter2 = new SqlParameter();
outPutParameter2 .ParameterName = “@ElectronicSignatureID”;
outPutParameter2 .SqlDbType = System.Data.SqlDbType.Int;
outPutParameter2 .Direction = System.Data.ParameterDirection.Output;
sql.Parameters.Add(outPutParameter2 );
sql.Parameters.Add(outPutParameter1 );
           sql.ExecuteNonQuery();
//Here Your Values
string ElectronicSignatureID= outPutParameter2 .Value.ToString();
string StatementAnswerID= outPutParameter1 .Value.ToString();

       }
       finally
       {
           if (killConnection)
               sql.Dispose();
       }
       return returnValue;
   }
相关文章：