public List<Employee> getAllEmployee(Employee emp, String sortColumn,Boolean asc,Boolean exactFilter)
{
List<Employee> result = new List<Employee>();
MySqlTransaction tr = null;
MySqlDataAdapter rdr = null;
String sql = "SELECT * FROM EMPLOYEE WHERE 1=1 ";
Dictionary<String, String> paramDic = new Dictionary<String, String>();
if(emp != null)
{
String strFilter = emp.getStrFilter();
if (!String.IsNullOrWhiteSpace(strFilter))
{
if (exactFilter)
{
sql += " AND E_NAME = @NAME ";
paramDic.Add("@NAME",strFilter);
}
else
{
sql += " AND E_NAME LIKE '% @NAME %' ";
paramDic.Add("@NAME",strFilter);
}
}
}
if(sortColumn != null)
{
String sort = (asc == true? "ASC":"DSC");
sql += " ORDER BY @COLUMN @SORT ";
paramDic.Add("@COLUMN",sortColumn);
paramDic.Add("@SORT", sort);
}
try
{
conn = getConnection();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tr;
cmd.CommandText = sql;
foreach (KeyValuePair<String, String> pair in paramDic)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
rdr = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
rdr.Fill(dt);
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
}
finally
{
if(rdr != null)
{
rdr.Close();
}
DBUtil.CloseConnection(conn);
}
return result;
}