/// <summary>
/// Adds a record to the Course Registrations database in the E Journal Server.
/// </summary>
internal static int RegisterUserToCourse(SqlConnection dBconnection,
ejsSessionToken sessionToken, ejsCourse Course)
{
SqlCommand command = new SqlCommand();
command.CommandTimeout = 60;
try
{
command.Connection = dBconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "RegisterUserToCourse";
command.Parameters.Add("CourseId", System.Data.SqlDbType.Int).Value = Course.Id;
command.Parameters.Add("UserId", System.Data.SqlDbType.UniqueIdentifier).Value = sessionToken.UserId;
command.Parameters.Add("RegistrationDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int resultCode = (int)returnValue.Value;
return(resultCode);
}
finally
{
command.Dispose();
}
}
internal static int UpdateCourseRecord(SqlConnection dBconnection,
ejsSessionToken sessionToken, ejsCourse course)
{
SqlCommand command = new SqlCommand();
command.CommandTimeout = 60;
try
{
command.Connection = dBconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "UpdateCourseRecord";
command.Parameters.Add("UserId", System.Data.SqlDbType.UniqueIdentifier).Value = sessionToken.UserId;
command.Parameters.Add("CourseId", System.Data.SqlDbType.Int).Value = course.Id;
command.Parameters.Add("Name", System.Data.SqlDbType.NVarChar, 150).Value = course.Name;
command.Parameters.Add("Description", System.Data.SqlDbType.NVarChar, 500).Value = course.Description;
command.Parameters.Add("Owner", System.Data.SqlDbType.NVarChar, 200).Value = course.Owner;
command.Parameters.Add("IsActive", System.Data.SqlDbType.Bit).Value = course.IsActive;
SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int resultCode = (int)returnValue.Value;
return(resultCode);
}
finally
{
command.Dispose();
}
}
/// <summary>
/// Returns a list of ALL the courses currentlty registered in the server.
/// </summary>
internal static void GetAllRegisteredCourses(SqlConnection dBconnection,
ejsSessionToken sessionToken, bool includeDisabledCourses, ref List <ejsCourse> result)
{
result.Clear();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
command.CommandTimeout = 60;
try
{
command.Connection = dBconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "GetAllRegisteredCourses";
command.Parameters.Add("UserId", SqlDbType.UniqueIdentifier).Value = sessionToken.UserId;
command.Parameters.Add("IncludeNotAvailable", SqlDbType.Bit).Value = 1;
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ejsCourse course = new ejsCourse();
course.Id = reader.GetInt32(0);
course.Name = reader.GetString(1);
course.Description = reader.GetString(2);
course.Owner = reader.GetString(3);
course.CreationDate = reader.GetDateTime(4);
course.IsActive = reader.GetBoolean(5);
result.Add(course);
}
}
}
finally
{
command.Dispose();
if (reader != null)
{
reader.Close();
reader.Dispose();
}
}
}
/// <summary>
/// Returns all the courses that a particular user has registered to.
/// </summary>
internal static void GetRegisteredCoursesForUser(SqlConnection dBconnection,
ejsSessionToken sessionToken, ref List <ejsCourse> courseList)
{
courseList.Clear();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
command.CommandTimeout = 60;
try
{
command.Connection = dBconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "GetRegisteredCoursesForUser";
command.Parameters.Add("UserId", SqlDbType.UniqueIdentifier);
command.Parameters[0].Value = sessionToken.UserId;
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ejsCourse course = new ejsCourse();
course.Id = reader.GetInt32(0);
course.Name = reader.GetString(1);
course.Description = reader.GetString(2);
course.Owner = reader.GetString(3);
course.CreationDate = reader.GetDateTime(4);
courseList.Add(course);
}
}
}
finally
{
command.Dispose();
if (reader != null)
{
reader.Close();
reader.Dispose();
}
}
}
/// <summary>
/// Register the given ejsCourse object as a new course in the
/// E Journal Server Courses database.
/// </summary>
internal static int RegisterNewCourse(SqlConnection dBconnection,
ejsSessionToken sessionToken, ejsCourse newCourse)
{
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
command.CommandTimeout = 60;
try
{
command.Connection = dBconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "RegisterNewCourse";
command.Parameters.Add("Name", System.Data.SqlDbType.NVarChar, 150).Value = newCourse.Name;
command.Parameters.Add("Description", System.Data.SqlDbType.NVarChar, 500).Value = newCourse.Description;
command.Parameters.Add("Owner", System.Data.SqlDbType.NVarChar, 200).Value = newCourse.Owner;
command.Parameters.Add("CreationDate", System.Data.SqlDbType.DateTime).Value = newCourse.CreationDate;
command.Parameters.Add("IsActive", System.Data.SqlDbType.Bit).Value = newCourse.IsActive;
SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int resultCode = (int)returnValue.Value;
return(resultCode);
}
finally
{
command.Dispose();
if (reader != null)
{
reader.Close();
reader.Dispose();
}
}
}
