How to get SQL query result value into C# string variable

Data and Databases
1

I need help getting the result from a SQL select query into a string variable in C#. I’m building a login system where after users log in successfully, I want to check their role from the database and redirect them to different pages. Admin users should go to the admin dashboard and regular users should go to the user dashboard.

Here’s my current login button code:

protected void btnLogin_Click(object sender, EventArgs e)
{
    string connectionStr = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
    string query = "SELECT COUNT(*) FROM USER_ACCOUNTS WHERE USERNAME=@USERNAME AND PASS_WORD=@PASS_WORD";
    
    SqlConnection connection = new SqlConnection(connectionStr);
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandType = CommandType.Text;
    command.CommandText = query;
    
    SqlParameter userParam = new SqlParameter("@USERNAME", SqlDbType.NVarChar, 40);
    userParam.Value = txtUsername.Text.Trim();
    command.Parameters.Add(userParam);
    
    SqlParameter passParam = new SqlParameter("@PASS_WORD", SqlDbType.NVarChar, 60);
    passParam.Value = txtPassword.Text.Trim();
    command.Parameters.Add(passParam);
    
    connection.Open();
    int loginResult = (Int32)command.ExecuteScalar();
}

This is where I’m stuck:

    if (loginResult >= 1)
    {
        string roleQuery = "SELECT USER_TYPE FROM USER_ACCOUNTS WHERE USERNAME=@USERNAME";
        
        if (userRole == "MEMBER")
        {
            Session.Add("username", txtUsername.Text);
            Response.Redirect("~/MEMBER_PAGES/MemberDashboard.aspx");
        }
        else if (userRole == "ADMINISTRATOR")
        {
            Session.Add("username", txtUsername.Text);
            Response.Redirect("~/ADMIN_PAGES/AdminPanel.aspx");
        }
    }
    else
    {
        ClientScript.RegisterStartupScript(Page.GetType(), "error", "<script>alert('Login failed - wrong credentials')</script>");
    }
}

How do I properly execute the second query and store the role value in the userRole variable?

2

hey! you’re not actually executing your roleQuery - planning to use ExecuteScalar() for that one too? also, why not grab both the count and user_type in one query instead of hitting the database twice?

3

you’ll need a separate SqlCommand for the roleQuery. use ExecuteScalar() to grab the value, add the username parameter again, and cast it to string: string userRole = (string)command2.ExecuteScalar();

4

Just combine both operations into one query that returns the user type when credentials match. Change your query to SELECT USER_TYPE FROM USER_ACCOUNTS WHERE USERNAME=@USERNAME AND PASS_WORD=@PASS_WORD and use ExecuteScalar() to get the result. If the login fails, ExecuteScalar() returns null, so check if (result != null) for success. Cast it to string: string userRole = result?.ToString(). This cuts your database calls in half and ditches the row counting—same result, way more efficient.