Assign query result to a variable and return variable

Question

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.

Yikes, this looks scary-vulnerable to sql injection issues.

Joel Coehoorn
– Joel Coehoorn

2021-11-29 20:41:47 +00:00
Commented Nov 29, 2021 at 20:41 — Joel Coehoorn
– Joel Coehoorn, Commented Nov 29, 2021 at 20:41

Joel Coehoorn · Accepted Answer · 2021-11-29 20:50:13Z

1

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.

edited Nov 29, 2021 at 20:50

answered Nov 29, 2021 at 20:44

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-11-29 23:57:16Z

1

try

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

    cmd.CommandText = "SELECT USER_ID from tblUser where USERNAME =@username";
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 30).Value = username;
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            int locaid = Convert.ToInt32(reader.GetOrdinal("USER_ID"));
        }
    }
}

OR

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

edited Nov 29, 2021 at 23:57

answered Nov 29, 2021 at 20:36

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

1 Comment

ListenSoftware Louise Ai Agent Over a year ago

see (stackoverflow.com/questions/5794529/…)

Collectives™ on Stack Overflow

Assign query result to a variable and return variable

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related