ASP.NET Discussion Boards

Jassim Rahma25-Dec-12 5:57

25-Dec-12 5:57

Hi,

I have been using ExecuteNonReader for a quite long time now and it's doing great for me. For example

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteNonReader();

then why should I use it like this:

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteScalar();

Technology News @ www.JassimRahma.com

Re: why should I care about ExecuteScalar

Sandeep Mewara25-Dec-12 19:30

Sandeep Mewara

25-Dec-12 19:30

Jassim Rahma wrote:
ExecuteNonReader

I don't find a knowledge base article for the same in MSDN. Based on what I know and can infer, using it to get just a 'single' value will be an overhead. It should be used when database query is going to provide a set of records.

My guess, term in discussion is ExecuteNonQuery and should not be ExecuteNonReader.

Jassim Rahma wrote:
ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query.
Refer: Refer: SqlCommand.ExecuteScalar Method [^]

Now, based on the details and documentation, for getting a count, ExecuteScalar fits in the best that will get me the result directly as needed.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

Re: why should I care about ExecuteScalar

CommDev25-Dec-12 19:33

CommDev

25-Dec-12 19:33

Hi,

ExecuteNonReader: used actions like insert, update, delete

ExecuteScalar: will return an object

for details you can learn from some books about these two.

Thanks,
CommDev

modified 6-Dec-16 1:27am.

Re: why should I care about ExecuteScalar

CommDev25-Dec-12 19:40

CommDev

25-Dec-12 19:40

Hi,

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 Smile | :)

Comm100 - Leading Live Chat Software Provider

Re: why should I care about ExecuteScalar

Jassim Rahma28-Dec-12 9:40

Jassim Rahma

28-Dec-12 9:40

Thanks CommDev.

But if I am going to return GetOrderById.. what's the difference if I am going to use ExecuteNonQuery with Output parameter or ExecuteReader with return SqldataReader?

Does it make a difference specially performance wise?

Technology News @ www.JassimRahma.com

Re: why should I care about ExecuteScalar

Naveen Katta26-Dec-12 0:10

Naveen Katta

26-Dec-12 0:10

hi,

Generally we use execute scalar function for displaying the first column and first row, but if we use ExecuteNonReader it will show error why because in asp.net only 3 commands are there ExecuteReader()
ExecuteNonQuery()
ExecuteScalar() this is the option to get one value from the database.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.