CODEWITHSUNDEEP
c#sqlsql-serverasp.net-mvc-4
neel
neel

Reputation: 5293

Check a field is in sql database using c#

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

Upvotes: 0

Views: 2755

Answers (4)

Dylan Perales
Dylan Perales

Reputation: 11

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

Upvotes: 1

Piotr Stapp
Piotr Stapp

Reputation: 19830

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.

Upvotes: 1

Karl Anderson
Karl Anderson

Reputation: 34846

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

Upvotes: 2

Ravi Gadag
Ravi Gadag

Reputation: 15851

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

                }

            }

Upvotes: 4

Related Questions