Use the Right Tool for the Right Job - ExecuteScalar or ExecuteNonQuery

Would you use a drill to pre-drill a hole for a nail to hang a picture? I wouldn't either. Just hammer the nail into the wall. OK, need something more code related? Would you define a class with a single property and no other members just to store a string? Me neither.

<FONT color=#008000>public class foo{

<FONT color=#008000>   public string goo = "";

<FONT color=#008000>}

This might seem like an obvious point to most, and I sure hope it does. But recently I worked on a project that just made me think "WTF"?  I saw some ADO.NET code that was intended to execute an UPDATE statement against a table in a SQL Server database. It worked just fine and dandy, but I had to take a second look because the code was issuing the ExecuteScalar method. At first I though, maybe the code was actually returning a value and not executing an UPDATE statement, so I looked at it again thinking I missed something. Nope, it was just and UPDATE statement.  So then I wondered if the UPDATE statement was also returning a value after the UPDATE ... looked again and nope, it wasn't. This code was simply doing something like this:

  <FONT color=#006400> string sql = "UPDATE tableX SET columnA = value1 WHERE columnB = value2";

So why was this code using ExecuteScalar to run this SQL? I started looking around the project that I inherited and found several other places that ExecuteScalar was used ... most of which were also issuing a simple action query with no intention of getting a result back. The code looked like this:

 <FONT color=#008000>  cmd.ExecuteScalar();   

That's not psuedo code, there was nothing on the left hand side to store the return value of the ExecuteScalar method. So I started to refactor the code to change the places that were using ExecuteScalar but not actually getting a return value into code like this:

<FONT color=#008000>   cmd.ExecuteNonQuery();

Use ExecuteScalar to execute a query to return a single scalar value and use ExecuteNonQuery to execute an action query and return no values. Is this basic stuff you already know? Very likely (or hell yeah!). In fact, that is why I looked so hard when I first saw this code ... I thought, geez, I must be missing something. Like most jobs, using the right tool for the right job just makes more sense.


Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders