How to get a parameter output from SQL Server with C# and put the value in a session variable

Question

I'm trying to obtain the user role from a select in SQL Server for blocking some pages depending on a session value. But actually I obtain an null value. To obtain the role I have an stored procedure with output parameter.

CREATE PROCEDURE obtenerROl 
     @user varchar(20), 
     @passwrd varchar(20), 
     @rol tinyint OUTPUT
AS
BEGIN
    SELECT @rol = idROl 
    FROM Users 
    WHERE nameUSer = @user AND password = @passwrd 

    RETURN @rol
END

In the login.aspx.cs page I try to retrieve the value for the output parameter and put in on a session variable.

public void obtenerRol()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connDb"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "obtenerROl";

        cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = txtUsuario.Text.Trim();
        cmd.Parameters.Add("@passwrd ", SqlDbType.VarChar).Value = txtContraseña.Text.Trim();
        SqlParameter rol = new SqlParameter("@rol", SqlDbType.TinyInt);
        rol.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(rol);

        cmd.Connection = conn;
        cmd.ExecuteReader(CommandBehavior.CloseConnection); 

        conn.Open();                    
        Session["UserRole"] = cmd.Parameters["@rol"].Value.ToString();
    }
}

After the login, in the main page I have a label just for checking the value of the session.

 mensajeID.Text = Session["UserRole"].ToString();

But I'm getting the error on that line:

System.Web.SessionState.HttpSessionState.this[string].get returned null.

So I'm guessing the method is not working well.

The last line I added was

cmd.ExecuteReader(CommandBehavior.CloseConnection);

because I read that is necessary to close the datareader to process the output parameters, but it didn't work. I hope you can help me with my problem, regards.

@Steve yeah it is varchar, sorry I deleted it by error, when i posted the question. — herMa694, May 26 '20 at 21:06
As a tip you should always specify the length of any varchar parameters in your C# code because otherwise you will get a different query plan for every different length string you pass in. — Dale K, May 26 '20 at 21:16
Have you tried running the SP from SSMS and checking the result? — Dale K, May 26 '20 at 21:17
Also, as `@rol` is an `output` parameter, you don't need to `return` it - return is used for a `return` value which you are not using here. — Dale K, May 26 '20 at 21:17
You're not opening the connection until after you call `ExecuteReader` - that doesn't look right. You need to step through your code in the debugger and view the parameters before and after the `execute` command. — Dale K, May 26 '20 at 21:20
In fact you don't want `ExecuteReader` you want `ExecuteNonQuery` AFAIK. — Dale K, May 26 '20 at 21:22
@Dale K let me try that cause actually the Stored Procedure is working well — herMa694, May 26 '20 at 21:25
When do you execute mensajeID.Text = Session["UserRole"].ToString(); Maybe before the Session Object is loaded? https://stackoverflow.com/a/1382811/1413718 — CaTeR, May 26 '20 at 21:32
@CaTeR It executes after the login, in the login.aspx.cs I try to get the value and load into a session variable. After its make the log in it redirects to Default.aspx where i try to load the session variable. — herMa694, May 26 '20 at 21:44
Do i see this right: You do the cmd.ExecuteReader(....); before conn.Open(); ? There should be an open connection before you execute the Reader — CaTeR, May 26 '20 at 22:05
@DaleK I change the code a little bit, just for checking the value on login page. But now I get this error `Procedure or function 'obtenerROl' expects parameter '@rol', which was not supplied.''` ¿why? @rol is an output parameter. — herMa694, May 26 '20 at 22:18
Even though you shouldn't need to, try setting its value to dbnull e.g. `Value = System.DBNull.Value` — Dale K, May 26 '20 at 22:20

score 0 · Answer 1 · answered May 26 '20 at 22:49

Hi I can only post code here and this would be the way I wrote it (couldn't test it):

I assume your default db schema is dbo => dbo.obtenerROl.

public void obtenerRol()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connDb"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand("dbo.obtenerROl", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = txtUsuario.Text.Trim();
            cmd.Parameters.Add("@passwrd ", SqlDbType.VarChar).Value = txtContraseña.Text.Trim();
            SqlParameter rol = new SqlParameter("@rol", SqlDbType.TinyInt);
            rol.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(rol);

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

            //according to https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/sql-clr-type-mapping
            byte userRole = (byte) cmd.Parameters["@rol"].Value;
            Session["UserRole"] = userRole;

            conn.Close();
        }
    }

and

CREATE PROCEDURE obtenerROl 
   @user varchar(20), 
   @passwrd varchar(20), 
   @rol tinyint OUTPUT
AS
BEGIN
   SELECT @rol = idROl 
   FROM Users 
   WHERE nameUSer = @user AND password = @passwrd 

   RETURN 0; -- for success
END

you could also

CREATE PROCEDURE obtenerROl 
    @user varchar(20), 
    @passwrd varchar(20)
AS
BEGIN
   DECLARE @rol tinyint = 0; -- default role
   SELECT @rol = idROl 
   FROM Users 
   WHERE nameUSer = @user AND password = @passwrd;

   SELECT @rol;
END

and your C# Code:
ExecuteScalar will get the @rol.

public void obtenerRol()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connDb"].ConnectionString))
    using (SqlCommand cmd = new SqlCommand("dbo.obtenerROl", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = txtUsuario.Text.Trim();
        cmd.Parameters.Add("@passwrd ", SqlDbType.VarChar).Value = txtContraseña.Text.Trim();

        conn.Open();
        var value = cmd.ExecuteScalar();

        //according to https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/sql-clr-type-mapping
        byte userRole = (byte)value;
        Session["UserRole"] = userRole;

        conn.Close();
    }
}

To be complete:

sanitize user inputs (txtUsuario/txtContrasena - are maybe TextInputFields?) to prevent SQL Injectiopns MSDN: SQL Injection
never save passwords cleartext in db SO: Some Best Practices for userdata

How to get a parameter output from SQL Server with C# and put the value in a session variable

1 Answers1