public bool AddTransferedCourse(int StudentID, Course course)
{
SqlConnection connection = SqlHelper.CreateConnection();
StringBuilder sb = new StringBuilder();
try
{
//Creating SqlParameter objects to fields in student
sb.Append("INSERT INTO StudentCourse(StudentID,CourseID,SemesterCourseID,CourseName,ElectiveID,SemesterID,");
sb.Append("Credits,[Status],IsActiveFL,CreationDate,LastUpdatedDate,CreatedBy,LastUpdatedBy) VALUES");
sb.Append("(" + StudentID + "," + course.ID + ",0,'" + course.Name + "'");
sb.Append(",0," + 0 + "," + course.Credits + ",'Pass','" + course.IsActiveFL);
sb.Append("','" + DateTime.UtcNow + "','" + DateTime.UtcNow + "'," + StudentID + "," + StudentID);
sb.Append(")");
SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sb.ToString());
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + sqlEx.ToString());
}
catch (Exception e)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + e.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return true;
}
public List<Course> GetCourses()
{
StringBuilder sb = new StringBuilder();
SqlConnection connection = SqlHelper.CreateConnection();
List<Course> listOfCourses = new List<Course>();
try
{
sb.Append("SELECT ID,Name,Abbreviation,[Description],DepartmentID,Credits,EnglishProficiencyFL,IsMandatoryFL,IsElectiveAFL,");
sb.Append("IsElectiveBFL,IsActiveFL FROM Course ORDER BY Name");
using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.Text, sb.ToString()))
{
while (dr.Read())
{
Course course = new Course();
course.ID = SqlHelper.ToInt32(dr["ID"]);
course.Name = SqlHelper.ToString(dr["Name"]);
course.Abbreviation = SqlHelper.ToString(dr["Abbreviation"]);
course.DepartmentID = SqlHelper.ToInt32(dr["DepartmentID"]);
if (dr["Description"] != null)
{
course.Description = SqlHelper.ToString(dr["Description"]);
}
course.Credits = SqlHelper.ToInt32(dr["Credits"]);
course.EnglishProficiencyFL = SqlHelper.ToBool(dr["EnglishProficiencyFL"]);
course.IsMandatoryFL = SqlHelper.ToBool(dr["IsMandatoryFL"]);
course.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]);
course.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]);
course.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]);
listOfCourses.Add(course);
}
}
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetCourses: " + sqlEx.ToString());
}
catch (Exception ex)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetCourses: " + ex.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return listOfCourses;
}
public Course SaveCourse(Course course)
{
try
{
course.CreationDate = DateTime.UtcNow;
course.LastUpdatedDate = DateTime.UtcNow;
course = GetDLCourse().SaveCourse(course);
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return course;
}
public Course SaveCourse(Course course)
{
SqlConnection connection = SqlHelper.CreateConnection();
try
{
string spName = "CourseSave";
ArrayList paramList = new ArrayList();
//Creating SqlParameter objects to fields in course
SqlParameter pID = new SqlParameter("@ID", SqlDbType.Int);
SqlParameter pName = new SqlParameter("@Name", SqlDbType.NVarChar);
SqlParameter pAbbreviation = new SqlParameter("@Abbreviation", SqlDbType.NVarChar);
SqlParameter pDescription = new SqlParameter("@Description", SqlDbType.NVarChar);
SqlParameter pCredits = new SqlParameter("@Credits", SqlDbType.Int);
SqlParameter pDepartmentID = new SqlParameter("@DepartmentID", SqlDbType.Int);
SqlParameter pEnglishProficiencyFL = new SqlParameter("@EnglishProficiencyFL", SqlDbType.Bit);
SqlParameter pIsMandatoryFL = new SqlParameter("@IsMandatoryFL", SqlDbType.Bit);
SqlParameter pIsElectiveAFL = new SqlParameter("@IsElectiveAFL", SqlDbType.Bit);
SqlParameter pIsElectiveBFL = new SqlParameter("@IsElectiveBFL", SqlDbType.Bit);
SqlParameter pIsActiveFL = new SqlParameter("@IsActiveFL", SqlDbType.Bit);
SqlParameter pCreatedBy = new SqlParameter("@CreatedBy", SqlDbType.Int);
SqlParameter pLastUpdatedBy = new SqlParameter("@LastUpdatedBy", SqlDbType.Int);
SqlParameter pCreationDate = new SqlParameter("@CreationDate", SqlDbType.DateTime);
SqlParameter pLastUpdatedDate = new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime);
pID.Direction = ParameterDirection.InputOutput;
pID.Value = course.ID;
pName.Value = course.Name;
pAbbreviation.Value = course.Abbreviation;
pDescription.Value = course.Description;
pCredits.Value = course.Credits;
pDepartmentID.Value = course.DepartmentID;
pEnglishProficiencyFL.Value = course.EnglishProficiencyFL;
pIsMandatoryFL.Value = course.IsMandatoryFL;
pIsElectiveAFL.Value = course.IsElectiveAFL ;
pIsElectiveBFL.Value = course.IsElectiveBFL;
pIsActiveFL.Value = course.IsActiveFL;
pCreationDate.Value = course.CreationDate;
pLastUpdatedDate.Value = course.LastUpdatedDate;
pCreatedBy.Value = course.CreatedBy;
pLastUpdatedBy.Value = course.LastUpdatedBy;
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, pID, pName, pAbbreviation, pDescription, pCredits, pDepartmentID,
pEnglishProficiencyFL, pIsMandatoryFL, pIsElectiveAFL,pIsElectiveBFL ,pIsActiveFL, pCreationDate, pLastUpdatedDate, pCreatedBy, pLastUpdatedBy);
course.ID = Convert.ToInt32(pID.Value);
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("CourseSave: " + sqlEx.ToString());
}
catch (Exception e)
{
SqlHelper.CloseConnection(connection);
throw new Exception("CourseSave: " + e.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return course;
}
public bool AddTransferedCourse(int StudentID, Course course)
{
dlStudent = GetDLStudent();
return dlStudent.AddTransferedCourse(StudentID, course);
}
public bool AddTransferedCourse(int StudentID, Course course)
{
BLStudent blStudent = new BLStudent();
return blStudent.AddTransferedCourse(StudentID, course);
}
public Course SaveCourseDetails(Course course)
{
BLCourse blCourse = new BLCourse();
blCourse.SaveCourse(course);
return course;
}