public bool Update(EnrollmentBase mClass)
{
bool mResult = false;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "UPDATE Enrollment SET StudentId = @StudentId, CourseId = @CourseId, ClassId = @ClassId, StartDate = @StartDate, HasPaid = @HasPaid, Completed = @Completed " +
"WHERE EnrollmentId = @EnrollmentId;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@EnrollmentId", SqlDbType.Int).Value = mClass.EnrollmentId;
cmd.Parameters.Add("@StudentId", SqlDbType.Int).Value = mClass.StudentId;
cmd.Parameters.Add("@CourseId", SqlDbType.Int).Value = mClass.CourseId;
cmd.Parameters.Add("@ClassId", SqlDbType.Int).Value = mClass.ClassId;
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = mClass.StartDate;
cmd.Parameters.Add("@HasPaid", SqlDbType.Bit).Value = mClass.HasPaid;
cmd.Parameters.Add("@Completed", SqlDbType.Bit).Value = mClass.Completed;
try
{
con.Open();
cmd.ExecuteNonQuery();
mResult = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(mResult);
}
public int Add(EnrollmentBase mClass)
{
int EnrollmentId = 0;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "INSERT INTO Enrollment (StudentId, CourseId, ClassId, StartDate, HasPaid, Completed)" +
" VALUES (@StudentId, @CourseId, @ClassId, @StartDate, @HasPaid, @Completed)" +
" SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@StudentId", SqlDbType.Int).Value = mClass.StudentId;
cmd.Parameters.Add("@CourseId", SqlDbType.Int).Value = mClass.CourseId;
cmd.Parameters.Add("@ClassId", SqlDbType.Int).Value = mClass.ClassId;
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = mClass.StartDate;
cmd.Parameters.Add("@HasPaid", SqlDbType.Bit).Value = mClass.HasPaid;
cmd.Parameters.Add("@Completed", SqlDbType.Bit).Value = mClass.Completed;
try
{
con.Open();
EnrollmentId = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
EnrollmentId = -1;
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(EnrollmentId);
}
private void Populate(EnrollmentBase mClass)
{
this.EnrollmentId = mClass.EnrollmentId;
this.StudentId = mClass.StudentId;
this.CourseId = mClass.CourseId;
this.ClassId = mClass.ClassId;
this.StartDate = mClass.StartDate;
this.HasPaid = mClass.HasPaid;
this.Completed = mClass.Completed;
this.Created = mClass.Created;
this.CourseCode = mClass.CourseCode;
this.CourseName = mClass.CourseName;
}
private EnrollmentBase Get(string WhereClause, params SqlParameter[] commandparameters)
{
EnrollmentBase mClass = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "SELECT db.EnrollmentId, db.StudentId, db.CourseId, db.ClassId, db.StartDate, " +
"db.HasPaid, db.Completed, db.Created, " +
"cr.Code AS CourseCode, cr.Name AS CourseName, " +
"sn.FirstName, sn.LastName " +
"FROM Enrollment db " +
"LEFT OUTER JOIN Course cr ON cr.CourseId = db.CourseId " +
"LEFT OUTER JOIN Student sn ON sn.StudentId = db.StudentId " +
WhereClause;
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = null;
foreach (var parameter in commandparameters)
{
cmd.Parameters.Add(parameter);
}
try
{
con.Open();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
mClass = LoadRow(rdr);
}
}
catch (Exception ex)
{
mClass = null;
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(mClass);
}
