Assign query result to a variable and return variable-CodePudding

private int getuserid(String username){
        
            SqlConnection con = new SqlConnection(_conString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "SELECT USER_ID from tblUser where USERNAME ='"  username "'";
            int locid = Convert.ToInt32(cmd.CommandText);

            return locid;
        }

Hi everyone, do you have an idea on how to assign value of user_id in a variable and return it. I tried but I am not sure its good.

CodePudding user response：

try

using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();

    cmd.CommandText = "SELECT USER_ID from tblUser where USERNAME =@user_name";
    cmd.Parameters.AddWithValue("@user_name", user_name);
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            int locaid = Convert.ToInt32(reader.GetOrdinal("USER_ID"));
        }
    }
}

    int locaid = (int) cmd.ExecuteScalar();

CodePudding user response：

You need to actually open the connection and run the command.

You should also take measures to protect against sql injection and leaving the connection open in the event of an exception. This code solves all three concerns:

private int getuserid(String username)
{
    using (var con = new SqlConnection(_conString))
    using (var cmd = new SqlCommand("SELECT user_id FROM tblUser WHERE username = @username", con))
    {
        // Use actual column type and length from the database
        cmd.Parameters.Add("@username", SqlDbType.NVarChar, 30).Value = username;
        con.Open();
        return (int)cmd.ExecuteScalar();
    }
}

Finally, you need to define what you want to happen if there's no match for your WHERE clause. Right now there will be an exception, but that's also true for the original code in the question, so as far as I know at this time that's what you actually want to do.