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 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 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 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 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 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;
}