public static Course fetchCoursebyId(int _courseid)
{
dbManager db = new dbManager();
Course cs = null;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@courseid", _courseid);
sqlCommand = "select * from Course where courseid=@courseid";
DataTable ds = db.fetchRows(sqlCommand, param);
int i = 0;
foreach (DataRow dr in ds.Rows)
{
cs = new Course(Convert.ToInt32(dr["courseid"].ToString()),
dr["coursename"].ToString(),
(DateTime)dr["startdate"],
(DateTime)dr["enddate"],
dr["classondays"].ToString(),
(TimeSpan)dr["classstarttime"],
(TimeSpan)dr["classendtime"]);
i++;
}
return cs;
}
public static Resource[] fetchResourcesbyCourse(int _courseid)
{
dbManager db = new dbManager();
Resource[] rsc = null;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@courseid", _courseid);
sqlCommand = "select * from Resource where courseid=@courseid";
DataTable ds = db.fetchRows(sqlCommand, param);
rsc = new Resource[ds.Rows.Count];
int i = 0;
foreach (DataRow dr in ds.Rows)
{
rsc[i] = new Resource((int)dr["resourceid"],
(ResourceType)Enum.Parse(typeof(ResourceType),dr["resourcetype"].ToString()),
dr["resourcename"].ToString(),
(int)dr["uploadedby"],
(DateTime)dr["uploadedat"],
(int)dr["Courseid"],
dr["filename"].ToString());
i++;
}
return rsc;
}
public static MyUser getUser(string username, string password)
{
dbManager db = new dbManager();
MyUser usr=null;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@Username", username);
param[1] = new MySqlParameter("@Psswrd", password);
sqlCommand = "select * from user where Username=@Username and Psswrd=@Psswrd";
DataTable ds = db.fetchRows(sqlCommand, param);
foreach (DataRow dr in ds.Rows)
{
usr = new MyUser((int)dr["userid"], dr["Username"].ToString(),
dr["nameuser"].ToString(), dr["contactno"].ToString(),
(UserType)Enum.Parse(typeof(UserType), dr["usertype"].ToString()),
(UserStatus)Enum.Parse(typeof(UserStatus), dr["userstatus"].ToString()));
}
return usr;
}
public static string fetchTopicARN(int _courseId)
{
dbManager db = new dbManager();
string topic = "";
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@courseid", _courseId);
sqlCommand = "select topicarn from SNSTopicArn where courseid = @courseid";
DataTable ds = db.fetchRows(sqlCommand, param);
foreach (DataRow dr in ds.Rows)
{
topic = dr["topicarn"].ToString();
}
return topic;
}
public static MyUser[] fetchUsersforCourseid(int _courseid)
{
dbManager db = new dbManager();
MyUser[] users = null;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@courseid", _courseid);
sqlCommand = "select User.* from UserCourse join User on UserCourse.Userid=user.Userid "+
" where Courseidy=@courseid";
DataTable ds = db.fetchRows(sqlCommand, param);
users = new MyUser[ds.Rows.Count];
int i = 0;
foreach (DataRow dr in ds.Rows)
{
users[i] = new MyUser((int)dr["userid"],
dr["username"].ToString(),dr["nameUser"].ToString(),
dr["contactno"].ToString(),
(UserType)Enum.Parse(typeof(UserType), dr["usertype"].ToString()),
(UserStatus)Enum.Parse(typeof(UserStatus), dr["UserStatus"].ToString()));
i++;
}
return users;
}
public static int fetchUploadedCourse(string _coursename)
{
dbManager db = new dbManager();
int csid = 0;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@coursename", _coursename);
sqlCommand = "select courseid from Course where coursename like @coursename";
DataTable ds = db.fetchRows(sqlCommand, param);
foreach (DataRow dr in ds.Rows)
{
csid = (int)dr["courseid"];
}
return csid;
}
public static int fetchUploadedResource(int _uploadedby)
{
dbManager db = new dbManager();
int rsid = 0;
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[1];
param[0] = new MySqlParameter("@uploadedby", _uploadedby);
sqlCommand = "select max(resourceid) as resourceid from resource where uploadedby=@uploadedby";
DataTable ds = db.fetchRows(sqlCommand, param);
foreach (DataRow dr in ds.Rows)
{
rsid = (int)dr["resourceid"];
}
db = new dbManager();
sqlCommand = "INSERT INTO mccdb.resourcefeedback(resourceid) values (@resourceid)";
param = new MySqlParameter[1];
param[0] = new MySqlParameter("@resourceid", rsid);
db.executeDDL(sqlCommand, param);
return rsid;
}