通过存储过程将 SQL Server 输出参数返回给 C# [英] Returning SQL Server Output parameter to C# by Stored Procedure

问题描述

我在 SQL Server 2012 中有一个带有 OUTPUT 参数的存储过程.当我调用 c# 解密函数时，当我点击 ExecuteNonQuery() 时，我总是得到错误:

I have a stored procedure in SQL Server 2012 with an OUTPUT parameter. When I call the c# decrypt function, at the point when I hit ExecuteNonQuery(), I always get the error:

过程或函数DecryptCCode"需要未提供的参数@decryptedStr".

Procedure or function 'DecryptCCode' expects parameter '@decryptedStr', which was not supplied.

如何在代码中获取存储过程的 OUTPUT 值?谢谢.

How do I get the OUTPUT value of my stored procedure in code? Thanks.

存储过程:

ALTER PROCEDURE [dbo].[DecryptCCode]
   @decryptedStr nchar(5) OUTPUT 
AS
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'rfsdffsssdfsdfwerefeses'

IF NOT EXISTS
    (SELECT * FROM sys.certificates WHERE name='ClientCert')  
    CREATE CERTIFICATE ClientCert 
    WITH SUBJECT = 'My ClientCode Certificate';

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE name='ClientCode_K1')   
    CREATE SYMMETRIC KEY ClientCode_K1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ClientCert;


OPEN SYMMETRIC KEY ClientCode_K1
   DECRYPTION BY CERTIFICATE ClientCert;

SELECT 
    @decryptedStr = CONVERT(nvarchar, DecryptByKey(ClientCode, 1 , HashBytes('SHA1', CONVERT(varbinary, InstitutionID)))) 
FROM 
    dbo.lu_Institution
END

C# 代码

public  string  Decrypt()
{
    using (var cn = new SqlConnection(((EntityConnection) ObjectContext.Connection).StoreConnection.ConnectionString))
    {
             try
             {
                 var sqlcmd = new SqlCommand("EXEC [dbo].[DecryptCCode]", cn);
                 sqlcmd.Parameters.Add("@decryptedStr", SqlDbType.NChar, 5);
                 sqlcmd.Parameters["@decryptedStr"].Direction = ParameterDirection.Output;
                 cn.Open();
                 sqlcmd.ExecuteNonQuery();
                 cn.Close();

                 return sqlcmd.Parameters["@decryptedStr"].Value != DBNull.Value ? (string)sqlcmd.Parameters["@decryptedStr"].Value : string.Empty;
             }
             catch (Exception e)
             {
                 cn.Close();
                 Console.WriteLine(e.Message);
                 return string.Empty;
             }
    }
}

推荐答案

您的代码看起来不错，但是您需要向 Command 指定 CommandType 属性，您尝试执行的 sql 是存储过程.

Your code looks fine, but you need to specify to the Command the CommandType property, that the sql you are trying to execute is a Stored Procedure.

public  string  Decrypt()
 {
     using (var cn = new SqlConnection(((EntityConnection) ObjectContext.Connection).StoreConnection.ConnectionString))
     {
         try
         {       
             cn.Open();

             var sqlcmd = new SqlCommand("[dbo].[DecryptCCode]", cn);

             // specify the command is a Stored Procedure
             sqlcmd.CommandType = CommandType.StoredProcedure;

             sqlcmd.Parameters.Add("@decryptedStr", SqlDbType.NChar, 5);
             sqlcmd.Parameters["@decryptedStr"].Direction = ParameterDirection.Output;

             sqlcmd.ExecuteNonQuery();          

             return sqlcmd.Parameters["@decryptedStr"].Value != DBNull.Value ? (string)sqlcmd.Parameters["@decryptedStr"].Value : string.Empty;
         }
         catch (Exception e)
         {
             Console.WriteLine(e.Message);
             return string.Empty;
         }
         finally
         {
            cn.Close();
         }
     }
 }

这篇关于通过存储过程将 SQL Server 输出参数返回给 C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！