locked
Data link layer and best practice RRS feed

  • Question

  • User-1683372009 posted

    I need to know the best practices in the following approach. I have an application which is using layered architecture.In data layer, I am having two classes like below.

    A class to convert the datatable to List with corresponding business object.

    public List<User> GetUser()
            {
                List<User> user = new  List<User>();
                DataTable dtLoginDetails = new DataTable();
                string selectQuery = "SELECT * FROM user_details";
               try
                {
    
                    dtUserDetails = connection.SelectCommand(selectQuery); // call the function in another class
                }
                catch (MySqlException mx)
                {
                    throw;
                }
                if (dtUserDetails.Rows.Count > 0)
                {
                   //loops through datatable 'dtUserDetails' and converts to List
                }
                return user;            
            }

    Another class to do all the database operations.

    public DataTable SelectCommand(String query)
            {
    
                    DataTable dtSelect = new DataTable();
                    try
                    {
                        MySqlCommand command = ConnecttoDB().CreateCommand();
                        command.CommandText = query;
                        MySqlDataAdapter dtAdapter = new MySqlDataAdapter(command);
                        dtAdapter.Fill(dtSelect);
                        DisconnecttoDB();
                    }
                    catch (MySqlException mx)
                    {
                        throw;
                    }
                    finally
                    {
                        DisconnecttoDB(); 
                    }
                    return dtSelect;
            }

    So I just want to know whether this is a good approach or I should write something like below.

    public List<Person> Read()
    
            {
                con.ConnectionString = ConString;
                if (ConnectionState.Closed == con.State)
                    con.Open();
                SqlCommand cmd = new SqlCommand("select * from Person",con);
                try
                {
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        objP = new Person();
                        objP.ID = Convert.ToInt16(rd.GetValue(0));
                        objP.name = rd.GetString(1);
                        objP.surname = rd.GetString(2);
                        List.Add(objP);
                    }
    
                    return List;
                }
                catch
    
                {
                    throw;
    
                }
            }

    In this case I don't have separate class to do database operations, but I need to write different function with separate sql query which return different business object.

    Thanks.

    Thursday, August 7, 2014 10:49 PM

Answers

  • User-821857111 posted

    The second approach will be more efficient as it uses a DataReader. You could use Generics and reflection to make the method work for hydrating any type from a DataReader, but then you are getting into the world of ORM writing. In that case, my advice would be just to ditch your own data layer and use Entity Framework. These days. with the maturing of EF, there is little justification for writing your own data access layer - except as a learning exercise.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 8, 2014 2:25 AM

All replies

  • User-821857111 posted

    The second approach will be more efficient as it uses a DataReader. You could use Generics and reflection to make the method work for hydrating any type from a DataReader, but then you are getting into the world of ORM writing. In that case, my advice would be just to ditch your own data layer and use Entity Framework. These days. with the maturing of EF, there is little justification for writing your own data access layer - except as a learning exercise.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 8, 2014 2:25 AM
  • User-1611549905 posted

    Just one extra point here: don't wrap your method bodies in a try { ... } catch { throw; } block. It doesn't have any benefit and just adds noise and causes confusion.

    Friday, August 8, 2014 4:35 AM