Best way to check if Single Item exists in MS Sql with C# (sql command )

Question

I made a method that will execute query, I only need to know if it returns true or false

   public bool CheckData(string sql)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["FabFlabs"].ToString());
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);

        cmd.ExecuteNonQuery();
        con.Close();

        bool setYN =?            

        return setYN ;
    }

The query is something like select email from Admin where email = '' Is there a better way to structure the query?

score 5 · Accepted Answer · answered May 15 '14 at 08:18

The thing I notice currently is that the sql is clearly not parameterized. That to me is a huge alarm bell, and should be fixed immediately. As a secondary point, you aren't using correctly, and you are using ExecuteNonQuery when your query is clearly... a query.

A better check, then, would be:

public bool CheckData(string sql)
{
    using(var con = CreateConnection())
    using(var cmd = new SqlCommand(sql, con))
    {
        // ADD PARAMETERS
        con.Open();
        return cmd.ExecuteScalar() != null; // checks for a row
    }
}

However; passing in the parameters is not easy. I strongly suggest a tool like "dapper" in place (i.e. remove completely) your CheckData method. Consider:

bool haveEmail;
string email = /* TODO; for example, "admin@example.com" */
using(var conn = CreateConnection())
{
    haveEmail = conn.Query<int>(
        "select top 1 1 from Admin where email=@email",
        new { email }).Any(); // regular LINQ over IEnumerable<T>
}

Simple, clean, fully parameterized, efficient.

score 2 · Answer 2 · edited May 23 '17 at 12:22

For SELECT statement, ExecuteNonQuery just executes your command. It doesn't do anything more.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1.

You can change your query little bit with COUNT() and use ExecuteScalar method.

using(SqlConnection con = new SqlConnection(ConString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "select COUNT(email) from Admin where email = @email";
   cmd.Parameters.AddWithValue("@email", string.Empty);
   con.Open();
   int count = (int)cmd.ExecuteScalar();

   bool b; 
   if(count > 0)
      b = true;
   else
      b = false;
}

I liked Marc's solution as well. Since ExecuteScalar returns object, you can easily check it's null or not with;

cmd.ExecuteScalar() != null

I agree with the comments, using TOP 1 or EXIST would be simpler instead of COUNT.

Personally I prefer `ExecuteScalar` with `SELECT TOP 1 NULL ...` and then check for `null` vs `DBNull.Value`. That saves SQL Server actually counting. — ta.speot.is, May 15 '14 at 08:22
COUNT(email) could be very ugly solution: what if there're many, say 100000 emals = '' in the Admin table? Often (esp. if there're no index on email) is chaper just to open cursor and try to read from it — Dmitry Bychenko, May 15 '14 at 08:25
@ta.speot.is Yeah, your and Dmitry Bychenko's suggestions seems better and simpler. Added to my answer. Thank you. — Soner Gönül, May 15 '14 at 08:31

score 1 · Answer 3 · edited May 23 '17 at 12:22

1

try using select exists with your query within the parentheses.

SELECT EXISTS(your query)

ref: Best way to test if a row exists in a MySQL table

edited May 23 '17 at 12:22

Community

1
1

answered May 15 '14 at 08:16

Mana

1,925
6
39
55

score 1 · Answer 4 · answered May 15 '14 at 08:19

Here is my approach to check if data exists

            int q = 0;
            bool NewCity = true; 
            MainWindow.cmdSel = new SqlCommand("SELECT COUNT(*) FROM DBO.Cities t WHERE t.Name=@Name", MainWindow.conn);
            MainWindow.cmdSel.Parameters.Add("@Name", SqlDbType.NVarChar);
            MainWindow.cmdSel.Parameters["@Name"].Value = Name;
            try
            {
                q = (int)MainWindow.cmdSel.ExecuteScalar();
                if (q > 0)
                {
                    NewCity = false;
                    MessageBox.Show("City exists", "Warning");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Best way to check if Single Item exists in MS Sql with C# (sql command )

4 Answers4