CommandBehavior.SingleRow
I'm working on a chapter on ADO.NET and am going through documentation to make sure I have everything perfect and I came across something I hadn't noticed before. I'm sure I'm the last yahoo to see this, but hopefully not, since the point of this post is to help anyone that hasn't seen this option yet, but often times I need something halfway in between ExecuteScalar and ExecuteReader. You know, those times when you want to return more than one field, but the DataReader seems like overkill since you know you will only be returning one row every time. An example of this is say, looking up a User to get some settings about them back. The typical, “SELECT * FROM Users WHERE ID = @ID” type of thing. You know there's only going to be one row ever.
I'm not sure if I'm the only one, but I've only ever really used CommandBehavior.CloseConnection to have the connection automatically closed when the DataReader is closed. Well, back to my scenario about only needing one row of data. There's also CommandBehavior.SingleRow, which is actually optimized for only one row, which is exactly what I need! Way to go Microsoft, sorry I didn't notice this before! *hits head on desk*. Now I can go back and find where i'm only ever going to have one row returned and use this for a little peformance gain, which is always good! :)
6 Comments
Andres Aguiar said
August 10, 2003
AFAIK, it has no effect for SqlClient. In the documentation says that is up to the data providers to do something with that value, and says that the OleDB client uses it. I asked once a guy in the SqlClient team if it has some effect for SqlClient and he said it does not. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommandbehaviorclasstopic.asp
HumanCompiler said
August 10, 2003
Well that's too bad...I did read that it's up to the provider to enhance it or not. I guess I'll be in no hurry to use it and I'll just hope that maybe it might be enhanced in the future. Thanks for the info Andres!
Phil Scott said
August 10, 2003
I've been sticking with good ol' output parameters and ExecuteNonQuery. Avoids creating the DataReader and the such.
HumanCompiler said
August 10, 2003
I'm really curious if the performance is better or worse with that...I'll have to do some testing. Like say I want two fields and I want their values returned...is using Parameters that much faster over bringing back the two fields in a result set?
Maulik said
July 09, 2008
I bet Output parameters will be a way faster than bringing resultset.
http:// said
February 05, 2009
My asp.net page became a lot faster. Got from about 30 to 5 seconds. Still when using parameters the page was slow, when I added CommandBehavior.SingleRow to ExecuteReader the magic happed. So, thanks Erik :)