Get return values from a stored procedure in c# (login process)

I am trying to use a Stored Procedure which takes two parameters (login, pw) and returns the user info.

If I execute the SP manually, I get

Session_UID   User_Group_Name      Sys_User_Name    
------------------------------------ -------------------------------------------------- -
NULL            Administrators       NTMSAdmin
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[p_SYS_Login].

But with the code below, I only get the return value. do you know how to get the other values shown above like Session_UID, User_Group_Name, and Sys_User_Name ?

if you see the commented part below code. I tried to add some output parameters but it doesn't work with incorrect number of parameters error.

            string strConnection = Settings.Default.ConnectionString;            
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    SqlDataReader rdr = null;
                    cmd.Connection = conn;
                    cmd.CommandText = "p_SYS_Login";
                    //cmd.CommandText = "p_sys_Select_User_Group";
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter paramReturnValue = new SqlParameter();
                    paramReturnValue.ParameterName = "@RETURN_VALUE";
                    paramReturnValue.SqlDbType = SqlDbType.Int;
                    paramReturnValue.SourceColumn = null;
                    paramReturnValue.Direction = ParameterDirection.ReturnValue;

                    //SqlParameter paramGroupName = new SqlParameter("@User_Group_Name", SqlDbType.VarChar, 50);
                    //paramGroupName.Direction = ParameterDirection.Output;

                    //SqlParameter paramUserName = new SqlParameter("@Sys_User_Name", SqlDbType.VarChar, 50);
                    //paramUserName.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(paramReturnValue);

                    //cmd.Parameters.Add(paramGroupName);
                    //cmd.Parameters.Add(paramUserName);

                    cmd.Parameters.Add开发者_开发问答WithValue("@Sys_Login", textUserID.Text);
                    cmd.Parameters.AddWithValue("@Sys_Password", textPassword.Text);

                    try
                    {
                        conn.Open();
                        object result = cmd.ExecuteNonQuery();
                        int returnValue = (int)cmd.Parameters["@RETURN_VALUE"].Value;
                        if (returnValue == 0)
                        {
                            Hide(); 
                            Program.MapForm.Show();
                        }
                        else if (returnValue == 1)
                        {
                            MessageBox.Show("The username or password you entered is incorrect", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                        else if (returnValue == 2)
                        {
                            MessageBox.Show("This account is disabled", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                        else                         
                        {
                            MessageBox.Show("Database error. Please contact administrator", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                    catch (Exception ex)
                    {
                        string message = ex.Message;
                        string caption = "MAVIS Exception";
                        MessageBoxButtons buttons = MessageBoxButtons.OK;

                        MessageBox.Show(
                            message,
                            caption,
                            buttons,
                            MessageBoxIcon.Warning,
                            MessageBoxDefaultButton.Button1);
                    }                    
                }
            }

Thanks for your help.

Have you tried with Try with ParameterDirection.Output?

 SqlParameter paramReturnValue = new SqlParameter();
 paramReturnValue.ParameterName = "@RETURN_VALUE";
 paramReturnValue.Direction = ParameterDirection.Output;

 ...

 conn.Open();
 cmd.Connection = conn;
 cmd.ExecuteNonQuery();

 ...

 int returnValue = (int)cmd.Parameters["@RETURN_VALUE"].Value;

If your SP uses an OUT parameter, like this:

 CREATE PROC FOO(@bar INT OUT) AS
 SELECT @bar = 1

Then you need to use the ParameterDirection.Output when setting up your SqlCommand object.

However, if your SP uses the RETURN keyword, like this:

 CREATE PROC FOO(@bar INT OUT) AS
 RETURN 1 -- NOTE the RETURN

Then you have to use ParameterDirection.ReturnValue. I suspect the former case is the good one.

See this previous question

You need to call ExecuteReader method on the cmd object and read the output by using Read method of the SqlDataReader class.

Here is an example: SqlDataReader.Read method.