//Consultations and specific operations we will put here
public override int Insert(GeoLocalizationEntity _geoLocalization)
{
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
//string[] localLabes = _geoLocalization.LocalName.Split(' ');
//var twoLabel = (from i in localLabes
// orderby i descending
// select i).Take(3);
//var localName = string.Join(" ", twoLabel);
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.CommandText = "PROC_INSERT_GEOLOCALIZATION";
dataCommand.Parameters.AddWithValue("@P_EmployeeID", _geoLocalization.EmployeeID);
dataCommand.Parameters.AddWithValue("@P_LocalName", _geoLocalization.LocalName);
dataCommand.Parameters.AddWithValue("@P_Lat", _geoLocalization.Lng);
dataCommand.Parameters.AddWithValue("@P_Long", _geoLocalization.Lat);
dataCommand.Parameters.Add(new MySqlParameter("@P_int_Identity", MySqlDbType.Int64));
dataCommand.Parameters["@P_int_Identity"].Direction = ParameterDirection.Output;
dataCommand.Parameters.Add(new MySqlParameter("@P_Return_Message", MySqlDbType.VarChar));
dataCommand.Parameters["@P_Return_Message"].Direction = ParameterDirection.Output;
try
{
dataCommand.Connection = conn;
conn.Open();
dataCommand.ExecuteNonQuery();
var id = 0;
var errorCode = dataCommand.Parameters["@P_Return_Message"].Value.ToString();
if (errorCode != string.Empty)
{
Debug.WriteLine(id.ToString() + errorCode);
}
else
{
id = Convert.ToInt32(dataCommand.Parameters["@P_int_Identity"].Value.ToString());
}
return(id);
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
}
public override CompanyEntity FindByID(int id)
{
IList <CompanyEntity> list = new List <CompanyEntity>();
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandText = "SELECT CompanyID" +
",CompanyType" +
",Name " +
",Address " +
",PostCode" +
",State" +
",Country" +
",Email" +
",WebSite" +
",Phone" +
",DateCreated " +
"FROM Company " +
"Where CompanyID = @CompanyID ";
dataCommand.Parameters.AddWithValue("@CompanyID", id);
dataCommand.CommandType = CommandType.Text;
try
{
dataCommand.Connection = conn;
conn.Open();
var reader = dataCommand.ExecuteReader();
if (reader.HasRows)
{
list = Mapper.DataReaderMapToList <CompanyEntity>(reader);
}
reader.Close();
}
catch (SqlException e)
{
Debug.WriteLine(string.Format("Error: {0} ", e.Message));
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(list.FirstOrDefault());
}
public override string Update(EmployeeEntity _employee)
{
var returnMessage = "";
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.CommandText = "PROC_UPDATE_EMPLOYEE";
dataCommand.Parameters.AddWithValue("@P_CompanyID", _employee.TCompany.CompanyID);
dataCommand.Parameters.AddWithValue("@P_LastName", _employee.LastName);
dataCommand.Parameters.AddWithValue("@P_FirstName", _employee.FirstName);
dataCommand.Parameters.AddWithValue("@P_UserName", _employee.UserName);
dataCommand.Parameters.AddWithValue("@P_Email", _employee.Email);
dataCommand.Parameters.AddWithValue("@P_Password", _employee.Password);
dataCommand.Parameters.AddWithValue("@P_RoleID", _employee.RoleID);
dataCommand.Parameters.AddWithValue("@P_EmployeeID", _employee.EmployeeID);
dataCommand.Parameters.Add(new MySqlParameter("@P_Return_Message", MySqlDbType.VarChar));
dataCommand.Parameters["@P_Return_Message"].Direction = ParameterDirection.Output;
try
{
dataCommand.Connection = conn;
conn.Open();
dataCommand.ExecuteNonQuery();
var errorCode = dataCommand.Parameters["@P_Return_Message"].Value.ToString();
if (errorCode != string.Empty)
{
returnMessage = "Forbidden : <br>" + errorCode;
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(returnMessage);
}
public override IEnumerable <EmployeeEntity> GetAllByCompany(EmployeeEntity _employee)
{
IList <EmployeeEntity> list = new List <EmployeeEntity>();
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandText = "SELECT CompanyID " +
",EmployeeID " +
",LastName " +
",FirstName " +
",UserName " +
",Email " +
",Password " +
",RoleID " +
",DateCreated " +
"FROM Employee " +
"where CompanyID = @CompanyID " +
"LIMIT 100";
dataCommand.Parameters.AddWithValue("@CompanyID", _employee.TCompany.CompanyID);
dataCommand.CommandType = CommandType.Text;
try
{
dataCommand.Connection = conn;
conn.Open();
var reader = dataCommand.ExecuteReader();
if (reader.HasRows)
{
list = Mapper.DataReaderMapToList <EmployeeEntity>(reader);
}
reader.Close();
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(list);
}
public override string Update(GeoLocalizationEntity _geoLocalization)
{
var returnMessage = "";
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.CommandText = "PROC_UPDATE_GEOLOCALIZATION";
dataCommand.Parameters.AddWithValue("@P_GeoLocalizationID", _geoLocalization.GeoLocalizationID);
dataCommand.Parameters.AddWithValue("@P_EmployeeID", _geoLocalization.EmployeeID);
dataCommand.Parameters.AddWithValue("@P_LocalName", _geoLocalization.LocalName);
dataCommand.Parameters.AddWithValue("@P_Lat", _geoLocalization.Lng);
dataCommand.Parameters.AddWithValue("@P_Long", _geoLocalization.Lat);
dataCommand.Parameters.Add(new MySqlParameter("@P_Return_Message", MySqlDbType.VarChar));
dataCommand.Parameters["@P_Return_Message"].Direction = ParameterDirection.Output;
try
{
dataCommand.Connection = conn;
conn.Open();
dataCommand.ExecuteNonQuery();
var errorCode = dataCommand.Parameters["@P_Return_Message"].Value.ToString();
if (errorCode != string.Empty)
{
returnMessage = "Forbidden : <br>" + errorCode;
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(returnMessage);
}
public EmployeeEntity FindByUserName(EmployeeEntity _employee)
{
IList <EmployeeEntity> list = new List <EmployeeEntity>();
using (var conn = new DBLoadMySql().cnn)
{
try
{
conn.Open();
/* Dapper */
var query =
@"SELECT em.* FROM Employee em Where em.UserName = @UserName and em.Password = @Password and em.CompanyID = @CompanyID";
var oPara = new DynamicParameters();
oPara.Add("@UserName", _employee.UserName, DbType.String);
oPara.Add("@Password", _employee.Password, DbType.String);
oPara.Add("@CompanyID", _employee.TCompany.CompanyID, DbType.Int32);
var employeeFound = conn.Query <EmployeeEntity>(query, oPara);
var subsetquery =
@"select co.* from Company co where co.CompanyID = @Id";
foreach (var item in employeeFound)
{
var oChild = new DynamicParameters();
oChild.Add("@Id", item.CompanyID, DbType.Int32);
using (var multi = conn.QueryMultiple(subsetquery, oChild, commandType: CommandType.Text))
{
item.TCompany = multi.Read <CompanyEntity>().FirstOrDefault();
}
}
list = employeeFound.ToList();
}
finally
{
conn.Dispose();
}
return(list.FirstOrDefault());
}
}
public override GeoLocalizationEntity FindByID(GeoLocalizationEntity _geoLocalization)
{
IList <GeoLocalizationEntity> list = new List <GeoLocalizationEntity>();
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandText = "SELECT GeoLocalizationID " +
",EmployeeID" +
",LocalName " +
",Lat " +
",Lng " +
"FROM GeoLocalization " +
"Where GeoLocalizationID = @GeoLocalizationID";
dataCommand.Parameters.AddWithValue("@GeoLocalizationID", _geoLocalization.GeoLocalizationID);
dataCommand.CommandType = CommandType.Text;
try
{
dataCommand.Connection = conn;
conn.Open();
var reader = dataCommand.ExecuteReader();
if (reader.HasRows)
{
list = Mapper.DataReaderMapToList <GeoLocalizationEntity>(reader);
}
reader.Close();
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(list.FirstOrDefault());
}
public override IEnumerable <GeoLocalizationEntity> GetAllByCompany(GeoLocalizationEntity _geoLocalization)
{
IList <GeoLocalizationEntity> list = new List <GeoLocalizationEntity>();
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandText = "SELECT GeoLocalizationID " +
",EmployeeID" +
",LocalName " +
",Lat " +
",Lng " +
"FROM GeoLocalization ";
//"LIMIT 100";
dataCommand.CommandType = CommandType.Text;
try
{
dataCommand.Connection = conn;
conn.Open();
var reader = dataCommand.ExecuteReader();
if (reader.HasRows)
{
list = Mapper.DataReaderMapToList <GeoLocalizationEntity>(reader);
}
reader.Close();
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(list);
}
public override string Delete(GeoLocalizationEntity _geoLocalization)
{
var returnMessage = "";
using (var conn = new DBLoadMySql().cnn)
{
using (var dataCommand = new MySqlCommand())
{
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.CommandText = "PROC_DELETE_GEOLOCALIZATION";
dataCommand.Parameters.AddWithValue("@P_EmployeeID", _geoLocalization.EmployeeID);
dataCommand.Parameters.AddWithValue("@P_GeoLocalizationID", _geoLocalization.GeoLocalizationID);
dataCommand.Parameters.Add(new MySqlParameter("@P_Return_Message", MySqlDbType.VarChar));
dataCommand.Parameters["@P_Return_Message"].Direction = ParameterDirection.Output;
try
{
dataCommand.Connection = conn;
conn.Open();
dataCommand.ExecuteNonQuery();
var errorCode = dataCommand.Parameters["@P_Return_Message"].Value.ToString();
if (errorCode != string.Empty)
{
returnMessage = "Forbidden : <br>" + errorCode;
}
}
finally
{
dataCommand.Dispose();
conn.Dispose();
}
}
}
return(returnMessage);
}