SQLCommand.ExecuteReader() does not restrict to read only statements

Question

So apparently, ExecuteReader is used for read only and ExecuteNonQuery is used for transactions. But for some reason even when I used ExecuteReader I am still able to run write (Insert, Update, Delete) commands (typed in textbox1). Is there something wrong with my code or am I misunderstanding the way ExecuteReader is supposed to work?

//MY CODE

string sqlStatement = textbox1.Text;

System.Data.SqlClient.SqlConnectionStringBuilder builder =
  new System.Data.SqlClient.SqlConnectionStringBuilder();
builder.DataSource = ActiveServer;
builder.IntegratedSecurity = true;

System.Data.SqlClient.SqlConnection Connection = new 
   System.Data.SqlClient.SqlConnection(builder.ConnectionString);
Connection.Open();

System.Data.SqlClient.SqlCommand command = new 
  System.Data.SqlClient.SqlCommand(sqlStatement, Connection);
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

dataGridView1.AutoGenerateColumns = true;

bindingSource1.DataSource = reader;
dataGridView1.DataSource = bindingSource1;

reader.Close();
Connection.Close();

Adam Houldsworth · Accepted Answer · 2010-07-27 21:11:09Z

7

ExecuteReader simply returns a reader that can read rows returned from a SQL procedure - it doesn't stop you from running arbitrary SQL on the way to providing that result set.

Doing inserts / updates / deletes and then immediately returning a result set (so from code looking like a read) is arguably a little odd (read: code smell), and should be reviewed to see if it can be split into distinct actions.

answered Jul 27, 2010 at 21:11

Adam Houldsworth

64.8k11 gold badges161 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Proulx Over a year ago

Is there a way to restrict the type of queries that can be run?

Adam Houldsworth Over a year ago

Not unless you scan the query in code beforehand and throw an exception.

nawfal · Accepted Answer · 2013-02-23 08:46:42Z

Though both executes the sql, ExecuteReader is expected to return records while ExecuteNonQuery number of records affected. Both are different hence. But internally how different they are would depend on vendor specific implementation. You could use ExecuteReader alone for all your db actions because it just worked (until now) but since its not documented its not really the right approach to go for. You could be more clear about your intent with ExecuteNonQuery.

As far as performance is considered I don't think there's a difference at all. I tried with SQLite, MySqlClient, SqlClient, SqlServerCe and VistaDb and saw no noticeable difference to favour either. And they all should use ExecuteReader internally one way or other.

The essentials:

SqlClient:

private int InternalExecuteNonQuery(DbAsyncResult result, string methodName, bool sendToPipe)
{
    if (!this._activeConnection.IsContextConnection)
    {
        if (this.BatchRPCMode || CommandType.Text != this.CommandType || this.GetParameterCount(this._parameters) != 0)
        {
            Bid.Trace("<sc.SqlCommand.ExecuteNonQuery|INFO> %d#, Command executed as RPC.\n", this.ObjectID);
            SqlDataReader sqlDataReader = this.RunExecuteReader(CommandBehavior.Default, RunBehavior.UntilDone, false, methodName, result);
            if (sqlDataReader == null)
            {
                goto IL_E5;
            }
            sqlDataReader.Close();
            goto IL_E5;
        }
    IL_B5:
        this.RunExecuteNonQueryTds(methodName, flag);
    }
    else
    {
        this.RunExecuteNonQuerySmi(sendToPipe);
    }
IL_E5:
    return this._rowsAffected;
}

and

MySqlClient:

public override int ExecuteNonQuery()
{
  int records = -1;

  #if !CF
  // give our interceptors a shot at it first
  if ( connection != null && 
       connection.commandInterceptor != null &&
       connection.commandInterceptor.ExecuteNonQuery(CommandText, ref records))
    return records;
  #endif

  // ok, none of our interceptors handled this so we default
  using (MySqlDataReader reader = ExecuteReader())
  {
    reader.Close();
    return reader.RecordsAffected;
  }
}

As you can see MySqlClient directly calls ExecuteReader while SqlClient does only for certain conditions. Mind you inserts and updates are rarely the bottleneck (its often the selects).

As I said you wouldn't get the number of rows affected with the help of ExecuteReader, so use ExecuteNonQuery better to execute queries. A more direct replacement by ExecuteReader would be of ExecuteScalar which returns the data in first column of first row read.

The essentials:

SqlClient:

override public object ExecuteScalar()
{
    SqlConnection.ExecutePermission.Demand();

    // Reset _pendingCancel upon entry into any Execute - used to synchronize state
    // between entry into Execute* API and the thread obtaining the stateObject. 
    _pendingCancel = false;

    SqlStatistics statistics = null;
    IntPtr hscp;
    Bid.ScopeEnter(out hscp, "<sc.sqlcommand.executescalar|api> %d#", ObjectID);
    try
    {
        statistics = SqlStatistics.StartTimer(Statistics);
        SqlDataReader ds = RunExecuteReader(0, RunBehavior.ReturnImmediately, true, ADP.ExecuteScalar);

        object retResult = null;
        try
        {
            if (ds.Read())
            {
                if (ds.FieldCount > 0)
                {
                    retResult = ds.GetValue(0);
                }
            }
            return retResult;
        }
        finally
        {
            // clean off the wire 
            ds.Close();
        }
    }
    finally
    {
        SqlStatistics.StopTimer(statistics);
        Bid.ScopeLeave(ref hscp);
    }
}

and

MySqlClient:

public override object ExecuteScalar()
{
    lastInsertedId = -1;
    object val = null;

    #if !CF
    // give our interceptors a shot at it first
    if (connection != null &&
        connection.commandInterceptor.ExecuteScalar(CommandText, ref val))
        return val;
    #endif

    using (MySqlDataReader reader = ExecuteReader())
    {
        if (reader.Read())
            val = reader.GetValue(0);
    }

    return val;
}

So it doesn't hurt to use ExecuteReader for ExecuteScalar and no performance difference whatsoever..

Mark Wilkins · Accepted Answer · 2010-07-27 21:11:53Z

3

The underlying implementation of those methods simply executes the given SQL statement, so you can run pretty much any statement with any of those methods. It's just that the end result is that a reader tries to return an expected result set, while the other method does not expect a result set. It is probably a little less efficient to use ExecuteReader to run a statement that does not produce a result set. The provider will still create a data reader object, which would add a little bit of cost (probably negligible in most cases unless the provider ends up making another round-trip call to the server).

answered Jul 27, 2010 at 21:11

Mark Wilkins

41.3k5 gold badges61 silver badges111 bronze badges

Collectives™ on Stack Overflow

SQLCommand.ExecuteReader() does not restrict to read only statements

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related