[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

This question is related to c# winforms sqlcommand

The answer is


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
}

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
}

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");
  }

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
                }
            }
        }
    }

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
    }
}

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");
}

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


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


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();

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 
}

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
}

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>");

        }
    }

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to winforms

How to set combobox default value? Get the cell value of a GridView row Getting the first and last day of a month, using a given DateTime object Check if a record exists in the database Delete a row in DataGridView Control in VB.NET How to make picturebox transparent? Set default format of datetimepicker as dd-MM-yyyy Changing datagridview cell color based on condition C# Inserting Data from a form into an access Database How to use ConfigurationManager

Examples related to sqlcommand

Check if a record exists in the database How to run multiple SQL commands in a single SQL connection? Insert into C# with SQLCommand How to export and import a .sql file from command line with options? How to clear mysql screen console in windows? Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated How to pass table value parameters to stored procedure from .net code