Comment puis-je récupérer avec succès les paramètres de sortie d'une procédure stockée SQL Server en C# ?

Utilisation des paramètres de sortie de procédure stockée en C#

Question

Le retour des paramètres de sortie d'une procédure stockée SQL Server dans une variable C# peut être difficile. Malgré diverses recommandations, de nombreux développeurs signalent toujours des valeurs nulles ou d'autres problèmes.

Procédure stockée

Considérez la procédure stockée suivante usp_InsertContract, conçue pour insérer un numéro de contrat dans la base de données et renvoyer la clé primaire comme paramètre de sortie :

<code class="language-sql">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</code>

Ouvrir la base de données

Ouvrez la connexion à la base de données en utilisant le code suivant :

<code class="language-csharp">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;
}</code>

Exécuter la commande

Exécutez la procédure stockée en utilisant le code suivant :

<code class="language-csharp">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);
}</code>

Solution

Utilisez SCOPE_IDENTITY() pour récupérer la clé primaire et apporter quelques modifications à la procédure stockée pour résoudre le problème de valeur nulle :

<code class="language-sql">CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Contracts] (ContractNumber)
    VALUES (@ContractNumber);
    SELECT @NewId = SCOPE_IDENTITY();
END</code>

Pour utiliser cette procédure stockée modifiée en C#, implémentez le code suivant :

<code class="language-csharp">using(SqlConnection conn = new SqlConnection(pvConnectionString))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7).Value = contractNumber;
    cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
    conn.Open();
    cmd.ExecuteNonQuery();
    int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    conn.Close();
}</code>

Ce code récupère et imprime avec succès la clé primaire renvoyée par la procédure stockée.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!