Anatoly Lubarsky Logo
programming, design, integration, games, music

OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar

Before we start I just wanted to clarify several things: I don't use Oledb namespace in my everyday work. Working with MSSQL I use SqlClient. Using SqlCommand object I never (and really don't) understand actually why we need SqlCommand's ExecuteScalar function in order to output a single query value, it is far more efficient to use ExecuteNonQuery along with procedure with output parameter instead (IMHO).


In any case -- here I want to speak especially about OledbCommand. Speaking of which it has the same functions as SqlCommand has but the implementations are very different from one to another. We can use Oledb namespace dealing with all databases (It is much faster to use SqlClient when we have to deal with MSSQL 7.0 and higher but still...).


OledbCommand, oh man -- when we look through the source code it turns out that ExecuteReader and ExecuteScalar are actually the same functions - I told you -- don't use ExecuteScalar:


public object ExecuteScalar()
{
   OleDbConnection.OleDbPermission.Demand();
   object obj1 = null;
   try
   {
      this.executeQuery = true;
      using (OleDbDataReader reader1 = 
         this.ExecuteReaderInternal(CommandBehavior.Default, "ExecuteScalar"))
      {
         if (reader1.Read() && (0 < reader1.FieldCount))
         {
            obj1 = reader1.GetValue(0);
         }
         return obj1;
      }
   }
   catch
   {
      throw;
   }
   return obj1;
}

and ExecuteReader:


public OleDbDataReader ExecuteReader(CommandBehavior behavior)
{
   OleDbDataReader reader1;
   OleDbConnection.OleDbPermission.Demand();
   this.executeQuery = true;
   try
   {
      reader1 = this.ExecuteReaderInternal(behavior, "ExecuteReader");
   }
   catch
   {
      throw;
   }
   return reader1;
}

You can see -- they both call ExecuteReaderInternal (no magic), ExecuteScalar just fetches the first field from reader inside itself and it looks a bit ugly vs. ExecuteNonQuery. Despite this fact one can learn a lot about how to use using directive and validate if the reader is not empty from this piece of code.


You can notice though that OledbCommand.ExecuteNonQuery looks also very alike ExecuteReader:


public int ExecuteNonQuery()
{
   OleDbConnection.OleDbPermission.Demand();
   this.executeQuery = false;
   try
   {
      this.ExecuteReaderInternal(CommandBehavior.Default, "ExecuteNonQuery");
   }
   catch
   {
      throw;
   }
   return this.recordsAffected;
}

The difference is in executeQuery property. Being set to false we don't go through the huge piece of code inside ExecuteReaderInternal.


Related Posts:

Friday, April 15, 2005 5:38 PM

Comments

# re: OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar
In all the implementations above, what do you suppose the try..catch is accomplishing? Just curious...

4/18/2005 8:18 PM by Jiho Han

# re: OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar
hm, I suppose it will catch exception from ExecuteReaderInternal (it makes sense since it is private).
In ExecuteScalar you have using which is equivalent to try-catch-finally, so you have try-try there.

4/18/2005 8:58 PM by Anatoly Lubarsky

# re: OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar
Really nice post - really nice

4/22/2005 4:54 AM by Bill

# re: OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar
thanks

4/22/2005 6:29 AM by Anatoly Lubarsky

# Traffic Analysis

8/5/2005 4:58 PM by Anatoly Lubarsky: Weblog

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs