CODEWITHSUNDEEP
c#asp.netsql
Nullbyte
Nullbyte

Reputation: 251

SQl from concatenation to parameters

I have this sql connection with concatenated strings query and I would like to change it to parameters. I am starting coding on my own and I do not have many reference. Tried googling around but I have not found something clear enough to me.

public bool DBAuthenticate(string strUsername, string strPassword)
{
    string sqlstring;
    sqlstring = "SELECT * FROM credentials WHERE [Username]='" + strUsername + "' AND [Password]='" + strPassword + "'";
    string getconnstring = ConfigurationManager.ConnectionStrings["WTAConnectionString"].ConnectionString;
    SqlConnection conn = new SqlConnection(getconnstring);
    System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn);
    System.Data.SqlClient.SqlDataReader reader;
    conn.Open();
    reader = comm.ExecuteReader();
    if (reader.Read())
        return true;
    else
       return false;
}

can someone please show me how to modify the code to change the query from concatenation to parameters? Thank you a lot.

Upvotes: 1

Views: 1829

Answers (2)

Christos
Christos

Reputation: 53958

Try this one:

sqlstring = "SELECT * FROM credentials WHERE [Username]=@UserName AND [Password]=@Password";

After the declaration of comm object, write this:

comm.Parameters.Add(new SqlParameter(@UserName,strUsername));
comm.Parameters.Add(new SqlParamter(@Password,strPassword);

Upvotes: 0

Markus
Markus

Reputation: 22481

In order to use parameters, you need to change your statement as follows:

SELECT * FROM credentials WHERE [Username]=@username AND [Password]=@password

It then contains two parameters (@username and @password) that you need to provide the values for in the command. You can do this with the AddWithValue method:

// ...
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn);
comm.Parameters.AddWithValue("@username", strUsername);
comm.Parameters.AddWithValue("@password", password);
System.Data.SqlClient.SqlDataReader reader;
// ...

Please also note that you should always dispose the connection, commands and readers reliably, so adding some using blocks will help:

using(SqlConnection conn = new SqlConnection(getconnstring))
{
    conn.Open();
    using(SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn))
    {
        // ...
        using(SqlDataReader reader = comm.ExecuteReader())
        {
            // ...
        }
    }
}

Upvotes: 5

Related Questions