Check a field is in sql database using c#

Question

i want to check weather a user is in my database (checking with the id). i am using the following code. It is working. i just want to know ,is this the right way or is there any other method for doing this better(like using COUNT(*) or any other query). I am doing my project in MVC4

  public bool CheckUser(int mem_id)
    {
        bool flag = false;
        using (SqlConnection con = new SqlConnection(Config.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT Id FROM Mem_Basic WHERE Id="+ mem_id +"", con))
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    flag = true;
                }
            }
        }
        return flag;
    }

http://stackoverflow.com/questions/4253960/sql-how-to-properly-check-if-a-record-exists — rags, Aug 27 '13 at 04:41

score 4 · Answer 1 · answered Aug 27 '13 at 04:41

if you want a single value you can use ExecuteSclar function. and Use parametrized queries to avoid sql injection.

using (SqlConnection con = new SqlConnection(Config.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT 1 FROM Mem_Basic WHERE Id=@id", con))
                {

                    cmd.Parameters.AddWithValue("@ID", yourIDValue);
                    con.Open();
                    var found=(int)cmd.ExecuteScalar(); //1 means found

                }

            }

score 2 · Accepted Answer · answered Aug 27 '13 at 04:42

Yes, your code will be simpler if you use a SELECT COUNT(*) query and assign the single value returned to an int instead of using the reader syntax.

Try this:

public bool CheckUser(int mem_id)
{
    bool flag = false;
    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Mem_Basic WHERE Id="+ mem_id +""", con))
        {
            con.Open();
            int count = (int) cmd.ExecuteScalar();
            if(count > 0)
            {
                flag = true;
            }
        }
    }
    return flag;
}

Dylan Perales · Answer 3 · 2013-08-28T06:01:58.370

1

You could also do something similar to yours but instead just check for null.

public bool CheckUser(int mem_id)
{
    bool flag = false;
    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Id FROM Mem_Basic WHERE Id="+ mem_id +"", con))
        {
            con.Open();
            if (cmd.ExecuteScalar() != null)
            {
                flag = true;
            }
         }
      }
 }

edited Aug 28 '13 at 06:01

answered Aug 27 '13 at 04:45

Dylan Perales

11
4

a closing brace for if condition is missing – Nisha Aug 27 '13 at 05:38

score 1 · Answer 4 · edited May 23 '17 at 11:49

1

Instead of using ExecuteReader you can use ExecuteScalar. In my opinion your code will be more clean. See more on MSDN

About your sql query: you can check performance in SQL query analyzer in Managment Studio. See more Where is the Query Analyzer in SQL Server Management Studio 2008 R2? . But in 99% it is optimal.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 27 '13 at 04:46

Piotr Stapp

19,392
11
68
116

Check a field is in sql database using c#

4 Answers4