How to pass datareader value from DAL to presentation layer

Question

I am working on a project using 3 tier architecture. I want to knw about how to pass datareader value from DAL to Presentation layer

My code is like this. In DAL layer

public class HomeDAL
{
 public SqlDataReader DefaultSearchFriends(long userid)
 {
    SqlConnection SocialConn = new SqlConnection(connstr);

    using (SqlCommand comm = new SqlCommand("proc_FriendsSearch", SocialConn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@userid", userid);
        SocialConn.Open();
        SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }
 }
}

In BAL layer

public class HomeBAL
{
    public SqlDataReader DefaultSearchFriends(long userid)
    {
       HomeDAL HDAL = new HomeDAL();
       SqlDataReader dr = HDAL.DefaultSearchFriends(userid);
       return dr;
    }
}

On Presentaion Layer I wrote this on Page load

HomeBAL HBAL = new HomeBAL();
SqlDataReader dr = HBAL.DefaultSearchFriends(user_id);
while (dr.Read())
{ 
 //some code
}

Now i want to know two things

1- Is this right to call datareader in this way or there is some better logic.

2- how to close datareader object in BAL layer and in DAL layer.

DHN · Accepted Answer · 2013-01-25T07:53:44.520

Well, the basic idea of a layered architecture is to decouple the different components for several reasons. Some reasons are testability, maintainability, extensibility but there are many more.

To pass the data between these layers - well it depends a bit on the kind of data - but usually you would use some simple classes as data transfer objects (DTO), which would be filled with data in the DAL. E.g.

public class Person
{
  public string Name {get; set;}
  public string FirstName {get; set;}
  ...
}

With your approach you are breaking this idea, because you're passing the DataReader to presentation layer which implies, that you cannot switch the DAL technology without touching the other layers. E.g. if you want to use Entity Framework you would have to modify every part in the code, where you're currently using the SqlDataReader.

You can also see, if you'd stick to the idea behind the layered approach, you don't have to think about your second question.

I hope this helps a little bit.

EDIT

Ok I'm a bit curious, that you don't find a proper solution. But anyways, the first and simplest approach could be, don't publish the SqlDataReader. Handle its life cycle in the DAL. Means, assuming that you're using my DTO above

public class HomeDAL
{
 public List<Person> DefaultSearchFriends(long userid)
 {
    SqlConnection SocialConn = new SqlConnection(connstr);

    using (SqlCommand comm = new SqlCommand("proc_FriendsSearch", SocialConn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@userid", userid);
        SocialConn.Open();
        SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

        var persons = new List<Person>();

        while (dr.Read())
          persons.Add(new Person { Name = dr["Name"], FirstName = dr["FirstName"] });

        dr.Close();

        return persons;
    }
 }
}

would be the much better approach.

Thanks dude, but can u give me a proper solution now how to use sqldatareader. i show u my architecture, i knw it has problems. but now how can i rectify these error. Please show me the solution as i show u my code. wht to do in DAL and BLL file — ankit Gupta, Jan 25 '13 at 06:42
@ankitGupta: I'm not sure, why you're not seeing the solution after our explanations, but well pls see my edit. — DHN, Jan 25 '13 at 07:55
@ankitGupta: Well if it works for you, it's not wrong. But with having regard to a layered architecture, it's totally wrong, because you don't separate anything. As I described in my post above, the only thing which should be published by the DAL is the data via DTO. The `SqlDataReader` isn't data, it's an implementation detail, which should be hidden from the other layers to maintain the basic idea behind this architecture. I also mentioned this above. — DHN, Jan 25 '13 at 11:14
@ankitGupta: No problem, you're welcome. Pls, mark my post as answer, if you think, that it helps you. — DHN, Jan 25 '13 at 12:41

score 0 · Answer 2 · answered Jan 24 '13 at 13:23

0

I think your architecture has problems;

you are using concrete classes to query database; instead you need an abstraction in case you change the backend SQL server or the querying mechanism.
you are passing concrete SqlDataReader into all of your layers. From DAL, you need to return data objects not the db operation context instance.
you need to change only domain objects between layers, not the object doing the actual work.

I suggest you to refer to n layer reference application of microsoft spain

answered Jan 24 '13 at 13:23

daryal

14,643
4
38
54

thanks sir, can u explain me with a good example. wht to do in this scenerio. – ankit Gupta Jan 24 '13 at 13:26
if you refer to the link I have provided, there is detailed explanation. It will be too long to be explained here (there are many concepts, which you need to decide to use or not to use depending on your needs). – daryal Jan 24 '13 at 13:28
Thanks dude, i knw my architecture contains problems. but now i want to rectify these error. Please show me the solution as i show u my code. wht to do in DAL and BLL file. – ankit Gupta Jan 25 '13 at 06:17

score 0 · Answer 3 · answered Jan 25 '13 at 11:02

IN DAL File

public class HomeDAL
{
  public void DefaultSearchFriends(ref HomeBAL hBAL)
  {
    SqlConnection SocialConn = new SqlConnection(connstr);
    using (SqlCommand comm = new SqlCommand("proc_FriendsSearch", SocialConn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@userid", hBAL.userid);
        SocialConn.Open();
        hBAL.Search_Reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
    }
  }
}

In BAL File

public class HomeBAL
{
public SqlDataReader Search_Reader = null;
}

and in Presentaion layer

 HomeBAL HBAL = new HomeBAL();
HomeDAL HDAL = new HomeDAL();
HDAL.DefaultSearchFriends(ref HBAL);
SqlDataReader dr = HBAL.Search_Reader;    
while (dr.Read())
{
}

How to pass datareader value from DAL to presentation layer

3 Answers3