SqlDataReader and 3-tier

[TomislaW]

how to pass values betwen data and busines tier using
SqlDataReader. I was thinking about ArrayLists but what if I have number of
rows and columns?

 

[Eliahu]

..net PetShop example from Microsoft does use ArrayList for implementation
....
in any case it's better than the DataSet

 

[Karl Seguin]

public class User{
private int userId;
private UserStatus status;
private string userName;

public int UserId{ get { return userId; } set {userId = value}}
public UserStatus Status{ get { return status; } set {status= value}}
public string UserName{ get { return userName; } set {value= value}}

public User(){}
...
}


your DAL:

public User GetUserById(int userId){
SqlConnection connection ...
SqlCommand command ...
command.Parameters.Add ...

try{
conn.open()
SqlDataReader dr = command.ExecuteReader(SingleRow);
User u = null;
if (dr.Read()){
u = PopulateUserFromIDataRecord(dr);
}
dr.close();
return u;
catch{...}finally{ CLEANUP }
}

public static User PopulateUserFromIDataRecord(IDataRecord dr){
User u = new User();
u.UserId = Convert.ToInt32(dr["userId"], CultureInfo.InvariantCulture);
u.Status = (UserStatus)Convert.ToInt32(dr["status"],
CultureInfo.InvariantCulture);
u.UserName= Convert.ToString(dr["username"], CultureInfo.CurrentCulture);
return u;
}

You could/should also create a UserCollection for when you want to deal with
more than 1 user...

Alternatively, you can do O/R mapping (http://www.ormapper.net/)

Karl

 

[Bliss]

how to pass values betwen data and busines tier using
SqlDataReader. I was thinking about ArrayLists but what if I have number of
rows and columns?

Generally speaking and correct me if I am wrong. I return the
SqlDataReader back from the data access layer and mess with it in the
service layer. I think the approach of using the DAO layer for
anything more then running querys and binding params and other low
level things is not good practice.

How I have done it in .net and java is to control the connection in
the service layer i.e. open it. When I create a DAO objerct i pass the
open connection to it it will retrive the ssqldatareader needed and
return as is to the service layer. Ther service layer can then run
buiz rules and clean up the connection. Works great for me.

I will usally creat ValueHolder objects to hold the data needed from
the reader. For example if I am getting employees from a DB I have a
employee.cs that has get and set props. I then can use this object in
the view.