public Survey.Survey getSurvey(int id)
{
Survey.Survey survey = null;
StringBuilder sql = new StringBuilder();
sql.Append("SELECT SurveyID, SurveyName, OpenDate,DaysOpen, Active FROM Surveys ");
sql.Append(" WHERE SurveyID=@id");
SqlCommand cmd = new SqlCommand(sql.ToString());
cmd.Parameters.Add(new SqlParameter("@id", id));
using (IDataReader reader = this.ExecuteReader(cmd))
{
while (reader.Read())
{
int colIndex = -1;
survey = new Survey.Survey();
survey.SurveyId = reader.GetInt32(++colIndex);
survey.Name = reader.GetString(++colIndex);
if (!reader.IsDBNull(reader.GetOrdinal("OpenDate")))
{
survey.OpenDate = reader.GetDateTime(++colIndex);
}
else
{
++colIndex;
}
if (!reader.IsDBNull(reader.GetOrdinal("DaysOpen")))
{
survey.DaysOpen = reader.GetInt32(++colIndex);
}
else
{
++colIndex;
}
if (!reader.IsDBNull(reader.GetOrdinal("Active")))
{
survey.Active = reader.GetString(++colIndex).ToUpper() == "Y" ? true : false;
}
else
{
++colIndex;
}
}
}
return(survey);
}
public IList <Survey.Survey> GetAllSurveys(bool fetchActiveOnly = true)
{
var surveyList = new List <Survey.Survey>();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT SurveyID, SurveyName, OpenDate, Active FROM Surveys ");
if (fetchActiveOnly)
{
sql.Append(" WHERE Active='{0}' ");
}
sql.Append(" ORDER BY Active DESC, OpenDate DESC ");
using (IDataReader reader = this.ExecuteReader(CommandType.Text, sql.ToString()))
{
while (reader.Read())
{
int colIndex = -1;
var survey = new Survey.Survey();
survey.SurveyId = reader.GetInt32(++colIndex);
survey.Name = reader.GetString(++colIndex);
if (!reader.IsDBNull(reader.GetOrdinal("OpenDate")))
{
survey.OpenDate = reader.GetDateTime(++colIndex);
}
else
{
++colIndex;
}
if (!reader.IsDBNull(reader.GetOrdinal("Active")))
{
survey.Active = reader.GetString(++colIndex).ToUpper() == "Y" ? true : false;
}
else
{
++colIndex;
}
surveyList.Add(survey);
}
}
return(surveyList);
}
//public static CuteSurvey.SurveyFactory.SurveyTemplate load(int sid) {
//}
public static Survey.Survey Bind(Survey.Survey survey)
{
survey.SurveyID = 100;
return(survey);
}
public bool UpdateSurvey(Survey.Survey survey)
{
Database db = this.CreateDatabase();
string commaSeparatedStudentIdsList = "";
string commaSeparatedEnrolmentIdsList = "";
if (survey.Students != null)
{
commaSeparatedStudentIdsList = String.Join(",", (from s in survey.Students
where s.EnrollmentId != 0 && s.StudentNo != 0
select s.StudentNo.ToString()).ToArray());
commaSeparatedEnrolmentIdsList = String.Join(",", (from s in survey.Students
where s.EnrollmentId != 0 && s.StudentNo != 0
select s.EnrollmentId.ToString()).ToArray());
}
#region Survey Update Query
SqlCommand surveyUpdateCommand = new SqlCommand("UpdateSurvey");
surveyUpdateCommand.CommandType = CommandType.StoredProcedure;
surveyUpdateCommand.Parameters.AddWithValue("@StudentIdsList", commaSeparatedStudentIdsList);
surveyUpdateCommand.Parameters.AddWithValue("@EnrolmentIdsList", commaSeparatedEnrolmentIdsList);
surveyUpdateCommand.Parameters.AddWithValue("@SurveyId", survey.SurveyId);
surveyUpdateCommand.Parameters.AddWithValue("@SurveyName", survey.Name);
surveyUpdateCommand.Parameters.AddWithValue("@QuestionGroupId", survey.QuestionGroupId);
if (survey.OpenDate == null)
{
surveyUpdateCommand.Parameters.AddWithValue("@OpenDate", DBNull.Value);
}
else
{
surveyUpdateCommand.Parameters.AddWithValue("@OpenDate", survey.OpenDate);
}
if (survey.DaysOpen == null)
{
surveyUpdateCommand.Parameters.AddWithValue("@DaysOpen", DBNull.Value);
}
else
{
surveyUpdateCommand.Parameters.AddWithValue("@DaysOpen", survey.DaysOpen);
}
surveyUpdateCommand.Parameters.AddWithValue("@Active", survey.Active == true?'Y':'N');
if (survey.CourseOfferingNo == null)
{
surveyUpdateCommand.Parameters.AddWithValue("@CourseOfferingNo", DBNull.Value);
}
else
{
surveyUpdateCommand.Parameters.AddWithValue("@CourseOfferingNo", survey.CourseOfferingNo);
}
surveyUpdateCommand.Parameters.AddWithValue("@Submitted", survey.Submitted);
#endregion
this.ExecuteNonQuery(surveyUpdateCommand);
return(true);
}
