How can check if ExecuteNonQuery was successful?

Question

how can check if executenonquery successful c# in command line//need check if nonaquery true ? almost return the value -1 when make execute

        using (SqlConnection con = new SqlConnection(constr))
        {
            con.Open();

            using (SqlCommand scm = new SqlCommand())
            {
                scm.Connection = con;
                scm.CommandText = "select [BOOk_ID],[Member_id] from [borrow] where BOOk_ID="+textBox19.Text  +" AND  Member_id ="+textBox18.Text  +"";


                int h = scm.ExecuteNonQuery();
                 //need check if nonaquery true
                 if( )
                {
                    using (SqlCommand scm2 = new SqlCommand())
                    {
                        scm2.Connection = con;
                        scm2.CommandText = "delete from Borrow where  BOOk_ID=" + textBox19.Text + " AND  Member_id =" + textBox18.Text + "";
                        scm2.ExecuteNonQuery();
                    }
        }

If 'successful' means 'no exception', then you know it was successful when the execution reaches the `if`. If 'successful' means something else, then define that else and check if it's true. Also please meet [Bobby Tables](http://stackoverflow.com/q/332365/11683). — GSerg, May 10 '14 at 23:18
need to check if the execution success ..to execute another command such as show in above code — user3420275, May 10 '14 at 23:22
As I have already said, if 'successful' for you means 'did not generate exception', then you know it was successful when the execution reaches the `if`. If 'successful' means something else, then define that 'else' and check if it's true. — GSerg, May 10 '14 at 23:25

score 0 · Accepted Answer · answered May 10 '14 at 23:25

ExecuteNonQuery is used for a query that updates, inserts, deletes or executes some management command, not for a query that retrieves one or more records. In your case, you use a SELECT statement to retrieve a record, so you should use ExecuteReader or use an SqlDataAdapter to fill a DataSet or DataTable.

When using an ExecuteReader, you check the property HasRows and then try to read your records with the Read method, with an SqlDataAdapter you simply count the number of rows present in your tables.

using (SqlConnection con = new SqlConnection(constr))
{
    con.Open();
    using (SqlCommand scm = new SqlCommand())
    {
        scm.Connection = con;
        scm.CommandText = @"select [BOOk_ID],[Member_id] from [borrow] where 
                            BOOk_ID=@bkid AND Member_id =@mbid";
        scm.Parameters.AddWithValue("@bkid", textBox19.Text);
        scm.Parameters.AddWithValue("@mbid", textBox18.Text);
        using(SqlDataReader reader = scm.ExecuteReader())
        {
            if(reader.HasRows())
            {
               reader.Read();
               using (SqlCommand scm2 = new SqlCommand())
               {
                   scm2.Connection = con;
                   scm2.CommandText = @"delete from Borrow where
                                      BOOk_ID=@bkid AND Member_id =@mbid";
                   scm2.Parameters.AddWithValue("@bkid", textBox19.Text);
                   scm2.Parameters.AddWithValue("@mbid", textBox18.Text);
                   scm2.ExecuteNonQuery();
               }
           }
        }
    }
}

Notice that I have changed your query to a parameterized query to avoid Sql Injections and parsing problem.

However looking at your code, I think you don't need at all to call the first query to check if the book exists and then delete it. You could simply delete it immediately. If the record doesn't exist your database cannot delete anything.

A last note. When a connection is used by a SqlDataReader it cannot be used to do other works on the database. You need to add to your connection string the MultipleActiveResultSets=True

perfect answer -thanks more ,but connection of execute reader how can close it befor execute another command ? this will make exception — user3420275, May 11 '14 at 00:44
You could close the SqlDataReader before executing the command (`reader.Close()`), you can change your connection string to contain also `;MultipleActiveResultSets=True;` or simply forget about the reader and execute directly the Delete command — Steve, May 11 '14 at 07:50

How can check if ExecuteNonQuery was successful?

1 Answers1