public static List <TribalCourseRun> GetCourseInstancesByCourseId(int CourseId, string connectionString, out string errorMessageGetCourseRuns)
{
errorMessageGetCourseRuns = string.Empty;
var tribalCourseRuns = new List <TribalCourseRun>();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dfc_GetCourseInstancesByCourseId";
command.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int));
command.Parameters["@CourseId"].Value = CourseId;
try
{
//Open connection.
sqlConnection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
TribalCourseRun tribalCourseRun = ExtractCourseRunFromDbReader(dataReader);
if (tribalCourseRun != null)
{
tribalCourseRuns.Add(tribalCourseRun);
}
}
// Close the SqlDataReader.
dataReader.Close();
}
}
catch (Exception ex)
{
errorMessageGetCourseRuns = string.Format("Error Message: {0}" + Environment.NewLine + "Stack Trace: {1}", ex.Message, ex.StackTrace);
}
finally
{
sqlConnection.Close();
}
}
}
return(tribalCourseRuns);
}
public static TribalCourseRun ExtractCourseRunFromDbReader(SqlDataReader reader)
{
TribalCourseRun tribalCourseRun = new TribalCourseRun();
tribalCourseRun.CourseId = (int)CheckForDbNull(reader["CourseId"], 0);
tribalCourseRun.VenueId = (int?)CheckForDbNull(reader["VenueId"], null);
tribalCourseRun.CourseInstanceId = (int)CheckForDbNull(reader["CourseInstanceId"], 0);
tribalCourseRun.ProviderOwnCourseInstanceRef = (string)CheckForDbNull(reader["ProviderOwnCourseInstanceRef"], string.Empty);
tribalCourseRun.AttendanceType = (AttendanceType)CheckForDbNull(reader["AttendanceTypeId"], AttendanceType.Undefined);
tribalCourseRun.StartDateDescription = (string)CheckForDbNull(reader["StartDateDescription"], string.Empty);
tribalCourseRun.StartDate = (DateTime?)CheckForDbNull(reader["StartDate"], null);
tribalCourseRun.Url = (string)CheckForDbNull(reader["Url"], string.Empty);
tribalCourseRun.Price = (decimal?)CheckForDbNull(reader["Price"], null);
tribalCourseRun.PriceAsText = (string)CheckForDbNull(reader["PriceAsText"], string.Empty);
tribalCourseRun.DurationUnit = (TribalDurationUnit)CheckForDbNull(reader["DurationUnitId"], TribalDurationUnit.Undefined);
tribalCourseRun.DurationValue = (int)CheckForDbNull(reader["DurationUnit"], 0);
tribalCourseRun.StudyMode = (TribalStudyMode)CheckForDbNull(reader["StudyModeId"], TribalStudyMode.Undefined);
tribalCourseRun.AttendancePattern = (TribalAttendancePattern)CheckForDbNull(reader["AttendancePatternId"], TribalAttendancePattern.Undefined);
tribalCourseRun.VenueName = (string)CheckForDbNull(reader["VenueName"], string.Empty);
return(tribalCourseRun);
}
