What is the preferred way to get data from database for data that frequently used?

Question

Says, I have employee table, with {id, name, unit, etc..} what is the preferred ways to get this data if I frequently use this data, my first option is to query it from database when needed, something like this,

public static List<Employee> GetEmployees(string unitID)
{
    List<Employee> employees = new List<Employee>();

    using (SqlConnection sqlConn = new SqlConnection(string.Format(constring, UserID, Password, Server, Database)))
    {
        using (SqlCommand sqlComm = new SqlCommand(string.Format("SELECT ID, Name FROM Employee WHERE UnitID = {0} ORDER BY Name", unitID), sqlConn))
        {
            try
            {
                sqlConn.Open();
                using (SqlDataReader sqlReader = sqlComm.ExecuteReader())
                {
                    if (sqlReader.HasRows)
                    {
                        while (sqlReader.Read())
                        {
                            Employee employee = new Employee()
                            {
                                ID= sqlReader["ID"].ToString(),
                                Name = StaticField.id_ID.TextInfo.ToTitleCase(sqlReader["Name"].ToString())
                            };
                            employees.Add(employee);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(ex.Message, "GetEmployees Error", MessageBoxButton.OK, MessageBoxImage.Stop);
            }

            return employees;
        }
    }
}

Or second option, create DataSet, then add employee table as TableAdapter on the dataset.

var employeeListTableAdapter = new DataSetEmployeeTableAdapters.EmployeeListTableAdapter();
employeeListTableAdapter.ClearBeforeFill = true;
employeeListTableAdapter.Fill(dataset.EmployeeList);

then when I need to filter the row, I just use something like this.

DataView data = dataset.EmployeeList.DefaultView;
data.RowFilter = string.Format("Unit = {0}", unitID);

Answer 1

To make this really flexible and transparent, you need an interface and a couple of concrete classes implementing the interface, and eschew static classes and methods for true object oriented programming:

public interface IEmployeeRepository
{
    IEnumerable<Employee> FindByUnit(int unitId);
    Employee Find(int id);
}

I'm adding a Find method to the interface to illustrate later the difficulty of caching these objects.

Now, the EmployeeRepository class implements the IEmployeeRepository interface, which queries the database each time:

public class EmployeeRepository : IEmployeeRepository
{
    public EmployeeRepository(string connectionString)
    {
        if (string.IsNullOrEmpty(connectionString))
            throw new ArgumentNullException("connectionString");

        this.connectionString = connectionString;
    }

    private string connectionString;

    public Employee Find(int id)
    {
        // Query by Id and return a single Employee
    }

    public IEnumerable<Employee> FindByUnit(int unitId)
    {
        // Query the database and return the list of employees
    }
}

Now some code that uses the concrete class and interface:

IEmployeeRepository repository = new EmployeeRepository("...");

IEnumerable<Employee> employees = repository.FindByUnit("3");

This brings you to where you currently are. The database gets queried each time. Now we can introduce some caching:

public class CachedEmployeeRepository : IEmployeeRepository
{
    public CachedEmployeeRepository(IEmployeeRepository employees)
    {
        if (employees == null)
            throw new ArgumentNullException("employees");

        this.employees = employees;
        this.cacheById = new Dictionary<int, Employee>();
        this.cacheByUnitId = new Dictionary<int, Employee>();
    }

    private Dictionary<int, Employee> cacheById;

    private Dictionary<int, IEnumerable<Employee>> cacheByUnitId;

    public Employee Find(int id)
    {
        Employee employee = null;

        if (cacheById.ContainsKey(id))
        {
            employee = employees.Find(id);

            if (employee != null)
            {
                cacheById[id] = employee;
            }
        }
        else
        {
            employee = cacheById[id];
        }

        return employee;
    }

    public IEnumerable<Employee> FindByUnit(int unitId)
    {
        if (cacheByUnitId.ContainsKey(unitId))
        {
            return cacheByUnitId[unitId];
        }

        IEnumerable<Employee> results = employees.FindByUnit(unitId);

        if (results.Any())
        {
            cacheByUnitId[unitId] = results;
        }

        return results;
    }
}

The CachedEmployeeRepository takes an EmployeeRepository in its constructor, and implements the IEmployeeRepository interface. Now you can introduce caching seamlessly in your application:

IEmployeeRepository repository = new CachedEmployeeRepository(
    new EmployeeRepository("..."));

IEnumerable<Employee> employees = repository.FindByUnit("3");

The rest of your code doesn't even know, or need to know, that caching is happening.

But be careful with caching. Notice the finding an employee by Id and a list of them by unit Id has different caches, and therefor different instances of the Employee class.

There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. - Martin Fowler

Answer 2

You can implement a simple cache like this:

public class DatabaseHelper
{
    static private Dictionary<string,List<Employee>> _lookup = new Dictionary<string, List<Employee>>();

    static public List<Employee> GetEmployees(string unitID, bool refresh)
    {
        List<Employee> list;

        if (!refresh)
        {            
            bool exists = _lookup.TryGetValue(unitID, out list);
            if (exists) return list;
        }

        list = GetEmployees(unitID);
        _lookup[unitID] = list;
        return list;
    }

   static private List<Employee> GetEmployees(string unitID)
   {
       //You already wrote this part
   }
}