Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

People please stop passing DataReader to the presentation layer. If you want to use datareader object then use it to fill a custom collection and then pass the custom collection to the presentation layer where it can be bind to a control or whatever.

Check out this code:  

public static SqlDataReader GetCategories()
    {
        
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
        SqlConnection myConnection = 
new SqlConnection(connectionString);
        SqlCommand myCommand = 
new SqlCommand("SELECT * FROM Categories", myConnection);

        SqlDataReader dr = 
null;
        
        myConnection.Open();
        dr = myCommand.ExecuteReader();
        myConnection.Close();

        
return dr; 
    }

Now, use the GetCategories method.  

 protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = BL.GetCategories();
        GridView1.DataBind(); 
       
    }

But what happened!! You recieved the error: Invalid attempt to FieldCount when reader is closed.

The Reader is not able to run because the connection is closed. However you can solve this problem using the following code:

  public static SqlDataReader GetCategories()
    {
        
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
        SqlConnection myConnection = 
new SqlConnection(connectionString);
        SqlCommand myCommand = 
new SqlCommand("SELECT * FROM Categories", myConnection);

        SqlDataReader dr = 
null;
        
        myConnection.Open();
        dr = myCommand.ExecuteReader();
        
        
// I ain't closing the connection
        //myConnection.Close();

        
return dr; 
    }

But now we are not closing the connection. That's even bad than the error we had last time :).

Offcourse you can close the connection on the client side but thats not a good idea. Why does the SqlDataReader even reach the client side + the connection is still open. So basically we are wondering on the presentation layer with our connection open.

If you want to make good use of SqlDataReader then populate a custom collection with it and return the custom collection to the presentation layer. This way you are sending a disconnected entity to the user interface rather a connected entity.

powered by IMHO

 

Posted on Wednesday, November 16, 2005 9:41 PM | Back to top


Comments on this post: Please don't pass DataReader to the presentation layer :(

# re: Please don't pass DataReader to the presentation layer :(
Requesting Gravatar...
if you are not anti-dataset/datatable, you can also use the new VS2005 LOAD method to load your datareader into a dataset or datatable and then pass the data to the UI in that object.
Left by Julie Lerman on Nov 17, 2005 7:51 AM

# re: Please don't pass DataReader to the presentation layer :(
Requesting Gravatar...
I agree with your above statement but you can return the reader from the method *with* an open connection. The CommandBehavior.CloseConnection will make sure that as soon as you close the Reader, the underlying connection is closed as well.
In Business Layer
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

In Presentation Layer

something like this would be fast, easy and clean:

MyReader = MyBusinessLayer.GetUsefulData();
MyDataGrid.DataSource = MyReader;
MyDataGrid.DataBind();

At this point, the grid will be bound, the Reader is closed and finally the connection is closed as well.
Left by Vishalg on Mar 16, 2006 1:57 PM

# re: Please don't pass DataReader to the presentation layer :(
Requesting Gravatar...
offcourse you can do this way but this is not considered a good technique. The reason is that you are sending an open connection to the presentation layer. It is always a good idea to fill the Custom Entity List using DataReader and then send the list back to the Presentation layer.
Left by azamsharp on Mar 16, 2006 3:50 PM

# re: Pass DataReader to the presentation layer from bussiness layer
Requesting Gravatar...
I want to return SqlDataReader object from bussiness layer to presentation layer

//This code is in Bussiness Layer.
public SqlDataReader load_data(string Text1)
{
string boText1 = Text1;
SqlDataReader DR = null;
SqlConnection conn = new SqlConnection(connectionString)
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM EMPLOYEEMASTER", conn);
SqlDataReader DR = cmd.ExecuteReader();
return DR;


//This code is in Prasentation Layer.
//this is the calss object of Bussiness Layer class bdpsClass
bdpsClass cls_bdps = new bdpsClass();
SqlDataReader DRstr= cls_bdps.load_data(text1);
DRstr.Read();
Left by Pravin Shete on Apr 01, 2008 7:03 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net