Data link layer and best practice

Question

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.

Answer 1

You should have the following layers:

• database
• data access layer (a layer responsible to support each needed operation type for each table)
• business layer (a layer responsible for the algorithmic database operation combination)
• engine
• UI

Let's say you have a banking application and a user wants to sent money to another user, so he types the value, selects the other user and then clicks a button. This happens on the UI. The button click triggers the event in the engine. Event: sending money. The engine passes the task to the business layer. On the business layer the algorithm must be executed. Inside a transaction there are two operations: amount subtraction from your amount and addition of the same amount for the receiver. Then the business layer passes the tasks to the access layers, which in turn generate the SQL to be executed and send it to the database server.