truncated output parameter from executenonquery

Question

I am trying to implement ExecuteNonQuery in C#. The storedProcedure on its own is working fine when I execute in SQL. It is returning a GUID for SubmissionId and the text 'submit sequence' for SubmitSequence. But in .net, it's returning one character as output for SubmitSequence

ALTER PROC [dbo].[test_SP]
(
    @FormId uniqueidentifier,
    @FormCode varchar(10),
    @FormTitle nvarchar(200),
    @User nvarchar(50),
    @Url nvarchar(255) = NULL,
    @Host nvarchar(50),
    @RemoteHost nvarchar(50) = NULL,
    @UserAgent nvarchar(255) = NULL, 
    @Referrer nvarchar(255) = NULL,
    @SubmissionId uniqueidentifier out,
    @SubmitSequence varchar(30) out
) AS

BEGIN 

SET @SubmissionId = newid();
set @SubmitSequence = 'submit sequence'

-- INSERT Query
SELECT 
    @SubmissionId as SubmissionId,  @SubmitSequence  as SubmitSequence
END 

But in the .net, I am getting the GUID for SubmissionId(which is correct) and the text 's' for SubmitSequence.

public SubmissionHeaderDTO GetRefNo()
        {
            var inPrms = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase)
                            {
                                {"FormId", Guid.NewGuid()},
                                {"FormCode", "TST"},
                                {"FormTitle", "Test form"},
                                {"User", "test"},
                                {"Host", "iisserver"}                               
                            };
            var outPrms = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase) { 
                            { "SubmissionId", Guid.NewGuid() },
                            { "SubmitSequence", "two"}
                            };
            var result = DBHelper.ExecSP(Constants.SPNames.SubmissionHeaderInsert, inPrms, outPrms);
            SubmissionHeaderDTO refNo = DictionaryToObject<SubmissionHeaderDTO>(result);
            return refNo;
        }

DBHelper.cs

public static Dictionary<string, object> ExecSP(string sp, Dictionary<string, object> paramIn, Dictionary<string, object> paramOut)
        {
            var dbAccess = new DBAccess();
            var results = dbAccess.ExecuteQuery(sp, paramIn, paramOut);
            return results;
        }

DBAccess.cs

public class DBAccess
    {
        public Dictionary<string, object> ExecuteQuery(string storedProcedure, Dictionary<string, object> parameters,
                                          Dictionary<string, object> outParameters)
        {

            using (var sqlConn = new SqlConnection(Configuration.DBConnection))
            {
                sqlConn.Open();
                using(var transaction = sqlConn.BeginTransaction("Results"))
                {
                    using(var sqlcmd = new SqlCommand(storedProcedure, sqlConn, transaction))
                    {
                        sqlcmd.CommandType = CommandType.StoredProcedure;
                        foreach(var kv in parameters)
                        {
                            sqlcmd.Parameters.AddWithValue(kv.Key, kv.Value);
                        }
                        foreach(var kv in outParameters)
                        {
                            sqlcmd.Parameters.AddWithValue(kv.Key, kv.Value).Direction = ParameterDirection.Output;
                        }

                        try
                        {
                            sqlcmd.ExecuteNonQuery();
                            var result = GetOutputParameters(sqlcmd.Parameters);
                            transaction.Commit();
                            return result;
                        }
                        catch(Exception)
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }

        }

        private Dictionary<string, object> GetOutputParameters(SqlParameterCollection paramCollection)
        {
            var returnParameters = new Dictionary<string, object>();
            foreach (SqlParameter par in paramCollection)
            {
                if ((par.Direction == ParameterDirection.Output) || (par.Direction == ParameterDirection.ReturnValue))
                {
                    returnParameters.Add(par.ParameterName, par.Value);
                }
            }
            return returnParameters;
        }
    }

Answer 1

Basically you need to give the correct SQLDbType and Size to your parameter. Especially if you omit the Size, your output will be truncated to 1 character.

SQL:

    @szError nvachar(max) output

C#:

    SqlParameter szError = new SqlParameter("szError", System.Data.SqlDbType.NVarChar)
    {
      Direction = System.Data.ParameterDirection.Output,
      Size = 4000
    };

Answer 2

I put your code into my own solution and recreated the problem. You are running into one of the reasons you should actually never do AddWithValue. Since you are adding a generic object, there is no way to infer what data type the sql parameter should be. If you break before ExecuteNonQuery and inspect the parameter list you will see it is defined as NVARCHAR with no length. So by default it is length 1. You need to build your sql parameters with a real SqlDbType and you should set them with the Value property instead of AddWithValue.

UPDATE

To get started dynamically querying the parameter meta-data look at this. Note you still have more work to do, like mapping the sql types to SqlDbType:

DECLARE @spName sysname;
SET @spName = 'test_SP';

DECLARE @objId int;
SELECT @objId = ISNULL([object_id], 0) FROM sys.procedures WHERE [name] = @spName;

SELECT p.[name], p.[system_type_id], t.[name], p.[max_length], p.[precision], 
    p.[scale], p.[is_output], p.[is_nullable]
FROM sys.parameters p
INNER JOIN sys.systypes t
    ON p.[system_type_id] = t.[xtype]
WHERE [object_id] = @objId;