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); }