public BookType GetByID(Guid ID)
{
Database db = new Database("Books");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblBookTypeGETBYID";
db.Command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = ID;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
base.Initialize(dr);
InitializeBusinessData(dr);
return this;
}
else
{
throw new Exception("Book");
}
}
catch (Exception ex)
{
throw;
}
}
public ReviewList GetByBookID(Guid BookID)
{
Database db = new Database("Books");
try
{
db.Command.Parameters.Clear();
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblReviewGETBYBOOKID";
db.Command.Parameters.Add("@BookID", SqlDbType.UniqueIdentifier).Value = BookID;
DataTable dt = db.ExecuteQuery();
foreach (DataRow dr in dt.Rows)
{
Review s = new Review();
s.Initialize(dr);
s.InitializeBusinessData(dr);
s.IsDirty = false;
s.IsNew = false;
s.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(s);
}
}
catch (Exception ex)
{
throw;
}
return this;
}
public ReviewList GetAll()
{
try
{
_list.Clear();
Database db = new Database("Books");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblReviewGETALL";
DataTable dt = db.ExecuteQuery();
//dgvStudent.DataSource = dt;
foreach (DataRow dr in dt.Rows)
{
Review s = new Review();
s.Initialize(dr);
s.InitializeBusinessData(dr);
s.IsDirty = false;
s.IsNew = false;
s.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(s);
}
}
catch(Exception ex)
{
throw;
}
return this;
}
public Zipcode GetByZipId(Guid zipId)
{
try
{
Database db = new Database("Student");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblZipcodeGetById";
db.Command.Parameters.Add("@Id", System.Data.SqlDbType.UniqueIdentifier).Value = zipId;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
Id = (Guid)dr["Id"];
Zip = dr["Zipcode"].ToString();
City = dr["City"].ToString();
State = dr["State"].ToString();
return this;
}
else
{
throw new Exception("Zipcode not found");
}
}
catch
{
throw;
}
}
private Boolean Delete(Database db)
{
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentClassDelete";
base.Initialize(db.Command, base.Id);
db.ExecuteNonQueryWithTransaction();
base.Initialize(db.Command);
return true;
}
catch (Exception ex) { throw; }
}
public StudentClass Save(Database db, Guid parentid)
{
_studentId = parentid;
Boolean result = false;
if (base.IsNew == true && IsSavable() == true)
result = Insert(db);
else if (base.Deleted == true)
result = Delete(db);
else if (base.IsNew == false && IsSavable() == true)
result = Update(db);
base.IsDirty = false;
base.IsNew = false;
return this;
}
public Boolean Save(Database db, Guid parentId)
{
Boolean result = true;
foreach (StudentComment s in _list)
if (s.IsSavable() == true)
{
s.Save(db, parentId);
if (s.IsNew == true)
{
result = false;
break;
}
}
return result;
}
public Client GetById(Guid id)
{
Database db = new Database("Customer");
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblClient_GetById";
base.Initialize(db.Command, id);
DataTable dt=db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
InitializeBusinessData(dr);
base.Initialize(dr);
}
return this;
}
public bool Save(Database db, Guid studentId)
{
bool result = true;
foreach (StudentAddress s in _list)
if (s.IsSavable() == true)
{
s.Save(db, studentId);
if (s.IsNew == true)
{
result = false;
break;
}
}
return result;
}
public ForumList GetAll()
{
_list.Clear();
Database db = new Database("student");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblForumGetAll";
DataTable dt = db.ExecuteQuery();
foreach (DataRow dr in dt.Rows)
{
Forum f = new Forum();
f.InitializeBusinessData(dr);
_list.Add(f);
}
return this;
}
public StudentList GetAll()
{
_list.Clear();
Database db = new Database("student");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentGetAll";
DataTable dt = db.ExecuteQuery();
foreach (DataRow dr in dt.Rows)
{
Student s = new Student();
s.Initialize(dr);
s.InitializeBusinessData(dr);
s.IsDirty = false;
s.IsNew = false;
s.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(s);
}
return this;
}
public StudentAddressList GetByStudentId(Guid studentId)
{
_list.Clear();
Database db = new Database("Student");
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentAddressGetByStudentID";
db.Command.Parameters.Add("@StudentId", SqlDbType.UniqueIdentifier).Value = studentId;
DataTable dt = db.ExecuteQuery();
foreach (DataRow dr in dt.Rows)
{
StudentAddress s = new StudentAddress();
s.Initialize(dr);
s.InitializeBusinessData(dr);
s.IsDirty = false;
s.IsNew = false;
s.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(s);
}
return this;
}
public PhoneTypeList GetAll()
{
_list.Clear();
Database db = new Database("student");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblPhoneTypeGetAll";
DataTable dt = db.ExecuteQuery();
PhoneType Blank = new PhoneType();
Blank.Type = "Select Phone Type";
_list.Add(Blank);
foreach (DataRow dr in dt.Rows)
{
PhoneType e = new PhoneType();
e.Initialize(dr);
e.InitializeBusinessData(dr);
e.IsDirty = false;
e.IsNew = false;
e.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(e);
}
return this;
}
public BookList GetByBookTypeID(Guid bookTypeID)
{
_list.Clear();
Database db = new Database("Books");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblBookGETBYBOOKTYPEID";
db.Command.Parameters.Add("@BookTypeID", SqlDbType.UniqueIdentifier).Value = bookTypeID;
DataTable dt = db.ExecuteQuery();
//dgvStudent.DataSource = dt;
foreach (DataRow dr in dt.Rows)
{
Book s = new Book();
s.Initialize(dr);
s.InitializeBusinessData(dr);
s.IsDirty = false;
s.IsNew = false;
s.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(s);
}
return this;
}
public ClassList GetByProgramId(Guid programId)
{
_list.Clear();
Database db = new Database("student");
db.Command.CommandType = System.Data.CommandType.StoredProcedure;
db.Command.CommandText = "tblClassGetByProgramId";
db.Command.Parameters.Add("@ProgramId", SqlDbType.UniqueIdentifier).Value = programId;
DataTable dt = db.ExecuteQuery();
Class Blank = new Class();
Blank.Name = "Select Classes";
_list.Add(Blank);
foreach (DataRow dr in dt.Rows)
{
Class c = new Class();
c.Initialize(dr);
c.InitializeBusinessData(dr);
c.IsDirty = false;
c.IsNew = false;
c.evtIsSavable += new IsSavableHandler(s_evtIsSavable);
_list.Add(c);
}
return this;
}
public bool Save(Database db, Guid studentId)
{
bool result = true;
try
{
foreach (StudentClass s in _list)
if (s.IsSavable() == true)
{
s.Save(db, studentId);
if (s.IsNew == true)
{
result = false;
break;
}
}
}
catch (Exception e)
{
throw;
}
return result;
}
public Program GetById(Guid Id)
{
Database db = new Database("Student");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblProgramGetById";
db.Command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
base.Initialize(dr);
InitializeBusinessData(dr);
return this;
}
else
throw new Exception("Program not found");
}
catch (Exception ex)
{
throw;
}
}
public Student Register(string firstname,
string lastname,
string username,
string password,
Guid programId)
{
Database db = new Database("student");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentRegister";
db.Command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = firstname;
db.Command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastname;
db.Command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username;
db.Command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
db.Command.Parameters.Add("@ProgramId", SqlDbType.UniqueIdentifier).Value = programId;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
base.Initialize(dr);
InitializeBusinessData(dr);
return this;
}
else
return null;
}
catch (Exception ex)
{
throw;
}
}
private Boolean Insert(Database db)
{
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblPhoneTypeInsert";
base.Initialize(db.Command, Guid.Empty);
db.Command.Parameters.Add("@Type", SqlDbType.VarChar).Value = _Type;
db.ExecuteNonQuery();
base.Initialize(db.Command);
return true;
}
catch (Exception ex)
{
throw;
}
}
private Boolean Update(Database db)
{
try
{
db.Command.Parameters.Clear();
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblBookUPDATE";
base.Initialize(db.Command, base.ID);
db.Command.Parameters.Add("@BookTypeID", SqlDbType.UniqueIdentifier).Value = _BookTypeID;
db.Command.Parameters.Add("@Title", SqlDbType.VarChar).Value = _Title;
db.Command.Parameters.Add("@Price", SqlDbType.VarChar).Value = _Price;
db.Command.Parameters.Add("@BookCover", SqlDbType.Image).Value = PhotoHelper.Photo.FileToByteArray(_PhotoName);
db.Command.Parameters.Add("@MimeType", SqlDbType.VarChar).Value = Path.GetExtension(_PhotoName);
db.ExecuteNonQueryWithTransaction();
base.Initialize(db.Command);
return true;
}
catch (Exception ex)
{
throw;
}
}
private Boolean Delete(Database db)
{
try
{
db.Command.Parameters.Clear();
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblBookDELETE";
base.Initialize(db.Command, base.ID);
db.ExecuteNonQueryWithTransaction();
base.Initialize(db.Command);
return true;
}
catch (Exception ex)
{
throw;
}
}
public Book Save()
{
Database db = new Database("Books");
db.BeginTransaction();
Boolean result = true;
if (base.IsNew == true && IsSavable() == true)
{
result = Insert(db);
}
else if (base.Deleted == true)
{
result = Delete(db);
}
else if (base.IsNew == false && IsSavable() == true)
{
result = Update(db);
}
base.IsDirty = false;
base.IsNew = false;
if (result == true && _ReviewList != null && _ReviewList.IsSavable() == true)
{
result = _ReviewList.Save(db, base.ID);
}
if (result == true)
{
db.EndTransaction();
}
else
{
db.RollBackTransaction();
}
return this;
}
public Book Save(Database db, Guid userID)
{
_UserID = userID;
Boolean result = true;
if (base.IsNew == true && IsSavable() == true)
{
result = Insert(db);
UserBook ub = new UserBook();
ub.UserID = userID;
_UserBooks = new UserBookList();
_UserBooks.List.Add(ub);
}
else if (base.Deleted == true)
{
result = Delete(db);
}
else if (base.IsNew == false && IsSavable() == true)
{
result = Update(db);
}
base.IsDirty = false;
base.IsNew = false;
if (result == true && _UserBooks != null && _UserBooks.IsSavable() == true)
{
result = _UserBooks.Save(db, userID, base.ID);
}
return this;
}
public Student ChangePassword(string newpassword)
{
Database db = new Database("student");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentChangePassword";
base.Initialize(db.Command, base.Id);
db.Command.Parameters.Add("@Password", SqlDbType.VarChar).Value = newpassword;
db.ExecuteNonQuery();
base.Initialize(db.Command);
return this;
}
catch (Exception ex)
{
throw;
}
}
public Student Login(string username, string password)
{
Database db = new Database("student");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentLogin";
db.Command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username;
db.Command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
base.Initialize(dr);
InitializeBusinessData(dr);
return this;
}
else
throw new ApplicationException("Invalid Login");
}
catch (Exception ex)
{
throw;
}
}
public Boolean Recover(string firstname,
string lastname,
string username,
string emailaddress)
{
Database db = new Database("student");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentRecover";
db.Command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = firstname;
db.Command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastname;
db.Command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
Email email = new Email();
email.Host = "smtp.gmail.com";
email.Port = 587;
email.Username = "******";
email.Password = "******";
email.To = emailaddress;
email.From = "*****@*****.**";
email.Subject = "Password Recovery";
email.Body =
String.Format("Your password is {0}",
dr["password"].ToString());
email.Send();
return true;
}
else
return false;
}
catch (Exception ex)
{
throw;
}
}
private Boolean Update(Database db)
{
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblProgramUpdate";
base.Initialize(db.Command, base.Id);
db.Command.Parameters.Add("@Name", SqlDbType.VarChar).Value = _Name;
db.ExecuteNonQuery();
base.Initialize(db.Command);
return true;
}
catch (Exception ex)
{
throw;
}
}
public Student Save()
{
Database db = new Database("student");
try
{
db.BeginTransaction();
Boolean result = true;
if (base.IsNew == true && IsSavable() == true)
{
result = Insert(db);
}
else if (base.Deleted == true)
{
result = Delete(db);
}
else if (base.IsNew == false && IsSavable() == true)
{
result = Update(db);
}
base.IsDirty = false;
base.IsNew = false;
//SAVE THE CHILDREN
if (result == true && _comments != null && _comments.IsSavable() == true)
{
result = _comments.Save(db, base.Id);
}
if (result == true && _emails != null && _emails.IsSavable() == true)
{
result = _emails.Save(db, base.Id);
}
if (result == true && _phones != null && _phones.IsSavable() == true)
{
result = _phones.Save(db, base.Id);
}
if (result == true && _addresses != null && _addresses.IsSavable() == true)
{
result = _addresses.Save(db, base.Id);
}
if (result == true && _classes != null && _classes.IsSavable() == true)
{
result = _classes.Save(db, base.Id);
}
if (result == true)
{
db.EndTransaction();
}
else
{
db.RollBackTransaction();
}
}
catch (Exception e)
{
db.RollBackTransaction();
throw;
}
return this;
}
public Program Save()
{
Database db = new Database("student");
Boolean result = false;
if (base.IsNew == true && IsSavable() == true)
{
result = Insert(db);
}
else if (base.Deleted == true)
{
result = Delete(db);
}
else if (base.IsNew == false && IsSavable() == true)
{
result = Update(db);
}
base.IsDirty = false;
base.IsNew = false;
return this;
}
private Boolean Update(Database db)
{
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblStudentUpdate";
base.Initialize(db.Command, base.Id);
db.Command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = _FirstName;
db.Command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = _LastName;
db.Command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = _UserName;
db.Command.Parameters.Add("@Password", SqlDbType.VarChar).Value = _Password;
db.ExecuteNonQueryWithTransaction();
base.Initialize(db.Command);
return true;
}
catch (Exception ex)
{
throw;
}
}