Jump to content

How to count the number of records in sql server using C#

i want to see if the number that the user input is there in the database by counting it and if the count is 0 i want to set a label visibility to true.i have tried this code but it didnt work:

 

private void textBox5_KeyDown_1(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                textBox6.Focus();
                e.Handled = true;

                //The code delow is for selecting the module name from the module table and displaying it on textbox2
                cn.ConnectionString = @"Data Source=Dell-PC\SQLEXPRESS;Initial Catalog=employee_db;Integrated Security=True";
                cmd.Connection = cn;

                
                string xy = textBox1.Text;

                cn.Open();
                SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Employees] WHERE ([emp_ID] = @xy)", cn);
                check_User_Name.Parameters.AddWithValue("@x", textBox1.Text);
                int UserExist = (int)check_User_Name.ExecuteScalar();

                if (UserExist > 0)
                {
                    label4.Visible = true;
                }
                else
                {
                    MessageBox.Show("success");
                }
                cn.Close();

}

 

Link to comment
Share on other sites

Link to post
Share on other sites

You use parameter @xy in your query and then add parameter @x to the command.

SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Employees] WHERE ([emp_ID] = @xy)", cn);
check_User_Name.Parameters.AddWithValue("@x", textBox1.Text);

 

1 hour ago, Shammikit said:

if the count is 0 i want to set a label visibility to true

1

You also mention this but use the greater than operator instead of the equals operator

if (UserExist > 0)

 

Link to comment
Share on other sites

Link to post
Share on other sites

@madknight3 covered it, but I'd also use a try/catch structure for error management:

using (SqlConnection cn = new SqlConnection(ConnectionString))
{
  	try
  	{
     	 cn.Open();
      	 //SQL stuff
  	}
  	catch (SqlCeException sql_ex)
  	{
      	MessageBox.Show("SQL database error: " + sql_ex.Message);
  	}
} 
//cn close automatically when exiting

 

Also I like to use Datasets but that's a bit overkill for your use case. But it's useful if you want to do other things later (like getting user parameters):

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM [Employees] WHERE ([emp_ID] = @xy)", cn);
check_User_Name.Parameters.AddWithValue("@xy", textBox1.Text);

SqlCeDataAdapter adapter = new SqlCeDataAdapter(check_User_Name);
DataSet ds = new DataSet();
adapter.Fill(ds);


// If unique return value, valid login
int count = ds.Tables[0].Rows.Count;
if (count == 1)
{
    //success
}

 

[Insert smart comment here]

Link to comment
Share on other sites

Link to post
Share on other sites

42 minutes ago, jldjul said:

Also I like to use Datasets but that's a bit overkill for your use case. But it's useful if you want to do other things later (like getting user parameters):


SqlCommand check_User_Name = new SqlCommand("SELECT * FROM [Employees] WHERE ([emp_ID] = @xy)", cn);
check_User_Name.Parameters.AddWithValue("@xy", textBox1.Text);

SqlCeDataAdapter adapter = new SqlCeDataAdapter(check_User_Name);
DataSet ds = new DataSet();
adapter.Fill(ds);


// If unique return value, valid login
int count = ds.Tables[0].Rows.Count;
if (count == 1)
{
    //success
}

 

 

Just in case OP gets confused, for their current query where they are getting the count, and for any query that returns a single value, ExecuteScalar is the best option. So they are correct in that regard. It's overkill to use anything else.

 

Also, note that there are pros and cons to the different readers (performance, memory usage, convenience, etc). In some cases, you'll want to use SqlCommand/SqlDataReader over SqlDataAdapter and vice versa.

 

Here are some references

StackOverflow: What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

StackOverflow: When to use ExecuteScalar,ExecuteReader,ExecuteNonQuery?

StackOverflow: SqlCommand or SqlDataAdapter?

StackOverflow: SqlDataAdapter vs SqlDataReader

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×