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 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 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 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 int addTopicARN(string _topicarn)
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@courseid", this.courseId);
param[1] = new MySqlParameter("@topicarn", _topicarn);
sqlCommand = "insert into SNSTopicArn (TopicArn,CourseId) " +
" values (@topicarn,@courseid)";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
public int addCourse()
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[6];
param[0] = new MySqlParameter("@courseName", this.courseName);
param[1] = new MySqlParameter("@startDate", this.startDate);
param[2] = new MySqlParameter("@endDate", this.endDate);
param[3] = new MySqlParameter("@classOnDays", this.classOnDays);
param[4] = new MySqlParameter("@classStartTime", this.classStartTime);
param[5] = new MySqlParameter("@classEndTime", this.classEndTime);
sqlCommand = "insert into Course (coursename,startdate,enddate,"+
"classondays,classstarttime,classendtime) "+
" values (@courseName,@startDate,@endDate"+
",@classOnDays,@classStartTime,@classEndTime)";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
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 int addResource()
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[6];
param[0] = new MySqlParameter("@resourceType", this.resourceType);
param[1] = new MySqlParameter("@resourcename", this.resourceName);
param[2] = new MySqlParameter("@uploadedby", this.uploadedBy);
param[3] = new MySqlParameter("@uploadedat", this.uploadedAt);
param[4] = new MySqlParameter("@courseid", this.courseId);
param[5] = new MySqlParameter("@resourcefile", this.resourceFile);
sqlCommand = "insert into Resource (ResourceType,ResourceName,UploadedBy,"+
"UploadedAt,CourseId,ResourceFile) "+
" values (@resourceType,@resourcename,@uploadedby"+
",@uploadedat,@courseid,@resourcefile)";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
public static int updateGrade(int _resourceid, string _grade)
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@resourceId", _resourceid);
param[1] = new MySqlParameter("@grade", _grade);
sqlCommand = "update resourcefeedback set grade=@grade where resourceid=@resourceId";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
public static int updateComments(int _resourceid, string _comments)
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@resourceId", _resourceid);
param[1] = new MySqlParameter("@comments", _comments);
sqlCommand = "update resourcefeedback set comments=@comments where resourceid=@resourceId";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
public static int insertGrade(int _resourceid, string _grade)
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@resourceId", _resourceid);
param[1] = new MySqlParameter("@grade", _grade);
sqlCommand = "insert into resourcefeedback(resourceid,grade) values (@resourceId,@grade)";
int sqlRet = db.executeDDL(sqlCommand, param);
return sqlRet;
}
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;
}
public int addUser()
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter [] param = new MySqlParameter[6];
param[0] = new MySqlParameter( "@Username", this.username);
param[1] = new MySqlParameter("@Psswrd", this.password);
param[2] = new MySqlParameter("@NameUser", this.name);
param[3] = new MySqlParameter("@ContactNo", this.contactNo);
param[4] = new MySqlParameter("@UserType", (int)this.usertype);
param[5] = new MySqlParameter("@UserStatus", (int)this.status);
sqlCommand="insert into user (Username,Psswrd,NameUser,ContactNo,UserType,UserStatus) "+
" values (@Username,@Psswrd,@NameUser,@ContactNo,@UserType,@UserStatus)";
int sqlRet = db.executeDDL(sqlCommand, param);
try
{
//Mailer.Send("priyanka.gupta.email", "aspirations", this.username, "Test Message", "Hi");
}
catch (Exception ex)
{ }
return sqlRet;
}
public static int registerInCourse(int _userid,int _courseid)
{
dbManager db = new dbManager();
string sqlCommand;
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("@UserID", _userid);
param[1] = new MySqlParameter("@CourseId", _courseid);
sqlCommand = "insert into UserCourse (UserId,CourseId) " +
" values (@UserID,@CourseId)";
int sqlRet = db.executeDDL(sqlCommand, param);
try
{
//Mailer.Send("priyanka.gupta.email", "aspirations", this.username, "Test Message", "Hi");
}
catch (Exception ex)
{ }
return sqlRet;
}