Return that data reader

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Web Development category of DaniWeb, a massive community of 397,689 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,529 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Web Development advertiser:

•

hollystyles's Blog

Web Development

Feb 22nd, 2007, 10:25 am

Show Printable Version

I'm sitting at my workstation, I should be coding some fixes to a website project, but I'm surfing DaniWeb instead. It has been on my mind to add some entries to my blog for months, but I always seem to be too busy, or I just can't think of anything to write.

I read a book by Joe Spolskey recently 'Joel On Software' I liked it a lot and amongst all the great stuff in there, he said that the best way to get good at writing is to write. This seems kind of obvious but it's very true, so here I am just writing, hopefully it will get more interesting soon!

I've never really liked DataReaders (forward only, readonly database cursors) Everywhere you go technical articles say they are the most efficient way to get result sets from a database. In my ASP days with the good old VBScript ADO recordset I always seemed to get better performance by getting a disconnected recordset and immediately converting it to an array and ditching the resource intensive ADO objects as soon as possible. With .NET 1 and now 2 I revisited them but I couldn't see how to return a reader object from a function (something you're going to want to do if you create n tier type web applications with a seperate data layer) the problem with returning a DataReader is it requires the connection to remain open while you iterate the rows, but having left the function that spawned the reader there's no easy way to close the connection that was opened when the calling function is finished with the reader, so we have to hope that the garbage collector will sort things out for us in a timely manner, which it often doesn't. Well I finally discovered the trick, in the ADO command object you can set behaviours before calling ExecuteReader, in these behaviours you can instruct that the connection be closed when the reader is closed automatically.
This is how you do it

Help with Code Tags

(Toggle Plain Text)

namespace myDataAccessLayer
{
   public class DataObject
   {
     public static DataReader GetReader()
     {
        SqlConnection connection = new Sqlconnection(myConnString);
        
        string sql = "select * from myTable";
       
        SqlCommand command = new SqlCommand(sql, connection);
        
        DataReader myReader = command.ExecuteReader(CommandBehaviour.SingleResult | CommandBehaviour.CloseConnection);
        
        command.Dispose //Yes! you can do this here.
        
        return myReader;
     }
   }
}

public partial class _Default : System.Web.UI.Page
{
     private void Page_Load(object sender, System.EventArgs e)
     { 
          if(!IsPostBack)
          {
             DataReader myReader = myDataAccessLayer.DataObject.GetReader();
             
             this.myGridView.DataSource = myReader;
             this.myGridView.DataBind;
             
             myReader.Close(); //the connection is also closed for us.
           }
      }
}

This blog entry was written by hollystyles. It has received 2,180 views, 1 comment, and 1 linkback. 1 voter has rated this entry 5 out of 5 stars.