C #에서 저장 프로 시저 출력 매개 변수 사용

C #에서 저장 프로 시저 출력 매개 변수 사용

---

Sql Server 저장 프로 시저의 출력 매개 변수를 C # 변수로 반환하는 데 문제가 있습니다. 나는 여기뿐만 아니라 다른 사이트에서 이것에 관한 다른 게시물을 읽었으며 작동시킬 수 없습니다. 여기 내가 현재 가지고있는 것이 있습니다. 현재 나는 돌아 오는 값을 인쇄하려고합니다. 다음 코드는 null 값을 반환합니다. 내가 반환하려는 것은 기본 키입니다. 나는 @@IDENTITY및 SCOPE_INDENTITY()(즉 SET @NewId = SCOPE_IDENTITY())을 사용해 보았습니다 .

저장 프로 시저 :

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),

    @NewId int OUTPUT
AS
BEGIN

    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)

    Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber
END

데이터베이스 열기 :

pvConnectionString = "Server = Desktop-PC\\SQLEXPRESS; Database = PVDatabase; User ID = sa;
    PASSWORD = *******; Trusted_Connection = True;";

try
{
    pvConnection = new SqlConnection(pvConnectionString);
    pvConnection.Open();
}
catch (Exception e)
{
    databaseError = true;
}

다음 명령을 실행합니다.

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);

pvCommand.Transaction = pvTransaction;
pvCommand.CommandType = CommandType.StoredProcedure;    

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));

SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@NewId";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
pvCommand.Parameters.Add(pvNewId);

try
{
    sqlRows = pvCommand.ExecuteNonQuery();

    if (sqlRows > 0)
        Debug.Print("New Id Inserted =  ", 
            pvCommand.Parameters["@NewId"].Value.ToString()); 
    }
    catch (Exception e)
    {
        Debug.Print("Insert Exception Type: {0}", e.GetType());
        Debug.Print("  Message: {0}", e.Message);
    }
}

---

저장 프로 시저 (를 사용하기 위해 SCOPE_IDENTITY) 를 약간 수정 했으며 다음과 같이 보입니다.

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Contracts] (ContractNumber)
    VALUES (@ContractNumber)

    SELECT @NewId = SCOPE_IDENTITY()
END

나는 이것을 시도했고 잘 작동합니다 (수정 된 저장 프로 시저로).

// define connection and command, in using blocks to ensure disposal
using(SqlConnection conn = new SqlConnection(pvConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    // set up the parameters
    cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7);
    cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

    // set parameter values
    cmd.Parameters["@ContractNumber"].Value = contractNumber;

    // open connection and execute stored procedure
    conn.Open();
    cmd.ExecuteNonQuery();

    // read output value from @NewId
    int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    conn.Close();
}

Does this work in your environment, too? I can't say why your original code won't work - but when I do this here, VS2010 and SQL Server 2008 R2, it just works flawlessly....

If you don't get back a value - then I suspect your table Contracts might not really have a column with the IDENTITY property on it.

---

Before changing stored procedure please check what is the output of your current one. In SQL Server Management run following:

DECLARE @NewId int
EXEC    @return_value = [dbo].[usp_InsertContract]
            N'Gary',
            @NewId OUTPUT
SELECT  @NewId

See what it returns. This may give you some hints of why your out param is not filled.

---

Stored Procedure.........

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7)
AS
BEGIN

    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
END

C#

pvCommand.CommandType = CommandType.StoredProcedure;

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));
object uniqueId;
int id;
    try
    {
    uniqueId = pvCommand.ExecuteScalar();
     id = Convert.ToInt32(uniqueId);
    }
    catch (Exception e)
    {
        Debug.Print("  Message: {0}", e.Message);
    }
}

EDIT: "I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'll take this up again tomorrow. I'm off to my other job,"

I believe the Id column in your SQL Table isn't a identity column.

---

In your C# code, you are using transaction for the command. Just commit the transaction and after that access your parameter value, you will get the value. Worked for me. :)

ReferenceURL : https://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp