MySQL Check if User exist C#

Question

Code:

 static public void InsertUser(string userName, DataGridView DadataGridView1)
        {
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                MySqlCommand check_User_Name = new MySqlCommand("SELECT * FROM IP WHERE(Username = @username)", connection);
                check_User_Name.Parameters.AddWithValue("@username", userName);
                int UserExist = (int)check_User_Name.ExecuteScalar();

                if (UserExist > 0)
                {
                    MessageBox.Show("User Exist");
                }
                else
                {
                    MySqlCommand commandInsert = new MySqlCommand("INSERT INTO IP(Username) VALUES(@Username)", connection);
                    commandInsert.Parameters.AddWithValue("@username", userName);
                    commandInsert.ExecuteNonQuery();
                    commandInsert.Parameters.Clear();
                    MessageBox.Show("User Inserted sucessfuly");
                }

            }
            catch (MySqlException exception)
            {
                MessageBox.Show(exception.ToString());

            }
            finally
            {
                connection.Close();

            }

I want check duplicates in my database, I have problem on

int UserExist = (int)check_User_Name.ExecuteScalar();

System.NullReferenceException was unhandled Message=Object reference not set to an instance of an object.

Looks like your `ExecuteScalar` returns `null`. http://stackoverflow.com/questions/1999020/handling-executescalar-when-no-results-are-returned — Soner Gönül, Sep 08 '15 at 10:58
Also [What is a `NullReferenceException` and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Soner Gönül, Sep 08 '15 at 10:59

sujith karivelil · Answer 1 · 2015-09-08T11:17:27.467

0

use SELECT count(*) FROM IP WHERE(Username = @username) instead for SELECT * FROM IP WHERE(Username = @username) because .ExecuteScalar() is capable of handling only a single value. * will fetch all column values. you cannot use .ExecuteScalar() in such scenarios.

So your code will be like:

 MySqlCommand check_User_Name = new MySqlCommand("SELECT count(*) FROM IP WHERE Username = @username", connection);
                check_User_Name.Parameters.AddWithValue("@username", userName);
                int UserExist = (int)check_User_Name.ExecuteScalar();    
                if (UserExist > 0)
                {
                    MessageBox.Show("User Exist");
                } 
                else
                  {
                //execute insert here
                  }

edited Sep 08 '15 at 11:17

answered Sep 08 '15 at 11:02

sujith karivelil

26,861
6
46
76

Ok, my problem is I want check all colums, how I can check all column s values ? What use to replace ExecuteScalar? – marko121 Sep 08 '15 at 11:08
Why are you checking username in all columns? or you have to check in each row? have you tried my suggestion? – sujith karivelil Sep 08 '15 at 11:10
Yes, I try your sugesstion it check only first insert, and also give System.NullReferenceException, I want check all inserts. – marko121 Sep 08 '15 at 11:14

score 0 · Answer 2 · edited May 23 '17 at 11:59

Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data (from Wiki).

Thus, you should ensure that username in your database is unique by adding a unique constraint on your table for that column, for example:

ALTER TABLE MyUsers ADD CONSTRAINT ux_username UNIQUE (username)

This way you can be assured that whichever application tries to insert duplicate usernames will fail, otherwise each consumer of your database need to perform the check to see if the username hasn't been added or not. Also you can run into race conditions where after your check thinks that the username doesn't exist, another process may have added the username in the meantime and your insert will add a duplicate record.

So rather use the database capabilities to ensure data integrity.

You can then just perform the insert and trap the exception, see:UNIQUE constraint vs checking before INSERT

MySQL Check if User exist C#

2 Answers2