[c#] Check if a record exists in the database

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

sqlConnection.Open();
using (var sqlCommand = new SqlCommand("SELECT COUNT(*) FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "'", sqlConnection))
{

    SqlDataReader reader = sqlCommand.ExecuteReader();
    if (reader.HasRows)
    {
        lblMessage.Text ="Record Already Exists.";

    }
    else
    {
        lblMessage.Text ="Record Not Exists.";
    }

    reader.Close();
    reader.Dispose();
}

sqlConnection.Close();

sda = new SqlCeDataAdapter("SELECT COUNT(regNumber) AS i FROM  tblAttendance",con);
sda.Fill(dt);

string i = dt.Rows[0]["i"].ToString();
int bar = Convert.ToInt32(i);

if (bar >= 1){

    dt.Clear();
    MetroFramework.MetroMessageBox.Show(this, "something");
}
else if(bar <= 0) {

    dt.Clear();
    MetroFramework.MetroMessageBox.Show(this, "empty");
}

try this

 public static bool CheckUserData(string phone, string config)
    {
        string sql = @"SELECT * FROM AspNetUsers WHERE PhoneNumber = @PhoneNumber";
        using (SqlConnection conn = new SqlConnection(config)
            )
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@PhoneNumber", phone);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.HasRows)
                {
                    return true;  // data exist
                }
                else
                {
                    return false; //data not exist
                }
            }
        }
    }

The ExecuteScalar method should be used when you are really sure your query returns only one value like below:

SELECT ID FROM USERS WHERE USERNAME = 'SOMENAME'

If you want the whole row then the below code should more appropriate.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
SqlDataReader reader = check_User_Name.ExecuteReader();
if(reader.HasRows)
{
   //User Exists
}
else
{
   //User NOT Exists
}

You can write as follows:

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);
if (check_User_Name.ExecuteScalar()!=null)
{
    int UserExist = (int)check_User_Name.ExecuteScalar();
    if (UserExist > 0)
    {
        //Username Exist
    }
}

I had a requirement to register user. In that case I need to check whether that username is already present in the database or not. I have tried the below in C# windows form application(EntityFramework) and it worked.

 var result = incomeExpenseManagementDB.Users.FirstOrDefault(x => x.userName == registerUserView.uNameText);
  if (result == null) {
      register.registerUser(registerUserView.fnameText, registerUserView.lnameText, registerUserView.eMailText, registerUserView.mobileText, registerUserView.bDateText, registerUserView.uNameText, registerUserView.pWordText);
  } else {
      MessageBox.Show("User Alreay Exist. Try with Different Username");
  }

protected void btnsubmit_Click(object sender, EventArgs e)
{

        string s = @"SELECT * FROM tbl1 WHERE CodNo = @CodNo";
    SqlCommand cmd1 = new SqlCommand(s, con);
    cmd1.Parameters.AddWithValue("@CodNo", txtid.Text);
    con.Open();
    int records = (int)cmd1.ExecuteScalar();

    if (records > 0)
    {

        Response.Write("<script>alert('Record not Exist')</script>");

    }
    else
    {
        Response.Write("<script>alert('Record Exist')</script>"); 
     }
  }
        private void  insert_data()
{

        SqlCommand comm = new SqlCommand("Insert into tbl1(CodNo,name,lname,fname,gname,EmailID,PhonNo,gender,image,province,district,village,address,phonNo2,DateOfBirth,school,YearOfGraduation,exlanguage,province2,district2,village2,PlaceOfBirth,NIDnumber,IDchapter,IDpage,IDRecordNumber,NIDCard,Kankur1Year,Kankur1ID,Kankur1Mark,Kankur2Year,Kankur2ID,Kankur2Mark,Kankur3Year,Kankur3ID,Kankur3Mark) values(@CodNo,N'" + txtname.Text.ToString() + "',N'" + txtlname.Text.ToString() + "',N'" + txtfname.Text.ToString() + "',N'" + txtgname.Text.ToString() + "',N'" + txtemail.Text.ToString() + "','" + txtphonnumber.Text.ToString() + "',N'" + ddlgender.Text.ToString() + "',@image,N'" + txtprovince.Text.ToString() + "',N'" + txtdistrict.Text.ToString() + "',N'" + txtvillage.Text.ToString() + "',N'" + txtaddress.Value.ToString() + "','" + txtphonNo2.Text.ToString() + "',N'" + txtdbo.Text.ToString() + "',N'" + txtschool.Text.ToString() + "','" + txtgraduate.Text.ToString() + "',N'" + txtexlanguage.Text.ToString() + "',N'" + txtprovince1.Text.ToString() + "',N'" + txtdistrict1.Text.ToString() + "',N'" + txtvillage1.Text.ToString() + "',N'" + txtpbirth.Text.ToString() + "','" + txtNIDnumber.Text.ToString() + "','" + txtidchapter.Text.ToString() + "', '" + txtidpage.Text.ToString() + "','" + txtrecordNo.Text.ToString() + "',@NIDCard,'" + txtkankuryear1.Text.ToString() + "','" + txtkankurid1.Text.ToString() + "','" + txtkankurscore1.Text.ToString() + "','" + txtkankuryear2.Text.ToString() + "','" + txtkankurid2.Text.ToString() + "','" + txtkankurscore2.Text.ToString() + "','" + txtkankuryear3.Text.ToString() + "','" + txtkankurid3.Text.ToString() + "','" + txtkankurscore3.Text.ToString() + "')", con);

        flpimage.SaveAs(Server.MapPath("~/File/") + flpimage.FileName);
        string img = @"~/File/" + flpimage.FileName;
        flpnidcard.SaveAs(Server.MapPath("~/Tazkiera/") + flpnidcard.FileName);
        string img1 = @"~/Tazkiera/" + flpnidcard.FileName;

        comm.Parameters.AddWithValue("CodNo", Convert.ToInt32(txtid.Text));
        comm.Parameters.AddWithValue("image", flpimage.FileName);
        comm.Parameters.AddWithValue("NIDCard", flpnidcard.FileName);

        comm.ExecuteNonQuery();
        con.Close();

        Response.Redirect("~/SecondPage.aspx");
        //Response.Write("<script>alert('Record Inserted')</script>");

        }
    }

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

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
}

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

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
}

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 
}