public bool IsEmailExists(Student student)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Student WHERE (Email=@Email)";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
command.Parameters.Add("Email", SqlDbType.VarChar);
command.Parameters["Email"].Value = student.Email;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
bool isEmailExist = false;
if (reader.HasRows)
{
isEmailExist = true;
}
connection.Close();
return isEmailExist;
}
public int Save(Student student)
{
//Department department = new Department();
connection.ConnectionString = connectionString;
string query = "INSERT INTO Student(Name,Email,ContactNo,Date,Address,DepartmentId,DepartmentCode) VALUES(@Name,@Email,@ContactNo,@Date,@Address,@DepartmentId,@DepartmentCode)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Clear();
command.Parameters.Add("Name", SqlDbType.VarChar);
command.Parameters["Name"].Value = student.Name;
//command.Parameters.Add("RegNo", SqlDbType.VarChar);
//command.Parameters["RegNo"].Value = student.Date.ToString("yyyy") + "-" + student.ContactNo + "-" + student.Id;
command.Parameters.Add("Email", SqlDbType.VarChar);
command.Parameters["Email"].Value = student.Email;
command.Parameters.Add("ContactNo", SqlDbType.VarChar);
command.Parameters["ContactNo"].Value = student.ContactNo;
command.Parameters.Add("Date", SqlDbType.DateTime);
command.Parameters["Date"].Value = student.Date;
command.Parameters.Add("Address", SqlDbType.VarChar);
command.Parameters["Address"].Value = student.Address;
command.Parameters.Add("DepartmentId", SqlDbType.Int);
command.Parameters["DepartmentId"].Value = student.DepartmentId;
command.Parameters.Add("DepartmentCode", SqlDbType.VarChar);
command.Parameters["DepartmentCode"].Value = student.DepartmentCode;
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public ActionResult SaveStudent(Student student, string Email)
{
ViewBag.PostBack = false;
List<Department> departments = departmentManager.GetAllDepartments();
ViewBag.Departments = departments;
ViewBag.Message = studentManager.Save(student);
if (Email != String.Empty)
{
ViewBag.PostBack = true;
}
StudentView students = studentManager.GetAllStudentByEmail(Email);
ViewBag.Students = students;
ModelState.Clear();
return View();
}
public string Save(Student student)
{
if (studentGateway.IsEmailExists(student))
{
return "Email already exist";
}
else
{
if (studentGateway.Save(student) > 0)
{
return "Saved";
}
else
{
return "Save failed";
}
}
}
public Student GetAllStudentbyRegNo(string regNo)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Student WHERE Student.RegNo= '" + regNo + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
Student student = new Student();
while (reader.Read())
{
student.Id = Convert.ToInt32(reader["Id"].ToString());
student.Name = reader["Name"].ToString();
student.Email = reader["Email"].ToString();
student.RegNo = reader["RegNo"].ToString();
student.DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString());
}
reader.Close();
connection.Close();
return student;
}
public List<Student> GetAllStudent()
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Student ORDER BY RegNo ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<Student> studentslist = new List<Student>();
while (reader.Read())
{
Student student = new Student();
student.Id = Convert.ToInt32(reader["Id"].ToString());
student.RegNo = reader["RegNo"].ToString();
student.DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString());
studentslist.Add(student);
}
reader.Close();
connection.Close();
return studentslist;
}