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 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 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;
}
}
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 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 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;
}
}
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;
}
}
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 User Register(string email, string firstname, string lastname, string username,
string question1, string answer1, string question2, string answer2, string question3, string answer3)
{
Database db = new Database("Books");
try
{
String Pass = ValidateEmail.GeneratePassword (3,3,3);
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblUserREGISTER";
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 = Pass;
db.Command.Parameters.Add("@Email", SqlDbType.VarChar).Value = email;
db.Command.Parameters.Add("@Question1", SqlDbType.VarChar).Value = question1;
db.Command.Parameters.Add("@Answer1", SqlDbType.VarChar).Value = answer1;
db.Command.Parameters.Add("@Question2", SqlDbType.VarChar).Value = question2;
db.Command.Parameters.Add("@Answer2", SqlDbType.VarChar).Value = answer2;
db.Command.Parameters.Add("@Question3", SqlDbType.VarChar).Value = question3;
db.Command.Parameters.Add("@Answer3", SqlDbType.VarChar).Value = answer3;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
base.Initialize(dr);
InitializeBusinessData(dr);
Email e = new Email();
e.To = email;
e.Subject = "Your Password";
e.Body = String.Format("Your password is {0}", dr["Password"].ToString());
e.Send();
return this;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw;
}
}
public Boolean Recover(string email, string firstname, string lastname, string username, string answer)
{
Database db = new Database("Books");
try
{
if (_Answers[_index] == answer)
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblUserRECOVER";
db.Command.Parameters.Add("@Email", SqlDbType.VarChar).Value = email;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
Email e = new Email();
e.To = email;
e.Subject = "Password Recovery";
e.Body = String.Format("Your password is {0}", dr["password"].ToString());
e.Send();
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
catch (Exception ex)
{
throw;
}
}
public Boolean Recover(string email)
{
Database db = new Database("Books");
try
{
db.Command.CommandType = CommandType.StoredProcedure;
db.Command.CommandText = "tblUserRECOVER";
db.Command.Parameters.Add(@"Email", SqlDbType.VarChar).Value = email;
DataTable dt = db.ExecuteQuery();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
_Questions.Add(dr["Question1"].ToString());
_Questions.Add(dr["Question2"].ToString());
_Questions.Add(dr["Question3"].ToString());
_Answers.Add(dr["Answer1"].ToString());
_Answers.Add(dr["Answer2"].ToString());
_Answers.Add(dr["Answer3"].ToString());
base.Initialize(dr);
return true;
}
else
{
return false;
}
}
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;
}
