public override List <object> SqlList(string sql, int limit = 0, int offset = 0)
{
List <object> categoryList = new List <object>();
dbEntities = new mpi_dbEntities();
var members = dbEntities.members
.SqlQuery(sql
).
Select(member => new
{
member
});
if (limit > 0)
{
members = members.Skip(offset * limit).Take(limit).ToList();
}
else
{
members = members.ToList();
}
foreach (var u in members)
{
member member = u.member;
categoryList.Add(member);
}
count = countSQL(sql, dbEntities.members);
return(categoryList);
}
public override List <object> SqlList(string sql, int limit = 0, int offset = 0)
{
List <object> categoryList = new List <object>();
dbEntities = new mpi_dbEntities();
var positions = dbEntities.positions
.SqlQuery(sql
).
Select(position => new
{
position
});
if (limit > 0)
{
positions = positions.Skip(offset * limit).Take(limit).ToList();
}
else
{
positions = positions.ToList();
}
foreach (var u in positions)
{
position position = u.position;
categoryList.Add(position);
}
count = countSQL(sql, dbEntities.positions);
return(categoryList);
}
public override BaseEntity GetById(object Id)
{
dbEntities = new mpi_dbEntities();
@event @event = (from o in dbEntities.events where o.id == (int)Id select o).SingleOrDefault();
return(@event);
}
public override BaseEntity GetById(object Id)
{
dbEntities = new mpi_dbEntities();
program program = (from c in dbEntities.programs where c.id == (int)Id select c).SingleOrDefault();
return(program);
}
public override List <object> SqlList(string sql, int limit = 0, int offset = 0)
{
List <object> categoryList = new List <object>();
dbEntities = new mpi_dbEntities();
var Events = dbEntities.events
.SqlQuery(sql
).
Select(@event => new
{
@event
});
if (limit > 0)
{
Events = Events.Skip(offset * limit).Take(limit).ToList();
}
else
{
Events = Events.ToList();
}
foreach (var u in Events)
{
@event @event = u.@event;
categoryList.Add(@event);
}
count = countSQL(sql, dbEntities.events);
return(categoryList);
}
public override BaseEntity GetById(object Id)
{
dbEntities = new mpi_dbEntities();
position position = (from c in dbEntities.positions where c.id == (int)Id select c).SingleOrDefault();
return(position);
}
public override BaseEntity GetById(object Id)
{
dbEntities = new mpi_dbEntities();
member member = (from c in dbEntities.members where c.id == (int)Id select c).SingleOrDefault();
return(member);
}
public override BaseEntity Add(object Obj)
{
position position = (position)Obj;
position.created_date = DateTime.Now;
dbEntities = new mpi_dbEntities();
position newposition = dbEntities.positions.Add(position);
try
{
dbEntities.SaveChanges();
return(newposition);
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
{
Exception raise = dbEx;
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
string message = string.Format("{0}:{1}",
validationErrors.Entry.Entity.ToString(),
validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
raise = new InvalidOperationException(message, raise);
}
}
throw raise;
// return null;
}
}
public List <division> GetByInsitutionId(int id)
{
List <object> ObjList = new List <object>();
dbEntities = new mpi_dbEntities();
var Sql = (from p in dbEntities.divisions where p.institution_id == id select p);
List <division> List = Sql.ToList();
return(List);
}
public override BaseEntity GetById(object Id)
{
try
{
dbEntities = new mpi_dbEntities();
division division = (from c in dbEntities.divisions where c.id == (int)Id select c).SingleOrDefault();
return(division);
}
catch (Exception ex)
{
return(null);
}
}
public override BaseEntity Update(object Obj)
{
dbEntities = new mpi_dbEntities();
member Member = (member)Obj;
member DBmember = (member)GetById(Member.id);
if (DBmember == null)
{
return(null);
}
dbEntities.Entry(DBmember).CurrentValues.SetValues(Member);
dbEntities.SaveChanges();
return(Member);
}
public override List <object> ObjectList(int offset, int limit)
{
dbEntities = new mpi_dbEntities();
List <object> ObjList = new List <object>();
var Sql = (from p in dbEntities.members orderby p.name select p);
List <member> List = Sql.Skip(offset * limit).Take(limit).ToList();
foreach (member c in List)
{
ObjList.Add(c);
}
count = dbEntities.members.Count();
return(ObjList);
}
public override BaseEntity Update(object Obj)
{
dbEntities = new mpi_dbEntities();
position position = (position)Obj;
position DBposition = (position)GetById(position.id);
if (DBposition == null)
{
return(null);
}
dbEntities.Entry(DBposition).CurrentValues.SetValues(position);
dbEntities.SaveChanges();
return(position);
}
public override BaseEntity Update(object Obj)
{
dbEntities = new mpi_dbEntities();
program program = (program)Obj;
program DBProgram = (program)GetById(program.id);
if (DBProgram == null)
{
return(null);
}
dbEntities.Entry(DBProgram).CurrentValues.SetValues(program);
dbEntities.SaveChanges();
return(program);
}
public override bool Delete(object Obj)
{
try
{
dbEntities = new mpi_dbEntities();
@event @event = (@event)Obj;
dbEntities.events.Remove(@event);
dbEntities.SaveChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public override bool Delete(object Obj)
{
try
{
dbEntities = new mpi_dbEntities();
position position = (position)Obj;
dbEntities.positions.Remove(position);
dbEntities.SaveChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public override bool Delete(object Obj)
{
try
{
dbEntities = new mpi_dbEntities();
member member = (member)Obj;
dbEntities.members.Remove(member);
dbEntities.SaveChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public override BaseEntity Update(object Obj)
{
dbEntities = new mpi_dbEntities();
@event @event = (@event)Obj;
@event.created_date = DateTime.Now;
@event DBEvent = (@event)GetById(@event.id);
if (DBEvent == null)
{
return(null);
}
dbEntities.Entry(DBEvent).CurrentValues.SetValues(@event);
dbEntities.SaveChanges();
return(@event);
}
public post findLatestPost()
{
dbEntities = new mpi_dbEntities();
Dictionary <string, object> filter = new Dictionary <string, object>();
filter.Add("orderby", "post.created_date");
filter.Add("ordertype", "DESC");
List <object> posts = SearchAdvanced(filter, 1, 0, false);
if (posts == null || posts.Count == 0)
{
return(null);
}
post Post = (post)posts[0];//.SingleOrDefault();
// return postObj.SingleOrDefault();
return(Post);
}
public List <BaseEntity> GetByMonthAndYear(int month, int year, int divisionId)
{
List <object> ObjList = new List <object>();
dbEntities = new mpi_dbEntities();
var Sql = (from e in dbEntities.events where
SqlFunctions.DatePart("month", e.date) == month &&
SqlFunctions.DatePart("year", e.date) == year &&
e.program.section.division_id == divisionId
select e);
List <@event> List = Sql.ToList();
List <BaseEntity> baseEnties = new List <BaseEntity>();
foreach (@event e in List)
{
baseEnties.Add(e);
}
return(baseEnties);
}
public user GetUserByUsernameAndPassword(string Username, string Password)
{
DebugConsole.Debug(this, "DB ENTITIES IS NULL: " + (dbEntities == null));
dbEntities = new mpi_dbEntities();
if (dbEntities == null || Username == null || Password == null)
{
return(null);
}
DebugConsole.Debug(this, dbEntities.users.ToString());
user User = (from u in dbEntities.users
where u.username.Equals(Username) && u.password.Equals(Password)
select u).SingleOrDefault();
if (User != null)
{
return(User);
}
return(null);
}
public override BaseEntity Update(object Obj)
{
dbEntities = new mpi_dbEntities();
section section = (section)Obj;
section DBsection = (section)GetById(section.id);
if (DBsection == null)
{
return(null);
}
dbEntities.Entry(DBsection).CurrentValues.SetValues(section);
try
{
dbEntities.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
return(null);
}
return(section);
}
public override BaseEntity Add(object Obj)
{
user user = (user)Obj;
if (user.admin == null)
{
user.admin = 1;
}
dbEntities = new mpi_dbEntities();
user newUser = dbEntities.users.Add(user);
try
{
dbEntities.SaveChanges();
return(newUser);
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
{
Exception raise = dbEx;
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
string message = string.Format("{0}:{1}",
validationErrors.Entry.Entity.ToString(),
validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
raise = new InvalidOperationException(message, raise);
}
}
throw raise;
// return null;
}
}
