How to check if SQL database already has the entered Username?

Question

I've written this registration form which adds data to my SQL Server database. What I want is an exception when the user enters a username that is already in the database.

protected void Button1_Click(object sender, EventArgs e)
{
        try
        {
            SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn2.Open();    

            string CheckUser = "select Username from UserData where Username like @Username";

            SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
            com2.Parameters.AddWithValue("@Username", "'%"+ UsernameTextBox.Text +"%'");

            com2.ExecuteNonQuery();

            int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());
            conn2.Close();

            if (IsMatch == 0)
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
                conn.Open();

                string InsertQuery = "insert into UserData (Username, Email, Password, Country) values (@Username, @Email, @Password, @Country)";

                SqlCommand com = new SqlCommand(InsertQuery, conn);
                com.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                com.Parameters.AddWithValue("@Email", EmailTextBox.Text);
                com.Parameters.AddWithValue("@Password", PasswordTextBox.Text);
                com.Parameters.AddWithValue("@Country", CountryDropDownList.SelectedItem.ToString());

                com.ExecuteNonQuery();

                Response.Redirect("Manager.aspx");

                conn.Close();
            }
            else
            {
                Response.Write("User Already Exists!");
            }               
        }
        catch (Exception ex)
        {
            Response.Write(Convert.ToString(ex));
        }
}

When I run it, I get an exception on the following line:

int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());

Answer 1

Blam's second solution works, but the IsMatch can be simplified a bit by casting to int instead of going to string and parsing.

This should also be handled at the database level. Set a primary key on your username column:

ALTER TABLE UserData ADD CONSTRAINT
PK_UserData PRIMARY KEY CLUSTERED (Username) 

If you do it this way, then you don't even have to check for duplicates explicitly, you can just try to create the user and handle the exception if it fails:

        try
        {
            using (var conn = new SqlConnection((ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString)))
            {
                conn.Open();

#if DOUBLE_CHECK
                string CheckUser = "select count(*) from UserData where Username = @Username";
                SqlCommand com2 = new SqlCommand(CheckUser, conn);
                com2.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                if ((int)com2.ExecuteScalar() > 0)
                {
                    Response.Write("User already exists");
                    return;
                }
#endif                  
                string InsertQuerry = "insert into UserData (Username,Email,Password,Country) values (@Username,@Email,@Password,@Country)";
                SqlCommand com = new SqlCommand(InsertQuerry, conn);
                com.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                com.Parameters.AddWithValue("@Email", EmailTextBox.Text);
                com.Parameters.AddWithValue("@Password", PasswordTextBox.Text);
                com.Parameters.AddWithValue("@Country", CountryDropDownList.SelectedItem.ToString());
                com.ExecuteNonQuery();
                Response.Redirect("Manager.aspx");
            }
        }
        catch (SqlException se)
        {
            if (se.Errors.OfType<SqlError>().Any(e => e.Number == 2627))
            {
                Response.Write("User already exists");
            }
            else
            {
                Response.Write(se.ToString());                  
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }

If you handle the exception this way, the #if DOUBLE_CHECK section is redundant and can be removed. An attempt to add duplicate name will cause a SQL error and exception, and this will detect and handle the "duplicate key" error.

Two unrelated notes on your code:

1. Response.Redirect() will abort the current thread and your conn.Close() will not be called. Use a using() to ensure it's called.
2. Storing a password in the database as plain text is a disaster waiting to happen. PLEASE take a look at Best way to store password in database for some ideas about how to do this correctly

Answer 2

This returns 0 or 1. This should fix your issue. Looks like you need to return an int type. Or you could change it to bool if you want. Either way, this sql statement should help! :)

select
isnull(convert(bit,(select top 1 case 
                    when username != '' then 1
                    else 0 end 
from UserData
where username like @Username)),0)

Answer 3

That won't return an integer

string CheckUser = "select count(*) from UserData where Username like @Username";
SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
com2.Parameters.AddWithValue("@Username", "'%"+ UsernameTextBox.Text +"%'");
int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());

And you don't need to use two different connections.
Just use one and close it in a Finally.

string CheckUser = "select count(*) from UserData where Username = @Username";
SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
com2.Parameters.AddWithValue("@Username", UsernameTextBox.Text );
int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());