public it_log GetById(int id)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
return(db.it_log.Find(id));
}
}
public List <it_log_all> SearchByPersonnel(int id)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
/*var result = db.Database.SqlQuery<IList>("SELECT l.it_log_id, l.name, " +
* "l.office, l.date, l.time, l.service_request, p.it_personnel_name " +
* "FROM it_log AS l INNER JOIN it_personnel AS p ON l.it_personnel_id = p.it_personnel_id" +
* "WHERE l.date BETWEEN DATE(@from) AND DATE(@to)",
* new SqlParameter("@from", from),
* new SqlParameter("@to", to)).ToList();
*/
var result = (from l in db.it_log
join p in db.it_personnel
on l.it_personnel_id equals p.it_personnel_id
where l.it_personnel_id == id
select new it_log_all
{
id = l.it_log_id,
Name = l.name,
Office = l.office,
Date = l.date,
Time = l.time,
Service_Request = l.service_request,
IT_Personnel = p.it_personnel_name
}).ToList();
return(result);
}
}
public void Update(it_log obj)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
db.it_log.Attach(obj);
db.Entry(obj).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public it_log Insert(it_log obj)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
db.it_log.Add(obj);
db.SaveChanges();
return(obj);
}
}
public void Delete(int id)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
//using (dbemployeeEntities db = new dbemployeeEntities())
//{
// db.Database.ExecuteSqlCommand("UPDATE employee_info SET lastname = 'SQLTEST' WHERE empID = 19");
//}
var delete = (from l in db.it_log
where l.it_log_id.Equals(id)
select l).FirstOrDefault();
db.it_log.Attach(delete);
db.it_log.Remove(delete);
db.SaveChanges();
}
}
public List <it_log_all> GetAll()
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
var itlog = (from l in db.it_log
join p in db.it_personnel
on l.it_personnel_id equals p.it_personnel_id
select new it_log_all
{
id = l.it_log_id,
Name = l.name,
Office = l.office,
Date = l.date,
Time = l.time,
Service_Request = l.service_request,
IT_Personnel = p.it_personnel_name
}).ToList();
return(itlog);
}
}
public List <it_log_all> Search(string text)
{
using (ittransactionlogEntities db = new ittransactionlogEntities())
{
var result = (from l in db.it_log
join p in db.it_personnel
on l.it_personnel_id equals p.it_personnel_id
where (l.name.Contains(text) || l.office.Contains(text) ||
l.service_request.Contains(text) || p.it_personnel_name.Contains(text))
select new it_log_all
{
id = l.it_log_id,
Name = l.name,
Office = l.office,
Date = l.date,
Time = l.time,
Service_Request = l.service_request,
IT_Personnel = p.it_personnel_name
}).ToList();
return(result);
}
}
