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