If you're learning, you could perhaps move on from this old low level way of doing data access and use something a bit more modern and easy. Dapper is an example of a library that isn't a huge leap above what you already know but makes your life a lot nicer:
using(var conn = new MySqlConnection("conn str here"){
var sql = "SELECT count(*) FROM tblUsers WHERE username = @u AND password = @p";
var prm = new {
u = txtUsername.Text, //give your textboxes better names than textbox2,textbox1!
p = txtPassword.Text.GetHashCode() //do NOT store plain text passwords!
};
bool valid = await conn.QuerySingleAsync<int>(sql, prm) > 0;
if(valid)
... valid login code
else
... invalid login
}
Some notes on this:
- dapper is a device that you simply give your sql and parameter values to
- the sql holds @parameters names like @u
- an anonymous typed object has properties called the same name as the parameter name, with a value, like
u = "my username"
- use async/await when running queries; dapper makes this easy. Avoid jamming your UI up on queries that take 10 seconds to run
- in this case you only need to ask the db to count the matching records, you don't need to download them all to find out if there are any, so we use
QuerySingleAsync<int> which queries a single value of type it, and if it's more than 0, the login was valid
- never store password in a database in plaintext. Use a one way hashing function like MD5, SHA256 etc, even the lowly string.GetHashCode is better than storing plaintext, particularly because people use the same passwords all the time so anyone breaking into your db (very easy; the password is in the code) will reveal passwords treat people probably use in their banking etc. We can't really be asking, on the one hand, how to prevent a huge security hole like SQL injection, and then on the other hand leave a huge security hole like plaintext passwords ;)
- always name your textboxes a better name than the default textboxX - it takes seconds and makes your code understandable. If Microsoft called all their class property names like that, then the entire framework would be full of things like
myString.Int1 rather than myString.Length and it would be completely unusable
- life is too short to spend it writing
AddWithValue statements; use Dapper, Entity Framework, strongly typed datasets.. Some db management technology that eases the burden of writing that code
Where Dapper makes things really nice for you is its ability to turn objects into queries and vice versa; this above is just a basic count example, but suppose you had a User class:
class User
{
string Name { get; set; }
string HashedPassword { get; set; }
int age {get; set; }
}
And you had a table tblUsers that was similar (column names the same as the property names), then you could query like:
User u = new User() { Name = "someuser" };
User t = await conn.QuerySingleAsync<User>("SELECT Name, HashedPassword, Age FROM tblUsers WHERE Name = @Name", u);
We want to look up all the info of the someuser user, so we make a new User with that Name set (we could also use anonymous type, like the previous example) and nothing else, and we pass that as the parameters argument. Dapper will see the query contains @Name, pull the contents of the Name from the u user that we passed in, and run the query. When the results return it will create a User instance for us, fully populated with all the data from the query
To do this old way we'd have to:
- have a command,
- have a connection,
- add parameters and values,
- open the connection,
- run the sql,
- get a reader,
- check if the reader had rows,
- loop over the reader pulling the first row,
- make a new
User,
- use
reader.GetInt/GetString etc to pull the column values out one by one and
- finally return the new user
- oh and dispose of all the db stuff, close the connection etc
Writing that code is repetitive, and it is really boring. In computing, when we have something repetitive and boring, that we need to do thousands of times through out life (like serializing to json, calling a webservice, designing a windows UI) we find some way to make the computer do the repetitive boring bit; they do it faster and more accurately than we can. This is exactly what Dapper does; it does away with that boring repetitive and reduces it to a single line where you say what you want back, using what query, with what parameters. And it keeps your UI working:
await x.QueryAsync<type>(query, parameters)
Win. Seek out some Dapper tutorials! (I have no affiliation)
class MySqlDataAdapterbut maybe there is parameterized overloads if the library support common SQL features. Or there are some APIs to add and pass SQL parameters when SQL executed.