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

ExecuteReader, ExecuteNonQuery, ExecuteScalar ... When to use What ?

SqlClient namespace in ADO.NET provides 3 basic methods to run queries with SqlCommand against MSSQL:


  • ExecuteReader
  • ExecuteNonQuery
  • ExecuteScalar

.NET Framework is around for 5 years already now, but these 3 methods are among the most misused in application development. The problem is that ExecuteReader will work for every query via SqlCommand, so most people use it everywhere. The direct result - slow and non-scalable solution. Other results include database bottlenecks, locks and connection leaks.


So I decided to provide a small cheat-sheet that clearly demonstrates when to use each method.


ExecuteReader

Do not use: when database query is going to provide for sure exactly 1 record. It may be getting record by its id (which is PK in the database) - GetOrderById and such. In this case use ExecuteNonQuery with output parameters.


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. In all these cases we can use input/output/input-output parameters. Please note that from the application architecture point of view it is also good practices when your Insert and Update stored procedure returns changed record exactly like Get By Id method does.


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. For example type image cannot be output parameter in MSSQL.


The most common example for ExecuteScalar is fetching a single image stored in the database and converting it to array of bytes. If you google it - most examples will demonstrate using of ExecuteReader to accomplish image handler, however ExecuteScalar will be more scalable and faster.


Conclusion

Always use ExecuteNonQuery except: when you have a set of records - use ExecuteReader and when you have a single output value that cannot be defined as a parameter - use ExecuteScalar. Hope this helped to clarify something. Enjoy :)


Related Posts:

Tuesday, May 01, 2007 9:58 PM

Comments

# re: ExecuteReader, ExecuteNonQuery, ExecuteScalar ... When to use What ?
thank you very much for this.

i was looking for such a comparison.


Cheers!

/andrei

11/23/2007 5:02 PM by Andrei

# re: ExecuteReader, ExecuteNonQuery, ExecuteScalar ... When to use What ?
thanks.... very good comparison.

2/19/2008 9:30 PM by karthick

# re: ExecuteReader, ExecuteNonQuery, ExecuteScalar ... When to use What ?
Can you explain why ....

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

I would like to know.

Thanks!

4/15/2008 4:21 AM by Gunthar

# re: ExecuteReader, ExecuteNonQuery, ExecuteScalar ... When to use What ?
Nice Comparision.....Thank you.

4/24/2008 11:28 AM by Rani

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