Check if a record exists in the database

Question

I am using these lines of code to check if the record exists or not.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);

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

But I am getting an error:

Object reference not set to an instance of an object.

I want to do:

if (UserExist > 0)
    // Update record

else

    // Insert record

Answer 1

I was asking myself the same question, and I found no clear answers, so I created a simple test.

I tried to add 100 rows with duplicate primary keys and measured the time needed to process it. I am using SQL Server 2014 Developer and Entity Framework 6.1.3 with a custom repository.

Dim newE As New Employee With {.Name = "e"}
For index = 1 To 100
  Dim e = employees.Select(Function(item) item.Name = "e").FirstOrDefault()
  If e Is Nothing Then
    employees.Insert(newE)
  End If
Next  

2.1 seconds

Dim newE As New Employee With {.Name = "e"}
For index = 1 To 100
  Try
    employees.Insert(newE)
  Catch ex As Exception
  End Try
Next  

3.1 seconds

Answer 2

ExecuteScalar returns the first column of the first row. Other columns or rows are ignored. It looks like your first column of the first row is null, and that's why you get NullReferenceException when you try to use the ExecuteScalar method.

From MSDN;

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

You might need to use COUNT in your statement instead which returns the number of rows affected...

Using parameterized queries is always a good practise. It prevents SQL Injection attacks.

And Table is a reserved keyword in T-SQL. You should use it with square brackets, like [Table] also.

As a final suggestion, use the using statement for dispose your SqlConnection and SqlCommand:

SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Table] WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
int UserExist = (int)check_User_Name.ExecuteScalar();

if(UserExist > 0)
{
   //Username exist
}
else
{
   //Username doesn't exist.
}

Answer 3

I would use the "count" for having always an integer as a result

SqlCommand check_User_Name = new SqlCommand("SELECT count([user]) FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') " , conn);

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

if (UserExist == 1) //anything different from 1 should be wrong
{
  //Username Exist
}

Answer 4

MySqlCommand cmd = new MySqlCommand("select * from table where user = '" + user.Text + "'", con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds1 = new DataSet();
da.Fill(ds1);
int i = ds1.Tables[0].Rows.Count;
if (i > 0) {
    // Exist
}
else {
    // Add 
}

Answer 5

Use the method Int.Parse() instead. It will work.

Answer 6

Use try catch:

try
{
    SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);

    int UserExist = (int)check_User_Name.ExecuteScalar();
    // Update query
}
catch
{
    // Insert query
}