public static bool Delete(string id)
{
using (Command cmd = new Command("delete_"+Selection.Entity))
{
try
{
if (SelectionGateway.Delete(cmd, id))
{
cmd.Commint();
return true;
}
cmd.RollBack();
throw new DeleteBaseObjException(Selection.Label, id);
}
catch (DeleteBaseObjException)
{
throw;
}
catch
{
cmd.RollBack();
throw new DeleteBaseObjException(Selection.Label, id);
}
}
}
public static bool Insert(IDictionary<string, object> values)
{
if(!values.ContainsKey("Score"))
values.Add("Score",null);
using (Command cmd=new Command("insert_"+Selection.Entity))
{
try
{
if( SelectionGateway.Insert(cmd, values))
{
cmd.Commint();
return true;
}
cmd.RollBack();
throw new InsertBaseObjException(Selection.Label,Convert.ToString(values["Id"]));
}
catch (InsertBaseObjException)
{
throw;
}
catch
{
cmd.RollBack();
throw new InsertBaseObjException(Selection.Label, Convert.ToString(values["Id"]));
}
}
}
public static bool Insert(IDictionary<string, object> values)
{
using (Command cmd = new Command("insert_Course"))
{
try
{
if(CourseGateway.Insert(cmd, values))
{
cmd.Commint();
return true;
}
cmd.RollBack();
throw new InsertBaseObjException(Course.Label, Convert.ToString(values["Name"]));
}
catch (InsertBaseObjException)
{
throw;
}
catch
{
cmd.RollBack();
throw new InsertBaseObjException(Course.Label, Convert.ToString(values["Name"]));
}
}
}
public static IUser Get(Command cmd, string id, Role role)
{
string table = null;
string columns = null;
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
columns = string.Join(",", Administrator.Properties.Select(p => p.Name).ToArray());
break;
case Role.Teacher:
table = Teacher.Table;
columns = string.Join(",", Teacher.Properties.Select(p => p.Name).ToArray());
break;
case Role.Student:
table = Student.Table;
columns = string.Join(",", Student.Properties.Select(p => p.Name).ToArray());
break;
}
string sql = string.Format(@"select {0} from {1} where lower(Id)=lower(@Id)", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
SqlCeDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
IUser user = ReaderFactory.Reader(reader, role.ToEntity()) as IUser;
reader.Close();
return user;
}
reader.Close();
return null;
}
public static ICourse Get(string id)
{
using (Command cmd = new Command("Get"))
{
ICourse course = CourseGateway.Get(cmd, id);
return course;
}
}
public static bool Delete(Command cmd,string id)
{
string table = Selection.Table;
string sql = string.Format(@"delete from {0} where Id=@Id", table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static int CountByCourse(Command cmd,string courseId)
{
string table = Selection.Table;
string sql = string.Format(@"select count(*) from {0} where lower(Course_Id)=lower(@Course_Id)", table);
cmd.CommandText = sql;
cmd.AddParameter("@Course_Id", courseId);
object count = cmd.ExecuteScalar();
return (count == null || count is DBNull) ? 0 : Convert.ToInt32(count);
}
public static ISelection Get(string courseId,string studentId)
{
using (Command cmd = new Command("Get_" + Selection.Entity))
{
try
{
return SelectionGateway.Get(cmd, courseId,studentId);
}
catch
{
return null;
}
}
}
public static bool IsExisting(string name)
{
using (Command cmd = new Command("Get"))
{
try
{
ICourse course = CourseGateway.GetByName(cmd, name);
return course != null;
}
catch
{
return false;
}
}
}
public static bool Insert(Command cmd,IDictionary<string,object> values)
{
string table = Selection.Table;
string columns = string.Join(",", values.Keys.Select(p => p != "Id" && p != "Score" ? p + "_Id" : p).ToArray());
string paras = string.Join(",", values.Keys.Select(p => "@" + (p != "Id" && p != "Score" ? p + "_Id" : p)).ToArray());
string sql = string.Format(@"insert {1} ({0}) values ({2})", columns, table, paras);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
if (field != "Id" && field != "Score")
cmd.AddParameter("@" + field+"_Id", values[field]);
else
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static ICourse GetByName(Command cmd,string name)
{
string table = Course.Table;
string columns = string.Join(",", Course.Properties.Select(p => p.Name == "Teacher" ? p.Name + "_Id" : p.Name).ToArray());
string sql = string.Format(@"select {0} from {1} where lower(Name)=lower(@Name)", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@Name", name);
SqlCeDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
ICourse course = ReaderFactory.Reader(reader, Course.Entity) as ICourse;
reader.Close();
return course;
}
reader.Close();
return null;
}
public static ISelection Get(Command cmd, string id)
{
string table = Selection.Table;
string columns = string.Join(",", Selection.Properties.Select(p => p.Name != "Id" && p.Name != "Score" ? p.Name + "_Id" : p.Name).ToArray());
string sql = string.Format(@"select {0} from {1} where lower(Id)=lower(@Id)", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
SqlCeDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
ISelection course = ReaderFactory.Reader(reader, Selection.Entity) as ISelection;
reader.Close();
return course;
}
reader.Close();
return null;
}
public static IList<ICourse> List(Command cmd)
{
string table = Course.Table;
string columns = string.Join(",", Course.Properties.Select(p => p.Name == "Teacher" ? p.Name + "_Id" : p.Name).ToArray());
string sql = string.Format(@"select {0} from {1}", columns, table);
cmd.CommandText = sql;
SqlCeDataReader reader = cmd.ExecuteReader();
IList<ICourse> courses = new List<ICourse>();
while (reader.Read())
{
ICourse course = ReaderFactory.Reader(reader, Course.Entity) as ICourse;
if (course != null)
courses.Add(course);
}
reader.Close();
return courses;
}
public static bool Delete(Command cmd, Role role, string id)
{
string table = null;
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"delete from {0} where Id=@Id", table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static IUser Get(string id, Role role)
{
using (Command cmd = new Command("get_" + role.ToEntity()))
{
try
{
IUser user = UserGateway.Get(cmd, id, role);
if (user == null)
throw new NotFoundBaseObjException(role.ToLabel(),id);
return user;
}
catch (NotFoundBaseObjException)
{
throw;
}
catch
{
throw new NotFoundBaseObjException(role.ToLabel(), id);
}
}
}
public static bool Delete(Role role, string id)
{
using (Command cmd = new Command("delete_" + role.ToEntity()))
{
try
{
bool result = UserGateway.Delete(cmd, role, id);
if (!result)
throw new DeleteBaseObjException(role.ToLabel(), id);
cmd.Commint();
return true;
}
catch (DeleteBaseObjException)
{
cmd.RollBack();
throw;
}
catch
{
cmd.RollBack();
throw new DeleteBaseObjException(role.ToLabel(), id);
}
}
}
public static IUser Authenticate(string id, string pwd, Role role)
{
using (Command cmd = new Command("auth"))
{
try
{
IUser user = UserGateway.Authenticate(cmd, id, pwd, role);
if (user == null)
throw new FailAuthException();
cmd.Commint();
return user;
}
catch (AuthenticationException)
{
cmd.RollBack();
throw;
}
catch
{
cmd.RollBack();
throw new FailAuthException();
}
}
}
public static bool Delete(string id)
{
using (Command cmd = new Command("delete_Course"))
{
try
{
bool result = CourseGateway.Delete(cmd, id);
if (!result)
throw new DeleteBaseObjException(Course.Label, id);
cmd.Commint();
return true;
}
catch (DeleteBaseObjException)
{
cmd.RollBack();
throw;
}
catch
{
cmd.RollBack();
throw new DeleteBaseObjException(Course.Label, id);
}
}
}
public static bool Update(Command cmd, Role role, IDictionary<string, object> values)
{
string table = null;
string cals = string.Join(",", values.Keys.Where( p=>p!="oId").Select(p => p + "=" + "@" + p).ToArray());
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"update {1} set {0} where Id=@oId", cals, table);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static IList<IUser> List(Command cmd, Role role)
{
string table = null;
string columns = null;
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
columns = string.Join(",", Administrator.Properties.Select(p => p.Name).ToArray());
break;
case Role.Teacher:
table = Teacher.Table;
columns = string.Join(",", Teacher.Properties.Select(p => p.Name).ToArray());
break;
case Role.Student:
table = Student.Table;
columns = string.Join(",", Student.Properties.Select(p => p.Name).ToArray());
break;
}
string sql = string.Format(@"select {0} from {1}", columns, table);
cmd.CommandText = sql;
SqlCeDataReader reader = cmd.ExecuteReader();
IList<IUser> users = new List<IUser>();
while (reader.Read())
{
IUser user = ReaderFactory.Reader(reader, role.ToEntity()) as IUser;
if (user != null)
users.Add(user);
}
reader.Close();
return users;
}
public static bool Insert(Command cmd, Role role, IDictionary<string, object> values)
{
string table = null;
string columns = string.Join(",", values.Keys.Select(p => p).ToArray());
string paras = string.Join(",", values.Keys.Select(p => "@" + p).ToArray());
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"insert {1} ({0}) values ({2})", columns, table, paras);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static IList<ICourse> List()
{
using (Command cmd=new Command("list_Course"))
{
try
{
return CourseGateway.List(cmd);
}
catch
{
return new List<ICourse>();
}
}
}
public static bool Update(string courseId, string studentId, int score)
{
ISelection target = Get(courseId, studentId);
using (Command cmd=new Command("update_"+Selection.Entity))
{
try
{
IDictionary<string,object> values=new Dictionary<string, object>();
values.Add("Score",score);
values.Add("oId",target.Id);
return SelectionGateway.Update(cmd, values);
}
catch
{
cmd.RollBack();
return false;
}
}
}
public static bool IsExisting(string courseId,string studentId)
{
using (Command cmd=new Command("isExisting_"+Selection.Entity))
{
try
{
ISelection item = SelectionGateway.Get(cmd, courseId,studentId);
return item != null;
}
catch
{
return false;
}
}
}
public static IList<IStudent> ListStudentsByCourse(string courseId)
{
using (Command cmd=new Command("listByCourse_"+Selection.Entity))
{
try
{
return SelectionGateway.ListStudentsByCourse(cmd, courseId);
}
catch
{
return new List<IStudent>();
}
}
}
public static IList<ICourse> ListByTeacher(string teacherId)
{
using(Command cmd=new Command("listByTeacher_Course"))
{
try
{
return CourseGateway.ListByTeacher(cmd, teacherId);
}
catch
{
return new List<ICourse>();
}
}
}
public static IBaseObject Reader(SqlCeDataReader reader, string entity)
{
Assembly entityImpl = Assembly.GetAssembly(typeof (BaseObject));
string entityName = entityImpl.FullName.Substring(0, entityImpl.FullName.IndexOf(",")).Trim() + "." + entity;
Type type = entityImpl.GetType(entityName);
object obj = entityImpl.CreateInstance(entityName);
foreach (PropertyInfo p in type.GetProperties())
{
try
{
int index;
object value;
if (p.PropertyType == typeof(ITeacher))
{
index = reader.GetOrdinal("Teacher_Id");
using (Command cmd = new Command("init_Teacher"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = UserGateway.Get(cmd, id, Role.Teacher) as ITeacher;
}
catch
{
value = null;
}
}
}
else if (p.PropertyType == typeof(ICourse))
{
index = reader.GetOrdinal("Course_Id");
using (Command cmd = new Command("init_Course"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = CourseGateway.Get(cmd, id);
}
catch
{
value = null;
}
}
}
else if (p.PropertyType == typeof(IStudent))
{
index = reader.GetOrdinal("Student_Id");
using (Command cmd = new Command("init_Student"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = UserGateway.Get(cmd, id, Role.Student) as IStudent;
}
catch
{
value = null;
}
}
}
else
{
index = reader.GetOrdinal(p.Name);
value = reader.GetValue(index);
value = value is DBNull ? null : value;
}
p.SetValue(obj, value, null);
}
catch
{
continue;
}
}
return obj as IBaseObject;
}
public static bool Update(Command cmd,IDictionary<string,object> values )
{
string table = Selection.Table;
string cals = string.Join(",",
(from p in values.Keys
where p != "oId"
let n = (p != "Id" && p != "Score" ? p + "_Id" : p)
select n + "=" + "@" + n).ToArray());
string sql = string.Format(@"update {1} set {0} where Id=@oId", cals, table);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
if (field != "Id" && field!="Score" && field!="oId")
cmd.AddParameter("@" + field + "_Id", values[field]);
else
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static IList<ISelection> ListByCourse(Command cmd,string courseId)
{
string table = Selection.Table;
string columns = string.Join(",", Selection.Properties.Select(p => p.Name != "Id" && p.Name != "Score" ? p.Name + "_Id" : p.Name).ToArray());
string sql = string.Format(@"select {0} from {1} where lower(Course_Id)=lower(@Course_Id)", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@Course_Id", courseId);
SqlCeDataReader reader = cmd.ExecuteReader();
IList<ISelection> selections = new List<ISelection>();
while (reader.Read())
{
ISelection selction = ReaderFactory.Reader(reader, Selection.Entity) as ISelection;
if (selction != null)
selections.Add(selction);
}
reader.Close();
return selections;
}
public static bool Update(IDictionary<string,object> values )
{
using (Command cmd = new Command("update_" + Selection.Entity))
{
try
{
return SelectionGateway.Update(cmd, values);
}
catch
{
cmd.RollBack();
return false;
}
}
}
