CODEWITHSUNDEEP
c#sql
Jack
Jack

Reputation: 526

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

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?

Upvotes: 0

Views: 1122

Answers (4)

Soner Gönül
Soner Gönül

Reputation: 98840

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.

Upvotes: 2

Firdavs Kurbonov
Firdavs Kurbonov

Reputation: 1252

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

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063609

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, "[email protected]" */
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.

Upvotes: 5

Mana
Mana

Reputation: 1929

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

Upvotes: 1

Related Questions