Not able to access SQL Server stored procedure output parameter in C#

wbrvyc0a  于 2023-04-28  发布在  SQL Server
关注(0)|答案(1)|浏览(197)

I am trying to get a value of output parameter which I used in my stored procedure, but I am not getting it. I've been trying hard but could not figure it out. The program is not throwing any error, but in the end, the value of _clientCode is null (Refer screenshot):

I need your support, please help me to find out where I need to change.

public string getData5(string icuser)
{
    string _clientCode;

    SqlDataReader dataReader;
            
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Server=xxx-dbprod;DataBase=TCS;Integrated Security=True;";
                // conn.ConnectionString = "Data Source = tcr-dbprod; Initial Catalog = TCS; Integrated Security = True;";
                //conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

    SqlCommand command = new SqlCommand();
    command.Connection = conn;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "[dbo].[spTCRGetClientCode]";

    command.Parameters.AddWithValue("@icUserID", icuser);
    command.Parameters.Add(new SqlParameter("@clientCode", SqlDbType.NVarChar, 300));
    command.Parameters["@clientCode"].Direction = ParameterDirection.Output;
    
    try
    {
        conn.Open();

        int i = command.ExecuteNonQuery();
        dataReader = command.ExecuteReader();

        _clientCode = Convert.ToString(command.Parameters["@clientCode"].Value);
        conn.Close();

        return _clientCode;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return null;
        //conn.Close();
        //DbConnection.Dispose();
    }
}

I explained in my question detail and attached the screenshot for your help.

63lcw9qa

63lcw9qa1#

you can't use ExecuteReader and ExecuteNonQuery for a command,please review your code and use one of ways

you can use two below my code and replace with your code to solve your problem

your code:

int i = command.ExecuteNonQuery();
         dataReader = command.ExecuteReader();
        _clientCode = Convert.ToString(command.Parameters["@clientCode"].Value);

this is my code

int i = command.ExecuteNonQuery();
        _clientCode = Convert.ToString(command.Parameters["@clientCode"].Value);
         dataReader = command.ExecuteReader();
int i = command.ExecuteNonQuery();
 using (SqlDataReader dataReader = command.ExecuteReader())
        {
            while (reader.Read()) { /* do whatever with result set data here */ }
        }
        _clientCode = Convert.ToString(command.Parameters["@clientCode"].Value);

SqlCommand.ExecuteNonQuery Method

SqlCommand.ExecuteReader Method

Description for ExecuteNonQuery and ExecuteReader from ExecuteReader, ExecuteNonQuery and Executescalar in ADO.NET by Mageshwaran R

ExecuteNonQuery:

ExecuteNonQuery method is used to execute SQL Command or the storeprocedure performs, INSERT, UPDATE or Delete operations. It doesn't return any data from the database. Instead, it returns an integer specifying the number of rows inserted, updated or deleted.

ExecuteReader:

ExecuteReader method is used to execute a SQL Command or storedprocedure returns a set of rows from the database.

相关问题