Friday 13 January 2012

Explanation about Execute Non Query, Execute Scaler, Execure Reader

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.

No comments:

Post a Comment