Вопрос по c#, sql-server – Используя выходные параметры хранимой процедуры в C #

49

У меня проблема с возвратом выходного параметра из хранимой процедуры Sql Server в переменную C #. Я читал другие посты, касающиеся этого, не только здесь, но и на других сайтах, и я не могу заставить его работать. Вот что у меня сейчас есть. В настоящее время я просто пытаюсь напечатать возвращаемое значение. Следующий код возвращает нулевое значение. То, что я пытаюсь вернуть, является первичным ключом. Я пытался использовать@@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);
    }
}
Не могли бы вы попытаться зафиксировать транзакцию перед чтением выходного параметра? Steve
Да, я вижу строку в базе данных. Gary
Я бы также использовал SCOPE_IDENTITY () вместо того, чтобы запрашивать ту же таблицу, и в случае, если вы получаете запрос, вы можете получить несколько результатов. ошибка при назначении одного выхода вар. JMC
Использую ли я команду set для этого? SET NewId = SCOPE_IDENTITY ()? Я заменил оператор выбора следующим ... SET @NewId = SCOPE_IDENTITY (), и я все еще получаю нулевое значение обратно. Gary
Конечно, вы можете подтвердить, что новая запись действительно вставлена, верно? Не могли бы вы показать больше своего кода на C # (открытие соединения, создание команды и т. Д.) Steve

Ваш Ответ

4   ответа
0

В вашем коде C # вы используете транзакцию для команды. Просто зафиксируйте транзакцию, и после этого получите доступ к значению вашего параметра, и вы получите это значение. Работал на меня. :)

104

Я немного изменил вашу хранимую процедуру (чтобы использовать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();
}

Это работает и в вашей среде? Я не могу сказать, почему ваш оригинальный код не работает - но когда я делаю это здесь, VS2010 и SQL Server 2008 R2, он просто работает без нареканий ....

Если вы не вернули значение - я подозреваю, что ваш столContracts может не иметь колонку сIDENTITY собственность на это.

@ User3569147:most definitely NOT!! Вы должны проверитьCan we stop using AddWithValue() already? и прекратить использование.AddWithValue() - это может привести к неожиданным и неожиданным результатам ...
Спасибо! Я ценю ваше время, отвечая на это для тех из нас, кто гуглит это годами позже. :-)
@marc_s, если размер вывода Varchar неизвестен, то как решить эту проблему.
cmd.Parameters.Add (& quot; @ NewId & quot ;, SqlDbType.Int) .Direction = ParameterDirection.Output; необходимо изменить на cmd.Parameters.AddWithValue (& quot; @ NewId & quot ;, SqlDbType.Int) .Direction = ParameterDirection.Output; Это потому, что он обесценился
3

Перед изменением хранимой процедуры, пожалуйста, проверьте, какой вывод у вашей текущей. В SQL Server Management запустите следующее:

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

Посмотрите, что это возвращает. Это может дать вам некоторые подсказки о том, почему ваш параметр не заполнен.

Это почти работает как ответ, но, вероятно, это должен быть комментарий, однако я не могу его преобразовать из-за использования нескольких символов @ (даже модам это запрещено), и я не могу его редактировать, чтобы он работал , :)
1

Хранимая процедура.........

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: & quot; Я все еще возвращаю значение DBNull .... Объект не может быть преобразован из DBNull в другие типы. Я займусь этим снова завтра. Я ухожу на другую работу "

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

enter image description here

Но это не отвечает на вопрос, почему выходной параметр для нового идентификатора возвращает ноль. СExecuteScalar Вы можете получить возвращаемое значение, а не выходной параметр.
Это потому, что параметр называется SCOPE_IDENTITY в SQL; C # все еще ищет параметр с именем @ContractNumber.
Я также получаю следующую ошибку при внесении этого изменения ... Ссылка на объект не установлена на экземпляр объекта. Gary
ContractNumber - это значение, добавляемое в таблицу. Первичный ключ называется Id. Gary
ExecuteScalar возвращает объект, а не int. Я получаю исключение при выполнении кода. Gary

Похожие вопросы