How to pass datareader value from DAL to presentation layer
I am working on a project using a 3-tier architecture. I want to know how to pass datareader value from DAL to presentation layer
My code is like this. In the 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 the BAL layer
public class HomeBAL
{
public SqlDataReader DefaultSearchFriends(long userid)
{
HomeDAL HDAL = new HomeDAL();
SqlDataReader dr = HDAL.DefaultSearchFriends(userid);
return dr;
}
}
In the Presentationaion layer I wrote this on the load page
HomeBAL HBAL = new HomeBAL();
SqlDataReader dr = HBAL.DefaultSearchFriends(user_id);
while (dr.Read())
{
//some code
}
Now I want to know two things
1- Is it right to call datareader that way or is there better logic.
2- how to close datareader object in BAL layer and DAL layer.
source to share
Well, the main idea behind a layered architecture is to keep different components separate for several reasons. Some reasons are testability, maintainability, extensibility, but there are many more.
Passing data between these layers - well, it depends a little on the data type - but usually you should use some simple classes as data transfer objects (DTOs) that will be populated with data in the DAL. For example.
public class Person
{
public string Name {get; set;}
public string FirstName {get; set;}
...
}
With your approach, you break this idea because you are passing a layer DataReader
into the presentation, which implies that you cannot switch DAL technology without touching other layers. For example. if you want to use Entity Framework you will have to change every piece of code that you are currently using SqlDataReader
.
You can also see that if you are sticking with the idea of a tiered approach, you don't need to think about your second question.
Hope this helps a little.
EDIT
Ok, I'm a little curious that you haven't found the right solution. But in any case, the first and easiest approach might be, don't post SqlDataReader
. Process your lifecycle in DAL. Means you are 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 a much better approach.
source to share
I think your architecture has problems;
-
you are using concrete classes to query the database; instead, you need an abstraction if you change the SQL server or query engine.
-
you transfer concrete
SqlDataReader
to all your layers. From the DAL, you need to return data objects, not an instance of the db operation context. -
you only need to change the domain objects between the layers, not the object doing the actual work.
I suggest you refer to n reference application Microsoft spain app
source to share
In the 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 the BAL file
public class HomeBAL
{
public SqlDataReader Search_Reader = null;
}
and in the "Presentation" layer
HomeBAL HBAL = new HomeBAL();
HomeDAL HDAL = new HomeDAL();
HDAL.DefaultSearchFriends(ref HBAL);
SqlDataReader dr = HBAL.Search_Reader;
while (dr.Read())
{
}
source to share