3

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);
  • 1
    You don't need if (sqlReader.HasRows) and return employees; after the using. You can just filter List<Employee> with LINQ. – paparazzo Jan 31 '17 at 8:36
  • @Paparazzi could you write some example using LINQ – SIRS Feb 1 '17 at 4:02
2

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

1

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
   }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.