public bool Add(TrainingAccess training) { ConnectDB db = new ConnectDB(); SqlCommand cmd; cmd = new SqlCommand("AddTraining"); cmd.CommandType = CommandType.StoredProcedure; SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int); RetVal.Direction = ParameterDirection.ReturnValue; SqlParameter trainingName = cmd.Parameters.Add("@trainingName", SqlDbType.VarChar, 50); trainingName.Direction = ParameterDirection.Input; SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 512); description.Direction = ParameterDirection.Input; SqlParameter type = cmd.Parameters.Add("@type", SqlDbType.VarChar, 35); type.Direction = ParameterDirection.Input; SqlParameter moduleId = cmd.Parameters.Add("@moduleId", SqlDbType.Int); moduleId.Direction = ParameterDirection.Input; if (training.TrainingName != null) { trainingName.Value = training.TrainingName; } if (training.Description != null) { description.Value = training.Description; } if (training.Type != null) { type.Value = training.Type; } if (training.ModuleId != default(int)) { moduleId.Value = training.ModuleId; } db.changeData(cmd); db.Close(); if (Int32.Parse(RetVal.Value.ToString()) == 0) { return(true); } else { return(false); } }
/// <summary> /// /// </summary> /// <param name="moduleId"></param> /// <returns></returns> public ModuleAccessDetail GetModuleDetailData(int moduleId) { ModuleAccessDetail moduleDetail = new ModuleAccessDetail(); List <TrainingAccess> trainingList = new List <TrainingAccess>(); List <QuizAccess> quizList = new List <QuizAccess>(); ConnectDB db = new ConnectDB(); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; //query training table cmd.CommandText = "SELECT trainingId, trainingName, description, type FROM TrainingModule WHERE moduleId = @moduleId"; cmd.Parameters.AddWithValue("@moduleId", moduleId); dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { TrainingAccess training = new TrainingAccess(); if (System.DBNull.Value.Equals(dr[0])) { training.TrainingId = 0; } else { training.TrainingId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { training.TrainingName = null; } else { training.TrainingName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { training.Description = null; } else { training.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { training.Type = null; } else { training.Type = Convert.ToString(dr[3]); } trainingList.Add(training); } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } moduleDetail.Training = trainingList; //query quiz table cmd.CommandText = "SELECT quizId, quizName, description, passingGrade FROM Quiz WHERE moduleId = @moduleIdq"; cmd.Parameters.AddWithValue("@moduleIdq", moduleId); dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { QuizAccess quiz = new QuizAccess(); if (System.DBNull.Value.Equals(dr[0])) { quiz.QuizId = 0; } else { quiz.QuizId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { quiz.QuizName = null; } else { quiz.QuizName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { quiz.Description = null; } else { quiz.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { quiz.PassingGrade = 0; } else { quiz.PassingGrade = Convert.ToInt32(dr[3]); } quizList.Add(quiz); } } moduleDetail.Quiz = quizList; if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } db.Close(); return(moduleDetail); }
public TrainingTransfer GeTrainingDetailData(int trainingId) { TrainingTransfer trainingDetail = new TrainingTransfer(); TrainingAccess training = new TrainingAccess(); List <DocumentAccess> documentList = new List <DocumentAccess>(); ConnectDB db = new ConnectDB(); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; //select data from TrainingModule table according trainingId cmd.CommandText = "select * from TrainingModule WHERE trainingId = @trainingId;"; cmd.Parameters.AddWithValue("@trainingId", trainingId); dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { if (System.DBNull.Value.Equals(dr[0])) { training.TrainingId = 0; } else { training.TrainingId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { training.TrainingName = null; } else { training.TrainingName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { training.Description = null; } else { training.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { training.Type = null; } else { training.Type = Convert.ToString(dr[3]); } if (System.DBNull.Value.Equals(dr[4])) { training.ModuleId = 0; } else { training.ModuleId = Convert.ToInt32(dr[4]); } } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } trainingDetail.Training = training; //select document list from database cmd.CommandText = "SELECT documentId, name, type, uploadDate, description, size, trainingId FROM Document WHERE trainingId = @trainingDetailId"; cmd.Parameters.AddWithValue("@trainingDetailId", trainingId); dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { DocumentAccess document = new DocumentAccess(); if (System.DBNull.Value.Equals(dr[0])) { document.DocumentId = 0; } else { document.DocumentId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { document.DocumentName = null; } else { document.DocumentName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { document.Type = null; } else { document.Type = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { document.UploadDate = default(DateTime); } else { document.UploadDate = Convert.ToDateTime(dr[3]); } if (System.DBNull.Value.Equals(dr[4])) { document.Description = null; } else { document.Description = Convert.ToString(dr[4]); } if (System.DBNull.Value.Equals(dr[5])) { document.Size = 0; } else { document.Size = Convert.ToInt32(dr[5]); } if (System.DBNull.Value.Equals(dr[6])) { document.TrainingId = 0; } else { document.TrainingId = Convert.ToInt32(dr[6]); } documentList.Add(document); } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } db.Close(); trainingDetail.DocumentList = documentList; return(trainingDetail); }
/// <summary> /// /// </summary> /// <param name="moduleId"></param> /// <returns></returns> public List <TrainingAccess> GetTrainingDataForModule(int moduleId) { List <TrainingAccess> trainingList = new List <TrainingAccess>(); ConnectDB db = new ConnectDB(); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM TrainingModule WHERE moduleId = @moduleId;"; cmd.Parameters.Add("@moduleId", SqlDbType.Int).Value = moduleId; dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { TrainingAccess training = new TrainingAccess(); if (System.DBNull.Value.Equals(dr[0])) { training.TrainingId = 0; } else { training.TrainingId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { training.TrainingName = null; } else { training.TrainingName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { training.Description = null; } else { training.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { training.Type = null; } else { training.Type = Convert.ToString(dr[3]); } if (System.DBNull.Value.Equals(dr[4])) { training.ModuleId = 0; } else { training.ModuleId = Convert.ToInt32(dr[4]); } trainingList.Add(training); } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } db.Close(); return(trainingList); }
/// <summary> /// /// </summary> /// <param name="quizRecord"></param> /// <returns></returns> public bool SaveQuizRecord(QuizRecordAccess quizRecord) { ConnectDB db = new ConnectDB(); SqlCommand cmd; DateTime today; SqlDataReader dr; int grade = 0; bool isInsert = false; //update data in Quiz table cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; //find whether there is a record in Quiz_record table cmd.CommandText = "SELECT highestGrade FROM Quiz_record WHERE quizId = @quizId AND accountId = @accountId;"; cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId); cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId); dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { TrainingAccess training = new TrainingAccess(); if (System.DBNull.Value.Equals(dr[0])) { grade = 0; isInsert = true; } else { grade = Convert.ToInt32(dr[0]); } } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } if (grade <= quizRecord.HighestGrade) { grade = quizRecord.HighestGrade; today = quizRecord.HighGradeTime; } else { today = DateTime.Now; } if (isInsert == true) { //insert a new record in Quiz_record table cmd.CommandText = "INSERT INTO Quiz_record(accountId,quizId,attendTimes,highestGrade,highGradeTime)VALUES(@accountId, @quizId, @attendTimes, @highestGrade, @highGradeTime);"; cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId); cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId); cmd.Parameters.AddWithValue("@attendTimes", today); cmd.Parameters.AddWithValue("@highestGrade", grade); cmd.Parameters.AddWithValue("@highGradeTime", today); } else if (grade != quizRecord.HighestGrade) { //update data in Quiz_record table cmd.CommandText = "UPDATE Quiz_record SET highestGrade = @highestGrade,highGradeTime = @highGradeTime WHERE quizId = @quizId AND accountId = @accountId ;"; cmd.Parameters.AddWithValue("@highestGrade", grade); cmd.Parameters.AddWithValue("@highGradeTime", today); cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId); cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId); } db.changeData(cmd); db.Close(); return(true); }