How to check if I have the username in database C#?

Question

I'm new in C# and databases. I made a database in which I add data from a person. Actually, are data from a person who create an account. I checked if the email have email structure and I checked if password is the same with the retype password. Here you have the code:

if (Pass.Text != Rpass.Text)
        {
            MessageBox.Show("Password don't match!");
            return;
        }
        string emailCheck = Email.Text;

        bool valid = false;
        int count_dot = 0;

        for (int i = 0; i < emailCheck.Length; i++)
            if (emailCheck[i] == '@') valid = true;
            else if (emailCheck[i] == '.') count_dot++;

        if (valid == false || count_dot == 0)
        {
            MessageBox.Show("Invalid email!");
            return;
        }
        con = new SqlConnection(@"Data Source=MARIA-PC;Initial Catalog=Account;Integrated Security=True");
        con.Open();
        cmd = new SqlCommand("INSERT into [dbo].[Cont1] (Username,Password,First_name,Last_name,Birth_year,Email,Work_type) VALUES (@Username,@Password,@First_name,@Last_name,@Birth_year,@Email,@Work_type)", con);
        cmd.Parameters.Add("@Username", Username.Text);
        cmd.Parameters.Add("@Password", Password.Text);
        cmd.Parameters.Add("@First_name", First_name.Text);
        cmd.Parameters.Add("@Last_name", Last_name.Text);
        cmd.Parameters.Add("@Birth_year", DateTime.Parse(Birth_year.Text));
        cmd.Parameters.Add("@Email", Email.Text);
        cmd.Parameters.Add("@Work_type", Work_tpye.SelectedItem.ToString());
        cmd.ExecuteNonQuery();

        con.Close();

So, what I want to do is to check, before to add in database, if I already have the Username in database. I have the idea what I have to do, but I don't know how to do.

I would advise you to split your method into smaller methods - right now you have one method that is doing at least 3 things, so adding a fourth will make it even more complex. — D Stanley, Apr 19 '16 at 19:04
To answer your question, write a query that checks for an existing row (something like `SELECT 1 FROM Users where userName = @UserName`), execute the query, check the results. — D Stanley, Apr 19 '16 at 19:05
http://stackoverflow.com/questions/24098263/check-if-username-exists-in-database — Jande, Apr 19 '16 at 19:05

score 1 · Answer 1 · answered Apr 19 '16 at 19:07

1

Have a look at the Merge command. There are many other tutorials on the web.

answered Apr 19 '16 at 19:07

Peter Bill

508
3
12

score 0 · Answer 2 · answered Apr 19 '16 at 19:04

0

update your sql command text and make it smarter. for ex)

declare @p_user_name varchar(10) = 'test'

IF(NOT EXISTS(SELECT 1 FROM Users Where userName = @p_user_name))
BEGIN

    INSERT INTO Users(name)
    VALUES (@p_user_name)
END

answered Apr 19 '16 at 19:04

dfdsfdsfsdf

653
3
7

this would just idle in case a login name is already taken. OP will need to provide feedback. – Cee McSharpface Apr 19 '16 at 19:10
sorry. I was lazy in reading and writing the entire solution. the point is that you can use TSQL to implement the logic. – dfdsfdsfsdf Apr 19 '16 at 19:21

score 0 · Answer 3 · answered Apr 19 '16 at 19:05

0

You have two options:

1-Get the Count of registers with the Username equal to your user's name:

Query: "SELECT COUNT(*) FROM [dbo].[Cont1] WHERE Username=@Username"

If the result is bigger than 0 then the user already eists.

2-Create an unique index on the Username field (that's the recommended approach) and just try to insert, if the username is already used it will cause an exception about duplicate key.

answered Apr 19 '16 at 19:05

Gusman

14,905
2
34
50

you never want to count(*). do count(1). – dfdsfdsfsdf Apr 19 '16 at 19:07
@KMC count(1)? that will always return 1, no? count() takes as input the field name, but that supplies a constant... – Gusman Apr 19 '16 at 19:08
count(1) won't hit the index. if you use *, the optimizer needs to resolve what * is which is all the columns. – dfdsfdsfsdf Apr 19 '16 at 19:17
@KMC then it will be slower, no? sorry but I can't find references about count(1) on sql, is this an only transact-sql for sql server functionality? I'm really curious about it :) – Gusman Apr 19 '16 at 19:19
that would have to be a poor optimizer then. is there any evidence that this really has an impact on execution plan in recent RDBMS? we used `COUNT(*)` by the thousands, in several products, and it never occurred to anybody on the team that this would come at a price. – Cee McSharpface Apr 19 '16 at 19:26
hmm i think you're right. There's no difference based on the execution plan. I thought the optimizer needs to resolve what * is but it didn't. – dfdsfdsfsdf Apr 19 '16 at 19:29

How to check if I have the username in database C#?

3 Answers3