Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Tip 6 : Function returning SqlDataReader Type

I am strongly against building a function that returns SqlDataReader type because it destroys the basic purpose of building a function: decoupling or separation of concerns.  For SqlDataReader, an active database connection needs be kept open across your function to the client that calls the function.

But, sometimes I have no choice. One of my clients has many DotNetNuke applications that use functions returning SqlDataReader everywhere.  I am required to either keep coding style consistent or call functions from their dlls.

So, if you have to work with functions returning SqlDataReader, you need to keep in mind:

1. Within the function, set CommanderBehavior to CloseConnection. It will close the connection automatically when the SqlDataReader is closed.

2. Within the function, never close the connection.

3. In the client code, after completing the data read, close the SqlDataReader.

5 Comments

  • I wish to show my thanks to you just for rescuing me out of such a predicament.
    As of searching throughout that the world wide web and coming across thoughts that were
    not helpful, I thought my entire life was well over.
    Living without that the approaches to the issues you’ve solved
    because a result of your main guide is usually a crucial case, as well as the kind which may have adversely damaged my career
    if I had not noticed your web page. Your
    actual skills and kindness in dealing with all the stuff was crucial.
    I don’t recognize what I would’ve done if I had
    not come upon such a step for example this. I can at this time look ahead to my future.
    Thanks so much for that the impressive and
    effective help. I will not be reluctant to recommend your web pages
    to any other person who should receive guidelines on this query.

  • Rich, I use sponsorspace for my sponsorship program sponsor me

  • I do agree with all of that the ideas you’ve presented in your post.

    That they’re really convincing and will certainly work.

    Still, the posts are very short for newbies. Could you
    please extend them a bit from next time? Thanks for the post.

  • This will be that the proper weblog if you really wants to check out this topic.

    You are aware of much it is virtually demanding to argue along with you (not too I just would want…HaHa).
    You certainly put a different spin on that the topic thats been written
    about for a long time. First-class stuff, just wonderful!

  • this what i searching for... thanknyou very much. i have a project that need to be done int short time.. so I can't read each msdn docs. very helpfull..

Comments have been disabled for this content.