Using Delegates with Data Readers to Control DAL Responsibility
 
Published: 17 Sep 2004
Unedited - Community Contributed
Abstract
Teemu Keiski demonstrates how to use delegates to create a call-back mechanism which remains the responsibility of a data reader on the DAL but gives full consuming capability to the client.
by Teemu Keiski
Feedback
Average Rating: 
Views (Total / Last 10 Days): 31131/ 43

Background

Why People Don't Use Data Readers

A problem with data readers is their connected nature. Every data reader has one database connection tied to it. With .NET 1.X the limitation is one open data reader per database connection, while .NET 2.0 brings new features which allow more than one active result set per connection, but the connection still exists. This means that passing a data reader to the client means also passing the responsibility of closing the data reader and the connection.

Developers also tend to avoid using data readers because they are read-only, forward-only and therefore a bit unsuitable for complex calculation scenarios which might involve the need to access data more than once. This means that with data readers you would need to keep the connection and reader open for a longer time or reacquire the result set, increasing the time the connection is in use.

Using Delegates to Control DAL Responsibility

Delegates are a way to reference methods based on their signature and return type, allowing an instantiated delegate to reference a given method assuming that the method matches the delegate definition. Delegate instances can be passed as method parameters which means that methods themselves can be passed and again invoked by the class/method which receives the delegate as an argument.

Delegates are one solution to the previously mentioned responsibility problem with data readers. In most common data binding scenarios an IDataReader instance is pulled from the DAL, assuming data readers and the data binding are in use, resource releasing is left up to the client. With delegates we can turn this so that a data binding method taking an IDataReader instance as a parameter, is itself given as a parameter to the DAL. With this idea the responsibility of cleanup is up to the DAL, because DAL invokes the delegate instance, waits for it to finish execution and then closes the data reader and the database connection.

Code

DAL

public class DataComponent
{
 //Delegate to declare accepted databinding callback method
 public delegate void IDataReaderHandler(IDataReader reader);


 //Get the data reader callback using given delegate
 public static void GetAuthor(string authorID,IDataReaderHandler handler)
 {
  //Connection scope -  using pubs database
  using(SqlConnection conn=new SqlConnection("server=.;Trusted_Connection=True;DATABASE=pubs"))
         {
   //Query
   SqlCommand command=new SqlCommand("select * from authors where au_id=@ID",conn);
   command.Parameters.Add("@ID",SqlDbType.NVarChar,11).Value=authorID; 
   conn.Open();


   //DataReader scope - calling the delegate method and finishing it before
   //going out of scope and closing the reader
   using(SqlDataReader rdr=command.ExecuteReader(CommandBehavior.CloseConnection))
   {
    handler(rdr);
   }
  }
 }
}

Page (code-behind)

public class WebForm1 : System.Web.UI.Page
{
 protected System.Web.UI.WebControls.DataGrid DataGrid1;


 private void Page_Load(object sender, System.EventArgs e)
 {
  //Bind grid with given author id
  if(!IsPostBack)
   DataComponent.GetAuthor("172-32-1176",new DataComponent.IDataReaderHandler(BindGrid)); 
 }


 //Bind the datagrid when this is called by the delegate
 private void BindGrid(IDataReader reader)
 {
  DataGrid1.DataSource = reader;
  DataGrid1.DataBind();
 }
}

Conclusion

The point is that data readers can be dangerous if they aren’t in control. With this simple idea you can mitigate the potential threat of running out of resources and maybe even consider passing data readers to the client in proper scenarios. It’s still not as flexible as the data table but with this you can build data readers as part of your DAL to get the maximum flexibility out of ADO.NET.



User Comments

Title: Delegate for DataReaders??   
Name: Varangian
Date: 2007-03-02 5:41:39 AM
Comment:
I cannot understand why there is the need of a delegate at all. Imagine return a DataReader from an ExecuteReader with CommandBehaviour.CloseConnection, after reading the whole records then closing the DataReader... basically I find it the same.
Title: Re: How abt App Blocks   
Name: Teemu
Date: 2006-10-04 5:35:44 AM
Comment:
I agree that if you have an app block (basically reusable component) which already does the task "well enough", why not just to use it. It's like having apples and oranges. You eat the one you like. ;-)
Title: How abt App Blocks   
Name: Deepak
Date: 2006-09-27 6:50:31 PM
Comment:
Good Article,
Wouldnt it be easier to use Data App Block, and serve the results up to the UI layer when compared with this pattern?

I understand that using the Plain Vanilla ADO.NET, this is a good way to handle the results.

Any comment?
Title: Using/Try...Finally   
Name: Teemu
Date: 2005-08-30 1:20:01 AM
Comment:
VB.NET 1.0 does not have Using keyword, so it requires you to use Try...Finally block. VB.NET 2005 on the other hand has Using keyword similar to C#'s.
Title: VB.Net   
Name: Mark Olszowka
Date: 2005-08-29 3:38:17 PM
Comment:
Can this be coded in VB.Net? I don't see the corresponding keyword to using in VB.Net.
Title: Thanks   
Name: Teemu
Date: 2005-05-17 11:25:19 AM
Comment:
Matt,

thanks for the comments. Certainly, that's one more way to utilize this technique. You have a good point.

I've set up the example this way to keep it simple as my original point was in demonstrating how to control the responsibility, and not in what's the best possible design when using it in real-world.

So, I've left the conclusions intentionally to the reader and it is nice that they come up like this (even if just as comments to the article) as they benefit all the readers.

Again,

thanks for the comments.
Title: Why pass reader to UI?   
Name: Matt
Date: 2005-05-17 11:14:09 AM
Comment:
Nice article ... Wouldn't it make for a cleaner design to return an Author(new class) to the code-behind instead of the IDataReader? It would seem to me to make more sense to write a 2-tier DAL and loan the IDataReader from a base class(say DataAccess) to object-specific data access classes(like AuthorDataAccess). The way your code is set up you're going to write the SqlConnection and ExecuteReader code over and over in every data access method.
Title: Useful for multiple result sets   
Name: Mike Poole
Date: 2005-03-11 11:47:03 AM
Comment:
Have found this technique useful when you have a single SQL returning multiple result sets. e.g. a parameterised stored procedure (parent query) which calls other stored procedures (child queries) based on a control flow.

In this case you pass a series of delegates created in your BLL up thru 1 call to the DAL, the DAL method shows the flow of each SQL result set quite nicely. With the BLL class contruction separated in that class.

The delegated methods which build the object hierarchy can be re-used in other delegated data access calls as there is 1-to-1 correspondance with individual child queries.
Title: disadvantages   
Name: DalGuru
Date: 2005-03-06 2:21:18 AM
Comment:
There are a few disadvantages to this pattern:
a. You now need two client methods, rather than one. (That's a lot of extra methods when you have 100s of calls to a DAL tier).
b. You need a new delegate definition for each case where the delegate invoked method's signature varies.
c. The intial calling client method parameter list is now more complex. (passing in a new delegate each time is not an elegant solution when an app can have 100s of client calls to a central DAL tier.
Title: Why not bind to collections   
Name: Jeff Gonzalez
Date: 2005-01-12 4:26:08 PM
Comment:
Where I work we use CodeSmith to generate an abstraction class and collection class for each table in our database.

We fill the abstraction and collection classes using readers and then close them immediately and return the collections to the aspx page.

I liked the idea of a delegate that closes the reader, but in our architecture it would be kind of useless.
Title: To Paul   
Name: Teemu
Date: 2004-11-17 6:33:19 AM
Comment:
Paul,

thanks for participating the discussion.

Yes, DBEnumerator does close reader when it finishes the reading, but it doesn't do for example error handling itself (e.g if there's an error in reading, DBEnumerator itself also throws the exception up and won't close the reader).

What I'm trying to say is that there are also other possible places for errors than just when ExecuteReader is called. It can occur anywhere as long as DataReader is handled (from executeReader call to consuming), so if you place executeReader in try...catch, it doesn't remove the need for them on calling (databinding) code as well, for error scenarios.

With the solution I demonstrate, it is fool-proof. You can't get out of the code without datareader getting closed. It covers the whole "lifecycle" of the reader, from creation to consuming, and resources are always freed.

I'm not saying this is the only solution or even the best one, you are still free to write code as you like, however, it seems pretty good one to get over the horror scenarios one might have with data readers.
Title: DBEnumerator   
Name: Paul
Date: 2004-11-16 5:11:28 PM
Comment:
You missed the point of Developer's comments. A DataGrid uses the IEnumerable interface to get a DBEnumerator from a SqlDataReader. The DBEnumerator automatically closes the reader when complete and the connection, if the behavior is set accordingly. So, this is unnecessary as long as you set the behavior to close connection. The lack of using {} in his example is not an issue because I would hope in your DAL you would already wrap it with a using to catch SqlConnection errors or ExecuteReader errors.
Title: Nevermind %)   
Name: Richard P
Date: 2004-11-01 3:16:33 PM
Comment:
Nevermind. I was missing the whole point. Mondays.

BindGrid() is the handler and is being called from the GetAuthor function, which is wrapping the call to the handler in a using statement. So it'll get disposed if there's an exception.
Title: What about exceptions?   
Name: Richard P
Date: 2004-11-01 3:11:52 PM
Comment:
You seem to be relying on CommandBehavior.CloseConnection to ensure the connection gets closed.

What happens if there's an unhandles exception in your BindGrid function before DataGrid1.DataBind()? The reader would never reach the end and the connection would not get closed. You just leaked a connection into the pool.

Am I missing something?
Title: Great example   
Name: Ian
Date: 2004-10-30 10:14:02 PM
Comment:
Considering that I've just built a preliminary DAL using nothing BUT datareaders, this sort of idea will probably go a long way to the development of my next DAL.
Title: Very Nice Indeed   
Name: Doug Nelson
Date: 2004-10-30 3:14:35 PM
Comment:
I have shyed away from using DataReaders in the past because I did not like have to make my business logic classes dependant upon a specific data provider class.

To to Developer as far as unneeded, totally not true, you code example is dependant upon the System.Data.SqlClient namespace and you have not seperated your data access from your application layer and thus if the data provider was changed to Oracle for example your application code would have to be rewritten, instead of just the DAL layer.

This is a most elegant solution. Thanks
Title: Aspnet Developer   
Name: Pablo Harguindey
Date: 2004-10-20 8:13:52 AM
Comment:
Very useful I was arguing against the use of data readers on my work until I read your article. Your solution is simple and practical.

Best Regards
Title: This pwns!   
Name: Michael K. Campbell
Date: 2004-09-22 7:42:48 PM
Comment:
Teemu,

This is just plain awesome stuff. Way to capitalize on the disconnected nature of Delegates to solve a complex problem that has caused oodles of problems.

I'll be revisiting some of my DALs with this approach. I LOVE it.
Title: Thank you!   
Name: Teemu Keiski
Date: 2004-09-20 3:02:25 AM
Comment:
Thank you for the comments.

Developer,

using(){} is essentially same as try..catch...finally which throws the exception up, I see it as very convenient way to handle this sort of stuff plus that it keeps code readable. There is not much overhead until the Exception is actually raised (when there's always overhead in handling it)

David,

Yes, I could have put SqlCommand to be disposed (as you pointed out it's IDisposable), but SqlCommand's Dispose does nothing more than remove SqlCommand from it's Site's Container (component stuff) and raise Disposed event, so I didn't see using it reasonable enough to keep the example as simple as possible. But certainly, being explicit in this sort of stuff is good and your comment is accurate. I do recommend using "using" statement when it is possible.

Thank you for the comments!
Title: Agree with Steve   
Name: David L. Penton
Date: 2004-09-19 7:50:22 PM
Comment:
Right on Steve...this is a great thing to implement.

As for "Developer", in your example you are not .Dispose()-ing of any of your objects. In the ADO.Net world, this is a necessity for high usage apps. The only thing I see that is missed in the above example is a "using" block for the SqlCommand (to accomodate for the guaranteed calling of .Dispose()).

The purpose of DAL architechture is ensuring the database resources get managed properly. With ADO.Net, "using" does this nicely, and saves the business component developer time.
Title: Unneeded and only causes overhead   
Name: Developer
Date: 2004-09-19 4:22:36 PM
Comment:
In the example shown, this is unneeded because once the datagrid is done with the datareader, it automatically closes the reader. The key is the CommandBehavior.CloseConnection parameter passed in. Try the code below and you will see that both the reader and connection are closed by the datagrid.

SqlConnection con = new SqlConnection("server=.;integrated security=sspi;initial catalog=pubs");
SqlCommand cmd = new SqlCommand("SELECT * FROM authors", con);

con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Response.Write(rdr.IsClosed);

grid.DataSource = rdr;
grid.DataBind();

Response.Write(rdr.IsClosed);
Response.Write(con.State);
Title: Awesome   
Name: Steve Smith
Date: 2004-09-14 3:27:16 PM
Comment:
This is an excellent way to get the benefits of the datareader without the dangerous parts! Very cool!






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 8:03:01 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search