public static IUser Authenticate(Command cmd, string id, string pwd, 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) and Password=@pwd", columns,
table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
cmd.AddParameter("@pwd", pwd);
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 bool Insert(Command cmd, IDictionary<string, object> values)
{
string table = Course.Table;
string columns = string.Join(",", values.Keys.Select(p => p == "Teacher" ? p + "_Id" : p).ToArray());
string paras = string.Join(",", values.Keys.Select(p => "@" + (p == "Teacher" ? 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 == "Teacher")
cmd.AddParameter("@" + field + "_Id", values[field]);
else
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
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 ISelection Get(Command cmd,string courseId,string studentId)
{
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) and lower(Student_Id)=lower(@Student_Id)", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@Course_Id", courseId);
cmd.AddParameter("@Student_Id", studentId);
SqlCeDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
ISelection selction = ReaderFactory.Reader(reader, Selection.Entity) as ISelection;
reader.Close();
return selction;
}
reader.Close();
return null;
}
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 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 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 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 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<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(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<ICourse> ListByTeacher(Command cmd, string teacherId)
{
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(Teacher_Id)=@TeacherId", columns, table);
cmd.CommandText = sql;
cmd.AddParameter("@TeacherId", teacherId);
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;
}