using ExecuteReader instead of ExecuteNonQuery

Question

MySqlCommand cmd = new MySqlCommand
                    (@"INSERT INTO Table(field) VALUES('somevalue');" +
                    "SELECT * FROM table",cn);

This works fine to me since I'm only passing those statements to my MySQL server.

Is it OK to use ExecuteReader() when inserting & updating & deleting?

I usually use ExecuteNonQuery() on those.

Answer 1

You're just fine bundling the extra INSERT in along with your SELECT with ExecuteReader(). This is okay.

I do see two things that are potentially not okay... nothing in the code itself, but what you showed is simplified, and the simplified code hints at some potential poor practices:

The first not okay is that your code looks like it might be using string concatenation to substitute values into your query. Something like this:

MySqlCommand cmd = new MySqlCommand
                (@"INSERT INTO Table(field) VALUES('" + somevariable + "');" +
                "SELECT * FROM table",cn);

That is a huge problem, as it opens a gaping security hole in your application. Instead, you need to use query parameters, so the code looks more like this:

MySqlCommand cmd = new MySqlCommand
                (@"INSERT INTO Table(field) VALUES(@somevalue);" +
                "SELECT * FROM table",cn);
cmd.Parameters.Add("@somevalue", SqlDbType.VarChar, 50).Value = somevariable;

The other potential problem is that your command and, more importantly, your connection, should be wrapped in a try/finally block (or for preference a using block), like this:

using (var cn = new MySqlConnection("..."))
using (var cmd = new MySqlCommand("@INSERT INTO Table(field).... ", cn))
{
    cmd.Parameters.Add(...);
    cn.Open();
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            //...
        }
    }
}

Answer 2

ExecuteReader

Do not use: when database query is going to provide for sure exactly 1 record.

Use: when database query is going to provide a set of records. It may be search or report.

ExecuteNonQuery

Use: when we are talking about a single database record - in Update, Insert, Delete and Get by Id.

ExecuteScalar

Do not use: when database query returns a single value and this value can be defined as parameter in T-SQL. ExecuteNonQuery with output parameter(s) is always preferred in this case since it is more flexible, tomorrow there will be 2 values therefore having ExecuteNonQuery we do not need to change method signatures.

Use: when database query returns a single value and this value cannot be defined as output parameter, because of T-SQL type limitation for variables.

Reference

Answer 3

If, for the sake of structuring, you have to use ExecuteReader then go ahead, just remember to dispose of (Close) the reader afterwards. It has a little effect on the program, but it is most likely limited to the .Net part, mostly just the memory consumption of an additional class instance. In fact, certain ADO.Net providers use ExecuteReader behind ExecuteNonQuery internally.

The bigger problem here is the SELECT * FROM table part after your insert query. Even though you're not using the reader to read the data, it still consumes more resources to execute a select query. Here, there should be no difference on the MySQL side whether you use ExecuteReader or ExecuteNonQuery (unless you read from the reader, of course)