private void Populate(CourseClassBase mClass)
{
this.CourseClassId = mClass.CourseClassId;
this.CourseId = mClass.CourseId;
this.TeacherId = mClass.TeacherId;
this.StartDate = mClass.StartDate;
this.EndDate = mClass.EndDate;
this.CourseDay = mClass.CourseDay;
this.StartTime = mClass.StartTime;
this.EndTime = mClass.EndTime;
this.IsOpen = mClass.IsOpen;
this.IsComplete = mClass.IsComplete;
this.Created = mClass.Created;
this.CourseCode = mClass.CourseCode;
this.CourseName = mClass.CourseName;
this.TeacherName = mClass.TeacherName;
}
private CourseClassBase Get(string WhereClause, params SqlParameter[] commandparameters)
{
CourseClassBase mClass = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "SELECT db.CourseClassId, db.CourseId, db.TeacherId, db.StartDate, db.EndDate, db.CourseDay, " +
"db.StartTime, db.EndTime, db.IsOpen, db.IsComplete, db.Created, " +
"cr.Code AS CourseCode, cr.Name AS CourseName, " +
"(tc.FirstName + ' ' + tc.LastName) AS TeacherName, " +
"st.TimeLabel AS StartTimeLabel, et.TimeLabel AS EndTimeLabel " +
"FROM CourseClass db " +
"LEFT OUTER JOIN Course cr ON cr.CourseId = db.CourseId " +
"LEFT OUTER JOIN Teacher tc ON tc.TeacherId = db.TeacherId " +
"LEFT OUTER JOIN SystemTime st ON st.TimeId = db.StartTime " +
"LEFT OUTER JOIN SystemTime et ON et.TimeId = db.EndTime " +
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);
}
public int Add(CourseClassBase mClass)
{
int CourseClassId = 0;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "INSERT INTO CourseClass (CourseId, TeacherId, StartDate, EndDate, CourseDay, StartTime, EndTime, IsOpen, IsComplete)" +
" VALUES (@CourseId, @TeacherId, @StartDate, @EndDate, @CourseDay, @StartTime, @EndTime, @IsOpen, @IsComplete)" +
" SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@CourseId", SqlDbType.Int).Value = mClass.CourseId;
cmd.Parameters.Add("@TeacherId", SqlDbType.Int).Value = mClass.TeacherId;
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = mClass.StartDate;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = mClass.EndDate;
cmd.Parameters.Add("@CourseDay", SqlDbType.VarChar).Value = mClass.CourseDay;
cmd.Parameters.Add("@StartTime", SqlDbType.Int).Value = mClass.StartTime;
cmd.Parameters.Add("@EndTime", SqlDbType.Int).Value = mClass.EndTime;
cmd.Parameters.Add("@IsOpen", SqlDbType.Bit).Value = mClass.IsOpen;
cmd.Parameters.Add("@IsComplete", SqlDbType.Bit).Value = mClass.IsComplete;
try
{
con.Open();
CourseClassId = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
CourseClassId = -1;
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(CourseClassId);
}
public bool Update(CourseClassBase mClass)
{
bool mResult = false;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "UPDATE CourseClass SET CourseId = @CourseId, TeacherId = @TeacherId, StartDate = @StartDate, EndDate = @EndDate, CourseDay = @CourseDay, StartTime = @StartTime, EndTime = @EndTime, IsOpen = @IsOpen, IsComplete = @IsComplete " +
"WHERE CourseClassId = @CourseClassId;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@CourseClassId", SqlDbType.Int).Value = mClass.CourseClassId;
cmd.Parameters.Add("@CourseId", SqlDbType.Int).Value = mClass.CourseId;
cmd.Parameters.Add("@TeacherId", SqlDbType.Int).Value = mClass.TeacherId;
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = mClass.StartDate;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = mClass.EndDate;
cmd.Parameters.Add("@CourseDay", SqlDbType.VarChar).Value = mClass.CourseDay;
cmd.Parameters.Add("@StartTime", SqlDbType.Int).Value = mClass.StartTime;
cmd.Parameters.Add("@EndTime", SqlDbType.Int).Value = mClass.EndTime;
cmd.Parameters.Add("@IsOpen", SqlDbType.Bit).Value = mClass.IsOpen;
cmd.Parameters.Add("@IsComplete", SqlDbType.Bit).Value = mClass.IsComplete;
try
{
con.Open();
cmd.ExecuteNonQuery();
mResult = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(mResult);
}