Rework using parameterized queries C#

Question

There is a lot of tutorials regarding parameterized queries but most of them involve using SqlCommand, the only thing my code accepts is SqlDataAdapter for my SQL command, anytime I try to instantiate it as a SqlCommand I get errors.

TL;DR
I either need guidance on how to rework this using parameterized queries or I need to know why my SqlDataAdapter line doesn't work as an SqlCommand line.

private void btnLogin_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jake\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;");

    SqlDataAdapter sda = new SqlDataAdapter("Select Count(*) From Login Where Username = '" + txtUsername.Text + "' and Password = '" + txtPassword.Text + "'", con);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    if (dt.Rows[0][0].ToString() == "1")
    {
        this.Hide();
        MessageBox.Show("LOGIN!");
    }
    else
    {
        MessageBox.Show("FAILED!");
    }
}

the sql commend is to run statements like insert and update because you dont need to return nothing. If you need to return something you need a sqldatadadapter. Your query needs sqlparamenters, add them using @ParameterName and add the sql parameter to your sqldata adapter. If you keep your query like this you are vulnerable for sql injections and your code is not secure. — Juan
– Juan, Commented Sep 24, 2015 at 2:51

Juan · Accepted Answer · 2015-09-24 12:37:00Z

2

You can use:

   using (SqlDataAdapter sqlDA = new SqlDataAdapter("Select * from user where username = @username and password = @pass", sqlConnect))
    {
        sqlDA.SelectCommand.CommandType = CommandType.Text;
        sqlDA.SelectCommand.Parameters.Add("@username", SqlDbType.Varchar).Value = username;
sqlDA.SelectCommand.Parameters.Add("@pass", SqlDbType.Varchar).Value = password;

        sqlDA.Fill(dataTableVariable);
        return dataTableVariable;
    }

For your password remember to use a hash algorithm

This code is not tested. If you want the best performance you can explore execute scalar.

edited Sep 24, 2015 at 12:37

answered Sep 24, 2015 at 2:56

Juan

1,3921 gold badge15 silver badges20 bronze badges

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

2 Comments

Jaekx Over a year ago

Can you elaborate on hash algorithm? Sorry I'm new to this and you've got my attention 8)

Juan Over a year ago

The hash is one way encryption and is used to encrypt password most of the time. When your system create the user the password is encrypted using your hash algorithm (sha please) and then when the user wants to log in the password provided is converted to a hash a compared with the one stored in the database

Community · Accepted Answer · 2017-05-23 12:22:24Z

1

You can use Parameters.AddWithValue in SqlDataAdapter

    sda.SelectCommand.Parameters.AddWithValue("@ParamName",value);

Check this: c# Using Parameters.AddWithValue in SqlDataAdapter

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Sep 24, 2015 at 2:56

japzdivino

1,7463 gold badges17 silver badges25 bronze badges

2 Comments

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

japzdivino Over a year ago

@marc_s not really, it always depend on the situation, but if needed , he can use .Parameters.Add("@ParamName", DataTypeHere).Value = ValueHere

meda · Accepted Answer · 2015-09-24 03:01:17Z

1

Try using parametrize query, it will avoids errors and sql injection Once you have the reader you can use HasRow() to check if a record is returned

string conString = "xxxxxxxxxxxxxxxxxxxxxxxx";
using (SqlConnection con = new SqlConnection(conString))
{
    con.Open();
    using (var cmd = new SqlCommand(
    "SELECT * FROM Login Where Username=@Username AND Password=@Password",
    con))
    {
        cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
        cmd.Parameters.AddWithValue("@Password", txtPassword.Text);

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRow())
            {
                if(reader.Read())
                {
                    var username = reader["Username"].ToString();
                }
            }
            else
            {
                //User does not exists
            }
        } 
    }
}

You will need to import some library:

using System;
using System.Data;
using System.Data.SqlClient;

answered Sep 24, 2015 at 3:01

meda

45.5k14 gold badges96 silver badges123 bronze badges

3 Comments

Jaekx Over a year ago

Interesting this is totally new to me, ill have to mess around with this method of handling it, thank you for the help!

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

marc_s Over a year ago

Or since this query returns one row, one column only, it would be much simpler to use a SqlCommand cmd = ...... and then call cmd.ExecuteScalar() on it.....

Collectives™ on Stack Overflow

Rework using parameterized queries C#

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related