Returning SQL Server Output parameter to C# by Sto

问题:

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:

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

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

Stored procedure:

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# Code

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;
             }
    }
}

回答1:

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();
         }
     }
 }