Why can't I get output parameter value back from executenonquery?

Question

5.00/5 (1 vote)

See more:

I am executing a stored procedure and trying to get the 'after execution' value of an output parameter (@parmRecID), which should be the value of the RecID of the record the stored procedure added to a table.

The value of the output parameter is always "1" although the stored procedure correctly sets the it to the RecID of the newly added record.

The below code instantiates a class then uses a function from that class to execute the stored procedure and then (unsuccesfully) retrieve the value of the output parameter and return that value as the return value of the called method (function).

C#

SQLDatabase MySQLClass = new SQLDatabase();
string TheDatabase = "CustomerDB";
string TheProcedure = "usp_AddCustomer";

Dictionary<string, ParameterValues> TheParams = new Dictionary<string, ParameterValues>();
ParameterValues values = new ParameterValues();
values.Value = TxtFirstName.Text; values.Direction = "Input";
TheParams.Add("@parmFirstName", values);
values = new ParameterValues();
values.Value = TxtLastName.Text; values.Direction = "Input";
TheParams.Add("@parmLastName", values);
values = new ParameterValues();
values.Value = TxtAddress.Text; values.Direction = "Input";
TheParams.Add("@parmAddress", values);
values = new ParameterValues();
values.Value = TxtCity.Text; values.Direction = "Input";
TheParams.Add("@parmCity", values);
values = new ParameterValues();
values.Value = TxtZipCode.Text; values.Direction = "Input";
TheParams.Add("@parmZipCode", values);
values = new ParameterValues();
values.Value = ""; values.Direction = "Output";
TheParams.Add("@parmRecID", values);

string RecID = MySQLClass.ExecStoredProcedure(TheDatabase, TheProcedure, TheParams);

MessageBox.Show("From FrmMain, RecID is " + RecID);

Below is the called method (MySQLClass.ExecStoredProcedure):

C#

public string ExecStoredProcedure(string Database, string Procedure, Dictionary<string, ParameterValues> Parameters)
        {
            string TheConnectionString = $"Data Source=DESKTOP-EL4GU86;Initial Catalog={Database};Integrated Security=true";
            using (SqlConnection Conn = new SqlConnection(TheConnectionString))
            {
                using (SqlCommand Cmd = new SqlCommand(Procedure, Conn))
                {
                    Conn.Open();
                    Cmd.CommandType = CommandType.StoredProcedure;
                    foreach (KeyValuePair<string, ParameterValues> entry in Parameters)
                    {
                        ParameterValues vals = entry.Value;
                        Cmd.Parameters.AddWithValue(entry.Key, vals.Value);
                        switch (vals.Direction)
                        { 
                            case "Output":
                                Cmd.Parameters[entry.Key].Direction = ParameterDirection.Output;
                                break;
                            default:
                                Cmd.Parameters[entry.Key].Direction = ParameterDirection.Input;
                                break;
                        }
                    }

                    Cmd.ExecuteNonQuery();

                    foreach (SqlParameter param in Cmd.Parameters)      // This 'foreach' is just to show the 'after execution'
                    {                                                   // value of the output parameter..
                        string pName = param.ParameterName;
                        if (pName == "@parmRecID")
                        {
                            string pValue = param.Value.ToString();
                            MessageBox.Show("From SQLClass, " + pName + " = " + pValue);
                        }
                    }

                    return Cmd.Parameters["@parmRecID"].Value.ToString();
                }
            }
        }
    }

    public class ParameterValues
    {
        public string Value { get; set; }
        public string Direction { get; set; }
    }

While researching this problem I found "System.Data.ParameterDirection.ReturnValue;"
which would allow me to get a return value from the stored procedure, but I'm just now trying to figure out why I can't get the value of the output parameter after executing the stored procedure...

While I'm at it, here's the stored procedure...

SQL

CREATE OR ALTER procedure [dbo].[usp_AddCustomer] 

	@parmFirstName as varchar(50),
	@parmLastName as varchar(50),
	@parmAddress as varchar(50),
	@parmCity as varchar(50),
	@parmZipCode as varchar(50),
	@parmRecID as varchar(10) output

as

	if exists
				(
					select	* 
					from	CustomerDB.dbo.SmallCustomerTable 
					where	FirstName = @parmFirstName
					and		LastName = @parmLastName
					and		Address = @parmAddress
				)
			set @parmRecID = '0'
	else
			begin
				insert into	CustomerDB.dbo.SmallCustomerTable
				select		@parmFirstName,
							@parmLastName,
							@parmAddress,
							@parmCity,
							@parmZipCode 
				set @parmRecID = (select convert(varchar, max(RecID)) from SmallCustomerTable)
			end

