CODEWITHSUNDEEP
c#mysql
dotancohen
dotancohen

Reputation: 31471

How to know if a query succeeded?

How can I know if the following INSERT query succeeded?

MySqlConnection connection = new MySqlConnection(ConnectionString);
connection.Open();
MySqlCommand command = new MySqlCommand("INSERT INTO People (PersonName, dateAdded) VALUES (?name, NOW())", connection);
command.Parameters.Add(new MySqlParameter("name", Name));
MySqlDataReader Reader = command.ExecuteReader();

I tried running Reader.Read() but only an empty string is returned. Thanks.

Upvotes: 3

Views: 6858

Answers (4)

xomalli
xomalli

Reputation: 96

Only use the SqlDataReader when you're queryng the data base, the SqlDataReader is a fast forward-only pointer, so only is recommended for Querying, use the ExecuteNonQuery() method of the SqlCommand, for example as the next code:

using (SqlConnection conn = new SqlConnection("[QueryString]")) {
    using (SqlCommand cmd = new SqlCommand("SQL Command",conn)) {
        //here you return the number of affected rows
        int a = cmd.ExecuteNonQuery();                    
    }
}

Upvotes: 3

user7116
user7116

Reputation: 64068

You instead use ExecuteNonQuery:

int affectedRows = command.ExecuteNonQuery();
if (affectedRows <= 0)
{
    // Houston, we have a problem.
}

Keep in mind if you have triggers on the data, it may not reflect the actual number of rows you attempted to INSERT:

You can use ExecuteNonQuery to perform any type of database operation, however any resultsets returned will not be available. Any output parameters used in calling a stored procedure will be populated with data and can be retrieved after execution is complete. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

In general, however, you will receive a MySqlException if your query encounters an error.

Upvotes: 9

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

An exception will be thrown if it fails. The exception object will typically contain information about what went wrong (e.g. "primary key violation" etc...).

Also, use ExecuteNonQuery for INSERT, UPDATE or DELETE. The returned value will contain the number of affected rows.

Upvotes: 2

Andre Loker
Andre Loker

Reputation: 8408

Don't use ExecuteReader if you're doing an INSERT, UPDATE or DELETE. Use ExecuteNonQuery instead, it'll return the number of affected rows.

See http://dev.mysql.com/doc/refman/5.6/en/connector-net-tutorials-intro.html#connector-net-tutorials-sql-command

Upvotes: 4

Related Questions