There are two SqlHelper classes and a DishesTypes class used as DAL

public class SqlHelper{ public static SqlDataReader ExecuteReader(string procedure, params SqlParameter[] commandParameters){ using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) using (var command = new SqlCommand(procedure, _connection)){ command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(commandParameters); return command.ExecuteReader(); } } } } public class DishesTypes{ public static SqlDataReader DishesTypesSelectAll(){ return SqlHelper.ExecuteReader("DishesTypesSelectAllRows");//название хранимой процедуры } }

And there is a class DishedTypes which is used as BLL

 public class DishesTypes { public int DishTypeId { get; set; } public string DishType { get; set; } public static List<DishesTypes> DishesTypesSelectAll() { IDataReader dr = DataAccessLayer.DishesTypes.DishesTypesSelectAll(); List<DishesTypes> dishesTypesList = new List<DishesTypes>(); while (dr.Read()) { DishesTypes myDishesTypes = new DishesTypes { DishTypeId = (int)dr["DishTypeId"], DishType = (string)dr["DishType"] }; dishesTypesList.Add(myDishesTypes); } return dishesTypesList; } }

An error occurs during the execution of this code section.

 while (dr.Read())

The reason for this is that the connection is already closed at this point and you need to reopen it. What is the best way to change the implementation of classes by adhering to the DAL and BLL layers in order for it to work?

  • Return a DataTable . For SqlDataReader you need an open connection. - Igor

2 answers 2

DAL as a rule always returns data already ready for operation. Those. it opens a connection, a request is executed, the connection is closed, and the data is converted to some kind. These can be objects (POCO / DTO) - you can immediately return a collection of objects of type DishesTypes . It can also be a DataTable .

After you execute the ExecuteReader method ExecuteReader connection is closed, so an attempt to read data from the SqlDataReader fails. Populate a collection of objects / table with data inside the ExecuteReader method.

    When you first look at this code, the question arises: if DishesTypes refers not to the data layer, but to business logic, then why is it engaged in extracting data from the reader? And why should business logic work with IDataReader, an entity that, in theory, should not go beyond DAL? I think it makes sense for you to transfer all the logic related to getting data from sql to the inside of the DAL, and from it to return already constructed objects. In other words, the ExecuteReader method should not return a SqlDataReader, but an already constructed object (in particular, DishesTypes). It is possible that DishesTypes is not the only class whose instances you want to receive from the query results. Therefore, you can make a generalized solution. For example, something like:

     public class SqlHelper { public static List<T> GetData<T>(string procedure, Func<SqlDataReader, T> convertor, params SqlParameter[] commandParameters) { using (var connection = new SqlConnection(ConnectionString)) using (var command = new SqlCommand(procedure, connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(commandParameters); var res = new List<T>(); var reader = command.ExecuteReader(); while (reader.Read()) { res.Add(convertor(reader)); } return res; } } } public class DishesTypes { public static List<DishesTypes> DishesTypesSelectAll() { Func<SqlDataReader, DishesTypes> convertor = // ф-ция, конвертирующая данные из ридера в DishesTypes return SqlHelper.GetData<DishesTypes>("DishesTypesSelectAllRows", convertor); } }