GO

What I have tried:

The above code is what I have tried...

Posted 13-Feb-20 7:02am

Member 14633063

Updated 13-Feb-20 8:06am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-02-13T08:07:00

Because the value of your parameter is set to an empty string, and you don't set the parameter's Size property, the parameter defaults to nvarchar(1).

As a result, your code will only return the first character of the output parameter value.

The quick fix is to set Size = -1 on the output parameter.

C#

case "Output":
    Cmd.Parameters[entry.Key].Direction = ParameterDirection.Output;
    Cmd.Parameters[entry.Key].Size = -1;
    break;

There are various other improvements you could make. For example, your code will currently only work if you have a parameter called @parmRecID, and it will only return the value of that parameter. Any other output parameters will be ignored.

You should also use SCOPE_IDENTITY within your stored procedure, rather than selecting the maximum ID.
SCOPE_IDENTITY (Transact-SQL) - SQL Server | Microsoft Docs[^]

Here's a start:

C#

public class ParameterValue
{
    public object Value { get; set; }
    public string Direction { get; set; } = "Input";
    public int? Size { get; set; }
}

public class SQLDatabase
{
    private readonly string _connectionString;
    
    public SQLDatabase(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public SQLDatabase() : this("Data Source=DESKTOP-EL4GU86;Integrated Security=true")
    {
    }
    
    public int ExecuteNonQuery(string database, string commandText, IReadOnlyDictionary<string, ParameterValue> parameters, CommandType commandType = CommandType.StoredProcedure)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            connection.ChangeDatabase(database);
            
            using (var command = new SqlCommand(commandText, connection))
            {
                command.CommandType = commandType;
                
                var outputParameters = new Dictionary<SqlParameter, ParameterValue>();
                foreach (var pair in parameters)
                {
                    var param = command.Parameters.AddWithValue(pair.Key, pair.Value.Value);
                    if (pair.Value.Size != null) param.Size = pair.Value.Size.GetValueOrDefault();
                    
                    switch (pair.Value.Direction)
                    {
                        case "Output":
                        {
                            param.Direction = ParameterDirection.Output;
                            outputParameters.Add(param, pair.Value);
                            break;
                        }
                        case "ReturnValue":
                        {
                            param.Direction = ParameterDirection.ReturnValue;
                            outputParameters.Add(param, pair.Value);
                            break;
                        }
                    }
                }
                
                int result = command.ExecuteNonQuery();
                
                foreach (var pair in outputParameters)
                {
                    pair.Value.Value = pair.Key.Value;
                }
                
                return result;
            }
        }
    }
}

Usage:

C#

var db = new SQLDatabase();
const string theDatabase = "CustomerDB";
const string commandText = "usp_AddCustomer";

var parameters = new Dictionary<string, ParameterValue>
{
    ["@paramFirstName"] = new ParameterValue { Value = TxtFirstName.Text, Size = 50 },
    ["@paramLastName"] = new ParameterValue { Value = TxtLastName.Text, Size = 50 },
    ["@paramAddress"] = new ParameterValue { Value = TxtAddress.Text, Size = 50 },
    ["@paramCity"] = new ParameterValue { Value = TxtCity.Text, Size = 50 },
    ["@paramZipCode"] = new ParameterValue { Value = TxtZipCode.Text, Size = 50 },
    ["@paramRecID"] = new ParameterValue { Direction = "Output", Size = -1 }
};

db.ExecuteNonQuery(theDatabase, commandText, parameters);

object RecID = parameters["@paramRecID"].Value;
MessageBox.Show("From FrmMain, RecID is " + RecID);

Stored procedure:

SQL

CREATE OR ALTER PROCEDURE [dbo].[usp_AddCustomer] 
(
    @parmFirstName As varchar(50),
    @parmLastName As varchar(50),
    @parmAddress As varchar(50),
    @parmCity As varchar(50),
    @parmZipCode As varchar(50),
    @parmRecID As int OUTPUT
)
As
    If Exists
    (
        SELECT * 
        FROM dbo.SmallCustomerTable 
        WHERE FirstName = @parmFirstName
        And LastName = @parmLastName
        And Address = @parmAddress
    )
    BEGIN
        SET @paramRecID = 0;
    END
    Else
    BEGIN
        INSERT INTO dbo.SmallCustomerTable (FirstName, LastName, Address, City, ZipCode)
        VALUES (@parmFirstName, @parmLastName, @parmAddress, @parmCity, @parmZipCode);
        
        SET @paramRecID = SCOPE_IDENTITY();
    END;
GO